Using ADF is it possible to update id column in cosmos using upsert in sink. It is creating new document instead of updating the old document.

Question

I have already inserted millions of records using adf copy step into cosmos from azure sql.

Source data
pkey = 123
id = 27

In cosmos the same document is created
pkey: 123
id :27

Now i want to update the id to a different value based on pkey.
pkey: 123
id: guid()

Since cosmos doesn't provide the key column in the sink it is creating a new record instead of update the old. Any solutions.

Answer 1

Hi @Anonymous ,

I had discussion with PG team also reproduced my end. Below are the complete details.

id field in cosmos db item will going to act as Key column to perform update or delete or upsert. Hence You cannot update id field of document in cosmos db from dataflow.

Please note, while you want to perform update or delete actions then mentioning partition Key is mandatory. Because based on that partition key service to go and search of id field value and if matching found then service will perform update or delete accordingly.

In below screenshots example, my source data id will get mapped with Sink data id (cosmos Db document id) and then if matching found then update will happen if not insert will happen. Please note, category is partition key of my collection in cosmos DB.

Hope this helps. Please let us know if any further queries.

-----------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

I tried the same thing but it is not updating can u pls suggest how to update view count column under the array in cosmos db container