OPTIMIZE every hour on a 20 TB table
Symptoms
- DBU spike
- Queries already hit one date partition
Interactive Databricks interview questions on OPTIMIZE, Z-ORDER, Liquid Clustering. Practice with the matching lesson. Small files from MERGE and streams get compacted. Z-ORDER and liquid clustering make filters skip files.
Question 1 of 9
What does OPTIMIZE do?
Answer it out loud, then reveal. Play steps through like the simulators.
Case 1 of 3 · symptoms
Symptoms
Indexed as FAQ. Open any item if you prefer a list to Play.
Showing 9 of 9 questions.
What does OPTIMIZE do?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Compacts small files into larger ones so scans launch fewer tasks.
Why: 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 they will ask: Does OPTIMIZE make MERGE faster, slower, or both?
Z-ORDER vs liquid clustering — when do you pick each?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Z-ORDER is a rewrite you schedule. Liquid clustering is incremental on write (newer DBR).
Why: 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 they will ask: How do you prove skipping worked?
OPTIMIZE ran 3 hours and the next query was unchanged. Why?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Filters don't match clustered columns, or the reader bypassed Delta, or stats weren't used.
Why: 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 depth: 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 they will ask: What's the write-amplification tradeoff?
A streaming bronze job lands around 1,200 files an hour. Which two table properties attack that at write time instead of leaving it for a nightly OPTIMIZE?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: delta.autoOptimize.optimizeWrite, which shuffles before writing to produce fewer larger files, and delta.autoOptimize.autoCompact, which compacts small files right after a write.
Remember these
Why: Optimized writes add a shuffle in front of the write so each task produces one healthier file instead of one file per Spark partition, which is where the 1,200-files-an-hour number usually comes from. Auto compaction then checks after a successful commit whether the table has accumulated enough small files and, if so, runs a small compaction as a follow-up commit. Both are table properties, so they apply to every writer rather than to one job's code, which matters when three pipelines append to the same bronze table. They reduce how often you need OPTIMIZE but they do not replace it: neither one reorganizes data by a clustering key, and auto compaction targets modest file sizes rather than a full bin-pack. The cost is on the write path — the extra shuffle and the follow-up commit make each micro-batch slower, which is a real trade on a latency-sensitive stream.
ALTER TABLE ops.bronze.orders_raw SET TBLPROPERTIES (
'delta.autoOptimize.optimizeWrite' = 'true',
'delta.autoOptimize.autoCompact' = 'true'
)Common mistake: Turning both on and dropping the scheduled OPTIMIZE, then wondering why selective filters still read the whole table.
Follow-up they will ask: What does the extra shuffle do to a one-minute micro-batch?
Explain how Delta decides a file cannot possibly match a WHERE clause, and what OPTIMIZE ZORDER changes about that decision.
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Each file's add entry in the log carries per-column min/max and null counts; the scan drops files whose range refutes the predicate, and Z-ORDER tightens those ranges.
Remember these
Why: When Delta commits a data file it records statistics for the leading columns of the schema, bounded by delta.dataSkippingNumIndexedCols. At read time the planner evaluates the predicate against those ranges and skips any file it can prove holds no matching row, so the scan never opens it. If rows for a given region are scattered across every file, each file's min/max for region spans the whole alphabet and nothing can be refuted. OPTIMIZE ZORDER BY (region, order_date) rewrites the data so rows with similar values for those columns land together, which narrows each file's ranges until the predicate becomes refutable. Three things defeat it: a filter column that sits past the indexed-column cutoff, a long string column whose statistics are truncated, and wrapping the column in a function so the predicate no longer compares against the raw column. The query profile is the proof — it reports files pruned against files read for the scan.
-- stats cover only the leading columns of the schema
ALTER TABLE ops.silver.orders
SET TBLPROPERTIES ('delta.dataSkippingNumIndexedCols' = '16');
OPTIMIZE ops.silver.orders ZORDER BY (region, order_date);Common mistake: Believing Z-ORDER builds an index. It reorders rows so existing file statistics become useful; there is no secondary structure to look up.
Follow-up they will ask: Your filter column is the 40th column in the schema. What are your options?
You added a nightly OPTIMIZE and the storage bill went up rather than down. Is that expected?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Yes — OPTIMIZE writes new files and only marks the old ones removed in the log, so both copies occupy storage until VACUUM deletes the unreferenced ones.
Remember these
Why: A Delta commit never deletes bytes. OPTIMIZE adds the compacted files and records remove actions for the originals, which keeps time travel to earlier versions working and lets in-flight readers finish against the file list they already resolved. Those removed files stay in object storage until a VACUUM runs and they are older than delta.deletedFileRetentionDuration, so a table that is optimized nightly and never vacuumed holds every generation of itself. Running OPTIMIZE more often makes this worse, because each run creates another full set of superseded files for the data it touched. The fix is to schedule VACUUM alongside OPTIMIZE with a retention that respects your recovery requirement, and to check the VACUUM commit's metrics to confirm it actually deleted something. On Unity Catalog managed tables predictive optimization can handle both, but you should still be able to point at the commits that prove it ran.
VACUUM ops.silver.orders; -- deletes unreferenced files past retention
SHOW TBLPROPERTIES ops.silver.orders ('delta.deletedFileRetentionDuration');Common mistake: Running VACUUM with a very short retention to reclaim the space, which breaks time travel and can fail concurrent readers.
Follow-up they will ask: How do you pick a retention window you can defend to both finance and legal?
What decides the file size OPTIMIZE aims for, and when would you deliberately ask for smaller files?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Databricks tunes the bin-packing target automatically; you can pin it with delta.targetFileSize, and smaller files are the right call on a MERGE-heavy table.
Remember these
Why: OPTIMIZE bin-packs small files toward a target size rather than producing one giant file, and on Databricks that target is chosen for you based on the table's size unless you override it. delta.targetFileSize pins it explicitly, and delta.tuneFileSizesForRewrites tells Delta to treat the table as rewrite-heavy and favour sizes that keep MERGE cheap. The tension is between read and write: large files mean fewer tasks and less listing for a full scan, but copy-on-write means changing one row rewrites a whole file, so a 1 GB file is 1 GB of rewrite for a single update. On a silver table that takes hourly upserts, smaller files cut the rewrite amplification you measured in numTargetFilesRemoved, at the cost of more tasks per scan. On an append-only gold table that BI scans end to end, go the other way. Decide from the dominant access pattern and then verify with the MERGE commit metrics and the scan's task count, not from a number someone quoted in a blog post.
ALTER TABLE ops.silver.orders SET TBLPROPERTIES (
'delta.targetFileSize' = '128mb',
'delta.tuneFileSizesForRewrites' = 'true'
)Senior depth: File size is a read/write trade, so I pick it from whichever side dominates the bill. I would measure both ends before touching it: bytes scanned and task count per typical query, and numTargetFilesRemoved per MERGE. If the table is both heavily scanned and heavily merged, that is usually a modelling signal — the mutable grain and the analytical grain want to be different tables — rather than a file-size tuning problem.
Common mistake: Pushing the target file size up to speed up scans on a table whose real cost is an hourly MERGE, which then rewrites more bytes per run.
Follow-up they will ask: Which metric would you watch for a week after changing it?
You enabled deletion vectors. DELETEs and MERGEs got much cheaper, then the morning dashboards got slower. Explain both halves.
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Deletion vectors make deletes merge-on-read — the write only marks rows, and readers pay to apply those marks until a rewrite materializes them.
Remember these
Why: With delta.enableDeletionVectors set, a DELETE, UPDATE or MERGE can record which row positions are no longer valid in a side file instead of rewriting the Parquet, which is why the write collapses from minutes to seconds. The cost moves to the read path: every scan must apply the vectors, and a file whose rows are 90% deleted is still read in full because the bytes are still there. As vectors accumulate over days, the effective rows per byte scanned drops and dashboards slow down. OPTIMIZE materializes them as part of compaction, so a maintenance schedule is no longer optional on a table with deletion vectors. When you need the deleted bytes physically gone — a GDPR erasure, for instance — REORG TABLE ... APPLY (PURGE) forces the rewrite so a later VACUUM can remove the old files. Note the reader requirement too: deletion vectors are a table feature, so clients too old to understand it cannot read the table correctly.
ALTER TABLE ops.silver.orders
SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');
REORG TABLE ops.silver.orders APPLY (PURGE);
VACUUM ops.silver.orders;Senior depth: Deletion vectors move cost from writers to readers, which is usually the right direction for a table with frequent small deletes and a maintenance window. The two things I insist on before enabling them are a real OPTIMIZE schedule, because unmaterialized vectors degrade every reader, and a documented purge path for erasure requests, because 'deleted' now means two different things depending on whether you are a query or a regulator. I would also check that every consumer of the table — external engines especially — supports the table feature before turning it on.
Common mistake: Telling an auditor the rows are deleted after a DELETE with deletion vectors on, when the bytes are still in the Parquet until a purge and a vacuum.
Follow-up they will ask: How would you tell from the table that vectors have been accumulating?
Predictive optimization is enabled on your Unity Catalog managed tables. Do you delete the scheduled OPTIMIZE jobs?
OPTIMIZE, Z-ORDER, Liquid Clustering · tap to open the answer
Say this first: Only for the managed tables it covers — external and legacy-metastore tables still need your job, and SLA-critical tables still need deterministic timing.
Remember these
Why: Predictive optimization runs maintenance operations such as OPTIMIZE and VACUUM on Unity Catalog managed tables, choosing which tables and when from observed read and write patterns, and it bills as its own usage line rather than on your job cluster. Its coverage is the first thing to check: external tables and anything still in the legacy metastore are outside it, so deleting those jobs silently stops maintenance. The second is timing. The service decides when work is worthwhile; if a table must be compacted in the ten minutes between the nightly load and the first dashboard refresh, keep an explicit OPTIMIZE in that job so the guarantee is yours. Verify rather than assume: DESCRIBE HISTORY shows OPTIMIZE and VACUUM commits whether a human or the service made them, so you can confirm coverage per table and then retire the redundant jobs. Watch the billing line too, because maintenance you used to see on an ETL cluster now appears somewhere else.
Senior depth: I treat it as a default with exceptions. Managed tables with no hard timing requirement go to the service and their jobs get deleted after I can see service-made commits in their history. Tables with a post-load latency commitment keep an explicit OPTIMIZE in the pipeline, because an SLA I cannot schedule is not an SLA. Then I would run a periodic audit over table histories to catch the tables that fell through the gap — external paths, legacy metastore, anything created outside the standard pattern — since that gap is silent by construction.
Common mistake: Switching it on, deleting every maintenance job, and discovering months later that the external tables were never covered.
Follow-up they will ask: How would you audit, across 400 tables, which ones have had no maintenance commit in a month?