Time travel & RESTORE
Databricks · Delta Lake
Theory
Because the transaction log keeps every version, you can query a Delta table as it was in the past. SELECT ... FROM t VERSION AS OF 3 reads version 3, and TIMESTAMP AS OF '2026-11-07 09:00' reads the version that was current at that time. In Python, use spark.read.option("versionAsOf", 3).table(name). DESCRIBE HISTORY lists versions with operation, user and metrics, so you know which number to use.
RESTORE TABLE t TO VERSION AS OF n rolls the table back by writing a new version whose file list equals version n; history is kept, so a restore can itself be undone. Time travel works only while the old data files still exist: VACUUM removes files older than the retention period (7 days by default), after which those versions cannot be read. For example, if a bad job deletes all West orders in version 5, you compare against version 4 and RESTORE to version 4.
Why we use it
Mistakes happen: a bad job overwrites a table or deletes the wrong rows. Time travel lets you audit what changed, reproduce old reports and roll back in seconds without restoring backups.
When to use it
Use time travel for audits, debugging, reproducing reports and quick rollbacks. It is not a long-term backup: old versions disappear after VACUUM. For long retention, keep snapshot tables or deep clones.
How it works
- Someone reports wrong data
- DESCRIBE HISTORY to find the bad version
- Query VERSION AS OF the previous version to compare
- Is the old version still readable (not vacuumed)? Yes: RESTORE TABLE ... TO VERSION AS OF n. No: Rebuild from source data or a clone.
- Table shows correct data in a new version
Syntax
DESCRIBE HISTORY main.sales.orders
SELECT * FROM t VERSION AS OF 4
SELECT * FROM t TIMESTAMP AS OF '2026-11-07 09:00:00'
RESTORE TABLE t TO VERSION AS OF 4
RESTORE TABLE t TO TIMESTAMP AS OF '2026-11-07'
spark.read.option("versionAsOf", 4).table("main.sales.orders")Example
-- 1. Find the bad write
DESCRIBE HISTORY main.sales.orders;
-- 2. Compare current data with the version before it
SELECT region, COUNT(*) FROM main.sales.orders VERSION AS OF 4 GROUP BY region;
SELECT region, COUNT(*) FROM main.sales.orders GROUP BY region;
-- 3. Roll back (creates a new version 6 equal to version 4)
RESTORE TABLE main.sales.orders TO VERSION AS OF 4;
# Python
old = spark.read.option("versionAsOf", 4).table("main.sales.orders")Key points
- VERSION AS OF uses a version number; TIMESTAMP AS OF uses a point in time
- RESTORE adds a new version; it does not erase history
- VACUUM removes old files, which ends time travel to those versions
- Default file retention for VACUUM is 7 days
- Time travel is for recovery and audit, not a replacement for backups