Small Files Problem in Delta Lake: Detection and Compaction Strategy

>GLB_ // tools · Delta Lake

A Delta table that ingests every five minutes looks fine on day one. By day ninety, a SELECT count(*) that used to take 4 seconds takes 90, and nothing in the query changed. The data volume barely grew. The file count did.

This post covers how to measure the problem from the transaction log, why it hurts, and a compaction strategy that doesn’t fight your streaming jobs.

Why small files hurt

Every Parquet file carries fixed costs that don’t scale with its size:

• Object store requests. Each file is at least one GET (usually more: footer, then column chunks). 10,000 files of 2 MB cost far more in latency and requests than 20 files of 1 GB.
• Task scheduling. Spark packs files into input splits, but per-file overhead (opening, reading the footer, building the schema) still adds up.
• Transaction log growth. Every file is an add action in _delta_log. More files → bigger log → slower snapshot reconstruction and slower planning.
• Weaker data skipping. Min/max stats per file are less useful when each file covers a tiny, overlapping slice of the data.

The usual causes:

1. Streaming / micro-batch ingestion with short triggers.
2. High-cardinality partitioning (one file per partition per batch).
3. MERGE / UPDATE / DELETE rewriting only the touched rows into new small files.
4. Too many shuffle partitions on small writes (spark.sql.shuffle.partitions = 200 writing 50 MB).

Step 1: Detect it

Quick check

DESCRIBE DETAIL my_catalog.sales.events;

numFiles and sizeInBytes give you the average file size. Anything averaging below ~64–128 MB on a table larger than a few GB is a candidate.

Real distribution from the transaction log

The average lies. A table with 20 files of 1 GB and 5,000 files of 1 MB averages ~5 MB, but the problem is concentrated. Get the distribution from the active snapshot instead of listing storage (storage also contains files already removed from the table but not yet vacuumed). With delta-rs you can read the add actions of the current snapshot without Spark and without scanning data:

from deltalake import DeltaTable
import pyarrow.compute as pc

dt = DeltaTable("s3://lake/sales/events")
adds = dt.get_add_actions(flatten=True)  # one row per active file

sizes_mb = pc.divide(adds["size_bytes"], 1024 * 1024)
print("files:", len(adds))
print("p50 MB:", pc.quantile(sizes_mb, 0.5)[0])
print("p90 MB:", pc.quantile(sizes_mb, 0.9)[0])
print("files < 32 MB:", pc.sum(pc.less(sizes_mb, 32)).as_py())

If the table is partitioned, group by the partition columns (partition.<col> in the flattened output) to find which partitions are fragmented. Usually it’s the recent ones still receiving writes.

Signals to track over time

Thresholds depend on the engine and workload; treat these as starting points, not rules.

Step 2: Compact with OPTIMIZE

OPTIMIZE bin-packs small files into larger ones and commits the change as a new table version:

-- Whole table
OPTIMIZE my_catalog.sales.events;

-- Only recent partitions (cheaper, and avoids rewriting cold data)
OPTIMIZE my_catalog.sales.events
WHERE event_date >= current_date() - INTERVAL 7 DAYS;

Python API (Delta Lake OSS):

from delta.tables import DeltaTable

path = "s3://lake/sales/events"
(DeltaTable.forPath(spark, path)
    .optimize()
    .where("event_date >= '2026-10-01'")
    .executeCompaction())

Key properties:

• Idempotent. Re-running on already-compacted files does nothing.
• dataChange = false. The commit is marked as a rearrangement, so streaming readers of the table don’t reprocess the data.
• Old files are not deleted. They’re removed from the snapshot but remain in storage until VACUUM (default retention: 7 days). Compaction alone doesn’t reduce your storage bill short-term; it can temporarily increase it.
• Target size. Controlled by spark.databricks.delta.optimize.maxFileSize (default 1 GB).

With delta-rs, outside Spark:

DeltaTable(path).optimize.compact(target_size=512 * 1024 * 1024)

Step 3: Prevent it at write time

Compaction is the cleanup. These reduce how much cleanup you need:

ALTER TABLE my_catalog.sales.events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'  = 'true'
);

• Optimized writes add a shuffle before the write so each partition is written by fewer tasks → fewer, larger files. Costs a shuffle; worth it for partitioned tables and streaming.
• Auto compaction runs a small, synchronous compaction after a write when a partition has accumulated enough small files. It targets smaller files than OPTIMIZE and adds latency to the writing job.

Availability of these properties depends on your Delta version and platform. Check the docs for your runtime before relying on them.

Other levers:

• Lengthen streaming triggers when latency allows (trigger(processingTime="5 minutes") vs. every few seconds).
• repartition() / coalesce() before small batch writes.
• Revisit partitioning (see the partitioning post in this series). Over-partitioning is the most common root cause I find.

A compaction strategy that works

1. Write side: optimized writes on; streaming triggers as long as the SLA allows.
2. Hot partitions: scheduled OPTIMIZE ... WHERE over the last N days, e.g. hourly or daily depending on ingestion rate.
3. Cold data: compact once after the partition stops receiving writes. Don’t keep re-optimizing it.
4. Storage: VACUUM on a schedule, respecting the retention window required by time travel and your longest-running readers.
5. Measure: log p50/p90 file size per table after each run. If p50 keeps dropping between runs, the write side is the problem, not the schedule.

On newer Delta versions, liquid clustering replaces partitioning + Z-order for many tables and makes OPTIMIZE incremental. Worth evaluating before designing a new partition scheme.

Concurrency note

OPTIMIZE uses optimistic concurrency. It can conflict with concurrent UPDATE/DELETE/MERGE that touch the same files. Append-only writers don’t conflict with it. If you run frequent MERGEs, schedule compaction in windows where they don’t overlap the same partitions.

References

• Delta Lake — Optimizations (OPTIMIZE, Z-order): https://docs.delta.io/latest/optimizations-oss.html
• Delta Lake — Table utility commands (DESCRIBE DETAIL, VACUUM): https://docs.delta.io/latest/delta-utility.html
• Delta Lake — Concurrency control: https://docs.delta.io/latest/concurrency-control.html
• Delta Lake transaction log protocol (add/remove actions, dataChange): https://github.com/delta-io/delta/blob/master/PROTOCOL.md
• delta-rs — DeltaTable.optimize.compact and get_add_actions: https://delta-io.github.io/delta-rs/
• Databricks — Auto optimize / optimized writes: https://docs.databricks.com/en/delta/tune-file-size.html
• Databricks — Liquid clustering: https://docs.databricks.com/en/delta/clustering.html