What you will be able to do
- Match partitioning, ZORDER, and liquid clustering to the column cardinality and filter patterns each one handles
- Explain the limits of ZORDER: it works only within partitions, cannot use partition columns, and becomes less effective with each added column
- Choose liquid clustering for skewed, concurrently written, or changing workloads, and know what happens to existing data when clustering keys change
1.Column cardinality decides which technique fits
After table size, the next question is which columns your queries filter on and how many distinct values those columns have. Partitioning creates one partition per value, so it only works well for low- or known-cardinality fields, such as a date or a physical location. It is a poor fit for high-cardinality fields like timestamps, because they would produce a huge number of tiny partitions.
ZORDER works the other way round. It is meant for columns that appear often in query predicates and have many distinct values, and it handles fields that keep gaining new values, like a customer ID in an orders table. Liquid clustering covers both ends. It works for low- and high-cardinality columns, without the fixed partition boundaries and small-file problems of static partitioning. Filtering on high-cardinality columns is one of the scenarios where Databricks says clustering particularly helps.
| Technique | Column cardinality it suits | Key constraint |
|---|---|---|
| Partitioning | Low or known cardinality (e.g. date fields, physical locations) | Not for high-cardinality fields such as timestamps; partition columns must be top-level |
| ZORDER (via OPTIMIZE) | High cardinality, including fields that grow infinitely | Only within a partition; cannot Z-order on partition columns |
| Liquid clustering | Both low and high cardinality | Not compatible with partitioning or ZORDER |
Checkpoint 1 of 6· Match them up
Match each layout technique to the column it is designed to handle
Tap a term, then the definition that fits it.
Partitioning breaks down as cardinality rises. ZORDER covers high cardinality but only inside partitions. Liquid clustering covers both without fixed boundaries.
“Partitioning works well only for low or known cardinality fields”Source: docs.databricks.com
2.Where ZORDER runs out
ZORDER is not a table property. It is a clause you add to the OPTIMIZE command, which rewrites files so that related values end up in the same set of files. Databricks data skipping then uses the per-file statistics to avoid reading files that cannot match a filter. The example below Z-orders only the last day of an events table on eventType.
OPTIMIZE events
WHERE date >= current_timestamp() - INTERVAL 1 day
ZORDER BY (eventType)Checkpoint 2 of 6· Fill the gap
Which keyword completes this command so that it colocates eventType values while rewriting files?
OPTIMIZE events
WHERE date >= current_timestamp() - INTERVAL 1 day
? BY (eventType)ZORDER BY is the OPTIMIZE clause for colocating data. CLUSTER BY is the liquid clustering clause in table definitions, and it cannot be combined with ZORDER.
Source: docs.databricks.comZORDER has several limits that make it a weaker tool than liquid clustering for many query patterns. On a partitioned table, files cannot be combined across partition boundaries, so Z-ordering only happens inside each partition. You also cannot Z-order on the partition columns themselves.
You can list multiple ZORDER columns, but the docs warn that effectiveness drops with each extra column. A ZORDER column is only useful if statistics are collected for it. Without statistics, Z-ordering is ineffective and wastes compute. Z-ordering is also not idempotent: running it again on data that has changed rewrites that data, and the run time is not guaranteed to shrink over repeated runs. Finally, ZORDER cannot be used at all on a table with liquid clustering. Databricks recommends liquid clustering over Z-ordering for new tables.
Checkpoint 3 of 6· Check yourself
A table is partitioned by date and Z-ordered by user_id. Which statement about this layout is correct?
Files cannot be combined across partition boundaries, so Z-order clustering stays inside each partition. You also cannot Z-order on a partition column, and liquid clustering cannot be combined with partitioning or ZORDER.
“You cannot combine files across partition boundaries, and so Z-order clustering can only occur within a partition.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
A 50 TB Delta table `sales_history` was originally partitioned by `ingest_date` (roughly 1,800 daily partitions) and Z-Ordered by `customer_id` within each partition to speed up point lookups by customer. The rigid daily directories now cause small-file problems on low-volume days, and query planning must prune 1,800 partition directories before the Z-Order ordering helps. The team wants a supported approach that keeps fast `customer_id` lookups while removing the small-file overhead from the daily directories. Which approach should they take?
Correct answer: A — Drop the daily partitioning and run `ALTER TABLE sales_history CLUSTER BY (customer_id, ingest_date)` followed by `OPTIMIZE sales_history FULL` to recluster the existing data under liquid clustering.
- A. Registering liquid clustering keys and forcing a full recluster reorganizes the existing files around `customer_id` and `ingest_date` without rigid partition directories, resolving the small-file issue while preserving fast customer lookups.
- B. Running the Z-Order job more often still operates within the existing 1,800 rigid partition boundaries, so the small-file fragmentation on low-volume days and the partition-pruning overhead both remain.
- C. Partitioning by an extremely high-cardinality column like `customer_id` would create an unmanageable number of tiny partition directories, making the small-file problem far worse rather than solving it.
- D. The disk cache only accelerates repeated reads of already-cached files on a given cluster; it does not reorganize data layout on disk and does not replace clustering or partitioning for query pruning.
Sources2
3.Skew, concurrent writes, and access patterns that change
Cardinality is not the only factor. The Databricks docs list workload traits where liquid clustering is the better choice:
- Data with heavy skew. Static partitioning makes you pick partition columns up front, and uneven values then produce uneven partitions. Liquid clustering is described as skew-resistant. - Fast-growing tables that would otherwise need ongoing maintenance and tuning. - Tables with concurrent writes. Liquid clustering supports row-level concurrency, which reduces write conflicts. - Tables where a typical partition key would produce too many or too few partitions. - Tables whose access patterns vary or change over time.
The last point is where liquid clustering differs most from partitioning. To change a partition scheme, you have to rewrite the table. To change clustering keys, you run ALTER TABLE ... CLUSTER BY, and the key order does not matter. The catch is that changing the keys does not reorganize anything on its own. Only rows clustered by the next OPTIMIZE run follow the new keys, and rows already clustered by the old keys stay as they were.
If you would rather not choose keys at all, CLUSTER BY AUTO on Unity Catalog managed tables lets Databricks pick the clustering columns and adjust them as the workload changes.
Checkpoint 5 of 6· Put it in order
Put these steps in order to add a second clustering dimension to a table and have new layout work use it
- 1.ALTER TABLE t CLUSTER BY (a, b);
- 2.CREATE TABLE t(a int, b string) CLUSTER BY (a);
- 3.OPTIMIZE t;
First create the table with clustering, then change the clustering columns with ALTER TABLE. Rows are only clustered by the new columns when OPTIMIZE runs afterwards.
“To cluster rows with altered clustering columns, you must run OPTIMIZE.”Source: docs.databricks.com
No. Rows clustered by the previous clustering columns are not affected. To recluster data that was already clustered, use OPTIMIZE t FULL on Databricks Runtime 16.0 and above.
4.Moving a partitioned table to liquid clustering
If a table's size or query patterns show its partitioning is wrong, Databricks Runtime 18.1 and above can convert it in place with ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY. The listed benefits are the same points covered above: better performance for tables with poor data skipping or over-partitioning, keys that are easy to change instead of a rigid scheme, and fewer write conflicts thanks to row-level concurrency.
The example below converts a table partitioned on (year, month, day) to clustering on different columns. Databricks recommends keeping the new clustering columns close to the old partition columns. Very different columns trigger a large reclustering job on the first OPTIMIZE run.
ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id);
OPTIMIZE t1;Checkpoint 6 of 6· Exam question
A streaming table ingests high-volume clickstream events and is queried concurrently by many downstream jobs filtering on `user_id` (extremely high cardinality) and `event_type` (low cardinality, about 20 values). Query patterns shift week to week as new dashboards are added. Which characteristics make liquid clustering, configured with `CLUSTER BY AUTO`, a better fit than a legacy layout that partitions by `event_type` and Z-Orders by `user_id`? (Select all that apply.)(Select 3)
Correct answers: A, B, C — Databricks can automatically change the clustering keys as the observed query workload shifts, without requiring the table to be dropped and recreated.; Databricks can automatically change the clustering keys as the query workload shifts over time, without ever dropping and recreating the underlying table.; `OPTIMIZE` reclusters only the data that needs it incrementally, instead of requiring a full table rewrite every time new clickstream events are appended.
- A. This is accurate: `CLUSTER BY AUTO` lets Databricks pick and revise clustering keys based on observed query history as access patterns evolve, without a table rebuild.
- B. This is accurate: without fixed partition directories, concurrent writers are not funneled into contention over a small set of `event_type` folders the way rigid partitioning would force.
- C. This is accurate: standard `OPTIMIZE` on a liquid-clustered table only rewrites data that needs reclustering, rather than rewriting the whole table on every run like a full Z-Order pass would.
- D. This overstates the guarantee; liquid clustering improves data skipping through better file organization, but it does not guarantee single-file reads for every filtered query.
- E. This is backwards; `CLUSTER BY AUTO` relies on Databricks continuously analyzing query history to decide whether and how to adjust clustering keys over time.
- F. Z-Order statistics are only maintained within each partition, so the optimizer cannot use them to skip data across `event_type` partition boundaries, limiting global data skipping.
Sources3
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.On a partitioned table, ZORDER colocates data across the whole table, and you can add the partition column to ZORDER BY for extra skipping.Why is that wrong?
Z-ordering only happens within each partition, and partition columns cannot be Z-order columns.
Covered in Where ZORDER runs out
2.Running ALTER TABLE ... CLUSTER BY with new columns immediately reorganizes all existing data by the new keys.Why is that wrong?
Changing the keys rewrites nothing by itself. OPTIMIZE has to run afterwards, and rows already clustered by the old columns stay as they were.
Covered in Skew, concurrent writes, and access patterns that change
3.Partitioning by a high-cardinality column such as a timestamp is a good way to speed up filters on that column.Why is that wrong?
Partitioning only works well for low- or known-cardinality fields. High-cardinality filter columns are a reason to choose liquid clustering.
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/partitionsOfficial docs
“Liquid clustering works for both low and high cardinality columns and avoids the fixed partition boundaries and small-file issues common with static partitioning.”
↩︎ Column cardinality decides which technique fits“You cannot Z-order on fields used for partitioning.”
↩︎ Exam trap 1“but not for fields with high cardinality such as timestamps”
↩︎ Exam trap 3“Z-order works for all fields, including high cardinality fields and fields that may grow infinitely”
↩︎ Prediction“Partitioning works well only for low or known cardinality fields”
↩︎ Checkpoint“You cannot combine files across partition boundaries, and so Z-order clustering can only occur within a partition.”
↩︎ Checkpoint - 2.
“if that column has high cardinality (that is, a large number of distinct values), then use ZORDER BY.”
↩︎ Column cardinality decides which technique fits“However, the effectiveness drops with each extra column.”
↩︎ Where ZORDER runs out“Databricks recommends that you don't use ZORDER BY on columns that don't have statistics collected because it is ineffective and uses unnecessary compute resources.”
↩︎ Where ZORDER runs out“Z-ordering is not idempotent but aims to be an incremental operation.”
↩︎ Where ZORDER runs out“You can't use ZORDER in combination with liquid clustering.”
↩︎ Where ZORDER runs out - 3.https://docs.databricks.com/aws/en/tables/clusteringOfficial docs
“Queries that filter on high cardinality columns.”
↩︎ Column cardinality decides which technique fits“Tables with varied or changing access patterns.”
↩︎ Skew, concurrent writes, and access patterns that change“Performance improvements for tables that suffer from poor data-skipping or over-partitioning.”
↩︎ Moving a partitioned table to liquid clustering“Clustering columns are flexible and simple to alter, whereas partitioning is rigid and difficult to alter.”
↩︎ Moving a partitioned table to liquid clustering“Using very different columns triggers a large reclustering operation on the first OPTIMIZE run.”
↩︎ Moving a partitioned table to liquid clustering - 4.
“Static partitioning requires you to select partition columns and reorganize data up front, which can cause data skew for unevenly distributed values.”
↩︎ Skew, concurrent writes, and access patterns that change“Liquid clustering is self-tuning, skew-resistant, and incremental, rewriting only the data that needs reorganization on each run.”
↩︎ Skew, concurrent writes, and access patterns that change - 5.
“Directs Delta Lake to automatically determine and over time adapt to the best columns to cluster by.”
↩︎ Skew, concurrent writes, and access patterns that change“Note that rows clustered by previous clustering columns are not affected.”
↩︎ Exam trap 2“To cluster rows with altered clustering columns, you must run OPTIMIZE.”
↩︎ Checkpoint