Why there is no upsert key for cosmos Db container

Question

Hello,

Using the copy activity- Azure SQL to cosmos DB there is no upsert key. Is this expected behavior or this is a product limitation?

Appriciate your help!

Answer 1

Hello Michael M,

Welcome to the Microsoft Q&A forum.

Azure Synapse's Copy Data activity does not provide an option to specify the unique key column for upsert operations into Azure Cosmos DB.

The reason behind this is:

The id field in Cosmos DB item acts as the key column to perform update or delete or upsert operations. When performing update or delete actions, specifying the partition key is mandatory. This is because the service uses the partition key to search for the id field value and perform the update or delete operation accordingly.

The unique key violation error you're encountering suggests that the data being copied contains duplicate values for the id field defined in your Cosmos DB container.

Please ensure that the data in Azure SQL does not contain duplicates for the fields that form the unique key in Cosmos DB.

I hope this answers your question.

Please let me know if you have any further questions.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.