Migrating Large-Scale Z-Ordered Tables to Liquid Clustering: Strategies for Production Pipelines

[Khasim_1]

Hi everyone,

As we look to modernize our Delta tables, I’m evaluating the move from Z-Ordering to Liquid Clustering, particularly for our historical datasets that exceed 500TB.

While the benefits of Liquid Clustering (avoiding over-partitioning and better flexibility) are clear for new tables, I’m curious about operational reality of migrating existing massive tables:

1. In your experience, is it worth the compute cost to perform a full OPTIMIZE rewrite on petabyte-scale historical data, or are you finding it better to only apply Liquid Clustering to new partitions moving forward?
2. How are you handling the transition period in downstream pipelines—did you notice any performance regression in read-latency while the table was in a "hybrid" state of both Z-Order and Liquid Clustering?
3. For those managing highly skewed data, has Liquid Clustering removed the need for manual workarounds like salt-keys entirely?

I’m trying to justify the migration ROI to my stakeholders and would love some real-world feedback.

[data_pulse]

@Khasim_1 

I went through a similar migration and took a cautious approach rather than doing a full rewrite immediately. Have more content on it out of lessons learnt from the implementation, but only posting here what's been required for the questions posted.

1) Full historical rewrite vs new data only:
Didn't go for a full rewrite straight away. Let new writes use LC first, watched how it performed for a bit, then decided if reclustering the old data was worth it. When we did pull the trigger on the historical partitions, used OPTIMIZE table_name FULL, that's the command built for exactly this converting old partition/ZORDER files into LC layout.

Took a few hours on a large serverless warehouse for our biggest tables, so plan for it as a one-time chunk of compute, not something to run casually.

2. Transition period

Wouldn't call it both active at once really. Old files just sit there in the old layout until they get touched, new/re-clustered files get LC benefits. So performance during the transition depends on file age more than anything. We kept an eye on the same queries and merges through this period and didn't see any real regression, just less benefit on the parts that hadn't been re-clustered yet.

3. Skew/salting

LC definitely helps with layout and pruning, but I wouldn't say it kills the need for salting entirely. Salting is more about skew during joins/aggregations at runtime, that's a different problem than how data sits on disk. If a few keys are dominating your join, LC alone won't save you there.

Rough flow we followed:

baseline current setup → test similar workload in non-prod → turn on LC → compare merge/query perf → drop the partitioned write logic → set LC with max 4 keys → run first OPTIMIZE FULL → turn on Predictive Optimization → only promote once it looked good.

One current Databricks direction worth noting: For new tables Databricks now recommends Liquid Clustering over traditional partitioning/ZORDER, and for UC managed tables you can use CLUSTER BY AUTO so Databricks can choose and adapt clustering keys automatically. Existing large tables still need a deliberate migration/re-clustering decision, so the baseline-first approach is still important.

Also a reference that already talks about LC being better than partitioning. Worth looking into it. Pay some attention to LC limitations too from the docs before jumping the ships.

[Islam_hoti · Accepted solution]

Hi,

The good news is that the migration is much cheaper than you are assuming, because the full rewrite is optional.

When you run ALTER TABLE with CLUSTER BY, existing data is not rewritten. Subsequent writes and normal OPTIMIZE runs use the new clustering, while historical files stay as they are. Reclustering the back catalogue only happens if you explicitly run OPTIMIZE FULL. So your option two in question one is not a compromise, it is the default behaviour, and at 500TB it is almost certainly the right starting point.

If you later want to recluster selectively, OPTIMIZE FULL WHERE lets you scope it with a predicate, so you can rewrite the last year of data where most queries land and leave the cold tail untouched. That turns an all or nothing compute decision into something you can size against actual query patterns.

Also worth checking your runtime. On DBR 18.1 and above there is ALTER TABLE with REPLACE PARTITIONED BY WITH CLUSTER BY, which converts a partitioned table directly. That is a lot less painful than the CTAS path older runtimes required.

On the hybrid state in question two, the two are mutually exclusive on a table, so you never really run both. What you have is a clustered table where some files still carry the old layout. In practice reads degrade gracefully, since data skipping works per file either way, and the older files just skip less well than the new ones. The regression to watch is on queries that span both eras, not on the table as a whole.

On question three, I would separate two things that often get conflated. Liquid clustering fixes layout skew, so it does remove the need for tricks aimed at uneven file sizes and hot partitions. It does not help with shuffle skew during joins and aggregations, which is what salt keys usually address. If you are salting because one key dominates a join, clustering will not change that.

For the stakeholder case, the honest framing is that the near term return comes from removing partition maintenance and the risk of over partitioning, not from a one time layout improvement that costs a petabyte scale rewrite to obtain.