Time Travel — Querying Historical Versions

In this lecture, let's explore Delta Lake's time travel capability — how it works, hands-on, and where it's genuinely useful.

Setup

Create a new notebook in your Delta Lake folder (we'll call it 05-timetravel-in-delta), attach a cluster — a serverless cluster is perfectly fine for this — and set your catalog and schema:

sqlCopy
%sql
USE CATALOG workspace;
USE SCHEMA default;

Building a Table With Real History

To actually see time travel in action, we need a table with a meaningful sequence of changes. Let's create one, and walk it through several transactions.

Step 1: Create the Table

sqlCopy
%sql
CREATE OR REPLACE TABLE employees (
  employee_id  INT     COMMENT 'Unique employee identifier',
  name         STRING  COMMENT 'Full name',
  department   STRING  COMMENT 'Department name',
  salary       DOUBLE  COMMENT 'Annual salary in USD',
  status       STRING  COMMENT 'active or terminated'
)
USING DELTA
COMMENT 'Employee records — used for time travel demo';

This alone creates version 0 — no data yet, just the table definition.

Step 2: Initial Load — 8 Employees

sqlCopy
%sql
INSERT INTO employees VALUES
  (1,  'Alice Nguyen',    'Engineering', 95000.00, 'active'),
  (2,  'Bob Patel',       'Engineering', 88000.00, 'active'),
  (3,  'Carol Santos',    'Engineering', 92000.00, 'active'),
  (4,  'David Kim',       'Engineering', 78000.00, 'active'),
  (5,  'Eva Müller',      'Marketing',   72000.00, 'active'),
  (6,  'Frank Osei',      'Marketing',   68000.00, 'active'),
  (7,  'Grace Lin',       'Marketing',   74000.00, 'active'),
  (8,  'Hiro Yamamoto',   'Marketing',   69000.00, 'active');

This creates version 1 — 8 employees loaded.

Step 3: Engineering Gets a 10% Raise

sqlCopy
%sql
UPDATE employees
SET    salary = ROUND(salary * 1.10, 2)
WHERE  department = 'Engineering';

This creates version 2.

Step 4: A New Hire Joins

sqlCopy
%sql
INSERT INTO employees VALUES
  (9, 'Ingrid Larsson', 'Engineering', 85000.00, 'active');

Another version created (version 3, after an automatic maintenance operation — see below).

Step 5: An Employee Is Terminated

sqlCopy
%sql
UPDATE employees
SET    status = 'terminated'
WHERE  employee_id = 6;

Another new version.

Looking at the Full History

sqlCopy
%sql
DESCRIBE HISTORY employees;

Running this shows the complete timeline — versions 0 through 6 in this case. Here's what each one represents:

• Version 0 — CREATE OR REPLACE TABLE (table created, no data)
• Version 1 — WRITE (the initial 8-employee insert)
• Version 2 — UPDATE (the 10% engineering raise). You can even see the predicate used: department = 'Engineering'. Scrolling into the operation metrics shows exactly what happened — one file deleted, one file added, four records updated.
• Version 3 — an automatic OPTIMIZE operation, executed by Databricks itself as routine table maintenance (we'll cover OPTIMIZE properly in an upcoming lecture) — think of it as Databricks reorganizing the underlying file structure for efficiency.
• Version 4 — WRITE (the new hire insert)
• Version 5 — UPDATE (the termination)
• Version 6 — another automatic OPTIMIZE

The key takeaway: everything gets recorded as a version — not just your own inserts/updates/deletes, but also automatic maintenance operations Databricks performs behind the scenes. Every version comes with both a version number (0, 1, 2, 3...) and a timestamp — giving you a complete, precise timeline of the table's entire life.

What Is Time Travel?

Time travel means going back into this history and querying the table exactly as it looked at a specific point in time.

Querying the table normally gives you the current state:

sqlCopy
%sql
select * from employees

This returns 9 records — the current, latest state (8 initial + 1 new hire, with one marked terminated).

But since we have the full timeline, we can also ask: what did this table look like before the engineering raise?

Querying by Version Number

sqlCopy
%sql
SELECT   employee_id, name, department, salary, status
FROM     employees VERSION AS OF 1
ORDER BY department, employee_id;

Since we know version 1 was the initial load (before the raise), this returns the original 8 employees, with their pre-raise salaries.

sqlCopy
%sql
SELECT   employee_id, name, department, salary, status
FROM     employees VERSION AS OF 2
ORDER BY department, employee_id;

Version 2 is right after the raise — still 8 employees, but now with the engineering salaries bumped up by 10%.

Compare that against the current state:

sqlCopy
%sql
SELECT   employee_id, name, department, salary, status
FROM     employees
ORDER BY department, employee_id;

You can literally trace one person's salary through time — their value before the raise (version 1), after the raise (version 2), and now (current). Same employee, three different snapshots.

Querying by Timestamp

If you don't know the exact version number, but you have a rough sense of when something happened, you can query by timestamp instead:

sqlCopy
%sql
SELECT   employee_id, name, department, salary, status
FROM     employees TIMESTAMP AS OF '2026-05-28T13:26:15.000+00:00'
ORDER BY department, employee_id;

You don't need to hit the exact commit timestamp either — querying even a second before the actual update timestamp will still return the state as it was right before that change (i.e., the previous version's snapshot). Timestamp-based queries are often more practical than version numbers, since you usually have a better sense of when something happened than which exact version number it corresponds to.

Time Travel in PySpark

The same capability is available through the DataFrame API too:

pythonCopy
df_v1 = spark.read \
    .format("delta") \
    .option("versionAsOf", 1) \
    .table("employees")

print(f"Version 1 row count: {df_v1.count()}")
display(df_v1.orderBy("department", "employee_id"))

There's also a timestampAsOf option, working the same way as TIMESTAMP AS OF in SQL.

A Real Audit Scenario

Let's put this together with a realistic question: "Who earned more than $80,000 in Engineering, before the raise?"

sqlCopy
%sql
SELECT   employee_id,
         name,
         department,
         salary AS salary_before_raise,
         ROUND(salary * 1.10, 2) AS salary_after_raise
FROM     employees VERSION AS OF 1
WHERE    department = 'Engineering'
AND      salary > 80000
ORDER BY salary DESC;

This combines time travel with ordinary filtering — querying an old version of the table, exactly as if it were the current one, complete with WHERE clauses and calculated columns. This is a genuinely realistic pattern for audits, compliance checks, or "what changed" investigations.

How Time Travel Actually Works

It's worth understanding the mechanism, briefly: Delta does not store a full copy of the table for every version. It stores the Parquet data files, plus the transaction log. When you time travel to a given version, Delta reads the log, figures out exactly which Parquet files were part of the table at that version, and reads only those files. No duplication, no separate snapshot storage — the transaction log alone is enough to reconstruct any past state.

Where Time Travel Is Useful

• Auditing — query the table exactly as it existed at a specific business moment.
• Debugging — compare current data against a version you know was correct, to pinpoint where something went wrong.
• Reporting — produce point-in-time snapshots without building any separate snapshotting infrastructure.
• Rollback — if you can identify a known-good version, you can use it to recover from a bad update or a corrupted load.

An Important Limitation

Time travel is only possible as far back as the old Parquet files still physically exist in storage. There's a maintenance operation called VACUUM (which we'll cover in an upcoming lecture) that cleans up old, unreferenced files to save storage costs. If your table has been vacuumed and only, say, one month of old files were retained, you simply cannot time travel further back than that — the underlying files are gone. Time travel depends entirely on those historical files still being present.

Summary

That's Delta Lake's time travel — a powerful, built-in capability for auditing, debugging, reporting, and rollback, all without any extra snapshot infrastructure.

See you again. Keep learning, and keep growing!