What you will be able to do
- Create a clustered table with CREATE TABLE ... CLUSTER BY, including the CTAS form
- Enable or change clustering keys on an existing table and know when OPTIMIZE must run
- Choose between explicit clustering columns, AUTO and NONE
- Convert a partitioned table to liquid clustering and verify the result
1.Creating a clustered table
Liquid clustering lays out a Delta table's data files by the clustering keys you choose, so queries that filter on those keys can skip files. In SQL, you enable it with the CLUSTER BY clause. The simplest case is a new, empty table, where you list the clustering columns after the column definitions.
CREATE TABLE table1 (col0 INT, col1 STRING) CLUSTER BY (col0);You can name more than one key. Pick the columns your queries filter on most.
CREATE TABLE table1 (
col0 STRING,
col1 DATE,
col2 BIGINT
)
CLUSTER BY (col0, col1);Analysts often build a table from a query result. In a CREATE TABLE ... AS SELECT (CTAS) statement, CLUSTER BY goes right after the new table's name, not inside the SELECT.
CREATE TABLE table2 CLUSTER BY (col0)
AS SELECT * FROM table1;CREATE TABLE table3 LIKE table1; copies an existing table's structure, including its clustering configuration. Python and Scala users can do the same through the DataFrame and DeltaTable APIs, but on Databricks SQL the clause above is all you need.
Checkpoint 1 of 6· Fill the gap
Which keyword completes this statement so the table is laid out with liquid clustering on col0?
CREATE TABLE table1 (col0 INT, col1 STRING) ? BY (col0);CLUSTER BY enables liquid clustering. PARTITIONED BY creates the older partitioned layout, which cannot be combined with clustering, and ZORDER BY is an OPTIMIZE clause, not a table property.
Sources1
2.Clustering an existing table, and why OPTIMIZE matters
You don't have to recreate a table to cluster it. You can enable liquid clustering on an existing unpartitioned table with ALTER TABLE <table_name> CLUSTER BY (<clustering_columns>). The same statement changes the keys later, for example to add a second column that queries have started filtering on.
ALTER TABLE ... CLUSTER BY is a metadata change. It doesn't move any data, and rows already clustered by the previous keys stay as they are. The physical work is done by OPTIMIZE. On a liquid-clustered table, OPTIMIZE rewrites data files to group data by the clustering keys, and it does this incrementally.
By default, clustering is not applied to data written before clustering was enabled. To recluster everything, including previously clustered data, run OPTIMIZE <table_name> FULL (Databricks Runtime 16.0 and above). On newer runtimes, FULL can be combined with a simple range predicate on one clustering column to recluster only part of the table:
> OPTIMIZE events FULL WHERE date >= '2025-01-01';Two more details are often tested. First, column order in CLUSTER BY doesn't matter, so (a, b) and (b, a) describe the same clustering. Second, you cannot change the clustering columns of materialized views or streaming tables with ALTER TABLE.
Checkpoint 2 of 6· Check yourself
After clustering is first enabled on an existing table, an analyst wants every file reclustered, including data written before clustering. Which command does that?
Plain OPTIMIZE clusters incrementally, and the default behavior doesn't recluster previously written data. OPTIMIZE FULL rewrites the whole table. ZORDER can't be used on clustered tables, and ANALYZE only collects statistics.
“Optimize the whole table, including data that was previously clustered (for tables using liquid clustering).”Source: docs.databricks.com
Checkpoint 3 of 6· Exam question
An analyst is creating a new managed Delta table in Unity Catalog that will store sensor readings and will almost always be filtered by `device_id` and `reading_date`. Which statement correctly enables liquid clustering on these columns at table creation time?
Correct answer: A — `CREATE TABLE sensor_readings (device_id STRING, reading_date DATE, value DOUBLE) CLUSTER BY (device_id, reading_date);`
- A. The `CLUSTER BY` clause on `CREATE TABLE` is the documented syntax for enabling liquid clustering with the listed columns as clustering keys, and it lets those keys be redefined later without rewriting existing data.
- B. `PARTITIONED BY` creates rigid Hive-style partitions rather than liquid clustering, and combining two columns this way on frequently filtered fields risks the over-partitioning problem liquid clustering is designed to avoid.
- C. `ZORDER BY` is not valid table-creation syntax; Z-ordering is applied through an `OPTIMIZE ... ZORDER BY` command on existing data, not declared when the table is first created.
- D. There is no `cluster_keys` table option in Delta Lake syntax; clustering keys are declared through the dedicated `CLUSTER BY` clause, not through a generic `OPTIONS` map.
3.Explicit columns, AUTO or NONE
CLUSTER BY accepts three forms: a list of columns you choose, AUTO, or NONE. With AUTO, available in Databricks SQL and Databricks Runtime 15.4 and above, Delta Lake picks the clustering columns and keeps adapting them to your query patterns. NONE turns clustering off: newly inserted or updated data is no longer clustered by OPTIMIZE.
AUTO relies on predictive optimization, which automatically runs OPTIMIZE, VACUUM and ANALYZE on Unity Catalog managed tables. With predictive optimization, OPTIMIZE triggers incremental clustering for you. If automatic liquid clustering is on, predictive optimization may also choose new clustering keys before clustering the data. For all Unity Catalog managed tables, Databricks recommends automatic liquid clustering together with predictive optimization.
> ALTER TABLE t CLUSTER BY NONE;Checkpoint 4 of 6· Match them up
Match each CLUSTER BY option to what it does
Tap a term, then the definition that fits it.
The CLUSTER BY clause accepts an explicit column list, AUTO or NONE. AUTO hands key selection to Databricks, and NONE stops OPTIMIZE from clustering new data.
“Directs Delta Lake to automatically determine and over time adapt to the best columns to cluster by.”Source: docs.databricks.com
4.Converting a partitioned table
A plain ALTER TABLE ... CLUSTER BY works only on unpartitioned tables, because clustering and partitioning cannot coexist. For an existing partitioned Delta table, Databricks Runtime 18.1 and above adds a conversion statement: ALTER TABLE <table_name> REPLACE PARTITIONED BY WITH CLUSTER BY, followed by explicit columns, AUTO, or nothing at all.
- Columns: Databricks recommends keeping them similar to the original partition columns. Very different columns trigger a large reclustering operation on the first OPTIMIZE run.
- **AUTO: starts from the current partition columns and lets predictive optimization adapt them over time. It is available only for Unity Catalog managed tables.
- No option**: the current partition columns become the clustering columns.
ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id);
OPTIMIZE t1;The conversion helps tables with poor data skipping or too many partitions. It also reduces write conflicts, because liquid-clustered tables support row-level concurrency. To check that it worked, run DESCRIBE EXTENDED and look for the new clustering columns.
Checkpoint 5 of 6· Put it in order
Put these steps in order to move a partitioned table onto new clustering columns and confirm the result
- 1.Run OPTIMIZE t1 so data is reclustered on the new columns
- 2.Run ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id)
- 3.Run DESCRIBE EXTENDED to confirm the new clustering columns
The ALTER statement changes the layout definition. OPTIMIZE then rewrites data to match it, and DESCRIBE EXTENDED shows the resulting clustering columns.
“To benefit from altering clustering columns, you must run OPTIMIZE.”Source: docs.databricks.com
Checkpoint 6 of 6· Exam question
A data analyst is asked to speed up filtering on a large Unity Catalog managed table but does not have visibility into which columns end users actually filter on in their dashboards. Which approach lets Databricks choose and maintain the clustering keys automatically based on observed query patterns?
Correct answer: A — Declare the table with `CLUSTER BY AUTO` so Databricks analyzes the historical query workload and selects candidate columns itself
- A. `CLUSTER BY AUTO` on a Unity Catalog managed table lets Databricks automatically analyze the table's query history and pick the best candidate clustering columns without the analyst having to know the filter patterns in advance.
- B. There is no wildcard `CLUSTER BY (*)` syntax, and clustering on every column instead of the columns actually used in filters would not deliver the targeted data-skipping benefit liquid clustering provides.
- C. Manually reviewing dashboard SQL is a slow, error-prone process that duplicates work the `AUTO` mode already automates, and it does not scale as query patterns change over time.
- D. A result cache only helps when an identical query repeats; it does not reduce the files scanned for new or varied filter values, so it does not solve the underlying data-skipping problem on a large unclustered table.
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Running ALTER TABLE ... CLUSTER BY immediately reorganizes the data already in the table.Why is that wrong?
ALTER only changes the clustering definition. OPTIMIZE does the reclustering, and previously written data needs OPTIMIZE FULL.
Covered in Clustering an existing table, and why OPTIMIZE matters
2.In a CTAS statement, CLUSTER BY goes in or after the SELECT query.Why is that wrong?
In CTAS, CLUSTER BY goes directly after the new table's name and before AS SELECT.
Covered in Creating a clustered table
3.Any partitioned table can be converted with REPLACE PARTITIONED BY WITH CLUSTER BY AUTO.Why is that wrong?
The AUTO option for conversion is available only for Unity Catalog managed tables. Other tables must name columns or keep the partition columns.
Covered in Converting a partitioned table
Practise it for real
Create a clustered table, add a second clustering key, recluster it and confirm the layout
1.Run CREATE TABLE t(a int, b string) CLUSTER BY (a);
Why: Clustering is easiest to enable when the table is created
You should see: An empty Delta table clustered on column a
2.Run ALTER TABLE t CLUSTER BY (a, b);
Why: Adds a second clustering dimension for queries that also filter on b
You should see: The statement succeeds without rewriting any data
3.Run OPTIMIZE t;
Why: Rows are grouped by changed clustering columns only when OPTIMIZE runs
You should see: OPTIMIZE returns file statistics for the files removed and added
4.Run DESCRIBE EXTENDED t;
Why: Confirms which clustering columns the table now uses
You should see: The clustering columns a and b appear in the output
Stuck? Get a nudge
If OPTIMIZE seems to change nothing on an empty table, insert a few rows first. With no data files, there is nothing to cluster.
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
“CLUSTER BY must appear after the table name, not in the SELECT clause”
↩︎ Creating a clustered table“You can enable liquid clustering on an existing unpartitioned table or during table creation.”
↩︎ Clustering an existing table, and why OPTIMIZE matters“Using very different columns triggers a large reclustering operation on the first OPTIMIZE run.”
↩︎ Converting a partitioned table“To verify that a conversion was successful, run DESCRIBE EXTENDED to see the new clustering columns.”
↩︎ Converting a partitioned table“The default behavior does not apply clustering to previously written data.”
↩︎ Exam trap 1“CLUSTER BY must appear after the table name, not in the SELECT clause”
↩︎ Exam trap 2“Only available for Unity Catalog managed tables.”
↩︎ Exam trap 3“To benefit from altering clustering columns, you must run OPTIMIZE.”
↩︎ Checkpoint - 2.
“For tables with liquid clustering enabled, OPTIMIZE rewrites data files to group data by liquid clustering keys.”
↩︎ Clustering an existing table, and why OPTIMIZE matters - 3.
“The column order does not matter.”
↩︎ Clustering an existing table, and why OPTIMIZE matters“You cannot change the clustering columns of materialized views or streaming tables with ALTER TABLE.”
↩︎ Clustering an existing table, and why OPTIMIZE matters“Turns off clustering for the relation being altered.”
↩︎ Explicit columns, AUTO or NONE“To cluster rows with altered clustering columns, you must run OPTIMIZE.”
↩︎ Prediction“Directs Delta Lake to automatically determine and over time adapt to the best columns to cluster by.”
↩︎ Checkpoint - 4.
“Triggers incremental clustering for enabled tables.”
↩︎ Explicit columns, AUTO or NONE“If automatic liquid clustering is enabled, predictive optimization might select new clustering keys before clustering data.”
↩︎ Explicit columns, AUTO or NONE
Also cited
“Optimize the whole table, including data that was previously clustered (for tables using liquid clustering).”
↩︎ Checkpoint