Databricks Certified Data Engineer Associate free question 9 / 10 · Domain 6: Troubleshooting, Monitoring, and Optimization
You changed the clustering keys of the liquid clustering table shipments (a Unity Catalog managed table) with ALTER TABLE shipments CLUSTER BY (region, ship_date). Every row in the table has a ship_date from 2020 onward. In a job on Databricks Runtime 17.3 LTS, you want to rearrange the rows, including those that were there before the change, by the new keys. What do you do?
- Run OPTIMIZE shipments FULL WHERE ship_date >= '2020-01-01' to have rows of the whole period rearranged by the new keys
- Remove the keys with ALTER TABLE shipments CLUSTER BY NONE and set them again, then let a following OPTIMIZE shipments rearrange the existing rows
- Run OPTIMIZE shipments FULL, with no range condition, to rearrange the rows of the table, including those from before the change, by the new keys
- Run OPTIMIZE shipments FULL WHERE region IN ('EU', 'US', 'APAC') to have the rows of the three regions rearranged
Show the answer and explanation
Answer: C
Why this is correct
Changing the clustering keys does not rewrite existing data. OPTIMIZE ... FULL is what rearranges every row by the new keys, and it is available on Databricks Runtime 16.4 LTS and above. The job runs on 17.3 LTS, so OPTIMIZE shipments FULL is the command to run.
Why the other options are wrong
"Run OPTIMIZE shipments FULL WHERE ship_date >= '2020-01-01' to have rows of the whole period rearranged by the new keys" … OPTIMIZE ... FULL WHERE, which rearranges only the rows you specify, is a feature the documentation marks as "In Databricks Runtime 18.1 and above". It cannot be used in a 17.3 LTS job.
"Run OPTIMIZE shipments FULL WHERE region IN ('EU', 'US', 'APAC') to have the rows of the three regions rearranged" … Using a region as the condition still uses FULL WHERE, which is a feature of Databricks Runtime 18.1 and above. It cannot be used in a 17.3 LTS job.
"Remove the keys with ALTER TABLE shipments CLUSTER BY NONE and set them again, then let a following OPTIMIZE shipments rearrange the existing rows" … The documentation says: "When you change clustering keys, subsequent OPTIMIZE and write operations use the new clustering approach, but existing data is not rewritten." For NONE too, "Setting cluster keys to NONE does not rewrite clustered data", so removing and resetting the keys does not rewrite existing rows. Even an OPTIMIZE without FULL afterwards does not arrange the rows from before the change by the new keys.
Common trap
Running OPTIMIZE ... FULL on a large table whose data is not yet arranged by the keys you specified can take hours. After you change keys, include this rearrangement in the time estimate for your jobs.