Incremental load with a watermark in SQL

Keep the highest updated_at you have loaded so far (the watermark) in a small control table. Each run, read only the source rows changed since that watermark, merge them into the target on the business key, and move the watermark forward only after the merge succeeds.

Shape of the job

-- 1. read the last watermark
SELECT last_loaded_at FROM etl_control WHERE table_name = 'orders';

-- 2. pull changes with a small overlap, dedupe, and merge
MERGE INTO dw.orders t
USING (
  SELECT *
  FROM src.orders
  WHERE updated_at > :last_loaded_at - INTERVAL '10 minutes'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at >= t.updated_at THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

-- 3. only after success, advance the watermark
UPDATE etl_control
SET last_loaded_at = (SELECT MAX(updated_at) FROM src.orders WHERE ...)
WHERE table_name = 'orders';

Why each choice

• Overlap. A row can commit in the source with an updated_at slightly earlier than rows you already loaded, because transactions commit out of order. Re-reading the last few minutes catches those. The merge makes the re-read harmless.
• Dedupe the source so the merge never matches one target row twice.
• Advance the watermark last. If the merge fails halfway and the watermark already moved, you lose changes. If the watermark moves only after success, a failed run simply retries with the same window.
• Take the new watermark from the data you actually read, not from the current clock time, so rows that arrived while the job ran are not skipped.

What this approach cannot see

• Hard deletes. A row deleted at the source leaves no updated_at to find. You need CDC from the database log, a soft-delete flag, or a periodic comparison of keys between source and target.
• Rows updated without changing updated_at, for example by a bad script. The pipeline never sees them. Periodic reconciliation counts catch this.
• Many rows sharing the same timestamp at the watermark boundary. Using > can skip some, so the overlap or a >= with merge protects you.

Say that the job should be safe to rerun for any day, which is what makes backfills easy.