Time travel and history

Because the log keeps every commit, the table has versions numbered from 0. Reading an old version is replaying the log only up to it.

DESCRIBE HISTORY silver.orders;

SELECT COUNT(*) FROM silver.orders VERSION AS OF 12;
SELECT COUNT(*) FROM silver.orders TIMESTAMP AS OF '2024-06-01 00:00:00';

-- what came in between two versions
SELECT * FROM silver.orders VERSION AS OF 13
EXCEPT
SELECT * FROM silver.orders VERSION AS OF 12;

-- undo a bad load
RESTORE TABLE silver.orders TO VERSION AS OF 12;

v12 = spark.read.option("versionAsOf", 12).table("silver.orders")

What it is for

• Auditing and debugging: "the number changed since yesterday; what came in?".
• Reproducibility: train or recompute on the exact version used before.
• Recovery: RESTORE rolls the table back to a version (the RESTORE itself becomes a new commit, and it can be undone).

The limit: VACUUM

Time travel needs the old data files. VACUUM deletes the files that are not part of the current version and are older than the retention (default 7 days, delta.deletedFileRetentionDuration). After it, the versions that depended on those files can no longer be read. The log history itself is kept for delta.logRetentionDuration (default 30 days).

• Time travel is not a backup: deleting the table or the storage takes the history with it.
• DESCRIBE HISTORY shows who did each operation, when, and how many rows it changed (operationMetrics).