Idempotency is most of the jobIdempotency is most of the job

A pipeline will be run again. This is inevitable. The network drops halfway through, someone patches a bug and backfills a week, an operator is not sure the job finished and triggers it again.

The only real question is what happens the second time. If re-running is safe and boring, most operational problems become small. If it is not, every failure turns into an investigation about what state the data is in.

Append is the trap

The simplest load is an insert, and inserts are not idempotent. Run yesterday's file twice and yesterday exists twice. Nothing errors. The row count looks plausible. Revenue is quietly double for one day, and it is found weeks later by someone who remembers that Tuesday was not that good.

The fix is to make the load target a state rather than an event: this key should end up with these values. Whether that is a merge, an upsert, or delete-then-insert for a bounded partition matters less than the property: running it twice leaves the same result as running it once.

You need a key that means something

Merging requires knowing what makes a row the same row. A surrogate id generated at load time cannot do that; it is different on every run by definition.

So you need a business key, such as an order number, a transaction reference, a composite of source system plus source id. If the source genuinely has no stable key, that is worth surfacing early, because every deduplication strategy downstream will be guesswork built on it.

If you cannot say what makes two rows the same row, you cannot make the load repeatable. Everything after that is hope.

Partitions make re-runs cheap

Idempotency and incremental loading meet here. If the data is partitioned by day and a load owns its partition completely, re-running a day is trivially safe: replace the partition. No merge logic, no key matching, no ambiguity.

That is often the simplest correct design, and it is worth reaching for before anything cleverer. It fails when records move between partitions after the fact, such as a late correction to an old order. This is is precisely when you do need the merge.

The operational payoff

When re-running is safe, the recovery procedure for almost everything becomes the same: run it again. Backfills stop being special. A failed job at 3am can wait until morning. A nervous operator clicking twice is not an incident.

That is why it is worth spending design time on early. Not because it is elegant, but because it converts a whole category of 3am problems into a category of ordinary ones.