Time Travel Retention, Data Fixes and RESTORE in Delta Lake

1.How far back you can travel

A historical version is readable only while two things still exist: its entry in the transaction log, and the data files it points to. Each is removed on its own schedule, by a separate mechanism:

- Data files are deleted when VACUUM runs against the table. With predictive optimization enabled, Databricks runs VACUUM for you automatically. - Log files are removed automatically after table versions are checkpointed.

Two table properties set these thresholds. In each name, <format> is delta or iceberg.

The shorter of the two windows wins. Once VACUUM has removed the data files for a version, the log may still describe that version, but you can no longer read it. Newer runtimes enforce this. In Databricks Runtime 18.0 and above, a time travel query is blocked if it asks for a version older than deletedFileRetentionDuration. For Unity Catalog managed tables, this applies from Databricks Runtime 12.2.

To keep 30 days of readable history, set delta.deletedFileRetentionDuration = "interval 30 days", which matches the log default. On those same runtimes, logRetentionDuration must be greater than or equal to deletedFileRetentionDuration. The cost is storage: more old data files are kept. You can set these properties when you create the table or later with ALTER TABLE.

Sources12

2.Using an old version as a query source

A time-travelled table reference works like any other table in a FROM or USING clause. That means you can read old rows and write them into the current table, all in SQL. The documentation gives three patterns.

Recover deleted rows. If a user's rows were deleted by mistake, select those rows from yesterday's version and insert them back:

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

Undo bad updates. Use yesterday's version as the source of a MERGE. Matched rows are overwritten with their earlier values: MERGE INTO my_table target USING my_table TIMESTAMP AS OF date_sub(current_date(), 1) source ON source.userId = target.userId WHEN MATCHED THEN UPDATE SET *.

Compare points in time. Subtract last week's distinct count from today's to get the number of new customers. One subquery reads my_table, and the other reads my_table TIMESTAMP AS OF date_sub(current_date(), 7).

All three use a relative timestamp expression, so the same query works on any day. None of them changes the table's history. The fixes are ordinary new writes, recorded as new versions.

Sources1

3.Rolling the whole table back with RESTORE

When the whole table needs to go back, not just some rows, use RESTORE. It accepts the same two forms of time travel argument and returns the table to an earlier state. You can restore a table that was already restored, and you can restore a cloned table.

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

Four facts about RESTORE are worth knowing:

- Permission: you need MODIFY on the table. A SELECT on an old version only reads, but RESTORE writes. - Timestamp formats: when restoring by timestamp, use yyyy-MM-dd HH:mm:ss or yyyy-MM-dd. - Retention still applies: after VACUUM or a manual delete has removed a version's data files, you can't restore to that version. - It is a new write: RESTORE adds a new version, and its log entries have dataChange set to true. A downstream job such as a streaming read sees the restored files as new data, which can cause duplicates.

When it finishes, RESTORE returns one row of metrics, including num_restored_files and table_size_after_restore.

Sources31

Exam traps

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

1. 1.Table history is kept for 30 days, so you can always time travel 30 days back.Why is that wrong?

   Time travel also needs the version's data files. VACUUM can remove those after the 7-day deletedFileRetentionDuration default, so both settings have to be raised to reach further back.

   Covered in How far back you can travel

2. 2.RESTORE just rewinds the table pointer, so downstream consumers are not affected.Why is that wrong?

   RESTORE is a data-changing commit (dataChange = true). Downstream readers, such as streaming jobs, may process the restored files again as new data.

   Covered in Rolling the whole table back with RESTORE

Practise it for real

Make a mistake on a scratch Delta table, then use time travel to inspect and repair it

1. 1.Run DESCRIBE HISTORY on a scratch Delta table you own, after making at least two writes and then a DELETE.

   Why: You need the version number and timestamp of the state you want to go back to.

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

2. 2.Run SELECT count(*) on the table, then on the same table with VERSION AS OF set to the version just before the DELETE.

   Why: This confirms that the old version still contains the rows the DELETE removed.

   You should see: The historical count is higher than the current count.

3. 3.Run the same historical query again using the @v shorthand, for example table_name@v<version>.

   Why: This shows that @v and VERSION AS OF read the same version.

   You should see: The same count as the VERSION AS OF query.

4. 4.Run RESTORE TABLE on the table TO VERSION AS OF that version, then run DESCRIBE HISTORY LIMIT 1.

   Why: RESTORE rolls the table back by committing a new version. It does not erase history.

   You should see: A metrics row from RESTORE, and a newest history entry with operation RESTORE.