Interactive Databricks interview questions on Liquid Clustering: change your mind later. Practice with the matching lesson. CLUSTER BY replaces rigid directories and full Z-ORDER rewrites — and lets you change the keys later.
Question 1 of 4
You are creating a new fact table and someone proposes PARTITIONED BY (event_date) plus a nightly OPTIMIZE ZORDER BY (customer_id). What would you do instead?
Answer it out loud, then reveal. Play steps through like the simulators.
Case 1 of 2 · symptoms
Symptoms
Indexed as FAQ. Open any item if you prefer a list to Play.
You are creating a new fact table and someone proposes PARTITIONED BY (event_date) plus a nightly OPTIMIZE ZORDER BY (customer_id). What would you do instead?
Liquid Clustering: change your mind later · tap to open the answer
Short: CLUSTER BY (event_date, customer_id) — liquid clustering replaces both the Hive partitioning and the Z-ORDER.
Detailed: CREATE TABLE ... CLUSTER BY (cols) lets Delta organize files by those keys and maintain the layout incrementally as data lands, instead of pinning a physical directory structure and periodically re-sorting the whole table. You cannot have both: a table is either partitioned or clustered, and CLUSTER BY is rejected alongside PARTITIONED BY.
Common mistake: Treating liquid clustering as a new name for Z-ORDER and keeping the partition columns as well.
Follow-up: What actually performs the clustering after rows are written?
Query patterns moved from customer_id to region. What does re-keying cost on a liquid-clustered table versus a partitioned one?
Liquid Clustering: change your mind later · tap to open the answer
Short: ALTER TABLE t CLUSTER BY (region) changes the keys with no rewrite of existing data; repartitioning a Hive-partitioned table means recreating it and rewriting every byte.
Detailed: Clustering keys are table metadata, so the ALTER applies to subsequent writes and to subsequent OPTIMIZE runs, which cluster new and touched data incrementally — old files stay as they are until something rewrites them. Changing PARTITIONED BY is not an ALTER at all, which is exactly why teams stayed stuck with a bad partition column for years.
Common mistake: Expecting the ALTER to re-lay-out history instantly, then declaring clustering broken when the first query looks the same.
Follow-up: What would you run if you genuinely needed history in the new layout today?
Someone partitioned by customer_id and the table is now millions of sub-megabyte files. Walk me through the fix and why clustering prevents a repeat.
Liquid Clustering: change your mind later · tap to open the answer
Short: High-cardinality partition columns create a directory per value so every write drops tiny files; recreate the table with CLUSTER BY (customer_id, event_date).
Detailed: Hive partitioning makes the physical layout a function of cardinality, so a million customers means a million directories and a scan that launches a task per tiny file. Liquid clustering decouples key choice from directory structure: files are written at a normal target size and the keys only decide which rows sit together, with file-level min/max statistics doing the skipping.
Senior: Partitioning is a physical commitment; clustering is a layout the engine maintains for you. I would migrate with a CTAS into a CLUSTER BY table, cut readers over, then verify in the query profile — files pruned versus files read, and bytes scanned before and after. The real win is not that clustering is magic, it is that re-keying later costs an ALTER instead of a full rewrite, so the choice stops being permanent.
Common mistake: Adding more OPTIMIZE runs while keeping the partition column, so compaction fights the partition boundaries forever.
Follow-up: How do you prove skipping actually improved after the migration?
You own 400 tables and nobody can tell you which columns each one is filtered on. Do you just set CLUSTER BY AUTO everywhere?
Liquid Clustering: change your mind later · tap to open the answer
Short: It is the right default for the long tail with unknown or drifting access patterns; keep explicit keys where the predicate is known and under an SLA.
Detailed: CLUSTER BY AUTO lets Databricks choose and adjust clustering keys from observed query patterns and maintain them through predictive optimization, so you are not hand-auditing hundreds of tables. Explicit CLUSTER BY (cols) still wins where the access path is known and critical — a merge key you rely on for MERGE pruning — because you do not want keys drifting under a latency commitment.
Senior: Treat it as a portfolio: AUTO for the tables nobody can characterize, explicit keys for the handful with a contract — merge keys and headline dashboard filters. Then measure instead of assuming: sample query profiles for files-pruned ratios per table, and watch the optimization DBUs, because incremental clustering costs scale with churn rather than table size. That is the improvement over Z-ORDER's full rewrite, not a free lunch.
Common mistake: Turning AUTO on and assuming layout work is now free, with no maintenance running and no check that queries improved.
Follow-up: What would make you override AUTO with explicit keys on a specific table?
