Cannot merge data from a CDC enabled table

[afernand0]

Hi,

I'm trying to upsert data from a CDC enabled table in an Azure SQL database table to a Fabric database using a copy job. As you can see in the screenshot below, I have selected Merge as the update method. However, the column mapping is disabled when CDC is enabled - which is expected. 

There's a primary key in the dbo.customer table in the source. However, copy job isn't recognizing it nor allowiing me to specify it. Is this a known limitation or a bug?

[v-prasare]

Hi @afernand0,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@tayloramy,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[tayloramy]

Hi @afernand0, 

It’s expected behavior in CDC Copy Job that the column mapping UI is disabled. CDC auto-derives the schema and keys. Per the docs: when CDC tables are selected, "column mapping can’t be configured", and the Merge update method "matches the required key columns to the primary key defined in the source by default". Also note a key limitation: composite primary keys aren’t supported for CDC merge yet (Microsoft Learn: CDC in Copy job (Preview)).

So if your dbo.customer has:

• a single-column primary key: CDC should auto-recognize it.
• a composite PK (multi-column): CDC Merge won’t work today (limitation).
• only a unique index (no actual PRIMARY KEY constraint): CDC won’t detect it as the key.

Quick check on the source table:

SELECT k.name AS pk_name, c.name AS pk_column FROM sys.key_constraints k JOIN sys.index_columns ic ON ic.object_id = k.parent_object_id AND ic.index_id = k.unique_index_id JOIN sys.columns c ON c.object_id = k.parent_object_id AND c.column_id = ic.column_id WHERE k.parent_object_id = OBJECT_ID('dbo.customer') AND k.type = 'PK'; 

If you do have a single-column PK and it still isn’t detected, that’s likely a product issue-capture the Job/Run IDs and open a ticket via the Fabric Help pane (Create a support ticket).

Workarounds today:

• Switch this table to watermark-based incremental (not CDC). You'll be able to pick the incremental column and configure mapping/keys (Copy job overview).
• Or stage to a destination and run your own upsert logic (e.g., stored proc or PySpark). Be aware that T-SQL MERGE isn’t supported in Fabric Warehouse yet (T-SQL surface area limitations).

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this post as the solution.