Incremental loads in Azure Data Factory: the watermark pattern step by step

There's a pattern that repeats in almost every ingestion pipeline: at the start of the project, reloading the whole table on every run seems harmless. The source is small, the load runs in seconds, and nobody thinks twice. The problem shows up months later, when the table has tens of millions of rows and the "full" load that used to run overnight starts spilling into business hours.

The solution in Azure Data Factory doesn't require a new tool or rewriting the pipeline from scratch. It's the watermark pattern: instead of reprocessing everything, you keep the last value already loaded and, on the next run, bring only the rows that arrived after it.

What a watermark is

A watermark is simply the point up to which you've already loaded. In practice, it's the maximum value of a column that only grows at the source:

• a modification date/time column (ModifiedDate, UpdatedAt);
• an incremental identifier (Id, RowVersion);
• or a change tracking / CDC marker, when the source has no reliable column.

You keep that value in a small control table. On each run, the pipeline reads the old watermark, finds the new maximum at the source, and moves only the interval between the two.

The control table

Start with something minimal:

CREATE TABLE dbo.load_control (
    TargetTable     sysname      NOT NULL,
    WatermarkValue  datetime2    NOT NULL,
    CONSTRAINT PK_load_control PRIMARY KEY (TargetTable)
);

-- seed: a very old date so the first load picks up everything
INSERT INTO dbo.load_control VALUES ('sales', '1900-01-01');

Step 1 — Lookup the old and the new watermark

In the pipeline, use two Lookup activities:

• "Old watermark" Lookup — reads the current value from the control table:

SELECT WatermarkValue AS old
FROM dbo.load_control
WHERE TargetTable = 'sales';

• "New watermark" Lookup — reads the current maximum at the source:

SELECT MAX(ModifiedDate) AS new
FROM dbo.sales;

Reading the new maximum once, at the start, closes the window (old, new] before moving any data. This avoids the classic problem of rows that arrive during the load and would end up in a limbo between one run and the next.

Step 2 — Copy Data only for the new window

In the Copy Data activity, use a dynamic query on the source, parameterized with the output of the two Lookups:

SELECT *
FROM dbo.sales
WHERE ModifiedDate >  '@{activity('lkp_old').output.firstRow.old}'
  AND ModifiedDate <= '@{activity('lkp_new').output.firstRow.new}'

The detail that avoids duplication: use strictly greater than the old watermark and less than or equal to the new one (> @old AND <= @new). If you use >= on both sides, the row exactly on the boundary enters two consecutive runs.

Step 3 — A Stored Procedure updates the watermark

The pipeline's last activity is a Stored Procedure that writes the new value only after the Copy finished successfully:

CREATE PROCEDURE dbo.sp_update_watermark
    @Table sysname,
    @NewValue datetime2
AS
BEGIN
    UPDATE dbo.load_control
    SET WatermarkValue = @NewValue
    WHERE TargetTable = @Table;
END

Because the update depends on the Copy's success, if the pipeline fails midway the watermark does not advance — and the re-run simply redoes the same window. That's what makes the load idempotent: running it again neither duplicates nor skips a row.

When a watermark isn't enough

The pattern assumes a column that only grows and reflects every change. It works very well for inserts and updates with a ModifiedDate maintained by the application, but it has limits:

• Physical deletes don't appear — the row vanishes from the source without touching the maximum. To capture them, use a soft delete (a flag) or CDC.
• Sources with no reliable modification column — swap the watermark for SQL Server Change Tracking or CDC, which deliver exactly the set of changes since the last point.
• Fabric / OneLake — the same pattern applies in the Copy Job and in Fabric Data Factory pipelines; the interface changes, not the logic.

Quick checklist

1. Control table with one watermark per target, seeded with an old value.
2. Lookup of the old watermark (control) + Lookup of the new maximum (source).
3. Copy Data with the query WHERE column > @old AND column <= @new.
4. Stored Procedure writes @new to the control after the Copy succeeds.
5. For deletes or sources without a date column, move to CDC / change tracking.

Conclusion

Before asking for more compute or squeezing the overnight window, swap the full load for an incremental one. The watermark pattern in Azure Data Factory is one of the best cost-benefit tweaks in ingestion: less data moving, loads in minutes, and the freedom to run hourly instead of once a day. It's state control, not magic — and it fits in four activities in the pipeline.