AUTO CDC SCD Type 2 with late-arriving deletes: edge cases the docs don't cover

[LiresaFerizaj]

Hi everyone,

I'm designing a Lakeflow Declarative Pipeline that processes customer profile changes from a CDC feed into an SCD Type 2 Silver table. Events can arrive out of order, sometimes up to 24 hours late. The planned flow:

CREATE OR REFRESH STREAMING TABLE silver_customers;

CREATE FLOW customers_cdc AS AUTO CDC INTO silver_customers
FROM STREAM(bronze_customer_changes)
KEYS (customer_id)
APPLY AS DELETE WHEN operation = 'DELETE'
SEQUENCE BY STRUCT(event_ts, source_lsn)
COLUMNS * EXCEPT (operation, _rescued_data)
STORED AS SCD TYPE 2;

From the docs, I understand that deletes are kept as tombstones for a retention period set by pipelines.cdc.tombstoneGCThresholdInSeconds (default two days), and that a STRUCT in SEQUENCE BY breaks ties by the fields in order. A few things I couldn't find answers to:

1. Deletes after tombstone expiry. If a delete arrives after its tombstone retention has passed, for example during a backfill of old data, what happens? Is the event silently ignored, applied as a new change, or does it cause an error?
2. Tombstone retention and storage. Are there downsides to setting the retention much higher, say 7 days or more, on a table with frequent deletes? For example, does it noticeably affect storage or MERGE performance?
3. Full refresh safety. Our Bronze source files are only kept for 30 days. If the Silver table is fully refreshed, it seems the SCD2 history would be rebuilt only from the data that's still available. Is setting pipelines.reset.allowed = false on Silver the recommended protection? And if resets are blocked, what's the best way to handle a later schema change?

I'd appreciate hearing from anyone who has dealt with these in production, or links to anything that covers them.

Thanks!

[ThomazNeto]

Hi Liresa,

I went through the current docs for these. Two of your three questions have gaps in the documentation, so I'll separate what's documented from what I'd verify.

1. Deletes after tombstone expiry. Not documented. The only guidance is the rule for sizing the threshold: "Set pipelines.cdc.tombstoneGCThresholdInSeconds to a value that exceeds the maximum expected delay between event arrival and pipeline execution. This ensures that delete tombstones are retained long enough to correctly handle late-arriving or out-of-order deletion events." Once the tombstone is gone, the key is unknown to the target. Reading that mechanically, a late DELETE for a missing key has nothing to close, and a late UPDATE with an older sequence than the delete could be treated as a new record. That second case is the one that hurts, and it's my inference from how the docs describe the mechanism, not something they state. For a backfill of old data, don't rely on tombstones at all: test it on a copy of the table first, and treat the backfill as a controlled reprocess rather than a late-arrival case. With 24h lateness, I'd set at least 3 days, so a weekend outage doesn't eat the margin.
https://docs.databricks.com/aws/en/ldp/developer/ldp-python-ref-apply-changes

2. Higher retention. No storage or MERGE guidance in the docs. Tombstones are rows kept in the underlying table and filtered from what readers see, so the extra cost is proportional to deletes per day times retention days. On an SCD2 table you're already keeping every version forever, so 7 days of deleted keys is small next to that. One detail: tombstoneGCFrequencyInSeconds no longer appears in the docs. Only the threshold does, set as a table property:

sql

CREATE OR REFRESH STREAMING TABLE silver_customers
TBLPROPERTIES (
  'pipelines.cdc.tombstoneGCThresholdInSeconds' = '604800',
  'pipelines.reset.allowed' = 'false'
);

3. Full refresh safety. Yes, pipelines.reset.allowed = false is the documented lever: "To prevent full refreshes from being run on a table or view, set the table property pipelines.reset.allowed to false." The docs are explicit about the risk you describe: a full refresh "can result in dropped records if input data is no longer available", and "a full refresh loses data not still in the source." What they don't say is whether a blocked table fails the pipeline-level full refresh or is just skipped, so test that once before you depend on it.

For schema changes with resets blocked: adding columns is "generally safe" without a refresh. Renames, type changes and column drops require a full refresh, and the recommended workaround is "creating a new column with the desired schema or name, then using a view on top of the streaming table to union the old and new values." Given your 30-day Bronze retention, I'd also keep a cheap archive of the Bronze files (or a Bronze streaming table with no retention limit) so that a full refresh stays possible when you eventually need one.
https://docs.databricks.com/aws/en/ldp/updates
https://docs.databricks.com/aws/en/ldp/full-refresh-st
https://docs.databricks.com/aws/en/ldp/properties

If you run the post-expiry delete test, please post what you see. It would fill a real hole in the docs.

[niteshm]

Hi @LiresaFerizaj , these are real-world issues, and I bet every Sr DSA has taken this with a pinch of salt at some point. This is a pattern implemented across most enterprises I have worked with and very common. Here is the short version with what to actually do...

1. Delete arriving after tombstone expiry
   1. What happens: The consistent behavior is a silent no-op. A delete matching no live “ROW” and no tombstone has nothing to act on, and there is no error path for unmatched deletes.
   2. Solution: before backfilling old deletes, raise pipelines.cdc.tombstoneGCThresholdInSeconds so the new tombstones outlive the backfill window. Size it from measured max arrival lag.
   3. But raising the threshold only gives headroom, it does not fix the cause. If you hit the same issue at 7 days or '604800' seconds, talk to the upstream team. Something in the source or the ingestion path is causing the delay, and that is where the real fix lives.
   4. 
2. Cost of raising retention to 7+ days
   1. Storage: tombstones are physical rows in the Delta table, so cost scales as delete rate X retention, on top of normal SCD2 FULL history.
   2. Your SEQUENCE BY STRUCT(event_ts, source_lsn) is the expensive case for GC: a scalar sequence column lets the engine skip files via min/max stats, a STRUCT forces broad scans and rewrites.
   3. Solution: keep retention only as long as measured lag demands, monitor GC duration in the pipeline event log, and if a single scalar preserves your true source order, flatten the STRUCT.
3. Full refresh with 30-day Bronze
   1. YES, the history loss is real and documented: a full refresh clears the table and its checkpoints, then reprocesses only what is still in the source. Anything older than 30 days is gone permanently.
   2. Solution: set pipelines.reset.allowed = false on the Silver table. That is the documented protection, and incremental writes keep flowing normally.

CREATE OR REFRESH STREAMING TABLE silver_customers
TBLPROPERTIES (
'pipelines.cdc.tombstoneGCThresholdInSeconds' = '604800', --(from Section: 1.3) 
'pipelines.reset.allowed' = 'false'
) ;

1. References
   1. https://docs.databricks.com/aws/en/ldp/developer/ldp-python-ref-apply-changes
   2. https://community.databricks.com/t5/data-engineering/auto-scd-api-tombstone-garbage-collection/m-p/1...
   3. https://community.databricks.com/t5/data-engineering/lakeflow-sdp-failed-with-delta-streaming-incomp...

[data_pulse]

@LiresaFerizaj 

The other replies already cover the retention/performance and full refresh questions well, I only did a quick validation around point 1 : late events after the tombstone retention window.

I used SCD2 AUTO CDC with: 

"pipelines.cdc.tombstoneGCThresholdInSeconds": "60"

and tested this sequence:

• seq=1 INSERT → create id=1 with value A
• seq=2 UPDATE → change id=1 from A to B
• seq=3 DELETE → delete id=1
• late seq=2 UPDATE → ignored
• waited beyond the 60-second retention and reran the pipeline
• another late seq=2 UPDATE → still ignored
• seq=4 UPDATE → accepted and created a new current SCD2 version
• another stale seq=2 UPDATE → still ignored

So in this validation, I could not reproduce a stale event resurrecting the row after the retention window. AUTO CDC continued to honour SEQUENCE BY, while a genuinely newer sequence value was accepted as expected.

Agree that the docs don’t clearly state when the tombstone is physically garbage collected after the retention threshold, so this validates the observed behaviour rather than the exact internal clean up timing.

[Islam_hoti]

Hi Liresa, here's how I'd approach each one.

1. Late delete after the tombstone expires

It doesn't cause an error, and the delete isn't lost. In SCD2 the history for the key stays in the table. The late delete gets placed on the timeline by SEQUENCE BY and closes the version that was active at that point (sets its __END_AT).

The real risk is the opposite case: an old insert or update arriving after the tombstone of a newer delete has been removed. Without the tombstone, nothing records that the key was deleted, so that old event can bring a deleted customer back as active. This exact edge case isn't described in the docs, so I'd test it with a small replay before running the backfill.

Rule of thumb: set pipelines.cdc.tombstoneGCThresholdInSeconds higher than your maximum lateness. The default of 2 days already covers your 24 hours. For a backfill of older data, raise it temporarily for that run.

2. Longer retention (7+ days)

The cost scales with how many deletes you have. Tombstones are extra rows in the table that a view filters out. A longer retention means more of these rows during MERGE and reads. In SCD2 this is usually small compared with the history rows the table already keeps forever. If you have a lot of deletes, liquid clustering on customer_id helps keep MERGE efficient. 7 days is reasonable. Check the table size and update duration in the event log after you change it.

3. Full refresh safety

Yes, pipelines.reset.allowed = false on silver_customers is the documented protection. It blocks full refresh, but incremental updates keep running.

sql
CREATE OR REFRESH STREAMING TABLE silver_customers
TBLPROPERTIES ('pipelines.reset.allowed' = 'false');

I'd also fix the root cause. Keep the raw CDC data long-term in a Bronze Delta streaming table (also with reset.allowed = false), not only in the 30-day files. Then the SCD2 history can always be rebuilt.

Schema changes when resets are blocked:

Adding columns: this works incrementally with COLUMNS * EXCEPT (...). No full refresh is needed.
Breaking changes (type change, key change): create silver_customers_v2 and backfill it from the long-term Bronze table. Or copy the existing history with DML, which Unity Catalog streaming tables allow as long as __START_AT/__END_AT stay valid. Then point the flow at v2 and switch consumers over with a view.

Docs:

AUTO CDC INTO (tombstones, SEQUENCE BY STRUCT)
Pipeline properties (pipelines.reset.allowed)
Advanced AUTO CDC (DML on targets)