Can I use Change Data Capture between blob storages ?

Question

Can I use Change Data Capture between blob storages ?

kosmos 246

I have one blob storage with many folders and files.

I want to iterate through the folders and the files to load them to Snowflake. First through a staging table ( I am flexible to not use the staging table if necessary)

Does it make sense to use the Change Data Capture tool ? Or is it made only for Azure SQL databases ?

Thank you!

2 answers

Your answer

Answer 1

Subashri Vasudevan 11,226

Hi @kosmos

Yes right now only Azure SQL database and SQL managed instance is what is supported. Along with other file based target as mentioned below.

Screenshot_2023-07-02-09-36-28-32_40deb401b9ffe8e1df2f1cc5ba480b12

Please check this link for more details and limitations.If your staging is az. Sql database or any of the supported file type, it will be a better option to use CDC.

Hope that helps.

Thanks

Answer 2

https://learn.microsoft.com/en-us/azure/data-factory/concepts-change-data-capture

https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake

Yes, you can use Change Data Capture (CDC) between blob storages.

The Azure Data Factory (ADF) provides several methods

Change Data Capture factory resource: This method provides an easy way to get started with CDC in ADF. You can select your sources and destinations, apply optional transformations, and then start your data capture. The top-level CDC resource can run continuously, which is ideal for your scenario where data in the blob storage may change over time.

Native change data capture in mapping data flow: ADF mapping data flow can automatically detect and extract changed data, including inserted, updated, and deleted rows, from the source databases. This method uses native CDC technology in the databases.

Auto incremental extraction in mapping data flow: This method can automatically detect and extract newly updated rows or updated files from the source stores. When loading new or updated files only from a storage store, ADF mapping data flow works through files' last modify time.

Change files capture from file based storages: ADF provides a simple one-click solution to load new or updated files only from file-based storages, such as Azure Blob Storage, Azure Data Lake Storage Gen2, or Azure Data Lake Storage Gen1. This is the simplest and recommended way to achieve delta load from these file-based storages in mapping data flow.

For your ue case, you can use these methods to extract data from your blob storage, and then load the data into Snowflake. Azure Data Factory supports Snowflake as both a source and a sink. You can use the Copy activity to copy data from the blob storage to Snowflake. When copying data to Snowflake, it takes advantage of Snowflake's COPY into [table] command to achieve the best performance.

Also, the Snowflake connector in ADF supports several capabilities like Copy activity (source/sink), Mapping data flow (source/sink), Lookup activity, and Script activity. The connector is compatible with both Azure integration runtime and self-hosted integration runtime.

When defining the Snowflake dataset in ADF, the type property of the dataset must be set to SnowflakeTable. If Snowflake is the sink, the name of the schema and the name of the table/view must be specified, and these names are case-sensitive.

In conclusion, it definitely makes sense to use CDC for your use case. It's not limited to Azure SQL databases, and it can be used effectively with blob storages and Snowflake.

kosmos 246 Reputation points

2023-07-03T08:02:56.0866667+00:00

Hi @Sedat SALMAN ,

Amazing response.

I see that in CDC you can select between minutes and hour of sync. My use case only requires daily delta extracts.

Would you recommend any tutorial that explains how to implement CDC for azure blob storage in an ADF pipeline ?

My use case is simple: I wanto to either

Copy from blob to blob efficiently. Therefore only processing new files

Copy from blob to snowflake also processing the files.

There are of course other possibilites without CDC, like looping the files and comparing them to a watermarktable but I would need to loop all the files and this is very time consuming.

Thanks!
Sedat SALMAN 14,180 Reputation points MVP

2023-07-03T10:16:17.43+00:00

Hi @kosmos

You can follow this article

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-change-data-capture-feature-portal

https://docs.microsoft.com/en-us/learn/modules/copy-data-incrementally-change-data-capture/

Given your specific use case, you may need to adapt these instructions to suit your needs. For instance, the source and sink in your case would be different (Azure Blob Storage to Azure Blob Storage, or Azure Blob Storage to Snowflake). Also, CDC typically works with databases that support this feature natively, like SQL Server. Azure Blob Storage is a data storage service, not a database, and doesn't support CDC natively. For your case, you might need to use a different approach to identify new/changed files in Blob Storage, such as using Last Modified timestamps, or maintaining a separate metadata store with details of processed files.

For the Blob to Snowflake scenario, you might need to look at Snowflake's COPY INTO command, which can directly load data from Azure Blob Storage to a Snowflake table. You can create an Azure Data Factory pipeline to orchestrate this operation on a schedule, similar to the CDC scenario in the tutorial.
kosmos 246 Reputation points

2023-07-03T11:10:20.1766667+00:00

hi @Sedat SALMAN

if CDC is not supported in Blob storages, what are the benefits of using CDC vs creating an ADF pipeline that checks the last updated time files and loads them to Snowflake/Blob ?

Share via

Can I use Change Data Capture between blob storages ?

2 answers

Your answer