Auto
compaction.

What it is

Auto compaction is Delta tidying up immediately after a write. The moment a write commits, the same job — on the same cluster — checks whether a partition (or the table) now holds too many small files, and if so merges those small files into fewer, right-sized ones before the job ends. It runs synchronously on the write cluster, post-commit, and only ever touches files that haven't been compacted before, so it never re-chews work it already did.

The contrast with the previous lesson is the whole point. Optimized writes (Lesson 05) acts before the commit — it shuffles data so the write emits fewer, larger files. Auto compaction acts after the commit — it merges the small files that still landed on disk. They're complementary, and together they make up the "auto optimize" umbrella (Lesson 07).

Analogy
Auto compaction is the busboy clearing and consolidating tables right after each seating — the meal (write) finishes, then in the same shift the small plates get stacked into a few trays so the next service starts clean. (Optimized writes is the host seating fewer, fuller tables to begin with.)

Real-world use case
A streaming / MERGE pipeline micro-batching into a bronze table every minute. Each batch commits a few tiny files; left alone they pile up into thousands. With delta.autoOptimize.autoCompact = auto, each write's tail merges the new small files into ~128 MB+ chunks — so the table stays query-fast without a separate nightly OPTIMIZE job carrying all the load.

Why it matters

• Streaming and MERGE create small files continuously. Every micro-batch or merge commit adds a few files. Auto compaction is the automatic, per-write cleanup that keeps the count under control between (or instead of) manual OPTIMIZE runs.
• It runs where the data already is. Because it executes on the write cluster right after the commit, the data is hot and the cluster is already up — no separate job, no separate cluster spin-up.
• It's safe by construction. It only compacts files not previously compacted, so re-running pipelines doesn't re-compact the same data; and it commits as a normal Delta transaction, so readers and streams see consistent snapshots throughout.

See it work — write → commit → auto-compaction timeline

Step through a write on both sides at once. Optimized writes (left) shuffles before the commit so fewer, larger files are written. Auto compaction (right) lets the small files commit, then merges them after the commit — synchronously on the same cluster. Click a step, or use Next.

Simplified illustration of the concept. Real behavior depends on data volume, partition layout, and the configured target size; both features can be on together.

See it work — the minNumFiles threshold

Auto compaction doesn't fire on every tiny write — it waits until at least minNumFiles small files have accumulated in a partition. Click Append a batch to add small files; when the count crosses the threshold, the post-commit compaction pass fires and merges them into one right-sized file.

Simplified illustration. Real triggering also weighs file sizes and the target size; minNumFiles is the minimum small-file count that makes a compaction pass worthwhile.

How it works — sub-topic by sub-topic

1 · What it does, and exactly when it runs

Auto compaction combines small files within partitions after a write succeeds. It runs synchronously on the write cluster, post-commit: the write transaction commits first, then the same job performs a compaction pass and commits that as a second transaction before finishing. The trade-off is that it adds time to the tail of the write job — for latency-critical micro-batches that's a real cost; for most pipelines the steady file hygiene is worth it.

SQL-- Turn on auto compaction at the TABLE level (applies to all writers of this table).
-- 'auto' autotunes the target file size (recommended).
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'auto');

PySpark# Auto compaction is a TABLE PROPERTY, so any job that writes this table
# runs the post-write compaction pass.
spark.sql("""
  ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'auto')
""")

2 · "Only compacts files not previously compacted"

Each pass considers only files that haven't already been compacted by auto compaction. Once a set of small files is merged into a right-sized file, that output won't be re-merged on the next write. This makes auto compaction incremental and self-limiting — leaving it on permanently doesn't cause the cluster to keep rewriting the same data; it targets only the new small files. It's a local cleanup, not a global re-layout (that's OPTIMIZE / liquid clustering).

3 · The values: auto, true, legacy, false

• auto — recommended. On, and autotunes the target file size.
• true — on, with a fixed 128 MB target, no dynamic sizing.
• legacy — an alias for true (same fixed 128 MB behavior).
• false — off.

auto lets Delta grow the target as the table grows instead of pinning every table to 128 MB; true/legacy give the older fixed behavior. Prefer auto for new work.

SQL-- recommended: on + autotuned target
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'auto');

-- 'true' = on, FIXED 128 MB target · 'legacy' = alias for 'true' · 'false' = off
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'false');  -- opt this table OUT

4 · Table property vs session config

Two control surfaces: the table property delta.autoOptimize.autoCompact travels with the table (every writer gets the same behavior); the session config spark.databricks.delta.autoCompact.enabled is set on the cluster/session and affects writes from that session. Make the table property the source of truth for production tables; use the session config deliberately. Mixing both is the classic "why is compaction running?" trap.

SQL-- SESSION-level switch (affects writes from THIS session/cluster).
-- Same value vocabulary: auto / true / legacy / false.
SET spark.databricks.delta.autoCompact.enabled = auto;

PySpark# Session/cluster scope (not per-table).
spark.conf.set("spark.databricks.delta.autoCompact.enabled", "auto")

5 · Tuning knobs: maxFileSize and minNumFiles

spark.databricks.delta.autoCompact.maxFileSize is the target/cap for the compacted output size; spark.databricks.delta.autoCompact.minNumFiles is the minimum number of small files in a partition (or the table) needed to trigger a pass. minNumFiles too low → compaction fires constantly (latency on nearly every write); too high → small files linger. Tune to your commit cadence (see the threshold simulator above).

SQL-- Don't compact until at least this many small files exist (avoid firing on tiny writes).
SET spark.databricks.delta.autoCompact.minNumFiles = 50;

-- Steer the target size of the compacted output files (here ~128 MB in bytes).
SET spark.databricks.delta.autoCompact.maxFileSize = 134217728;

6 · Always on for MERGE / UPDATE / DELETE

Auto compaction and optimized writes are always on for MERGE, UPDATE, and DELETE — and this cannot be disabled. Those operations rewrite affected files and are prime small-file producers, so the file-hygiene features are forced on as a guardrail. You can't opt a MERGE-heavy job out of the post-write compaction cost, even for ultra-low latency.

SQL-- Auto compaction + optimized writes are ALWAYS ON for MERGE/UPDATE/DELETE
-- (can't be disabled) — the small files this MERGE creates get swept up.
MERGE INTO main.delta_opt_demo.events AS t
USING staged_updates AS s
  ON t.event_id = s.event_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

7 · Background auto compaction for UC managed tables

Managed = self-tidying No PO required

For Unity Catalog managed tables, a background auto compaction mode is available that does not require predictive optimization to run. It gives managed tables ongoing file hygiene even where predictive optimization isn't enabled, compacting in the background rather than only on the synchronous write path. It's distinct from both the write-cluster (synchronous) compaction and from predictive optimization.

8 · Auto compaction and predictive optimization are INDEPENDENT

Auto compaction runs synchronously on the write cluster; predictive optimization (Lesson 09) runs asynchronously on serverless compute. They're independent — use either alone or both together. Choose based on where you want the cost to land: on your write cluster (auto compaction, immediate and local) or on serverless, off the write path (predictive optimization).

9 · It does NOT replace OPTIMIZE on big tables

Auto compaction (like optimized writes) reduces the small-file problem but does not replace a periodic OPTIMIZE. For tables > 1 TB you still schedule OPTIMIZE (or let predictive optimization run it) to further consolidate, and use liquid clustering for query-key skipping. Treat auto compaction as "keeps the table from degrading between OPTIMIZE runs," not "I never need OPTIMIZE." (Lessons 04 / 07 / 09.)

10 · Migrating off the legacy config

To move from the older fixed (legacy/true) behavior to the modern default, remove the session config and unset the table property so the table falls back to the platform default. Do it deliberately — unsetting changes behavior for all writers — and re-verify with SHOW TBLPROPERTIES.

SQL-- (1) clear the session config, then (2) unset the table property.
SET spark.databricks.delta.autoCompact.enabled = false;  -- or remove from cluster config
ALTER TABLE main.delta_opt_demo.events
  UNSET TBLPROPERTIES (delta.autoOptimize.autoCompact);

-- Confirm the property is gone:
SHOW TBLPROPERTIES main.delta_opt_demo.events;

Optimized writes vs auto compaction

Both are the two halves of "auto optimize" (Lesson 07): optimized writes reduces how many small files appear; auto compaction merges the ones that still do.

Uses, edge cases & limitations

Uses

• Streaming / micro-batch ingestion and MERGE-heavy pipelines that commit small files often — set delta.autoOptimize.autoCompact = auto.
• Pair with optimized writes on high-churn tables to attack small files from both sides (pre-write sizing + post-write cleanup).
• UC managed tables can also lean on background auto compaction for ongoing hygiene without predictive optimization.

Edge cases an interviewer probes

• Latency-critical micro-batches — auto compaction is synchronous, so it lengthens the write job's tail; move maintenance off the write path (predictive optimization) and raise minNumFiles if you need minimal batch latency.
• minNumFiles too low → fires on nearly every write; too high → small files linger. Tune to commit cadence.
• MERGE/UPDATE/DELETE → always on, can't be disabled.
• Big tables (> 1 TB) → still run OPTIMIZE; use liquid clustering for skipping.
• Mixed table property + session config → confusing behavior; make the table property authoritative.

Limitations

• It's a local, per-write sweep — no global re-layout, no clustering by query keys; does not replace OPTIMIZE.
• Only compacts files not previously compacted — won't re-balance already-compacted regions.
• Runs synchronously on the write cluster by default (extra write-tail latency); background/async behavior is a separate capability (managed-table background compaction / predictive optimization).
• legacy is just an alias for true (fixed 128 MB, no dynamic sizing) — not a different mode.

Common gotchas

• Optimized writes ≠ auto compaction. Pre-commit sizing vs post-commit merging. Use both for high-churn tables.
• It's synchronous — on the write cluster, post-commit; budget for the write-tail latency on tight micro-batch SLAs.
• Prefer auto — on + autotuned size; true/legacy pin 128 MB; false is off.
• MERGE/UPDATE/DELETE always compact — can't be disabled for those.
• Tune minNumFiles to your cadence — too low = constant rewrites, too high = lingering small files.
• Doesn't replace OPTIMIZE — schedule it on > 1 TB tables (or use predictive optimization); cluster for skipping.
• Migrating off legacy? Remove the session config and UNSET the table property, then verify.
• Independent of predictive optimization — write-cluster (synchronous) vs serverless (asynchronous); use either or both.