Home › Learn › Databricks interview prep › Maintenance in pictures
Databricks interview prep · data engineering
Table maintenance in pictures, with real runs
OPTIMIZE, Z-order, VACUUM, deletion vectors, RESTORE, CLONE, optimized writes and auto compaction, drawn and measured on a real Delta table.
Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.
Every number on this page was measured by running the commands on a local open-source Delta table (Spark 3.5.1, Delta 3.2.0). Databricks has Photon, Predictive Optimization and different default file sizes, so your counts will differ, but the behaviour is the same. The script is _guides/code/table_maintenance/run.py.
Start here
One idea explains every command
A Delta table is a folder of Parquet files plus a log that says which files are in the table right now. A write never edits a file: it adds new files and records "add these, stop using those". So three kinds of mess pile up, and each command cleans one of them.
| The mess | Why it happens | The command |
|---|---|---|
| Too many tiny files | Every small write makes at least one file | OPTIMIZE (compaction) |
| Files that are not grouped by what you filter on | Rows land in arrival order | Z-order or liquid clustering |
| Old files nobody uses any more | Rewrites leave the previous files behind | VACUUM |
| Deleted rows still inside files | DELETE with deletion vectors only marks them | REORG … APPLY (PURGE) |
The whole story
From 200 tiny files to a clean table
Real run on a local Delta table. Dashed squares are files the log no longer lists but that still sit on disk until VACUUM. The panels show a sample of the squares, not all of them.
-- 1. the problem: 200 small appends = 200 files SELECT count(*) FROM orders; -- 300,000 rows DESCRIBE DETAIL orders; -- numFiles = 200, avg 20.5 KB -- 2. compaction OPTIMIZE orders; -- 200 files removed from the log, 17 added -- 3. compaction + clustering by a filter column OPTIMIZE orders ZORDER BY (customer_id); -- 17 removed, 12 added -- 4. delete the files the log no longer lists VACUUM orders RETAIN 168 HOURS; -- default; dry run found 0 files to delete (all too new)
Read the picture carefully. After OPTIMIZE the table is faster, but disk usage went up: 217 files on disk, though only 17 belong to the table. Only VACUUM brings it back down, to 12. The default 7-day retention is why a dry run right after OPTIMIZE finds nothing to delete.
OPTIMIZE
What compaction does, step by step
- Look at the current files and find small ones (under the minimum file size).
- Group them into bins up to the target size (about 1 GB by default; I lowered it to 250 KB so this tiny table makes several files).
- Read each bin, write one bigger Parquet file, then commit "remove these N, add this one".
- Old files stay on disk. Queries now open far fewer files.
Result here: 200 files became 17. Readers running during OPTIMIZE keep working, because they read the older version of the log. It does not change data, so it rarely conflicts with appends.
Data skipping
Why compaction alone is not enough
Compaction alone does not help skipping when each file still spans every customer. Z-order (or liquid clustering) groups similar values so most files can be skipped.
Plain OPTIMIZE merged files in arrival order, so each of the 17 files still held customers from every part of the range and the query read all of them. After Z-order, the same query read 1 file. The log stores the minimum and maximum of each column per file, and a filter skips every file whose range cannot match.
VACUUM
What it deletes, and what it breaks
Time travel needs the old files.
| What I ran | Result (measured) |
|---|---|
| VACUUM … DRY RUN with the default 7 days | 0 files to delete: everything was too new |
| VACUUM RETAIN 0 HOURS | files on disk 229 → 12 |
| Read an old version afterwards (VERSION AS OF 100) | failed: SparkFileNotFoundException (the old data file is gone) |
| RESTORE to version 0 after VACUUM | failed: Not all files from version 0 are available in file system. |
| Read a shallow clone after its source was vacuumed | failed: SparkFileNotFoundException (the clone points at files the source vacuum deleted) |
The retention check normally refuses less than 7 days; I switched that safety off to show the damage. Keep the retention longer than your slowest reader or stream, and longer than the time travel you promise.
Deletion vectors
How they work and how they help
Measured: deleting 2973 rows from a 60,000-row table added 4 bitmaps and rewrote 0 data files.
| Step | What happens inside |
|---|---|
| DELETE | The engine finds the matching row positions in each file and writes a small compressed bitmap (a roaring bitmap), either inline in the log or in a .bin file beside the data. The log entry for the data file now points at that bitmap. |
| UPDATE / MERGE | Old row versions are marked in the bitmap; the new versions are written to a new file. |
| Read | The scan loads the bitmap for each file and drops the marked rows. |
| REORG … APPLY (PURGE) | Rewrites only files that carry bitmaps, so the deleted rows are physically gone. |
Measured: deleting 2973 of 60,000 rows touched 4 files, added 4 bitmaps and rewrote 0 files; the row count went 60,000 → 57,027 while the file count stayed 4. PURGE then rewrote 4 files and removed 4 bitmaps (size 577 → 549 KB); files on disk went 8 → 4 after VACUUM.
- Helps: a small DELETE or MERGE costs in proportion to the rows changed, not the file size; writers collide less because they touch rows, not whole files.
- Costs: every read applies the bitmaps; deleted data stays on disk until PURGE and VACUUM; readers must support deletion vectors, so some engines and UniForm cannot read such tables (check the current rules).
RESTORE and CLONE
Undo a mistake, copy a table
| Command | Measured result |
|---|---|
| Bad DELETE then RESTORE to version 0 | rows 4,958 → 10,000 |
| SHALLOW CLONE | 2 files in the clone, 0 Parquet files copied; it points at the source's 4 |
RESTORE is a new commit that makes an old version current again. It works only while the old files exist. A shallow clone is a pointer, not a backup; a deep clone copies the data and can be a backup.
On the write path
Optimized writes and auto compaction
| Setting | What it does | Measured |
|---|---|---|
| Optimized writes | Shuffles before writing so each task writes fewer, bigger files | 40 files → 1 |
| Auto compaction | After a write commits, compacts small files if enough pile up in a partition | 60 small appends → 60 files without, 6 with (threshold lowered to 10 files) |
Both add work to the writing job. The section on streaming tiny files below explains when that is a bad trade.
Not measured
Liquid clustering on this page is from documentation
In this local open-source setup, CREATE TABLE … CLUSTER BY (customer_id) worked, but OPTIMIZE on the clustered table failed (failed in this local open-source setup: SparkException 'Boxed Error' during OPTIMIZE (cause not isolated)), so I have no measured numbers for it. The explanation of how it works is on the clustering page and should be read with that in mind.
Practice
Questions to rehearse
- Why does disk usage go up after OPTIMIZE?
- Why did the dry run delete nothing?
- Compaction reduced the file count but the query still read every file. Why?
- What does a deletion vector actually contain and where is it stored?
- Why did RESTORE fail after VACUUM?
FAQ
Questions people ask
Does OPTIMIZE free storage?
No. It adds files. VACUUM frees storage after the retention period.
Is a shallow clone a backup?
No. It points at the source's files, so it breaks if the source is vacuumed.
Next
Where to go next
All Databricks interview topics · All data engineering · Databricks on this site