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

  1. Someone reports wrong data
  2. DESCRIBE HISTORY to find the bad version
  3. Query VERSION AS OF the previous version to compare
  4. Is the old version still readable (not vacuumed)? Yes: RESTORE TABLE ... TO VERSION AS OF n. No: Rebuild from source data or a clone.
  5. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic