Liquid Clustering vs Partitioning and Z-ORDER on Databricks

Choosing how to lay out a Delta table has always meant guessing how it will be queried. Partition by the wrong column and you get thousands of tiny folders. Z-ORDER by the wrong columns and OPTIMIZE spends hours rewriting data for little gain. When I worked on Databricks a few years ago, our standard was a date partition plus a weekly OPTIMIZE ... ZORDER BY on the main filter columns. It worked, but every new query pattern meant revisiting the design.

Liquid clustering, now generally available, replaces both partitioning and Z-ORDER for most tables. This post compares the three approaches using insurance transaction tables, and covers when to switch.

Partitioning: the old default

 1: CREATE TABLE prod.core.risks (
 2:   umr                STRING,
 3:   section_no         STRING,
 4:   coverholder_id     STRING,
 5:   certificate_ref    STRING,
 6:   bordereau_month    DATE,
 7:   year_of_account    INT,
 8:   sum_insured        DECIMAL(18,2),
 9:   gross_premium      DECIMAL(18,2)
10: )
11: PARTITIONED BY (bordereau_month);

Partitioning physically splits files into folders by value. Queries filtering on bordereau_month skip whole folders, which is fast. The problems:

• Only one access pattern benefits. A query by coverholder or UMR across all months still reads everything.
• Small files. Partition by something too granular, such as day or coverholder, and each folder holds a few small files. Reads get slower, not faster.
• It's fixed. Changing the partition column means rewriting the whole table.

The usual guidance was to only partition tables over about a terabyte, with at least a gigabyte per partition. Most insurance tables, even years of risk transactions, are smaller than that.

Z-ORDER: better, but manual

1: OPTIMIZE prod.core.risks
2: ZORDER BY (coverholder_id, umr);

Z-ORDER sorts data within files so related values sit together, which makes file skipping work for several columns at once. But it has costs:

• Every OPTIMIZE rewrites all the data in scope, even data that was already well organised.
• New data isn't clustered until the next run.
• It works within partitions, so a poor partition choice still hurts.

Liquid clustering

 1: CREATE TABLE prod.core.risks (
 2:   umr                STRING,
 3:   section_no         STRING,
 4:   coverholder_id     STRING,
 5:   certificate_ref    STRING,
 6:   bordereau_month    DATE,
 7:   year_of_account    INT,
 8:   sum_insured        DECIMAL(18,2),
 9:   gross_premium      DECIMAL(18,2)
10: )
11: CLUSTER BY (bordereau_month, coverholder_id);

With liquid clustering, you name up to four clustering keys and Databricks keeps the data organised by them. The differences that matter:

• Incremental. OPTIMIZE only reorganises data that needs it, so regular runs are cheap.
• Changeable. You can change the keys with ALTER TABLE. Existing data isn't rewritten immediately. New and optimised data follows the new keys.
• No small-file trap. Clustering on a high-cardinality column like coverholder_id or umr is fine. It doesn't create a folder per value.

1: ALTER TABLE prod.core.risks
2: CLUSTER BY (bordereau_month, umr);
3: 
4: OPTIMIZE prod.core.risks;

Liquid clustering can't be combined with PARTITIONED BY or ZORDER on the same table. It replaces both.

Choosing clustering keys

Pick the columns that appear most in filters and join conditions, not the ones that seem most important. For insurance transaction tables, typical choices are:

Table	Common filters	Suggested keys
Risk transactions	Bordereau month, coverholder, UMR	bordereau_month, coverholder_id
Premium transactions	Year of account, UMR and section	year_of_account, umr
Claim transactions	Claim reference, bordereau month	bordereau_month, claim_ref
Small dimensions (broker, coverholder)	Any	None. They're too small to benefit.

Fewer keys usually works better than more. Each extra key dilutes how well the others are clustered. Start with one or two and check query profiles before adding more.

MERGE gets faster too

The benefit isn't only for reads. A MERGE has to find the target files that might contain matching keys. If the target is clustered on the merge key, far fewer files are candidates:

1: MERGE INTO prod.core.claims t
2: USING claims_updates s
3:   ON  t.claim_ref = s.claim_ref
4:   AND t.bordereau_month = s.bordereau_month
5: WHEN MATCHED THEN UPDATE SET *
6: WHEN NOT MATCHED THEN INSERT *;

With the claims table clustered on (bordereau_month, claim_ref), this MERGE touches a small set of files instead of scanning the table. On tables that receive regular corrections, that's often the bigger win.

Migrating an existing table

For an unpartitioned table, you can enable clustering in place:

1: ALTER TABLE prod.core.premium CLUSTER BY (year_of_account, umr);
2: OPTIMIZE prod.core.premium;

New data is clustered from then on. Existing data is reorganised over time as OPTIMIZE runs. If you need the whole table clustered straight away, rebuilding it is the quickest route.

A partitioned table needs to be rebuilt, for example with CREATE TABLE ... CLUSTER BY ... AS SELECT into a new table and a swap. Check the runtime too: liquid clustering needs a recent Databricks Runtime, and readers and writers on older runtimes can't use the table.

Let the platform run OPTIMIZE

For Unity Catalog managed tables, predictive optimization can run OPTIMIZE and VACUUM automatically when Databricks judges it worthwhile. If it's enabled for your account, it removes the need for a maintenance job altogether. If not, schedule OPTIMIZE after your main loads.

What I would do

• For new tables, start with liquid clustering on one or two common filter columns.
• Avoid partitioning unless a table is very large and always filtered on one low-cardinality column.
• Retire weekly Z-ORDER jobs as you move tables across.
• Cluster on merge keys for tables that receive frequent corrections.