Making Your Data Pipeline Safe to Rerun

Retries, redelivered messages, and reruns after a partial failure happen constantly in any real pipeline, whether you planned for them or not. If your writes aren't idempotent, every one of those normal events is a chance to duplicate or corrupt data. Treating idempotency as a nice-to-have instead of a correctness requirement is how a routine retry turns into a data quality incident.

Give every record a deterministic key derived from the source data itself, a source record id combined with an event type or version, rather than relying on an auto-generated id assigned at write time. Write with an upsert against that key instead of a plain insert, so replaying the same batch twice produces the same end state instead of two copies of every row.

Raw ingestion is the easy part. Aggregation steps are where idempotency quietly breaks: a naive "add this batch's total to the running total" step double counts on rerun even if the raw rows underneath it are perfectly deduplicated, because the increment itself isn't idempotent. The fix is recomputing the aggregate from the already-idempotent raw table on every run, rather than incrementing a running number.

Consider an order-events pipeline. Raw events land in a staging table keyed by order id, event type, and event timestamp, written with an upsert, so a redelivered event overwrites itself instead of duplicating. A daily revenue rollup is then recomputed from that staging table on every run, rather than incremented from the previous day's total. If the job fails halfway through and gets rerun, the staging table ends up in the same state either way, and the rollup recomputed from it produces the identical number, not a doubled one.

• Every write step uses a deterministic key derived from source data, not an auto-generated one assigned at write time.
• Every aggregate is recomputed from idempotent raw data on each run, not incremented from a previous total.
• A partial failure halfway through a batch doesn't leave the destination table in a state a rerun can't reconcile cleanly.
• You've actually tested a rerun after a deliberate partial failure, not just assumed the design will hold up.

A record that arrives a day late, common when an upstream system retries its own failed delivery, is a special case of the same idempotency problem: your pipeline needs to accept it into the correct place without disturbing everything computed since. An upsert keyed on the record's own stable identity handles this the same way it handles a straightforward rerun, since the pipeline doesn't actually need to know the record arrived late, only that it needs to land in the same place it would have on time.

Aggregates that were already computed for the affected period still need to be recomputed once the late record lands, which is exactly why the recompute-from-raw-data pattern matters more than an incremental one: a recompute naturally picks up the late arrival, while an incremental running total has already moved on and has no mechanism to go back and correct itself.

Most teams test a pipeline's happy path and stop, so a rerun scenario is never actually exercised until it happens for real, usually during an incident. Add a test that runs the pipeline once, kills it partway through a second run of the same input, reruns it again, and asserts the final output matches a single clean run byte for byte, not just approximately.

Run that same test after any schema change to the destination table, not only when the pipeline logic itself changes. A new column or a changed constraint can quietly break the upsert key's uniqueness guarantee even when the pipeline code hasn't been touched at all.