OPTIMIZE, Z-ORDER, Liquid Clustering
OPTIMIZE every hour on a 20 TB table
Symptoms
- DBU spike
- Queries already hit one date partition
OPTIMIZE, Z-ORDER, Liquid Clustering interview questions
Interactive Databricks interview questions on OPTIMIZE, Z-ORDER, Liquid Clustering. Same topic as /learn/databricks/optimize. Small files from MERGE and streams get compacted. Z-ORDER and liquid clustering make filters skip files.
Question 1 of 3
What does OPTIMIZE do?
Answer it out loud, then reveal. Play steps through like the simulators.
Production scenario
Case 1 of 1 · symptoms
All questions on this page
Indexed as FAQ. Open any item if you prefer a list to Play.
beginnerWhat does OPTIMIZE do?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Short: Compacts small files into larger ones so scans launch fewer tasks.
Detailed: It rewrites data files and commits a new Delta version. It does not change business grain. Z-ORDER / CLUSTER BY is extra: it colocates filter columns.
Common mistake: OPTIMIZE as a vacuum of old versions.
Follow-up: Does OPTIMIZE make MERGE faster, slower, or both?
intermediateZ-ORDER vs liquid clustering — when do you pick each?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Short: Z-ORDER is a rewrite you schedule. Liquid clustering is incremental on write (newer DBR).
Detailed: High-cardinality id Z-ORDER can be wasted. Cluster by columns you actually filter. Don't Z-ORDER every column.
Common mistake: Z-ORDER(customer_id, date, country, sku, event_id) 'to be safe'.
Follow-up: How do you prove skipping worked?
seniorOPTIMIZE ran 3 hours and the next query was unchanged. Why?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Short: Filters don't match clustered columns, or the reader bypassed Delta, or stats weren't used.
Detailed: Check PartitionFilters / data skipping in the scan. If you Z-ORDER columns you never filter, you paid a rewrite for nothing. Photon/warehouse still needs a selective predicate.
Senior: Better reads vs extra DBUs and longer MERGE (more rewrite). Measure scan bytes before/after, not just 'we optimized'.
Common mistake: Running OPTIMIZE daily on a table nobody filters.
Follow-up: What's the write-amplification tradeoff?