What you will be able to do
- Explain why liquid clustering needs less layout maintenance than partitioning or ZORDER
- Enable liquid clustering on new and existing tables, and convert partitioned tables
- Describe how automatic liquid clustering chooses keys, what it depends on, and why it might choose none
- Verify whether a table has automatic liquid clustering enabled
1.Why liquid clustering replaces partitioning and ZORDER
Partitioning and ZORDER make you commit to a layout early. Pick partition columns that turn out to have too many values and you get a flood of tiny partitions. Pick the wrong columns and the only fix is rewriting the whole table. Z-ordering doesn't need a rewrite, but someone has to keep re-running it.
Liquid clustering is the replacement Databricks recommends. It organizes data by clustering keys, and you can change those keys later without rewriting existing data, so the layout can change as query patterns change. It is generally available for Delta Lake tables on Databricks Runtime 15.4 LTS and above, and it works for streaming tables and materialized views as well. Databricks recommends it for all new tables.
| Workload trait | Overhead with partitioning or ZORDER |
|---|---|
| Queries that filter on high cardinality columns | Too many tiny partitions if partitioned on that column |
| Fast growing tables that require maintenance and tuning effort | Recurring manual tuning as the table grows |
| Tables with varied or changing access patterns | Layout fixed by a partition key that no longer fits the queries |
| Tables with concurrent write requirements | Write conflicts; liquid clustering allows row-level concurrency |
A table can only use one of these approaches. Clustering is not compatible with partitioning or ZORDER, so you can't add clustering keys to a partitioned table and keep the partitions. Clustering a table also upgrades its Delta protocol (writer version 7, reader version 3), and protocol versions can't be downgraded. Delta clients that don't support those versions can't read the table.
Checkpoint 1 of 5· Check yourself
A team wants to keep their existing date partitions and add liquid clustering keys on top for extra data skipping. What does Databricks documentation say about this?
A table uses either liquid clustering or partitioning/ZORDER, not both. To adopt clustering, a partitioned table has to be converted.
“Clustering is not compatible with partitioning or ZORDER.”Source: docs.databricks.com
With partitioning, a wrong layout costs a full rewrite, and the bigger the table gets, the more that rewrite costs. With liquid clustering you can change the keys as patterns shift, and only data clustered after the change uses the new keys, so the cost of choosing wrong doesn't grow with the table.
Sources1
2.Enabling clustering and converting partitioned tables
For a new table, add CLUSTER BY to the create statement. For an existing unpartitioned Delta table, use ALTER TABLE <table_name> CLUSTER BY (<clustering_columns>). Clustering is incremental: the default behaviour doesn't recluster data already written. New data gets clustered when OPTIMIZE runs, and OPTIMIZE <table_name> FULL reclusters everything. On a managed table with predictive optimization, you don't have to schedule that OPTIMIZE yourself, because predictive optimization's OPTIMIZE triggers incremental clustering on enabled tables.
CREATE TABLE table1 (col0 INT, col1 STRING) CLUSTER BY (col0);Partitioned tables used to require a full CTAS rewrite to move to clustering. On Databricks Runtime 18.1 and above, ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY converts the table in place with minimal reader and writer downtime, and it works on both managed and external tables. The clause has three forms: explicit columns, no columns (keep the current partition columns as clustering keys), or AUTO. AUTO starts from the partition columns and lets predictive optimization adapt the keys over time, and it is only available for Unity Catalog managed tables. If you choose very different columns from the old partitions, the first OPTIMIZE has to do a large reclustering.
ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id);
OPTIMIZE t1;| Current data optimization technique | Recommendation for clustering keys |
|---|---|
| Hive-style partitioning | Use partition columns as clustering keys. |
| Z-order indexing | Use the ZORDER BY columns as clustering keys. |
| Hive-style partitioning and Z-order | Use both partition columns and ZORDER BY columns as clustering keys. |
| Generated columns to reduce cardinality (for example, date for a timestamp) | Use the original column as a clustering key, and don't create a generated column. |
Checkpoint 2 of 5· Check yourself
An engineer runs ALTER TABLE events CLUSTER BY (customer_id) on an existing unpartitioned table holding two years of data. What happens to the existing rows right away?
Enabling clustering changes how new data is laid out when OPTIMIZE runs. To recluster existing data, you have to run OPTIMIZE FULL.
“The default behavior does not apply clustering to previously written data.”Source: docs.databricks.com
3.CLUSTER BY AUTO: letting the platform choose the keys
Manual liquid clustering still leaves you choosing the keys. CLUSTER BY AUTO hands that choice to Databricks. It's available for Unity Catalog managed Delta tables on Databricks Runtime 15.4 LTS and above, and for materialized views and streaming tables. It depends on predictive optimization for both key selection and clustering, and it runs asynchronously. Databricks reviews the table's historical query workload, chooses candidate columns, and picks new keys if query patterns or data distributions change. Key changes are cost-aware: a change happens only when the predicted savings from better data skipping outweigh the cost of reclustering.
Checkpoint 3 of 5· Fill the gap
Which keyword completes this statement so that Databricks chooses the clustering keys?
CREATE OR REPLACE TABLE table1 (column01 int, column02 string) CLUSTER BY ? ;CLUSTER BY AUTO turns on automatic liquid clustering. CLUSTER BY NONE turns clustering off.
Source: docs.databricks.comYou can turn it on for an existing table, including one with manually chosen keys. To steer the first choice, set keys as hints and then switch to AUTO. To turn it off, use CLUSTER BY NONE, or set explicit columns. Be careful with CREATE OR REPLACE: replacing the table without CLUSTER BY AUTO turns automatic clustering off and drops the selected columns.
ALTER TABLE table1 CLUSTER BY (c1, c2);
ALTER TABLE table1 CLUSTER BY AUTO;Automatic clustering doesn't always choose keys, and that's expected. The table might be too small to benefit, it might already be clustered well (by earlier manual keys or by an insertion order that matches the queries), it might not be queried often, or it might be used from runtimes older than 15.4 LTS. Because the heuristics decide whether clustering is worth the cost, Databricks says you can apply CLUSTER BY AUTO to every managed table whatever its data and query characteristics. To see why keys weren't chosen, open the AUTO LIQUID row marked Not applied in the Catalog Explorer History tab. To check the current state, DESCRIBE TABLE or SHOW TBLPROPERTIES shows clusterByAuto set to true and lists the current clusteringColumns.
Checkpoint 4 of 5· Check yourself
A 50 MB lookup table on Databricks Runtime 16.4 has CLUSTER BY AUTO and predictive optimization enabled, yet no clustering keys have ever been chosen. What is the most likely explanation?
Small tables are one of the documented reasons key selection doesn't happen. The cost-aware heuristics skip it when clustering wouldn't pay off, so nothing needs fixing.
“The table is too small to benefit from liquid clustering.”Source: docs.databricks.com
Checkpoint 5 of 5· Exam question
A newly created Unity Catalog managed table is defined with `CREATE TABLE events (event_id STRING, event_date DATE, payload STRING) CLUSTER BY AUTO;`. Query patterns against this table are expected to shift over the first few months as new dashboards are built. What behavior should the team expect from automatic liquid clustering on this table?
Correct answer: A — Databricks analyzes the historical query workload against the table and selects clustering keys itself, later choosing new keys if query patterns change and the expected performance gain outweighs the reclustering cost.
- A. `CLUSTER BY AUTO` hands key selection to Databricks itself: it inspects the workload's query history to pick candidate clustering columns and later swaps in new keys only when the projected performance benefit justifies the cost of reclustering, adapting as dashboards and access patterns evolve.
- B. `CLUSTER BY AUTO` already engages automatic key selection at creation time, so the table does not sit unclustered awaiting a manual `ALTER TABLE` statement; that command is how explicit, non-automatic clustering keys are set instead.
- C. Clustering keys are not fixed by declaration order and are not permanent; automatic clustering explicitly re-evaluates and can change the chosen keys over the table's lifetime as query patterns shift, without requiring the table to be dropped and recreated.
- D. Liquid clustering organizes data files logically without creating a rigid directory structure per distinct value, which is the Hive-style partitioning behavior it is designed to replace, so it avoids the small-partition and directory-explosion problems that come with high-cardinality static partitioning.
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.You can keep a table's partitions and add liquid clustering keys on top for extra data skipping.Why is that wrong?
Liquid clustering replaces partitioning and ZORDER and can't be combined with either. Partitioned tables are converted with REPLACE PARTITIONED BY WITH CLUSTER BY.
Covered in Why liquid clustering replaces partitioning and ZORDER
2.REPLACE PARTITIONED BY WITH CLUSTER BY AUTO works on any table that the conversion command supports, including external tables.Why is that wrong?
The conversion itself works on external and managed tables, but the AUTO option is restricted to Unity Catalog managed tables, because it depends on predictive optimization.
Covered in Enabling clustering and converting partitioned tables
3.Rerunning CREATE OR REPLACE on a table that already uses CLUSTER BY AUTO keeps its automatically selected keys.Why is that wrong?
Unless the replace statement includes CLUSTER BY AUTO again, automatic clustering is turned off and the selected columns are lost.
Covered in CLUSTER BY AUTO: letting the platform choose the keys
Practise it for real
Hand a managed table's maintenance and clustering key choice to Databricks, then confirm the configuration took effect.
1.Run ALTER TABLE table_name ENABLE PREDICTIVE OPTIMIZATION; on a Unity Catalog managed table you own.
Why: Automatic liquid clustering needs predictive optimization for key selection and for running the clustering.
You should see: The statement succeeds. It fails if the table is external.
2.Run ALTER TABLE table_name CLUSTER BY AUTO; (on an unpartitioned table).
Why: This turns on automatic liquid clustering, so Databricks chooses keys from the table's query history.
You should see: The statement succeeds without rewriting any data.
3.Run SHOW TBLPROPERTIES table_name and look for clusterByAuto and clusteringColumns.
Why: These properties show whether automatic clustering is on and which keys are currently selected.
You should see: clusterByAuto is true. clusteringColumns may be empty until key selection has run.
4.Later, open the table's History tab in Catalog Explorer and filter to Automatic runs.
Why: This shows what predictive optimization actually ran or skipped, with the reasons.
You should see: Rows labelled Auto for operations that ran, or Not applied (for example on AUTO LIQUID) with a skip reason. Results can take up to 24 hours.
Stuck? Get a nudge
If clusteringColumns stays empty on a small or rarely queried table, that's the cost-aware heuristic deciding clustering isn't worth it, not a misconfiguration.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/tables/clusteringOfficial docs
“Unlike traditional partitioning, you can redefine clustering keys without rewriting existing data.”
↩︎ Why liquid clustering replaces partitioning and ZORDER“Fast growing tables that require maintenance and tuning effort.”
↩︎ Why liquid clustering replaces partitioning and ZORDER“Conversion minimizes reader and writer downtime and supports both external and managed tables.”
↩︎ Enabling clustering and converting partitioned tables“Automatic liquid clustering requires predictive optimization for automatic key selection and clustering operations, and runs asynchronously.”
↩︎ CLUSTER BY AUTO: letting the platform choose the keys“Databricks changes clustering keys only when the predicted cost savings from data skipping improvements outweigh the data clustering cost.”
↩︎ CLUSTER BY AUTO: letting the platform choose the keys“If automatic liquid clustering is enabled, the clusterByAuto property is set to true.”
↩︎ CLUSTER BY AUTO: letting the platform choose the keys“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Exam trap 1“Only available for Unity Catalog managed tables.”
↩︎ Exam trap 2“running CREATE OR REPLACE table_name without CLUSTER BY AUTO turns off automatic clustering and does not preserve clustering columns”
↩︎ Exam trap 3“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Checkpoint“The default behavior does not apply clustering to previously written data.”
↩︎ Checkpoint“The table is too small to benefit from liquid clustering.”
↩︎ Checkpoint - 2.
“Triggers incremental clustering for enabled tables.”
↩︎ Enabling clustering and converting partitioned tables
Also cited
- https://docs.databricks.com/aws/en/tables/managedOfficial docs
“For tables with predictive optimization, automatically selects and updates clustering keys as query patterns change to improve performance and lower costs.”
↩︎ Prediction