ADE Incremental Loading and CDC

A full load copies an entire table on every run. That approach works for small tables and collapses under large ones. Incremental loading copies only the rows that changed since the last run. The pipeline finishes faster, costs less, and puts less strain on the source system.

Full Load vs Incremental Load

Imagine a library that wants a copy of the borrowing register. A full load photocopies the whole register every night. An incremental load photocopies only the pages written today. The second method saves paper, time, and effort.

Feature	Full Load	Incremental Load
Data copied per run	Entire table	Only new and changed rows
Run time	Grows as the table grows	Stays steady
Source system strain	High	Low
Design effort	Simple	Needs change tracking

The Watermark Pattern

A watermark is a marker that records how far the pipeline has read. The marker usually lives in a column such as last_modified or an ever-increasing ID. The pipeline reads the stored marker, copies rows beyond it, and saves a new marker.

CONTROL TABLE                     SOURCE TABLE (loans)
last_watermark = 2026-10-24       loan_id | last_modified
        |                         --------+--------------
        |                         101     | 2026-10-23
        v                         102     | 2026-10-25  <-- new
Step 1: Lookup old watermark      103     | 2026-10-25  <-- new
Step 2: Lookup new watermark (MAX of last_modified = 2026-10-25)
Step 3: Copy rows where last_modified > old AND <= new
Step 4: Save new watermark back to the control table

Building It in Azure Data Factory

1. A Lookup activity reads the old watermark from a small control table.
2. A second Lookup activity reads the newest last_modified value in the source.
3. A Copy activity filters the source with a query that uses both values.
4. A Stored Procedure activity writes the new watermark to the control table.

SELECT *
FROM loans
WHERE last_modified > '@{activity('GetOldMark').output.firstRow.last_watermark}'
  AND last_modified <= '@{activity('GetNewMark').output.firstRow.new_mark}'

The upper bound matters. Rows that arrive during the copy wait for the next run instead of slipping through the gap.

Scenario: Town Library Loans

The fictional Riverside Library records 2 million loans in an Azure SQL Database. A full nightly copy took two hours. The incremental pipeline copies about 3,000 changed loans and finishes in under two minutes. The saved time frees the team to run extra quality checks.

Change Data Capture

The watermark pattern misses two cases: deleted rows and tables without a modified column. Change Data Capture, known as CDC, solves both. CDC reads the database transaction log and records every insert, update, and delete as a change row.

Source database
   |  (transaction log records every change)
   v
CDC change table
   | operation | loan_id | change_time |
   |  INSERT   |  104    | 10:01       |
   |  UPDATE   |  101    | 10:05       |
   |  DELETE   |  099    | 10:07       |
   v
Pipeline applies each change to the target in order

Azure SQL Database and SQL Server support CDC and Change Tracking natively. Azure Data Factory also offers a CDC resource that moves changes continuously without hand-built watermark logic.

Handling Deletes

• Soft delete: The source flips an is_deleted flag, and the pipeline copies the flag like any other change.
• CDC delete rows: The pipeline reads delete operations and removes matching rows from the target.
• Periodic full compare: A weekly job compares key lists and removes orphans.

Applying Changes to the Target

The target needs an upsert, which updates existing rows and inserts new ones. Delta Lake and Azure SQL Database both provide a MERGE command for this job. Run the merge on a staging copy of the changes so a failed load never touches final tables.

Pitfalls to Avoid

• Time zone differences between source and pipeline shift the watermark and skip rows.
• A source that updates last_modified unreliably breaks the whole pattern.
• Saving the new watermark before the copy succeeds loses data after a failure.
• Rerunning a failed load without a MERGE creates duplicates.

Key Points

• Incremental loading copies only new and changed rows.
• A watermark column and a control table track progress between runs.
• CDC captures inserts, updates, and deletes straight from the transaction log.
• MERGE into the target keeps reruns safe and free of duplicates.