Support for append mode in CDC in copy job

[kjetil_hamre]

Hi im trying out CDC from a source SQL database. Really, what I need to have a stream of all the inserts, updates and deletes in the source database. With such a stream I can create SCD-2 records in a medallion dataplatform. It seems CDC with an "append" destination should solve this. I have tried to set it up with copy-job into an Azure SQL managed instance. However - the wizard stops with this error: 

CDC is only supported for destinations where the update method is merge. For all other update methods, please use watermark-based incremental copy, which requires specifying an incremental column to identify changes from the source.

I thought that was a very weird error message. I mean it should work? watermark-based incremental copy is not an option as the source does not have a "last updated" column - AND it would not capture deletes.

Really I wanted to do it right into a lakehouse - but that fails with:
CDC is not supported for Microsoft Fabric Lakehouse Table as destinations. Please use watermark-based incremental copy, which requires specifying an incremental column to identify changes from the source.

To do it into a fabric sql server fails with:
CDC is not supported for SQL Database as destinations. Please use watermark-based incremental copy, which requires specifying an incremental column to identify changes from the source.

So, I wonder is this functionality that is not yet supported and is coming very soon, or am I just looking in the wrong place? Any help apprechiated.

[Ugk161610]

Hi @kjetil_hamre ,

You’re not doing anything wrong this is actually a current platform limitation in Microsoft Fabric.

Right now, Change Data Capture (CDC) in Fabric only supports destinations that use the “Merge” update method.
CDC with Append destinations, Lakehouse, or Fabric SQL Database is not yet supported. That’s why you’re getting those errors when you try to set up CDC → Append or CDC → Lakehouse.

If your goal is to build SCD Type 2 history (to keep inserts, updates, and deletes), here’s the recommended workaround:

1. Use CDC → Merge to land changes into a staging table (for example, in Azure SQL or Fabric Warehouse).

2. Then use a Fabric notebook or Dataflow to read from that staging table and apply your SCD Type 2 logic —

   • Insert new records.

   • Update existing rows by setting an end_date or is_current flag.

   • Handle deletes accordingly.

3. Finally, write the SCD-2 history data into your Lakehouse (append mode) as a separate layer.

This is the approach most teams are following until Fabric adds direct CDC support for Lakehouse and Append destinations, which is already on Microsoft’s roadmap.

So in short — it’s not supported yet, and your setup is correct. Use CDC → Merge + Notebook for SCD-2 logic as a reliable solution for now.

Hope this helps!

Gopi Krishna

[kjetil_hamre]

someone pointed me to this! All CDC Changes to be captured in Copy Job's Incre... - Microsoft Fabric Community 

I do not understand - documentation seems to say this is not just an idea? And the idea has not got any votes apart from mine? I am the only person living in this universe?

[v-aatheeque]

Hi @kjetil_hamre 

I completely understand your concern, and you’re not the only one encountering this scenario. Currently, CDC in Copy Jobs is supported only for destinations where the update method is Merge. As mentioned in the documentation’s Known limitations section, CDC isn’t yet available for destinations such as Lakehouse or SQL Database, which is why you’re seeing the warnings.

Submitting this as an Idea was the right approach  the Product Group team reviews these requests regularly, and prioritization is largely influenced by customer votes and usage impact. I’d recommend adding more context about your use case in this thread, as it helps the team better assess the feature’s importance.

[kjetil_hamre]

It does not really help, but I gave you Kudos anyway 🙂 Maybe someone else have more insight?

[nielsvdc]

Hi @kjetil_hamre, when testing your scenarios I get the same warnings on all destinations. So I think you are looking in the right place, but maybe it's still too early. Have you check the Know limitations section on the CDC page? Change data capture (CDC) in Copy Job - Known limitations

Hope this helps. If so, please give a Kudos 👍 and mark as Accepted Solution ✔️.