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]

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?
If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[spaceman127]

@afernand0 ,

The problems should have been fixed by now. I just checked again quickly, and it works.
Please try again.

@tayloramy,

Merge is now supported for Fabric Data Warehouse, in case you didn't know. However, it is still in preview mode, but this has only been the case for a short time.

Best regards 

[tayloramy]

Thanks @spaceman127, I saw that on the september fabric update. I am quite excited at the new features that are coming out.

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.

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

[afernand0]

Thanks for the reply. The query above returns the following. I have opened a support ticket as you suggested.

pk_name	pk_column
PK_Customers	customer_id

[v-prasare]

Thanks for your response. Please do keep posted updates here, this will useful for community members with similar issues.