Idempotency and Safe Backfills

An operation is idempotent when performing it many times produces the same final state as performing it once. Pipelines retry: an orchestrator re-runs a failed task, and a Kafka 129 consumer that crashes before committing its offset receives the same message again (Consumers and Queues). The simplest technique is a unique event ID plus a uniqueness constraint. This script delivers one event five times to a table with a key and one without:

idempotent.sh: one event, five deliveriesShell

psql() { docker exec -i l2-pg psql -U postgres -d booknest -X -q "$@"; }
psql -c "CREATE TABLE payments (event_id text PRIMARY KEY, order_id int, amount numeric(10,2))"
psql -c "CREATE TABLE payments_naive (event_id text, order_id int, amount numeric(10,2))"
for try in 1 2 3 4 5; do              # a consumer that crashed and retried four times
  psql -c "INSERT INTO payments_naive VALUES ('ABC123', 100001, 100.00)"
  psql -c "INSERT INTO payments VALUES ('ABC123', 100001, 100.00)
           ON CONFLICT (event_id) DO NOTHING" -c "\echo try $try: inserted :ROW_COUNT"
done
psql -c "SELECT (SELECT count(*) FROM payments) AS with_key,
                (SELECT count(*) FROM payments_naive) AS without_key"

Output

try 1: inserted 1
try 2: inserted 0
...
 with_key | without_key
----------+-------------
        1 |           5

DO UPDATE would make it an upsert. The ID must come from the producer; a SERIAL key makes every retry new.

A backfill re-runs a pipeline for past periods: days missed in an outage, or history reprocessed after a bug fix. It is safe only if each run is idempotent for its period. The usual pattern is partition overwrite: the run for day D deletes what it owns for D and inserts fresh rows in one transaction, as BookNest's load step does (BookNest's Daily Pipeline). Take the period from the run, never the clock: a task that computes "yesterday" itself loads the wrong day when it runs late; one given Airflow 129 's logical date (How a DAG Run Executes) does not.

MENU