Incremental Loading Strategies in SQL (Watermarks, MERGE)

Full load vs incremental load

A full load re-reads and reprocesses the entire source table on every run — simple and always correct, but increasingly expensive as the table grows. An incremental load reads only rows that are new or changed since the last successful run, keeping runtime roughly proportional to how much actually changed rather than to total table size.

The watermark pattern

CREATE TABLE pipeline_watermarks (
  pipeline_name  VARCHAR(100) PRIMARY KEY,
  last_watermark TIMESTAMP
);

-- Read the last successful watermark before pulling new data
SELECT last_watermark FROM pipeline_watermarks WHERE pipeline_name = 'orders_incremental';

-- Pull only rows strictly newer than that watermark
SELECT * FROM orders
WHERE updated_at > '2026-08-09 23:00:00';  -- the watermark just read

-- After a successful load, advance the watermark to the max value just processed
UPDATE pipeline_watermarks
SET last_watermark = '2026-08-10 00:15:00'  -- max(updated_at) from this batch
WHERE pipeline_name = 'orders_incremental';

Storing the watermark in a durable table rather than a script variable means it survives across pipeline runs and can be inspected or manually reset if something needs to be reprocessed.

MERGE-based idempotent loading

MERGE INTO target_orders AS t
USING incremental_batch AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET
  t.amount = s.amount, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, amount, updated_at)
  VALUES (s.order_id, s.amount, s.updated_at);
-- Running this exact statement twice on the same batch produces the same end state

A plain INSERT has no concept of "this row might already exist" — re-running an incremental batch after a retry would create duplicates. MERGE checks for an existing match first, making the entire load idempotent: safe to re-run without corrupting the target table.

Handling late-arriving data

A row can be written to the source system after the pipeline has already moved its watermark past that point in time — a sale backdated to yesterday but entered into the system today, for instance. A watermark based purely on a business date can permanently miss it, since the pipeline never re-checks a date range it already considers "done":

-- Instead of pulling strictly newer than the watermark, re-check a trailing buffer
-- to catch rows that arrived late but belong to an already-processed period
SELECT *
FROM orders
WHERE updated_at > DATEADD(DAY, -3, '2026-08-09 23:00:00');  -- 3-day lookback window

Re-processing a small trailing window on every run costs some redundant work, but the MERGE pattern above makes that redundancy harmless — rows already loaded simply update to their same values again.

When full reload is still the right call

• Small dimension tables where the entire table costs almost nothing to reprocess — the complexity of incremental logic isn't worth it.
• Sources with no reliable watermark column — no trustworthy updated_at, no append-only guarantee.
• After a transformation logic change significant enough that historical data needs recalculating from scratch, not incrementally patched forward.

Common mistakes

• Trusting an updated_at column an application doesn't reliably set on every write path.
• Using strict > instead of considering boundary edge cases around the exact watermark timestamp, risking a row processed twice or missed entirely at the boundary.
• Advancing the watermark before confirming the load succeeded, so a partial failure leaves the pipeline believing data landed that never actually did.
• Ignoring late-arriving data entirely with no lookback window, silently losing legitimately late but valid records.