How do you make a re-run of an incremental batch load land the same window without duplicates?

## Why replays are not optional A batch load will be re-run. The orchestrator retries after a timeout, an operator reruns a task that failed at the last step, a lookback window deliberately re-reads yesterday, or a backfill overlaps the nightly job. The question is never whether the same window is applied twice, only whether the second application changes anything. The nastiest version is the retry after an ambiguous failure: the load committed, the acknowledgement was lost, the orchestrator marks the task failed and runs it again. Nothing is broken and nothing errors — the fact table just quietly holds every affected row twice, and someone notices weeks later when a total is wrong. ## Requirement one: a fixed window A run that computes its own bounds from the current time is not replayable, because "since the last watermark, up to now" is a different set of rows every time you evaluate it. Give the run explicit half-open bounds — `updated_at >= :lo AND updated_at < :hi` — supplied as parameters and recorded in the run log. Now the run is a function of its parameters. Replaying it means running it with the same `lo` and `hi`, which reads the same rows, which lets the write be a no-op. This also makes backfills and window replays trivially expressible: the same job, different bounds. Any design where "rerun yesterday" requires editing the watermark table by hand is a design that will be got wrong under pressure. ## Requirement two: a keyed write There are exactly two idempotent shapes for applying a slice. **Upsert on identity.** `MERGE INTO target USING staging ON target.pk = staging.pk`, updating when matched and inserting when not. Applying the same slice twice sets the same columns to the same values. This is the general answer and works whether or not the target is partitioned. **Replace the partition.** Delete every target row in the window and insert the slice, in one transaction — or, on formats that support it, overwrite the partition atomically. This suits append-shaped data with a natural time partition (events, log-like facts) where per-row upserting is expensive. The critical detail is that the delete predicate must exactly match the window the insert covers, or you will delete rows you are not about to reinsert. What is never idempotent is `INSERT INTO target SELECT ... FROM staging`. Not with a unique index — that turns duplication into a hard failure, which is better than silence but still means every retry needs manual intervention. Not with `INSERT IGNORE`-style suppression either, which silently drops legitimate updates to existing keys. ## The third requirement people forget: dedupe within the slice A lookback window or a wide backfill chunk often contains several versions of the same key — a row updated three times inside the window arrives three times. Most engines' `MERGE` is undefined or errors when the source matches one target row more than once, and even where it succeeds you may not get the version you wanted. Collapse the slice to one row per key before merging, keeping the newest by the change column: ```sql SELECT * FROM ( SELECT s.*, row_number() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn FROM stg_orders_slice s ) WHERE rn = 1; ``` Where ties on the change column are possible, add a deterministic tiebreaker so two runs over the same data pick the same winner — otherwise the load is idempotent in row count but not in content. ## Guard against out-of-order application If windows can be applied out of order — a replayed old window running after a newer one has already landed — an unconditional upsert will overwrite a newer row with an older version. Make the update conditional: only overwrite when the incoming change value is greater than or equal to the one already stored. That turns the load from merely idempotent into order-insensitive, which is what a backfill running alongside a nightly job actually needs. ## Staging is part of the pattern Write the extracted slice to a staging table or a landing path named for the window before touching the target. It gives you a place to dedupe, a place to run row-count and null checks before publishing, and a clean truncate-and-refill on retry. Staging tables should be per-window or truncated at the start of the run, never appended to across runs, or the staging table itself becomes the thing that duplicates. ## How to verify you actually got it right Do not assert idempotency, test it. In a test environment, run the same window twice and assert that the target's row count, checksums and a per-key hash are unchanged by the second run. Add a scheduled duplicate check on the natural key of critical tables in production. Most teams that believe their loads are idempotent have never run the load twice on purpose.