Revolutionize Your Data Ingestion: Why the Top-Level CDC Resource in ADF is a Game-Changer

The new CDC resource in ADF allows for full fidelity change data capture that continuously runs in near real-time through a guided configuration experience.

In my previous projects, I faced the challenge of ingesting near real-time data from Azure SQL DB. We needed a solution that was scalable, maintainable, and cost-effective. After exploring and evaluating many options, I landed on what has now become a built-in feature in ADF.

CDC from your data source(s) without needing to design pipelines or data flows and triggers. Just point to your sources, tell ADF where you want to land the data, and click start. It's that super easy!!

With the advent of big data, real-time streaming and near real-time and batch streaming in enterprises, there can be 1k or more pipelines running either every day or every hour so it is very important that you either track it efficiently or maintain it smartly. With this Microsoft has come up with CDC as native top-level container where DE's and business people can configur these pipelines at scale without the need to know what is trigger, integration runtime etc.

Components involved in the "old way" of building pipeline are:-

• Manually building lookup activities to get the last watermarks.

• Writing stored procedures or complex queries to identify new or changed data.

• Building the pipeline logic to handle inserts, updates, and deletes.

• Managing state tables to store watermarks.

Some potential drawbacks:

• Time-consuming to author and maintain.

• Prone to human error.

• Can be difficult to scale.

• Requires a high level of expertise from a data engineer.

This no-code solution provides the following advantages: -

• Drag-and-drop interface for source and sink configuration.

• Built-in logic for change detection, eliminating the need for manual watermarking.

• Auto-scaling capabilities to handle varying data volumes.

• Support for various sources and sinks.

• It uses a 4-core General Purpose dataflow cluster that is only billed while your data is being processed based on the latencies you select.

Some key points to remember:-

• This solution still requires you set-up CDC on your source.

• It supports latency for real-time, 15-min, 30-min, 1-hour and 2-hour.

• Sources supporting it are Azure SQL DB, Azure SQL Managed Instance, Azure Cosmos DB, SAP and Snowflake.

• It supports following destinations - Azure SQL DB, Azure SQL managed instance, ADSL Gen2 and Synapse.

*image from Microsoft website showing cdc in action over last 24 hours

Scenario - Let's assume I move 10 MB of data from Azure SQL DB to ADLS Gen2 every 15 minutes for 1 month and let's break down the cost doing the old-way and the new-way.

Assumptions : Total Runs: (60/15) * 24 * 30 = 2,880 runs, Total Data Moved: 10 MB * 2,880 = 28,800 MB = 28.8 GB, For now not region specific.

Calculations:

Orchestration Cost: ($1 / 1,000 runs) * 2,880 runs = $2.88.

Data Movement Cost: For a small 10MB transfer, the duration of the Copy Data activity is likely very short, but ADF has a minimum billing duration. Let's assume a generous average run time of 1 minute per run and a single DIU.

(2,880 runs * 1 min/run) / 60 min/hr = 48 hours.

48 hours * 1 DIU * $0.25/DIU-hour = $12.00.

Total estimated cost (Old Way): $2.88 (Orchestration) + $12.00 (Data Movement) = App. 15$/month

Native CDC Resource or the new-way of doing things uses a single continuous monitored data flow.

Calculations:

Let's assume the CDC process runs for 30 seconds every 15 minutes.

Total processing time: (2,880 runs * 30 seconds) / 3600 seconds/hr = 24 hours.

Total vCore-hours: 24 hours * 4 vCores = 96 vCore-hours.

Data Flow Cost: 96 vCore-hours * $0.274/vCore-hour = App. 27$/month

Although the old way is cheaper than the native CDC resource way of doing things it ignores the non-monetary costs of the old approach, which are the true differentiators like- Development Costs, Skills of a DE, Maintenance and Reliability.

In an enterprise set-up time saved in development costs, retaining talent makes the new-way of building pipelines ( Native CDC resource ) a cost effective and a strategic decision.

Embrace the future of Data Integration

The top-level CDC resource in ADF is not just a new tool; it's a fundamental shift in how organizations can approach data ingestion.

It is more accessible, reliable, and cost-effective, allowing data teams to focus on delivering business value rather than on pipeline maintenance.

More on Native CDC resource in ADF and its development -> Capture changed data with schema evolution by using a change data capture resource - Azure Data Factory | Microsoft Learn

Source: Some materials are taken from Microsoft( https://www.microsoft.com/) however most of the content is from the author's personal experiences.