Why is time travel returning the wrong snapshot?

Summary

• VERSION AS OF reads the version you name. In the Delta Lake source, TIMESTAMP AS OF resolves to the latest commit at or before the timestamp, so a wrong-looking snapshot usually traces back to how the timestamp was read.
• A timestamp string with no zone is cast with the Spark session time zone (spark.sql.session.timeZone). Put the zone in the string and compare against DESCRIBE HISTORY.
• A version's timestamp depends on its log file, so timestamp time travel can break after you copy a table directory. Time travel by version is unaffected.
• RESTORE is recorded as a new version, and a read with no time travel clause returns the most recent snapshot.
• Time travel needs both the log and the data files. VACUUM deletes data files only. The documented defaults are 30 days for delta.logRetentionDuration and 7 days for delta.deletedFileRetentionDuration.

Why is time travel returning the wrong snapshot?

Why is time travel returning the wrong snapshot?

VERSION AS OF reads the version you name. In the Delta Lake source, TIMESTAMP AS OF resolves to the latest commit that happened at or before the timestamp, not to the closest commit. When the result looks wrong, the cause is usually how the timestamp was interpreted, how the commit timestamps were recorded, or what a later operation did to the table. When the version or timestamp is outside what the table still retains, the query fails with an error instead of returning another snapshot.

What usually causes an unexpected snapshot?

Cause	What happens	What to do
Time zone	A timestamp string with no zone is cast using the Spark session time zone	Read spark.sql.session.timeZone, or put the zone in the string
Copied table	A version's timestamp depends on its log file, so timestamp time travel can break after you copy the table directory	Time travel by version, which is unaffected
RESTORE	RESTORE is recorded as a new table version	Look for a RESTORE operation in DESCRIBE HISTORY
No time travel clause	A plain read returns the most recent snapshot	Add VERSION AS OF to pin the result

How do I debug a mismatch?

Step 1: list the versions and their commit timestamps.

DESCRIBE HISTORY my_table LIMIT 10;

The output has a row for each write, newest first, with version, timestamp and operation columns (the Delta docs show WRITE, UPDATE and DELETE). Once you know the version you expect, query it directly:

SELECT * FROM my_table VERSION AS OF 123;

Step 2: check the session time zone.

spark.conf.get("spark.sql.session.timeZone")

The Spark migration guide says that a string with no time zone information uses the zone from spark.sql.session.timeZone, and Delta Lake casts the time travel expression the same way. The setting defaults to the local time zone on open-source Spark, and to Etc/UTC on Databricks serverless compute. An explicit zone, as in the Delta docs, removes the ambiguity:

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

Step 3: check retention.

SHOW TBLPROPERTIES my_table;

Look for delta.logRetentionDuration and delta.deletedFileRetentionDuration.

Why does time travel fail instead of returning a snapshot?

To query a previous version you must retain both the log and the data files for it. Data files are deleted only when you run VACUUM, and the default threshold is 7 days. VACUUM does not delete log files. They are cleaned up after checkpoints are written, and the default log retention is 30 days. To keep 30 days of history even when VACUUM runs, set delta.deletedFileRetentionDuration to 30 days. The docs warn that this may raise storage costs.

ALTER TABLE my_table SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = '30 days');

Log cleanup can also remove a version that is younger than the retention interval. Delta Lake needs all consecutive log entries since the previous checkpoint, so with versions 0 through 19 and a checkpoint at version 10, cleaning up the entry for version 0 means you cannot time travel to versions 1 through 9.
The Delta Lake source defines the messages you may see:

• Cannot time travel Delta table to version <userVersion>. Available versions: [<earliest>, <latest>].
• The provided timestamp (<userTimestamp>) is before the earliest version available to this table (<commitTs>). Please use a timestamp after <timestampString>.
• Timestamp <providedTimestamp> is after the table's latest version (<lastCommitTimestamp>). Use a timestamp at or before <maximumTimestamp>.

How does Databricks handle time travel?

• The @ syntax puts the point in the table name: people10m@20190101000000000 (the timestamp format is yyyyMMddHHmmssSSS) or people10m@v123.
• In Databricks Runtime 18.0 and above, time travel queries are blocked if they request a version older than deletedFileRetentionDuration (default 7 days). For Unity Catalog managed tables this applies to Databricks Runtime 12.2 and above.
• The docs advise using only the past 7 days for time travel unless you have set both the data and log retention configurations to a larger value.
• Predictive optimization runs VACUUM on Unity Catalog managed tables. To support extended time travel, set delta.deletedFileRetentionDuration before you enable it.

FAQs

Does RESTORE rewind the version number?

No. In the Databricks docs example, RESTORE(version=1) is recorded as table version 3, and the log entries it adds have dataChange set to true.

Why did my timestamps change after I copied the table?

The Delta docs say the timestamp of version N depends on the timestamp of the log file for version N, so timestamp time travel can break after a copy. The Delta protocol also defines in-commit timestamps (delta.enableInCommitTimestamps), which store the commit timestamp inside the commit. Readers use it for time travel on commits written after the feature was enabled.

Why does the same query return different data on repeated runs?

Delta tables auto update, so a read with no version or timestamp can return different results if the underlying data changes. Pin the read with versionAsOf or VERSION AS OF.