Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 36 ยท Delta Lake

Delta Time Travel and RESTORE

Query old versions of a Delta table with VERSION AS OF and TIMESTAMP AS OF, compare versions, undo mistakes with RESTORE, and understand how retention and VACUUM limit it.

Because a Delta table keeps every change in its log and old data files stay on disk for a while, you can read the table as it was in the past. This is called time travel. It answers questions like "what did this table look like before last night's job?", "which rows changed?" and, most valuable of all, "how do I undo the DELETE I just ran?".

Real Databricks SQL and Python are shown read-only; the stand-ins model the idea with versioned snapshots in plain Python.

Reading an old version

First find the version with the history (see The Delta transaction log):

SQL
DESCRIBE HISTORY main.sales.orders;

Then query it:

SQL
SELECT * FROM main.sales.orders VERSION AS OF 3;
SELECT * FROM main.sales.orders TIMESTAMP AS OF '2024-05-01 08:00:00';

-- shorthand
SELECT * FROM main.sales.orders@v3;

In Python:

Python
old = spark.read.option("versionAsOf", 3).table("main.sales.orders")
old_by_time = spark.read.option("timestampAsOf", "2024-05-01").table("main.sales.orders")

An old version is a read-only snapshot; reading it does not change the table.

Comparing versions

Find what a bad job changed by comparing the current table with the version before it:

SQL
-- rows that existed before but are gone now
SELECT * FROM main.sales.orders VERSION AS OF 7
EXCEPT
SELECT * FROM main.sales.orders;

-- rows that are new
SELECT * FROM main.sales.orders
EXCEPT
SELECT * FROM main.sales.orders VERSION AS OF 7;

For continuous change tracking, Delta's Change Data Feed records inserts, updates and deletes row by row, which is covered in the production chapters.

RESTORE: undo a mistake

SQL
-- oops
DELETE FROM main.sales.orders WHERE order_date >= '2024-01-01';

-- go back
RESTORE TABLE main.sales.orders TO VERSION AS OF 7;
-- or by time
RESTORE TABLE main.sales.orders TO TIMESTAMP AS OF '2024-05-01 08:00:00';

RESTORE does not rewrite history. It creates a new commit that re-adds the files that version 7 had, so the history afterwards says "version 9: RESTORE". You can restore a restore. It works only while the needed data files still exist.

What limits time travel

Two retention settings decide how far back you can go:

  • delta.logRetentionDuration (default 30 days): how long log history is kept.
  • delta.deletedFileRetentionDuration (default 7 days): how long files that were removed from the table are kept on disk before VACUUM may delete them.
SQL
VACUUM main.sales.orders;                   -- deletes unreferenced files older than the retention period
VACUUM main.sales.orders RETAIN 168 HOURS;  -- explicit 7 days

After VACUUM removes files, versions that needed them can no longer be read: the log still lists the version but the data is gone and the query fails. Databricks refuses to vacuum below the default retention unless you disable a safety check, and you should not. A long-running query or a stream reading old versions can break if files disappear underneath it.

Raise the retention if you need longer history:

SQL
ALTER TABLE main.sales.orders SET TBLPROPERTIES (
  'delta.deletedFileRetentionDuration' = 'interval 30 days'
);

Longer retention costs more storage, because old files stay.

Time travel is not a backup strategy. It lives in the same storage as the table, and retention is short. Use it for quick recovery and audits, and keep real backups or replicas for disaster recovery.

Stand-in: snapshots and restore

The stand-in keeps a list of immutable snapshots. AS OF reads one, and restore appends a new snapshot copied from an old one, like Delta's RESTORE (a new commit rather than a rewrite).

Python Stand-in: version history and RESTORE

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second stand-in diffs two versions, as in the EXCEPT queries above, and then simulates VACUUM: files older than the retention period that are no longer referenced are deleted, which makes the old version unreadable.

Python Stand-in: diff versions, then VACUUM breaks old reads

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Good habits

  • Check DESCRIBE HISTORY before running big deletes, and record the version number in your notes or job log.
  • For risky one-off changes, note the version first, so you know where to restore.
  • Do not shorten retention without checking who reads old versions (streams, long jobs, audits).

Common mistakes

  • Relying on time travel as the only backup.
  • Running VACUUM with a very short retention and breaking a running stream.
  • Forgetting that RESTORE is itself a change that other readers will see.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement reads the table as of version 5?

    VERSION AS OF (or TIMESTAMP AS OF) reads a past snapshot without changing the table.
  2. 2. What does RESTORE TABLE do to the history?

    RESTORE is recorded as a new version, so the full history stays intact.
  3. 3. What stops you from time travelling to a very old version?

    After VACUUM or log cleanup, the files or log entries for that version no longer exist.
  4. 4. Is time travel a replacement for backups?

    Time travel helps recover quickly but is not disaster recovery.

Summary

  • Query old snapshots with VERSION AS OF and TIMESTAMP AS OF; compare them with EXCEPT.
  • RESTORE TABLE undoes mistakes by creating a new commit.
  • Retention settings and VACUUM decide how far back you can go.
  • Treat time travel as a safety net, not a backup.

Next: MERGE and Upserts