What you will be able to do
- Explain what CLUSTER BY AUTO does, what it requires, and why it may choose not to select keys
- List the maintenance operations predictive optimization runs and what it deliberately does not run
- Enable, disable, or inherit predictive optimization at the catalog, schema, and table level
- Find out what predictive optimization did, or skipped, for a table
1.CLUSTER BY AUTO: letting Databricks choose the keys
Liquid clustering organizes a table's data around clustering keys, and you can change those keys without rewriting the table. With manual keys, someone still has to choose those columns and revisit them when query patterns change. Automatic liquid clustering hands that decision to Databricks: "Automatic liquid clustering allows Databricks to intelligently choose clustering keys to optimize query performance, using the CLUSTER BY AUTO clause." It is available for Unity Catalog managed Delta tables on Databricks Runtime 15.4 LTS and above, and also for materialized views and streaming tables, including Lakeflow pipelines.
Three behaviours define it. It analyzes the table's historical query workload to find the best candidate columns. It adapts: if query patterns or data distributions change, it selects new keys. And it is cost-aware: keys change only when the predicted savings from better data skipping are larger than the cost of clustering the data. It doesn't run on its own. It needs predictive optimization to select keys and run clustering, and it runs asynchronously.
ALTER TABLE table1 CLUSTER BY (c1, c2);
ALTER TABLE table1 CLUSTER BY AUTO;In Python you can set those hints in a single operation, but only when creating or replacing a table: pass .clusterBy with the hint columns together with .option("clusterByAuto", "true") on the DataFrameWriter or DataFrameWriterV2. This needs Databricks Runtime 16.4 and above. On a table that already has automatic clustering enabled, a .clusterBy hint is accepted only once, and the columns are set only if the table has no clustering columns yet.
You can also start from an existing partitioned Delta table. In Databricks Runtime 18.1 and above, ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY AUTO converts it to liquid clustering, uses the current partition columns as the initial clustering columns, and lets predictive optimization adapt them over time. The AUTO option is available only for Unity Catalog managed tables.
ALTER TABLE t2 REPLACE PARTITIONED BY WITH CLUSTER BY AUTO;Choosing no keys is often the expected result, not a failure. Automatic clustering may skip key selection when the table is too small, already has an effective layout, isn't queried often, or the workload runs on a runtime older than 15.4 LTS. To check, use DESCRIBE TABLE or SHOW TBLPROPERTIES: clusterByAuto is true when automatic clustering is enabled, and clusteringColumns shows the current keys. Two ways to lose the setting are easy to miss. CREATE OR REPLACE without CLUSTER BY AUTO turns automatic clustering off and drops the selected columns. And in the DataFrame APIs, the clusterByAuto option can be set only in overwrite mode.
Checkpoint 1 of 6· Fill the gap
Which keyword completes this statement so that it turns off automatic liquid clustering on table1?
ALTER TABLE table1 CLUSTER BY ? ;CLUSTER BY NONE turns automatic clustering off. To switch to manual keys instead, you would list the columns, e.g. CLUSTER BY (column01, column02).
Source: docs.databricks.comCheckpoint 2 of 6· Exam question
A 20 TB liquid-clustered sales table was originally clustered on `store_id`. A data engineer runs `ALTER TABLE sales CLUSTER BY (region, product_category)` and wants every existing file in the table to reflect the new keys before the next quarterly report runs tonight. Which command accomplishes this?
Correct answer: C — Run `OPTIMIZE sales FULL` to force a complete rewrite of all existing data so that every file reflects the newly configured clustering keys immediately.
- A. Plain `OPTIMIZE` performs incremental clustering, rewriting only new or changed data rather than the entire table, so older files written under the previous key would remain unclustered by the new keys.
- B. `VACUUM` only deletes files no longer referenced by the table's transaction log for storage cleanup; it does not rewrite or recluster live data files at all.
- C. `OPTIMIZE ... FULL` forces a complete reclustering of all existing data, which is exactly the recommended command after changing clustering keys and needing the whole table realigned immediately.
- D. Predictive optimization schedules incremental `OPTIMIZE` runs based on its own evaluation of benefit, not guaranteed full reclustering, so waiting for it does not reliably realign the whole table by tonight.
Sources1
2.What predictive optimization runs, and what it doesn't
Predictive optimization is the engine behind automatic clustering, but it does more than clustering. It runs routine table maintenance for you: it finds tables that would benefit from maintenance and queues the operations, and it collects statistics when data is written to a managed table. It is enabled by default for accounts created on or after November 11, 2024. It runs on serverless compute for jobs, and your account is billed under a serverless jobs SKU.
| Operation | What it does under predictive optimization |
|---|---|
| OPTIMIZE | Triggers incremental clustering for liquid-clustered tables and optimizes file sizes; never runs ZORDER |
| VACUUM | Deletes data files no longer referenced by the table to reduce storage cost |
| ANALYZE | Scans the table and collects statistics to improve query performance |
Two details are worth remembering. First, OPTIMIZE run by predictive optimization does not run ZORDER, and it ignores Z-ordered files. A Z-ordered table gets no layout benefit from it, which is one more reason to move to liquid clustering. Second, VACUUM respects the delta.deletedFileRetentionDuration table property, which defaults to 7 days. If you need a longer time-travel window, set the property before you enable predictive optimization. If you set it below 7 days, predictive optimization still keeps files for at least 7 days when it runs VACUUM FULL.
ALTER TABLE table_name SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = '30 days');Checkpoint 3 of 6· Exam question
How many columns can be specified as liquid clustering keys on a single Delta table?
Correct answer: B — Up to four columns
- A. Two is below the documented limit; liquid clustering supports more than two key columns on a single table.
- B. Liquid clustering supports a maximum of four clustering key columns per table, which is the documented limit for `CLUSTER BY`.
- C. Eight exceeds the documented maximum; only up to four columns can be designated as clustering keys.
- D. Clustering keys are not unlimited; Databricks caps the number of clustering key columns at four per table.
Sources2
3.Requirements and the inheritance model
Predictive optimization has hard requirements. The workspace must be on the Premium plan or above in a supported region. You must use SQL warehouses or Databricks Runtime 12.2 LTS or above. Only Unity Catalog managed tables are supported: it does not run on external tables or on tables loaded as OpenSharing recipients.
You can enable it at four levels: account, catalog, schema, and table. Each level inherits from the one above unless it is set explicitly. An account admin sets the account default in the accounts console under Settings, then Feature enablement. Below that, a catalog, schema, or table owner, or a user with MANAGE on that object, can override the default with SQL. INHERIT resets an object to follow its parent again.
ALTER CATALOG [catalog_name] { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION;
ALTER { SCHEMA | DATABASE } schema_name { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION;
ALTER TABLE table_name { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION;Explicit settings win over inherited ones in both directions. If you turn predictive optimization off at the account level, catalogs or schemas that explicitly enabled it keep it on. If you disable it on an object and later enable it for the account, that object stays blocked. To see the effective value and whether it is inherited, run DESCRIBE (CATALOG | SCHEMA | TABLE) EXTENDED.
Checkpoint 4 of 6· Check yourself
An account admin disables predictive optimization at the account level. The finance catalog had previously been set with ALTER CATALOG finance ENABLE PREDICTIVE OPTIMIZATION. What happens to tables in finance?
An explicit setting on a catalog or schema overrides the inherited account value. Turning it off at the account level doesn't affect objects that enabled it themselves.
“Disabling predictive optimization at the account level does not disable it for catalogs or schemas that have specifically enabled it.”Source: docs.databricks.com
Sources2
4.Seeing what predictive optimization did, or skipped
Predictive optimization runs in the background, so you need ways to see what it did. There are three. For a single table on Databricks Runtime 18 LTS and above, DESCRIBE TABLE EXTENDED ... AS JSON returns a predictive_optimization_evaluations field. It shows only the latest result for each operation type, with no history, and it doesn't show skip reasons for ANALYZE. Results can take up to 24 hours to appear.
DESCRIBE TABLE EXTENDED catalog_name.schema_name.table_name AS JSONThe second is the table's History tab in Catalog Explorer. In the Operation column, Auto marks an operation that ran and Not applied marks one that was skipped. Clicking a Not applied row shows the reason. This is also where you learn why automatic liquid clustering chose no keys: look for an AUTO LIQUID row marked Not applied. The third covers the whole account: the system table system.storage.predictive_optimization_operations_history records operations, estimated DBU cost, and impact across tables. It updates within two hours.
Checkpoint 5 of 6· Put it in order
Put these steps in order to find out why predictive optimization skipped an operation on a table
- 1.In the dropdown, select Automatic runs
- 2.Find rows with the Not applied label in the Operation column
- 3.Click a Not applied row to view the skip reason
- 4.Open the table's History tab in Catalog Explorer
Filtering to Automatic runs hides manually triggered operations. Not applied marks skipped operations, and the skip reason appears when you click that row.
“select Automatic runs, then look for rows with the Not applied label”Source: docs.databricks.com
Checkpoint 6 of 6· Match them up
Match each DESCRIBE TABLE EXTENDED operation type to the operation shown in Catalog Explorer
Tap a term, then the definition that fits it.
Compaction and incremental clustering both appear as OPTIMIZE in Catalog Explorer. Key selection for automatic liquid clustering appears as AUTO LIQUID.
“AUTO_CLUSTERING_COLUMN_SELECTION | AUTO LIQUID | Evaluation or evolution of liquid clustering keys”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Predictive optimization keeps Z-ordered tables tuned by running OPTIMIZE ... ZORDER BY for you.Why is that wrong?
Predictive optimization never runs ZORDER and ignores Z-ordered files. To get automatic layout maintenance, the table needs liquid clustering.
Covered in What predictive optimization runs, and what it doesn't
2.Disabling predictive optimization at the account level turns it off everywhere.Why is that wrong?
Explicit catalog or schema settings override the inherited account value, so objects that enabled it themselves keep it on.
Covered in Requirements and the inheritance model
3.Running CREATE OR REPLACE on an auto-clustered table 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 Databricks choose the keys
Practise it for real
Create an auto-clustered managed table, make sure predictive optimization is enabled for it, and see where its decisions appear.
1.In a Unity Catalog schema, run: CREATE OR REPLACE TABLE table1 (column01 int, column02 string) CLUSTER BY AUTO;
Why: Automatic liquid clustering is available only for Unity Catalog managed tables.
You should see: The table is created with no error.
2.Run DESCRIBE TABLE EXTENDED table1 (or SHOW TBLPROPERTIES table1).
Why: This confirms that automatic clustering is on and shows which keys, if any, have been selected.
You should see: clusterByAuto is true. clusteringColumns may still be empty for a new, small table.
3.Run ALTER TABLE table1 ENABLE PREDICTIVE OPTIMIZATION; if the table doesn't already inherit it, then DESCRIBE TABLE EXTENDED table1 again.
Why: Automatic key selection and clustering run through predictive optimization.
You should see: The Predictive Optimization field shows that it is enabled, either set explicitly or inherited from a parent.
4.Open the table's History tab in Catalog Explorer, select Automatic runs, and look for AUTO LIQUID rows.
Why: Skipped key selection appears here with a reason.
You should see: Possibly nothing yet, since results can take up to 24 hours. Later you may see a Not applied AUTO LIQUID row, for example because the table is too small.
Stuck? Get a nudge
An empty clusteringColumns on a tiny table is the cost-aware heuristic working as intended, not a failure.
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
“Automatic liquid clustering requires predictive optimization for automatic key selection and clustering operations, and runs asynchronously.”
↩︎ CLUSTER BY AUTO: letting Databricks choose the keys“In Databricks Runtime 15.4 LTS and above, you can enable automatic liquid clustering for Unity Catalog managed Delta Lake tables.”
↩︎ CLUSTER BY AUTO: letting Databricks 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 Databricks choose the keys“If automatic liquid clustering is enabled, the clusterByAuto property is set to true.”
↩︎ CLUSTER BY AUTO: letting Databricks choose the keys“the clusterByAuto option can only be set when using overwrite mode”
↩︎ CLUSTER BY AUTO: letting Databricks choose the keys“To set initial clustering column hints for key selection using DataFrameWriter:”
↩︎ CLUSTER BY AUTO: letting Databricks choose the keys“a .clusterBy hint is accepted only once.”
↩︎ CLUSTER BY AUTO: letting Databricks choose the keys“AUTO: Uses the current partition columns as the initial clustering columns and lets predictive optimization adapt over time.”
↩︎ CLUSTER BY AUTO: letting Databricks choose the keys“In Databricks Runtime 18.1 and above, to convert an existing partitioned Delta Lake table to liquid clustering”
↩︎ CLUSTER BY AUTO: letting Databricks choose the keys“running CREATE OR REPLACE table_name without CLUSTER BY AUTO turns off automatic clustering and does not preserve clustering columns”
↩︎ Exam trap 3“The table is too small to benefit from liquid clustering.”
↩︎ Prediction - 2.
“Predictive optimization automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables (Delta Lake and Iceberg) on Databricks”
↩︎ What predictive optimization runs, and what it doesn't“Predictive optimization is enabled by default for accounts created on or after November 11, 2024.”
↩︎ What predictive optimization runs, and what it doesn't“On tables that use Z-order, predictive optimization ignores Z-ordered files.”
↩︎ What predictive optimization runs, and what it doesn't“set this property before you enable predictive optimization”
↩︎ What predictive optimization runs, and what it doesn't“predictive optimization still retains data files for a minimum of 7 days when it runs VACUUM FULL”
↩︎ What predictive optimization runs, and what it doesn't“Predictive optimization runs ANALYZE, OPTIMIZE, and VACUUM operations using serverless compute for jobs.”
↩︎ What predictive optimization runs, and what it doesn't“Your Databricks workspace must be on the Premium plan or above in a supported region.”
↩︎ Requirements and the inheritance model“Only Unity Catalog managed tables are supported.”
↩︎ Requirements and the inheritance model“If predictive optimization is later enabled at the account level, it remains blocked for objects that have specifically disabled it.”
↩︎ Requirements and the inheritance model“Only the latest evaluation is shown with no history.”
↩︎ Seeing what predictive optimization did, or skipped“In the Operation column, the Auto label indicates the operation ran, and the Not applied label indicates the operation was skipped.”
↩︎ Seeing what predictive optimization did, or skipped“Databricks provides the system table system.storage.predictive_optimization_operations_history for observability into predictive optimization operations, costs, and impact.”
↩︎ Seeing what predictive optimization did, or skipped“OPTIMIZE does not run ZORDER when executed by predictive optimization.”
↩︎ Exam trap 1“Disabling predictive optimization at the account level does not disable it for catalogs or schemas that have specifically enabled it.”
↩︎ Exam trap 2“Disabling predictive optimization at the account level does not disable it for catalogs or schemas that have specifically enabled it.”
↩︎ Checkpoint“select Automatic runs, then look for rows with the Not applied label”
↩︎ Checkpoint“AUTO_CLUSTERING_COLUMN_SELECTION | AUTO LIQUID | Evaluation or evolution of liquid clustering keys”
↩︎ Checkpoint - 3.
“The predictive optimization system table updates within two hours.”
↩︎ Seeing what predictive optimization did, or skipped