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.
Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.
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
| Command | What it does | What it does NOT do | Use it when |
|---|---|---|---|
| OPTIMIZE | Rewrites 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 BY | Compaction 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. |
| VACUUM | Physically 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 writes | Shuffles before writing so each task writes fewer, bigger files. | Fix files that already exist. | Partitioned tables, MERGE-heavy jobs. |
| Auto compaction | After a write, runs a small compaction on the files that write made. | Replace a full OPTIMIZE. | Streaming sinks you cannot schedule OPTIMIZE for. |
| Predictive Optimization | Runs 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 STATISTICS | Computes 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 vectors | DELETE, 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). |
| RESTORE | Moves the table back to an older version (a new commit). | Work if the old files were vacuumed. | Undo a bad write. |
| CLONE | Shallow: 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
- OPTIMIZE versus VACUUM: what does each do, and which frees storage?
- What happens if you VACUUM with retention 0?
- Z-order versus liquid clustering versus partitioning: when each?
- What are deletion vectors and what is the downside?
- Why can RESTORE fail?
- How do you fix ConcurrentAppendException?
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