Skip to content

SSIS Incremental Loads: Watermarks, CDC, Change Tracking, and Staging

Reloading everything every night stops working at some point — the window closes, the source can no longer take the read, or the target cannot absorb the writes. Incremental loading is the answer, and it introduces the hardest correctness problem in ETL: state.

A full reload is self-correcting. An incremental load remembers where it got to, and if that memory is wrong — advanced too early, computed from the wrong clock, or invalidated by a retention window — you get silent, permanent data loss that nobody notices for months.

This article is mostly about getting that right, and only secondarily about which SQL Server feature to use.

Full load — read everything, replace the target. Simple, self-correcting, genuinely the right answer for small reference tables. A 5,000-row lookup table costs nothing to reload and can never drift.

Incremental load — read only what changed. Necessary above a certain volume, but it introduces state that can be wrong.

Choose full when Choose incremental when
The table is small The source is large
The source has no reliable change signal Changes are a small fraction of the whole
Correctness matters more than run time The batch window is under pressure
The source is a file that always arrives complete Reading everything harms the source system

Many warehouses do both: incremental nightly, full weekly or monthly, so drift cannot accumulate indefinitely. That periodic full reload is cheap insurance and is frequently the thing that catches a broken watermark.

The mental model: detect, extract, stage, apply, commit state

Section titled “The mental model: detect, extract, stage, apply, commit state”
┌────────────┐ ┌──────────┐ ┌─────────┐ ┌───────┐ ┌──────────────┐
│ DETECT │──>│ EXTRACT │──>│ STAGE │──>│ APPLY │──>│ COMMIT STATE │
│ what │ │ only │ │ raw, by │ │ set- │ │ watermark / │
│ changed │ │ those │ │ batch │ │ based │ │ LSN / version│
└────────────┘ └──────────┘ └─────────┘ └───────┘ └──────────────┘
▲ │
└──────────── next run reads the state committed here ───────┘
THE CARDINAL RULE:
state advances LAST, only after the apply has committed.
Advance it earlier and the skipped data is gone forever.

Every pattern below is a different implementation of DETECT. The rest of the pipeline is the same, and the rest of the pipeline is where correctness actually lives.

The simplest incremental load that is not wrong:

-- 1. Read state
SELECT @LastLoaded = LastLoadedOn FROM dbo.LoadControl WHERE SourceName = 'Customer';
-- 2. Fix an upper bound BEFORE extracting, from the SOURCE server's clock
SELECT @NewWatermark = MAX(ModifiedOn) FROM src.Customer;
-- 3. Extract a CLOSED interval
SELECT CustomerId, CustomerName, Region, ModifiedOn
FROM src.Customer
WHERE ModifiedOn > ? -- @LastLoaded
AND ModifiedOn <= ?; -- @NewWatermark
-- 4. Apply from staging, then 5. advance state — in that order.
UPDATE dbo.LoadControl SET LastLoadedOn = @NewWatermark WHERE SourceName = 'Customer';

The two details that make this correct rather than merely functional: the closed interval with a pre-captured upper bound, and advancing state last. Skip either and you have an intermittent data-loss bug.

Keep the highest value of a monotonically increasing column you have already loaded; next time read only rows above it.

CREATE TABLE dbo.LoadControl
(
SourceName SYSNAME NOT NULL PRIMARY KEY,
LastLoadedOn DATETIME2(3) NULL, -- timestamp watermark
LastLoadedId BIGINT NULL, -- identity/sequence watermark
LastLsn BINARY(10) NULL, -- CDC
LastVersion BIGINT NULL, -- Change Tracking
LastRunStatus VARCHAR(20) NOT NULL CONSTRAINT DF_LoadControl_Status DEFAULT 'Success',
UpdatedOn DATETIME2(3) NOT NULL CONSTRAINT DF_LoadControl_Upd DEFAULT SYSUTCDATETIME()
);

The package shape:

[Execute SQL Task] read watermark → User::LastLoadedOn (Single row)
│
[Execute SQL Task] read new upper bound → User::NewWatermark (Single row)
│ SELECT MAX(ModifiedOn) FROM src.Orders
▼
[Data Flow Task] OLE DB Source, parameterised, closed interval
→ fast load into stg.Orders (with BatchId)
│
▼
[Execute SQL Task] apply stg.Orders → dbo.Orders (idempotent, set-based)
│ Success
▼
[Execute SQL Task] advance watermark to User::NewWatermark
Problem Effect Mitigation
Deletes are invisible Removed rows stay in the target forever Soft deletes at source, periodic reconciliation, or use CDC/Change Tracking
Clock skew Rows missed or duplicated at the boundary Take the bound from the source server, never from SSIS or the target
Long transactions A row committed after your watermark passed its timestamp is never seen Overlap the window; make the apply idempotent
Column not maintained Silent data loss — the worst failure mode Verify every write path sets it, including bulk loads and triggers
State advanced on failure Data permanently skipped Advance only after the apply commits
Time zone / DST Boundary errors twice a year, or a one-hour gap Use UTC end to end, or DATETIME2 with a documented zone
Ties at the boundary Rows with identical timestamps split across runs Closed interval plus idempotent apply makes the overlap harmless

The long-transaction problem deserves attention because it is the subtlest. A transaction that starts at 01:00, stamps ModifiedOn = 01:00, and commits at 01:20 is invisible to a run that read up to 01:10 and advanced its watermark past 01:00. The row is never seen again.

The standard mitigation is a safety overlap:

-- Re-read a margin of already-seen rows; the idempotent apply absorbs duplicates
WHERE ModifiedOn > DATEADD(MINUTE, -15, ?) -- @LastLoaded, less a margin
AND ModifiedOn <= ?; -- @NewWatermark

Correctness beats minimal I/O. Choose the margin from the longest realistic write transaction on the source.

An identity or sequence column works for insert-only tables — track the maximum id loaded. It cannot detect updates, so it suits append-only logs, events, and audit tables, not entities.

Beware identity gaps and out-of-order commits: with IDENTITY, a higher value can commit before a lower one, so WHERE Id > @LastId can skip a row that was still uncommitted when you read the maximum. The same overlap technique applies.

CDC reads the transaction log asynchronously and writes changed rows into change tables under the cdc schema. It captures inserts, updates (before and after images), and deletes, ordered by LSN.

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@role_name = NULL,
@supports_net_changes = 1;

Enabling CDC creates a capture instance, change tables, and two SQL Server Agent jobs — capture and cleanup. Two table-valued functions expose the data:

  • cdc.fn_cdc_get_all_changes_<capture_instance> — every change in the interval, in order.
  • cdc.fn_cdc_get_net_changes_<capture_instance> — one row per changed key: the net effect. Requires @supports_net_changes = 1 and a primary key or unique index.

Net changes is usually what an ETL wants. If a row was inserted then updated three times inside the window, you want the final state, not four rows.

The __$operation column identifies the change type:

Value Meaning
1 Delete
2 Insert
3 Update — before image (only in “all with old values” mode)
4 Update — after image
5 Merge row (net changes with merge mode)

SSIS historically shipped three components for this: the CDC Control Task, CDC Source, and CDC Splitter. They managed LSN ranges and split the stream into insert/update/delete outputs.

Microsoft deprecated these CDC Flow Components (announced February 2024), along with the Attunity connectors. Deprecated means no further investment and eventual removal.

This is a genuine trap: a great deal of SSIS material — including tutorials that look current — still teaches the CDC Control Task as the standard approach. Do not build new packages around them, and factor replacement into any modernisation plan for packages that use them.

Straightforward, using only Execute SQL Tasks, variables, and a normal data flow.

-- 1. Determine the LSN range for this run
DECLARE @from_lsn BINARY(10) =
(SELECT LastLsn FROM dbo.LoadControl WHERE SourceName = 'Orders');
DECLARE @to_lsn BINARY(10) = sys.fn_cdc_get_max_lsn();
-- First run: start from the capture instance's minimum LSN
IF @from_lsn IS NULL
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Orders');
-- Guard: has the retention window eaten our starting point?
IF @from_lsn < sys.fn_cdc_get_min_lsn('dbo_Orders')
THROW 50010, 'CDC retention gap: full reseed required.', 1;
-- 2. Extract net changes for that range in an OLE DB Source
SELECT __$operation,
OrderId,
CustomerId,
OrderTotal,
ModifiedOn
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(?, ?, N'all')
ORDER BY OrderId;

Then land the rows in staging, separate deletes from upserts on __$operation (in SQL or with a Conditional Split), apply set-based statements, and only then persist @to_lsn as the new starting point.

That retention guard is not optional. It converts a silent, permanent data gap into a loud failure that tells you exactly what to do.

  • Cleanup retention. The CDC cleanup job removes change data older than the retention window — three days by default. If the ETL falls behind that window, changes are gone and a reseed is required. Match retention to your worst realistic outage, not your average one.
  • Capture latency. CDC is asynchronous. sys.fn_cdc_get_max_lsn() reflects what the capture job has processed, not what has been committed. Extracting to max_lsn is correct; assuming it equals “now” is not.
  • Schema changes. A capture instance is tied to a column list. Adding a column usually means creating a second capture instance and cutting over — a table supports two, which is exactly what makes the transition possible.
  • Availability groups. Change enumeration can be done on a secondary replica to reduce load on the primary.
  • Restore behaviour. Restoring a database to another server disables CDC unless you use KEEP_CDC, and KEEP_CDC is blocked on editions that do not support CDC.
  • CDC is not available in every SQL Server edition — it requires Standard or Enterprise (or Azure SQL Managed Instance / Azure SQL Database). Check the editions-and-supported-features page for your version.
  • SQL Server Agent must be running for the capture and cleanup jobs on SQL Server.
  • Change tables consume space proportional to change volume × row width × retention.
  • CDC is not supported in contained databases.

The lighter-weight sibling. Change Tracking records that a row changed and its primary key, synchronously with the transaction — but not old values, and not intermediate versions.

ALTER DATABASE StagingDb
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE dbo.Orders
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
-- Extract: changed keys joined back to the source for current values
DECLARE @last_version BIGINT =
(SELECT LastVersion FROM dbo.LoadControl WHERE SourceName = 'Orders');
-- Guard: is our version still valid?
IF @last_version < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Orders'))
THROW 50011, 'Change Tracking retention gap: full reseed required.', 1;
SELECT ct.OrderId,
ct.SYS_CHANGE_OPERATION, -- I, U, D
ct.SYS_CHANGE_VERSION,
o.CustomerId,
o.OrderTotal,
o.ModifiedOn
FROM CHANGETABLE(CHANGES dbo.Orders, @last_version) AS ct
LEFT JOIN dbo.Orders AS o
ON o.OrderId = ct.OrderId; -- NULL for deletes
-- New version to store AFTER a successful apply
SELECT CHANGE_TRACKING_CURRENT_VERSION();

Deletes appear with SYS_CHANGE_OPERATION = 'D' and no matching source row — exactly the gap watermarks cannot fill.

CHANGE_TRACKING_MIN_VALID_VERSION is the equivalent of the CDC retention guard, and equally mandatory.

CDC Change Tracking
Captures Full row images, every intermediate change Changed keys and operation type only
Mechanism Asynchronous, reads the transaction log Synchronous with the transaction
Overhead Low on the transaction; background job does the work Small synchronous cost per write
Storage Change tables sized by volume × width × retention Much smaller — keys and metadata
Historical values Yes, including before-images No
Edition requirements Not available in every edition Available broadly
Latency Capture-job latency Immediate
SSIS components The dedicated components are deprecated Never had any; plain SQL
Retention risk Yes — guard with fn_cdc_get_min_lsn Yes — guard with CHANGE_TRACKING_MIN_VALID_VERSION

Choose CDC when you need history, before-images, or a full audit of every change. Choose Change Tracking when you only need “which rows changed since version N” and can read current values from the source.

For most warehouse loads that want current state plus deletes, Change Tracking is simpler, cheaper, and has no deprecated tooling attached.

When the source offers nothing — no timestamp, no CDC, no Change Tracking, and often no cooperation — you compare.

1. Extract the full source (or a partition of it) into staging.
2. Compare staging against the target on the business key.
3. In staging only → insert
In both, different → update
In target only → delete (or soft delete)

Comparing every column is verbose and slow. Compare a hash:

ALTER TABLE stg.Customer ADD RowHash BINARY(32);
UPDATE stg.Customer
SET RowHash = HASHBYTES('SHA2_256',
CONCAT(
ISNULL(CustomerName, N'<NULL>'), N'|',
ISNULL(Region, N'<NULL>'), N'|',
ISNULL(CONVERT(NVARCHAR(30), CreditLimit, 2), N'<NULL>'), N'|',
ISNULL(CONVERT(NVARCHAR(30), StatusId), N'<NULL>')
));

Hashing rules that prevent false negatives:

  • Use a separator. Without one, ("AB","C") and ("A","BC") hash identically.
  • Make NULL distinguishable. CONCAT treats NULL as empty, merging NULL and ''. A sentinel token fixes it.
  • Use culture-invariant, lossless conversions for dates and decimals — CONVERT with an explicit style, so the same value always produces the same string.
  • Keep the column list stable and versioned. Changing it invalidates every stored hash; plan the rebuild.
  • Store the hash on the target so comparison is a single-column test.
  • Do not use HASHBYTES for security decisions here — this is change detection, and collisions are astronomically unlikely but not a security boundary.

This can also be done in the pipeline with a Lookup against the target’s key and hash, splitting matched-and-changed from matched-and-unchanged from unmatched.

The cost is honest: you still read the whole source. What you save is the write side — only genuinely changed rows touch the target, its indexes, and anything downstream watching for changes.

Applying changes: inserts, updates, upserts

Section titled “Applying changes: inserts, updates, upserts”

Whatever detects the changes, apply set-based from staging. Never use OLE DB Command to update row by row.

-- Upsert with MERGE
MERGE dbo.Customer AS tgt
USING stg.Customer AS src
ON tgt.CustomerId = src.CustomerId
WHEN MATCHED AND tgt.RowHash <> src.RowHash THEN
UPDATE SET tgt.CustomerName = src.CustomerName,
tgt.Region = src.Region,
tgt.CreditLimit = src.CreditLimit,
tgt.RowHash = src.RowHash,
tgt.ModifiedOn = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerId, CustomerName, Region, CreditLimit, RowHash, ModifiedOn)
VALUES (src.CustomerId, src.CustomerName, src.Region, src.CreditLimit,
src.RowHash, SYSUTCDATETIME());

MERGE is concise and is what most teams reach for. Two cautions worth knowing:

  • It has a history of edge-case bugs and locking surprises under concurrency. Keep the source set deduplicated and the join key genuinely unique — a MERGE whose source has duplicate keys fails at runtime.
  • Separate statements are more verbose but easier to reason about, tune, and batch. Many teams prefer them for high-volume applies.
-- The explicit alternative
UPDATE tgt
SET tgt.CustomerName = src.CustomerName,
tgt.Region = src.Region,
tgt.CreditLimit = src.CreditLimit,
tgt.RowHash = src.RowHash,
tgt.ModifiedOn = SYSUTCDATETIME()
FROM dbo.Customer AS tgt
JOIN stg.Customer AS src ON src.CustomerId = tgt.CustomerId
WHERE tgt.RowHash <> src.RowHash;
INSERT INTO dbo.Customer (CustomerId, CustomerName, Region, CreditLimit, RowHash, ModifiedOn)
SELECT src.CustomerId, src.CustomerName, src.Region, src.CreditLimit,
src.RowHash, SYSUTCDATETIME()
FROM stg.Customer AS src
WHERE NOT EXISTS (SELECT 1 FROM dbo.Customer AS tgt WHERE tgt.CustomerId = src.CustomerId);

Decide deliberately, because it affects history:

Approach Behaviour Use when
Hard delete Row removed Simple targets with no history requirement and no dependents
Soft delete IsDeleted = 1, DeletedOn set Warehouses — preserves history, lets consumers choose
Ignore Deletes not propagated Append-only facts where source deletes cannot happen

Watermarks cannot detect deletes at all. That single fact is the most common reason teams move from watermarks to Change Tracking or CDC.

The apply should be one transaction per batch, short and set-based:

SET XACT_ABORT ON;
BEGIN TRAN;
DELETE FROM dbo.FactSales WHERE BatchId = @BatchId; -- idempotent re-entry
INSERT INTO dbo.FactSales (...) SELECT ... FROM stg.SalesLine WHERE BatchId = @BatchId;
UPDATE dbo.LoadControl SET LastLoadedOn = @NewWatermark WHERE SourceName = 'Sales';
COMMIT;

Putting the state advance inside the same transaction as the apply is the strongest guarantee available: either both happen or neither does. Where that is impossible (state lives on a different server), advance state after the apply commits and accept that a crash between them causes a harmless re-run — which is exactly what idempotence is for.

For very large applies, batch the statement in chunks to limit log growth and lock duration — accepting that the operation is then no longer atomic, so idempotence becomes mandatory rather than merely desirable.

A fictional customer dimension using Change Tracking, with deletes and late-arriving data handled.

LoadCustomerDim.dtsx
[Execute SQL Task] "Open batch" → User::BatchId
▼
[Execute SQL Task] "Read + validate CT version"
DECLARE @last BIGINT = (SELECT LastVersion FROM dbo.LoadControl WHERE SourceName='Customer');
IF @last IS NULL OR @last < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('src.Customer'))
SELECT @ReseedRequired = 1;
SELECT @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();
Results → User::LastVersion, User::CurrentVersion, User::ReseedRequired
│
├── [Expression: @ReseedRequired == 1] ──> [Execute SQL Task] "Alert: reseed required"
│ then [Data Flow] FULL extract
│
└── [Expression: @ReseedRequired == 0]
▼
[Data Flow Task] "Extract changes"
OLE DB Source:
SELECT ct.CustomerId, ct.SYS_CHANGE_OPERATION, c.CustomerName,
c.Region, c.CreditLimit, c.StatusId
FROM CHANGETABLE(CHANGES src.Customer, ?) AS ct
LEFT JOIN src.Customer AS c ON c.CustomerId = ct.CustomerId;
▼
Derived Column BatchId, LoadedOn
▼
OLE DB Destination (fast load) → stg.CustomerChange
▼
[Execute SQL Task] "Apply batch" EXEC dbo.usp_DimCustomer_ApplyBatch @BatchId = ?
│ Success (the proc advances LastVersion inside its own transaction)
▼
[Execute SQL Task] "Close batch"
CREATE OR ALTER PROCEDURE dbo.usp_DimCustomer_ApplyBatch
@BatchId INT
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
DECLARE @NewVersion BIGINT =
(SELECT TOP (1) CurrentVersion FROM stg.CustomerChangeBatch WHERE BatchId = @BatchId);
BEGIN TRAN;
-- Deletes → soft delete, preserving history
UPDATE d
SET d.IsDeleted = 1,
d.DeletedOn = SYSUTCDATETIME()
FROM dbo.DimCustomer AS d
JOIN stg.CustomerChange AS s ON s.CustomerId = d.CustomerId
WHERE s.BatchId = @BatchId
AND s.SYS_CHANGE_OPERATION = 'D'
AND d.IsDeleted = 0;
-- Inserts and updates → one upsert over the non-delete rows
MERGE dbo.DimCustomer AS tgt
USING (SELECT * FROM stg.CustomerChange
WHERE BatchId = @BatchId AND SYS_CHANGE_OPERATION <> 'D') AS src
ON tgt.CustomerId = src.CustomerId
WHEN MATCHED THEN
UPDATE SET tgt.CustomerName = src.CustomerName,
tgt.Region = src.Region,
tgt.CreditLimit = src.CreditLimit,
tgt.IsDeleted = 0, -- resurrect if it came back
tgt.ModifiedOn = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerId, CustomerName, Region, CreditLimit, IsDeleted, ModifiedOn)
VALUES (src.CustomerId, src.CustomerName, src.Region, src.CreditLimit, 0, SYSUTCDATETIME());
-- State advances in the SAME transaction as the apply
UPDATE dbo.LoadControl
SET LastVersion = @NewVersion,
UpdatedOn = SYSUTCDATETIME()
WHERE SourceName = 'Customer';
COMMIT;
END;

Points worth noticing:

  • The reseed guard is a first-class branch, not an afterthought — retention gaps are a normal operational event, not an exception.
  • Deletes are soft, and a resurrected customer is handled (IsDeleted = 0 on match).
  • State advances inside the apply transaction, giving all-or-nothing semantics.
  • The change extract is a single query, so there is nothing row-by-row anywhere.

Two distinct problems that get conflated.

Late-arriving facts — a sale for last Tuesday arrives today. The row’s business date is older than the current load window. Handle by keying the apply on business date, not load date, and by making per-date reloads cheap (partition switching, or delete-then-insert by date). Never assume today’s file only contains today’s data.

Late-arriving dimensions — a fact references a dimension member that does not exist yet. Options:

Approach Behaviour
Reject the fact Safe, but loses data and needs a replay mechanism
Inferred member Insert a placeholder dimension row with the business key and unknown attributes; update it when the real record arrives
Unknown key Point the fact at a catch-all “Unknown” member; loses the linkage permanently

The inferred-member pattern is usually correct in a warehouse: the fact loads, the relationship is preserved, and the dimension self-heals.

CDC gives you LSN order; Change Tracking gives you version order; a watermark gives you timestamp order, which is not commit order. If your apply depends on processing changes in the order they happened — for example a status machine — a timestamp watermark is not sufficient.

When several sources feed one table, each needs its own state row and its own batch. Sharing a watermark across sources means the slowest source drags the others, or the fastest one advances past changes the others have not seen.

Every incremental design needs a documented reseed path, and it should be tested:

  1. Detect the gap (the retention guards above).
  2. Full-extract the source into staging.
  3. Apply as a complete replacement for the affected scope.
  4. Capture the current LSN/version before the full extract begins, and store it after the apply — otherwise you re-open the gap.

That last step is the one people get wrong.

System-versioned temporal tables keep full history automatically, with ValidFrom/ValidTo period columns and a system-managed history table.

They solve a related but different problem. Temporal tables give you history on the table you own; CDC and Change Tracking give you a change feed from a source you read. They intersect usefully:

  • If the source is temporal, FOR SYSTEM_TIME BETWEEN gives an effective incremental extract with no extra infrastructure — and, unlike a watermark, it is based on system-maintained times you can trust.
  • If the target is temporal, the ETL can upsert current state and let the engine maintain history, replacing hand-built Type 2 slowly-changing-dimension logic in some designs.

See Temporal Tables in SQL Server for the mechanics.

BAD:
[Read watermark] → [Advance watermark] → [Extract] → [Apply]
The apply fails. The watermark has already moved.
Those rows are never read again. Nobody notices for months.
BETTER:
[Read watermark] → [Capture upper bound] → [Extract] → [Apply] → [Advance watermark]
and, where possible, advance INSIDE the apply transaction.
BAD:
WHERE ModifiedOn > @LastLoaded
then: SET @LastLoaded = (SELECT MAX(ModifiedOn) FROM the rows I just read)
Rows written DURING the extract may or may not be included, and the new
watermark depends on which. Non-deterministic gaps.
BETTER:
Capture @NewWatermark BEFORE extracting; use a closed interval
( > @LastLoaded AND <= @NewWatermark ); store @NewWatermark after the apply.

Anti-pattern: trusting an unverified ModifiedOn

Section titled “Anti-pattern: trusting an unverified ModifiedOn”

A column that “should” be maintained by the application, but is not set by the nightly bulk correction job, by direct DBA fixes, or by a legacy import path. The load looks healthy and silently misses a class of changes. Verify every write path before basing a load on it — and run a periodic full reconciliation regardless.

  • Assuming a watermark catches deletes. It does not.
  • Building new work on the deprecated SSIS CDC components.
  • No retention guard, so a long outage silently becomes a data gap.
  • Hashing without separators or NULL handling.
  • Row-by-row applies via OLE DB Command.
  • MERGE against a source with duplicate keys, which fails at runtime.
  • Sharing one watermark across multiple sources.
  • Never testing the reseed path until the day it is needed.

Failure modes, recovery, and troubleshooting

Section titled “Failure modes, recovery, and troubleshooting”
Symptom Likely cause Action
Rows missing, no errors ever logged Watermark advanced on a failed run, or an unmaintained source column Reconcile counts source vs target; check LoadControl history
Duplicate rows in the target Non-idempotent apply plus a re-run Make the apply delete-then-insert by batch, or key-based upsert
Deleted source rows still present Watermark pattern cannot see deletes Move to Change Tracking/CDC, or add periodic reconciliation
“An insufficient number of arguments” from a CDC function @from_lsn is NULL on first run Initialise from sys.fn_cdc_get_min_lsn
CDC returns nothing though data changed Capture job not running, or extracting past max_lsn Check Agent jobs; sys.fn_cdc_get_max_lsn()
Change Tracking query errors after an outage Version older than the minimum valid version Reseed; add the guard so it fails loudly next time
MERGE fails: “attempted to update the same row more than once” Duplicate keys in the source set Deduplicate in staging before the apply
Load slows every week Growing source with an ineffective predicate Index the watermark column; check the plan
Boundary rows duplicated twice a year Local time and DST Move to UTC end to end

Reconciliation is the only reliable detector of silent loss. Run it on a schedule, not only when someone complains:

-- Periodic reconciliation: does the target agree with the source?
SELECT 'source' AS side, COUNT(*) AS row_count, SUM(CAST(OrderTotal AS DECIMAL(19,2))) AS total
FROM src.Orders WHERE OrderDate >= @From AND OrderDate < @To
UNION ALL
SELECT 'target', COUNT(*), SUM(CAST(OrderTotal AS DECIMAL(19,2)))
FROM dbo.FactOrders WHERE OrderDate >= @From AND OrderDate < @To;
  • Index the watermark column. A non-SARGable or unindexed predicate turns an “incremental” extract into a full scan with extra steps.
  • CDC change tables grow with change volume × row width × retention. They also need their own maintenance.
  • Change Tracking adds a small synchronous cost to every write on the tracked table — usually negligible, occasionally not on a very hot OLTP table.
  • Staging-and-compare reads everything, so its cost is bounded by source size, not change volume. Its benefit is on the write side.
  • Hash computation costs CPU. Doing it in SQL after staging is usually cheaper and more consistent than doing it in the pipeline.
  • The apply is often the real bottleneck, not the extract — index maintenance on the target dominates once change volume is significant.
  • CDC change tables contain full row images, including columns you may not want widely readable. Use the @role_name gating role to control access.
  • Enabling CDC or Change Tracking requires elevated permissions (sysadmin for CDC on SQL Server; db_owner on Azure SQL Database), which is a change-control event, not a developer action.
  • The LoadControl table is security-relevant. Anyone who can write to it can cause data to be skipped silently. Restrict writes to the ETL identity.
  • Staging tables hold source data at rest. Apply the same classification and retention as the target.
  • Restoring a database with KEEP_CDC carries the capture configuration with it, including the gating roles — verify they still mean what you intend on the new server.

Testing, validation, and operational monitoring

Section titled “Testing, validation, and operational monitoring”
  • Test the reseed path on a schedule. An untested recovery procedure is a hypothesis.
  • Test idempotence explicitly: run the same batch twice, assert the target is identical.
  • Test the boundary: insert a row with a timestamp exactly equal to the watermark and confirm it is handled once, not zero or twice.
  • Test deletes, including a delete-then-reinsert of the same key.
  • Monitor state freshness, not only job success:
-- Sources whose watermark has not moved when it should have
SELECT SourceName,
LastLoadedOn,
LastVersion,
UpdatedOn,
DATEDIFF(HOUR, UpdatedOn, SYSUTCDATETIME()) AS hours_since_update
FROM dbo.LoadControl
WHERE DATEDIFF(HOUR, UpdatedOn, SYSUTCDATETIME()) > 26 -- expected daily
ORDER BY hours_since_update DESC;

That query catches the failure mode nothing else does: a job that “succeeds” every night while loading nothing.

Situation Use
Small reference table Full reload
Reliable ModifiedOn, deletes don’t matter Watermark
Need deletes, current state only Change Tracking
Need full history and before-images CDC (via SQL functions, not the deprecated components)
Source offers no change signal Staging and compare with hashes
You own the source and want history for free Temporal tables
Append-only events Watermark on identity/sequence
Very large partitioned fact table Watermark or CDC + partition switching
Dimension Watermark Change Tracking CDC Compare
Setup cost Lowest Low Moderate Low
Source overhead None Small, synchronous Background job Read amplification
Detects deletes No Yes Yes Yes
History available No No Yes No
Retention risk No Yes Yes No
Edition constraints None Broad support Standard/Enterprise None

Q: What is wrong with WHERE ModifiedOn > @LastLoaded and then setting @LastLoaded to the maximum you read? It is an open-ended window whose upper bound is determined by the data you happened to read. Rows written during the extract may or may not be included, so the new watermark is non-deterministic and can skip rows on the next run. The fix is to capture the upper bound from the source before extracting, use a closed interval (> @Last AND <= @New), and store the pre-captured bound after the apply commits.

Q: A load has been running “successfully” for six months and rows are missing. Where do you look first? The state advance. Either the watermark was advanced before the apply committed (so a failed run silently skipped a window), or the source’s ModifiedOn column is not maintained by every write path — a bulk correction job, a legacy import, or manual DBA fixes that bypass the application. Both look identical from the outside: green jobs, missing data. Reconciliation between source and target over a historical window is the diagnostic.

Q: Explain the long-transaction problem with timestamp watermarks. A transaction can stamp ModifiedOn at the moment it starts but commit much later. If a load reads up to 01:10 and advances past a row stamped 01:00 that only commits at 01:20, that row is never visible to any future run. The mitigation is a safety overlap — re-read a margin of already-processed time — combined with an idempotent apply so the duplicates are harmless. LSN- or version-based mechanisms (CDC, Change Tracking) do not have this problem because they order by commit, not by a column value.

Q: Why are the SSIS CDC components no longer the right answer? Microsoft deprecated the CDC Flow Components — CDC Control Task, CDC Source, CDC Splitter — in February 2024, along with the Attunity connectors. Deprecated means no further investment and eventual removal. CDC itself, the SQL Server feature, is unaffected: you consume it with sys.fn_cdc_get_min_lsn / get_max_lsn and the fn_cdc_get_net_changes_* functions from ordinary Execute SQL Tasks and an OLE DB Source. The trap is that a lot of current-looking material still teaches the deprecated components.

Q: How do you protect an incremental load against a retention gap? Guard explicitly at the start of every run. For CDC, compare your stored @from_lsn against sys.fn_cdc_get_min_lsn(capture_instance); for Change Tracking, compare your stored version against CHANGE_TRACKING_MIN_VALID_VERSION. If the stored value is older, the change history you need has been cleaned up — fail loudly and trigger the reseed path rather than extracting a partial window and pretending it was complete. And when reseeding, capture the current LSN/version before the full extract starts, or you reopen the gap.

Q: Design a change-detection strategy for a source with no timestamp, no CDC, and no cooperation from its owners. Staging-and-compare with hashing. Full-extract into a staging heap, compute a HASHBYTES('SHA2_256', …) change hash over the business columns with explicit separators, NULL sentinels, and culture-invariant conversions, then compare against the stored hash on the target keyed by business key. Rows in staging only are inserts, hash mismatches are updates, rows in the target only are deletes. You still pay the full read, but the write side — indexes, downstream propagation, history tables — only sees genuine changes.

Q: Where should the watermark update live relative to the apply? Ideally inside the same transaction, so the apply and the state advance are atomic — either both happen or neither does. When the state lives elsewhere and that is impossible, advance it immediately after the apply commits and rely on idempotence: a crash in between causes a harmless re-run of the same batch. What is never acceptable is advancing before the apply.

Q: A fact row arrives referencing a customer that does not exist in the dimension. Options? Three: reject the fact (safe but loses data and needs a replay mechanism), point it at an “Unknown” member (loses the linkage permanently), or create an inferred member — a placeholder dimension row carrying the business key with unknown attributes, updated in place when the real record arrives. In a warehouse the inferred member is usually correct: the fact loads, the relationship survives, and the dimension self-heals without manual intervention.