Databricks Community

LiresaFerizaj · Saturday

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:

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?
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?
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!

Liresa Ferizaj

ThomazNeto · Sunday

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.

Thomaz A. Rossito Neto
Principal Data Architect & AI Strategy — CI&T
thomazn@ciandt.com
linkedin.com/in/thomaz-antonio-rossito-neto

niteshm · Sunday

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...

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.
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.
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'
) ;

References

data_pulse · Monday

@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 · Monday

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)

Louis_Frolio · an hour ago

Hello @LiresaFerizaj , I took a look at both internal and external documentation and here is what I found.

You've already had solid answers from Thomaz, Nitesh, data_pulse, and Islam, so I'll tie the thread together, separate what's documented from what we're inferring, and add a few things nobody has touched yet.

Late events and tombstone expiry

Two scenarios are getting mixed together, and Islam was right to split them.

A DELETE that arrives late doesn't need a tombstone in SCD Type 2. The key's history rows are still there, so SEQUENCE BY places the delete on the timeline and sets __END_AT on the version active at that point. If the key is already closed out, there's nothing to close and the likely result is Nitesh's silent no-op. No error path is documented.

The case that can hurt is an INSERT or UPDATE with a sequence older than a DELETE, arriving after that delete's tombstone has been garbage collected. The SCD Type 2 example on the AUTO CDC page shows a stale update slotting in as a closed version while the tombstone is present. What happens once it's gone isn't documented, so I wouldn't state either "always ignored" or "always resurrects" as fact. Thomaz called it a gap, and that's exactly what it is.

data_pulse, thanks for running a test. One wrinkle: the late seq=2 UPDATE had already been applied before the delete, so the engine ignores it as a duplicate with or without a tombstone. The test that settles this, on a disposable copy:

INSERT at seq 1, DELETE at seq 3, with a short pipelines.cdc.tombstoneGCThresholdInSeconds.
Wait well past the threshold and run the pipeline at least once more, since the GC pass runs on its own schedule and the threshold only marks a tombstone eligible.
Send an UPDATE at seq 2 the table has never seen, then a newer event at seq 4.
Check both the current rows and the full history.

Closed versions (1, 2) and (2, 3) mean the engine reconstructs the delete from history. An open row with __START_AT = 2 is the resurrection case. Please post what you see.

For backfills: raising the threshold before the run (Nitesh and Islam's advice) only protects deletes that happen after you raise it. Collected tombstones don't come back, and retention is no substitute for keeping the source data. Run the backfill as a ONCE flow into the same target so it uses the same sequencing. If your test shows resurrection, pre-filter the backfill from Silver's own history: a key with no row where __END_AT IS NULL was deleted at its max(__END_AT), so drop backfill events for that key with a lower sequence. You lose a little history on those keys and keep deleted customers deleted.

Longer tombstone retention

Storage is a rounding error. Hidden rows scale with deletes per day times retention days, and an SCD Type 2 table already keeps every version forever. The docs don't quantify it.

The cost that shows up in practice is the GC pass, which is a DELETE against the Delta table. Nitesh raised it here, and in an earlier thread de01 and saisaranv ran into it on a 10B-row table: with a STRUCT in SEQUENCE BY, file-level min/max stats don't prune well and each GC pass becomes a wide rewrite. Field experience, not documentation, but consistent.

So keep your ordering and reconsider its representation. STRUCT(event_ts, source_lsn) is lexicographic: event_ts first, source_lsn only on ties. If source_lsn is a true total order across the feed (LSNs usually are within one source database), sequencing by it alone is simpler and cheaper, with event_ts kept as a normal column. If you need both, pack them into one sortable BIGINT that preserves the same order. Don't drop the tie-breaker for convenience, and decide before go-live: __START_AT and __END_AT take the type of the sequence expression, so changing it later means the full refresh question 3 is trying to avoid.

The GC frequency knob was never documented. de01 found pipelines.applyChanges.tombstoneGCFrequencyInSeconds works when set as "86400 seconds" (unit spelled out), but it's internal and can change. Islam's liquid clustering on customer_id is the supported way to keep MERGE cheap, and CLUSTER BY AUTO is the current recommendation. Watch num_upserted_rows, num_deleted_rows, table size, and update duration in the event log after any change.

Full refresh and the 30-day Bronze window

pipelines.reset.allowed = false is the documented guard and everyone's SQL is right. Treat it as a seat belt against an accidental "Full refresh all," not a backup. When you genuinely need to rebuild Silver, flip it to true, refresh deliberately, flip it back. The docs don't say whether a blocked table is skipped or fails during a pipeline-wide full refresh. My understanding is it just gets a normal incremental update, but prove that once in a non-production workspace, as Thomaz suggested.

The real fix is upstream, and it's the documented best practice: land raw CDC in a Bronze streaming table with forgiving types (string or variant) and put reset.allowed = false on it too. Your flow already reads STREAM(bronze_customer_changes), so check what the 30 days applies to. If it's the raw files and Bronze is a Delta streaming table that nobody full refreshes, Bronze already holds everything and Silver can always be rebuilt. If Bronze itself is trimmed, that's the thing to fix.

Schema changes with resets blocked: adding columns is generally safe. For renames, type changes, or drops, the docs recommend a new column plus a view that unions old and new. Islam's silver_customers_v2 pattern (build from Bronze, validate, cut consumers over through a view) is cleaner when you have full Bronze history.

Two more tools, both Beta on the Preview channel. Pipeline rewind (pipelines.rewind.betaEnabled = true) rolls table versions, offsets, and checkpoints back to a point in time and replays only the affected data, and it supports AUTO CDC SCD Type 1 and 2 targets. Seven-day window, can't cross a full refresh, so it's for "last week's load was wrong," not a Bronze replacement. Pipeline unit testing (DBR 18.1+) can mock Auto CDC inputs for sequencing checks, though the time-based GC test above is easier on a real disposable pipeline.

Takeaway: SCD Type 2 is forgiving of late deletes because the history is the record. The one undocumented case is a stale insert or update after tombstone GC, and that's a short test before you depend on it. Fix retention at Bronze, treat reset.allowed as a guard rail, and settle your SEQUENCE BY type now.

References: