Databricks DE · Topic 2.4

Delta Time Travel

Query or restore an earlier version of a Delta table using its transaction-log history — undo bad writes, audit, reproduce past states, and diff versions. This deep dive walks each with the mechanism and the code.

VERSION AS OFTIMESTAMP AS OFRESTOREretentionCDF

The one-liner

Every write made a version; time travel reads any of them by VERSION AS OF n or TIMESTAMP AS OF '…'. DESCRIBE HISTORY lists versions; RESTORE TABLE rolls back (as a new commit) — all bounded by retention (VACUUM + log retention).

📄 Analogy: it's Google Docs version history for a table — view how it looked yesterday, or restore that version as the current one. (But only as far back as retention keeps the files.)

Travel the versions — drag the slider

▸ drag to a version; versions past retention are VACUUMed

v0 (oldest)v4 (current)

⚠️ v0–v1 are older than deletedFileRetentionDuration (7 days) — VACUUM removed those files, so they're no longer queryable.

1 · Querying the past

Two SQL forms, an @ shorthand, and PySpark read options — all read a past snapshot read-only. Pin a report to a version for reproducible numbers.

Spark SQL

PySpark

SELECT * FROM main.sales.orders VERSION AS OF 41;
SELECT * FROM main.sales.orders TIMESTAMP AS OF '2026-06-20T09:00:00Z';

-- @ shorthand: @v<version> or @<yyyyMMddHHmmssSSS>
SELECT * FROM main.sales.orders@v41;
SELECT * FROM main.sales.orders@20260620090000000;

v_old = spark.read.option("versionAsOf", 41).table("main.sales.orders")
t_old = spark.read.option("timestampAsOf", "2026-06-20").table("main.sales.orders")

2 · The operations — click each

DESCRIBE HISTORY main.sales.orders;
SELECT * FROM main.sales.orders VERSION AS OF 41;
RESTORE TABLE main.sales.orders TO VERSION AS OF 41;

DESCRIBE HISTORY▸

Lists versions newest-first with timestamp, user, operation, and operationMetrics (rows written/updated/deleted). Your starting point to find the bad write.

VERSION / TIMESTAMP AS OF▸

Read-only query of a past snapshot — by commit number or point in time. Great for audits and reproducing reports.

RESTORE TABLE▸

Rolls the table back to a chosen version. It's a new data-changing commit (dataChange=true; history preserved) — and counts as a write for downstream streaming consumers, so make them idempotent.

RESTORE — SQL & PySpark

RESTORE TABLE main.sales.orders TO VERSION AS OF 41;

from delta.tables import DeltaTable
DeltaTable.forName(spark, "main.sales.orders").restoreToVersion(41)

3 · Retention — how far back you can actually go

Time travel is bounded by two independent settings — both must cover the window you want, or old versions become unqueryable.

Property	Controls	Default
`delta.deletedFileRetentionDuration`	how long removed data files are kept (VACUUM horizon)	7 days
`delta.logRetentionDuration`	how long commit/history metadata is kept	30 days

Extend BOTH together (higher storage cost)

ALTER TABLE main.sales.orders SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 60 days',
  'delta.logRetentionDuration'         = 'interval 60 days');

4 · Diffing versions — Change Data Feed

Time travel reads a snapshot; CDF answers "what rows changed between two versions?" — the enterprise pattern for incremental downstream propagation. Must be enabled before the changes you want (not retroactive).

ALTER TABLE main.sales.orders SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

-- row-level changes from v41 onward, tagged by change type
SELECT * FROM table_changes('main.sales.orders', 41);

changes = (spark.read.option("readChangeFeed", "true")
                     .option("startingVersion", 41)
                     .table("main.sales.orders"))
# _change_type ∈ insert | update_preimage | update_postimage | delete
# plus _commit_version, _commit_timestamp

Why it matters

Fast recovery: a bad write or accidental delete is one RESTORE away — no backups needed.
Reproducibility & audit: re-run a report exactly as of month-end, or prove what the data showed on a given day.

🌙 Real-world use case: an ETL job corrupts the orders table at 2am. You DESCRIBE HISTORY, find the last good version, and RESTORE TABLE orders TO VERSION AS OF 41 — fixed in seconds.

Which tool for "the past"?

Need	Use
See the table as it was	`VERSION` / `TIMESTAMP AS OF` (read-only)
Undo a bad write / delete	`RESTORE TABLE` (new commit)
Find the right version to use	`DESCRIBE HISTORY`
Know which rows changed between versions	Change Data Feed (`table_changes`)

Uses, edge cases & limitations

Uses: rollback/undo, auditing, reproducing historical analyses, debugging, version diffs (CDF), reproducible ML/reporting snapshots.
Edge cases: retention caps how far back you can go — VACUUM removes files past delta.deletedFileRetentionDuration (7 days); history metadata past delta.logRetentionDuration (30 days). RESTORE on a streaming source is a data change → may cause duplicate reprocessing (make downstream idempotent). CDF isn't retroactive.
Limitation: not a long-term backup/DR — bounded by retention; extend both properties together for more history (higher storage cost).

Common gotchas

❌ Assuming you can travel back arbitrarily far — VACUUM + retention cap it.
❌ Treating time travel as backup/DR — it's bounded by retention.
❌ Raising deletedFileRetentionDuration without also raising logRetentionDuration — both must cover the window.
❌ Forgetting RESTORE adds a new version (history preserved) and counts as a write for downstream streaming consumers.
❌ Expecting CDF to show changes from before you enabled it — it's not retroactive.