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.

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.

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 messWhy it happensThe command
Too many tiny filesEvery small write makes at least one fileOPTIMIZE (compaction)
Files that are not grouped by what you filter onRows land in arrival orderZ-order or liquid clustering
Old files nobody uses any moreRewrites leave the previous files behindVACUUM
Deleted rows still inside filesDELETE with deletion vectors only marks themREORG … APPLY (PURGE)

The whole story

From 200 tiny files to a clean table

1. Streaming-style writes200 tiny files, avg 20.5 KB300,000 rows, 4,095 KB2. OPTIMIZE17 new files in the table217 files on disk (old ones dashed)3. OPTIMIZE ZORDER BY12 files in the table229 files on disk4. VACUUM RETAIN 012 files on diskold files really deleted

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

  1. Look at the current files and find small ones (under the minimum file size).
  2. 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).
  3. Read each bin, write one bigger Parquet file, then commit "remove these N, add this one".
  4. 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

After plain OPTIMIZE: every file holds customers from the whole range 0–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–9990–999WHERE customer_id = 42 reads 17 of 17 files (measured)After OPTIMIZE … ZORDER BY (customer_id): each file covers a narrow slice0–8283–166167–249250–332333–416417–499500–582583–666667–749750–832833–916917–999WHERE customer_id = 42 reads 1 of 12 file (measured)

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

Version 100 needs file AOPTIMIZE rewrites A intoBVACUUM RETAIN 0 deletes AReading version 100 fails

Time travel needs the old files.

What I ranResult (measured)
VACUUM … DRY RUN with the default 7 days0 files to delete: everything was too new
VACUUM RETAIN 0 HOURSfiles 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 VACUUMfailed: Not all files from version 0 are available in file system.
Read a shallow clone after its source was vacuumedfailed: 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

One data file, twelve rows. DELETE removes three of them.row 1row 2row 3row 4row 5row 6row 7row 8row 9row 10row 11row 12Without deletion vectors: rewrite the whole file without those rows (cost grows with file size).With deletion vectors: leave the file alone, write a tiny bitmap next to it:001100001000Readers load the bitmap and skip rows marked 1. REORG … APPLY (PURGE) later rewrites the file for real.

Measured: deleting 2973 rows from a 60,000-row table added 4 bitmaps and rewrote 0 data files.

StepWhat happens inside
DELETEThe 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 / MERGEOld row versions are marked in the bitmap; the new versions are written to a new file.
ReadThe 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.

RESTORE and CLONE

Undo a mistake, copy a table

CommandMeasured result
Bad DELETE then RESTORE to version 0rows 4,958 → 10,000
SHALLOW CLONE2 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

SettingWhat it doesMeasured
Optimized writesShuffles before writing so each task writes fewer, bigger files40 files → 1
Auto compactionAfter a write commits, compacts small files if enough pile up in a partition60 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

  1. Why does disk usage go up after OPTIMIZE?
  2. Why did the dry run delete nothing?
  3. Compaction reduced the file count but the query still read every file. Why?
  4. What does a deletion vector actually contain and where is it stored?
  5. Why did RESTORE fail after VACUUM?
In an interview, say: OPTIMIZE adds new files, VACUUM removes old ones, Z-order or liquid clustering makes skipping work, deletion vectors make small deletes cheap, and PURGE makes them real.

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