@bricks_2026 ,

Lakeflow Spark Declarative Pipelines AUTO CDC runs in exactly the same way as a classic SQL/Python pipeline, just with a different runtime version. It can even run on a non-serverless compute if necessary. You can just go ahead and create a Python notebook with the AUTO CDC flow and at least try it out.  

What are the reasons you cannot use it yet? It would be just so much easier to accomplish your task with AUTO CDC.

Otherwise, with the manual approach, you would need to compare not only the key, but every column in the MERGE statement to update with the new values from an insert operation, something like this:

 MERGE INTO cdc_data_raw t
   USING updates s
     ON s.key = t.key
   -- ... delete merge logic ...
   WHEN MATCHED THEN UPDATE SET 
     A = CASE WHEN s.ts > t.ts AND s.a != t.a THEN s.a ELSE t.a END,
     B = CASE WHEN s.ts > t.ts AND s.b != t.b THEN s.b ELSE t.b END,
            -- ... for every column ...
     ts = CASE WHEN s.ts > t.ts THEN s.ts ELSE t.ts END
 WHEN NOT MATCHED THEN INSERT *

Hope it helps.