Databricks DE · Topic 3.2

Liquid Clustering

The modern Delta data layout — auto-organizes by clustering keys, replaces partitioning & ZORDER, and lets you change keys without rewriting data. This deep dive walks each with the mechanism and the code.

CLUSTER BYCLUSTER BY AUTOmax 4 keysOPTIMIZE FULLDBR 15.4 LTS+

The one-liner

Liquid Clustering replaces partitioning + ZORDER with automatic, key-based layout you can re-key without rewriting. Use CLUSTER BY (cols) or CLUSTER BY AUTO. Databricks recommends it for virtually all new tables (GA on DBR 15.4 LTS+).

🗄️ Analogy: partitioning is a filing cabinet with fixed labeled drawers — change the scheme, re-file everything. Liquid Clustering is a smart shelf that re-sorts as you add items, and you can change the sort rule anytime without re-shelving what's already there.

Layout approaches — click to compare

▸ partitioning vs ZORDER vs liquid clustering

Partitioning — rigid directory structure per partition value.

❌ Wrong key → small-file skew; changing it means a full rewrite
❌ Bad for high-cardinality columns

ZORDER — sorts/colocates within files on OPTIMIZE.

⚠️ Must be re-applied on every OPTIMIZE
⚠️ Needs manual tuning

Liquid Clustering — automatic key-based layout.

✅ Change keys without rewriting existing data
✅ Handles skew & high cardinality; great for fast-growing tables
✅ CLUSTER BY AUTO picks keys from query history

	Partitioning	ZORDER	Liquid Clustering
Change layout key	Full rewrite	Re-run OPTIMIZE	No rewrite
High cardinality	Poor	OK	Good
Combine with others	—	+ partitioning only	cannot mix
Recommended for new tables	No	No	Yes

1 · Create a table & choose keys

Pick the 1–4 columns you filter/join on most. Keys must be simple types (nested struct fields OK via dot notation; Array/Map/Struct columns are not). CLUSTER BY AUTO lets Predictive Optimization choose & adapt keys (DBR 15.4 LTS+, UC managed).

SQL — explicit keys & AUTO

CREATE TABLE main.sales.events (id BIGINT, event_date DATE, region STRING)
  CLUSTER BY (event_date, region);          -- up to 4 keys

CREATE OR REPLACE TABLE main.sales.events2 (id BIGINT, region STRING)
  CLUSTER BY AUTO;                            -- Databricks picks & adapts keys

PySpark (DataFrameWriterV2)

(df.writeTo("main.sales.events").using("delta")
   .clusterBy("event_date", "region").create())

2 · Change keys without rewriting — click each step

CREATE TABLE events (...) CLUSTER BY (event_date, region);
ALTER TABLE events CLUSTER BY (region);   -- re-key, no rewrite
OPTIMIZE events FULL;                      -- optional: recluster old data now (DBR 16.4 LTS+)

1 · CLUSTER BY (event_date, region)▸

Table is clustered by two keys. Writes & OPTIMIZE lay data out by these columns. (Up to 4 keys; simple types only.)

2 · ALTER … CLUSTER BY (region)▸

Query patterns shifted to region. Re-key instantly — existing data is NOT rewritten; new writes use the new key. Impossible with partitioning.

3 · OPTIMIZE events FULL▸

Optional (DBR 16.4 LTS+). Forces immediate reclustering of legacy data to the new key when you don't want to wait for incremental writes.

3 · Clustering on write & maintenance

Clustering on write is automatic but only triggers once a write reaches a size threshold (≈64 MB–1 GB depending on key count). Small/streaming writes may land unclustered until OPTIMIZE runs.

💡 Because not every operation clusters, run OPTIMIZE regularly — or rely on Predictive Optimization to do it automatically on UC managed tables. Structured Streaming to clustered tables needs DBR 16.4 LTS+.

Why it matters

Partitioning is rigid (skew + costly rewrites); ZORDER must be re-applied each OPTIMIZE. Liquid Clustering fixes both.
Recommended for virtually all new tables — a go-to "what would you choose today?" answer.

📅 Real-world use case: a fast-growing events table is filtered by event_date now, by region next quarter. Just ALTER TABLE … CLUSTER BY (region) — no full rewrite; future writes re-cluster.

Uses, edge cases & limitations

Uses: default for new tables; high-cardinality filters; skewed / fast-growing tables; shifting access patterns; concurrent writes.
Edge case: up to 4 keys recommended (small tables <10 TB can degrade past ~2); clustering-on-write triggers only past a size threshold — tiny/streaming writes may need OPTIMIZE; ALTER … CLUSTER BY doesn't touch old data (use OPTIMIZE FULL).
Limitations: GA on DBR 15.4 LTS+; OPTIMIZE FULL & streaming need 16.4 LTS+; cannot combine with partitioning or ZORDER; keys must be simple types (nested struct fields via dot notation OK; Array/Map/Struct columns not).

Common gotchas

❌ Using partitioning and Liquid Clustering together — incompatible.
❌ Expecting ALTER … CLUSTER BY to instantly reorganize old data — it applies going forward; use OPTIMIZE FULL (DBR 16.4 LTS+) to recluster now.
❌ Picking 5+ clustering columns — keep to the most-filtered 1–4.
❌ Clustering on a complex (Array/Map/Struct) column — unsupported.
❌ Assuming every write clusters — small writes may need OPTIMIZE; lean on Predictive Optimization for managed tables.