Why is a task that appends its window's rows unsafe to re-run, and what write pattern fixes it?

## Why appending breaks `INSERT INTO target SELECT ...` is an additive operation: it knows nothing about what previous attempts wrote. So the second execution of the same window — a retry, a cleared run, a replay of a missed interval, a deliberate backfill — leaves two copies of every row. The failure is quiet. No constraint fires, no task turns red, and the pipeline reports success. It surfaces days later as a metric that is exactly 2x for one day, or 3x for the day someone backfilled twice. The deeper problem is that an append makes the target a function of *how many times the pipeline ran*, which is an operational accident, rather than a function of the input data. Everything downstream inherits that. ## Pattern 1: delete-then-insert for the window The task declares the slice it owns — usually one partition, one day, one interval — deletes exactly that slice, and inserts the recomputed rows. ```sql BEGIN; DELETE FROM sales_daily WHERE dt >= :interval_start AND dt < :interval_end; INSERT INTO sales_daily (order_id, amount, dt) SELECT order_id, amount, order_ts::date FROM raw_orders WHERE order_ts >= :interval_start AND order_ts < :interval_end; COMMIT; ``` Two details make or break this. First, **atomicity**: if the delete commits and the process dies before the insert, the window is now empty and readers see a hole. Wrap both in one transaction, or use an engine operation that replaces a partition in a single step. Second, **ownership**: the delete predicate must cover exactly the rows this task produces and no others. If a second pipeline also writes into `sales_daily` for the same days, each run silently deletes the other's output. A third hazard is worth calling out because it bites during incidents: if the source query now returns zero rows — a broken upstream, a wrong credential, a filter typo — the delete still runs and you have replaced good data with nothing. Guarding the swap on a plausibility check (row count is non-zero, or within a sane band of the previous run) turns a silent wipe into a failed task. ## Pattern 2: merge / upsert on a natural key ```sql MERGE INTO sales_daily t USING (SELECT order_id, amount, order_ts::date AS dt FROM raw_orders WHERE order_ts >= :interval_start AND order_ts < :interval_end) s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET amount = s.amount, dt = s.dt WHEN NOT MATCHED THEN INSERT (order_id, amount, dt) VALUES (s.order_id, s.amount, s.dt); ``` Re-running updates rows in place, so replay converges instead of accumulating. Merge is the right choice when rows can arrive late or out of order, when several producers legitimately write to the same table, or when a row's own attributes change after first load. Its requirements are stricter: you need a key that is stable and genuinely unique in the source. Deriving that key from a mutable field, or from a load-time sequence, reintroduces duplicates through the back door. Merge is also the more expensive write on most engines because it must match against existing data rather than blindly replacing a slice. Note what merge does *not* fix: rows that were in the target from a previous run but are absent from the new source result — a cancelled order, a hard delete upstream — stay behind. If the source is the authority on the whole window, delete-then-insert is more truthful than merge. ## Pattern 3: write-new-then-swap On object storage and in lakehouse table formats the idiomatic move is to write the window's output to a fresh location and then flip a pointer — register the new partition, or commit a new table snapshot — in one metadata operation. Readers see either the old data or the new data, never a half-written mix, and rollback is a pointer flip back. This is the same replace semantics as pattern 1 with a better failure story, and it is what large backfills should use. ## Choosing between them Ask who owns the slice. If this task is the sole producer of a bounded partition and the source can regenerate the whole partition, overwrite it — simplest, cheapest, easiest to reason about during an incident. If rows trickle in over time, or several jobs contribute, or the grain is a business entity rather than a time slice, merge on the key. If your storage layer offers atomic partition or snapshot swaps, prefer that mechanism over hand-rolled delete-and-insert, because it removes the half-written window entirely. ## The pattern to refuse "Append everything and deduplicate downstream" shows up constantly and should be pushed back on. It moves correctness into a separate job that runs later, so the window between load and dedupe serves wrong numbers; the dedupe needs the very key you claimed you did not have; and storage grows with the number of retries. Deduplication as a *defensive* backstop is fine. Deduplication as the primary mechanism for idempotence is a bug that has not fired yet.