Data Engineer Professional · Domain 5: Cost & Performance Optimization · 15% of the exam
Task 5.1: Explain how Unity Catalog managed tables, Predictive Optimization, and Liquid Clustering reduce operational and maintenance overhead for a given workload.
What predictive optimization runs on managed tables, how it chooses work, automatic liquid clustering, and what still needs a person.
Study it
Managed tables, predictive optimization and automatic liquid clustering
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
A team runs a nightly job that executes OPTIMIZE ... ZORDER BY (customer_id) and VACUUM on 200 Unity Catalog managed tables. Predictive optimization is enabled for the account. They want to retire the job without losing data skipping on customer_id. What should they do?
AKeep the Z-order layout on customer_id and let predictive optimization take over the OPTIMIZE ZORDER runs
BCluster the tables by customer_id with liquid clustering and let predictive optimization run OPTIMIZE and VACUUM
CConvert the tables to external tables with their own storage layout
DKeep the nightly OPTIMIZE and VACUUM job and disable predictive optimization on the tables
Show the answer and why
AKeep the Z-order layout on customer_id and let predictive optimization take over the OPTIMIZE ZORDER runs
Incorrect
Predictive optimization never runs ZORDER, so the Z-order layout would decay as data is written.
BCluster the tables by customer_id with liquid clustering and let predictive optimization run OPTIMIZE and VACUUM
Correct
Predictive optimization does not run ZORDER and ignores Z-ordered files, but it triggers incremental clustering for liquid clustering tables and runs VACUUM.
CConvert the tables to external tables with their own storage layout
Incorrect
Predictive optimization supports only managed tables, so this removes the automation entirely.
DKeep the nightly OPTIMIZE and VACUUM job and disable predictive optimization on the tables
Incorrect
That keeps the maintenance burden the team wants to remove.
Predictive optimization runs OPTIMIZE and VACUUM on Unity Catalog managed tables, and its OPTIMIZE triggers incremental clustering for tables with liquid clustering. It does not run ZORDER and ignores Z-ordered files, so Z-ordered tables should move to liquid clustering.
To save storage, a team sets delta.deletedFileRetentionDuration to 2 days on a managed table that predictive optimization maintains. How long does predictive optimization keep unreferenced data files when it runs VACUUM?
A2 days, as the table property says
B30 days, the default log retention
CForever, because predictive optimization never runs VACUUM
DAt least 7 days, because it enforces a 7-day minimum
Show the answer and why
A2 days, as the table property says
Incorrect
Predictive optimization does not go below its 7-day minimum.
B30 days, the default log retention
Incorrect
Log retention controls history, not the VACUUM threshold.
CForever, because predictive optimization never runs VACUUM
Incorrect
Predictive optimization does run VACUUM on managed tables.
DAt least 7 days, because it enforces a 7-day minimum
Correct
Predictive optimization retains data files for a minimum of 7 days even when the property is set below that.
VACUUM retention comes from delta.deletedFileRetentionDuration, 7 days by default. If it is set below 7 days, predictive optimization still keeps files for at least 7 days to prevent data loss and keep recent time travel.
A managed table has not been compacted by predictive optimization for weeks. On Databricks Runtime 18 LTS, how can the data engineer see why the operation was skipped?
ARead predictive_optimization_evaluations in DESCRIBE TABLE EXTENDED AS JSON
BRun DESCRIBE HISTORY on the table and look for the skipped OPTIMIZE commits
CRun SHOW TBLPROPERTIES on the table and read its auto-optimize settings
DRun OPTIMIZE by hand on the table and read the metrics in its output
Show the answer and why
ARead predictive_optimization_evaluations in DESCRIBE TABLE EXTENDED AS JSON
Correct
This field lists each operation type and its most recent evaluation result; the History tab in Catalog Explorer also shows it.
BRun DESCRIBE HISTORY on the table and look for the skipped OPTIMIZE commits
Incorrect
History lists commits that happened, not evaluations that were skipped.
CRun SHOW TBLPROPERTIES on the table and read its auto-optimize settings
Incorrect
Table properties show settings, not evaluation results.
DRun OPTIMIZE by hand on the table and read the metrics in its output
Incorrect
A manual run does not explain why the automatic one was skipped.
After predictive optimization evaluates a managed table, the reason an operation was skipped is available through DESCRIBE TABLE EXTENDED ... AS JSON or the History tab; results can take up to 24 hours to appear.
A team runs a nightly ANALYZE TABLE job on Unity Catalog managed tables that predictive optimization maintains. What does predictive optimization already do that makes this job unnecessary?
AIt disables statistics, which managed tables no longer use
BIt copies statistics from the external tables in the schema
CIt collects statistics on write and runs ANALYZE when needed
DIt runs ANALYZE only when the job owner requests it in the UI
Show the answer and why
AIt disables statistics, which managed tables no longer use
Incorrect
Statistics are used for data skipping and query planning.
BIt copies statistics from the external tables in the schema
Incorrect
Statistics come from each table's own data.
CIt collects statistics on write and runs ANALYZE when needed
Correct
Predictive optimization collects statistics on write and runs ANALYZE among its operations.
DIt runs ANALYZE only when the job owner requests it in the UI
Incorrect
Predictive optimization chooses and queues operations itself.
Predictive optimization identifies tables that benefit from maintenance, collects statistics when data is written, and runs OPTIMIZE, VACUUM and ANALYZE on Unity Catalog managed tables.
A managed table uses automatic liquid clustering, and predictive optimization has already selected its clustering keys. A new nightly job rebuilds the table with CREATE OR REPLACE TABLE ... AS SELECT and no CLUSTER BY clause. What happens, and what should the job do?
ANothing changes; predictive optimization keeps the keys it selected
BAutomatic clustering is turned off; add CLUSTER BY AUTO to the job
CThe keys are kept only if the job runs OPTIMIZE FULL after it
DThe table is partitioned by its former clustering keys instead
Show the answer and why
ANothing changes; predictive optimization keeps the keys it selected
Incorrect
A replace without CLUSTER BY AUTO turns automatic clustering off and does not preserve the clustering columns.
BAutomatic clustering is turned off; add CLUSTER BY AUTO to the job
Correct
Including CLUSTER BY AUTO in the replace statement keeps automatic clustering and the previously selected columns.
CThe keys are kept only if the job runs OPTIMIZE FULL after it
Incorrect
OPTIMIZE reclusters data; it does not restore the clustering setting that the replace removed.
DThe table is partitioned by its former clustering keys instead
Incorrect
A replace without a clustering clause does not turn clustering keys into partitions.
If a table has automatic liquid clustering enabled, running CREATE OR REPLACE without CLUSTER BY AUTO turns off automatic clustering and does not preserve the clustering columns. Including CLUSTER BY AUTO in the replace statement keeps automatic clustering and any previously selected columns.