Databricks DE · Topic 3.1

OPTIMIZE & Z-ORDER

OPTIMIZE compacts many small files into fewer big ones; ZORDER co-locates related values so queries skip more data. This deep dive walks each with the mechanism and the code.

Small-file problemBin-packing ~1 GBData skippingOptimized writesLiquid Clustering preferred

The one-liner

OPTIMIZE = compaction (fix tiny files), bin-packing toward ~1 GB files. ZORDER BY = cluster related values on rewrite so filters skip more files. For new tables, Databricks now recommends Liquid Clustering over ZORDER.

📦 Analogy: a warehouse of tiny one-item boxes. OPTIMIZE repacks them into a few big neat boxes (faster to fetch). Z-ORDER also groups related items — all "red shirts" together — so a picker filtering for red shirts opens far fewer boxes.

OPTIMIZE — watch compaction

▸ click to run / reset

12 small files · slow reads

1 · OPTIMIZE — bin-packing compaction

Rewrites small files into fewer files near a target size (~1 GB by default, spark.databricks.delta.optimize.maxFileSize). Idempotent and reader-safe (snapshot isolation); scope it with a predicate to keep cost down.

SQL & PySpark

OPTIMIZE main.sales.events;                              -- whole table
OPTIMIZE main.sales.events WHERE event_date >= '2026-06-01';  -- scope = cheaper

from delta.tables import DeltaTable
DeltaTable.forName(spark, "main.sales.events").optimize().executeCompaction()

2 · Z-ORDER — data skipping in action

▸ click a layout; you're querying WHERE color = red

Files (each holds 4 rows)

ZORDER sorts data along a space-filling curve so similar values land in the same files → tighter per-file min/max → more files skipped. Pick the 1–3 columns you actually filter/join on; it's re-applied on each OPTIMIZE as new data lands.

OPTIMIZE main.sales.events ZORDER BY (event_date, user_id);

(DeltaTable.forName(spark, "main.sales.events")
   .optimize()
   .executeZOrderBy("event_date", "user_id"))

3 · Avoid small files at write time

Rather than only cleaning up afterward, reduce small files as you write. Both target ~128 MB (vs OPTIMIZE's ~1 GB), trading a little write latency for fewer files.

ALTER TABLE main.sales.events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',   -- ~128 MB files on write
  'delta.autoOptimize.autoCompact'   = 'true');  -- compact small files after write

4 · Let Databricks run it — Predictive Optimization

💡 On Unity Catalog managed tables, Predictive Optimization automatically runs OPTIMIZE (and VACUUM/ANALYZE) when it detects benefit — so often you don't schedule OPTIMIZE yourself (don't double-schedule). External tables aren't covered — you maintain those manually.

Why it matters

Streaming/frequent writes create thousands of small files → every query pays to open them all. OPTIMIZE restores fast reads.
Data skipping is how Delta avoids reading irrelevant files; Z-ORDER makes skipping far more effective on your filter columns.

📈 Real-world use case: an Auto Loader job writes a bronze table every minute, leaving tiny files. A nightly OPTIMIZE events ZORDER BY (event_date, user_id) compacts and clusters by the common filters → dashboards speed up.

ZORDER vs Liquid Clustering

	OPTIMIZE (bin-pack)	OPTIMIZE … ZORDER	Liquid Clustering
Fixes small files	✅	✅	✅ (incremental)
Improves filter skipping	❌	✅ (chosen cols)	✅ (chosen cols)
Idempotent / re-spec	✅ idempotent	re-applied each run	key set on table
Status	current	older approach	recommended (new tables)

💡 Modern default: for new tables prefer Liquid Clustering (CLUSTER BY, Topic 3.2) over partitioning/ZORDER. Reach for ZORDER mainly on existing tables not yet on liquid clustering.

Uses, edge cases & limitations

Uses: compacting streaming/bronze tables; speeding selective queries via ZORDER on high-cardinality filter columns; optimized writes to prevent small files up front.
Edge case: OPTIMIZE is CPU-intensive — run off-peak on adequate compute, use WHERE to limit scope. ZORDER on too many columns dilutes the benefit (pick 1–3).
Limitation: ZORDER must be re-applied on each OPTIMIZE and is the older approach — prefer Liquid Clustering for new tables. A rewrite is a data-change commit, so it interacts with VACUUM retention and streaming readers.

Common gotchas

❌ Expecting plain OPTIMIZE to speed up filtering — that's ZORDER; OPTIMIZE alone just compacts.
❌ ZORDER by a column you never filter on — no benefit, wasted compute.
❌ Partitioning + ZORDER for a brand-new table — prefer Liquid Clustering.
❌ Double-scheduling when Predictive Optimization already handles managed tables.
❌ Assuming ZORDER auto-maintains — new data needs another OPTIMIZE … ZORDER.