What you will be able to do
- Pick liquid clustering instead of partitioning or ZORDER, based on how a table is filtered, how big it is, and how its query patterns change
- Decide between explicit clustering keys and CLUSTER BY AUTO, given whether the table is a Unity Catalog managed table and whether predictive optimization runs on it
- Change clustering keys or convert a partitioned table, and know when OPTIMIZE is needed before the new layout takes effect
Key concept
Liquid clustering — A Delta data layout that groups data files by clustering keys you can change at any time. It is the recommended replacement for both Hive-style partitioning and ZORDER. With CLUSTER BY AUTO, Databricks picks those keys for you.
1.What liquid clustering replaces, and why
Partitioning and ZORDER have one weakness in common: the layout is fixed once you choose it. A partitioned table splits data into directories by partition value. If the partition column turns out to be wrong, the docs warn that fixing it requires a full rewrite of the data, which can be very expensive and slow for large tables. ZORDER sorts data only when you run OPTIMIZE with a ZORDER BY clause, and it gets less effective with each extra column you add.
Liquid clustering fixes the rigidity problem. You declare clustering keys with CLUSTER BY, and OPTIMIZE groups files by those keys incrementally. You can redefine the keys later without rewriting existing data, so the layout can change as your queries change. Liquid clustering is generally available for Delta Lake tables on Databricks Runtime 15.4 LTS and above. Databricks recommends it for all new tables, including streaming tables and materialized views.
The tradeoff is that you can't mix it with the older techniques. A table is either liquid-clustered or partitioned/Z-ordered. Clustered tables also use Delta writer version 7 and reader version 3, so Delta clients that don't support those protocols can't read them, and you can't downgrade the protocol.
CREATE TABLE table2 CLUSTER BY (col0)
AS SELECT * FROM table1;Checkpoint 1 of 7· Check yourself
Six months after launch, analysts stop filtering a liquid-clustered table by region and start filtering by customer_id. What does changing the layout cost?
Being able to redefine clustering keys without rewriting data is what separates liquid clustering from partitioning. Only data that OPTIMIZE processes later is laid out by the new keys.
“Unlike traditional partitioning, you can redefine clustering keys without rewriting existing data.”Source: docs.databricks.com
2.Access patterns that call for liquid clustering
On the exam you'll get a scenario that describes how a table is queried and written, and you have to name the right layout. The clustering docs list the patterns that benefit most from liquid clustering. Each one is a case where a fixed partition scheme either fails or needs constant tuning.
| Access pattern | Why partitioning struggles |
|---|---|
| Queries that filter on high cardinality columns | Partitioning works well only for low or known cardinality fields, not for high-cardinality fields such as timestamps |
| Tables with heavy data skew | Some partition values hold far more data than others |
| Fast growing tables that require maintenance and tuning effort | The partition scheme has to be re-tuned as the table grows |
| Tables with concurrent write requirements | Liquid clustering allows row-level concurrency, which reduces write conflicts |
| Tables with varied or changing access patterns | Partition columns are rigid and difficult to alter |
| A typical partition key returns too many or too few partitions | Many small partitions, each under 1 GB, hurt performance |
Table size is the other input. The partitioning guidance gives explicit thresholds. Under 1 TB, don't partition at all. From 1 TB to 100 TB, use liquid clustering instead. At 100 TB or more, partitioning *might* help, but Databricks still says to try liquid clustering first and confirm any gain. Any partitioning you do keep should have at least 1 GB of data per partition.
Two more details tend to settle close calls. Unpartitioned Delta tables already get ingestion time clustering automatically, which performs about as well as date-based partitioning without any tuning. And to skip data on a struct field, liquid clustering is the only option, because partition columns must be top-level columns.
Checkpoint 2 of 7· Check yourself
A 40 TB events table is filtered mostly on user_id, a column with millions of distinct values. Which layout fits the documented guidance?
The table falls in the 1–100 TB band, where the guidance is to use liquid clustering instead of partitioning. Filtering on a high-cardinality column is also one of the patterns liquid clustering is designed for.
“With more than 1 TB to 100 TB of data, use liquid clustering instead of partitioning.”Source: docs.databricks.com
Checkpoint 3 of 7· Exam question
A team manages a 50 TB fact table in Unity Catalog that backs several dashboards. The set of columns each dashboard filters on changes every few weeks as new dashboards are added, and nobody can commit to a fixed set of "important" columns for the next quarter. Which optimization strategy best fits this access pattern?
Correct answer: B — Use `CLUSTER BY AUTO` so Databricks can select and revise the table's clustering keys automatically as query patterns shift.
- A. Static Z-ORDER columns require someone to manually pick and periodically rewrite the key list, so the layout does not adapt on its own as filter patterns keep shifting.
- B. Automatic clustering continuously reassesses which columns are worth clustering on from observed workload, so it keeps adapting without anyone re-declaring keys each time dashboards change.
- C. Partitioning by ingestion date locks in a fixed physical layout, and swapping the Z-ORDER column weekly still requires manual intervention and a full rewrite each time.
- D. A manually fixed key list chosen for this month's dashboards goes stale the moment query patterns shift again, defeating the goal of an access pattern that keeps changing.
3.Explicit keys or CLUSTER BY AUTO
The CLUSTER BY clause has three forms. A column list sets explicit keys, and their order doesn't matter. AUTO lets Delta choose the columns and keep adjusting them over time. NONE turns clustering off for the table being altered.
Explicit keys fit when you know the filter columns and they're stable. If one column dominates, for example finding the latest date per category, you can prioritize it with hierarchical clustering. The docs warn that prioritizing a high-cardinality column can make your other keys less effective, so check that tradeoff first.
CLUSTER BY AUTO fits when query patterns vary or keep changing and you don't want to keep re-picking keys. It has hard requirements. It's available for Unity Catalog managed Delta tables on Databricks Runtime 15.4 LTS and above, and it depends on predictive optimization. Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE automatically on managed tables. It shows its key evaluation as an AUTO_CLUSTERING_COLUMN_SELECTION operation and may choose new keys before clustering data. It doesn't run on external tables or on tables loaded as OpenSharing recipients. It also doesn't help Z-ordered tables, because predictive optimization never runs ZORDER and ignores Z-ordered files.
CREATE TABLE table1 (col0 INT, col1 STRING) CLUSTER BY (col0);Checkpoint 4 of 7· Match them up
Match each CLUSTER BY option to what it does
Tap a term, then the definition that fits it.
These are the three forms of the CLUSTER BY clause in the SQL reference. AUTO is the one that hands key selection to the platform.
“Directs Delta Lake to automatically determine and over time adapt to the best columns to cluster by.”Source: docs.databricks.com
Checkpoint 5 of 7· Exam question
A 400 GB `orders` table is filtered heavily on `customer_id`, a high-cardinality column with a skewed distribution: a small number of customers account for a large share of the rows. The nightly `OPTIMIZE ... ZORDER BY (customer_id)` job is taking longer every week as the table grows. Which change should the team make?
Correct answer: C — Recreate the table with `CLUSTER BY (customer_id)` so the file layout is maintained incrementally as the skewed data grows.
- A. Adding a second Z-ORDER column does not solve the underlying cost problem, since a Z-ORDER `OPTIMIZE` still rewrites the full set of touched files on every run regardless of how many columns are listed.
- B. Partitioning by a high-cardinality, skewed column like `customer_id` creates an excessive number of small partitions, which drives up file-listing overhead and small-file problems instead of fixing the rewrite cost.
- C. Liquid clustering with `customer_id` declared as a key incrementally maintains the layout as new data arrives, avoiding the full-table rewrite cost that a growing skewed table incurs under Z-ORDER.
- D. Given how heavily queries filter on `customer_id`, automatic clustering would likely select that same column as a key rather than avoid it, so this does not describe expected behavior.
4.Changing keys and converting partitioned tables
Changing keys is a metadata operation. Run ALTER TABLE ... CLUSTER BY with the new columns. Rows that were already clustered stay as they are, and only an OPTIMIZE run lays data out by the new keys. By default, OPTIMIZE also doesn't recluster previously written data. To force that, use OPTIMIZE <table_name> FULL, available on Databricks Runtime 16.0 and above. Clustering keys of materialized views and streaming tables can't be changed with ALTER TABLE.
For partitioned tables, Databricks Runtime 18.1 and above adds an in-place conversion, ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY. With a column list, it clusters on those columns. The docs recommend keeping them close to the old partition columns, because very different columns trigger a large reclustering on the first OPTIMIZE. With AUTO, it starts from the current partition columns and lets predictive optimization adapt them; this works only on Unity Catalog managed tables. With no option, it keeps the current partition columns as the clustering keys.
ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id);
OPTIMIZE t1;Checkpoint 6 of 7· Fill the gap
A Unity Catalog managed table is partitioned, and its query patterns change often. Which option starts from the current partition columns and lets predictive optimization adapt the keys?
ALTER TABLE t2 REPLACE PARTITIONED BY WITH CLUSTER BY ? ;AUTO uses the current partition columns as the initial clustering keys and hands later key selection to predictive optimization. NONE turns clustering off, and an explicit list fixes the keys.
Source: docs.databricks.comCheckpoint 7 of 7· Check yourself
You run ALTER TABLE t CLUSTER BY (a, b) on a table that was clustered by a. Queries filtering on b aren't faster yet. Why?
ALTER only changes the clustering definition. Rows clustered under the previous keys aren't affected until OPTIMIZE runs, or OPTIMIZE FULL for a complete recluster.
“To cluster rows with altered clustering columns, you must run OPTIMIZE.”Source: docs.databricks.com
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.You can add liquid clustering to a partitioned or Z-ordered table to combine the benefits.Why is that wrong?
Liquid clustering can't be combined with partitioning or ZORDER. A partitioned table has to be converted with REPLACE PARTITIONED BY WITH CLUSTER BY, or recreated.
Covered in What liquid clustering replaces, and why
2.CLUSTER BY AUTO works on any Delta table, including external tables.Why is that wrong?
Automatic key selection runs through predictive optimization, which handles only Unity Catalog managed tables and does not run on external tables.
Covered in Explicit keys or CLUSTER BY AUTO
3.Running ALTER TABLE ... CLUSTER BY with new columns immediately reorganizes the existing data.Why is that wrong?
ALTER only changes the definition, and rows clustered under the previous keys are left as they are. OPTIMIZE, or OPTIMIZE FULL for a full recluster, applies the new keys.
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
“Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER.”
↩︎ What liquid clustering replaces, and why“Delta Lake tables with liquid clustering enabled use Delta writer version 7 and reader version 3.”
↩︎ What liquid clustering replaces, and why“Queries that filter on high cardinality columns.”
↩︎ Access patterns that call for liquid clustering“Automatic liquid clustering allows Databricks to intelligently choose clustering keys to optimize query performance, using the CLUSTER BY AUTO clause.”
↩︎ Explicit keys or CLUSTER BY AUTO“Prioritizing a high-cardinality column can reduce clustering effectiveness for your other clustering keys”
↩︎ Explicit keys or CLUSTER BY AUTO“AUTO: Uses the current partition columns as the initial clustering columns and lets predictive optimization adapt over time.”
↩︎ Changing keys and converting partitioned tables“To force reclustering, use OPTIMIZE <table_name> FULL or OPTIMIZE <table_name> FULL WHERE <predicate>.”
↩︎ Changing keys and converting partitioned tables“Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER.”
↩︎ Key concept“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Exam trap 1“In Databricks Runtime 15.4 LTS and above, you can enable automatic liquid clustering for Unity Catalog managed Delta Lake tables.”
↩︎ Exam trap 2“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Prediction“Unlike traditional partitioning, you can redefine clustering keys without rewriting existing data.”
↩︎ Checkpoint“Automatic liquid clustering requires predictive optimization for automatic key selection and clustering operations, and runs asynchronously.”
↩︎ Prediction - 2.https://docs.databricks.com/aws/en/tables/partitionsOfficial docs
“An ineffective partitioning strategy might negatively affect query performance and require a full rewrite of data to fix.”
↩︎ What liquid clustering replaces, and why“With 100 TB or more of data, partitioning might improve performance, but Databricks recommends using liquid clustering first and verifying performance improvements.”
↩︎ Access patterns that call for liquid clustering“Liquid clustering is the only way to data-skip on a struct field without first extracting it into a top-level column.”
↩︎ Access patterns that call for liquid clustering“With more than 1 TB to 100 TB of data, use liquid clustering instead of partitioning.”
↩︎ Checkpoint - 3.
“OPTIMIZE does not run ZORDER when executed by predictive optimization.”
↩︎ Explicit keys or CLUSTER BY AUTO
Also cited
“Note that rows clustered by previous clustering columns are not affected.”
↩︎ Exam trap 3“Directs Delta Lake to automatically determine and over time adapt to the best columns to cluster by.”
↩︎ Checkpoint“To cluster rows with altered clustering columns, you must run OPTIMIZE.”
↩︎ Checkpoint