CosmosDB Analytical store delete changes in sql sink

Question

Hi I am trying to capture delete changes from Azure cosmosdb analytical store using change data capture in Azure datafactory, Source is Cosmosdb, sink is Azure sql database. In between I am flattening my file using dataflow.

My insert, update is working fine, but delete is not working in sql database, so row is not deleting, if I delete a row in transactional cosmosdb.

If I understand it correctly Transaction and Analytical cosmosdb store is in sync, so change should be reflected immediately.

Is there any method or query I can use for delete operation in pipeline, so I can use any lookups.

Answer 1

Hi Thanks for your reply, I have setup delete option in sink for dataflow, and I have also established cdc first and then I am doing delete.

In sink I have enabled delete, update and upsert for dataflow. I am using {_rid} as key column on the sink side.

One thing I have noticed if I delete 1 record in transactional store and run the pipeline, my pipeline reads 1 change in record and in second step of flatten file, but in sink it is not writing anything (deleting that record in azure sql database). My pipeline runs successfully.

If I create a new pipeline then I still see that deleted record, if I load that data again from analytical container in cosmosdb (from beginning of time). That data does not exist in transaction container of cosmosdb.

Answer 2

Hi Do you have any solution for it, as my setup is correct, but my records are not deleting after I delete records in transaction database

Answer 3

Hi @Kuldeep Bhati
As per the documentation, currently, there is no way to delete or truncate a Cosmos container. Typically, the way most people do this today is to delete and then recreate the container. That avoids the RU/s cost of deleting every item. So, if you want to delete a row from the transactional Cosmos DB, you can delete the entire container and recreate it with the remaining rows.

You can use the REST endpoint in Azure Data Factory to call Azure REST API for Delete Document in Cosmos DB (SQL).

The Delete Document operation deletes an existing document in a collection.

Reference:
Copy and transform data from and to a REST endpoint - Azure Data Factory & Azure Synapse | Microsoft Learn
https://learn.microsoft.com/en-us/answers/questions/820865/using-azure-data-factory-to-call-azure-rest-api-of

Hope this helps. Do let us know if you any further queries.

Answer 4

Hi Harshiga,

Thanks for your reply.

My records are deleting correctly in cosmosdb transaction store, but same changes I do not see in analytical store. The reason why I am saying this because if I create a new pipeline and rerun the datafactory, I can see deleted records in sql database sink.

Second

My records are also not deleting in azure sql database which I am using as sink in ADF pipeline. I use cosmosdb analytical container as source for adf. I have enabled insert,upsert and delete at sink.Regards

Kuldeep Bhati

Answer 5

Hi Harshiga, thanks for your reply. I am still not get any deletes in my sink (SQL), I have selected the below options in change data capture in ADF.

Capture intermediate updates	Enable this option if you would like to capture the history of changes to items including the intermediate changes between change data capture reads.
Capture intermediate updates	Enable this option if you would like to capture the history of changes to items including the intermediate changes between change data capture reads.
Capture Deletes	Enable this option to capture user-deleted records and apply them on the Sink. Deletes can't be applied on Azure Data Explorer and Azure Cosmos DB Sinks.

Eevery time I add or update a record, I can see new row added in sql sink with incremental _ts. When I delete a record in cosmosdb, I do not dee that updated in sql sink

I looked at the below article shared by you and it mention ___usr_opType column in sink, how I can get that column in my sql or blob sink. I also added system generated column option in adf pipeline.

https://learn.microsoft.com/en-us/azure/cosmos-db/get-started-change-data-capture

Value	Description	Option
1	UPDATE	Capture Intermediate updates
1	UPDATE	Capture Intermediate updates
2	INSERT	There isn't an option for inserts, it's on by default
3	USER_DELETE	Capture Deletes
4	TTL_DELETE	Capture Transactional store TTLs

If I can get this column, I can identify that record was inserted, deleted or updated. Can you please help us on this how to get this column in ADF pipeline. I tried but I am not getting it. I also tried the pipeline mentioned in the article, but couldn't see usrtype data in delta table.

Regards

Kuldeep Bhati