Skip to content

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

    Partly covered by: Query Execution Order, Window Functions

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 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?

  1. APredictive optimization blocks key selection; turn it off
  2. BCLUSTER BY AUTO works only on external tables; recreate the table as an external table
  3. CThe command did not take effect; run CREATE OR REPLACE TABLE without a CLUSTER BY clause to reset it
  4. 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.

Question 2 · choose 1

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?

  1. APut WHEN amount > 1000 THEN 'large' before WHEN amount > 100 THEN 'medium'
  2. BChange ELSE 'small' to ELSE 'large' so that the large amounts fall through to it
  3. CChange both comparisons from > to >= so that the boundaries are included
  4. 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.

Databricks documentation

Question 3 · choose 1

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?

  1. AALTER TABLE main.ops.daily_exports CLUSTER BY AUTO
  2. BALTER TABLE main.ops.daily_exports DISABLE PREDICTIVE OPTIMIZATION
  3. CALTER TABLE main.ops.daily_exports CLUSTER BY NONE
  4. DOPTIMIZE main.ops.daily_exports FULL
Show the answer and why
  • AALTER TABLE main.ops.daily_exports CLUSTER BY AUTO

    Incorrect

    CLUSTER BY AUTO lets predictive optimization choose the keys; the table still has clustering keys.

  • BALTER TABLE main.ops.daily_exports DISABLE PREDICTIVE OPTIMIZATION

    Incorrect

    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.

Question 4 · choose 1

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?

  1. ADESCRIBE TABLE main.web.events, which shows clusterByAuto and clusteringColumns
  2. BSHOW PARTITIONS main.web.events, which lists the partition columns of the table
  3. CThe table's Lineage tab in Catalog Explorer, which lists related tables
  4. 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.

Question 5 · choose 1

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?

  1. AWHERE city LIKE 'New York%'
  2. BWHERE city ILIKE 'new york%'
  3. CWHERE city LIKE ALL ('new york%', 'New York%')
  4. 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.

Practice domain 5 →Practice all domains →