How can we synchronize data from Azure Cosmos DB to Snowflake using Azure Data Factory (ADF) with a Change Data Capture (CDC) mechanism

Question

We have data stored in Cosmos DB NoSQL and need to migrate it to Snowflake using Azure Data Factory (ADF) with a Change Data Capture (CDC) approach.

 

 Our objective is to perform CRUD operations based on CDC to handle all data changes.

 

This is our initial plan, but we are seeking advice on a more cost-effective and efficient solution. Please provide guidance on best practices for implementing CDC with ADF, or suggest alternative methods outside of ADF(within Azure services) if they might offer better cost management or efficiency for our ETL process.

Answer 1

Hi Sowndar Kumaresan,

Thanks for reaching out to Microsoft Q&A.

Batching Data Loads:

• Use batch windows to process the Change Feed data, instead of streaming it in real-time, to minimize the number of ADF activities and optimize cost.

Optimizing Transformation Logic:

• Avoid overusing complex transformations in ADF data flows, as these can increase cost significantly. For heavy transformations, consider offloading them to Azure Databricks or handling them inside Snowflake after the data load.

Monitoring and Autoscaling:

• Use ADF’s monitoring and autoscaling features to dynamically adjust pipeline runs, reducing costs during off-peak times.
• Leverage Synapse Analytics for improved cost and performance management if the data volumes are substantial.

Handling Deletes:

• Since Cosmos db's change feed doesnt track deletes natively a custom mechanism ("soft delete" flag or a background cleanup process) will need to be implemented. Periodically cleanup the records that are marked for deletion.

hth!

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.