Run It Twice, Count It Once: Building Idempotent Data Pipelines

Sooner or later every data pipeline runs twice. A job times out and the scheduler retries it. Someone imports last week’s file again by mistake. A parser is fixed and re-run over old input. A worker takes a task, goes slow, and a second worker takes it too. If these repeats add duplicate rows, totals double, charts spike, and nobody can say which numbers to trust. The defence is a property called idempotency: doing something twice leaves the data exactly as doing it once (Idempotency). It is the cheapest reliability measure there is, and the one most often left until after an incident.

Why repeats are normal

Many queues and schedulers promise that every task will be delivered at least once (At-least-once Delivery). That promise is made deliberately: if a worker’s acknowledgement is lost, the safe choice is to deliver again rather than risk losing the task. The price is that tasks sometimes run twice, and the receiving side must cope. “Exactly once” does not exist as a network guarantee; what exists is at least once plus idempotent processing, which has the same effect.

The key idea: a stable record key

Idempotent writing depends on being able to tell “the same record again” from “a different record”. Give every record a key that is derived from what the record is, not from when or how often it was written. A key built from the source, the batch or request, the entity, the metric, the dimensions and the time of the observation is stable across retries. A random ID generated at write time is not: each retry gets a new one and looks like a new record. A timestamp of the write is not either.

Then let the database enforce it, not the application’s good intentions:

CREATE TABLE observation (
  record_key   TEXT    NOT NULL,   -- derived from source, entity, metric, dimensions, observed_at
  revision     INTEGER NOT NULL DEFAULT 1,
  value        NUMERIC,
  observed_at  TIMESTAMPTZ NOT NULL,
  loaded_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (record_key, revision)
);

INSERT INTO observation (record_key, revision, value, observed_at)
VALUES ('shop-a|item-7|price|2026-10-15T06:00Z', 1, 19.99, '2026-10-15T06:00Z')
ON CONFLICT (record_key, revision) DO NOTHING;

Run that insert a hundred times and the table holds one row.

Repeat, re-observation, correction: three different things

Not every duplicate-looking input is a duplicate. A pipeline has to distinguish three situations, and they get three different treatments:

Situation	Example	What should happen
A repeat of the same observation	A retry of the same request; the same file imported twice	Ignore: nothing is added
A new observation that happens to have the same value	The price fetched on Tuesday is 19.99, as it was on Monday	Keep: it is a new observation at a new time. Never deduplicate by value
A correction of an earlier observation	The source revises Monday’s figure	Add a new revision that points at the old one; keep both (Append-only Ledger)

The common mistake is the second row. Deduplicating “rows with the same value” deletes the evidence that the price was checked and unchanged, which is itself information, and it flattens a time series into its changes only.

Imports and file loads

A file imported twice should not double the data. Two things help:

Record each import. Store the file’s content hash (Cryptographic Hash) and the time it was loaded; refuse or skip a file whose hash is already recorded, or at least make its rows collapse into existing keys.
Take times from the data, not from the clock. The observed time comes from the file; the loaded time is when you loaded it. Using the load time as the observation time makes a re-import look like a new observation.

Re-running a parser

When a parser improves and you re-run it over stored raw data, the results may differ from before. That is a correction, not a repeat: write new revisions, record the parser version, and leave the earlier values in place. Downstream readers can then choose “latest” or “as it was known on date D” (Point-in-Time).

Idempotent does not mean “always skip”

A replacement is also idempotent: “set the balance to 100” can be applied any number of times. Where the right outcome of a repeat is “replace with the same content” (a derived table rebuilt from raw data, for example), delete-and-reload inside a single transaction is fine. What is not idempotent is adding to what is there: “append these rows”, “add 100”.

Test it

Idempotency is easy to claim and easy to break. Test it directly:

Run the job on a sample, record the row counts and a checksum of the result.
Run it again, unchanged. Counts and checksum must be identical.
Run it with the same input in a different order, or split across two files. Same result.
Kill it halfway and run it again. Same result as an uninterrupted run.
Change the parser version and re-run. New revisions appear; old rows stay.

Put (2) and (4) into the automated tests. They catch the most common regressions.

A short checklist

Every record has a key derived from its content and context, never from the write.
The database enforces uniqueness on the key (and revision).
Repeats are ignored; new observations with equal values are kept; corrections become revisions.
Imports are recorded by content hash; times come from the data.
Re-runs of parsers write revisions rather than overwriting.
Tests run each job twice and after an interruption, and compare results.