Incremental Loads: Moving Only What Changed

An incremental load moves changes instead of copying the entire source every time. Its reliability depends on how changes are identified, how deletes are represented, and when progress is recorded.

Choose a Change Contract Before a Query

Ask what the source can guarantee. Does every relevant update change a timestamp?

Are deletions recorded? Can several transactions commit out of timestamp order? The extraction query cannot repair a missing source contract.

Use a stable business key to match source and target rows. Store the last successfully applied boundary separately from the time the scheduler started. These values describe different events.

Also decide whether the target needs the latest state or every intermediate change. A current-state synchronization can collapse repeated updates. An event history may need each one retained in order.

Use Watermarks Only When Their Meaning Is Reliable

A timestamp watermark is straightforward when the source provides a trustworthy modification value and a consistent extraction boundary. A timestamp assigned before commit can be missed by a naive moving cutoff. Equal timestamp values also require deliberate handling.

CREATE TABLE #SourceChanges
(
    Id int PRIMARY KEY,
    ModifiedAt datetime2 NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #SourceChanges VALUES
 (1, '2026-01-01T10:00:00', 10),
 (2, '2026-01-01T11:00:00', 20);
DECLARE @From datetime2 = '2026-01-01T09:00:00';
DECLARE @To datetime2 = '2026-01-01T11:00:00';
SELECT Id, ModifiedAt, Amount
FROM #SourceChanges
WHERE ModifiedAt > @From AND ModifiedAt <= @To;

This fixed temporary example illustrates the interval only. It is not a complete concurrency-safe extraction protocol for a changing source. Validate source transaction behavior before applying the pattern.

An overlap window with idempotent target application can reduce some late-arrival risk. It cannot guarantee correctness for arbitrarily late changes. Document the allowed delay and a reconciliation or reinitialization process.

Consider Change Tracking for Current-State Synchronization

Change Tracking records which keys changed and whether the change represents an insert, update, or delete. Consumers retrieve current values from the source when needed. It does not retain every intermediate row image.

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version;
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name,
       CHANGE_TRACKING_MIN_VALID_VERSION(object_id) AS minimum_valid_version
FROM sys.change_tracking_tables;

Validate the saved synchronization version against the minimum valid version for each tracked table. If it is too old, the required history may have been cleaned up. Reinitialize instead of pretending an incomplete change set is complete.

Microsoft documents a snapshot-transaction sequence for validating versions and reading changes consistently. Use that complete protocol, including its prerequisites. A version check performed long before extraction is not equivalent.

Use Hashes to Compare Stable Row Representations

A hash can help identify differences between source and target snapshots when no change feed exists. It does not eliminate the source scan needed to compute or obtain those hashes. It is a comparison technique, not a record of when the row changed.

DECLARE @Payload nvarchar(max) =
    N'{"Id":1,"Amount":"10.00","Status":"Open"}';
SELECT HASHBYTES('SHA2_256', @Payload) AS payload_hash;

The example hashes a deliberately defined representation. Real code must serialize columns consistently, including NULLs, separators, types, and date formats. Ambiguous concatenation can make different rows look identical before hashing even begins.

Hash collisions are possible, so choose verification appropriate to the consequence of an error. A checksum is not a universal proof of equality. Preserve a key-level comparison path when exact reconciliation is required.

Design Deletes and Retries Explicitly

A row removed from a timestamp-based source leaves nothing to select. Use tombstones, a retained change feed, or an approved full comparison when deletes matter. Otherwise, the target accumulates rows that no longer exist.

DECLARE @Target table (Id int PRIMARY KEY);
DECLARE @SourceSnapshot table (Id int PRIMARY KEY);
INSERT @Target VALUES (1), (2);
INSERT @SourceSnapshot VALUES (1);
SELECT t.Id AS missing_from_source
FROM @Target AS t
WHERE NOT EXISTS
 (SELECT 1 FROM @SourceSnapshot AS s WHERE s.Id = t.Id);

This reports candidates rather than deleting them. The source snapshot must be complete and authoritative before absence means deletion. A failed extraction must never look like an empty valid source.

Advance Progress After Durable Application

Apply the changes and save their progress boundary together when they share a transactional target. If those operations span systems, use a restartable protocol that tolerates repeats. Never move the watermark simply because extraction finished.

Test failure after extraction, during target writes, and before progress recording. Replaying the same accepted batch should not duplicate business effects. The useful guarantee is recoverable progress, not a scheduler that usually finishes.

Incremental loading is not just a WHERE clause, it is a contract for remembering change.

This post was rewritten from scratch in September 2026. The original, published on 2011-07-08, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.