Liquid clustering replaces both partitioning and ZORDER, and Databricks recommends it over Z-ordering for all new tables. Most Delta tables under 100 TB need no partitioning at all — which for a distributor selling into four countries means nothing in this warehouse is partitioned.
Why
PARTITIONED BY (year, month, day) is the default reflex, and here it is a guess made before anyone had queried anything. A day of order lines across four countries is a few megabytes, so every partition is one small file and a year is over a thousand files containing almost nothing — and any query filtering on customer_sk, which is most of them, opens the lot. You notice it as query times rising with the age of the table while the data volume stays trivial. By then the partition column is in the physical path of every file.
How
sqlgold.fct_order_line — the only layout statement it needs
The keys are not a guess: every dashboard query on this fact filters a date range and drills into a customer, so customer_sk and order_ts are what appears in the WHERE clause. Four keys is the maximum; two is usually right, because the keys share one ordering — every key you add spends part of the file layout on a column that appears in fewer queries, and dilutes the one that appears in all of them.
sqlChanging the layout of a table that already exists
-- unpartitioned table -> clustered
ALTER TABLE gold.dim_customer CLUSTER BY (customer_id);
-- ALREADY PARTITIONED -> clustered (this is the migration path).
-- FLOOR: DBR 18.1+. On 15.4 / 16.4 / 17.3 LTS this does not fail at
-- runtime, it fails to PARSE. Check the cluster before writing the script.
-- If any partition column is a TIMESTAMP, this comes first in the same
-- session or the statement errors out:
SET spark.databricks.delta.liquidConversion.statsGeneration.enabled = false;
ALTER TABLE gold.fct_order_line
REPLACE PARTITIONED BY WITH CLUSTER BY (customer_sk, order_ts);
-- hand the key choice to predictive optimization (DBR 15.4 LTS+).
-- TWO PREREQUISITES: the table must be Unity Catalog MANAGED, and
-- predictive optimization must be enabled on it -- that is the thing that
-- selects the keys. Without it, AUTO has nobody to ask.
ALTER TABLE gold.agg_revenue_month CLUSTER BY AUTO;
-- turn it off entirely
ALTER TABLE gold.agg_revenue_month CLUSTER BY NONE;
-- none of the above rewrites existing data. THIS does, once (DBR 16.4 LTS+):
OPTIMIZE gold.fct_order_line FULL;
Doing it
Create every table with CLUSTER BYNo PARTITIONED BY anywhere in the repository — a grep in CI is the whole enforcement.
Choose keys from observed filter columns, not from the calendarsystem.query.history tells you what the last month of dashboard queries actually filtered on.
Use CLUSTER BY AUTO where the access pattern is unknown or still movingDeclared keys act as the starting hint. Confirm first that the table is UC managed and that predictive optimization is enabled on it — AUTO is predictive optimization choosing the keys, so without it the clause buys nothing.
Before writing any partitioned-to-clustered migration, read the runtime off the clusterThe conversion statement needs DBR 18.1+; on an LTS runtime you are rewriting the table with a CTAS instead, not ALTERing it.
After a key change run OPTIMIZE … FULL exactly once, off-peak, then never by hand again
Embedding it
Have them clone gold.fct_order_line into a partitioned copy and a clustered copyRun the same dashboard query against both, and read numFiles from DESCRIBE DETAIL. The number ends the argument without you in it.
When someone proposes PARTITIONED BY, ask for the expected row count per partition rather than rejecting itThey usually withdraw it themselves, and now they know why.
Make the CI grep that rejects PARTITIONED BY the team's ten-line job, not yoursA standard without a check is a preference.
Defaults
CLUSTER BY on every new table; PARTITIONED BY on none
Two keys, four maximum, taken from real filter columns
Three statements here have three different runtime floors, Stand 29.07.2026CLUSTER BY AUTO needs DBR 15.4 LTS, OPTIMIZE … FULL needs 16.4 LTS, and REPLACE PARTITIONED BY WITH CLUSTER BY needs 18.1. Read them off the client's runtime, not off this page.
CLUSTER BY AUTO only on Unity Catalog managed tables with predictive optimization enabledBoth are prerequisites, not recommendations.
Clustering and ZORDER are mutually exclusive — a table is one or the other
OPTIMIZE … FULL once after a key change; never on a schedule
Gotchas
PARTITIONED BY (year, month, day) produces small files and locks you into a guessThe guess was made before anyone queried anything. At this warehouse's volumes each partition holds a few megabytes, so a year of data is a thousand tiny files that every customer-filtered query must open. The symptom is latency rising with table age while the data stays small.
ALTER TABLE … CLUSTER BY rewrites nothingExisting files keep their old layout, the table reports clustering keys, and performance does not move — which reads as 'liquid clustering does not work' rather than 'we skipped OPTIMIZE … FULL'.
A leftover scheduled OPTIMIZE … ZORDER BY pointed at a now-clustered tableClustering and Z-ordering are mutually exclusive, so the statement raises DELTA_CLUSTERING_WITH_ZORDER_BY and the nightly job starts failing — and nobody reads the log, or the alert, of a job that has been green for a year. You notice it when someone asks why the table stopped being compacted.
Clustering on order_line_sk because it is the primary keyNothing queries a fact by surrogate key, so the clustering does nothing while the OPTIMIZE cost is entirely real.
Running REPLACE PARTITIONED BY WITH CLUSTER BY on an LTS runtimeThe floor is DBR 18.1; on 15.4, 16.4 or 17.3 LTS it is a syntax error, so the migration notebook fails on its first table and the conclusion drawn is that the conversion path does not exist rather than that the cluster is two majors behind. The variant that survives the version check: a TIMESTAMP partition column, which errors until SET spark.databricks.delta.liquidConversion.statsGeneration.enabled = false has run in the same session.
Pointing the same conversion at a streaming table or materialized view a Lakeflow pipeline createdIt is not supported on pipeline-managed objects, so a script that walks a list of tables gets partway through and leaves the pipeline's half partitioned — and the half that failed is the half that ingests. Their layout is changed in the pipeline definition and applied by the next update.
CLUSTER BY AUTO on a table predictive optimization does not coverAn external table, or a managed table in a catalog where it is switched off. The DDL is accepted and the table advertises AUTO, but nothing ever selects a key, so it behaves like an unclustered table while every review of the DDL says it is clustered.
Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE automatically on Unity Catalog managed tables. It is on by default for accounts created on or after 11.11.2024, and the rollout to older accounts was expected to finish in August 2026 — that is, now. So the honest answer to 'do we need a nightly maintenance job?' differs from last quarter's, and the first move is to check rather than assume.
Why
Almost every team arrives with a nightly OPTIMIZE + VACUUM notebook copied from a post written before predictive optimization existed. Nothing about it looks wrong — the tables are compacted, the job is green — so nobody revisits it and the bill quietly carries two maintenance systems. The other half of that notebook is worse: a shortened VACUUM retention, added to save storage, which deletes files a concurrent reader is still using and destroys the time travel you were relying on for the incident you have not had yet.
How
sqlSet it where it is inherited, then watch what it costs
-- catalog level, so schemas and tables created later are covered
ALTER CATALOG dwh_prod ENABLE PREDICTIVE OPTIMIZATION;
-- narrower levers, same grammar: ENABLE | DISABLE | INHERIT
ALTER SCHEMA dwh_prod.gold INHERIT PREDICTIVE OPTIMIZATION;
ALTER TABLE dwh_prod.gold.fct_order_line INHERIT PREDICTIVE OPTIMIZATION;
-- what it did, and what it cost (usage_unit is ESTIMATED_DBU)
SELECT
operation_type, -- COMPACTION | VACUUM | ANALYZE | CLUSTERING
COUNT(*) AS operations,
ROUND(SUM(usage_quantity), 2) AS estimated_dbus
FROM system.storage.predictive_optimization_operations_history
WHERE catalog_name = 'dwh_prod'
AND start_time >= current_date() - INTERVAL 30 DAYS
GROUP BY 1
ORDER BY estimated_dbus DESC;
Doing it
Before writing any maintenance job, check whether predictive optimization is enabled on the catalogOn an account mid-rollout that check is the difference between necessary work and duplicate spend.
Enable it at catalog level on dwh_prod so tables created next year inherit it without anyone remembering
Delete the inherited nightly OPTIMIZE/VACUUM job for every table it coversLeaving it 'just in case' is the expensive way to do nothing.
Keep exactly one manual lever: OPTIMIZE … FULL after a clustering-key change
Embedding it
Ask the team to find every scheduled job whose name contains 'optimize' or 'vacuum' and justify each oneThe list is always longer than they expect, and finding it themselves is what makes them delete it.
Have them explain what the seven-day VACUUM retention protects, out loud, before they may shorten it
Hand over the operations-history query, not a screenshot of itA team that can rerun the number owns the number.
Defaults
Predictive optimization enabled at catalog level; individual tables INHERIT
No hand-written OPTIMIZE or VACUUM schedules for managed tables
Default VACUUM retention stays at seven days unless a written retention decision says otherwise
Maintenance cost reviewed monthly from the operations-history system table, not estimated
Gotchas
Manual OPTIMIZE running alongside predictive optimizationYou pay for both, and the writers contend on the same table version — visible as ConcurrentModificationException retries in a job whose code has not changed in months.
VACUUM RETAIN 0 HOURS, or lowering delta.deletedFileRetentionDuration to trim storageIt deletes files a running query is still reading, so a downstream job fails with FileNotFoundException on data that is plainly there, and the time-travel window you would have used to recover from a bad load is gone.
Predictive optimization does not cover external tablesIf half the warehouse is external, half of it is silently unmaintained, and the divergence surfaces months later as one dashboard getting slower for no visible reason.
Assuming it is on because the docs say it is on by defaultThat default applies to accounts created on or after 11.11.2024; an older account mid-rollout is exactly where the wrong assumption buys a duplicate maintenance bill.