🔥 Spark & Databricks · Chapter 7

Delta Lake: ACID, time travel, OPTIMIZE, VACUUM

A Delta table is just Parquet files plus a folder of JSON receipts. Everything — ACID, time travel, rollback — comes from those receipts.

Study this lesson free in the app →

The basics

Delta Lake is a table format that adds a transaction log on top of ordinary Parquet files in object storage, giving a data lake database-like guarantees.

Why it exists: plain Parquet on S3 has no transactions. If a job writing 400 files dies at file 230, readers see half a table. Two jobs writing at once can clobber each other. You cannot update one row, cannot undo a bad load, and cannot ask what the table looked like yesterday.

Delta fixes this with a folder called _delta_log/. Every change writes new Parquet files, then commits one JSON file — 000…042.json — listing which files were added and which were removed. The table is whatever the log says it is. A half-finished write never committed, so nobody sees it.

Mental model: a shop's stock book. The shelves (Parquet files) can hold old and new boxes at once; the book (log) says which boxes are on sale today. Old entries in the book let you reconstruct any past day — as long as the old boxes haven't been thrown out (VACUUM).

Explained

Tuesday 02:10. A Meghalaya Cabs fare job applies a 105% surge multiplier to every ride instead of 1.05. 40 million fares now average ₹19,000.

On plain Parquet this is a restore-from-backup afternoon.

On Delta it is one line: RESTORE TABLE fares TO VERSION AS OF 311. Two minutes, no data copied.

Unless somebody ran VACUUM fares RETAIN 0 HOURS last week to save storage.

The shop's stock book

A shop never repaints boxes on the shelf. When a price changes, a new box goes up and the book records: Tuesday — added box 88, removed box 41. Box 41 stays in the back room.

Want to know what was on sale last Thursday? Read the book up to Thursday. Box 41 is still in the back room, so you can even pull it out.

Once a week, a cleaner throws out back-room boxes that the book stopped listing more than seven days ago. After that, Thursday's list still exists in the book — but the boxes it names are gone. The book is the table. The cleaner is VACUUM.

What's on disk

s3://mc-lake/silver/fares/
├── _delta_log/
│   ├── 00000000000000000000.json   v0  CREATE
│   ├── 00000000000000000001.json   v1  add: part-a, part-b, part-c, part-d
│   ├── 00000000000000000002.json   v2  remove: part-c   add: part-e   (UPDATE)
│   ├── ...
│   └── 00000000000000000010.checkpoint.parquet   ← state up to v10
├── part-a.parquet
├── part-b.parquet
├── part-c.parquet   ← removed at v2, still on disk until VACUUM
├── part-d.parquet
└── part-e.parquet

reader of current version: checkpoint + later JSONs → {a, b, d, e}
reader of v1:              replay to v1           → {a, b, c, d}

How ACID actually works

Atomicity — a write is invisible until its commit file N+1.json is created. Object stores need a put-if-absent guarantee for that (S3 now supports conditional writes; older setups used a DynamoDB-backed log store, and Databricks handles it for you).

Consistency — schema enforcement rejects writes whose columns or types don't match, unless you opt into mergeSchema. Constraints (CHECK, NOT NULL) are validated at commit.

Isolation — readers pin a snapshot version; a long query never sees a half-applied MERGE. Writers use optimistic concurrency: read version N, do the work, try to commit N+1. If someone else committed first, Delta checks whether the two touched the same files. Disjoint appends both succeed; overlapping MERGEs → one gets ConcurrentAppendException / ConcurrentDeleteReadException and must retry.

Durability — data and log live in replicated object storage.

MERGE/UPDATE/DELETE are copy-on-write by default (rewrite affected files); with deletion vectors enabled, they mark deleted rows in a small side file and rewrite later.

The everyday commands

-- Upsert today's corrected fares (idempotent on ride_id)
MERGE INTO silver.fares t
USING updates u ON t.ride_id = u.ride_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

DESCRIBE HISTORY silver.fares;            -- version, timestamp, operation, metrics
SELECT * FROM silver.fares VERSION AS OF 311;
SELECT * FROM silver.fares TIMESTAMP AS OF '2026-11-03 01:00';
RESTORE TABLE silver.fares TO VERSION AS OF 311;   -- new version, no data copy

OPTIMIZE silver.fares WHERE ride_date >= '2026-11-01'
  ZORDER BY (driver_id);                 -- compact + co-locate for skipping

VACUUM silver.fares;                     -- default: retain 168 hours
VACUUM silver.fares DRY RUN;             -- list what would be deleted

ALTER TABLE silver.fares SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 30 days',
  'delta.logRetentionDuration'         = 'interval 30 days');

Want 30 days of time travel? Raise both properties — the log default is already 30 days, but files are kept only 7 days by default.

At 100×

Small files are Delta's chronic illness. A streaming job committing every 30 seconds writes ~2,900 small files a day per partition. Reads slow down from file-open overhead. Fixes: optimized writes / auto-compaction on Databricks, and a scheduled OPTIMIZE on recent partitions.

OPTIMIZE costs compute and temporarily doubles storage for what it rewrites. Target the partitions that changed (WHERE ride_date >= …), not the whole 3-year table each night.

Z-order loses value past ~3–4 columns and must be redone as data arrives. Newer Databricks tables use liquid clustering (CLUSTER BY) which clusters incrementally and replaces both partitioning and Z-order for most tables.

VACUUM on huge tables lists every file; run it on a schedule (daily/weekly) rather than never — a table nobody vacuums keeps every rewritten copy forever, and a MERGE-heavy table can be 10× its live size in storage.

Gotchas

• VACUUM RETAIN 0 HOURS — deletes files that running queries and in-flight writers still need, and kills time travel. Delta blocks retention under 168h unless you disable retentionDurationCheck — that check exists for a reason
• Expecting 30 days of time travel by default — log is 30 days, files 7; the shorter one wins once VACUUM runs
• Treating time travel as a backup — same storage account, same deletion risk; use deep clones or replication for DR
• Deleting files with aws s3 rm — the log still references them; reads fail. Always change data through Delta
• Running OPTIMIZE and expecting storage to drop — it rises until VACUUM
• Two jobs MERGEing into the same partitions at the same time — one fails with a concurrency exception; partition the work or serialise the jobs

The log is the table. Files are just storage.

Time travel needs the log and the files. VACUUM (7 days default) deletes the files.

OPTIMIZE adds a version and grows storage; VACUUM is what shrinks it.

Words to know

In two minutes

• Delta = Parquet data files + deltalog/ of ordered JSON commits (with periodic Parquet checkpoints); each commit is a table version.
• Atomicity comes from the commit: files are written first, then one log entry is created atomically; readers only see committed versions.
• Concurrent writers use optimistic concurrency — if two commits conflict on the same files, one fails with a concurrent-modification exception and must retry.
• UPDATE, DELETE and MERGE never edit files in place: they write new files and mark old ones removed (or use deletion vectors).
• Time travel reads an old version (VERSION AS OF / TIMESTAMP AS OF) by replaying the log — it works only while the old data files still exist.
• OPTIMIZE compacts small files toward ~1GB; ZORDER BY co-locates values so file min/max stats skip more files. Both create a new version and leave old files for VACUUM.
• VACUUM physically deletes files no longer referenced and older than the retention threshold — 7 days (168 hours) by default. Log entries are kept 30 days by default, but time travel past a VACUUM fails.

Test yourself

There are 8 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.