Merging 2 versions of an SCD table created from a managed ingestion pipeline

swang-pg — Thu, 30 Jul 2026 01:57:37 GMT

We've been using the Salesforce Databricks connector to ingest data into Databricks with history tracking (SCD2) turned on. The licence to one of the add-on modules was accidentally revoked for our ingestion user and subsequently reinstated. This led to some columns generating the error CANNOT_WRITE_TO_INACTIVE_COLUMNS. I backed up the affected tables before doing a full refresh on the affected tables. What's the best way to merge the back up table and ingestion table?

Re: Merging 2 versions of an SCD table created from a managed ingestion pipeline

binlogreader — Thu, 30 Jul 2026 03:16:27 GMT

@swang-pg We did not run into the error but had a similar situation of stitching tables while using Salesforce and Mysql connectors so I can share what we did. Two things about the situation shape what the merge can look like.

The first is that the merge cannot go into the ingestion table. The destination of a managed ingestion pipeline is a streaming table that only its own pipeline is allowed to write; a MERGE INTO is rejected, and you cannot attach your own flow to the connector's pipeline. So the combined history has to live in an object you own, fed by the connector's table.

The second is that everything in the backup is finished history; those closed SCD2 versions will not change again. So this is a one-time stitch plus the live feed you already have, not an ongoing two-way merge.

So this gives us couple of options:

(1) If your consumers can query a different name, a view is the simplest/low lift answer. Close the backup's open versions at the point where the refreshed table takes over, then union the two:

```sql
CREATE OR REPLACE VIEW account_history_full AS
WITH cutover AS (
SELECT Id, MIN(__START_AT) AS new_start
FROM live_catalog.sfdc.account
GROUP BY Id
)
SELECT b.* EXCEPT (__END_AT),
COALESCE(b.__END_AT, c.new_start) AS __END_AT
FROM backup_catalog.sfdc.account_backup b
LEFT JOIN cutover c USING (Id)
UNION ALL
SELECT a.* EXCEPT (__END_AT), a.__END_AT
FROM live_catalog.sfdc.account a;
```

There is no compute to schedule and nothing to keep in sync, and the connector table stays the single live object. If the refresh added or reactivated columns the backup lacks, write the column lists out explicitly.

(2) If you do need one physical table that stays current, I found a good article that I implemented eventually. The idea is to create a small declarative pipeline of your own with two AUTO CDC flows writing the same SCD2 target. One is a one-time backfill flow (`create_auto_cdc_flow` with `once=True`) reading the backup as a batch, sequenced by the old `__START_AT`, which rebuilds the pre-incident version chain. The other is a continuous flow reading the connector table's change feed from the refresh onward. Both are AUTO CDC, which is what lets them coexist on one target. We run this shape in production, and both flows register in a single deploy with no handoff. The cost is that you own delete handling, schema drift, and full refresh behavior on the copy from then on, so I would only take this path if the view cannot serve your consumers.

topic Re: Merging 2 versions of an SCD table created from a managed ingestion pipeline in Data Engineering

Merging 2 versions of an SCD table created from a managed ingestion pipeline

Re: Merging 2 versions of an SCD table created from a managed ingestion pipeline