Perhaps my statement (title) is wrong, however, we are experiencing the same behaviour experienced here https://community.fabric.microsoft.com/t5/Data-Engineering/Change-Data-Feed-bug-Unable-to-reconstruct-state-despite-recent/td-p/4663530
As described here, https://www.mssqltips.com/sqlservertip/7962/microsoft-fabric-automatic-table-maintenance-checkpoint-statistics/ checkpointing is an optimization.
Official documentation across the board mentions the need for logRetentionDuration and file retention:
https://learn.microsoft.com/en-us/fabric/data-engineering/delta-lake-time-travel?tabs=sparksql
No indication of needing checkpointRetention or its impact.
Checkpointing is described as an optimization across the board in official channels, however those channels don't indicate that CDF/TT will fail if the optimization files get removed.
However, CDF fails if checkpoint files have been removed within the log retention window. Take the following scenario:
We have both the delta logs (with a long retention) and the unreferenced parquet files (not vacuumed and a long retention) for the CDF version. However, previous checkpoints have been automatically removed due to the default checkpointRetentionDuration being 2 days.
Therefore, retrieving CDF versions of records are always working if done within 2 days of the transaction, but always fail if done after 2 days even though we still maintain the delta log and the unreferenced files.
Many user forums indicate users having this same experience where the solution is to set checkpointRetentionDuration to the same duration as your logRetention.
Is this how it's supposed to work? Is this a confirmed bug? If it's by design, can Microsoft document it somewhere?
I imagine that the failure is due to reading the delta log, seeing the checkpoint, and no longer being able to reference the checkpoint file and instead of traversing the log for the cumulative changes (which it would do normally), it just fails. Considering the optimization isn't actually needed, it shouldn't fail there. It should revert to traversing the logs/parquet files for TT and CDF.