Snapshot Isolation and Time Travel

A snapshot is an immutable list of data files. A query resolves the catalog pointer once when it is planned, then reads that snapshot to the end. Suppose a revenue query starts at 10:02:30 on snapshot 42 and runs for 90 seconds. A commit at 10:03 creates snapshot 43, and the running query never notices: its files stay in place, because nothing deletes a file that a retained snapshot lists.

Concurrent writers share 1 pointer

Writers take no locks. Each one reads the current snapshot, writes its data files, then tries to swap the pointer from the snapshot it read to its own. This is optimistic concurrency: assume nobody else committed, and check at the swap. When the swap fails, the writer reads the snapshot that beat it and asks whether that commit touched anything its own commit depends on.

Time	Writer A: web orders	Writer B: app orders	Pointer
10:00	reads 42	reads 42	42
10:02	writes 3 files	writes 2 files	42
10:03	swap 42 to 43 succeeds		43
10:03		swap 42 to 43 fails	43
10:03		43 only added files: rebuild metadata on 43	43
10:04		swap 43 to 44 succeeds	44

Writer B rewrote 1 small metadata file and reused its 2 data files untouched. The retry stays cheap while the 2 commits are independent. When they overlap, the loser cannot rebase, because its output was computed from data that has since changed:

Winner committed	Loser attempted	Outcome
append to 2026-10-24	append to 2026-10-24	rebase metadata and commit
append to 2026-10-23	overwrite 2026-10-23	conflict: the overwrite missed new rows, recompute
overwrite 2026-10-23	overwrite 2026-10-23	conflict: the file to replace is gone, recompute
compaction of 2026-10-22	append to 2026-10-22	rebase: the append removes no files

Writers A and B both read snapshot 50 of orders and append new files to the 2026-10-24 partition. A swaps first. What happens to B?

B fails and must recompute its rows, since both commits touched the same partition
B's swap succeeds anyway and replaces A's snapshot, so A's appended rows disappear
B blocks on a partition lock until A's swap completes, then commits normally
B's swap fails, then B rebuilds its metadata on snapshot 51 and reuses its data files

Time travel

Retained snapshots stay queryable. The history is in the snapshots metadata table. Adding a version or timestamp clause to a query pins it to an older state.

SELECT
  snapshot_id,
  committed_at,
  operation
FROM "orders$snapshots"
ORDER BY committed_at DESC
LIMIT 4

snapshot_id	committed_at	operation
44	2026-10-24 10:04	append
43	2026-10-24 10:03	append
42	2026-10-24 02:15	overwrite
41	2026-10-23 23:50	append

SELECT
  order_date,
  SUM(amount) AS revenue
FROM orders FOR VERSION AS OF 41
GROUP BY order_date

SELECT
  COUNT(*) AS order_count
FROM orders FOR TIMESTAMP AS OF TIMESTAMP '2026-10-24 09:00:00 UTC'

Store the snapshot id beside every published report, and the report can be regenerated exactly while that snapshot is retained. Compare 2 snapshots to see which rows a job changed. Roll back a bad commit by pointing the table at an earlier snapshot, a metadata commit that copies no data.

Retention sets the horizon

Snapshot expiry deletes snapshots older than the retention window, then deletes data files that no surviving snapshot lists. Retention is therefore a cost knob. If the nightly job overwrites a 50GB partition and retention is 7 days, the table keeps up to 7 replaced copies, about 350GB beyond its live size. A query pinned to an expired snapshot fails with an error.

Retention on orders is 7 days. On 2026-11-05 an analyst runs a query pinned to snapshot 41, committed on 2026-10-23. What happens?

It returns current data, because the engine falls back to the nearest snapshot it still holds
It fails, because snapshot 41 expired and files only it listed may already be deleted
It succeeds, because data files are immutable and nothing deletes them once written
It succeeds slowly, because the engine rebuilds snapshot 41 by replaying later commits

About this lesson

This section is part of the Storage Layers: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.