When a Retry Duplicates Half Your Table: Idempotent Loads in Airflow
A pipeline fails near the end. Airflow retries it. The retry succeeds, the run turns green, and half the table is now in there twice.
Madhuri P. described this exact problem in a LinkedIn post: a job processed a million records, failed near the end, and created duplicate records on the retry. It’s the kind of failure that’s easy to describe and rarely demonstrated, so I turned it into a free, step-by-step tutorial on my Airflow 3 + Postgres starter repo. It reproduces the failure on a real stack and then fixes it two ways.
The word to know here is idempotency: a load that gives you the same result no matter how many times it runs. Retries are normal, so a pipeline has to be safe to run again.
Fix A: primary key + ON CONFLICT. Give the target table a primary key and turn the insert into an upsert. The guarantee lives in the table and in every write.
Fix B: staging + MERGE. Clear a staging table at the start of every attempt, load into it, then MERGE into the target in one statement. The guarantee lives in the shape of the pipeline.
Both fixes rest on one decision made before a single row is loaded: the target table needs a primary key, and it has to be a real business key (order_id, not an auto-increment). The key alone isn’t the fix. It’s what lets the database recognize a row it has already seen, so ON CONFLICT or MERGE can treat a retry as an update instead of a second copy. Without it, no retry logic can tell a duplicate from new data.
The tutorial runs on Airflow 3 and Postgres 17 with a one-command setup, or right in your browser through GitHub Codespaces. It takes about 45 minutes, with a checkpoint after every step.
Originally posted on LinkedIn.