Time Travel with Delta Lake: Querying Previous Versions

1. Viewing a Table's Transaction History

Every operation that modifies a Delta table is recorded as a new version in a transaction log. You can view this detailed history using the DESCRIBE HISTORY command. This command provides an audit trail showing what changed, when it changed, and who changed it.

To understand what information is available, let's start with the official documentation.

As you've read, each row in the history corresponds to a version of the table. The operation column tells you what kind of change was made (WRITE, MERGE, UPDATE, DELETE, CREATE TABLE), and the operationParameters column gives you the specifics, like the predicates used in a DELETE or UPDATE.

The image below provides a great visual connection between the SQL commands you execute and the resulting table history.

2. Time Travel: Querying Previous Table States

Now that you know how to view the history, the next step is to query it. This is where Time Travel comes in. Delta Lake allows you to query a snapshot of a table using either a version number or a timestamp.

This capability is incredibly useful for several reasons:

• Auditing: Verifying the state of data at a specific time for compliance.
• Debugging: Comparing the current state with a previous, correct state to identify when a bug was introduced.
• Error Recovery: Restoring accidentally deleted or incorrectly updated data.
• Reproducibility: Re-running a machine learning model or report on the exact same data it was originally trained or run on.

The following video provides a comprehensive, hands-on demonstration of the different ways you can perform time travel queries using both SQL and PySpark.

As you saw, there are two primary syntaxes in SQL for time travel:

1. By Version Number: Use VERSION AS OF with the integer version from the table history. This is precise and unambiguous.

   -- Query the state of the table at version 1
   SELECT * FROM my_table VERSION AS OF 1;
   

2. By Timestamp: Use TIMESTAMP AS OF with a timestamp string. Delta Lake will find the latest version of the table at or before that timestamp.

   -- Query the state of the table as it was on a specific date and time
   SELECT * FROM my_table TIMESTAMP AS OF '2023-10-26T12:00:00.000Z';
   

The image below shows how you can use VERSION AS OF to see the exact state of a table after each DML operation.

3. Practice: Auditing Particle Observations

Let's apply these concepts in a hands-on exercise. We'll perform a series of operations on our particle_inventory table and then use DESCRIBE HISTORY and time travel to inspect the changes.

Please open a Databricks SQL notebook and execute the following steps.

Step 1: Build a Table History
Run these commands to create a table and modify it, generating several versions.

-- Create our main inventory table
CREATE OR REPLACE TABLE particle_inventory (
  particle_id INT,
  particle_name STRING,
  mass_gev DOUBLE,
  quantity_observed LONG
) USING DELTA
LOCATION 'dbfs:/tmp/particle_inventory'; -- Using a location for clarity

-- Version 0: Table is created
INSERT INTO particle_inventory VALUES
  (1, 'Proton', 0.938, 10500),
  (2, 'Neutron', 0.939, 9700),
  (3, 'Electron', 0.000511, 25000);
-- Version 1: Initial data insert

UPDATE particle_inventory SET quantity_observed = 26000 WHERE particle_id = 3;
-- Version 2: Update electron count

DELETE FROM particle_inventory WHERE particle_id = 2;
-- Version 3: Delete neutron record

MERGE INTO particle_inventory AS target
USING (SELECT 4 AS particle_id, 'Higgs Boson' AS particle_name, 125.1 AS mass_gev, 92 AS quantity_observed) AS source
ON target.particle_id = source.particle_id
WHEN NOT MATCHED THEN INSERT *;
-- Version 4: Merge Higgs Boson record

Step 2: Inspect the History
Now, use the command you learned to view the history of the particle_inventory table.

DESCRIBE HISTORY particle_inventory;

Analyze the output. You should see 5 versions (0 through 4), corresponding to CREATE TABLE, WRITE, UPDATE, DELETE, and MERGE. Note the version number and timestamp for each operation.

Step 3: Travel Back in Time
Your task is to answer the following questions using time travel queries.

1. What was the quantity_observed for 'Electron' just before it was updated?
2. How many rows did the table have just before the 'Neutron' was deleted?
3. Query the state of the table as it existed after the initial data insert but before any other modifications. Use the version number.
4. Query the state of the table using the timestamp from the UPDATE operation (Version 2).

-- Question 1 & 3: Query the state at version 1, after the first insert.
-- The electron's quantity_observed was 25000.
SELECT * FROM particle_inventory VERSION AS OF 1;

-- Question 2: Query the state at version 2, just before the delete.
-- The table had 3 rows.
SELECT COUNT(*) FROM particle_inventory VERSION AS OF 2;

-- Question 4: Query using the timestamp of the UPDATE operation.
-- First, get the timestamp from the history.
-- DESCRIBE HISTORY particle_inventory; 
-- Let's assume the timestamp for version 2 was '2023-10-27T10:30:00.000Z' (your value will be different).
SELECT * FROM particle_inventory TIMESTAMP AS OF '<paste_timestamp_for_version_2_here>';

A Note on Data Retention
How far back can you travel? By default, Delta Lake keeps the data files required for time travel for 7 days. This is controlled by the table property delta.deletedFileRetentionDuration. While you can increase this period, be aware that doing so will increase your storage costs as more historical data files are preserved.

Conclusion

In this lesson, you've mastered two of Delta Lake's most powerful reliability features: viewing table history and time travel. These tools transform your data lakehouse from a simple data store into a version-controlled, auditable system.

Key Takeaways

• DESCRIBE HISTORY: Your primary tool for auditing all changes made to a Delta table, showing who did what, and when.
• Time Travel: Allows you to query a read-only snapshot of a table at a specific point in the past.
• Two Query Methods: You can time travel using VERSION AS OF with a version number or TIMESTAMP AS OF with a timestamp.
• Key Use Cases: Auditing, debugging data quality issues, recovering from errors, and ensuring reproducible analyses.

Next Steps

You've now seen how Delta Lake tracks versions and lets you query them. But how does this work under the hood? In our next lesson, we will go one level deeper and inspect the Delta Lake transaction log itself. You'll learn how the JSON and Parquet files in the _delta_log directory record every change and make features like time travel possible.