Using CDC in Microsoft Fabric to Build Faster, More Efficient Data Pipelines

Imagine a 20-million-row operational database. Every hour, your analytics platform needs to know what changed.

The traditional approach?

Copy the table again.

But if only 5,000 rows changed, why move 20 million?

As data grows, full loads become slower, more expensive and harder on source systems. Deletes are also difficult to track reliably, and data freshness suffers.

This is the problem Change Data Capture (CDC) is designed to address.

Logical activities flow

Instead of asking:

“What is the number of rows of the entire table?”

we ask:

“What changed since the last run?”

In this article, we'll build a realistic scenario using Fabric Data Factory Copy job, then explore why CDC was chosen over the alternatives — and when it may not be the right choice.

What are we actually trying to solve?

The requirement

Think of a business system that has a large operational table:

Patient table

Now, picture when the source receives:

• New patients

• Demographic updates

• Status changes

• Records being deleted

The analytics platform needs an up-to-date representation.

What approach would you take?

The naive approach

Naive traditional full load

Here is the problem with this approach:

20 million rows → moved every hour → even if only 5,000 changed.

Review possible options?

We will now consider different options and approaches. In each case we will look at the pros and con of the approach and identify whether it is a good choice or not:

Option 1 — Full Load

Pros

• Simple

• Easy to understand

• No dependency on change tracking

Cons

• Moves everything

• Higher source and Fabric consumption

• Poor scalability

• Slower refresh

Good for small/static datasets. Poor choice for high-volume operational tables.

Option 2 — Watermark-based Incremental Load

Example:

WHERE Modified_Date > LastSuccessfulRun

Pros

• Relatively simple

• Efficient

• Works without CDC

Problem: deletes.

If a row disappears from the source, there may be no Modified_Date event to tell the destination that the record was deleted.

Fabric supports watermark-based incremental copy using columns such as timestamps, ROWVERSION, dates or integers.

Excellent where the source has a reliable change column and delete detection isn't critical.

Option 3 — CDC

CDC captures:

• INSERT

• UPDATE

• DELETE

That makes it particularly attractive when the destination needs to stay aligned with the source.

 Best fit for our scenario.

Let’s consider a scenario:

Think of an NHS organisation that has an operational SQL database containing referral and waiting-list information.

The analytics platform needs to provide the following key requirements:

• Current waiting-list position

• RTT status

• Specialty performance

• Patient pathway analysis

• Operational dashboards

The source table contains millions of records, but only a relatively small proportion changes between hourly loads.

The problem

The existing process performs repeated full extracts.

This creates:

Full extract problem

The team actually wants:

A simple solution that keeps Fabric synchronised with the operational source without rebuilding the entire dataset every time.

Solution objective

Build a CDC pipeline that:

1. Performs an initial load.

2. Detects subsequent changes.

3. Replicates inserts.

4. Replicates updates.

5. Handles deletes.

6. Writes the result to Fabric.

7. Requires minimal custom orchestration.

8. Can be monitored and extended.

Solution architecture:

This architectural diagram illustrates the logical flow of activities from data sources to insight and the value created.

Logical activities flow

Here is an important architectural point:

The solution removes unnecessary pipeline logic. Let the Copy job handle change detection and replication rather than building custom CDC logic around every table.

Fabric's Copy job is specifically designed to provide simplified data movement, including incremental copy and CDC replication.

How CDC Works

1. During an initial LoadSource: 10M rows → Fabric: 10M rows

2. When Data Changes+200 Inserts | ~1,500 Updates | −50 Deletes

3. CDC Captures ChangesOnly the changes are copied:

200 INSERT | 1,500 UPDATE | 50 DELETE

The key idea here is that only what has changed in the source is leaded without reloading the entire data at source.

 CDC vs Watermark — the engineering decision

 A simple decision table.

Note:

CDC isn't automatically the right answer. The source must support CDC, and the connector must support CDC replication. Fabric currently supports CDC across a growing set of connectors, including Azure SQL Database, SQL Server, Fabric workloads, Oracle, Snowflake and others, with some capabilities still in preview.

What happens to updates and deletes?

This is where you introduce SCD Type 1 vs Type 2.

SCD Type 1 — current state

Before data insert, table looks like:

Patient_ID | Status

1001       | Waiting

After new data insert, it becomes:

Patient_ID | Status

1001       | Treated

The destination represents the current source state.

This is the default Merge behaviour in CDC Copy job. Inserts are added, updates overwrite the existing record and deletes remove the record.

SCD Type 2 — preserve history

Patient_ID | Status   | Valid_From | Valid_To | Is_Current

1001       | Waiting  | Jan 2026   | Mar 2026 | No

1001       | Treated  | Mar 2026   | NULL     | Yes

Now the question becomes:

Do we need to know what the record looks like now, or what it looked like over time?

That's a much more useful explanation than simply saying "Fabric supports SCD Type 2."

Fabric's CDC Copy job provides built-in SCD Type 2 handling, including Valid_From, Valid_To and Is_Current; however, SCD Type 2 is currently documented as in preview.

Building CDC in Fabric

Step 1 — Prepare the source

• Identify the source table.

• Confirm CDC availability.

• Enable/configure CDC where required.

• Identify primary/business keys.

• Understand expected change volume.

Where CDC is not enabled in the source table, it can be enabled using the script below as an example:

Step 2 — Create the Copy job

Add copy job activity to your Fabric workspace. Choose the data source (e.g. Azure SQL / SQL Server), table(s), destination (e.g. Fabric Warehouse) with the right mappings, then on the settings choose incremental copy, the change detection options and the write method (Append, Merge, SCD). In this case I have chosen Merge as illustrated in the image below.

Key notes:

For the incremental strategy, it is recommended you use CDC-based incremental copy, when CDC is available and deletes matter.

For write behaviour, use SCD Type 1 / Merge if the requirement is a current-state operational dataset and use SCD Type 2 when historical versions are required.

Step 3 — Run the initial load (e.g with 20,000 rows)

Validate:

• Row counts

• Primary keys

• Nulls

• Data types

• Business totals

As illustrated below:

Note the initial load run a full load.

Now check the sample data warehouse:

Exact 20,000 records inserted.

When new records (e.g 5000 rows) are added to the table, notice the behaviour:

Then run the Copy job again.

Notice, only the new 5000 records that changed was inserted into the destination table and not another full load.

Now check the total records on the warehouse destination table.

Total records become 25,000. This show that the destination reflects those changes.

Recommendations: 

When building CDC, I would recommend that at initially to avoid the following:

• Complex custom notebooks

• Custom CDC frameworks

• Elaborate metadata databases

• Dozens of stored procedures

• Custom merge engines

• Over-engineered orchestration

  
  

Instead, start with a simplified option initially as illustrated below and can be scaled up as required:

Key limitations and considerations

Current Microsoft documentation identifies several limitations, including:

• If CDC-enabled and non-CDC-enabled tables are combined in a Copy job, the job treats them as watermark-based incremental copy.

• Net change capture is currently supported, with full change capture planned.

• Custom capture instances aren't supported.

• SCD Type 2 is currently preview.

• CDC support varies by connector.

Also, it is important to consider schema evolution. Copy job's behaviour differs depending on whether column mapping is configured, and incompatible source/destination data-type changes can cause failures.

Important reminders:

Always understand the connector and workload first and foremost, before standardising the pattern.

Here is a key take away from this article:

CDC isn't simply a faster copy mechanism. It changes the way we think about data movement.

Instead of repeatedly asking "What is the entire dataset?", we ask, "What changed since the last successful load?" This is where real value lies.

Conclusion

Building modern data platforms isn't always about adding more components. Sometimes the biggest improvement comes from removing unnecessary work.

In this example, CDC provides a simple way to move the changes that matter — inserts, updates and deletes — rather than repeatedly moving an entire operational dataset.

For the sample solution, the pattern is deliberately simple: enable CDC, configure the Copy job, choose the appropriate write strategy, validate the result and monitor it.

The important part isn't the number of Fabric features used. It's the engineering decision behind them.