CertSafari
    Databricks Certified Data Analyst Associate· Lessons

    Domain 5 · Lesson 22/39

    Predictive optimization and automatic liquid clustering

    Analyze table access patterns and query workloads to determine when to leverage Predictive Optimization and Automatic Liquid Clustering to optimize table performance.

    13 min read
    2.5% of exam
    5 sources
    Published 2 Nov 2026
    Docs as of 31 Oct 2026

    What you will be able to do

    • Describe which maintenance operations predictive optimization runs, and on which tables
    • Enable, disable or inherit predictive optimization at the account, catalog, schema and table level
    • Decide when CLUSTER BY AUTO beats choosing clustering keys by hand, and state its requirements
    • Choose between relying on predictive optimization and scheduling OPTIMIZE yourself, and check what it actually did

    1.What predictive optimization runs, and on which tables

    A table stays fast only with regular maintenance. Small files need compacting, clustered tables need reclustering as data arrives, old files need deleting and statistics need refreshing. Predictive optimization takes over that work for Unity Catalog managed tables. It works out which tables would benefit, queues the operations and runs them, so nobody has to track when each table last ran OPTIMIZE. It also collects statistics as data is written.

    The three operations predictive optimization runs
    OperationWhat it does for the table
    OPTIMIZETriggers incremental clustering on liquid-clustered tables and compacts files to better sizes
    VACUUMDeletes data files no longer referenced by the table, reducing storage cost
    ANALYZEScans the table and collects statistics that improve query performance

    Predictive optimization works only on Unity Catalog managed tables (managed vs external tables). It doesn't run on external tables or on tables received as an OpenSharing recipient. The workspace must be on the Premium plan or above, in a supported region, and you must use SQL warehouses or Databricks Runtime 12.2 LTS and above. The work runs on serverless compute for jobs and is billed to your account under a serverless jobs SKU.

    Managed tables also get better data skipping. External tables collect statistics on only the first 32 columns. On managed tables, predictive optimization also collects statistics on columns past the first 32 that your query filters use often.

    VACUUM follows the delta.deletedFileRetentionDuration table property, which defaults to 7 days. It removes data files that no Delta table version within that window still references. Time travel queries an older table version, and an older version depends on files the current version no longer uses, so once VACUUM deletes those files the version can't be queried. If you need a longer time travel history, raise the property before you enable predictive optimization. Setting it below 7 days doesn't shorten the window: predictive optimization still keeps files for at least 7 days.

    Checkpoint 1 of 7· Match them up

    Match each operation that predictive optimization runs to what it does for the table

    Tap a term, then the definition that fits it.

    Checkpoint 2 of 7· Check yourself

    An account admin turns on predictive optimization at the account level on a Premium workspace. A Unity Catalog schema contains two liquid-clustered tables: sales_managed (a managed table) and sales_ext (an external table). Which tables will predictive optimization maintain?

    Sources12

    2.Enabling it and how the setting is inherited

    Predictive optimization is on by default for accounts created on or after November 11, 2024. Older accounts are being enabled in a gradual rollout. An account admin can turn it on for every metastore under Settings, then Feature enablement, in the accounts console. Every Unity Catalog managed table inherits the account value unless something lower down overrides it.

    The setting is inherited from account to catalog to schema to table, and an explicit setting at any level overrides it. Explicit choices are sticky in both directions. If the account is disabled, a catalog or schema that was explicitly enabled stays enabled. If an object was explicitly disabled before the account was turned on, it stays blocked afterwards. INHERIT clears the explicit setting so the object follows its parent again.

    Enable, disable or reset predictive optimization at each levelsql
    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;

    Changing the setting takes ownership or the MANAGE privilege on that object. At the account level it takes an account admin. To check the current state, run DESCRIBE CATALOG EXTENDED, DESCRIBE SCHEMA EXTENDED or DESCRIBE TABLE EXTENDED. The Predictive Optimization field shows whether it is enabled and whether that value is inherited.

    Checkpoint 3 of 7· Check yourself

    Predictive optimization is on at the account level. The owner of catalog finance has run ALTER CATALOG finance ENABLE PREDICTIVE OPTIMIZATION. Catalog marketing has never had an explicit setting. The account admin now disables predictive optimization at the account level. What happens to the managed tables in the two catalogs?

    Sources1

    3.Letting Databricks pick the keys with CLUSTER BY AUTO

    Liquid clustering lays out a table's files by clustering keys, the columns that queries filter on most. OPTIMIZE rewrites data files to group rows with similar key values together, and a query that filters on a key can then skip the files that cannot match. That skipping is what makes the choice of keys matter. Clustering is also incremental: OPTIMIZE rewrites only the data that still needs clustering, which is the work predictive optimization schedules for you.

    Choosing keys by hand assumes you know how a table is queried. Often you don't: the filters live in dashboards and ad hoc queries that other people write, and they change from one quarter to the next. Automatic liquid clustering handles that case. Declare the table with CLUSTER BY AUTO and Databricks studies the table's query history to pick the keys itself. A wildcard key list doesn't exist, and leaving the table unclustered while relying on caching doesn't change how many files a new filter has to scan.

    Creating a table with automatic liquid clusteringsql
    CREATE OR REPLACE TABLE table1 (column01 int, column02 string) CLUSTER BY AUTO;

    Key selection depends on predictive optimization, so automatic liquid clustering is available only for Unity Catalog managed tables. Intelligent key selection relies on metadata introduced in Databricks Runtime 15.4 LTS. It also works on materialized views and streaming tables. Selection follows three rules:

    • Workload analysis: the table's historical query workload identifies the best candidate columns.
    • Adaptation: when query patterns or data distributions shift, new keys are selected.
    • Cost awareness: keys change only when the predicted data-skipping savings outweigh the cost of reclustering.

    Selecting no keys is a valid outcome. It happens when the table is too small, when it already has an effective layout (from earlier manual keys or from insertion order that matches the queries), when it isn't queried often, or when the runtime is older than 15.4 LTS. Because those heuristics decide whether clustering pays off, you can turn on CLUSTER BY AUTO for any managed table without studying its workload first.

    ALTER TABLE table1 CLUSTER BY AUTO turns it on for an existing table, including one that already has manual keys. Set keys first with ALTER TABLE ... CLUSTER BY (c1, c2) and they act as starting hints for selection. CLUSTER BY NONE turns it off. Be careful with CREATE OR REPLACE on an auto-clustered table: leaving out CLUSTER BY AUTO turns automatic clustering off and drops the selected columns. To check a table, run DESCRIBE TABLE or SHOW TBLPROPERTIES: clusterByAuto is true, and clusteringColumns lists the current keys.

    Checkpoint 4 of 7· Fill the gap

    Which keyword completes this statement so that Databricks picks the clustering keys from the table's query history?

    CREATE OR REPLACE TABLE table1 (column01 int, column02 string) CLUSTER BY  ? ;

    Checkpoint 5 of 7· Check yourself

    A data engineer rebuilds a managed table that has had CLUSTER BY AUTO for months. They use a CREATE OR REPLACE TABLE statement with the same columns but leave out any CLUSTER BY clause. What is the result for the table's layout settings?

    Checkpoint 6 of 7· 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?

    Sources34

    4.Relying on predictive optimization instead of scheduled maintenance

    On a table that predictive optimization covers, drop your own maintenance schedule. Databricks recommends disabling scheduled OPTIMIZE jobs once predictive optimization is on, because it runs OPTIMIZE whenever that is cost-effective. Running both just spends compute twice.

    Manual scheduling still has a place: external tables, tables in accounts or regions without predictive optimization, and Z-ordered tables. Z-ordering is an older layout for tables without liquid clustering: it is applied only when you run OPTIMIZE ... ZORDER BY (column), which rewrites files to collocate that column's values. Predictive optimization never runs ZORDER and ignores Z-ordered files, so those tables need your own OPTIMIZE runs. A good starting point is once a day, ideally at night when spot prices are low. Clustered tables with many updates or inserts can need a run every one or two hours. More frequent runs speed up queries and cost more.

    You can check what predictive optimization did instead of assuming it ran. In the table's History tab in Catalog Explorer, choose Automatic runs. An Auto label means an operation ran, and Not applied means it was skipped; click the row to see why. A skipped key selection appears as an AUTO LIQUID row. On Databricks Runtime 18 LTS and above, DESCRIBE TABLE EXTENDED ... AS JSON returns predictive_optimization_evaluations, the latest result for each operation type. Results can take up to 24 hours to appear. For history, cost and impact across tables, query the system.storage.predictive_optimization_operations_history system table. Its records usually arrive within 12 hours, and DBUs are shown as ESTIMATED_DBU.

    How evaluation types map to the operations shown in Catalog Explorer
    DESCRIBE TABLE EXTENDED operation typeCatalog Explorer operationMeaning
    COMPACTIONOPTIMIZEFile compaction
    CLUSTERINGOPTIMIZEIncremental liquid clustering
    AUTO_CLUSTERING_COLUMN_SELECTIONAUTO LIQUIDEvaluation or evolution of clustering keys
    VACUUMVACUUMRemoval of unreferenced data files

    Checkpoint 7 of 7· Check yourself

    Your team has a nightly OPTIMIZE job on four Unity Catalog managed tables in a Premium workspace. Predictive optimization is enabled at the account level, and none of the tables has an explicit setting. Recommended practice is to drop scheduled jobs where predictive optimization covers the work. For which table should you keep the scheduled job?

    Sources3415

    Practise it for real

    Create a managed table with automatic liquid clustering, make sure predictive optimization covers it, and confirm both settings.

    1. 1.In a Unity Catalog schema where you can create tables, run: CREATE OR REPLACE TABLE table1 (column01 int, column02 string) CLUSTER BY AUTO;

      Why: CLUSTER BY AUTO hands key selection to predictive optimization instead of naming columns yourself.

      You should see: The statement succeeds and creates a managed table.

    2. 2.Run: ALTER TABLE table1 ENABLE PREDICTIVE OPTIMIZATION;

      Why: Automatic clustering needs predictive optimization. Enabling it explicitly protects the table if the account or schema value changes later.

      You should see: The statement succeeds (you need ownership or MANAGE on the table).

    3. 3.Run: DESCRIBE TABLE EXTENDED table1

      Why: The Predictive Optimization field shows whether it is enabled and whether that value is inherited.

      You should see: The Predictive Optimization field reports it as enabled.

    4. 4.Run: SHOW TBLPROPERTIES table1

      Why: These properties record whether automatic clustering is on and which keys have been chosen.

      You should see: clusterByAuto is true. clusteringColumns may stay empty on a small, rarely queried table.

    Stuck? Get a nudge

    If no keys show up after a day, look for an AUTO LIQUID row marked Not applied in the table's History tab. It gives the skip reason, and 'too small' or 'not queried often' are normal for a test table.

    Sources

    (5)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “Predictive optimization automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables”
      ↩︎ What predictive optimization runs, and on which tables
      “Only Unity Catalog managed tables are supported.”
      ↩︎ What predictive optimization runs, and on which tables
      “Your account is billed for this compute using a serverless jobs SKU.”
      ↩︎ What predictive optimization runs, and on which tables
      “predictive optimization still retains data files for a minimum of 7 days when it runs VACUUM FULL.”
      ↩︎ What predictive optimization runs, and on which tables
      “VACUUM removes data files no longer referenced by a Delta table version within that window.”
      ↩︎ What predictive optimization runs, and on which tables
      “All Unity Catalog managed tables inherit the account value by default.”
      ↩︎ Enabling it and how the setting is inherited
      “If predictive optimization is later enabled at the account level, it remains blocked for objects that have specifically disabled it.”
      ↩︎ Enabling it and how the setting is inherited
      “Predictive optimization is enabled by default for accounts created on or after November 11, 2024.”
      ↩︎ Enabling it and how the setting is inherited
      “the Auto label indicates the operation ran, and the Not applied label indicates the operation was skipped.”
      ↩︎ Relying on predictive optimization instead of scheduled maintenance
      “Disabling predictive optimization at the account level does not disable it for catalogs or schemas that have specifically enabled it.”
      ↩︎ Exam trap 1
      “VACUUM | Reduces storage costs by deleting data files no longer referenced by the table.”
      ↩︎ Checkpoint
      “Disabling predictive optimization at the account level does not disable it for catalogs or schemas that have specifically enabled it.”
      ↩︎ Checkpoint
      “On tables that use Z-order, predictive optimization ignores Z-ordered files.”
      ↩︎ Checkpoint
    2. 2.
      “predictive optimization also collects file-skipping statistics on additional columns that are frequently used in your query filters, beyond the first 32 columns.”
      ↩︎ What predictive optimization runs, and on which tables
    3. 3.
      “Databricks analyzes the table's historical query workload and identifies the best candidate columns for clustering.”
      ↩︎ Letting Databricks pick the keys with CLUSTER BY AUTO
      “Databricks changes clustering keys only when the predicted cost savings from data skipping improvements outweigh the data clustering cost.”
      ↩︎ Letting Databricks pick the keys with CLUSTER BY AUTO
      “Automatic liquid clustering requires predictive optimization for automatic key selection and clustering operations, and runs asynchronously.”
      ↩︎ Letting Databricks pick the keys with CLUSTER BY AUTO
      “If automatic liquid clustering is enabled, the clusterByAuto property is set to true.”
      ↩︎ Letting Databricks pick the keys with CLUSTER BY AUTO
      “Liquid clustering improves query performance through automatic data skipping when filtering on clustering keys.”
      ↩︎ Letting Databricks pick the keys with CLUSTER BY AUTO
      “When using Predictive optimization, Databricks recommends disabling any scheduled OPTIMIZE jobs.”
      ↩︎ Relying on predictive optimization instead of scheduled maintenance
      “Automatic liquid clustering requires predictive optimization for automatic key selection and clustering operations, and runs asynchronously.”
      ↩︎ Exam trap 2
      “The table is too small to benefit from liquid clustering.”
      ↩︎ Prediction
      “running CREATE OR REPLACE table_name without CLUSTER BY AUTO turns off automatic clustering and does not preserve clustering columns”
      ↩︎ Checkpoint
    4. 4.
      “For tables with liquid clustering enabled, OPTIMIZE rewrites data files to group data by liquid clustering keys.”
      ↩︎ Letting Databricks pick the keys with CLUSTER BY AUTO
      “Databricks recommends that you start by running OPTIMIZE on a daily basis, preferably at night when spot prices are low”
      ↩︎ Relying on predictive optimization instead of scheduled maintenance
      “Delta Lake tables without liquid clustering can optionally include a ZORDER BY clause to improve data clustering on rewrite.”
      ↩︎ Relying on predictive optimization instead of scheduled maintenance

    Spotted a mistake, or was something unclear? Tell us.