Home › Learn › Databricks interview prep › Maintenance commands

Databricks interview prep · data engineering

OPTIMIZE, VACUUM and compaction: what each does

OPTIMIZE, Z-order, liquid clustering, VACUUM, auto compaction, deletion vectors, REORG, RESTORE and CLONE: what each does, what it does not, and the hard follow-ups.

Maintenance commandsRow-level securityCompute choiceJoinsSkew and shuffleAQE, Photon, POCost and performance leversDeclarative and semanticAsset BundlesLakeflow ConnectUniFormVector Searchdbt and ODCSEgress and ingressMaintenance in picturesClustering and compactionMaintenance strategySlow writesBackup and recoveryTaggingQuestion bank

Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.

Write adds new filesOPTIMIZE joins smallfilesOld files still on diskVACUUM deletes them

OPTIMIZE makes the new files; VACUUM removes the old ones. Storage goes up in between.

Start here

One idea explains it all

A Delta table is Parquet files plus a transaction log. Every write adds new files and records "add these, remove those". Nothing is edited in place. So small files pile up (OPTIMIZE), old files pile up (VACUUM), and time travel works only while the old files and log entries still exist.

Reference

What each command does

CommandWhat it doesWhat it does NOT doUse it when
OPTIMIZERewrites many small files into fewer big ones (about 1 GB target). Old files are marked removed but stay on disk.Delete anything. Change the data.Many small files from streaming or frequent appends.
OPTIMIZE … ZORDER BYCompaction plus putting rows with similar values of the chosen columns in the same files, so skipping works on several columns.Work incrementally (repeat runs get costlier). Help past 3 or 4 columns. Use partition columns.Older tables. New tables should use liquid clustering.
Liquid clustering (CLUSTER BY)Replaces partitioning and Z-order. Incremental: OPTIMIZE only touches data not yet clustered. Keys can change without rewriting the table. CLUSTER BY AUTO picks keys from query history.Combine with partitioning or ZORDER on the same table.Default for new tables, high-cardinality filters, skew, changing access patterns.
VACUUMPhysically deletes files no longer referenced by the log and older than the retention period (7 days by default).Compact. Touch _delta_log.Storage cost control; GDPR physical deletion.
Optimized writesShuffles before writing so each task writes fewer, bigger files.Fix files that already exist.Partitioned tables, MERGE-heavy jobs.
Auto compactionAfter a write, runs a small compaction on the files that write made.Replace a full OPTIMIZE.Streaming sinks you cannot schedule OPTIMIZE for.
Predictive OptimizationRuns OPTIMIZE, VACUUM and ANALYZE for you on Unity Catalog managed tables when its cost model says it pays.Cover external tables.The default for managed tables.
ANALYZE TABLE … COMPUTE STATISTICSComputes table and column statistics for the query planner (join order, broadcast choice).Replace Delta's file-level min/max stats.Tables used in big joins.
Deletion vectorsDELETE, UPDATE and MERGE mark rows as deleted in a side file instead of rewriting the whole file.Remove the rows physically.Cheap small updates.
REORG TABLE … APPLY (PURGE)Rewrites files that carry deletion vectors so deleted rows are really gone. Follow with VACUUM.Free space until VACUUM runs.Right to erasure (GDPR).
RESTOREMoves the table back to an older version (a new commit).Work if the old files were vacuumed.Undo a bad write.
CLONEShallow: copies metadata and points at source files. Deep: copies the data too.Keep a shallow clone alive if the source is vacuumed.Tests, backups, migration.

Gotcha

Time travel needs two things

The log entry (delta.logRetentionDuration, 30 days by default) and the data files (delta.deletedFileRetentionDuration, 7 days by default). Vacuuming at zero hours breaks time travel, long-running readers and streams that read older versions.

Hard problem

Concurrent writers and conflicts

Delta uses optimistic concurrency: two writers that touch the same files collide at commit (ConcurrentAppendException, ConcurrentDeleteReadException). Fixes: make writers touch disjoint data, put the partition or cluster predicate in the MERGE condition, use deletion vectors with row-level concurrency, retry with backoff. Do not lower the isolation level without knowing the trade-off.

Hard problem

Erasing a person from a huge table

DELETE with deletion vectors is only logical. Run REORG … APPLY (PURGE), then VACUUM past the retention period. Also cover derived copies: downstream tables, clones, backups and the change data feed. Cluster on the user key so the delete is cheap.

Practice

Questions to rehearse

  1. OPTIMIZE versus VACUUM: what does each do, and which frees storage?
  2. What happens if you VACUUM with retention 0?
  3. Z-order versus liquid clustering versus partitioning: when each?
  4. What are deletion vectors and what is the downside?
  5. Why can RESTORE fail?
  6. How do you fix ConcurrentAppendException?
In an interview, say: OPTIMIZE creates the new files, VACUUM removes the old ones, and neither changes the data. Never set VACUUM retention to zero in production.

FAQ

Questions people ask

Does OPTIMIZE delete old files?

No. It marks them removed in the log. VACUUM deletes them after the retention period.

Can I partition and use liquid clustering together?

No. Liquid clustering replaces partitioning and Z-order on a table.

Who runs these on managed tables?

Predictive Optimization can run OPTIMIZE, VACUUM and ANALYZE for Unity Catalog managed tables. External tables need your own jobs.

Next

Where to go next

All Databricks interview topics · All data engineering · Databricks on this site