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