Liquid Clustering vs Z-Order vs Partitioning

[LiresaFerizaj]

Liquid clustering: what it actually replaced, and the caveats worth knowing

I see a lot of questions here that boil down to "should I partition, Z-order, or use liquid clustering." Posting how I reason about it, partly to help and partly because I'd like to hear where others disagree.

All three do the same job

They exist to make the engine read fewer files. Delta stores min, max and null counts per file. When a query filters on a column, those statistics are compared against the predicate and files that cannot match are never opened. Clustering works by making those ranges tight. In a randomly ordered table every file spans the whole range, so nothing can be skipped.

The three approaches differ in granularity and in what they cost you later.

Partitioning

Physical directories per value. Fast pruning when it fits, but three problems. It breaks on high cardinality columns, because you get a directory and at least one file per value. It is a permanent choice, so changing the partition column means rewriting the table. And a skewed partition value is a hotspot you cannot fix without that rewrite.

Z-order

Multi-dimensional clustering within files rather than directories, so cardinality stops being a concern. Two remaining problems. It is a batch operation you have to keep re-running as data lands, and the effect degrades past three or four columns. The keys are also still effectively fixed, since changing them means a full rewrite.

Liquid clustering

Declared on the table, maintained incrementally as data arrives, and the keys are mutable.

CREATE TABLE silver.orders (...) CLUSTER BY (customer_id, order_date);
ALTER TABLE silver.orders CLUSTER BY (region);
CREATE TABLE silver.events (...) CLUSTER BY AUTO;

That second line is the part I think is underrated. Query patterns always change, and repartitioning a large table is a project rather than a config change. Being able to change clustering keys without a rewrite is the feature, more than the layout quality itself.

Caveats that catch people

It is best effort, not a guarantee. Newly written data is not clustered until maintenance runs, so you still need OPTIMIZE scheduled or predictive optimization enabled on Unity Catalog managed tables. People enable clustering, see no improvement, and conclude it does not work.

Changing keys does not reorganise existing data. New writes cluster on the new keys and old data converges over time. If you need existing data reclustered immediately there is a full reclustering variant of OPTIMIZE, and it is not cheap.

It replaces partitioning and Z-order rather than combining with them. You cannot have both on the same table.

There is a cap on the number of clustering keys, so it is not a substitute for thinking about which columns you actually filter and join on.

When I would still partition

A non-performance requirement. Regulatory separation of data by region into distinct paths, or making concurrent writers provably disjoint to avoid commit conflicts. Otherwise I default to liquid clustering on new tables.

Open questions

Has anyone measured CLUSTER BY AUTO against manually chosen keys on a workload with genuinely mixed query patterns? And has anyone migrated a large partitioned table across and found the reclustering cost worse than expected? Interested in numbers rather than impressions.

[Islam_hoti]

@LiresaFerizaj  

Good write up, and the framing that mutable keys are the actual feature rather than the layout quality matches my experience.

Two refinements.

On best effort clustering, it is slightly better than "nothing happens until maintenance runs". Databricks attempts to cluster on write when the incoming write is small enough, so steady trickle ingestion stays reasonably clustered between OPTIMIZE runs. It is large writes and bulk loads that land unclustered and wait for maintenance. Worth knowing because it changes which workloads actually need aggressive scheduling.

On when to still partition, I would add a second case to yours. If you delete by time, dropping a partition is vastly cheaper than a DELETE across a clustered table, and retention policies are a lot easier to evidence when the boundary is physical. That is a lifecycle argument rather than a performance one, but it is the reason I have kept date partitioning on a couple of tables.

On your AUTO question, the thing to keep in mind is that it chooses keys from observed query history through predictive optimization. So it needs a warm up period on a new table, and it reacts to a shift in query patterns rather than anticipating one. For genuinely mixed workloads that is probably fine, since a human picking keys is also reacting. Where I would expect manual keys to still win is when you know a pattern is coming that the history has not seen yet, for example a new consumer about to onboard.

No numbers from me on the reclustering cost, but I would be interested in the same. My suspicion is that most teams never run the full variant and just let old data age out of relevance, which sidesteps the question rather than answering it.

[aayush_410]

This lines up well with what Databricks' own docs and the 2026 refresh of their partitioning guidance say — a few things worth adding as confirmation/extra data points rather than pushback:

On the key-count cap: confirmed at 4 clustering keys max. Worth noting the degradation pattern is more specific than just "past 3-4 columns it gets worse" — Databricks' docs call out that on tables under 10TB specifically, filtering with 4 keys measurably underperforms filtering with 2, but that gap becomes negligible as table size grows. So "how many keys to use" is itself a function of table size, not a fixed rule.

On "when I would still partition": worth flagging that Databricks now defaults unpartitioned tables to ingestion-time clustering automatically — so a chunk of the traditional case for manual partitioning ("I want partition-like pruning without thinking about it") is already handled by default on new tables, with no CLUSTER BY declaration needed. Their current guidance is essentially: don't partition under 1TB, prefer liquid clustering from 1TB–100TB, and only reach for manual partitioning for the non-performance reasons you listed (regulatory path separation, disjoint concurrent writers).

On your open questions — Databricks published a "Debunking 8 data layout myths" post earlier in 2026 that directly takes on several of the assumptions people carry over from partitioning (including the pruning-granularity myth). Worth a read even though it's a vendor post and inherently has a rooting interest — it at least tells you which claims they're willing to put a stake in the ground on. I haven't seen independent numbers on CLUSTER BY AUTO vs. manually chosen keys under genuinely mixed access patterns, or hard reclustering-cost figures for a large partitioned-table migration — if anyone in the thread has run that migration, that's the more valuable data point than anything vendor-published.