Incremental Load Issue with SDP

[Sandeep11]

We have identified a critical issue in our pipeline and wanted to share it here to see if others have faced the same and how they approached it.

Pipeline architecture: Bronze (Lakeflow Connect) → Silver (SCD2) → Gold

The Problem: Every pipeline run triggers a full data reprocessing cycle across all layers(full refresh) instead of processing only new or changed records incrementally(incremental load).

Root Cause (as we understand it): Lakeflow Connect is used for data ingestion from some source and it operates as SCD1 and produces mutable tables at the Bronze layer with no append-only option. Because the Bronze source is mutable, Silver cannot stream incrementally from it and is forced into a full refresh on every run. This cascades upward — Silver rebuilds its entire SCD2 history on each run, and Gold recomputes on top of that — resulting in a full end-to-end reprocessing cycle every execution.

Impact: Significant cost and performance implications across all layers, which will become increasingly unsustainable as data volumes grow.

Questions:

1. Has anyone encountered this pattern with Lakeflow Connect and mutable tables?
2. Is there a recommended approach to enable incremental processing from a mutable Bronze source?
3. Are there any workarounds or alternative architectural patterns that others have successfully implemented in this scenario?

Any guidance or experience sharing would be greatly appreciated. Thanks!

[srini_ve]

@Sandeep11 

I think a practical way to handle this is to keep the Lakeflow Connect Bronze table as SCD1/current-state data, but introduce a change-detection step before Silver.

The architecture could be:

Source → Lakeflow Connect (Bronze/SCD1) → Hash-based Change Detection → Silver (SCD2) → Gold

Rather than rebuilding the complete Silver table on every run, we can use the business key + record hash to identify what has actually changed.

For example, in Bronze, keep the business key (e.g. customer_id) and generate a hash from the relevant non-key attributes:

customer_id + hash(customer_name, address, status, ...)

Then compare the current Bronze hash with the previously processed hash/state:

New business key → new record → insert into Silver
Same business key + same hash → no change → skip
Same business key + different hash → record changed → expire the existing SCD2 version and create a new version
Business key no longer present → handle as a delete, if deletes are part of the source/business requirement

The important part is that we persist the previous hash/state, so on the next run we only need to identify the affected business keys, rather than rebuilding the entire SCD2 history.

This allows Bronze to remain mutable/SCD1 while Silver still works incrementally as SCD2.

For Gold, I would follow the same principle and process only the impacted records/keys coming from Silver rather than recalculating the complete Gold layer.

So, rather than trying to force streaming directly from the mutable Bronze table, I would introduce hash-based change detection between Bronze and Silver. This gives us a relatively simple and scalable way to detect inserts and updates and avoid the full end-to-end refresh on every run.

One thing to keep in mind is that the hash should be generated from the attributes that should trigger an SCD2 change, not from the business key itself. The business key identifies the record; the hash tells us whether its attributes have changed.

This approach should significantly reduce the amount of data processed in Silver and Gold as the data volume grows, while still allowing Lakeflow Connect to maintain the Bronze layer as a mutable current-state table.

[Islam_hoti]

Hi,

Yes, this is a known pattern, and the root cause you describe is correct: Lakeflow Connect ingestion tables are AUTO CDC (SCD Type 1) targets, so they are mutable streaming tables rather than append-only. A normal streaming read against them either fails or forces you into full refresh.

The fix is to stop streaming the Bronze table itself and stream its change data feed instead.

Since DBR 15.2, you can read a change data feed from a streaming table that is the target of an AUTO CDC query, provided the table is published to Unity Catalog. That gives you an append-only stream of insert, update_preimage, update_postimage and delete records, which is exactly what SCD2 needs. In Silver you then feed that CDF stream into AUTO CDC INTO with STORED AS SCD TYPE 2, keyed on your business key and sequenced by a reliable ordering column. Roughly:

CREATE OR REFRESH STREAMING TABLE silver_customer;

CREATE FLOW silver_cdc AS AUTO CDC INTO silver_customer
FROM stream(bronze_customer) WITH (readChangeFeed = true)
KEYS (customer_id)
SEQUENCE BY _commit_version
STORED AS SCD TYPE 2;

One thing to check before you build this. If your workspace is on DBR 19 LTS or above and the Bronze table is a Unity Catalog managed table with row tracking enabled, automatic change data feed works with no table configuration at all. Otherwise you are on legacy CDF, which needs delta.enableChangeDataFeed set to true on the table, and you should confirm whether your ingestion pipeline lets you set that table property. Note that CDF is not a permanent history either way. Records are only retained for the table's retention window, so if a downstream stream sits idle too long you can lose the ability to resume.

Two other things worth verifying in your current setup, because they cause the same symptom independently.

First, make sure Silver is actually a streaming table with an AUTO CDC flow and not a materialized view. Materialized views recompute when their sources change, so if Silver is an MV you will get a full rebuild regardless of what Bronze does. Same question applies to Gold. Some MV query shapes refresh incrementally and some do not, and the pipeline event log tells you which happened on each run. That is the first place I would look to confirm where the full recompute is really originating.

Second, avoid reaching for skipChangeCommits here. It is the usual suggestion for streaming from a mutable source, but it silently drops updates and deletes, which would quietly corrupt an SCD2 history. It is the right tool only when you genuinely do not care about changes to existing rows.

Finally, depending on which connector you are using, it is worth checking whether the ingestion pipeline itself can write the destination as SCD Type 2. Support varies by connector, but if yours has it, Bronze keeps the history and the problem largely disappears at the source.

[Satyasai]

Yes, this is a known challenge when transitioning to Lakeflow Connect ingestion pipelines.

By default, Lakeflow Connect database ingestion uses Change Data Capture (CDC) to write to Streaming Tables in the Bronze layer, but depending on how the pipeline is configured or queried, it often defaults to updating the target table in place (SCD Type 1 behavior). Because Standard Spark Structured Streaming requires an append-only stream, attempting to stream from an updated/mutated table via readStream fails, forcing downstreams into a full-refresh pattern.
The industry-standard solutions and architectural workarounds for this pattern fall into three primary approaches:
Solution 1: Use table_changes() or Delta Change Data Feed (CDF)
Solution 2: Leverage APPLY CHANGES INTO (Lakeflow Auto-CDC)
Solution 3: Split into Raw-CDC Staging vs. Bronze (Append-Only Staging)

Documents Links for reference
https://docs.databricks.com/aws/en/ldp/concepts/#:~:text=Dataset%20type.%20How%20records%20are%20pro....

https://docs.databricks.com/aws/en/tables/features/change-data-feed?language=SQL