SSIS Incremental Loads: Watermarks, CDC, Change Tracking, and Staging
Why this topic matters
Section titled “Why this topic matters”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.
Fundamentals: full vs incremental
Section titled “Fundamentals: full vs incremental”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.
Minimal working example
Section titled “Minimal working example”The simplest incremental load that is not wrong:
-- 1. Read stateSELECT @LastLoaded = LastLoadedOn FROM dbo.LoadControl WHERE SourceName = 'Customer';
-- 2. Fix an upper bound BEFORE extracting, from the SOURCE server's clockSELECT @NewWatermark = MAX(ModifiedOn) FROM src.Customer;-- 3. Extract a CLOSED intervalSELECT CustomerId, CustomerName, Region, ModifiedOnFROM src.CustomerWHERE 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.
Pattern 1: high-water mark
Section titled “Pattern 1: high-water mark”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::NewWatermarkWhere watermarks go wrong
Section titled “Where watermarks go wrong”| 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 duplicatesWHERE ModifiedOn > DATEADD(MINUTE, -15, ?) -- @LastLoaded, less a margin AND ModifiedOn <= ?; -- @NewWatermarkCorrectness beats minimal I/O. Choose the margin from the longest realistic write transaction on the source.
When there is no timestamp
Section titled “When there is no timestamp”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.
Pattern 2: Change Data Capture (CDC)
Section titled “Pattern 2: Change Data Capture (CDC)”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 = 1and 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) |
The deprecated SSIS CDC components
Section titled “The deprecated SSIS CDC components”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.
Consuming CDC without those components
Section titled “Consuming CDC without those components”Straightforward, using only Execute SQL Tasks, variables, and a normal data flow.
-- 1. Determine the LSN range for this runDECLARE @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 LSNIF @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 SourceSELECT __$operation, OrderId, CustomerId, OrderTotal, ModifiedOnFROM 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.
Operational details
Section titled “Operational details”- 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 tomax_lsnis 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, andKEEP_CDCis blocked on editions that do not support CDC.
Costs and prerequisites
Section titled “Costs and prerequisites”- 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.
Pattern 3: Change Tracking
Section titled “Pattern 3: Change Tracking”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 StagingDbSET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE dbo.OrdersENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);-- Extract: changed keys joined back to the source for current valuesDECLARE @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.ModifiedOnFROM CHANGETABLE(CHANGES dbo.Orders, @last_version) AS ctLEFT JOIN dbo.Orders AS o ON o.OrderId = ct.OrderId; -- NULL for deletes
-- New version to store AFTER a successful applySELECT 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 vs Change Tracking
Section titled “CDC vs Change Tracking”| 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.
Pattern 4: staging and compare
Section titled “Pattern 4: staging and compare”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.CustomerSET 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.
CONCATtreats NULL as empty, mergingNULLand''. A sentinel token fixes it. - Use culture-invariant, lossless conversions for dates and decimals —
CONVERTwith 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
HASHBYTESfor 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 MERGEMERGE dbo.Customer AS tgtUSING stg.Customer AS src ON tgt.CustomerId = src.CustomerIdWHEN 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
MERGEwhose 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 alternativeUPDATE tgtSET tgt.CustomerName = src.CustomerName, tgt.Region = src.Region, tgt.CreditLimit = src.CreditLimit, tgt.RowHash = src.RowHash, tgt.ModifiedOn = SYSUTCDATETIME()FROM dbo.Customer AS tgtJOIN stg.Customer AS src ON src.CustomerId = tgt.CustomerIdWHERE 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 srcWHERE NOT EXISTS (SELECT 1 FROM dbo.Customer AS tgt WHERE tgt.CustomerId = src.CustomerId);Handling deletes
Section titled “Handling deletes”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.
Transaction boundaries
Section titled “Transaction boundaries”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 realistic end-to-end example
Section titled “A realistic end-to-end example”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 INTASBEGIN 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 = 0on 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.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”Late-arriving data
Section titled “Late-arriving data”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.
Ordering and concurrency
Section titled “Ordering and concurrency”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.
Multiple sources, one target
Section titled “Multiple sources, one target”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.
Reseed strategy
Section titled “Reseed strategy”Every incremental design needs a documented reseed path, and it should be tested:
- Detect the gap (the retention guards above).
- Full-extract the source into staging.
- Apply as a complete replacement for the affected scope.
- 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.
Temporal tables
Section titled “Temporal tables”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 BETWEENgives 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.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: advance-then-load
Section titled “Anti-pattern: advance-then-load”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.Anti-pattern: the open-ended window
Section titled “Anti-pattern: the open-ended window”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.
Other frequent mistakes
Section titled “Other frequent mistakes”- 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.
MERGEagainst 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 totalFROM src.Orders WHERE OrderDate >= @From AND OrderDate < @ToUNION ALLSELECT 'target', COUNT(*), SUM(CAST(OrderTotal AS DECIMAL(19,2)))FROM dbo.FactOrders WHERE OrderDate >= @From AND OrderDate < @To;Performance and resource considerations
Section titled “Performance and resource considerations”- 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.
Security and configuration considerations
Section titled “Security and configuration considerations”- CDC change tables contain full row images, including columns you may not want widely readable. Use the
@role_namegating role to control access. - Enabling CDC or Change Tracking requires elevated permissions (
sysadminfor CDC on SQL Server;db_owneron Azure SQL Database), which is a change-control event, not a developer action. - The
LoadControltable 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_CDCcarries 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 haveSELECT SourceName, LastLoadedOn, LastVersion, UpdatedOn, DATEDIFF(HOUR, UpdatedOn, SYSUTCDATETIME()) AS hours_since_updateFROM dbo.LoadControlWHERE DATEDIFF(HOUR, UpdatedOn, SYSUTCDATETIME()) > 26 -- expected dailyORDER BY hours_since_update DESC;That query catches the failure mode nothing else does: a job that “succeeds” every night while loading nothing.
Trade-offs and choosing a pattern
Section titled “Trade-offs and choosing a pattern”| 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 |
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”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.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Data Flow: Sources, Destinations, Transformations, and Pipeline Internals
- SSIS Large-Volume ETL and Performance Engineering
- SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints
- Temporal Tables in SQL Server
- SQL Server Indexes
- SQL Server Joins
Official Microsoft references
Section titled “Official Microsoft references”- Change Data Capture (SSIS)
- CDC Flow Components — note the deprecation banner
- Track data changes (SQL Server)
- What is change data capture (CDC)?
- Enable and disable change data capture
- Change data capture and other SQL Server features
- About Change Tracking (SQL Server)
- Work with change tracking (SQL Server)
- CHANGETABLE (Transact-SQL)
- CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)
- sys.sp_cdc_enable_table (Transact-SQL)
- MERGE (Transact-SQL)
- Temporal Tables