Data Engineering Interview: How Do You Diagnose and Fix the Small-Files Problem in a Lakehouse?

Prompt and Applicable Context

An Apache Iceberg event table on object storage has 20 TiB of active data. A streaming job commits one-minute micro-batches. It adds about 200 GiB per day but creates roughly 80,000 Parquet data files whose median size is only 3 MiB. Query p95 has recently increased from 20 seconds to 95 seconds, while the planning phase rose from 4 seconds to 32 seconds. The business must keep near-real-time ingestion, retain seven days of time travel, and never delete table objects directly from storage.

Explain how you would prove that small files dominate the regression, find the write and partitioning causes, stop the growth, and compact existing files safely. Include concurrency control, resource budgeting, rollback, and acceptance criteria.

All capacities, file counts, latencies, and throughput values are interview assumptions, not universal benchmarks. This question fits data engineering, analytics platform, lakehouse infrastructure, and data-platform SRE roles. Its core competency is data layout and table maintenance, so the category is data; it is not merely a Spark-configuration or object-storage operations question.

What the Interviewer Is Evaluating

First, can the candidate build an evidence chain? A high file count alone does not establish causality. Active file count, size distribution, partition skew, manifest-reading time, task startup, and file-open overhead must be related separately to query planning and scanning.

Second, can the candidate distinguish a backlog fix from prevention? Compaction handles existing files. If micro-batch frequency, writer count, data distribution, and overly fine partitioning remain unchanged, the table will fragment again.

Third, does the candidate respect table-format transaction boundaries? Iceberg compaction rewrites data files and commits a new snapshot. Older snapshots can still reference old files, so deleting objects behind the metadata is unsafe.

Fourth, can the candidate make bounded trade-offs? Bin packing primarily changes file sizes. Sorting or Z-ordering also changes clustering and can improve pruning, but costs more shuffle, sorting, and temporary storage.

Fifth, can the candidate quantify an operating plan? A strong answer estimates bytes rewritten per day, target file count, job window, and conflict risk, then uses both correctness and performance metrics to decide whether to expand the rollout.

Clarifying Questions Before Answering

• What target defines “small”? Read write.target-file-size-bytes, then choose thresholds using query selectivity, per-partition daily volume, and engine tests. One fixed size is not correct for every table.
• Are the 80,000 files active in the current snapshot or counted across historical snapshots? Start with files for the query path; use all_files and snapshot references for retention and storage cost.
• Is the latency in planning or scanning? Rising planning share points toward manifests and file tasks. Falling scan throughput also requires checking skew, delete files, compression, column statistics, and downstream resources.
• What are the partition spec and write distribution? High-cardinality or overly fine time partitions may never accumulate a target-sized file. Excessive parallel writers may each commit a partially filled file.
• Does the table use copy-on-write or merge-on-read? Merge-on-read may accumulate position or equality delete files, so compacting data files alone may not be enough.
• Which partitions still receive late data? Prefer closed, cold partitions. Hot partitions require smaller file groups, controlled concurrency, and conflict retries.
• What does seven-day retention mean? Confirm snapshot queryability, branch or tag retention, and object-store lifecycle separately. A directory deletion cannot substitute for all three.

30-Second Answer Framework

“I would profile active files, size percentiles, and partitions from the current Iceberg snapshot, then separate planning from scanning. At 200 GiB per day, 80,000 files versus roughly 400 ideal 512 MiB files makes micro-batches, writers, and partition granularity the first suspects.

I would stop new fragmentation by enlarging batches, distributing by partition key, and controlling writer count. Then I would bin-pack one cold partition with bounded concurrency, sorting only when pruning tests justify it. Compaction commits a new snapshot; old files follow seven-day retention and are never deleted directly. Data reconciliation, file percentiles, planning and query p95, lag, and conflicts determine whether the rollout expands.”

Step-by-Step Deep Dive

Step 1: Profile Files from the Current Snapshot

Query Iceberg metadata instead of recursively listing the object-store directory. The directory can contain files referenced only by historical snapshots or orphaned objects, so it does not represent what a current query plans. The following SQL illustrates a Spark and Iceberg catalog; adapt the catalog name and percentile function to the actual engine:

SELECT
  partition,
  COUNT(*) AS active_files,
  SUM(file_size_in_bytes) AS active_bytes,
  percentile_approx(file_size_in_bytes, array(0.5, 0.9, 0.99)) AS size_percentiles
FROM lakehouse.analytics.events.files
GROUP BY partition
ORDER BY active_files DESC;

Record the current snapshot ID, data-file and delete-file counts, manifest count, file-size percentiles by partition, and the percentage of files below a candidate threshold. A mean hides the long tail: a partition can have a few large files and tens of thousands of 1-to-3 MiB files. At minimum, inspect p50, p90, p99, and a histogram.

Break query p95 into catalog and manifest resolution, file planning, task scheduling, time to first byte, and scanning. The causal case becomes stronger when file count and planning time rise together and a test partition with the same total bytes plans substantially faster after compaction. If planning is stable but scanning slows, investigate selectivity, column statistics, delete files, skew, and compute resources instead.

Step 2: Find the Regeneration Cause in the Write Path

The scenario produces 1,440 one-minute batches per day. Eighty thousand files is about 56 files per batch on average. When each writer or writer-partition combination receives too little data, setting a 512 MiB target cannot turn a 3 MiB output into a full file. write.target-file-size-bytes is a target, not a guarantee that every file reaches it.

The root cause is often a combination: micro-batches are too frequent; upstream parallelism is excessive for each batch; rows are not clustered by the table partition key before writing; hourly, tenant, or user dimensions over-partition the table; hot keys cause skew; retries add commits; or merge-on-read updates create delete-file debt.

Interpret “small” per partition. A valid low-volume partition that receives only 40 MiB per day can never fill a 512 MiB file. Accept a smaller target, coarsen the partition spec, or use buckets or hidden partitioning instead of increasing compaction frequency forever.

Step 3: Stop Producing the Same Fragmentation

Make the smallest write-side change in a canary. Within the freshness SLA, combine one-minute commits into a larger trigger batch. Hash- or range-distribute rows by the Iceberg partition key. Choose writer count from bytes per batch rather than maximum cluster parallelism. Avoid directly partitioning on high-cardinality columns.

Test the target file size against real compression ratio, row width, query selectivity, and daily volume per partition. The scenario uses 512 MiB, or 536,870,912 bytes, as a candidate because the Iceberg default provides a reasonable starting point. It does not rule out 128 MiB, 256 MiB, or a larger value. If a partition is much smaller than the target, evolve the partition spec. If enough data exists but each writer receives few rows, fix distribution and parallelism.

Run an A/B comparison on two similarly loaded partitions. Compare file count, size distribution, commit latency, stream-processing lag, and failure recovery at equal data volume. Backlog compaction becomes sustainable only after the new-file generation rate falls materially.

Step 4: Bound Compaction by Benefit and Conflict Risk

For the first pass, choose one cold partition whose late-data and business-correction windows have closed, and at least exclude the currently written hour. Whether a partition can still change and whether snapshots are retained for seven days are separate timelines. Start with bin packing because the immediate goal is lower metadata and file-open overhead. Upgrade to sorting or Z-ordering only when common filter columns overlap heavily across files and a benchmark justifies the extra shuffle.

CALL lakehouse.system.rewrite_data_files(
  table => 'analytics.events',
  strategy => 'binpack',
  options => map(
    'target-file-size-bytes', '536870912',
    'min-input-files', '5',
    'max-concurrent-file-group-rewrites', '3',
    'partial-progress.enabled', 'true'
  ),
  where => 'event_date = DATE ''2026-07-10'''
);

The where predicate selects files that may contain matching rows. Align it with partition boundaries and inspect candidate bytes before execution. File groups bound each unit of work. Controlled concurrency prevents object storage, shuffle, and the query cluster from saturating together. Partial progress commits groups separately, reducing the cost of retrying a conflict, but it creates multiple snapshots and requires group-level monitoring and rollback.

If hot partitions cannot be avoided, shrink the time range and file groups, make scheduling idempotent, and distinguish data-file conflicts from retryable metadata commit conflicts. Never run two compaction jobs with overlapping table ranges.

Step 5: Estimate Target File Count, I/O, and Window

Dividing 200 GiB by 512 MiB gives an ideal count of about 400 target files. Partition boundaries, compression, and trailing remainders make the actual count somewhat higher. The estimate catches order-of-magnitude errors; it is not a promise of exactly 400 outputs.

Compacting one full daily partition reads about 200 GiB and writes about 200 GiB, or roughly 400 GiB of data I/O, plus shuffle, temporary storage, metadata, and retries. If a benchmark measures sustained end-to-end throughput of 100 MiB/s by input bytes, the ideal duration is:

200 GiB * 1024 MiB/GiB / 100 MiB/s = 2,048 s ≈ 34.1 min

Add margin for skew, concurrent queries, and retries. A 60-to-90-minute window is a reasonable scenario budget, together with limits on candidate bytes, concurrent file groups, object-store request rate, and temporary disk. If compaction can process only 150 GiB per day while 200 GiB arrives, the backlog must grow. Increase sustainable throughput or reduce new-file creation first.

Step 6: Separate Snapshot Retention from Physical Cleanup

After compaction, the new snapshot references large files. A query already reading an older snapshot can still finish, and seven-day time travel still needs old files. Directly deleting the original Parquet objects would break both behaviors.

Validate the new snapshot and observe a full business cycle before running expire_snapshots. Preserve the seven-day window, required branches or tags, and a minimum snapshot count. Snapshot expiration removes only files no longer required by any retained snapshot. Orphan files are a different class: no table metadata references them. Run remove_orphan_files separately, start with dry_run, choose a conservative older_than, and verify path schemes, authorities, and the longest in-flight write before deleting.

Snapshot expiration is not compaction, and orphan cleanup is not a substitute for expiring old snapshot references. Give all three independent schedules, permissions, and audit records.

Step 7: Canary, Validate, and Define Stop Conditions

Pin the pre-compaction snapshot and candidate partition. Record row count, distinct business-key count, critical amount or event aggregates, minimum and maximum event time, and null counts. Recompute the same logical range afterward. Total row count alone can hide one lost row offset by one duplicate, so critical tables should add bucketed checksums or business-key samples.

For performance, compare active file count, p50/p90/p99 size, manifest count, planning p50/p95, end-to-end query p95, scanned bytes, rewritten bytes, and resource cost. Operational metrics include small-file generation rate, compaction lag, failed file groups, commit conflicts, snapshot count, and reclaimable bytes.

Stop expansion if correctness differs, planning time does not improve, the cost moves into the ingestion SLA, or compaction throughput remains below the new fragmentation rate. A table snapshot can roll back the metadata pointer, but it cannot reverse already completed snapshot expiration and physical deletion by itself.

High-Quality Sample Answer

“I would not start by running compaction. I would first prove the bottleneck. An object-store directory mixes historical and orphaned files, so I would use the current Iceberg snapshot's files metadata table to measure active files, total bytes, and p50/p90/p99 sizes by partition. I would then separate catalog and manifest resolution, task planning, file opening, and scanning. In the scenario, 200 GiB per day creates 80,000 files with a 3 MiB median. A 512 MiB candidate target implies roughly 400 ideal files, so the write layout is a strong lead, but I would still confirm that a compacted partition with equal bytes plans faster.

Next I would fix regeneration. There are 1,440 one-minute batches per day and about 56 files per batch. I would inspect writer parallelism, partition cardinality, pre-write distribution, skew, retries, and merge-on-read delete files. Within the freshness SLA, I would enlarge commit batches, hash- or range-distribute by partition key, and size writer count from batch bytes. The 512 MiB value is only a starting point. A low-volume partition that cannot fill it needs a coarser partition or a smaller target.

For the backlog, I would canary one cold partition with rewrite_data_files bin packing, a partition predicate, bounded file-group concurrency, and a candidate-byte cap. I would use sorting or Z-ordering only if common filter tests show clear value because sorting adds shuffle and temporary storage. If a hot partition must be processed, I would use smaller file groups, partial progress, and bounded conflict retries. I would prevent overlapping compactions.

At 200 GiB and 512 MiB, the ideal output is about 400 files. One pass reads about 200 GiB and writes 200 GiB. At a measured end-to-end rate of 100 MiB/s by input bytes, the ideal runtime is 34.1 minutes; I would reserve 60 to 90 minutes and prove that daily capacity exceeds daily input.

Compaction atomically commits a new snapshot. Existing queries and seven-day time travel keep referencing old files, so I would never delete objects directly. After validating row counts, business aggregates, bucketed checksums, and query p95 on the new snapshot, I would expire snapshots under the seven-day policy. Orphan cleanup remains a separate, dry-run-first, conservatively delayed job. The rollout dashboard would track correctness, new small-file rate, file percentiles, planning p95, compaction lag, conflicts, and benefit per GiB. Any core regression stops expansion.”

Common Mistakes

• Assuming a high file count proves the cause → Historical files are not current query files → Profile the current snapshot and separate planning from scanning.
• Running one compaction and stopping → Micro-batches, writers, and fine partitions keep producing fragments → Reduce the new-fragment rate before clearing the backlog.
• Treating target size as a hard guarantee → A writer can output only the rows it receives → Tune target, batch bytes, distribution, and per-partition volume together.
• Rewriting the entire 20 TiB table → Cost and conflict scope become excessive → Process cold partitions in benefit-bounded batches.
• Defaulting to sort or Z-order → Both add shuffle and temporary storage → Start with bin packing and justify clustering with pruning benchmarks.
• Deleting old Parquet objects directly → Retained snapshots and concurrent queries may reference them → Use snapshot expiration and a separate dry-run orphan cleanup.
• Validating only total row count → A loss and a duplicate can cancel out → Add business aggregates, key counts, bucketed checksums, and samples.
• Estimating runtime without sustainable throughput → Daily processing below daily input grows the backlog → Budget reads, writes, shuffle, retries, and compaction lag.
• Using coalesce(1) as a universal fix → One writer destroys parallelism and creates a bottleneck → Compute writer count from partition volume and target bytes.

Follow-Up Questions and Responses

Follow-up 1: Why use a 512 MiB target instead of 128 MiB?

The Iceberg default target of 512 MiB, or 536,870,912 bytes, is an experimental starting point for the large table in this scenario, not a universal optimum. Benchmark 128, 256, and 512 MiB or larger candidates against query selectivity, planning cost, task parallelism, compression, and per-partition daily volume. Highly selective queries may prefer smaller files, while throughput scans and very large partitions may prefer larger ones.

Follow-up 2: Why are files still only a few MiB after increasing the target?

The target guides the output size a writer tries to reach; it does not merge data held by different tasks. If a one-minute batch is split across dozens of writers, or one writer touches many low-volume partitions, files close when the task commits. Change batch size, write distribution, parallelism, and partition design instead of only increasing the target.

Follow-up 3: How do you choose among bin packing, sorting, and Z-ordering?

Choose bin packing when the goal is fewer files and lower open overhead. Test sorting when queries frequently filter on one column or a hierarchical key and file statistics can prune ranges. Evaluate Z-ordering only when filters commonly span changing combinations of multiple dimensions. Include the extra shuffle, temporary storage, write amplification, and ongoing maintenance in both latter benchmarks.

Follow-up 4: What if compaction conflicts with streaming writes?

Exclude active partitions first. When that is impossible, use smaller file groups, limit concurrency, enable partial progress, and apply bounded retries to commit conflicts. The scheduler should enforce mutual exclusion for overlapping table ranges. Partial progress prevents one conflicting group from forcing a full rerun, but it adds snapshots and partial-success state, so record every group result.

Follow-up 5: Why does object-store usage not fall immediately after compaction?

Compaction commits a snapshot that references new files. Historical snapshots, branches, or tags still reference old files for concurrent reads and time travel. After seven-day retention is satisfied, snapshot expiration can reclaim files not needed by retained snapshots. Files referenced by no table metadata at all require a separate orphan-cleanup process.

Follow-up 6: How do you prove the gain came from fewer small files rather than cache or extra resources?

Use the same engine settings and make cache state consistently cold or consistently warmed. Run repeatable queries over the same logical snapshot range and record planning time, task count, file opens, scanned bytes, and execution time before and after. Keep an uncompacted, similarly loaded partition as a control and compare percentiles over multiple runs. Causal evidence is stronger only when file layout changes and planning or open overhead falls consistently with it.