Your data pipeline isn't idempotent until you prove it breaks and recovers

The "Exactly Once" myth is the most expensive fairy tale in data engineering. We tell ourselves that if we just buy a high-end orchestrator or use a specific streaming library, the system will magically handle retries without duplicating a single row. It won't. If you aren't designing for idempotency at the storage layer, you’re just waiting for a primary key violation to wake you up at 3 AM.

Why I chose this topic: I spent four years in healthcare fintech cleaning up "duplicate event" disasters caused by naive pipeline retries. I’m writing this because I’m tired of seeing engineers treat retry logic as an afterthought rather than a core requirement of every single data job.

It was 3:14 AM on a Tuesday. PagerDuty didn't just beep; it screamed. Our financial reconciliation pipeline, which processes millions of daily transactions, had failed. The dashboard showed a massive spike in 500 Internal Server Error responses from our downstream ledger API.

The Airflow UI was a sea of red. My first instinct—the one I’d been trained to do—was to hit "Clear" on the failed DAG tasks. I assumed the transient network blip had cleared, and hitting "Clear" would simply pick up where it left off. I hit the button. I went back to sleep. I was wrong.

What we saw

When I logged back in at 8:00 AM, the ledger was in total chaos. The reconciliation report showed a $12M discrepancy in our accounts payable. The logs were a mess of Duplicate Key exceptions.

We thought it was an API timeout issue. We spent three hours chasing the load balancer configuration, convinced that our max_retries setting of 3 was somehow causing a race condition in the API gateway. We checked the AWS X-Ray traces, we grepped the Nginx logs for upstream_response_time, and we even blamed the infrastructure team for a silent network partition.

Everything looked like a connectivity problem. Nothing looked like a logic problem. We were looking for a broken pipe, but the water was actually being pumped into the same bucket twice.

Photo by Henrique Ferreira on Unsplash

Root cause

The root cause was buried in a Python script that pushed processed batches to PostgreSQL. Our insert logic looked like this:

def load_data(batch):
    for record in batch:
        cursor.execute("INSERT INTO ledger_entries (tx_id, amount, status) VALUES (%s, %s, 'PENDING')", 
                       (record['id'], record['amount']))
    conn.commit()

The pipeline was configured with a retries: 3 and retry_delay: 300 in our Airflow task definition. When the network blip hit, the task failed mid-batch. But because the database connection didn't technically close immediately, some of those INSERT statements had already hit the wire and succeeded before the network interruption severed the connection.

When Airflow retried, it re-ran the entire task. It didn't know which records had already been inserted and which hadn't. It just blindly tried to insert the same tx_id values again. Our database had a PRIMARY KEY on tx_id, so the second attempt crashed. But on the third attempt, a different, partial set of records succeeded, creating a Swiss-cheese distribution of data in the production ledger.

We were relying on the "All or Nothing" promise of a transaction block, but our batching strategy was too large and our retry logic was too dumb.

Photo by Logan Voss on Unsplash

The fix

We stopped treating the entire batch as a single, fragile unit. Instead, we implemented a "Load-Stage-Merge" pattern using a staging table.

First, we changed the load process to move data into a temporary, un-logged staging table that shared the same schema as the production ledger.

-- Create temp table per execution
CREATE TEMP TABLE stage_ledger_entries (LIKE ledger_entries INCLUDING ALL) ON COMMIT DROP;

-- Bulk copy from S3 to stage
COPY stage_ledger_entries FROM 's3://bucket/data.csv' WITH (FORMAT csv);

-- Atomic UPSERT into production
INSERT INTO ledger_entries (tx_id, amount, status)
SELECT tx_id, amount, status FROM stage_ledger_entries
ON CONFLICT (tx_id) DO UPDATE SET
    status = EXCLUDED.status,
    amount = EXCLUDED.amount;

By using the ON CONFLICT clause (the UPSERT pattern), we made the operation inherently idempotent. Whether the job runs once, five times, or a hundred times, the final state of the ledger_entries table remains identical. If a task fails, we don't care how much data made it through before the crash. We just run the task again. The database handles the logic of ignoring duplicates or updating existing records.

What we changed so it never happens again

We stopped allowing "blind" retries. We now enforce a set of rules for every data pipeline we deploy.

First, we moved away from row-by-row inserts. Row-by-row is slow and makes partial failures almost impossible to untangle. We now use standard bulk loading tools like COPY (Postgres) or bcp (SQL Server), which are transactionally safer and significantly faster.

Second, we implemented "Versioned File Naming." Every output file in our S3 buckets is now suffixed with a hash of the content or a unique execution ID. If a pipeline runs, it writes to a specific path. If we retry, it writes to the same path, overwriting the previous partial attempt. This prevents the "junk data" problem where multiple failed runs litter your data lake with partial, corrupt files.

Third, we introduced an observability layer specifically for idempotency. We added a check in our CI/CD pipeline that scans for INSERT statements that lack an ON CONFLICT or a WHERE NOT EXISTS clause. If you’re writing an insert, you have to justify why it isn't idempotent. If you can’t, the build fails.

Finally, we adopted a "State-First" mindset. We treat our destination database as the source of truth for the job’s progress. Before any task starts, it queries the target table to see which tx_id values already exist. It then filters those out of the source batch. We basically turned the job into a self-pruning process.

You will have failures. The network will drop, the API will time out, and the power will flicker. Don’t build a system that needs human intervention to clean up the mess at 3 AM. Build a system that, when it wakes you up, allows you to say "just hit restart" and go back to sleep. If you can't restart your job without fear, you aren't doing engineering; you're doing crisis management.

Cover photo by Mark König on Unsplash.