Idempotent ingestion: running the same file twice must change nothing

An ingestion job is idempotent when processing the same input twice leaves the store in the same state as processing it once. Run the file 5 times, get the state you would have had after 1. That decides whether a failed run can simply be run again.

It is also a property you can test in an afternoon rather than argue about. Ingest a sample, record the row count, the distinct-key count and a SHA-256 checksum over the stored content, then ingest the identical input again and compare all 3. Anything that moved is a defect.

Three keys that look stable and are not

Idempotence is normally implemented as an upsert, and an upsert is only as good as the key it matches on. Three candidates get chosen constantly because they are always available, and each fails on a different ordinary event.

A fourth candidate deserves a warning because it looks sophisticated. A hash of extracted text is stable against renames and re-exports and unstable against your own code: upgrade the extractor or fix a UTF-8 bug and every key changes at once. Hashing raw bytes avoids that, but many export tools stamp a generation time into the file, so byte-identical content is never delivered twice.

Building a key when the source will not give you one

The order of preference is short, and worth working through in order.

1. A source-issued identifier, if one exists. A record id, a document number, a claim or invoice reference. It is stable because the source treats it as identity.
2. A composite of stable content coordinates. Issuer plus document number plus period, or account plus statement date. Write the rule down: the composite must be derivable from every future export.
3. A hash of the raw bytes, accepting its limits. Usable where the source really does emit identical files and a rewrite of an unchanged document producing a new key is tolerable.
4. A resolved identity, if nothing above works. You are then matching records rather than keying them — see what record linkage can and cannot decide first.

What the property is worth on the day something fails

Idempotence is invisible while everything works, which is why it gets skipped. Its value appears in four situations, and in each the alternative is manual work under time pressure.

• Replay after a partial failure. A run that died two-thirds through can simply be run again. Without it, somebody works out which of 30,000 records were written and builds a partial input.
• Reprocessing history. Every kind of backfill assumes it, because a backfill overlaps existing records by design — the operations separated in the four jobs the word backfill hides.
• Running a rebuild beside the live pipeline. A periodic full pass is the only way to catch drift from incremental updates, and it is safe only if rewriting an existing record is harmless — rebuilding the whole index against updating only what changed.
• Retries at the message level. At-least-once delivery is the norm, so a consumer sees some messages twice whatever you do.

Two limits are worth stating plainly. Idempotence guarantees one input processed twice is harmless; it says nothing about 2 sources describing the same entity, which is the precedence question in the golden record in a messy source set. And delete-then-insert satisfies the property while leaving a window where the record is absent — a user asking then gets nothing rather than something slightly old.

It is a good question to ask whoever builds the pipeline: the property is invisible in a demonstration and expensive to retrofit once a corpus is loaded. "Show me the store before and after the same file is ingested twice" takes 10 minutes and separates a working pipeline from one nobody has run twice — worth including in commissioning a build team against hiring in house and in any product build. Both sit in data readiness and pipelines, part of the engineering library.

• idempotence
• ingestion
• definitions
• data quality