Time travel and RESTORE
Query or roll back to any earlier version of a table, and what retention does to that promise.
On this page
Show code in
Every code block on the page follows this.
You will learn
- How to query an older version of a Delta or Iceberg table
- How to roll a table back, and what that writes
- Which retention settings limit how far back you can go
- Practical uses: audits, reproducible ML, debugging
Reading the past
spark.read.option("versionAsOf", 12).table("sales") spark.read.option("timestampAsOf", "2025-03-01 00:00:00").table("sales")
SELECT * FROM sales VERSION AS OF 12; SELECT * FROM sales TIMESTAMP AS OF '2025-03-01 00:00:00';
spark.read.option("snapshot-id", 10963874102873).table("db.sales") spark.read.option("as-of-timestamp", 1740787200000).table("db.sales") # epoch millis
SELECT * FROM db.sales VERSION AS OF 10963874102873; SELECT * FROM db.sales TIMESTAMP AS OF '2025-03-01 00:00:00';
A timestamp query resolves to the latest version committed at or before that time. To find versions, use Delta's DESCRIBE HISTORY sales or Iceberg's metadata table SELECT * FROM db.sales.snapshots.
Rolling back
# Delta DeltaTable.forName(spark, "sales").restoreToVersion(12)
-- Delta RESTORE TABLE sales TO VERSION AS OF 12; -- Iceberg CALL catalog.system.rollback_to_snapshot('db.sales', 10963874102873);
A Delta RESTORE does not delete newer history. It writes a new commit whose actions bring back the old file list, so the bad versions are still visible in history and the restore itself can be undone. An Iceberg rollback moves the table's current snapshot pointer back; later snapshots remain until expired.
How far back you can go
| Format | Limit | Defaults |
|---|---|---|
| Delta | Log entries must exist (delta.logRetentionDuration) and data files must not be vacuumed (delta.deletedFileRetentionDuration) | 30 days of log; files removed more than 7 days ago may be vacuumed |
| Iceberg | The snapshot must not be expired (expire_snapshots, history.expire.max-snapshot-age-ms) | Snapshots older than 5 days are eligible when you run expiration |
What it is good for
- Undoing a bad write: a job loaded duplicates; restore to the version before it.
- Debugging: compare today's table with yesterday's:
EXCEPTbetween two versions shows exactly which rows changed. - Reproducible ML: record the table version used for training so the dataset can be rebuilt exactly.
- Audit: history shows who changed what and when.
- Consistent multi-step reads: pin all reads of a job to one version so concurrent writes do not change results mid-job.
SELECT * FROM sales EXCEPT SELECT * FROM sales TIMESTAMP AS OF date_sub(current_date(), 1);
Common mistakes
Treating time travel as backup
Setting a short deletedFileRetentionDuration
Expecting RESTORE to remove history
Key takeaways
- Query old data with VERSION AS OF or TIMESTAMP AS OF.
- Delta RESTORE adds a commit; Iceberg rollback moves the current snapshot pointer.
- Retention settings and cleanup jobs limit how far back you can go.
- Time travel is great for recovery and debugging, but it is not a backup.
Check yourself
3 questions1. What does Delta RESTORE do to newer versions in history?
Show the answer
Keeps them and adds a new commit that restores the old file list. RESTORE is itself a commit, so history is preserved.
2. How do you find Iceberg snapshot ids?
Show the answer
Query the db.table.snapshots metadata table. Iceberg exposes snapshots through metadata tables.
3. Why is time travel not a backup?
Show the answer
Retention is limited and cleanup permanently deletes old files. Old versions disappear after vacuum or snapshot expiration.
Practice it
Interview problems that use this: write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: Delta: table utility commands · Iceberg: Spark queries