topic What happens to with SCD2 tables if the source pipeline performs a Full Refresh? in Data Engineering

What happens to with SCD2 tables if the source pipeline performs a Full Refresh?

lausikh — Fri, 24 Jul 2026 18:42:43 GMT

Hi everyone,

I'm looking for clarification on how Lakeflow/DLT behaves in the following scenario.

We have two independent pipelines:

Pipeline 1 (Ingestion)

Ingests Salesforce objects into Delta tables.
These tables represent the current snapshot of Salesforce.
Once a week, we plan to run this pipeline with Full Refresh (full_refresh: true) because records and columns are sometimes deleted in Salesforce, and incremental ingestion doesn't always reflect those deletions.

Pipeline 2 (SCD2)

For each ingested table, we have a separate pipeline that creates an SCD2 table using dlt.apply_changes_from_snapshot().

Conceptually it looks like this:

@Dlt.table def contact():
return spark.readStream.table("raw.contact")

dlt.apply_changes_from_snapshot( target="contact_scd2", source="contact", keys=["Id"], stored_as_scd_type=2 )

Important: We are NOT performing a Full Refresh on the SCD2 pipeline.

My questions

Is this architecture officially supported? Will apply_changes_from_snapshot() correctly detect deleted records and close the corresponding SCD2 records after the full refresh?
Is there any risk of checkpoint corruption or inconsistent state in the SCD2 pipeline because the upstream pipeline was fully refreshed?
How are schema changes handled (especially when a column is removed from the source)?

I've read the documentation for Full Refresh and apply_changes_from_snapshot(), but I couldn't find guidance on this specific scenario where an upstream snapshot pipeline is fully refreshed while the downstream SCD2 pipeline continues incrementally.

Any insight from the Databricks team or anyone with production experience would be greatly appreciated.

Re: What happens to with SCD2 tables if the source pipeline performs a Full Refresh?

ThomazNeto — Fri, 31 Jul 2026 10:56:28 GMT

The concept is supported — but there's one detail in your code that's likely the real risk, and it's not the full refresh itself.

1. Deletion detection: yes, that's literally what the API is designed for. AUTO CDC FROM SNAPSHOT (the new name for apply_changes_from_snapshot — same signature, Databricks recommends renaming) compares consecutive snapshots and infers inserts, updates and deletes; keys present in the target but gone from the source get deleted — which for SCD2 means the record is closed with an end date. So your weekly full refresh surfacing Salesforce deletions is exactly the use case.

2. Here's my concern: your code wraps the source in spark.readStream.table("raw.contact") and feeds THAT to the snapshot API. A streaming read on a table that gets fully rewritten once a week is where checkpoint pain lives — streaming reads error out when existing records change or disappear underneath them. The snapshot API doesn't want a stream at all: in periodic snapshot mode you pass the source as a plain table name (source="raw.contact"), and each pipeline update it reads the current state of that table and diffs it against the target. No streaming checkpoint on the source, so an upstream full refresh is just "the next snapshot" — which is the whole point. I'd drop the intermediate streaming table and pass the name directly. And you're right NOT to full-refresh the SCD2 pipeline — that would rebuild it from the current snapshot only and wipe your history.

3. Schema changes: this is the honest gap. New columns generally flow through, but I couldn't find documentation describing what happens when a column is REMOVED from the source for this specific API — so I won't guess. I'd test column removal in a dev pipeline before trusting it, or protect yourself with an explicit select of the columns you track so source drift doesn't surprise the target.

One small design note: since deletions only become visible at the weekly full refresh, your SCD2 end dates for deleted records will be accurate to the week, not the day. If that's fine for your consumers, great — just worth stating explicitly.

Re: What happens to with SCD2 tables if the source pipeline performs a Full Refresh?

AbhilashNagilla — Fri, 31 Jul 2026 14:32:07 GMT

The pattern is documented and you are right not to full refresh pipeline 2, but as written the deletion detection can never fire, and the full refresh is not why.

spark.readStream.table("raw.contact") makes contact append-only, so no key ever leaves it, and the API closes a record only when a key present in the target is "no longer present in the source." That read also fails outright on any update or delete to the source, which an ordinary sync produces, so skipChangeCommits would only hide the failure and the deletes with it.

Give the API a batch snapshot instead of a stream. The documented pattern is a @DP.view returning spark.read.table("raw.contact"), then dp.create_streaming_table("contact_scd2") and dp.create_auto_cdc_from_snapshot_flow(target="contact_scd2", source="<that view>", keys=["Id"], stored_as_scd_type=2). (apply_changes_from_snapshot is the old name for the same function.) With no checkpoint on the source, the weekly refresh is just the next snapshot and deletes close the SCD2 rows. Try it in a dev pipeline first, since changing the source a flow reads can require a full refresh.
Run pipeline 2 as a task after pipeline 1 in one job. A refresh that fails partway is documented to leave target tables "in an inconsistent state with partial data," and a snapshot read against a half-loaded table end-dates every key it cannot see. Nothing flags that for you either, since the snapshot API does not emit num_deleted_rows. A row-count expectation on the view is cheap insurance, and pipelines.reset.allowed = false on the target stops anyone refreshing the history away.
A removed column never reaches your SCD2 table. The connector marks it inactive rather than dropping it, so raw.contact keeps its schema. Type changes are the ones it cannot handle.

Worth questioning the weekly refresh itself, though: soft deletes land on the next sync and deleted columns are handled automatically, so the only cases documented as needing a full refresh are true hard deletes and records purged from the recycle bin before the next sync.

Re: What happens to with SCD2 tables if the source pipeline performs a Full Refresh?

ThomazNeto — Sun, 02 Aug 2026 10:29:45 GMT

This is the definitive answer for this thread — and a sharper diagnosis than mine. I'd flagged the readStream intermediate as where the checkpoint risk lives, but your point is stronger: it doesn't just risk breaking, it makes deletion detection structurally impossible, since an append-only intermediate never loses a key. That's a subtle failure mode because everything *looks* healthy while silently never closing a record.

The partial-load scenario in your point 2 is the one I'd underline for anyone finding this later: a snapshot read against a half-loaded source end-dates every key it can't see, with no signal. Orchestrating both as tasks in one job + a row-count expectation on the view is cheap insurance against an expensive, quiet data corruption.

One small clarification if you have a moment: is passing the source table name directly as a string to create_auto_cdc_from_snapshot_flow also supported, or is the @DP.view wrapper the required/recommended shape? Asking because the string form shows up in older examples and it'd be good to know if it behaves identically.