How do you replay quarantined records after a fix without double-loading rows that already landed?

## Replay is an ingestion run, not a rescue script The first decision is architectural. A replay that goes through a hand-written script written during the incident tests the script, not the fix, and it usually bypasses the very validations the pipeline exists to apply. The right shape is to make the ingestion job accept an alternative input source — the quarantined payloads — and otherwise behave exactly as it does for fresh data: same parsing, same validation, same write, same metrics, tagged with a `replay_run_id` so its output is attributable. If a replayed record fails again, it goes back to quarantine through the normal path with an incremented attempt count. ## Idempotency: merge on the business key An `INSERT` of replayed rows duplicates anything that arrived by another route in the meantime — a later batch, a manual correction, a full refresh. The write must therefore be keyed: a `MERGE`/upsert on the natural or business key, so replaying the same record twice converges on one row. Where the target is append-only by design (an events table, a raw landing zone), idempotency has to come from a deduplication key stored with the row — the source locator or a content hash — so a re-run can be filtered rather than deduplicated after the fact. ## The staleness guard, which is the part people forget Quarantined records are, by definition, old. Suppose an order row was rejected on Monday for a bad currency code, and on Wednesday the source sent a corrected version that loaded cleanly. Replaying Monday's record on Thursday with a plain upsert overwrites Wednesday's good data with Monday's bad shape. The replay has now caused a worse incident than the original rejection. The guard is to make the update conditional on the incoming record being newer than the stored one, using the record's own **source event timestamp** or **version**, not the ingestion time: `WHEN MATCHED AND src.source_updated_at > tgt.source_updated_at THEN UPDATE`. For change-capture-style sources, the natural version is the source's own sequence or commit position. Where no version exists at all, the honest alternative is not to replay the payload but to **re-extract the current state of that key from the source**, which sidesteps the ordering question entirely — and is often the better answer for small reject sets. ## Selecting the right rows A blind "replay everything in quarantine" re-runs failures that nothing has fixed, burning attempts and producing noise that hides the records that would now succeed. Select deliberately: - **By reason code** — replay only `date_unparseable` because that is what the deploy fixed. - **By time window or run id** — only the runs affected by the defect. - **By status** — only records a human moved to `pending_replay`, never `discarded` ones. This is why the reason code needs to be a stable identifier rather than an exception string: the replay predicate is a `WHERE` clause on it. ## Lifecycle transitions that survive concurrency Two engineers triaging the same incident can both launch a replay. Claim the rows with a conditional update — set `status = 'replaying'` and the `replay_run_id` only `WHERE status = 'pending_replay'` — and process only the rows the update actually claimed. On success move them to `replayed`; on repeated failure back to `new` with `attempt_count + 1`, and stop retrying a record past a cap so it is triaged by a human instead of cycling. Keeping the record rather than deleting it preserves the audit trail: you can later prove which rows entered the target by replay and when. ## Verify, then tell downstream After a replay, reconcile: the count of records marked `replayed` should equal the change in target row count plus the number that matched and updated existing rows. If the numbers do not add up, something was filtered by the staleness guard — that is fine, but it should be *reported*, not discovered later. Then handle the derived layer. Aggregates, incremental models and extracts built from the target on Tuesday do not know that Tuesday's data changed on Thursday. A replay that lands rows into base tables without triggering a refresh of the affected partitions leaves every downstream number stale, which is a subtler version of the original problem. If downstream models are incremental with a watermark on load time, a replay of old business dates may be invisible to them entirely — that is the case where the model needs a full refresh of the affected range, not another incremental run. ## What good looks like Replay is a first-class, repeatable operation: scoped by reason, run through the real pipeline, written idempotently with a version guard, tracked by status transitions that tolerate concurrency, reconciled afterwards, and followed by a targeted downstream refresh.