What you will be able to do
- Explain what liquid clustering replaces and which tables benefit most from it
- Enable liquid clustering with CLUSTER BY in SQL and with the DataFrame clusterBy API
- Predict what happens to existing data when clustering keys change, and when OPTIMIZE or OPTIMIZE FULL is needed
- Convert a partitioned or Z-ordered table to liquid clustering and choose its clustering keys
Key concept
Liquid clustering — A way of laying out a Delta table's data files. It groups rows by a set of clustering keys that you can change at any time. Unlike partition columns, changing the keys never forces a rewrite of the whole table, and OPTIMIZE does the clustering incrementally.
1.What liquid clustering replaces, and why
Before liquid clustering, Databricks had two ways to lay out a table's data. Hive-style partitioning splits the table into directories by column value. ZORDER rewrites files so that related values end up close together. Both are rigid. Once you choose a partition column, every later write follows it, and switching to a different column means rewriting the table. Liquid clustering replaces both approaches. You name one or more clustering keys, and Databricks arranges the data around them. The key difference from partitioning is that the keys are not fixed: "you can redefine clustering keys without rewriting existing data." Your data layout can change as your query patterns change.
Databricks recommends liquid clustering for all new tables, including streaming tables and materialized views. The documentation names the cases where it helps most: queries that filter on high-cardinality columns, tables with heavy data skew, fast-growing tables that need regular maintenance and tuning, tables with concurrent writes, tables whose access patterns vary or change, and tables where a typical partition key would return too many or too few partitions. Liquid clustering is generally available for Delta Lake tables on Databricks Runtime 15.4 LTS and above.
One rule shapes everything else on this page: liquid clustering cannot be combined with the older techniques. A table uses partitioning or ZORDER, or it uses clustering keys, but never a mix. That is why you can enable clustering only on an unpartitioned table or when you create a table, and why a partitioned table needs an explicit conversion (covered below).
Checkpoint 1 of 5· Check yourself
A team wants to keep their daily date partitioning and add liquid clustering on customer_id to speed up lookups. What is the problem with this plan?
Liquid clustering replaces partitioning and ZORDER and cannot be combined with them. You enable it on an unpartitioned table, or you convert the partitioned table.
“Clustering is not compatible with partitioning or ZORDER.”Source: docs.databricks.com
Sources1
2.Enabling clustering with CLUSTER BY
In SQL, you enable liquid clustering by adding a CLUSTER BY clause to the table definition. You can list several columns, and their order in the clause does not matter.
CREATE TABLE table1 (
col0 STRING,
col1 DATE,
col2 BIGINT
)
CLUSTER BY (col0, col1);When you create a table from a query (CTAS), it's easy to put the clause in the wrong place. CLUSTER BY belongs right after the table name, not inside the SELECT. For an existing unpartitioned table, use ALTER TABLE <table_name> CLUSTER BY (<clustering_columns>) instead.
CREATE TABLE table2 CLUSTER BY (col0)
AS SELECT * FROM table1;In Python and Scala, the DataFrame and DeltaTable APIs have a clusterBy method. These APIs come with a restriction: you can set clustering columns only when you create a table or write in overwrite mode. In append mode you cannot change the keys. To change keys on a table you append to, run a separate SQL ALTER TABLE outside your write. Also note the protocol requirement: clustered Delta tables use writer version 7 and reader version 3, so older Delta clients cannot read them, and you cannot downgrade the protocol.
Checkpoint 2 of 5· Fill the gap
Which DataFrameWriter method creates table2 with liquid clustering on col0?
df = spark.read.table("table1")
df.write. ? ("col0").saveAsTable("table2")clusterBy sets liquid clustering keys. partitionBy would create a Hive-style partitioned table, which is the layout liquid clustering replaces.
Source: docs.databricks.com3.OPTIMIZE does the clustering, and key changes are not retroactive
Setting or changing clustering keys only records metadata. The data is reorganized by OPTIMIZE: on a clustered table, OPTIMIZE rewrites data files to group rows by the clustering keys. It works incrementally, clustering data that has not been clustered yet. That is why the documentation tells you to run OPTIMIZE jobs after enabling clustering. Data written before the keys were set or changed keeps its existing layout. To recluster it, use the force form: OPTIMIZE <table_name> FULL, or OPTIMIZE <table_name> FULL WHERE <predicate> to limit it to a subset. This form is available in Databricks Runtime 16.0 and above.
You do not always have to run OPTIMIZE yourself. Predictive optimization automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables, which removes the manual maintenance. For clustered tables, the OPTIMIZE it runs triggers incremental clustering. It applies only to Unity Catalog managed tables: external tables are not covered.
Two more CLUSTER BY options matter here. CLUSTER BY NONE turns clustering off for a table: new or updated data is no longer clustered by OPTIMIZE. And you cannot use ALTER TABLE to change the clustering columns of materialized views or streaming tables. You set those in the definition of the view or streaming table.
Checkpoint 3 of 5· Check yourself
An engineer changes a clustered table's keys and wants all historical files, not just new data, reorganized by the new keys. Which command does that?
Normal OPTIMIZE clusters only data that has not been clustered yet. OPTIMIZE FULL forces previously written data to be reclustered. ZORDER cannot be used with liquid clustering.
“To force reclustering, use OPTIMIZE <table_name> FULL or OPTIMIZE <table_name> FULL WHERE <predicate>.”Source: docs.databricks.com
4.Migrating partitioned and Z-ordered tables
Most existing tables use partitioning, ZORDER, or both. When you move such a table to liquid clustering, Databricks recommends reusing the columns the old layout already relied on, because your queries were written around them.
| Current technique | Choose as clustering keys |
|---|---|
| Hive-style partitioning | The partition columns |
| Z-order indexing | The ZORDER BY columns |
| Partitioning and Z-order | Both the partition columns and the ZORDER BY columns |
| Generated column to reduce cardinality (e.g. date from a timestamp) | The original column; don't create the generated column |
In Databricks Runtime 18.1 and above, you convert a partitioned Delta table in place with ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY. The conversion keeps reader and writer downtime to a minimum and works on both managed and external tables. If you list no columns, the current partition columns become the clustering keys. You can also name different columns, but keys that differ a lot from the old partitions trigger a large reclustering on the first OPTIMIZE. Either way, OPTIMIZE is what actually reorganizes the data. To confirm the conversion, run DESCRIBE EXTENDED to see the new clustering columns.
ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id);
OPTIMIZE t1;The conversion command also accepts AUTO instead of a column list: REPLACE PARTITIONED BY WITH CLUSTER BY AUTO. This starts from the current partition columns as the initial clustering columns and lets predictive optimization adapt them over time. AUTO is available only for Unity Catalog managed tables, so a converted external table must use an explicit column list or no list at all.
After conversion, the old partition columns become hierarchical clustering keys. Hierarchical clustering (Databricks Runtime 17.1 and above) organizes data by the highest-priority key first and then by each following key. Any remaining clustering keys are standard clustering keys. It is configured with the delta.liquid.hierarchicalClusteringColumns table property, and every column in that property must also be a clustering key. The order in the property matters: the first column has the highest priority. It works best with low-cardinality columns, which is why former partition columns fit it well. When you list several hierarchical keys, order them from lower to higher cardinality. Keep high-cardinality columns such as IDs as standard clustering keys. If you set hierarchical clustering manually, predictive optimization respects it and does not override it. For example, a table converted from partitions (p1, p2) and converted with CLUSTER BY (p1, p2, c1, c2) gets p1 and p2 as hierarchical keys and c1 and c2 as standard keys.
Checkpoint 4 of 5· Match them up
Match each existing layout to the clustering keys Databricks recommends when migrating
Tap a term, then the definition that fits it.
Migration keeps the columns your queries already filter on. A generated column was only needed to reduce cardinality for partitioning, and liquid clustering doesn't need it.
“Use the original column as a clustering key, and don't create a generated column.”Source: docs.databricks.com
Sources1
5.Automatic liquid clustering with CLUSTER BY AUTO
Instead of naming keys yourself, you can write CLUSTER BY AUTO and let Databricks choose them. Databricks analyzes the table's historical query workload and picks the best candidate columns. If your query patterns or data distributions change, it can select new keys. It changes keys only when the predicted savings from better data skipping outweigh the cost of reclustering.
Automatic liquid clustering is for Unity Catalog managed tables and requires predictive optimization, which does the key selection and the clustering asynchronously. It may choose no keys, for example when the table is too small, already has an effective layout, or isn't queried often. To turn it on for an existing table, run ALTER TABLE table1 CLUSTER BY AUTO. To turn it off, run ALTER TABLE table1 CLUSTER BY NONE.
Checkpoint 5 of 5· Exam question
A team creates a new Delta table with `CLUSTER BY AUTO` to support a Lakeflow Spark Declarative Pipeline that ingests continuously via Auto Loader. Over several months, downstream analysts' filters shift from `customer_id` to `region` and `order_date`. What should the team expect regarding the table's clustering keys?
Correct answer: B — Databricks continuously evaluates the observed query workload and updates the clustering keys automatically once the predicted performance gain outweighs the cost of reclustering the data.
- A. This describes manually specified `CLUSTER BY` behavior, not `CLUSTER BY AUTO`. With automatic liquid clustering, Databricks selects and revises keys itself instead of requiring a manual `ALTER TABLE` statement.
- B. Automatic liquid clustering analyzes historical query patterns and adapts clustering keys over time, only switching keys when the predicted benefit justifies the reclustering cost, so no manual intervention is needed.
- C. Automatic liquid clustering is independent of ZORDER; liquid clustering and ZORDER are in fact incompatible on the same table, so key selection cannot depend on ZORDER statistics.
- D. `OPTIMIZE FULL` forces a complete rewrite of existing data to current keys, but it is not a prerequisite for the adaptive optimizer to begin considering new candidate keys based on workload changes.
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Running ALTER TABLE ... CLUSTER BY reorganizes the existing data immediately.Why is that wrong?
Changing keys only updates metadata. OPTIMIZE clusters new data incrementally, and previously written data is reclustered only with OPTIMIZE FULL.
Covered in OPTIMIZE does the clustering, and key changes are not retroactive
2.Liquid clustering can be layered on top of partitions or combined with ZORDER BY for extra benefit.Why is that wrong?
Liquid clustering replaces both techniques and cannot be used with either. A partitioned table has to be converted first.
Covered in What liquid clustering replaces, and why
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.”
↩︎ What liquid clustering replaces, and why“Queries that filter on high cardinality columns.”
↩︎ What liquid clustering replaces, and why“You can enable liquid clustering on an existing unpartitioned table or during table creation.”
↩︎ What liquid clustering replaces, and why“CLUSTER BY must appear after the table name, not in the SELECT clause”
↩︎ Enabling clustering with CLUSTER BY“You cannot change clustering keys when using append mode.”
↩︎ Enabling clustering with CLUSTER BY“Delta Lake tables with liquid clustering enabled use Delta writer version 7 and reader version 3.”
↩︎ Enabling clustering with CLUSTER BY“After enabling liquid clustering, run OPTIMIZE jobs to incrementally cluster data.”
↩︎ OPTIMIZE does the clustering, and key changes are not retroactive“use REPLACE PARTITIONED BY WITH CLUSTER BY in an ALTER TABLE statement”
↩︎ Migrating partitioned and Z-ordered tables“Using very different columns triggers a large reclustering operation on the first OPTIMIZE run.”
↩︎ Migrating partitioned and Z-ordered tables“To benefit from altering clustering columns, you must run OPTIMIZE.”
↩︎ Migrating partitioned and Z-ordered tables“Uses the current partition columns as the initial clustering columns and lets predictive optimization adapt over time.”
↩︎ Migrating partitioned and Z-ordered tables“Only available for Unity Catalog managed tables.”
↩︎ Migrating partitioned and Z-ordered tables“When you convert a partitioned table to liquid clustering, the partition columns are set as the new hierarchical clustering keys.”
↩︎ Migrating partitioned and Z-ordered tables“Every column in the property must also be a clustering key.”
↩︎ Migrating partitioned and Z-ordered tables“For multiple hierarchical clustering keys, order them from lower cardinality to higher cardinality.”
↩︎ Migrating partitioned and Z-ordered tables“Databricks recommends keeping high-cardinality columns as standard clustering keys rather than hierarchical clustering keys.”
↩︎ Migrating partitioned and Z-ordered tables“To verify that a conversion was successful, run DESCRIBE EXTENDED to see the new clustering columns.”
↩︎ Migrating partitioned and Z-ordered tables“Automatic liquid clustering requires predictive optimization for automatic key selection and clustering operations, and runs asynchronously.”
↩︎ Automatic liquid clustering with CLUSTER BY AUTO“If your query patterns or data distributions change over time, automatic liquid clustering selects new keys to optimize performance.”
↩︎ Automatic liquid clustering with CLUSTER BY AUTO“Databricks changes clustering keys only when the predicted cost savings from data skipping improvements outweigh the data clustering cost.”
↩︎ Automatic liquid clustering with CLUSTER BY AUTO“Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER.”
↩︎ Key concept“The default behavior does not apply clustering to previously written data.”
↩︎ Exam trap 1“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Exam trap 2“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Checkpoint“To force reclustering, use OPTIMIZE <table_name> FULL or OPTIMIZE <table_name> FULL WHERE <predicate>.”
↩︎ Checkpoint“Use the original column as a clustering key, and don't create a generated column.”
↩︎ Checkpoint - 2.
“The column order does not matter.”
↩︎ Enabling clustering with CLUSTER BY“You cannot change the clustering columns of materialized views or streaming tables with ALTER TABLE.”
↩︎ OPTIMIZE does the clustering, and key changes are not retroactive“Turns off clustering for the relation being altered.”
↩︎ OPTIMIZE does the clustering, and key changes are not retroactive“To cluster rows with altered clustering columns, you must run OPTIMIZE. Note that rows clustered by previous clustering columns are not affected.”
↩︎ Prediction - 3.
“For tables with liquid clustering enabled, OPTIMIZE rewrites data files to group data by liquid clustering keys.”
↩︎ OPTIMIZE does the clustering, and key changes are not retroactive - 4.
“Predictive optimization automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables”
↩︎ OPTIMIZE does the clustering, and key changes are not retroactive“Triggers incremental clustering for enabled tables.”
↩︎ OPTIMIZE does the clustering, and key changes are not retroactive