When you start out building loads, you reload everything, every night. Truncate the target, pull the whole source table, load it fresh. It's simple, it's obviously correct, and it works beautifully — right up until the table has fifty million rows and your nightly window can't fit a full reload any more. Then you have to learn incremental loading, and there's a right way and several wrong ways that look right until they eat a row.

I learned this one partly the hard way, so here's the pattern I wish someone had drawn on a whiteboard for me on day one.

Why "just load the new stuff" is harder than it sounds

The naïve version of incremental loading is "load rows where the modified date is newer than last time." Sounds fine. Here's what it misses:

  • Updates, not just inserts. A row that already exists but changed also needs to come across. If you only grab new rows, your target slowly drifts out of date on everything that gets edited.
  • The boundary problem. "Newer than last time" needs a precise, reliable definition of "last time," stored somewhere durable, or you'll either reprocess rows or skip them at the seam between runs.
  • The interrupted run. If a load dies halfway, what does "last time" mean now? Get this wrong and a crash quietly creates a permanent gap.

So the real pattern has three parts: a watermark you can trust, a change detection step that handles inserts and updates, and a transaction so an interrupted run doesn't corrupt the watermark.

Part 1: a watermark you actually trust

A watermark is just "the high-water mark of what I've already loaded" — usually the maximum modified-timestamp (or a rowversion) you successfully processed. Store it in a small control table, one row per source object:

CREATE TABLE etl.LoadWatermark (
    ObjectName     sysname       NOT NULL PRIMARY KEY,
    LastValueUtc   datetime2     NOT NULL,
    UpdatedAtUtc   datetime2     NOT NULL DEFAULT sysutcdatetime()
);

The load reads this value, pulls everything in the source newer than it, and — crucially — only advances it after the load has fully succeeded. Which brings us to the rule that matters most.

Part 2: only move the watermark inside the transaction

Here's the mistake that bites people, and it bit me: they load the data, then update the watermark as a separate step. Then a load dies between those two steps — data loaded, watermark not moved — and the next run reprocesses. Or worse, they move the watermark first and the load dies after — watermark moved, data not loaded — and those rows are gone forever, silently, because nothing will ever look for them again.

The fix is to make the data load and the watermark update one atomic unit. Either both happen or neither does.

BEGIN TRAN;
    -- load changed rows into target ...
    UPDATE etl.LoadWatermark
       SET LastValueUtc = @newHighWater, UpdatedAtUtc = sysutcdatetime()
     WHERE ObjectName = @object;
COMMIT;

If anything fails, the whole thing rolls back and the watermark stays exactly where it was, so the next run simply picks up from the last known-good point. No gaps, no double-processing. The interrupted run becomes a non-event.

Part 3: handle updates, not just inserts

For the "changed rows, not just new rows" problem, the workhorse in SQL Server is MERGE (or, if you prefer more control, a staged upsert — load the changes into a staging table, then UPDATE the matches and INSERT the new ones). Conceptually:

  • Row exists in target and differs → update it.
  • Row doesn't exist in target → insert it.
  • Row exists and is identical → leave it alone (don't waste writes).

Staging the delta first, then applying it, also plays nicely with the transaction above — you pull changes into staging outside the transaction (the slow bit), then apply staging-to-target inside it (the fast, atomic bit). That keeps your transaction short, which your database will thank you for.

The honest trade-offs

Incremental loading isn't free, and pretending otherwise is how people get burned:

  • You now depend on the source's modified-timestamp being honest. If the source updates a row without touching its timestamp, your incremental load will never see the change. Know your sources. Some you can trust; some you can't, and those need a different strategy (or a periodic full reconcile).
  • Deletes are their own problem. A row deleted in the source won't appear in a "changed since" query at all, so it lingers in your target forever unless you handle deletes explicitly. Decide deliberately whether you care.
  • It's more moving parts. A full reload has almost nothing to go wrong. Every bit of incremental cleverness is a bit more to test and maintain.

Reconcile periodically, or drift will find you

One more habit, because it's saved me from a silent nightmare. Even a correct incremental load can drift over months — a source that quietly updated a row without touching its timestamp, a run that half-failed in a way you didn't catch. The gaps are invisible precisely because incremental loading only ever looks at "what changed," so anything it once missed, it will never look at again.

The cheap insurance is a periodic full reconcile: a weekend job that compares source and target — row counts per table at minimum, checksums if you want to be thorough — and flags any mismatch. It doesn't fix anything; it just tells you, before a stakeholder does, that table X is running forty rows light. Run it weekly and your worst-case drift is seven days old, rather than discovered by accident in a board report six months from now.

Don't reach for incremental loading because it's sophisticated. Reach for it when the full reload genuinely stops fitting — and then build it so an interrupted run can never lose a row.

My rule now: full reload until it hurts, then incremental, done properly, with the watermark moving only inside the transaction. Sophistication you don't need is just risk you signed up for. But when you do need it, the watermark-inside-the-transaction pattern is the thing that lets you sleep — because the worst a failed run can do is make you re-run it, never quietly lose your data.