DeltaFileNotFoundException: [DELTA_TRUNCATED_TRANSACTION_LOG] Error in Streaming Table

[minhhung0507]

I am encountering a recurring issue while working with Delta streaming tables in my system. The error message is as follows:

com.databricks.sql.transaction.tahoe.DeltaFileNotFoundException: [DELTA_TRUNCATED_TRANSACTION_LOG] gs://cimb-prod-lakehouse/bronze-layer/icoredb/dpb_revi_loan/_delta_log/00000000000000000000.json: Unable to reconstruct state at version 899 as the transaction log has been truncated due to manual deletion or the log retention policy (delta.logRetentionDuration=3 days) and checkpoint retention policy (delta.checkpointRetentionDuration=2 days) 

Context:

• I am designing a system that uses Delta format for streaming tables.
• The affected tables have very few transactions or updates, which seems to make them prone to this error.
• Upon inspecting the _delta_log directory, I noticed that only checkpoint versions 900 and 979 exist. However, the error indicates that it is trying to read from version 899

Questions:

1. Why is Databricks attempting to access version 899 when the checkpoint files available start from version 900? Could this be a bug or misconfiguration in Delta Lake's automatic cleanup process?
2. Is it possible that Delta Lake's log and checkpoint retention policies are prematurely removing active checkpoints for tables with minimal updates? If so, how can I adjust these settings to prevent this issue?
3. What are the recommended best practices for managing retention policies (delta.logRetentionDuration and delta.checkpointRetentionDuration) for Delta tables with infrequent updates?

Additional Information:

• Retention settings:
  • delta.logRetentionDuration = "3 days"
  • delta.checkpointRetentionDuration = "2 days"

And even after reconfiguring the value of "logRetentionDuration" = "checkpointRetentionDuration" = 3 days or 7 days, the above error still occurs.

[minhhung0507]

Hi, does anyone have a solution to this problem?

[NandiniN]

Checking.

[NandiniN]

The issue you're encountering with the error DeltaFileNotFoundException: [DELTA_TRUNCATED_TRANSACTION_LOG] is related to Delta Lake's retention policy for logs and checkpoints, which manages the lifecycle of transaction log files and checkpoint files.

This error occurs because Delta Lake is trying to reconstruct the table's state from version 899, but the transaction log files for this version have already been removed as part of the log retention policy. This usually happens if there is no checkpoint file available for the requested version or for versions immediately preceding it.

Delta tables rely on periodic checkpoints to prevent the need for full log replay. If older checkpoint files or their corresponding JSON files have been removed due to the retention policies, operations that require time traveling or version-specific processing might fail.

The default settings for Delta Lake retain:

• Transaction log entries for 30 days (delta.logRetentionDuration)
• Checkpoint files for 2 days (delta.checkpointRetentionDuration)

If the requested version exceeds the retention period, the files may no longer exist, which results in a DeltaFileNotFoundException error.

If you have S3 Versioning enabled on AWS, Soft Delete enabled on Azure, Soft Delete enabled on GCP, or a similar backup mechanism that periodically saves a copy of the files, you should be able to recover your files.

You could also increase delta.logRetentionDuration and delta.checkpointRetentionDuration for the source Delta table.

This KB will be helpful https://kb.databricks.com/delta/deltafilenotfoundexception-when-reading-a-table/

Thanks.