CosmosDB Analytical store delete changes in sql sink

Kuldeep Bhati 0

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.

Harishga 3,930 Reputation points Microsoft Vendor

2024-05-01T05:44:25.51+00:00
Hi Kuldeep Bhati
Welcome to Microsoft Q&A platform and thanks for posting your question here.

You are correct that the Transactional and Analytical Cosmos DB stores are in sync, and changes should be reflected immediately. There may be a delay between when a document is deleted in the Transactional Store and when it is available in the Analytical Store. This delay is due to the way the Analytical Store is designed to optimize query performance.

If you are not seeing the deleted rows in the Azure SQL Database, it could be due to a few reasons:

The "Write behavior" setting in the Sink transformation is not set to "Delete". Make sure that you have selected the "Delete" option for the "Write behavior" setting in the Sink transformation.

The deleted rows are not being captured by the Change Data Capture (CDC) feature in Azure Data Factory. CDC only captures changes that are made to the data after CDC is enabled. If you deleted the rows before enabling CDC, they will not be captured.

There may be an issue with the pipeline configuration. Double-check that the pipeline is configured correctly and that all the settings are correct.

To troubleshoot the issue, you can check the following:

Check the logs in Azure Data Factory to see if there are any errors or warnings related to the pipeline.

Check the Azure SQL Database to see if the deleted rows are present.

Check the Analytical Store in Azure Cosmos DB to see if the deleted rows are present.

Check the configuration of the pipeline to make sure that all the settings are correct.

Regarding your question about using lookups for delete operations in the pipeline, you can use the Lookup activity in Azure Data Factory to retrieve data from your SQL database and use it to perform a delete operation on the same database. You can configure the Lookup activity to retrieve the data based on a specific condition, such as a primary key or a unique identifier. Once you have retrieved the data, you can use the Delete activity to delete the data from the database.

Reference
https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-change-data-capture
https://devblogs.microsoft.com/cosmosdb/now-in-preview-change-data-capture-cdc-with-azure-cosmos-db-analytical-store/

I hope this information helps you. Let me know if you have any further questions or concerns.

4 answers

Kuldeep Bhati 0 Reputation points

2024-05-01T14:04:29.26+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Kuldeep Bhati 0 Reputation points

2024-05-02T05:29:39.0733333+00:00

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
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Harishga 3,930 Reputation points Microsoft Vendor

2024-05-02T07:24:09.3266667+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Kuldeep Bhati 0 Reputation points

2024-05-09T14:12:26.07+00:00

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
Please sign in to rate this answer.
Harishga 3,930 Reputation points Microsoft Vendor

2024-05-10T08:00:08.31+00:00

Hi@Kuldeep Bhati
To address the first issue, you want to check if you have correctly configured the change data capture (CDC) feature in your Cosmos DB account. CDC is required to capture the changes made in the transactional store and replicate them to the analytical store.

Here are a few things you can check:

Make sure that you have enabled CDC on your Cosmos DB account. You can do this by going to the Settings tab of your Cosmos DB account and selecting the Change feed option.

Make sure that you have correctly configured the CDC feature in your ADF pipeline. You can use the Cosmos DB Change Feed source in your pipeline to read the changes made in the transactional store and replicate them to the analytical store.

Check if there is a delay in synchronization between the transactional and analytical stores. According to the Azure documentation, there can be a delay of up to two to three minutes before the changes made in the transactional store are reflected in the analytical store.

Regarding the second issue, you want to review your data flow configuration in Azure Data Factory to ensure that it is set up to handle delete operations.

Here are a few things you can check:

Use an Alter Row transformation with a Delete If condition to specify which rows should be deleted.

Consider using a pre-copy script in the SQL sink to execute SQL commands that will handle deletions appropriately. For example, you could use a script to truncate the table or delete specific rows based on a condition before the data is copied.

Use a stored procedure to manage deletions in your Azure SQL Database. This procedure can be executed before the data is loaded into the database to ensure that deletions are reflected.

Reference:
https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-change-data-capture

Hope this helps. Do let us know if you any further queries.
Sign in to comment

Share via

CosmosDB Analytical store delete changes in sql sink

4 answers