Forum Discussion

Mauro89's avatar
Mauro89
Super User
5 months ago
Solved

Delta Table time travel not readable

Hi folks,

 

I face some challenges if I want to get a certain delta table version via the following command:

 

ver = 9

deltaTable = "Tables/xyz"

spark.sql(f"SELECT * FROM delta.`{deltaTable}` VERSION AS OF {int(ver)}")
 
Error message: AnalysisException: Cannot time travel Delta table to version 9. Available versions: [10, 31].
 
But if I go to the related Lakehouse and check the folder "_delta_log" I see prio versions than 10.
"OPTIMIZE" done, but no effect, "VACUUM" never run on this table.
 
Would appreciate some ideas about it.
 
Thanks and regards!
 
Lakehouse
Notebook
  • V-yubandi-msft's avatar
    V-yubandi-msft
    5 months ago

    Thank you for sharing the screenshot. When I refer to an unbroken sequence, I mean that the transaction log versions should increase consecutively without any gaps, such as 3, 4, 5, 6, etc. The sequence in your screenshot appears consistent.

     

    Missing files like 00000000000000000000.json, 00000000000000000001.json, and 00000000000000000002.json are not necessarily a problem, since Delta can remove older log files based on log retention settings.

     

    With a checkpoint at version 10, Spark may only be able to reconstruct the table starting from that checkpoint if earlier logs needed for version 9 are no longer available. In this case, time travel would start from version 10.

     

    Thanks....

6 Replies

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    5 months ago

    Hi Mauro89 ,

    Thank you for reaching out to the Microsoft Fabric Community. The error means that version 10 is the earliest version you can use for time travel. Although you might see older files in the _delta_log folder, Delta Lake can only restore a table version if the complete transaction log chain is present. If any earlier logs are missing, outside the retention window, or not part of a valid checkpoint chain, Spark won’t let you time travel to those versions.

    To check which versions are available, run

    DESCRIBE HISTORY delta.`Tables/xyz`

    This will list the versions you can use for time travel.

     

    Regards,

    Yugandhar.

    • Mauro89's avatar
      Mauro89
      Super User
      5 months ago

      Hi V-yubandi-msft,

       

      thanks for your prompt response.

      First, if I just run your code without the "sparkisch" context like blow, I get wired behavior as copilot starts to execute and does some tasks. Iam pretty sure this in not intended behavior 😉

       

      If I run:

      spark.sql(f"DESCRIBE HISTORY delta.`{deltaTable}`").show(truncate=False)
      then I also see the versions below 10. So what else could be the reason?
       
      Best regards!

       

      • V-yubandi-msft's avatar
        V-yubandi-msft
        Community Support
        5 months ago

        Thank you for checking and confirming that DESCRIBE HISTORY still displays versions below 10.

        This suggests the issue could be related to how Delta reconstructs the table state from the transaction logs. Although earlier versions are listed in the history, time travel needs a complete and continuous transaction log chain starting from a valid checkpoint. If the checkpoint starts at version 10, or if some earlier JSON log files are missing or unreadable, Spark may not be able to reconstruct version 9.

         

        It could be helpful to look at the _delta_log folder to see if a checkpoint file starts at version 10, and to confirm that the JSON log files before version 10 are available and form an unbroken sequence.

         

        Thank You.

  • YassineHachguer's avatar
    YassineHachguer
    Regular Visitor
    5 months ago

    It could be related to log retention or checkpointing. Even if older files exist in _delta_log, they may not be usable for time travel if the checkpoint or metadata chain starts at version 10. You might want to check the table history with DESCRIBE HISTORY to confirm which versions are actually available.