Interview/Databricks

Databricks interview questions: Liquid Clustering: change your mind later

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.

Lesson · Simulation

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.

Production scenario

Liquid Clustering: change your mind later

Nightly ZORDER on a 30 TB table stopped finishing in its window

Symptoms

  • The OPTIMIZE ZORDER job grew from 40 minutes to over six hours as the table grew
  • It rewrites most of the table every night even though about 1% of rows changed
  • Morning dashboards are slow on any night the job is killed for overrunning

All questions on this page

Indexed as FAQ. Open any item if you prefer a list to Play.

Showing 10 of 10 questions.

beginner

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

Say this first: CLUSTER BY (event_date, customer_id) — liquid clustering replaces both the Hive partitioning and the Z-ORDER.

Why: 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 they will ask: What actually performs the clustering after rows are written?

Lesson · Simulation

intermediate

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

Say this first: 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.

Why: 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 they will ask: What would you run if you genuinely needed history in the new layout today?

Lesson · Simulation

senior

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

Say this first: 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).

Why: 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 depth: 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 they will ask: How do you prove skipping actually improved after the migration?

Lesson · Simulation

architect

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

Say this first: 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.

Why: 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 depth: 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 they will ask: What would make you override AUTO with explicit keys on a specific table?

Lesson · Simulation

beginner

Someone hands you a table and says it is clustered. How do you verify that, and on which keys?

Liquid Clustering: change your mind later · tap to open the answer

Say this first: DESCRIBE DETAIL reports clusteringColumns and partitionColumns, SHOW CREATE TABLE prints the CLUSTER BY clause, and DESCRIBE HISTORY tells you whether any OPTIMIZE has run.

Remember these

  • DESCRIBE DETAIL: clusteringColumns, partitionColumns, numFiles, sizeInBytes
  • SHOW CREATE TABLE prints the CLUSTER BY clause, including AUTO
  • A declared key with no OPTIMIZE commit has not reorganized history
  • Files pruned vs files read in the query profile is the real test

Why: DESCRIBE DETAIL is the one-stop answer: clusteringColumns lists the liquid clustering keys, partitionColumns is empty on a clustered table, and numFiles with sizeInBytes gives you the average file size so you can see whether the layout is healthy at all. SHOW CREATE TABLE prints the DDL including CLUSTER BY, which is useful when you want to reproduce the table or check whether it says AUTO. Declaring keys is only half of it — clustering is performed by writers and by OPTIMIZE, so DESCRIBE HISTORY matters: a table declared with CLUSTER BY three months ago that has never had an OPTIMIZE commit is clustered in name only for its historical data. Finally, check a real query in the profile and read files pruned against files read, because that is the number the clustering exists to move.

DESCRIBE DETAIL ops.silver.orders;
SHOW CREATE TABLE ops.silver.orders;
DESCRIBE HISTORY ops.silver.orders;

Common mistake: Trusting the CREATE TABLE statement in the repository, which says nothing about whether maintenance ever ran.

Follow-up they will ask: DESCRIBE DETAIL shows 300,000 files averaging 3 MB on a clustered table. What do you conclude?

Lesson · Simulation

intermediate

How many clustering keys would you declare, and what do you say when someone asks for six?

Liquid Clustering: change your mind later · tap to open the answer

Say this first: Liquid clustering takes up to four keys, and every key you add that nobody filters on dilutes the clustering of the ones they do.

Remember these

  • Up to four clustering keys; fewer usually prunes better
  • Each extra key loosens the per-file ranges of the others
  • Pick keys from query history, weighted by query cost
  • CLUSTER BY AUTO beats guessing when the pattern is unknown

Why: Clustering spreads rows across files so that each key's values are localized, and that localization is a finite budget split across the declared keys. Four is the documented ceiling, but the practical limit is lower: with four keys, each one's min/max ranges are looser than they would be with two, so a filter on a single key prunes less. The selection rule is empirical rather than aesthetic — take the columns that actually appear in WHERE clauses and join conditions from query history, weighted by how much those queries cost, and stop there. The classic waste is including a column because it is in the primary key or because it feels important; a surrogate id nobody filters on contributes nothing but dilution. If you genuinely cannot characterize the access pattern, CLUSTER BY AUTO lets Databricks pick and adjust the keys from observed queries, which is a better answer than guessing six.

CREATE OR REPLACE TABLE ops.silver.orders
CLUSTER BY (country, event_date)
AS SELECT * FROM staging.orders

Common mistake: Carrying over the full list of Z-ORDER columns from the old table, which was already too long for the same reason.

Follow-up they will ask: Which of the two keys in that example does the pruning for a query that filters only on event_date?

Lesson · Simulation

advanced

A liquid-clustered table has no partition directories. The physical plan shows no PartitionFilters at all. So what is pruning the files?

Liquid Clustering: change your mind later · tap to open the answer

Say this first: The same per-file min/max statistics in the Delta log that power data skipping — clustering just makes those ranges tight enough to be useful.

Remember these

  • Clustered tables prune on file statistics, not on directory paths
  • PartitionFilters is absent by design, not because pruning failed
  • The log marks clustered files so OPTIMIZE can work incrementally
  • Judge it on files pruned vs files read in the query profile

Why: Hive partitioning prunes by path: the planner reads the directory names, so the plan shows PartitionFilters and the elimination happens before any file metadata is consulted. A clustered table has a flat file layout, so there is nothing to prune by path and that line disappears from the plan, which is why people conclude pruning is gone. What actually happens is that writers and OPTIMIZE place rows with nearby clustering-key values into the same file, so each file's recorded min/max for those columns is narrow, and the scan refutes files from the log without opening them. The log also tracks which files have already been clustered, which is what lets a later OPTIMIZE do incremental work rather than re-reading the table. The evidence is in the query profile, not the plan text: files pruned against files read, and bytes scanned for the same query before and after clustering.

DESCRIBE DETAIL ops.silver.orders;  -- clusteringColumns, numFiles
-- then compare files pruned vs files read in the query profile

Senior depth: Partitioning gives you coarse, guaranteed elimination on an exact column match; clustering gives you statistical elimination that degrades gracefully across several keys and reorganizes incrementally. The practical consequence is that clustering rarely matches a well-chosen partition column on that column's own filter, but it does not collapse on the second filter column or on high cardinality, which is where partitioning fails badly. I argue it from measured bytes scanned per query class plus maintenance DBUs, because the headline comparison on a single predicate usually flatters partitioning and ignores everything it costs.

Common mistake: Reading explain output, seeing no PartitionFilters, and concluding the migration from partitioning broke pruning.

Follow-up they will ask: What kind of predicate still gets you nothing from either layout?

Lesson · Simulation

advanced

When would you run OPTIMIZE ... FULL on a clustered table, and what does it cost you?

Liquid Clustering: change your mind later · tap to open the answer

Say this first: When history must be laid out under keys it was never written with — and it costs the full rewrite that incremental clustering exists to avoid.

Remember these

  • Plain OPTIMIZE skips files the log already marks as clustered
  • That is why re-keying leaves history in the old layout
  • OPTIMIZE ... FULL reclusters every record, ignoring those marks
  • Cost is a full rewrite plus a pile of files for VACUUM

Why: A plain OPTIMIZE on a clustered table only processes files the log has not already marked as clustered, so its cost tracks the day's change rather than the table's size. That is exactly why the table does not reorganize itself after ALTER TABLE ... CLUSTER BY: the existing files are already marked clustered, just under the old keys. OPTIMIZE ... FULL ignores those marks and reclusters every record, which is the right call in two situations — right after changing keys when historical queries must prune immediately, and after enabling clustering on a table whose data predates it. The cost is a complete rewrite of the dataset: the same bill a nightly ZORDER was running up, plus a large number of superseded files that VACUUM will have to clean. So treat it as a one-off migration step: confirm the new keys are the ones queries really filter on, run it off-peak, and go back to plain incremental OPTIMIZE afterwards.

ALTER TABLE ops.silver.orders CLUSTER BY (region, event_date);
OPTIMIZE ops.silver.orders FULL;

Senior depth: FULL is a migration tool, not a maintenance mode. The decision is whether historical queries matter enough to pay a full rewrite now, and on most time-series tables they do not: the old layout ages out of the hot range on its own while new data lands under the new keys. I would re-key, let plain OPTIMIZE run, and measure pruning separately for the recent and historical date ranges — if the historical range is genuinely in the critical path, that is when FULL earns its bill, once.

Common mistake: Running OPTIMIZE ... FULL on a schedule, which turns incremental clustering back into the nightly full rewrite it replaced.

Follow-up they will ask: If history is cold and the hot range is the last 30 days, do you still need FULL?

Lesson · Simulation

senior

A team wants to cluster by their MERGE key to make the nightly upsert cheaper. Does clustering help writes, or only reads?

Liquid Clustering: change your mind later · tap to open the answer

Say this first: It helps the write, because the expensive part of a MERGE is finding and rewriting candidate target files — and that is a read.

Remember these

  • MERGE's file-finding phase prunes on the same statistics reads use
  • Clustering the merge key cuts numTargetFilesRemoved per run
  • High-cardinality keys cluster fine; they partition terribly
  • A non-selective source cancels the benefit regardless of layout

Why: MERGE first identifies which target files could contain a matching row, then rewrites those files. That identification pushes the ON clause against the per-file statistics, so if the merge key is a clustering key, the matched rows live in a small number of files and the rewrite shrinks with them. This is the main lever on an hourly upsert, and it is visible in numTargetFilesRemoved in the commit metrics. Clustering is also the right tool here precisely because a merge key is usually high cardinality, which is where Hive partitioning fails — a directory per key gives you millions of tiny files, while clustering writes normal-sized files and only decides which rows share them. Two caveats. The source has to be selective too: merging a full snapshot against a clustered table still touches everything. And a second clustering key such as an event date only helps if the MERGE's ON clause mentions it, so the key choice and the statement have to be designed together.

ALTER TABLE ops.silver.orders CLUSTER BY (order_id, event_date);
OPTIMIZE ops.silver.orders;
-- then check numTargetFilesRemoved on the next MERGE commit

Senior depth: Layout is one decision serving two workloads, so I would price both. Clustering on the merge key plus the window column makes the nightly MERGE prune and gives the dashboard's date filter something to work with, which is usually the best compromise. If the two access patterns genuinely conflict, the honest answer is two tables — a mutable serving table clustered for upserts and an append-only analytical table built from it — rather than a layout that is mediocre for both.

Common mistake: Clustering by the merge key while leaving the ON clause as a bare equality on a non-clustered column, so nothing prunes.

Follow-up they will ask: The table is also scanned end to end by BI every morning. Does that change your keys?

Lesson · Simulation

architect

You own a 60 TB table partitioned by event_date that performs acceptably today. Make the case for or against migrating it to liquid clustering, and say how you would do it.

Liquid Clustering: change your mind later · tap to open the answer

Say this first: Migrating is a full rewrite plus a reader cutover because there is no in-place conversion, so it only pays when the partition scheme is already costing you.

Remember these

  • No in-place conversion: migration is CTAS or clone plus rewrite
  • CLUSTER BY and PARTITIONED BY cannot coexist on one table
  • Migrate for measured pain: ZORDER cost, tiny files, drifted predicates
  • replaceWhere writers and path-reading tools break on the cutover

Why: A table is either partitioned or clustered — the two cannot be combined, and you cannot turn an existing partitioned table into a clustered one with an ALTER. The migration is a CREATE TABLE ... CLUSTER BY ... AS SELECT, or a clone followed by a rewrite, then repointing every reader and writer. So the argument has to come from pain you can measure: a second filter column forcing a nightly ZORDER whose cost scales with table size, a partition column with the wrong cardinality producing tiny files or enormous skewed partitions, or a query mix that has drifted away from event_date and cannot be fixed without recreating the table. If the dominant predicate is genuinely event_date and DESCRIBE DETAIL shows healthy file sizes, there is nothing to buy. Also count what breaks: writers using replaceWhere on partition predicates, external tools that read partition paths directly, and anything that assumes the directory layout. The real purchase is optionality — after migrating, the next layout change is an ALTER instead of another migration.

CREATE TABLE ops.silver.orders_clustered
CLUSTER BY (country, event_date)
AS SELECT * FROM ops.silver.orders;
-- cut readers over, then swap names

Senior depth: I would not migrate a healthy partitioned table on principle — the rewrite and the cutover are real risk for no measured gain. What I would do is make clustering the default for new tables and keep a short list of migration candidates ranked by what the current layout costs: nightly ZORDER DBUs, average file size from DESCRIBE DETAIL, and bytes scanned per query class. For the ones that qualify, I would run the CTAS into a new name, dual-write or backfill the gap, verify pruning and row counts on the new table, then swap names in one step so the rollback is a swap back.

Common mistake: Adding CLUSTER BY to the existing DDL in the repository and assuming the next deploy converts the table.

Follow-up they will ask: How would you cut readers over on a table that is written to every hour?

Lesson · Simulation