Skip to content
Cheat sheets

Data Engineer Associate · Domain 6: Troubleshooting, Monitoring, and Optimization · 10% of the exam

Task 6.4: Understand the features of Liquid Clustering and predictive optimization.

CLUSTER BY, choosing and changing keys, automatic clustering, OPTIMIZE, and what predictive optimization runs on Unity Catalog managed tables.

Study it

  • Liquid clustering and predictive optimization

    Lesson coming

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

The table events uses liquid clustering on event_date. Queries now filter mostly on customer_id, so the data engineer runs ALTER TABLE events CLUSTER BY (customer_id). What happens to the data that was already in the table?

  1. AALTER TABLE rewrites the existing files right away to cluster them by customer_id
  2. BThe statement fails; clustering keys are set when a table is created, not later
  3. CThe next VACUUM run rewrites the existing files using the new clustering key
  4. DIt stays as is; later writes and OPTIMIZE use customer_id, OPTIMIZE FULL reclusters it
Show the answer and why

  • AALTER TABLE rewrites the existing files right away to cluster them by customer_id

    Incorrect

    Changing keys is a metadata change; ALTER TABLE does not rewrite existing data.

  • BThe statement fails; clustering keys are set when a table is created, not later

    Incorrect

    Clustering keys can be changed at any time with ALTER TABLE; fixed keys are a limitation of partitioning.

  • CThe next VACUUM run rewrites the existing files using the new clustering key

    Incorrect

    VACUUM removes files no longer referenced by the table; it does not cluster or rewrite data.

  • DIt stays as is; later writes and OPTIMIZE use customer_id, OPTIMIZE FULL reclusters it

    Correct

    After a key change, later OPTIMIZE and writes use the new keys but existing data is not rewritten unless you force reclustering.

Liquid clustering lets you change keys without rewriting data. Run OPTIMIZE FULL when existing data should also be reorganized by the new keys.

Databricks documentation

Task 6.4Report a problem with this question

Question 2 · choose 1

A Unity Catalog managed Delta table with predictive optimization enabled is queried by many teams, and their filter columns change over time. Nobody can say which columns to cluster on. What should the data engineer use?

  1. AALTER TABLE ... CLUSTER BY AUTO on the managed table
  2. BPARTITIONED BY on the column with the most distinct values
  3. COPTIMIZE ... ZORDER BY on each of the filter columns
  4. DCLUSTER BY on the twelve columns of the table
Show the answer and why

  • AALTER TABLE ... CLUSTER BY AUTO on the managed table

    Correct

    Automatic liquid clustering analyzes the table's query workload and selects or changes clustering keys when the savings outweigh the cost.

  • BPARTITIONED BY on the column with the most distinct values

    Incorrect

    Partitioning on a high-cardinality column creates many small partitions and files.

  • COPTIMIZE ... ZORDER BY on each of the filter columns

    Incorrect

    Z-ordering is not compatible with liquid clustering, and spreading it over many columns weakens data skipping.

  • DCLUSTER BY on the twelve columns of the table

    Incorrect

    Liquid clustering allows up to four clustering keys.

CLUSTER BY AUTO needs predictive optimization and works on Unity Catalog managed tables; it chooses keys from the query history.

Databricks documentation

Task 6.4Report a problem with this question

Question 3 · choose 1

A Delta table is partitioned by event_date and suffers from small files. The team wants to move it to liquid clustering with minimal reader and writer downtime, without rewriting it through a new table. On Databricks Runtime 18.1, which statement should they run?

  1. AALTER TABLE events CLUSTER BY (event_date) on the partitioned table
  2. BOPTIMIZE events ZORDER BY (event_date)
  3. CALTER TABLE events REPLACE PARTITIONED BY WITH CLUSTER BY (event_date)
  4. DCREATE OR REPLACE TABLE events CLUSTER BY (event_date) AS SELECT * FROM events
Show the answer and why

  • AALTER TABLE events CLUSTER BY (event_date) on the partitioned table

    Incorrect

    Clustering can be enabled with ALTER TABLE only on unpartitioned tables; it is not compatible with partitioning.

  • BOPTIMIZE events ZORDER BY (event_date)

    Incorrect

    Z-ordering keeps the partitioned layout and is not liquid clustering.

  • CALTER TABLE events REPLACE PARTITIONED BY WITH CLUSTER BY (event_date)

    Correct

    This converts a partitioned table to liquid clustering in place, with minimal downtime, in Databricks Runtime 18.1 and above.

  • DCREATE OR REPLACE TABLE events CLUSTER BY (event_date) AS SELECT * FROM events

    Incorrect

    This rewrites the whole table, which the team wants to avoid.

REPLACE PARTITIONED BY WITH CLUSTER BY converts a partitioned table to liquid clustering in place, for managed and external tables alike.

Databricks documentation

Task 6.4Report a problem with this question

Question 4 · choose 1

Predictive optimization is enabled for the account. In Catalog Explorer, the History tab of managed tables shows automatic OPTIMIZE and VACUUM runs, but an external table in the same schema shows none. Why?

  1. APredictive optimization runs on tables that use liquid clustering, not on others
  2. BPredictive optimization supports Unity Catalog managed tables, not external ones
  3. CPredictive optimization runs when queries come from serverless SQL warehouses
  4. DPredictive optimization must be scheduled per table with an OPTIMIZE job
Show the answer and why

  • APredictive optimization runs on tables that use liquid clustering, not on others

    Incorrect

    It also compacts files, vacuums and analyzes tables without clustering.

  • BPredictive optimization supports Unity Catalog managed tables, not external ones

    Correct

    OPTIMIZE, VACUUM and ANALYZE by predictive optimization apply to managed tables; external tables are excluded.

  • CPredictive optimization runs when queries come from serverless SQL warehouses

    Incorrect

    Where queries run does not decide which tables predictive optimization maintains.

  • DPredictive optimization must be scheduled per table with an OPTIMIZE job

    Incorrect

    It identifies tables that need maintenance and queues the operations itself.

Predictive optimization is one reason to prefer managed tables: Databricks maintains them, while external tables need manual maintenance.

Databricks documentation

Task 6.4Report a problem with this question

Question 5 · choose 1

Most queries on a large events table filter on customer_id and event_date. The table also has event_ts, from which event_date is derived. Which clustering keys should the data engineer choose?

  1. AThe twelve columns of the table
  2. Bstatus, which has three distinct values
  3. Cevent_ts and event_date together
  4. Dcustomer_id and event_date as keys
Show the answer and why

  • AThe twelve columns of the table

    Incorrect

    Liquid clustering supports up to four keys, and more keys weaken single-column filtering on smaller tables.

  • Bstatus, which has three distinct values

    Incorrect

    Clustering should follow the columns used in filters; a low-cardinality column that queries rarely filter on helps little.

  • Cevent_ts and event_date together

    Incorrect

    The two are highly correlated, and only one of them is needed as a key.

  • Dcustomer_id and event_date as keys

    Correct

    Choose clustering keys from the columns most often used in query filters.

Pick up to four keys from the most common filter columns, and keep one of any correlated pair.

Databricks documentation

Task 6.4Report a problem with this question

Practise domain 6 →Practise all domains →