← One-Pager Library
Databricks · One-Pager · Delta Optimization

Delta Optimization & Liquid Clustering

How Delta keeps query-ready file layout — compaction, data skipping, and the clustering model that replaces partitioning and Z-order without rewriting data.

Liquid Clustering GA · DBR 15.4 LTS+ Predictive Optimization on by default Verified June 2026 docs

In one line

Delta optimization is the set of layout operations — OPTIMIZE compaction, data skipping via file stats, and liquid clustering — that minimize how much data a query has to read.

Why it matters

Streaming and incremental writes leave tables full of small files; queries then scan far more data than they need. These features pack files to a healthy size, keep per-file statistics so the engine can skip irrelevant files, and physically colocate related rows — so the same query touches a fraction of the table.

Core concepts

Data skipping (file stats)

Delta auto-collects per-file min/max, null counts and record counts (first 32 columns by default on UC external tables) to skip files that can't match a predicate.

OPTIMIZE bin-packing

Rewrites many small files into fewer evenly-sized ones; idempotent — a second run on the same data does nothing.

Z-order (legacy)

OPTIMIZE … ZORDER BY colocates related data on high-cardinality predicate columns; effectiveness drops with each added column. Only on non-clustered Delta tables.

Liquid clustering

CLUSTER BY (up to 4 keys) replaces partitioning and Z-order; keys can be redefined with no rewrite of existing data. Recommended for all new tables.

CLUSTER BY AUTO

Databricks picks and evolves clustering keys from query history; requires predictive optimization and runs asynchronously.

Predictive optimization

Auto-runs OPTIMIZE, VACUUM and ANALYZE on UC managed tables on serverless — no manual scheduling.

How layout strategies compare

Click a strategy
1 · Small files 2 · Partitioning 3 · Z-order 4 · Liquid clustering
Small files Partitioning Z-order Liquid clustering
Small files — incremental writes leave thousands of tiny files; queries waste I/O opening them. The baseline problem OPTIMIZE fixes.

Minimal example

SQL-- compact small files (idempotent); add ZORDER BY only on non-clustered tables
OPTIMIZE sales;

-- preferred for new tables: liquid clustering, keys editable with no rewrite
CREATE TABLE sales (id INT, region STRING, ts TIMESTAMP)
  CLUSTER BY (region, ts);

-- re-cluster only new data incrementally; OPTIMIZE FULL re-clusters everything
OPTIMIZE sales;

Choosing columns: clustering keys vs Z-order

Both techniques only pay off when the chosen columns match how queries actually filter the table — and only when those columns have file statistics collected (first 32 columns by default).

Liquid clustering keys (CLUSTER BY)

  • Pick the columns most commonly used in query filters and joins.
  • Up to 4 keys; on tables under ~10 TB, more keys can hurt single-column filters (4 keys can be slower than 2) — the penalty shrinks as the table grows.
  • If two columns are highly correlated, include only one of them.
  • Keys must have collected statistics; supported types are date/timestamp, string, numeric, and nested struct fields (dot notation) — not Struct/Map/Array.
  • Prefer CLUSTER BY AUTO to let Databricks choose and evolve keys from query history.

Z-order columns (ZORDER BY)

  • Pick columns commonly used in query predicates.
  • Favor high cardinality (many distinct values) — low-cardinality columns gain little from co-location.
  • Effectiveness drops with each extra column, so keep the list short — just the most-filtered columns.
  • Only Z-order columns that have statistics; ordering a column without stats is ineffective and wastes compute.
  • Not idempotent, and legacy — only on non-clustered Delta tables.
Shared rule of thumb
  • Both only skip files on columns the query filters or joins on — clustering a column nothing queries by buys nothing.
  • Both require statistics on the chosen columns; ordering past the stats boundary is wasted work.
  • Z-order rewards high cardinality; liquid clustering instead penalizes redundant correlated keys and caps you at four.

Gotchas & limits

Interview soundbites

Say this out loud
  • "Liquid clustering replaces partitioning and Z-order — you set CLUSTER BY keys and can redefine them later with no table rewrite. It's GA from DBR 15.4 LTS and the default recommendation for new tables."
  • "OPTIMIZE is idempotent bin-packing — it compacts small files into healthy-sized ones; data skipping then uses per-file min/max stats to skip files that can't match the predicate."
  • "Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE automatically on Unity Catalog managed tables on serverless — so you stop hand-scheduling maintenance jobs."

References

Verified against docs.databricks.com · June 2026