OPTIMIZE &
compaction.

What it is

OPTIMIZE is the command that takes the many small files a table has accumulated and rewrites them into fewer, right-sized files. That merging is called bin-packing — packing lots of little items into a smaller number of evenly-filled bins (here, the bins are data files of a healthy size). Nothing about your data changes — same rows, same values, same query results — only the physical file layout on storage gets tidier.

Why do small files pile up? Every streaming micro-batch, every MERGE/UPDATE/DELETE, every tiny append writes new Parquet files. A busy table can grow thousands of tiny files in a day. Each file carries fixed overhead — list it, open it, read its footer and stats, schedule a task for it — so thousands of tiny files make every read slow and metadata-heavy. OPTIMIZE is the broom that sweeps them back into a small number of well-sized files.

Analogy
It's like consolidating a junk drawer of half-empty envelopes into a few full folders. Same papers inside, far fewer things to pick up and flip through. Bin-packing balances folders by how thick they are (file size in storage), not by how many papers each holds (row count).

Real-world use case
A streaming events table ingests every few seconds and by noon has 40,000 files averaging 2 MB. A nightly OPTIMIZE compacts them to a few hundred ~256 MB files; the morning dashboards go from minutes to seconds. (On a UC managed table you'd let predictive optimization run this for you.)

Why it matters

• Small files make reads slow and expensive. Listing, opening, and scheduling a task per file is pure overhead. Replace 40,000 tiny files with a few hundred right-sized ones and the engine does far less bookkeeping before it touches a useful byte.
• Compaction is the read-side fix for write-side fragmentation. Frequent small writes (streaming, MERGE-heavy CDC) inevitably fragment a table; OPTIMIZE pays that debt down on a schedule instead of letting it accumulate forever.
• It composes with data skipping. Right-sized files plus colocation (ZORDER or liquid-clustering keys) means the engine reads fewer, bigger, better-organized files — the fast read.

See it work — bin-packing simulator

Below is a table stored as 32 small files. Click Run OPTIMIZE and watch the engine bin-pack them into a handful of right-sized files. The numFiles drops, the average file size jumps, and the read gets cheaper — same data throughout.

Simplified illustration. Real bin-packing balances files by their size in storage toward a target (autotuned ~256 MB→1 GB or an explicit delta.targetFileSize); total bytes are roughly preserved and results never change.

See it work — idempotency demo

Bin-packing is idempotent: the first OPTIMIZE compacts the files; a second, immediate run finds nothing left to merge and is a no-op (zero files rewritten). Contrast with a non-idempotent operation like OPTIMIZE … ZORDER BY, which can rewrite again on a re-run. Click repeatedly and watch the run-log.

Simplified illustration. Idempotency applies to plain bin-packing; OPTIMIZE … ZORDER BY is not idempotent. Whether a real run does work depends on how fragmented the table currently is.

How it works — sub-topic by sub-topic

1 · OPTIMIZE table_name — bin-packing into fewer right-sized files

OPTIMIZE reads the table's small Parquet files and rewrites their contents into a smaller number of larger files, then commits a new version pointing at them. This is bin-packing — greedily packing rows into output files until each reaches the target size. Fewer, larger files mean less listing/opening/footer-reading/task-scheduling on every read. The cost: a one-time CPU-heavy rewrite — run it on a schedule, not after every micro-batch.

SQL-- Compact ALL small files into fewer right-sized files (bin-packing).
-- Same data, same results afterward — only the physical layout changes.
OPTIMIZE main.delta_opt_demo.events;

PySpark# DeltaTable API equivalent of `OPTIMIZE table_name`.
from delta.tables import DeltaTable
dt = DeltaTable.forName(spark, "main.delta_opt_demo.events")
dt.optimize().executeCompaction()   # bin-packing (compaction only, no Z-order)

2 · OPTIMIZE t WHERE … — limit to a partition subset

OPTIMIZE t WHERE <partition predicate> restricts compaction to the files matching the predicate — e.g. only yesterday's partitions. The WHERE must reference partition columns; it scopes which files get rewritten, not which rows. On a big partitioned table you rarely need to recompact well-sized history, so scoping to recently-written partitions makes maintenance cheap.

SQL-- Only recompact the partitions that recently fragmented (cheaper).
-- The predicate must reference PARTITION columns.
OPTIMIZE main.delta_opt_demo.events
WHERE event_date >= DATE'2026-06-25';

PySpark# DeltaTable API: scope with .where() before executeCompaction().
dt = DeltaTable.forName(spark, "main.delta_opt_demo.events")
(dt.optimize()
   .where("event_date >= '2026-06-25'")   # partition predicate — limits the rewrite
   .executeCompaction())

3 · Python / Scala DeltaTable API: optimize().executeCompaction()

The DeltaTable API mirrors the SQL command. DeltaTable.forName(spark,"t").optimize().executeCompaction() runs bin-packing; chain .where("…") to scope to a partition subset, exactly like SQL. A Scala equivalent has the same shape. (For Z-order on non-LC tables the method is .executeZOrderBy(...) instead.) Use the API when you're orchestrating maintenance in PySpark/Scala — e.g. compacting several tables in a loop.

PySpark# Compact a list of tables, scoped to recent partitions.
from delta.tables import DeltaTable
for tbl in ["main.delta_opt_demo.events", "main.delta_opt_demo.clicks"]:
    (DeltaTable.forName(spark, tbl)
       .optimize()
       .where("event_date >= '2026-06-25'")   # optional; drop for a full compaction
       .executeCompaction())

Scala// Scala equivalent — same call shape as Python.
import io.delta.tables._
val dt = DeltaTable.forName(spark, "main.delta_opt_demo.events")
dt.optimize().where("event_date >= '2026-06-25'").executeCompaction()

4 · Bin-packing is idempotent, and balances by file SIZE

Bin-packing is idempotent — run OPTIMIZE twice and the second run has no effect, because the files are already right-sized. It aims for files evenly balanced by their size in storage, not by row count (though the two usually correlate). Idempotency makes OPTIMIZE safe to schedule aggressively: a no-op re-run costs almost nothing. (This applies to bin-packing; ZORDER is not idempotent — see sub-topic 6.)

SQL-- First run compacts the small files...
OPTIMIZE main.delta_opt_demo.events;
-- ...a second, immediate run is a NO-OP (idempotent): nothing left to bin-pack.
OPTIMIZE main.delta_opt_demo.events;   -- numFiles unchanged, no files rewritten

5 · Liquid clustering vs partitioned tables

On a liquid clustering table, OPTIMIZE doesn't just compact — it groups data by the clustering keys, incrementally (rewriting only what's needed to keep the layout healthy). On a partitioned table, compaction and layout happen within each partition — OPTIMIZE never merges files across partition boundaries. So an over-partitioned table (Lesson 02) can still have small files that compaction can't fully fix.

SQL-- LIQUID CLUSTERING table: OPTIMIZE is INCREMENTAL and groups by clustering keys.
OPTIMIZE main.delta_opt_demo.events;

-- PARTITIONED table: compaction happens WITHIN each partition (never across them).
OPTIMIZE main.delta_opt_demo.events_partitioned
WHERE event_date = DATE'2026-06-25';

6 · OPTIMIZE t ZORDER BY (col) — compaction + Z-order (non-LC only)

Adding ZORDER BY (cols) makes OPTIMIZE do two jobs at once: bin-pack and colocate rows along a space-filling curve so the chosen columns get tight, non-overlapping min/max ranges (Lesson 03) — better data skipping. Only for non-liquid-clustering tables (ZORDER is incompatible with liquid clustering), and unlike compaction it is NOT idempotent — re-running can rewrite again. For new tables, prefer liquid clustering.

SQL-- Compact AND colocate by the columns you filter on most (non-LC tables only).
-- NOTE: not idempotent — schedule deliberately, not on every job.
OPTIMIZE main.delta_opt_demo.events
ZORDER BY (event_type, user_id);   -- 1-2 dominant filter cols; more = less benefit each

PySpark# DeltaTable API: Z-order uses executeZOrderBy(...), not executeCompaction().
dt = DeltaTable.forName(spark, "main.delta_opt_demo.events")
dt.optimize().executeZOrderBy("event_type", "user_id")   # non-LC; NOT idempotent

7 · OPTIMIZE table_name FULL (DBR 16.0+) — force full reclustering

For a liquid clustering table, OPTIMIZE t FULL forces a full reclustering — it rewrites all data to the current clustering keys, not just the incremental delta. Run it right after you first enable clustering or change the keys, since plain OPTIMIZE is incremental and leaves existing data laid out the old way. It rewrites the whole table, so it can take hours on a big table — a one-time operation, not routine upkeep.

SQL-- After enabling clustering or CHANGING keys, force a full recluster (DBR 16.0+).
-- Rewrites ALL data to the current keys — can be slow/expensive on big tables.
ALTER TABLE main.delta_opt_demo.events CLUSTER BY (event_type, event_date);
OPTIMIZE main.delta_opt_demo.events FULL;   -- one-time, after the key change

8 · Snapshot isolation — live tables & streams are safe

Delta readers use snapshot isolation: they read a consistent committed version. When OPTIMIZE swaps the old small files for new compacted ones, in-flight readers keep reading their snapshot and aren't interrupted. Because OPTIMIZE makes no data changes, a read before and after returns the same results. Running it on a table that is a streaming source does not affect current or future streams. That's why it's safe on a live production table — no maintenance window needed.

9 · OPTIMIZE returns file statistics

OPTIMIZE returns a row of file statistics — min/max/total size of files removed and added, Z-ordering stats (when used), the number of batches, and the partitions optimized. The same metrics land in DESCRIBE HISTORY under operationMetrics. Pair them with DESCRIBE DETAIL (numFiles, sizeInBytes) for a clean before/after — measure the win instead of guessing.

SQL-- OPTIMIZE returns stats (files removed/added, batches, partitions optimized).
OPTIMIZE main.delta_opt_demo.events;

-- The same numbers land in DESCRIBE HISTORY under operationMetrics:
DESCRIBE HISTORY main.delta_opt_demo.events;   -- latest OPTIMIZE row → operationMetrics
DESCRIBE DETAIL  main.delta_opt_demo.events;   -- numFiles / sizeInBytes before/after

10 · Predictive optimization runs it automatically; frequency & compute

On UC managed tables, predictive optimization runs OPTIMIZE automatically when it's cost-effective — no scheduled job (Lesson 09). When you run it manually, frequency is a cost/performance trade-off: Databricks recommends start daily, then tune. OPTIMIZE is CPU-intensive (Parquet decode/encode) — use compute-optimized instances and attached SSDs. The target size the rewrite aims for is set by delta.targetFileSize / autotuning (Lesson 07).

SQL-- Let the platform run OPTIMIZE for you on UC MANAGED tables (Lesson 09):
ALTER SCHEMA main.delta_opt_demo ENABLE PREDICTIVE OPTIMIZATION;

-- Optional: pin the target file size the rewrite aims for (else autotuned — Lesson 07).
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.targetFileSize' = '256mb');

Comparison — OPTIMIZE variants & related maintenance

Uses, edge cases & limitations

Uses

• Plain OPTIMIZE: routine compaction of any fragmented Delta table (streaming sinks, MERGE-heavy CDC, many small appends).
• OPTIMIZE … WHERE: large partitioned tables where only recently-written partitions fragmented — recompact just those, cheaply.
• OPTIMIZE … ZORDER BY: legacy/external non-LC tables — compact and improve skipping in one pass.
• OPTIMIZE … FULL: one-time, after enabling liquid clustering or changing keys (DBR 16.0+).
• Predictive optimization: the modern default — let the platform run OPTIMIZE on UC managed tables.

Edge cases an interviewer probes

• OPTIMIZE on a streaming source → safe; snapshot isolation means current and future streams are unaffected and results don't change.
• Re-running compaction → idempotent no-op; but re-running OPTIMIZE … ZORDER BY is not a no-op and can rewrite again.
• WHERE scoping → on partitioned tables compaction is within partitions only, never across boundaries.
• Over-partitioned table → tiny partitions can't be merged across boundaries, so OPTIMIZE can't fully fix partition-explosion small files (Lesson 02).
• Very large table with autotuned target → growing the target file size does not re-optimize existing files; set a fixed delta.targetFileSize if you need uniformity (Lesson 07).
• OPTIMIZE FULL on a huge table → can take hours; it's a full rewrite, not routine maintenance.

Limitations

• OPTIMIZE is a rewrite — it costs compute and writes new files (CPU-heavy; prefer compute-optimized instances + SSDs).
• ZORDER BY is not idempotent and is not compatible with liquid clustering.
• OPTIMIZE … FULL requires DBR 16.0+.
• On partitioned tables, compaction never merges files across partitions.
• Plain OPTIMIZE only changes file layout — not data, schema, or the clustering/partition design.
• Predictive optimization is UC managed tables only (not external / OpenSharing).

Common gotchas

• OPTIMIZE is safe on live tables. Snapshot isolation — readers and streaming sources aren't interrupted and results don't change; no maintenance window needed.
• Compaction is idempotent; Z-order is not. Re-running plain OPTIMIZE is a free no-op; re-running OPTIMIZE … ZORDER BY can rewrite files again.
• Bin-packing balances by file SIZE, not row count. Expect evenly-sized files, not equal rows per file.
• WHERE is for partitions. It scopes compaction to a partition subset; it can't merge files across partition boundaries.
• Don't ZORDER a liquid-clustering table. They're incompatible — on LC tables plain OPTIMIZE already groups by the clustering keys.
• After changing clustering keys, run OPTIMIZE FULL. Plain incremental OPTIMIZE won't re-lay-out existing data to the new keys (DBR 16.0+).
• Measure, don't assume. Use OPTIMIZE's returned stats + DESCRIBE DETAIL + DESCRIBE HISTORY to confirm the win.
• On UC managed tables, prefer predictive optimization over a hand-scheduled OPTIMIZE job (Lesson 09).