Data Analyst Associate · Domain 5: Analyzing Queries · 15% of the exam
Task 5.3: Analyze table access patterns and query workloads to determine when to leverage Predictive Optimization and Automatic Liquid Clustering to optimize table performance; Fix a query to achieve the desired results
What predictive optimization runs on managed tables, CLUSTER BY AUTO and liquid clustering keys, and correcting SQL whose grouping, NULL handling, casts or window logic gives the wrong answer.
Study it
Predictive optimization and automatic liquid clustering
Lesson coming
Fixing a query: grouping, NULLs, casts and window functions
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
A month ago an analyst ran ALTER TABLE ... CLUSTER BY AUTO on the table main.ref.currency_codes, a 2 MB Unity Catalog managed table that a few reports read. Predictive optimization is enabled. In Catalog Explorer, the History tab shows an AUTO LIQUID row labeled Not applied. What explains this, and what should she do?
APredictive optimization blocks key selection; turn it off
BCLUSTER BY AUTO works only on external tables; recreate the table as an external table
CThe command did not take effect; run CREATE OR REPLACE TABLE without a CLUSTER BY clause to reset it
DThe table is too small to benefit, so no keys were chosen; nothing needs fixing
Show the answer and why
APredictive optimization blocks key selection; turn it off
Incorrect
Automatic liquid clustering requires predictive optimization for key selection and clustering operations.
BCLUSTER BY AUTO works only on external tables; recreate the table as an external table
Incorrect
Automatic liquid clustering is available for Unity Catalog managed tables.
CThe command did not take effect; run CREATE OR REPLACE TABLE without a CLUSTER BY clause to reset it
Incorrect
Replacing the table without CLUSTER BY AUTO turns automatic clustering off; it does not make keys get selected.
DThe table is too small to benefit, so no keys were chosen; nothing needs fixing
Correct
Automatic liquid clustering may not select keys for a table that is too small to benefit; the heuristics decide whether clustering is cost-beneficial.
Automatic liquid clustering (CLUSTER BY AUTO) on Unity Catalog managed tables relies on predictive optimization, which analyzes the query workload and changes keys only when the expected savings outweigh the clustering cost. It might not select keys when the table is too small, already well clustered, not queried often, or not on Databricks Runtime 15.4 LTS or above; the skip reason appears on the AUTO LIQUID row in the History tab.
An analyst labels orders with SELECT order_id, CASE WHEN amount > 100 THEN 'medium' WHEN amount > 1000 THEN 'large' ELSE 'small' END AS size FROM orders. An order of 1,500 is labeled 'medium', and no order is ever labeled 'large'. Which change fixes the query?
APut WHEN amount > 1000 THEN 'large' before WHEN amount > 100 THEN 'medium'
BChange ELSE 'small' to ELSE 'large' so that the large amounts fall through to it
CChange both comparisons from > to >= so that the boundaries are included
DAdd WHERE amount > 1000 to the query so the large orders are labeled
Show the answer and why
APut WHEN amount > 1000 THEN 'large' before WHEN amount > 100 THEN 'medium'
Correct
CASE returns the result of the first condition that evaluates to true, so the stricter test must come first.
BChange ELSE 'small' to ELSE 'large' so that the large amounts fall through to it
Incorrect
Amounts above 1000 already match the first condition, and orders of 100 or less would then be labeled 'large'.
CChange both comparisons from > to >= so that the boundaries are included
Incorrect
An amount of 1,500 still satisfies the first condition, which is evaluated before the second.
DAdd WHERE amount > 1000 to the query so the large orders are labeled
Incorrect
The filter removes the other orders from the result, and the first condition still labels the rest 'medium'.
A searched CASE expression evaluates its conditions in order and returns the result for the first condition that is true, or the ELSE value if none is. Ordering the conditions from the most to the least restrictive gives each order the intended label.
The table main.ops.daily_exports is liquid clustered on (region, product_id). Its only workload is now a nightly job that reads the whole table, so the team decides the table should stop being clustered, whether on keys chosen by hand or by predictive optimization. Which statement does that?
AALTER TABLE main.ops.daily_exports CLUSTER BY AUTO
This turns off automatic maintenance for the table; it is not the documented way to remove clustering keys.
CALTER TABLE main.ops.daily_exports CLUSTER BY NONE
Correct
CLUSTER BY NONE is the documented syntax to remove clustering keys.
DOPTIMIZE main.ops.daily_exports FULL
Incorrect
OPTIMIZE FULL reclusters the records with the current keys; it does not remove them.
To remove clustering keys from a liquid clustered table, use ALTER TABLE table_name CLUSTER BY NONE. CLUSTER BY AUTO hands key selection to predictive optimization, and OPTIMIZE FULL reclusters the data with the keys the table already has.
Automatic liquid clustering was turned on for the managed table main.web.events months ago. Before comparing the filters in its query workload with its data layout, an analyst wants to confirm that the feature is still enabled and see which columns are its clustering keys today. What shows both?
ADESCRIBE TABLE main.web.events, which shows clusterByAuto and clusteringColumns
BSHOW PARTITIONS main.web.events, which lists the partition columns of the table
CThe table's Lineage tab in Catalog Explorer, which lists related tables
DThe query profile of the latest query on the table, with its operators
Show the answer and why
ADESCRIBE TABLE main.web.events, which shows clusterByAuto and clusteringColumns
Correct
DESCRIBE TABLE shows clusterByAuto set to true when automatic clustering is on, and clusteringColumns lists the current keys.
BSHOW PARTITIONS main.web.events, which lists the partition columns of the table
Incorrect
Liquid clustering is not compatible with partitioning, so clustering keys are not partition columns.
CThe table's Lineage tab in Catalog Explorer, which lists related tables
Incorrect
Lineage shows where data comes from and goes, not clustering settings.
DThe query profile of the latest query on the table, with its operators
Incorrect
A query profile shows how one query executed, not the table's clustering settings.
To check whether a table has automatic liquid clustering enabled, use DESCRIBE TABLE or SHOW TBLPROPERTIES. When it is enabled, the clusterByAuto property is true, and the clusteringColumns property shows the current clustering columns, whether they were selected automatically or manually.
SELECT store_id, city FROM stores WHERE city LIKE 'new york%' returns no rows, although the city column holds values such as 'New York' and 'NEW YORK - Queens'. The analyst wants every row whose city starts with those two words in any letter case. Which WHERE clause returns them?
AWHERE city LIKE 'New York%'
BWHERE city ILIKE 'new york%'
CWHERE city LIKE ALL ('new york%', 'New York%')
DWHERE city LIKE ANY ('new york%', 'NY%')
Show the answer and why
AWHERE city LIKE 'New York%'
Incorrect
like matches the letters of the pattern literally, so 'NEW YORK - Queens' is still missed.
BWHERE city ILIKE 'new york%'
Correct
ilike matches the pattern case-insensitively, so every letter case of the two words matches.
CWHERE city LIKE ALL ('new york%', 'New York%')
Incorrect
With ALL, a value must match every pattern, and no city matches both.
DWHERE city LIKE ANY ('new york%', 'NY%')
Incorrect
ANY needs one pattern to match, but like still matches letters literally, so 'New York' is missed.
like returns true if str matches the pattern, which is matched literally apart from its wildcard symbols. ilike returns true if str matches the pattern case-insensitively, so WHERE city ILIKE 'new york%' keeps 'New York', 'NEW YORK - Queens' and any other letter case of the prefix.