I’ve been using the following code for years to determine the latest table version using Databricks’ Time Travel feature without any issues. However, after adding a new row to the table recently, I encountered the following error:

Question

Cannot time travel Delta table to version 1. Available versions: [3, 23].

This behavior is unexpected, as the code has worked reliably until now.

Here is the code I’m using:

from delta.tables import DeltaTable

import pyspark.sql.functions

dt = DeltaTable.forPath(spark, saveloc)

latest_version = int(dt.history().select(max(col("version"))).collect()[0][0])

lastest_table_dropped = (

spark.read.format("delta")  

.option("versionAsOf", latest_version)  

.load(saveloc)  

.createOrReplaceTempView("maxversion")  

)

start_table_dropped = (

spark.read.format("delta")  

.option("versionAsOf", 1)  

.load(saveloc)  

.createOrReplaceTempView("allprior")  

)

I can see that Databricks recognizes version 3 as the latest, but why is it suddenly not possible to time travel back to version 1?

Any insights would be greatly appreciated!

Answer 1

Hello Stephen01

For the matter you have posted here, please reach out to our support channel using the Azure Portal.

Then we can address the issue with the experts of Databricks.

Please accept this answer, if it's fine for you.

Answer 2

Hi @Stephen01

Thanks for posting your query!

Cannot time travel Delta table to version 1. Available versions: [3, 23].

The error you are getting indicates that the Delta table no longer has version 1 available for time travel. This will happen due to the retention policies and VACUUM operations that manage the storage of old versions of the table.

Delta Lake retains table versions based on the retention threshold for transaction log files and the frequency and specified retention for VACUUM operations. If you run VACUUM with the default settings, you will only be able to time travel up to the last 7 days. Any older versions, like version 1 in your case, might have been removed if they’re beyond the retention period.

Set delta.checkpointRetentionDuration to X days to retain checkpoints longer, allowing access to older versions.

Next, execute the following command on your Delta table:

spark.sql(f"""
    ALTER TABLE delta.`path`
    SET TBLPROPERTIES (
        delta.logRetentionDuration = 'interval X days',
        delta.deletedFileRetentionDuration = 'interval X days',
        delta.checkpointRetentionDuration = 'X days'
    )
""")

Reference: Remove unused data files with vacuum Work with Delta Lake table history

Similar thread: https://stackoverflow.com/questions/79415296/databricks-deltalake-cannot-time-travel-delta-table-to-version-1-available-ve

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.