The Small-Files Problem: Compaction, OPTIMIZE & File-Sizing Across Engines

The small files problem is the quiet tax every data lake pays as it grows: a table that should live in a few hundred right-sized files instead sprawls across hundreds of thousands of tiny ones, and every query, every listing, and every planning step slows down in proportion to the file count rather than the data size. It is the single most common reason a pipeline that was fast at 10 GB crawls at 10 TB even though the hardware never changed — the bytes barely grew, but the number of objects exploded, and distributed engines are exquisitely sensitive to the number of things they have to track, open, and schedule.

This guide walks the whole problem end to end, the way a senior engineer reasons about it in production and the way an interviewer probes it on a whiteboard. It covers why tiny files are so expensive (metadata pressure, per-file task overhead, slow object-store listings, and query-planning blowup), how they get created without anyone noticing (spark small files from wide shuffles, streaming micro-batches, over-partitioning, and MERGE churn), and how every serious engine fixes them with the same two moves — compaction to bin-pack many small files into few large ones, and write-side file sizing to stop making them in the first place. You will see the exact commands: Spark coalesce/repartition, Delta OPTIMIZE ... ZORDER, Iceberg rewrite_data_files, Hive CONCATENATE, plus the config knobs — optimizeWrite, spark.sql.files.maxPartitionBytes, and target-file-size settings — that keep a table healthy. Each section pairs a teaching block with a worked example and an interview-style scenario, answer choices, an elimination trace, and a concept-by-concept breakdown of why the winning fix wins.

When you want hands-on reps alongside the reading, drill query and layout tuning on the optimization practice library →, rehearse pipeline design on the ETL practice library →, and sharpen your rewrite instincts on the data-transformation practice library →.

---

On this page

• Why small files kill performance
• How small files happen — the write-side causes
• Compaction & OPTIMIZE across engines
• Right file sizing & write-side prevention
• Engine-specific playbooks & interview signals
• Cheat sheet — the small-files playbook
• Frequently asked questions
• Practice on PipeCode

---

1. Why small files kill performance

The cost of a table scales with the number of files, not just the volume of data

The one sentence that reframes everything: a distributed query engine pays a fixed per-file price — to record the file in metadata, to plan a split for it, to open and close it, and to schedule a task for it — so a table split into a hundred thousand tiny files costs roughly a hundred thousand times that fixed price, regardless of how little data each file holds. Two tables can store the identical 100 GB, but the one in 800 files of 128 MB will out-query the one in 800,000 files of 128 KB by an order of magnitude, because the second table forces the engine to do 800,000 units of bookkeeping instead of 800.

Where the cost actually lands. The small-files tax is not one cost; it is four, and they compound.

• Metadata pressure on the catalog / NameNode. On HDFS, the NameNode holds every file, block, and directory as an in-memory object (~150 bytes each); millions of tiny files can exhaust NameNode heap and slow every RPC. On a lakehouse table, the metadata layer (Delta transaction log, Iceberg manifests, Hive metastore partitions) grows with the file count — the manifest that lists your files becomes huge, and reading it before every query gets slow.
• Per-file task and open/close overhead. Spark (and most engines) create at least one read task per file split. A task has scheduling cost, a JVM has file-open/close and footer-read cost, and the driver has to track every task. With one 4 KB file per task, the engine spends nearly all its time in overhead and almost none reading data.
• Listing cost on object stores. On S3/GCS/ADLS there is no cheap directory; a "list this table" is a paginated LIST API call that returns ~1,000 keys per request and is rate-limited. A table with a million files needs a thousand round-trips just to enumerate before it reads a byte — and object stores throttle you if you list too aggressively.
• Query-planning blowup. Before execution, the engine reads each Parquet/ORC file's footer for schema, row-group stats, and min/max for pruning. More files means more footer reads, larger split-planning, and bigger broadcast of file lists to executors — planning time that grows linearly in file count and can dwarf the actual scan.

Why "the data is small" does not save you. A common misdiagnosis is "the query is slow but the table is only 50 GB, so it can't be I/O." Correct — it usually is not I/O. It is overhead: the engine is opening 400,000 files, each contributing a task, a footer read, and a metadata entry. The fix is never a bigger cluster (more executors just schedule the same 400,000 tasks faster); the fix is fewer, larger files.

The symptoms you will actually observe.

• Query planning / job submission takes seconds-to-minutes before any stage runs.
• The Spark UI shows tens or hundreds of thousands of tasks, most finishing in a few milliseconds.
• LIST/metadata API costs climb on your cloud bill even when compute is flat.
• SHOW PARTITIONS or a table DESCRIBE DETAIL reports a file count far larger than bytes / target_size.

Worked example — the arithmetic of one big file versus a hundred thousand tiny ones

Detailed explanation. The most convincing way to internalise the small-files tax is to do the arithmetic on a fixed 100 GB of data laid out two ways. Nothing about the data changes — only the file count — yet the overhead differs by orders of magnitude. This is the calculation an interviewer wants you to be able to do out loud, because it proves you understand that cost tracks file count, and it tells you exactly how aggressively to compact.

• Fixed per-file cost. Assume ~5 ms of combined task-launch + footer-read + open/close overhead per file (a conservative real-world figure on object storage).
• Metadata footprint. Assume ~150 bytes of catalog/NameNode metadata per file object.
• Two layouts. Layout A = 100 GB in 800 files of 128 MB. Layout B = 100 GB in 800,000 files of 128 KB.

Question. For the same 100 GB, how much pure overhead does the 128 KB layout add over the 128 MB layout, in task/open time and in metadata footprint?

Input.

Metric	Layout A (128 MB files)	Layout B (128 KB files)
Total data	100 GB	100 GB
File count	800	800,000
Per-file overhead	~5 ms	~5 ms
Per-file metadata	~150 bytes	~150 bytes

Code.

# Pure overhead time (file_count * per_file_overhead)
Layout A: 800      * 5 ms = 4,000 ms      = ~4 seconds
Layout B: 800,000  * 5 ms = 4,000,000 ms  = ~66.7 minutes

# Metadata footprint (file_count * bytes_per_file)
Layout A: 800      * 150 B = 120 KB
Layout B: 800,000  * 150 B = 120 MB   (in NameNode heap / manifest)

# Object-store LIST round-trips (1,000 keys per LIST call)
Layout A: 800 / 1000     = 1 LIST call
Layout B: 800,000 / 1000 = 800 LIST calls (paginated, rate-limited)

Step-by-step trace.

1. Multiply file count by the fixed per-file overhead: Layout A spends ~4 seconds in pure task/open overhead; Layout B spends ~67 minutes — a 1000× difference that has nothing to do with reading data.
2. Multiply file count by per-file metadata: Layout A holds ~120 KB of catalog metadata; Layout B holds ~120 MB, which stresses the NameNode heap or bloats the table manifest read on every query.
3. Divide file count by the object-store page size (1,000 keys): Layout A enumerates in one LIST; Layout B needs 800 paginated, rate-limited round-trips before a single byte is read.
4. The actual bytes read are identical (100 GB), so every difference above is pure overhead the second layout imposes — the definition of the small-files problem.

Output:

Layout	File count	Pure overhead time	Metadata footprint	LIST calls
A — 128 MB files	800	~4 seconds	~120 KB	1
B — 128 KB files	800,000	~67 minutes	~120 MB	800

Why this works — concept by concept:

• Fixed per-file price — every file costs a task launch, a footer read, and an open/close whether it holds 128 KB or 128 MB, so total overhead is file_count × constant, which is why the fix is always "fewer files," never "faster CPUs."
• Metadata is per-object — the NameNode heap and the table manifest grow with the number of files, so a tiny-file table can exhaust memory or slow planning long before it exhausts disk.
• Listing is per-page — object stores enumerate ~1,000 keys per rate-limited call, so file count directly sets how many round-trips precede every scan.
• Cost — collapsing 800,000 files into 800 cuts overhead time ~1000×, metadata ~1000×, and LIST calls ~800× while touching the same bytes; compaction is one of the highest-leverage optimisations in all of data engineering.

Reading a table's file-size health with DESCRIBE DETAIL

Detailed explanation. Before you compact anything you must measure, and the cheapest measurement is the ratio of table bytes to file count — the average file size. If the average is far below your target (say, kilobytes or a few megabytes against a 128 MB–1 GB target), you have a small-files problem; if it is far above (multi-gigabyte files), you have the opposite problem (skew and poor parallelism). Delta exposes this directly via DESCRIBE DETAIL; Iceberg via its files metadata table; Hive via partition file listings. The habit to build is: never compact blindly — compute the average file size and the file count per partition first, because that tells you both whether to act and how many output files to target.

• Average file size = sizeInBytes / numFiles — the headline health metric.
• Files per partition — a partition with thousands of tiny files is the real hotspot, not the table-wide average.
• Target output file count = total_bytes / target_file_size — how many files a good compaction should produce.
• Skew check — a handful of multi-GB files alongside thousands of tiny ones means both problems coexist.

Question. A Delta table reports 500 GB across 240,000 files. Is it a small-files problem, and how many files should a compaction to a 256 MB target produce?

Input.

Metric	Value
sizeInBytes	500 GB
numFiles	240,000
Target file size	256 MB
Current avg file size	500 GB / 240,000

Code.

-- Delta: read table-level health in one command
DESCRIBE DETAIL delta.`/mnt/lake/events`;
-- returns numFiles, sizeInBytes, partitionColumns, ...

-- Average file size (MB) and target file count:
--   avg_mb    = sizeInBytes / numFiles / 1024 / 1024
--   target_n  = sizeInBytes / (256 * 1024 * 1024)

-- Iceberg equivalent: inspect the files metadata table
SELECT COUNT(*)                        AS num_files,
       SUM(file_size_in_bytes)/1e9     AS gb,
       AVG(file_size_in_bytes)/1048576 AS avg_mb
FROM catalog.db.events.files;

Step-by-step trace.

1. Compute the average file size: 500 GB / 240,000 ≈ 2.18 MB per file — far below the 256 MB target, so yes, this is a textbook small-files problem.
2. Compute the ideal file count at the target: 500 GB / 256 MB ≈ 2,000 files — the table should hold ~2,000 files, not 240,000.
3. The compression ratio of the fix: 240,000 / 2,000 = 120× fewer files after compaction, which is roughly the overhead reduction you can expect.
4. Before running, drill into per-partition counts — if 90% of the tiny files sit in yesterday's partition (a streaming job), you compact that partition, not the whole 500 GB.

Output:

Measurement	Value	Verdict
Avg file size	~2.18 MB	far below target → small-files problem
Ideal file count @ 256 MB	~2,000	vs 240,000 today
Reduction factor	~120×	expected overhead cut

Rule of thumb. Always compute sizeInBytes / numFiles before you compact — if the average is well under ~64 MB you have a small-files problem, and total_bytes / target_size tells you exactly how many output files to aim for.

---

2. How small files happen — the write-side causes

Almost every small-files table traces back to one of four write patterns

The invariant to burn in: small files are almost never random — they are the mechanical output of how you wrote the data, and there are four recurring culprits: streaming micro-batches (one-plus file per trigger interval), over-partitioning (too many partition directories for the data volume), high write parallelism (N shuffle partitions become N output files per partition directory), and MERGE/UPSERT churn (copy-on-write rewrites and delete files). Diagnose which pattern created the mess before you compact, because the durable fix is to change the write, not to keep re-compacting the symptom.

The four causes, and the mechanism behind each.

• Streaming micro-batches. A Structured Streaming (or Flink) job commits output every trigger. At a 10-second trigger, that is 8,640 commits/day, and each commit writes at least one file per output partition. A month of a lightly-loaded stream is millions of tiny files. The file count is driven by time, not data volume.
• Over-partitioning. Partitioning by a high-cardinality column (or by too many columns — year/month/day/hour/country/device) creates a directory per distinct combination. If each combination holds only a few rows, you get one tiny file per directory. The classic mistake is partitioning a 50 GB table by user_id.
• High write parallelism. Spark writes one file per (partition directory × non-empty output task). If a shuffle produces 200 partitions and you write into 100 date directories, a naïve job can emit up to 20,000 files, most of them tiny, because each of the 200 tasks writes a sliver into each directory.
• MERGE / UPSERT churn. Copy-on-write tables rewrite whole files on update, and streaming upserts land many small delta files; merge-on-read tables accumulate small delete files and log files between compactions. Frequent small MERGEs are a steady small-file factory.

How to tell them apart quickly. The diagnosis is usually visible in the layout.

• Files clustered in the newest time partition, arriving on a fixed cadence → streaming micro-batches.
• Thousands of partition directories each holding 1–2 files → over-partitioning.
• A single partition directory holding exactly numShufflePartitions files → high parallelism.
• A partition whose file count climbs after every batch job without new data volume → MERGE churn.

Why fixing the write beats re-compacting. Compaction is a cure; changing the write pattern is prevention. If a streaming job creates 8,640 tiny files a day, a nightly OPTIMIZE cleans up after it — but you burn compute every night forever. Repartitioning the stream's output, raising the trigger interval, or enabling optimizeWrite makes the files right-sized at the source, so compaction becomes an occasional tidy-up rather than a mandatory daily chore.

Streaming micro-batch file explosion — a worked teaching example

Detailed explanation. Consider a Structured Streaming job reading from Kafka and appending to a Delta table, triggered every 10 seconds, with a default 200 shuffle partitions upstream. Each micro-batch can commit up to 200 files (one per shuffle partition), and even if most are empty, you routinely get dozens of tiny files per trigger. Over a day that is hundreds of thousands of files. The fix is to shrink the number of writers per batch (coalesce before the write, or set spark.sql.shuffle.partitions low for the sink) and, where latency allows, lengthen the trigger interval so each batch carries more data.

• Files per batch ≈ non-empty output partitions per trigger.
• Batches per day = 86,400 / trigger_seconds.
• Fix 1 — coalesce(k) before writeStream so each batch writes at most k files.
• Fix 2 — a longer processingTime (or availableNow) trigger so each file carries more rows.

Question. A 10-second-trigger stream writes ~40 files per batch. How many files per day, and how do the two fixes change it?

Input.

Fact	Value
Trigger interval	10 seconds
Files per batch (default)	~40
Fix 1	coalesce(1) per batch
Fix 2	trigger raised to 5 minutes

Code.

# BEFORE: every 10s, ~40 tiny files -> ~345,600 files/day
(spark.readStream.format("kafka").load()
   .writeStream.format("delta")
   .trigger(processingTime="10 seconds")
   .start("/mnt/lake/events"))

# FIX 1: coalesce to one writer per micro-batch (fewer, larger files)
def write_batch(df, batch_id):
    (df.coalesce(1)
       .write.format("delta").mode("append").save("/mnt/lake/events"))

(spark.readStream.format("kafka").load()
   .writeStream.foreachBatch(write_batch)
   .trigger(processingTime="5 minutes")   # FIX 2: bigger batches
   .start())

Step-by-step trace.

1. At a 10-second trigger there are 86,400 / 10 = 8,640 batches/day; at ~40 files each that is 8,640 × 40 = 345,600 files/day — a small-files factory.
2. Fix 1 (coalesce(1) per batch) drops files-per-batch from ~40 to 1, giving 8,640 × 1 = 8,640 files/day — a 40× reduction with no latency change.
3. Fix 2 (raise the trigger to 5 minutes) drops batches/day to 86,400 / 300 = 288; combined with coalesce(1) that is 288 × 1 = 288 files/day.
4. Each of those 288 files now carries 5 minutes of data instead of 10 seconds, so they are ~30× larger and far closer to the target size — prevention, not cleanup.

Output:

Configuration	Files/day	Relative to default
Default (10s, ~40 files/batch)	~345,600	1×
+ coalesce(1)	~8,640	40× fewer
+ 5-min trigger	~288	~1,200× fewer

Rule of thumb. For streaming sinks, drive file count with coalesce before the write and the longest trigger latency your SLA tolerates — then let a periodic compaction mop up the remainder.

Over-partitioning cardinality blowup — a worked teaching example

Detailed explanation. Over-partitioning is the most self-inflicted small-files cause: choosing a partition column with too many distinct values so that each partition directory holds only a handful of rows, hence one tiny file each. Partitioning is a pruning tool — it only pays off when queries filter on the partition column and each partition is large enough to be worth a directory (rule of thumb: at least ~1 GB per partition). Partitioning a 50 GB table by user_id (millions of values) yields millions of near-empty directories; the fix is to partition coarsely (by date) and use clustering/ZORDER for the high-cardinality access instead.

• Good partition column — low cardinality, high query-filter frequency, ≥ ~1 GB per partition (e.g. event_date).
• Bad partition column — high cardinality, few rows per value (e.g. user_id, session_id).
• Rule — data_size / distinct_partition_values should be comfortably above your target file size.
• Fix — coarse partition + clustering (ZORDER / sort) on the high-cardinality column.

Question. A 50 GB table partitioned by user_id (2,000,000 users) produces how many files, and what partitioning fixes it?

Input.

Fact	Value
Table size	50 GB
Partition column	user_id (2,000,000 distinct)
Rows per user	small, uneven
Target file size	256 MB

Code.

-- BAD: one directory (and >=1 tiny file) per user -> ~2,000,000 files
CREATE TABLE events_bad
USING delta PARTITIONED BY (user_id) AS
SELECT * FROM events_raw;             -- 50 GB / 2,000,000 = ~25 KB per partition

-- GOOD: coarse date partition (~large partitions) + cluster on user_id
CREATE TABLE events_good
USING delta PARTITIONED BY (event_date) AS
SELECT * FROM events_raw;
OPTIMIZE events_good ZORDER BY (user_id);   -- fast user lookups WITHOUT a dir per user

Step-by-step trace.

1. Partitioning by user_id creates one directory per distinct user: 2,000,000 directories, each holding 50 GB / 2,000,000 ≈ 25 KB — one ~25 KB file each, so ~2,000,000 tiny files.
2. Switching to event_date (say 400 days) creates 400 directories of 50 GB / 400 = 125 MB each — already near target with a single file per partition.
3. To keep fast per-user lookups without a directory explosion, ZORDER BY (user_id) clusters user rows within each date partition so pruning skips irrelevant blocks.
4. File count collapses from ~2,000,000 to a few hundred, and per-user queries stay fast via data-skipping min/max on the ZORDER column.

Output:

Partitioning	Directories	Avg partition size	Approx files
by user_id	2,000,000	~25 KB	~2,000,000
by event_date	400	~125 MB	~400
by event_date + ZORDER user_id	400	~125 MB	~400, clustered

Rule of thumb. Partition on a low-cardinality column that queries filter on and that yields ≥ ~1 GB partitions; push high-cardinality access into clustering/ZORDER, never into the partition key.

Interview scenario on write-side small-files causes

A Structured Streaming job appends clickstream events to a partitioned Delta table every 15 seconds. Within a week, queries slow to a crawl and the Spark UI shows 900,000 tasks for a 200 GB table. You must both stop the bleeding and prevent recurrence with the least ongoing operational cost.

Solution Using coalesce-before-write plus a longer trigger and periodic OPTIMIZE

Answer choices (as an interview would present them).

• A. Add executors to the streaming cluster so the 900,000 tasks schedule faster.
• B. coalesce each micro-batch to a few writers, lengthen the trigger to minutes, and schedule a periodic OPTIMIZE to compact existing tiny files.
• C. Repartition the whole table by a new high-cardinality key on every write.
• D. Switch the table to Parquet (no transaction log) and hope planning speeds up.

Code.

Elimination:
A  more executors  -> schedules the same 900k tasks faster, file count unchanged   [reject: treats symptom]
C  repartition by high-cardinality key  -> more partition dirs = MORE small files   [reject: worsens it]
D  plain Parquet   -> loses ACID + OPTIMIZE tooling, still tiny-file planning blowup [reject: wrong direction]
B  coalesce + longer trigger (prevent) + periodic OPTIMIZE (cure)                    [ACCEPT]

Step-by-step trace.

1. Name the cause: a 15-second trigger with default parallelism is a streaming micro-batch factory — file count is driven by time × writers, not data.
2. A only makes the cluster process the same 900,000 tiny-file tasks faster; the overhead and metadata pressure remain — eliminate (it treats the symptom, not the cause).
3. C repartitions by a high-cardinality key, which multiplies partition directories and creates more small files — eliminate (it worsens the exact problem).
4. D drops to plain Parquet, losing the ACID log and the OPTIMIZE tooling while keeping the tiny-file planning blowup — eliminate (wrong direction).
5. B fixes both halves: coalesce + a longer trigger prevent new tiny files at the source, and a scheduled OPTIMIZE compacts the backlog — least ongoing cost because prevention shrinks how often compaction must run.

Output:

Requirement	Winner
Stop making tiny files	coalesce + longer trigger
Clean up the backlog	periodic OPTIMIZE
Least ongoing ops	prevention reduces compaction frequency

Why this works — concept by concept:

• Prevent at the write, cure with compaction — the durable fix changes how files are written (coalesce, trigger interval) so compaction becomes an occasional tidy-up rather than a nightly obligation.
• More compute never fixes file count — adding executors schedules the same overhead faster; the only lever that removes overhead is fewer, larger files.
• High-cardinality repartitioning backfires — spreading writes across more directories multiplies small files, which is why the "just repartition it" instinct is a trap here.
• Cost — coalesce and trigger tuning cost nothing at runtime and shrink daily file creation ~1,000×, so the scheduled OPTIMIZE runs rarely and cheaply — the lowest total-cost design.

ETL
Topic — optimization
Layout and small-files optimization problems

Practice →

ETL Topic — etl Streaming ingestion and pipeline design problems

Practice →

---

3. Compaction & OPTIMIZE across engines

Every engine solves it the same way: read the small files, bin-pack them into large ones, then clean up the leftovers

The invariant: compaction is one idea — bin-pack many small files into few target-sized files and atomically swap them in — and each engine exposes it with different verbs: Spark uses coalesce/repartition on a rewrite, Delta uses OPTIMIZE (optionally with ZORDER), Iceberg uses the rewrite_data_files procedure, and Hive uses CONCATENATE or hive.merge.* output merging. Learn the shared mental model — bin-pack, then reclaim old files — and the per-engine syntax becomes a lookup.

Spark: coalesce vs repartition — the fundamental primitive. Every lakehouse OPTIMIZE is really a controlled Spark rewrite underneath, so you must know the two ways Spark changes partition count.

• coalesce(n) — narrow transformation; merges existing partitions without a shuffle, only ever reducing the count. Cheap, but can create skew (it collapses partitions unevenly) and cannot increase parallelism.
• repartition(n) or repartition(n, col) — wide transformation; a full shuffle that produces n evenly-sized partitions (optionally hash-partitioned by a column). More expensive, but balanced and able to increase or decrease the count.
• When to use which — reducing many partitions to few for a final write with no skew risk → coalesce; needing balanced output or partitioning by a column → repartition.

Delta Lake: OPTIMIZE and ZORDER. Delta's OPTIMIZE reads the small files in a partition and bin-packs them into files near spark.databricks.delta.optimize.maxFileSize (default ~1 GB), committing the swap atomically in the transaction log. ZORDER BY (cols) additionally multi-dimensionally clusters the data so that data-skipping (min/max per file) prunes far more files for queries filtering on those columns. VACUUM later removes the now-unreferenced small files after a retention window.

• OPTIMIZE table — bin-pack the whole table (or WHERE partition = … for one partition).
• OPTIMIZE table ZORDER BY (c1, c2) — bin-pack and cluster for skipping on c1, c2.
• VACUUM table RETAIN 168 HOURS — reclaim old files after the retention window (time-travel safety).

Iceberg: rewrite_data_files. Iceberg exposes compaction as a stored procedure with pluggable strategies: binpack (default — just combine small files to the target size) and sort/zorder (rewrite sorted/clustered for better skipping). You control the target with target-file-size-bytes and can filter to a subset with a where clause. rewrite_manifests and expire_snapshots handle the metadata-side cleanup.

Hive: CONCATENATE and output merging. For ORC/RCFile Hive tables, ALTER TABLE t [PARTITION (…)] CONCATENATE stitches small files together at the file level. For the write side, hive.merge.mapfiles, hive.merge.mapredfiles, and hive.merge.smallfiles.avgsize trigger an automatic merge step when average output file size is below a threshold.

coalesce vs repartition for a compacting rewrite — a worked teaching example

Detailed explanation. The most common hand-rolled compaction is "read the table, reduce the partition count, write it back." The choice between coalesce and repartition decides whether the rewrite is cheap-but-skewed or balanced-but-shuffled. If the input partitions are already roughly even and you only need to reduce count, coalesce avoids a shuffle and is far cheaper. If the input is skewed, or you must partition the output by a column, repartition pays for a shuffle to get evenly-sized output files. Getting this wrong shows up as either one giant straggler file (bad coalesce) or a needless full shuffle (unnecessary repartition).

• coalesce(n) — no shuffle, reduces only, risks uneven output partitions.
• repartition(n) — full shuffle, even output, can increase or decrease.
• repartition(n, col) — shuffle + hash by col, one file group per key range.
• Decision — even input + reduce-only → coalesce; skewed input or column layout → repartition.

Question. A job produces 10,000 skewed partitions (some huge, most tiny) that must be written as ~50 evenly-sized files. coalesce(50) or repartition(50)?

Input.

Fact	Value
Input partitions	10,000, heavily skewed
Desired output	~50 even files
Skew present	yes
Column layout needed	no

Code.

# coalesce(50): NO shuffle, but merges skewed partitions unevenly
df.coalesce(50).write.mode("overwrite").parquet("/out")   # risk: a few giant files + stragglers

# repartition(50): full shuffle -> 50 EVENLY sized output files
df.repartition(50).write.mode("overwrite").parquet("/out") # balanced, at the cost of a shuffle

# If output must be laid out by a column (e.g. one file group per country):
df.repartition(50, "country").write.mode("overwrite").parquet("/out")

Step-by-step trace.

1. coalesce(50) merges the existing 10,000 skewed partitions into 50 groups without shuffling — but because it just concatenates adjacent partitions, the pre-existing skew survives: a few output files are enormous, others tiny, and one straggler task dominates runtime.
2. repartition(50) performs a full shuffle that redistributes all rows across exactly 50 balanced partitions, so every output file is ~equal size.
3. The trade-off: repartition pays the network/disk cost of a shuffle; coalesce is free but cannot fix skew.
4. Because the input is skewed and you need even output, repartition(50) is correct here — the shuffle cost buys balanced, right-sized files.

Output:

Method	Shuffle?	Output balance	Verdict for skewed input
coalesce(50)	no	uneven (skew survives)	❌ stragglers
repartition(50)	yes	even	✅ balanced files

Rule of thumb. Reduce-only from even input → coalesce; whenever the input is skewed or you need a column layout, pay for the shuffle with repartition.

Delta OPTIMIZE with ZORDER — a worked teaching example

Detailed explanation. Delta's OPTIMIZE ... ZORDER BY does two jobs in one pass: it bin-packs a partition's small files into ~1 GB files (fixing the small-files problem) and it reorders rows so that data-skipping statistics (per-file min/max) tightly bound the ZORDER columns, so future queries filtering on those columns read far fewer files. You ZORDER on the high-cardinality columns queries filter on most (e.g. user_id, product_id) — not the partition column, which pruning already handles. The trade-off is that OPTIMIZE is a full rewrite of the targeted data, so you scope it with a WHERE on the partition to avoid rewriting the whole table.

• Bin-packing — merges small files toward the max-file-size target, cutting file count.
• ZORDER clustering — co-locates rows with nearby values on the ZORDER columns so min/max stats prune more files.
• Scope with WHERE — restrict OPTIMIZE to recent/affected partitions to bound the rewrite cost.
• VACUUM after — reclaim the superseded small files past the retention window.

Question. An events table partitioned by event_date has 60,000 tiny files in the last 7 days and is queried by user_id. Compact and cluster only those partitions.

Input.

Fact	Value
Partition column	event_date
Small files (last 7d)	~60,000
Frequent filter	WHERE user_id = …
Target	~256 MB–1 GB files, clustered on user_id

Code.

-- Bin-pack + cluster only the last 7 days (bounded rewrite)
OPTIMIZE events
WHERE event_date >= current_date() - INTERVAL 7 DAYS
ZORDER BY (user_id);

-- Later, reclaim the superseded small files (respect time-travel retention)
VACUUM events RETAIN 168 HOURS;

-- Verify the result
DESCRIBE DETAIL events;   -- numFiles should drop sharply for those partitions

Step-by-step trace.

1. OPTIMIZE ... WHERE event_date >= … restricts the rewrite to the 7 recent partitions, so only ~60,000 affected files are read and repacked — not the whole table.
2. Bin-packing combines those tiny files into ~1 GB files, so the 7-day partitions drop from ~60,000 files to a few dozen.
3. ZORDER BY (user_id) sorts rows so each output file covers a narrow user_id range; the min/max stored per file now let WHERE user_id = … skip files whose range excludes the value.
4. VACUUM ... RETAIN 168 HOURS later deletes the now-unreferenced small files, once the 7-day time-travel window has passed, reclaiming storage.

Output:

Stage	Files in last-7d partitions	WHERE user_id scan
Before	~60,000 tiny	reads most files
After OPTIMIZE (bin-pack)	~dozens	fewer, larger reads
After ZORDER	~dozens, clustered	data-skipping prunes most files

Rule of thumb. Scope OPTIMIZE with a partition WHERE, ZORDER on the high-cardinality columns you filter by (not the partition key), and VACUUM afterward to reclaim the old files.

Interview scenario on choosing a compaction strategy

A Delta table partitioned by dt has millions of tiny files from a streaming append, and 95% of analytical queries filter by dt and customer_id. Interactive dashboards on this table are slow. You must reduce file count and speed up the customer_id-filtered queries in a single maintenance operation.

Solution Using Delta OPTIMIZE with ZORDER on customer_id

Answer choices.

• A. VACUUM the table to delete old files.
• B. OPTIMIZE table WHERE dt >= … ZORDER BY (customer_id) to bin-pack and cluster in one pass.
• C. coalesce(1) the whole table into a single file.
• D. Add more partition columns (dt, hour, customer_id) to prune better.

Code.

Elimination:
A  VACUUM only deletes unreferenced files -> does NOT compact live small files      [reject: wrong tool]
C  coalesce(1) -> one giant file, zero parallelism, terrible for a large table       [reject: over-compacts]
D  add customer_id as a partition column -> high cardinality = MORE small files       [reject: worsens it]
B  OPTIMIZE ... ZORDER BY (customer_id) -> bin-pack + cluster in one operation         [ACCEPT]

Step-by-step trace.

1. Name the goals: fewer files (compaction) and faster customer_id filters (clustering) — ideally one operation.
2. A (VACUUM) only reclaims files already unreferenced by the log; it never merges live small files, so it does not fix the problem — eliminate.
3. C (coalesce(1)) collapses the table into a single file, destroying read parallelism and creating one unmanageable object — eliminate (over-compaction is its own problem).
4. D adds customer_id (high cardinality) as a partition column, which multiplies directories and creates more small files — eliminate.
5. B is exactly the tool: OPTIMIZE bin-packs the tiny files toward the target size and ZORDER BY (customer_id) clusters rows so data-skipping prunes files for the customer_id filter — both goals, one pass, scoped by dt.

Output:

Goal	Mechanism
Fewer files	OPTIMIZE bin-packing
Faster customer_id filter	ZORDER data-skipping
One operation	OPTIMIZE … ZORDER

Why this works — concept by concept:

• OPTIMIZE bin-packs — it reads the many small files in scope and rewrites them into target-sized files, which is the direct cure for the file-count overhead.
• ZORDER clusters for skipping — reordering rows tightens per-file min/max on customer_id, so the engine skips files whose range excludes the filter value — the query speedup the dashboards need.
• VACUUM is cleanup, not compaction — knowing that VACUUM only reclaims unreferenced files (and OPTIMIZE creates the new ones) is the exact distinction the question probes.
• Cost — scoping with WHERE dt >= … bounds the rewrite to recent partitions, so you pay a compaction cost proportional to the affected data, not the whole table, while every future query gets cheaper.

SQL
Topic — optimization
Compaction and query-optimization problems

Practice →

Transformation Topic — data-transformation Rewrite and repartitioning transformation problems

Practice →

---

4. Right file sizing & write-side prevention

The best compaction is the one you never have to run because the writes were already right-sized

The invariant: there is a file-size sweet spot — roughly 128 MB to 1 GB, with ~256 MB a good default — and the goal of write-side tuning is to land files in that band at write time so compaction becomes rare; go too small and you pay the metadata/task tax, go too large and you lose parallelism and pruning granularity and risk skew. Prevention is optimizeWrite/auto-compaction (bin-pack during the write), sane spark.sql.files.maxPartitionBytes (size the read splits), and coarse partitioning (large partitions plus clustering, not high-cardinality directories).

Why 128 MB–1 GB is the target. The band is not arbitrary; it balances two opposing costs.

• Too small (< ~64 MB) — per-file overhead dominates: metadata pressure, one task per tiny file, scheduler churn, LIST cost. This is the small-files problem.
• Too large (> ~1–2 GB) — a file becomes a single read unit that one task must process, hurting parallelism; row-group/min-max pruning is coarser (each file covers a wide value range); and a skewed giant file creates a straggler.
• The sweet spot (~128 MB–1 GB, default ~256 MB) — enough data per task to amortise overhead, small enough to parallelise and prune well. Historically this echoes the HDFS 128 MB block, and it still holds on object stores because it matches how engines split reads.

Write-side prevention knobs. These make files right-sized at the source.

• optimizeWrite (Delta / Databricks) — adds an adaptive shuffle before the write so each partition directory receives near-target-sized files instead of one-per-task slivers. autoCompact runs a small compaction right after a write if it detects too many small files.
• Iceberg write.target-file-size-bytes — the writer aims output files at this size (default 512 MB); combined with write.distribution-mode to shuffle rows sensibly before writing.
• Spark spark.sql.shuffle.partitions and AQE coalescePartitions — Adaptive Query Execution can coalesce small shuffle partitions at runtime so the final write emits fewer, larger files.
• maxRecordsPerFile — a hard cap on rows per output file to bound file size for very wide rows.

Read-side sizing: spark.sql.files.maxPartitionBytes. This controls how Spark splits files into read tasks (default 128 MB). It does not change files on disk, but it sets the read parallelism: too low and even good files get over-split into tiny tasks; too high and you under-parallelise large files. Tune it to match your file size and executor cores so each task reads a healthy chunk.

Partition granularity is a file-sizing decision. Coarser partitions mean larger files per partition; finer partitions mean smaller files. Choose the coarsest partitioning that still enables the pruning your queries need, and push finer access into clustering (ZORDER/sort) rather than more partition columns.

optimizeWrite target sizing — a worked teaching example

Detailed explanation. Without optimizeWrite, a Spark write emits one file per (output partition-directory × task), so a 200-task job writing into one date directory produces 200 files — even if the total is only a few hundred MB, that is 200 tiny files. optimizeWrite inserts an adaptive shuffle before the write so each directory instead receives a handful of near-target-sized files. It is the single highest-leverage write-side setting because it prevents small files without any change to query logic or a follow-up compaction.

• Without it — files per directory ≈ number of writing tasks.
• With it — files per directory ≈ directory_bytes / target_file_size (a few).
• autoCompact — a small automatic OPTIMIZE fires post-write if too many small files remain.
• Net effect — right-sized files at write time; compaction becomes occasional.

Question. A daily batch writes 400 MB into one date partition using 200 shuffle partitions. How many files without optimizeWrite, and with it (256 MB target)?

Input.

Fact	Value
Data written to one partition	400 MB
Shuffle/writing tasks	200
Target file size	256 MB
Setting under test	optimizeWrite on/off

Code.

# WITHOUT optimizeWrite: ~200 tiny files (one per task) for just 400 MB
(df.write.format("delta").mode("append")
   .partitionBy("event_date").save("/mnt/lake/events"))   # ~200 files, ~2 MB each

# WITH optimizeWrite + autoCompact: adaptive shuffle -> ~2 files near target
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")
spark.conf.set("spark.databricks.delta.autoCompact.enabled",  "true")
(df.write.format("delta").mode("append")
   .partitionBy("event_date").save("/mnt/lake/events"))   # ~2 files, ~200 MB each

# Table-level default so every writer benefits:
# ALTER TABLE events SET TBLPROPERTIES (delta.autoOptimize.optimizeWrite = true);

Step-by-step trace.

1. Without optimizeWrite, each of the 200 tasks writes its slice of the 400 MB into the date directory → ~200 files of ~2 MB each — a small-files problem created by write parallelism alone.
2. Enabling optimizeWrite adds an adaptive shuffle that repartitions the 400 MB toward the 256 MB target before writing → ceil(400 / 256) = 2 files.
3. autoCompact then checks the directory and, finding it already healthy, does nothing; on a directory that still had many small files it would fire a quick compaction.
4. The result is ~2 right-sized files with no separate OPTIMIZE job and no query-logic change.

Output:

Setting	Files for 400 MB	Avg file size
optimizeWrite off	~200	~2 MB
optimizeWrite on (256 MB target)	~2	~200 MB

Rule of thumb. Turn on optimizeWrite/autoCompact (or Iceberg target-file-size-bytes) as a table default so writes land near target — it removes the most common cause of small files for free.

Tuning maxPartitionBytes for read parallelism — a worked teaching example

Detailed explanation. spark.sql.files.maxPartitionBytes (default 128 MB) sets how Spark splits input files into read tasks — it is the read-side companion to file sizing and it does not rewrite anything. If your files are well-sized (say 256 MB) but maxPartitionBytes is left at 128 MB, each file is split into two tasks; if it is set too high (say 1 GB) against 256 MB files, you may under-parallelise and leave cores idle. The tuning goal is to make each read task process a healthy chunk (roughly aligned to file/row-group size) and to keep total task count near a small multiple of total executor cores.

• Too low — files over-split into many tiny tasks → scheduler overhead even on good files.
• Too high — large files under-split → fewer tasks than cores → idle executors.
• Aim — task count ≈ 2–4 × total cores; each task reads tens-to-hundreds of MB.
• Related — openCostInBytes and spark.sql.files.maxPartitionNum also shape splitting.

Question. A 256 GB table of 256 MB files is read on a 128-core cluster. What maxPartitionBytes gives good parallelism without over-splitting?

Input.

Fact	Value
Table size	256 GB
File size	256 MB (1,000 files)
Cluster cores	128
Goal	~2–4 tasks per core, no over-split

Code.

# Default 128 MB splits each 256 MB file into 2 -> 2,000 tasks (16x cores; over-split)
spark.conf.get("spark.sql.files.maxPartitionBytes")   # 134217728 (128 MB)

# Set to 256 MB so one task reads one file -> 1,000 tasks (~8x cores; healthy)
spark.conf.set("spark.sql.files.maxPartitionBytes", str(256 * 1024 * 1024))

# Read now schedules ~1,000 tasks over 128 cores -> ~8 waves, well-utilised
df = spark.read.format("delta").load("/mnt/lake/events")

Step-by-step trace.

1. At the default 128 MB, each 256 MB file is split into 2 read tasks → 1,000 × 2 = 2,000 tasks over 128 cores (~16 waves) — more scheduling than the data warrants.
2. Setting maxPartitionBytes = 256 MB makes one task read one whole 256 MB file → 1,000 tasks over 128 cores (~8 waves) — each task does meaningful work.
3. Pushing it to 1 GB would group ~4 files per task → 250 tasks over 128 cores (~2 waves) — risks idle cores and coarse retries.
4. 256 MB balances the two: enough tasks to keep all cores busy, each reading a full right-sized file.

Output:

maxPartitionBytes	Tasks	Waves over 128 cores	Verdict
128 MB (default)	2,000	~16	over-split
256 MB	1,000	~8	healthy
1 GB	250	~2	under-parallelised

Rule of thumb. Align maxPartitionBytes with your on-disk file size and target ~2–4 read tasks per core — it tunes read parallelism, but it never fixes small files on disk (only right-sizing/compaction does).

Interview scenario on preventing small files at write time

A daily Spark batch job writes a 300 GB partitioned Delta table but produces ~500,000 files because each of 200 shuffle partitions writes into ~2,500 date/hour directories. Queries are slow and storage LIST costs are climbing. You must right-size the writes with minimal code and no nightly compaction job.

Solution Using optimizeWrite plus coarser partitioning

Answer choices.

• A. Keep the layout and run a nightly OPTIMIZE to clean up the 500,000 files.
• B. Enable optimizeWrite/autoCompact and coarsen partitioning from date/hour to date, pushing hour into a clustering column.
• C. Increase spark.sql.shuffle.partitions to 1,000 for more parallelism.
• D. Set spark.sql.files.maxPartitionBytes to 16 MB.

Code.

Elimination:
A  nightly OPTIMIZE  -> cures but never prevents; burns compute every night forever   [reject: symptom]
C  more shuffle parts -> MORE writers per directory = MORE small files                 [reject: worsens it]
D  maxPartitionBytes=16MB -> read-side split size; over-splits, doesn't change writes   [reject: wrong knob]
B  optimizeWrite + coarser partition (date, cluster hour)                               [ACCEPT]

Step-by-step trace.

1. Name the cause: 200 writers × ~2,500 fine-grained date/hour directories = up to 500,000 files — over-partitioning multiplied by write parallelism.
2. A cures with a nightly OPTIMIZE but never prevents; you pay compaction compute every night indefinitely — eliminate (it treats the symptom).
3. C raises shuffle partitions to 1,000, putting more writers into each directory and creating more small files — eliminate (worsens it).
4. D sets a read-side split size (maxPartitionBytes) that over-splits reads and does nothing to how files are written — eliminate (wrong knob).
5. B fixes both drivers: optimizeWrite adds an adaptive shuffle so each directory gets a few target-sized files, and coarsening to date (with hour pushed into ZORDER/clustering) cuts directory count ~24×, so writes land right-sized with no nightly job.

Output:

Requirement	Winner
Fewer writers per dir	optimizeWrite adaptive shuffle
Fewer directories	coarsen date/hour → date
No nightly compaction	prevention at write time

Why this works — concept by concept:

• optimizeWrite bin-packs at the source — an adaptive pre-write shuffle turns "one file per task per directory" into "a few target-sized files per directory," removing the parallelism-driven cause.
• Coarser partitions enlarge files — dropping hour collapses 24× the directories, so each remaining date directory holds enough data to fill target-sized files; hour access moves to clustering.
• Right knob for the right axis — maxPartitionBytes sizes reads, shuffle.partitions sizes shuffles; neither prevents small files the way write-side bin-packing and partition granularity do.
• Cost — prevention removes the nightly OPTIMIZE entirely, so you stop paying recurring compaction compute and cut LIST/metadata cost from day one — the lowest total-cost outcome.

ETL
Topic — optimization
Write-side file-sizing and partitioning problems

Practice →

ETL Topic — etl Batch pipeline and partition-design problems

Practice →

---

5. Engine-specific playbooks & interview signals

Delta, Iceberg, Hudi, and warehouses all compact then clean up — name the lifecycle, not just the verb

The invariant: each table format solves small files with the same two-phase lifecycle — compact the data files, then reclaim the superseded files and prune metadata — but they name the verbs differently, and interviews reward you for naming both the cause and the full lifecycle, not just blurting "run OPTIMIZE." Below is the per-engine playbook plus the signals that tell an interviewer you have operated these systems, not just read about them.

Delta Lake playbook. Compact with OPTIMIZE (optionally ZORDER), prevent with optimizeWrite/autoCompact, then VACUUM to reclaim.

• Compact — OPTIMIZE t [WHERE …] [ZORDER BY (…)] bin-packs to ~1 GB and optionally clusters.
• Prevent — table properties delta.autoOptimize.optimizeWrite and autoCompact.
• Reclaim — VACUUM t RETAIN n HOURS deletes files unreferenced past the retention window (keep ≥ your time-travel need).
• Gotcha — OPTIMIZE does not delete old files; VACUUM does, and only after retention.

Iceberg playbook. Compact with rewrite_data_files, tidy metadata with rewrite_manifests, and reclaim with expire_snapshots + remove_orphan_files.

• Compact — CALL catalog.system.rewrite_data_files(table => 'db.t', strategy => 'binpack' | 'sort', options => map('target-file-size-bytes','536870912')).
• Metadata — rewrite_manifests combines many small manifest files (manifests are their own small-files problem).
• Reclaim — expire_snapshots drops old snapshots so their data files can be deleted; remove_orphan_files sweeps files no snapshot references.
• Prevent — write.target-file-size-bytes and write.distribution-mode on the table.

Hudi playbook. Choose the table type, then compaction follows from it.

• Copy-on-write (CoW) — every write rewrites whole files; reads are fast, writes amplify. Small files controlled by hoodie.parquet.small.file.limit (auto bin-packs small files on write).
• Merge-on-read (MoR) — writes append small delta/log files; a compaction merges base + logs periodically. Choose inline (synchronous, simpler) or async (non-blocking ingestion) compaction.
• Clustering — Hudi clustering reorganises files for size and layout without changing the record key.
• Prevent — hoodie.parquet.max.file.size and the small-file limit target file sizing at write time.

Warehouse playbook (managed — mostly automatic).

• Snowflake — micro-partitions are managed for you; you cannot hand-compact, but auto-clustering re-sorts data for pruning, and loading larger files (100–250 MB compressed) via COPY is the write-side best practice.
• BigQuery — fully managed storage; there is no user-visible small-files problem for native tables (Google manages the columnar blocks), though streaming inserts and many tiny load jobs can still create short-term fragmentation that background optimisation resolves.
• Redshift — VACUUM (sort/reclaim) and ANALYZE keep tables healthy; loading with evenly-sized files across slices avoids skew.

Interview signals — how to sound like you have operated this.

• You diagnose before you fix — "let me check numFiles vs sizeInBytes and which partition holds the tiny files" beats "run OPTIMIZE."
• You name the cause — streaming cadence, over-partitioning, parallelism, or MERGE churn — and fix the write, not just the symptom.
• You know compaction ≠ cleanup — OPTIMIZE/rewrite_data_files create new files; VACUUM/expire_snapshots remove the old ones, and only after a retention window (protecting time travel).
• You quote the target band — 128 MB–1 GB, ~256 MB default — and can justify both bounds.

Iceberg rewrite_data_files — a worked teaching example

Detailed explanation. Iceberg exposes compaction as the rewrite_data_files stored procedure with two main strategies: binpack (combine small files up to the target size, cheapest) and sort/zorder (rewrite sorted or clustered for better data-skipping, more expensive). You bound the work with a where filter and set the output size via target-file-size-bytes. Crucially, rewrite_data_files compacts data; you still run rewrite_manifests for the metadata layer and expire_snapshots to actually free the old files — the two-phase lifecycle.

• binpack — merge small files to target size, no reordering (fast).
• sort / zorder — rewrite with a sort/cluster order for pruning (slower, better reads).
• target-file-size-bytes — the output size the rewriter aims for (e.g. 512 MB).
• where — restrict the rewrite to a partition/predicate to bound cost.

Question. An Iceberg table db.events has millions of small files in the dt = '2026-08-14' partition. Compact just that partition to 512 MB files and then free the old files.

Input.

Fact	Value
Table	db.events
Hot partition	dt = '2026-08-14'
Strategy	binpack
Target file size	512 MB (536870912 bytes)

Code.

-- 1) Compact only the hot partition to 512 MB files
CALL catalog.system.rewrite_data_files(
  table    => 'db.events',
  strategy => 'binpack',
  where    => "dt = '2026-08-14'",
  options  => map('target-file-size-bytes','536870912')
);

-- 2) Combine small manifest files (metadata-side small files)
CALL catalog.system.rewrite_manifests('db.events');

-- 3) Expire old snapshots so the superseded data files can be deleted
CALL catalog.system.expire_snapshots(
  table => 'db.events', older_than => TIMESTAMP '2026-08-07 00:00:00'
);

Step-by-step trace.

1. rewrite_data_files(... where => "dt = '2026-08-14'") reads only that partition's small files and bin-packs them into 512 MB files, committing a new snapshot — the old files are now unreferenced by the new snapshot but still retained for time travel.
2. rewrite_manifests collapses the many small manifest files into fewer, so metadata reads (planning) speed up too.
3. expire_snapshots(older_than => …) drops snapshots older than the retention point, which makes the superseded small data files eligible for deletion and actually frees the storage.
4. The result: the hot partition holds a handful of 512 MB files, manifests are compact, and the old files are reclaimed — the full lifecycle, not just the compaction step.

Output:

Step	Effect
rewrite_data_files	millions of small files → few 512 MB files
rewrite_manifests	many small manifests → few
expire_snapshots	old files freed, storage reclaimed

Rule of thumb. In Iceberg, compaction is three procedures, not one: rewrite_data_files (data), rewrite_manifests (metadata), expire_snapshots/remove_orphan_files (reclaim) — name all three in an interview.

The interview-signal checklist — a worked teaching example

Detailed explanation. Small-files questions in system-design and Spark interviews are really testing whether you operate lakehouses or only read about them. The tell is a structured answer: diagnose (measure file count and locate the hot partition), attribute (name which of the four causes created it), fix (compaction now + write-side prevention), and clean up (reclaim old files, respecting time travel). Candidates who jump straight to "run OPTIMIZE" score lower than those who walk the lifecycle and quote the target size band. This example turns that into a repeatable script you can deliver under pressure.

• Diagnose — sizeInBytes / numFiles; which partition holds the tiny files?
• Attribute — streaming cadence / over-partition / parallelism / MERGE churn?
• Fix — compact (OPTIMIZE / rewrite_data_files) and prevent (optimizeWrite / trigger / coarser partitions).
• Clean up — VACUUM / expire_snapshots after retention; mention time-travel safety.

Question. Given "our Delta dashboard table has millions of tiny files and slow queries," what does a senior answer include that a junior one omits?

Input.

Answer element	Junior	Senior
Diagnose file count/partition	often skipped	always first
Name the write-side cause	rarely	explicitly
Compaction command	yes	yes, scoped
Write-side prevention	rarely	always
Reclaim + time-travel note	rarely	always

Code.

# The senior script, in order:
1. DIAGNOSE : DESCRIBE DETAIL -> numFiles/sizeInBytes; find the hot partition
2. ATTRIBUTE: streaming every 10s? over-partitioned by user_id? 200-way shuffle? MERGE churn?
3. FIX-NOW  : OPTIMIZE t WHERE <hot partition> ZORDER BY <filter col>   (scoped)
4. PREVENT  : enable optimizeWrite/autoCompact; coarsen partitions; lengthen trigger
5. RECLAIM  : VACUUM t RETAIN 168 HOURS   (keep >= time-travel window)

Step-by-step trace.

1. The senior answer measures first — it computes the average file size and finds which partition is the offender, so the fix is scoped, not table-wide.
2. It attributes the cause to a specific write pattern, which is what makes the prevention step credible rather than generic.
3. It scopes the compaction (WHERE a partition) and clusters on the actual filter column, showing cost-awareness.
4. It closes the loop with prevention (so the problem does not recur) and reclamation (with an explicit time-travel/retention caveat) — the two steps juniors usually omit.

Output:

Dimension	Junior answer	Senior answer
Structure	"run OPTIMIZE"	diagnose → attribute → fix → prevent → reclaim
Cost-awareness	table-wide	scoped to hot partition
Recurrence	ignored	prevented at write
Safety	ignored	retention/time-travel noted

Rule of thumb. In an interview, walk the full lifecycle — diagnose, attribute, compact, prevent, reclaim — and quote the 128 MB–1 GB target; that is what separates "operated it" from "read about it."

Interview scenario on a high-ingestion Iceberg table

An Iceberg table ingests high-volume streaming upserts and is queried interactively. Merge-on-read keeps ingestion fast but leaves growing delete/log files, so reads are slowing and old snapshots are inflating storage. You must keep ingestion non-blocking while restoring read speed and reclaiming space.

Solution Using async compaction plus expire_snapshots

Answer choices.

• A. Switch to copy-on-write so every write is fully compacted inline.
• B. Run async rewrite_data_files to compact base+delta files off the ingestion path, plus expire_snapshots to reclaim old files, keeping MoR ingestion non-blocking.
• C. Stop compacting and just add read replicas.
• D. remove_orphan_files on every write to keep storage low.

Code.

Elimination:
A  CoW inline compaction -> fully compacts but amplifies write cost; blocks fast ingest [reject: hurts ingestion]
C  read replicas         -> replicate the small-files slowness; never compact           [reject: no fix]
D  remove_orphan_files per write -> expensive full-scan cleanup, not compaction          [reject: wrong tool/cadence]
B  async rewrite_data_files (compact off-path) + expire_snapshots (reclaim)               [ACCEPT]

Step-by-step trace.

1. Name the constraint: "keep ingestion non-blocking" rules out any synchronous/inline compaction that competes with the write path.
2. A (copy-on-write) fully compacts inline but amplifies write cost and slows the fast ingestion the scenario requires — eliminate.
3. C adds read replicas, which just copy the small-files slowness to more nodes and never compact — eliminate (no fix).
4. D runs remove_orphan_files (a full-scan orphan sweep) on every write — expensive and it is a cleanup tool, not a compactor — eliminate.
5. B keeps MoR for fast non-blocking ingestion, runs rewrite_data_files asynchronously to merge base+delta files off the critical path, and expire_snapshots to reclaim old snapshots — read speed restored, ingestion untouched, storage freed.

Output:

Requirement	Mechanism
Non-blocking ingestion	keep MoR; compact async
Restore read speed	rewrite_data_files (base+delta merge)
Reclaim space	expire_snapshots

Why this works — concept by concept:

• MoR + async compaction — merge-on-read keeps writes cheap by deferring the merge; running compaction asynchronously restores read speed without ever blocking the ingestion path.
• Compaction vs reclamation are separate — rewrite_data_files builds the new compacted files; expire_snapshots frees the old ones — you need both, and knowing the split is the interview signal.
• CoW is the wrong trade here — copy-on-write optimises reads at the cost of write amplification, which directly violates the "fast, non-blocking ingestion" constraint.
• Cost — async compaction spends compute off the critical path and expire_snapshots reclaims storage, so you pay a controlled background cost instead of taxing every ingest write.

SQL
Topic — optimization
Table-maintenance and compaction optimization problems

Practice →

Course
Course — Spark internals
Apache Spark internals for data engineering interviews

Practice →

---

Cheat sheet — the small-files playbook

Symptom → cause → fix (memorise this table).

Symptom	Likely cause	Fix
Files pile up in the newest time partition on a cadence	streaming micro-batches	coalesce per batch + longer trigger + periodic OPTIMIZE
Thousands of directories with 1–2 tiny files each	over-partitioning (high-cardinality key)	coarsen partition (date) + ZORDER/cluster the key
One partition holds exactly shuffle.partitions files	high write parallelism	optimizeWrite / AQE coalesce / repartition before write
A partition's file count grows with no new volume	MERGE / UPSERT churn	scheduled compaction; MoR + async compaction
Slow planning, huge task count, rising LIST cost	too many files table-wide	compact (OPTIMIZE / rewrite_data_files) + reclaim
A few multi-GB files + one straggler task	over-compaction / skew	repartition for balance; lower max file size

Target file size & config per engine.

Engine	Compact	Prevent (write-side)	Reclaim
Spark (raw Parquet)	repartition/coalesce + rewrite	AQE coalescePartitions, maxRecordsPerFile	overwrite
Delta Lake	OPTIMIZE [ZORDER] (~1 GB)	optimizeWrite, autoCompact	VACUUM RETAIN n HOURS
Iceberg	rewrite_data_files (target-file-size-bytes, default 512 MB)	write.target-file-size-bytes, distribution-mode	expire_snapshots, remove_orphan_files
Hudi	MoR compaction (inline/async), clustering	parquet.max.file.size, small.file.limit	cleaner (retain commits)
Snowflake	auto (micro-partitions) + auto-clustering	load 100–250 MB files via COPY	managed
BigQuery	managed (no user compaction)	avoid many tiny load/stream jobs	managed

coalesce vs repartition vs OPTIMIZE decision line. Reduce partition count from even input with no shuffle → coalesce. Need balanced output or a column layout → repartition (pays a shuffle). Managed table with a transaction log and you want bin-pack + optional clustering + atomic swap → OPTIMIZE (Delta) / rewrite_data_files (Iceberg).

The two-phase compaction lifecycle (never forget phase two). Phase 1 — compact data files (OPTIMIZE / rewrite_data_files) creates new right-sized files. Phase 2 — reclaim old files (VACUUM / expire_snapshots) after the retention window, which protects time travel. Compaction alone does not free storage.

Target-size one-liners. Aim for 128 MB–1 GB, default ~256 MB. Below ~64 MB you pay the small-files tax; above ~1–2 GB you lose parallelism and pruning. total_bytes / target_size = how many output files to target.

---

Frequently asked questions

What is the small files problem in big data?

The small files problem is when a table is stored as a very large number of tiny files (kilobytes to a few megabytes) instead of fewer, larger ones, so the engine's cost is dominated by per-file overhead — catalog/NameNode metadata, one scheduling task per file, slow object-store LIST calls, and query-planning footer reads — rather than by the actual data volume. Two tables holding identical bytes perform very differently if one has 800 files and the other 800,000, because distributed engines pay a fixed price per file. The fix is always fewer, larger files via compaction and write-side sizing, never a bigger cluster.

What is a good target Parquet file size?

The widely-used sweet spot is roughly 128 MB to 1 GB, with ~256 MB a safe default. Below ~64 MB, per-file metadata and task overhead dominate; above ~1–2 GB you lose read parallelism (one big file is one read unit) and pruning granularity, and risk straggler skew. Delta's OPTIMIZE targets ~1 GB, Iceberg's writer defaults to 512 MB — both live comfortably inside that band, and you compute the number of files to aim for as total_bytes / target_size.

Does OPTIMIZE / compaction delete the old small files?

No — and this catches many people. OPTIMIZE (Delta) and rewrite_data_files (Iceberg) create new compacted files and mark the old ones as superseded, but the small files remain on storage until a separate reclamation step runs: VACUUM in Delta or expire_snapshots/remove_orphan_files in Iceberg, and only after a retention window that protects time travel. So compaction is a two-phase lifecycle: compact, then reclaim. If your storage does not shrink after OPTIMIZE, you have not run the reclaim phase yet.

coalesce vs repartition — which fixes small files?

Both reduce partition (and therefore output-file) count, but differently. coalesce(n) merges partitions with no shuffle and can only reduce the count — cheap, but it preserves any input skew, so you can end up with uneven files. repartition(n) does a full shuffle to produce n evenly-sized partitions (and can increase or decrease the count). Use coalesce when the input is already balanced and you just need fewer files for a final write; use repartition when the input is skewed or you need a specific column layout. For managed tables, prefer the engine's OPTIMIZE/rewrite_data_files, which bin-packs and swaps atomically.

How do I stop streaming jobs from creating tiny files?

Attack it at the write, not with endless compaction. coalesce each micro-batch to a small number of writers (via foreachBatch), lengthen the trigger interval to the largest your latency SLA allows so each batch carries more data, and enable optimizeWrite/autoCompact (Delta) or target-file-size-bytes (Iceberg) so writes land near target. Then schedule an occasional OPTIMIZE/rewrite_data_files to mop up the remainder — prevention shrinks how often that must run and cuts the recurring spark small files cost.

Do Snowflake / BigQuery have a small files problem?

Mostly no, because storage is managed for you. Snowflake organises data into micro-partitions automatically and offers auto-clustering for pruning — you can't hand-compact, but you should still load reasonably large files (100–250 MB) rather than many tiny ones. BigQuery manages its columnar storage entirely, so native tables have no user-visible small-files problem, though a flood of tiny streaming inserts or micro load jobs can cause short-term fragmentation that background optimisation resolves. The small-files problem is primarily a self-managed lake/lakehouse concern (Spark/Delta/Iceberg/Hudi/Hive on object storage).

---

Practice on PipeCode

Turn the small-files playbook into muscle memory

Reading about compaction is not the same as reasoning about it under interview pressure. PipeCode drills build the reflex the question actually tests — diagnosing file count, naming the write-side cause, choosing OPTIMIZE vs repartition, and defending the file-size and cost trade-offs. Pipecode.ai is Leetcode for Data Engineering — scenario-first practice on SQL, ETL, and layout optimization tuned to the trade-offs real lakehouse work rewards.

Practice optimization problems →
Practice ETL problems →