Delta Lake Time Travel: Validate and Compare Historical Results

1.Query a previous version with time travel

Delta Lake keeps a numbered version for every write to a table. Time travel lets you query any of those versions, either by version number or by timestamp, as recorded in the transaction log. To use it, add a clause after the table name. Version numbers come straight from the version column of DESCRIBE HISTORY.

SELECT * FROM people10m TIMESTAMP AS OF '2018-10-18T22:15:12.013Z';
SELECT * FROM people10m VERSION AS OF 123;

The timestamp can be any expression that is, or can be cast to, a timestamp: a literal string, a date string, current_timestamp() - interval 12 hours, or date_sub(current_date(), 1). Neither the timestamp nor the version can be a subquery, so you cannot compute the version with a nested SELECT. There is also a shorthand that puts the version or timestamp in the table name: @v123 for a version, or @ followed by a timestamp in yyyyMMddHHmmssSSS format.

-- Timestamp version
SELECT * FROM people10m@20190101000000000
-- Version number
SELECT * FROM people10m@v123

SELECT * FROM people10m TIMESTAMP AS OF '2018-10-18T22:15:12.013Z';
SELECT * FROM people10m  ?  AS OF 123;

Sources1

2.Validate results and fix mistakes against an earlier version

The documentation lists several uses for time travel. Two matter most to an analyst. The first is re-creating analyses, reports or outputs, which helps with debugging or auditing, especially in regulated industries. When a report figure is disputed, rerun its query against the version that existed when the report was produced and see whether you get the same number. The second is fixing mistakes in your data. You can do that by reading the good rows from an earlier version and writing them back.

INSERT INTO my_table
  SELECT * FROM my_table TIMESTAMP AS OF date_sub(current_date(), 1)
  WHERE userId = 111

For incorrect updates, the same pattern works with MERGE INTO my_table USING my_table TIMESTAMP AS OF ... as the source: matched rows are updated back to their earlier values. To roll back the whole table, RESTORE TABLE ... TO VERSION AS OF or TO TIMESTAMP AS OF returns it to an earlier state. RESTORE requires MODIFY permission on the table. It is also a data-changing operation, so it adds a new version and does not erase the history after the target. Downstream jobs may see the restored files as new data and produce duplicates.

RESTORE TABLE target_table TO VERSION AS OF <version>;
RESTORE TABLE target_table TO TIMESTAMP AS OF <timestamp>;

Sources12

3.Compare historical results and trends across versions

Because a historical version can be queried like any other table, one statement can compare the present with the past. The documented example counts the new customers added in the last week by subtracting last week's distinct user count from today's.

SELECT
(
  SELECT count(distinct userId)
  FROM my_table
)
-
(
  SELECT count(distinct userId)
  FROM my_table TIMESTAMP AS OF date_sub(current_date(), 7)
) AS new_customers

For repeated comparisons, you can save a snapshot as a temporary view, for example CREATE OR REPLACE TEMPORARY VIEW people_10k_v0 AS SELECT * FROM ... VERSION AS OF 0, and then join or aggregate it against the current table. Time travel also gives snapshot isolation for a set of queries on a fast-changing table. If every query in a comparison reads the same version, rows that arrive while the queries run cannot skew the result.

Sources13

4.How far back you can travel: retention and VACUUM

To query an old version you need both its log files and its data files. Two table properties control how long each is kept. logRetentionDuration, 30 days by default, controls how long history is kept. Log files are removed automatically after checkpointing, not by VACUUM. deletedFileRetentionDuration, 7 days by default, is the threshold VACUUM uses when it removes data files that the current version no longer references. Once VACUUM has removed those files, you can no longer query versions older than the retention period. In Databricks Runtime 18.0 and above, and in 12.2 and above for Unity Catalog managed tables, time travel requests older than deletedFileRetentionDuration are blocked.

To compare 30 days of trends, set delta.deletedFileRetentionDuration to "interval 30 days" so it matches the log retention. Keeping more data files raises storage costs.

Sources14

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Because DESCRIBE HISTORY shows 30 days of versions, you can time travel to any of them.Why is that wrong?

   History and time travel have separate thresholds. VACUUM removes unreferenced data files after 7 days by default, and the versions that need those files can then no longer be queried.

   Covered in How far back you can travel: retention and VACUUM

2. 2.You can compute the version for VERSION AS OF with a subquery, such as the MAX(version) from history.Why is that wrong?

   Neither the version nor the timestamp expression can be a subquery. Read the version number from DESCRIBE HISTORY and pass it in as a literal.

   Covered in Query a previous version with time travel

Practise it for real

Use a table's history to measure how many new customers arrived in the last week

1. 1.Run DESCRIBE HISTORY my_table and note the version and timestamp of the writes over the past seven days.

   Why: Time travel can only reach versions that still appear in history and whose data files have not been vacuumed.

   You should see: One row per write, newest first, with operation, userName and timestamp.

2. 2.Run SELECT * FROM my_table TIMESTAMP AS OF date_sub(current_date(), 7).

   Why: This confirms that last week's snapshot is still available before you build a comparison on it.

   You should see: The table as it was seven days ago. If its files were vacuumed, the query fails.

3. 3.Run the new_customers query that subtracts count(distinct userId) at TIMESTAMP AS OF date_sub(current_date(), 7) from the current count.

   Why: Comparing two versions in one statement gives the trend directly.

   You should see: A single row with one column, new_customers.