Making an Ingestion Pipeline Retry-Safe: A Walkthrough With Idempotency Keys

The consumer received the event, wrote a row, and was in the middle of sending its acknowledgment when the connection dropped. From the sender's point of view, the request failed, so it retried on schedule. From the pipeline's point of view, it had already done the work; it just never got to say so. The result is one real event represented as two rows, and nothing in the pipeline itself noticed anything went wrong, because both writes succeeded.

A tempting patch is a nightly job that looks for rows with matching content and removes the duplicates. It's fragile in the exact way that matters: it depends on defining 'matching' correctly for every event type, it runs after the duplicate has already been counted in anything that reads the table during the day, and it silently breaks the moment two genuinely different events happen to look similar under whatever matching rule was chosen. It treats the symptom, not the cause, and it does so on a delay.

Every event needs a stable identifier that's the same across retries: an event ID assigned by the sender, or if the sender doesn't provide one, a deterministic hash of the payload's immutable fields. The write itself changes from a plain insert to an upsert keyed on that identifier (ON CONFLICT DO NOTHING, or DO UPDATE if a retry might carry corrected data). The second attempt at writing the same event either does nothing or safely overwrites with the same result, instead of creating a second row. The database, not a nightly cleanup job, is what enforces uniqueness, and it enforces it at write time, not after the fact.

To make the write itself retry-safe, follow these steps:

1. Give every event a stable identifier that stays the same across retries, using the sender's event ID when one exists.
2. If the sender provides none, derive a deterministic hash from the payload's immutable fields, excluding anything that changes per attempt.
3. Change the write from a plain insert to an upsert keyed on that identifier, so a retry can't create a second row.
4. Choose DO NOTHING when retries carry identical data, and DO UPDATE when a retry may carry corrected data that should win.

Two things undermine this pattern even after it's implemented. First, a key that's generated at send time rather than derived from the payload's own content isn't actually stable across retries if the sender regenerates it on each attempt; check the sender's documentation for whether the ID it provides is guaranteed constant across retries of the same logical event, not just unique per request. Second, a check-then-insert pattern (look up whether the key exists, then insert if not) has a race condition under concurrent processing that a single upsert statement doesn't, because two workers can both pass the check before either one writes. The upsert needs to be one atomic database operation, not two separate steps in application code.

The actual test is simple and almost never written down: take a real batch, replay it twice through the pipeline in a staging environment, and assert the row count after the second run equals the row count after the first. If it doesn't, the pipeline isn't idempotent yet regardless of what the code looks like. This is worth adding as a standing integration test, not a one-time manual check, because a future change to the write path can silently reintroduce the plain-insert bug the upsert was meant to prevent.

The first write isn't the only place duplication can sneak back in. A downstream job that aggregates the ingestion table into a daily rollup, or a consumer that reads from the table and pushes to a third system, needs its own idempotency story if it can also be retried or rerun, since a clean upstream table doesn't automatically protect anything built on top of it. Treat every stage that writes as a stage that needs a stable key and a safe re-run behavior, not just the first one in the chain.