How do I run OPTIMIZE and compaction on a Delta table?

Summary

• Run OPTIMIZE table_name in SQL, or DeltaTable.forName(spark, "table_name").optimize().executeCompaction() in Python or Scala, to bin-pack small files into larger ones. SQL OPTIMIZE needs Delta Lake 1.2.0 or above; the Python and Scala APIs need Delta Lake 2.0 or above.
• Add a WHERE predicate on partition columns to compact only part of a table, or ZORDER BY (col) to colocate rows by a column you filter on often.
• OPTIMIZE removes the replaced files from the transaction log but leaves them in storage. VACUUM deletes unreferenced files older than the retention threshold, which defaults to 7 days, and open-source Delta Lake never runs it automatically.
• To compact as data lands, enable auto compaction (delta.autoOptimize.autoCompact) or optimized writes (delta.autoOptimize.optimizeWrite), both available in Delta Lake 3.1.0 and above.
• On Databricks, predictive optimization runs OPTIMIZE, VACUUM and ANALYZE for you on Unity Catalog managed tables.

How do I run OPTIMIZE and compaction on a Delta table?

Run the OPTIMIZE command. In open-source Delta Lake, OPTIMIZE events rewrites a table's small Parquet files into fewer, larger ones, a process the Delta docs call bin-packing. The DeltaTable API exposes the same operation as optimize().executeCompaction(). Schedule it to match how fast small files pile up, then run VACUUM, because OPTIMIZE by itself frees no storage. For new tables, use liquid clustering, which turns the same OPTIMIZE command into incremental clustering.

What OPTIMIZE changes, and what it leaves alone

Compaction rewrites files, not data. Per the Delta Lake docs:

• A read before and after OPTIMIZE returns the same results.
• Readers use snapshot isolation, so running queries aren't interrupted when OPTIMIZE removes files from the transaction log.
• Streams that use the table as a source keep running unaffected.
• Bin-packing is idempotent. Run it twice on the same data and the second run has no effect.
• The command returns file statistics (min, max, total) for the files it removed and added, plus the number of batches and partitions it optimized.

Bin-packing balances files by size on disk, not row count. Z-ordering aims for even row counts per file and isn't idempotent: every run reclusters all files in the partition.

Run compaction in SQL or Python

OPTIMIZE events;
OPTIMIZE delta.`/data/events`;
OPTIMIZE events WHERE date >= '2026-10-01';
OPTIMIZE events WHERE date = '2026-10-01' ZORDER BY (eventType);
VACUUM events DRY RUN;
VACUUM events;

The WHERE clause takes a predicate on partition columns. VACUUM ... DRY RUN lists the files that would be deleted before anything is removed.

from delta.tables import DeltaTable

events = DeltaTable.forName(spark, "events")
events.optimize().executeCompaction()
events.optimize().where("date='2026-10-01'").executeCompaction()
events.optimize().executeZOrderBy("eventType")
events.vacuum()

Scala exposes the same DeltaTable builder methods.

Choose the layout tool that fits the table

Option	When to use it	Trade-off
OPTIMIZE (bin-packing)	Frequent small appends or streaming writes leave many small files	Rewrites files; safe to rerun because it's idempotent
OPTIMIZE ... ZORDER BY (col)	Queries filter on a high-cardinality column and the table doesn't use clustering	Not idempotent; each extra column weakens locality; the columns need statistics
Liquid clustering (CLUSTER BY, then OPTIMIZE)	New tables, or filter patterns that change over time (Delta Lake 3.1.0 and above)	Can't be combined with partitioning or ZORDER; up to 4 clustering columns
Auto compaction	Writers keep producing small files and you want them compacted right after each write (Delta Lake 3.1.0 and above)	Runs synchronously on the cluster that wrote the data
Optimized writes	Partitioned tables that receive many small files per partition (Delta Lake 3.1.0 and above)	Data is shuffled before it's written, so write latency can rise

Compact as you write

Liquid clustering simplifies the layout decisions that partitioning and Z-ordering leave to you. Create the table with CLUSTER BY (col0) and keep running OPTIMIZE; clustering is incremental and rewrites only the data that needs it. If you change the keys with ALTER TABLE ... CLUSTER BY, run OPTIMIZE table_name FULL (Delta Lake 3.3 and above) to recluster existing records.
For write-time compaction, set table properties:

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

The session-level switches are spark.databricks.delta.autoCompact.enabled and spark.databricks.delta.optimizeWrite.enabled. Auto compaction uses 128 MB as its default target file size (spark.databricks.delta.autoCompact.maxFileSize), fires only once a table or partition has enough small files (spark.databricks.delta.autoCompact.minNumFiles), and skips files it has already compacted.

How Databricks approaches Delta compaction

The same commands run on Databricks. What changes is how much you schedule yourself:

• Predictive optimization schedules OPTIMIZE, VACUUM and ANALYZE by itself for Unity Catalog managed tables (Delta Lake and Iceberg), using serverless compute. Databricks recommends it for all managed tables.
• Automatic file sizing. Databricks tunes file sizes for Unity Catalog managed tables, and auto compaction and optimized writes are always on for MERGE, UPDATE and DELETE.
• Automatic liquid clustering. CLUSTER BY AUTO (Databricks Runtime 15.4 LTS and above for managed Delta tables) lets predictive optimization choose clustering keys from your query patterns.
• OPTIMIZE ... FULL forces reclustering on liquid-clustered tables in Databricks Runtime 16.0 and above.

For tables you maintain by hand, Databricks suggests starting with a daily OPTIMIZE, then adjusting for cost. See Optimize data file layout and Predictive optimization for Unity Catalog managed tables.

FAQs

Why didn't storage shrink after OPTIMIZE?

OPTIMIZE removes the old small files from the transaction log, but the files stay in storage. VACUUM deletes files that are no longer referenced and are older than the retention threshold (7 days by default), and after it runs you can no longer time travel to versions older than that period.

Can I compact only recent partitions?

Yes. Add a partition predicate, such as OPTIMIZE events WHERE date >= '2026-10-01' in SQL or .where("date='2026-10-01'") on the DeltaTable builder.

Should I use ZORDER BY or liquid clustering?

Use liquid clustering for tables you can redefine. It's incremental and lets you change keys without rewriting existing data. A table can't use both, because clustering isn't compatible with partitioning or ZORDER.