What Is Idempotent Ingestion? – Definition and How It Works

Informational P3 what is idempotent ingestion

Learn how idempotent ingestion makes a pipeline safely re-runnable, using natural keys, run scoping, and upsert semantics.

Idempotent ingestion means running the same load twice produces the same result as running it once. It is what makes a retry safe, and therefore what makes an unattended pipeline operable.

1. Why It Is Not Optional

Every pipeline fails partway through: a network drop at record 8,000 of 10,000, a container killed mid-write, a timeout during the load.

Without idempotency, restarting duplicates 8,000 records and resuming from an unknown position risks losing some. Both outcomes are worse than the original failure. The whole point is that recovery becomes "run it again" rather than an investigation.

2. Every Record Needs a Natural Key

A key derived from the data, not from insertion order or a generated sequence.

Data	Natural key
Product listing	Source URL, or site plus product ID
Search result	Query, engine, position, run date
Business listing	Source ID, or normalised name plus phone
Time series point	Entity plus timestamp
Document	Canonical URL plus content hash

Where no natural key exists, a stable hash of the identifying fields serves — but it must be computed from fields that do not change between runs. A hash including a timestamp or a page position is not stable, and is the usual reason a supposedly idempotent load duplicates.

3. Upsert Instead of Insert

INSERT INTO listings (source_url, title, price_cents, fetched_at)
VALUES (%s, %s, %s, %s)
ON CONFLICT (source_url) DO UPDATE
SET title = excluded.title,
    price_cents = excluded.price_cents,
    fetched_at = excluded.fetched_at
WHERE excluded.fetched_at > listings.fetched_at;

The WHERE clause is what makes out-of-order execution harmless: a replayed older record cannot overwrite a newer one. Without it, re-running yesterday's load overwrites today's data — technically idempotent, and wrong.

4. Run Scoping for Time-Series Loads

Where each run is a distinct observation, the key must include the run:

UNIQUE (query, engine, run_date, position)

Then re-running the same day replaces that day's rows and leaves history intact. Deleting the run's rows before loading, inside one transaction, gives clean replace semantics:

BEGIN;
DELETE FROM serp_results WHERE run_date = %s AND engine = %s;
INSERT INTO serp_results (...) SELECT ... FROM staging;
COMMIT;

5. Idempotency Across the Whole Pipeline

Every stage, not just the database write:

Fetch — a URL already fetched for this run is skipped, using a seen-set keyed on the run.

Store raw — write to a deterministic path, so a rewrite replaces rather than accumulates:

raw/2026-08-18/example.com/<sha256-of-url>.html.gz

Hashing the URL rather than using a counter is the detail that makes the path deterministic across runs.

Transform — a pure function of its input, with no dependence on the current time or on a random value.

Publish — an atomic swap or an upsert, never an append.

A pipeline is only as idempotent as its least idempotent stage.

6. What Breaks It

Auto-increment identifiers as identity, so the same record gets a new ID and appears new.
Timestamps inside the key, making every run's records distinct.
Append-only writes to the final table.
Non-deterministic output paths, including anything with a random component.
Side effects outside the transaction — a notification sent per run, a counter incremented, a file appended.
Sequence-based ordering where the source's order is not stable between fetches.

7. Testing It

The test is one line of intent and worth automating: run the load twice against a fixed input and assert that the row count, the checksum of the table, and the record contents are identical.

Anything that differs between the two runs is a source of non-idempotency, and running the test in CI catches it before a 3 a.m. retry does.

8. Summary

Idempotent ingestion makes a repeated load produce the same result as a single load, which turns recovery from an investigation into a re-run.

It needs a natural key computed from stable fields, upserts with a freshness guard so replays cannot overwrite newer data, run scoping for time-series observations, deterministic paths for raw storage, and a doubled-run test to prove the property holds.