Article 4 in the single-engineer data stack series. Article 1 swapped Spark for DuckDB. Article 2 swapped Pandas for Polars. Article 3 swapped full-refresh ETL for log-based CDC. This one answers the question that haunts all three: what happens when any of these pipelines fails halfway and retries?
The Retry Nobody Thinks About
Every pipeline fails eventually. Not might fail. Will fail. A network blip, a timeout, a worker that gets killed mid-batch. I have stopped being surprised by this. What surprised me for years was how rarely anyone asked the follow-up question.
The follow-up question is not whether your pipeline will fail. It is what happens the second time it runs.
Most engineers design for the happy path and let the orchestrator handle retries as an afterthought. Airflow retries the task. Dagster retries the task. dlt resumes from the last offset. All of that infrastructure assumes one thing without ever saying it out loud: that running the same logic twice on the same input produces the same result as running it once.
If that assumption is false, every retry is a small chance of corruption. Not a crash. Corruption. The kind that does not throw an error, does not page anyone, and sits quietly in a revenue table for months before someone notices the numbers do not add up.
This article is about making that assumption true. Not through better monitoring. Through pipeline design.
What Non-Idempotent Pipelines Actually Cost
I want to ground this in numbers before making any architectural claims, because vague warnings about "data quality" do not move anyone to action.
The diagram below summarizes three named incidents at very different scales.
A widely cited analytics case describes an e-commerce team whose tracking showed roughly 4,200 purchase events in a month against 2,100 actual transactions in their payment processor. A clean 2x inflation, running undetected for five months. The cause was ordinary: a browser pixel and a server-side conversion API both firing without a shared key to recognize duplicates, plus mobile clients retrying on flaky connections.
Andy Owens, VP of Analytics at Kargo, described a pipeline failure in Monte Carlo's "How Kargo Prevents Six-Figure Data Quality Issues." His words: "One of our main pipelines failed because an external partner didn't feed us the right information to process the data. That was a three day issue that became a $500,000 problem."
And then there is Citibank. On August 11, 2020, an operator intending to send Revlon's $7.8 million interest payment instead wired the full $900 million outstanding loan principal, roughly 100 times the intended amount. Ten investment firms kept more than $500 million of it under a legal defense a court initially upheld. This is not a CDC pipeline story. It is the cleanest illustration anywhere of why anything that touches money must be safe against accidental re-execution.
The pattern across all three: nobody designed for malice. Nobody made an obviously bad decision in the moment. The systems simply did exactly what they were told, more than once, and nothing stopped them.
The Property That Makes Retries Safe
Here is the concept that should have been taught to me on day one and was not. An operation is idempotent when applying it multiple times has the same effect as applying it once. In notation: f(f(x)) equals f(x).
I spent years treating this as an academic detail from API design, the reason PUT and DELETE are safe to retry but a naive POST /charges is not. It took me much longer to see that the exact same property is what separates a pipeline you can trust from one you have to babysit.
The architectural insight worth sitting with: true exactly-once delivery across a network is, for practical purposes, impossible to guarantee. Even Kafka's exactly-once machinery reduces, on the consuming side, to at-least-once delivery. A consumer can always reprocess after a crash or a rollback. The entire industry has converged on a different, achievable pattern instead: at-least-once delivery, paired with idempotent processing.
This reframes the whole problem. You stop trying to guarantee each event arrives exactly once, which is genuinely hard. You start guaranteeing that it does not matter if an event arrives twice, which is a property you can design into a single SQL statement.
The diagram below makes the contrast concrete.
This connects directly back to Article 3. PostgreSQL's own documentation states the deal plainly: the position of a replication slot persists only at checkpoint, so after a crash the slot can return to an earlier point in the log, and recent changes will be sent again. The database is telling you, explicitly, that it will redeliver. Your job is to make redelivery harmless.
Idempotent MERGE Patterns In DuckDB And dbt
The good news is that the pattern from Article 3 already does most of the work. The bad news is that "most" is not "all," and the missing part is where the bugs live.
DuckDB's MERGE INTO
The setup is the same as the rest of this series. If you have not already, uv add duckdb gets you everything you need, no separate installation step.
The keyed upsert from the CDC article is idempotent by construction, as long as two conditions hold. The match key has to be a stable identifier, the same logical row produces the same key every time. And the update has to be deterministic given the source row, no now(), no incrementing counters.
MERGE INTO dim_customers AS target USING staging_customers AS source ON target.customer_id = source.customer_id WHEN MATCHED THEN UPDATE SET status = source.status, updated_at = source.updated_at WHEN NOT MATCHED THEN INSERT (customer_id, status, updated_at) VALUES (source.customer_id, source.status, source.updated_at);
Re-run this with the same staging data and every matched row updates to values it already holds. Net change: zero. That is the property you want.
One thing worth knowing if you are running this against Iceberg tables, which v1.5.3 supports directly. DuckDB's own documentation notes that MERGE INTO on Iceberg uses merge-on-read semantics and writes positional deletes. A retry of an identical merge is logically idempotent, the queryable result converges correctly, but it is not physically idempotent. Every retry creates a new snapshot and new delete files, even when nothing actually changed. On a laptop-scale pipeline this is fine. At production scale, budget for table maintenance.
dbt's Incremental Strategies
dbt's own documentation is unusually candid about this: incremental models are the easiest place to accidentally break idempotence. Three strategies, two of them safe.
merge with a correct unique_key gives you an idempotent upsert. delete+insert with the same key does the same thing through a different mechanism. append, the default if you forget to specify a strategy, gives you neither. It inserts every row the model returns with no deduplication at all.
I have seen this exact mistake more than once: an incremental model with no unique_key set, working fine in testing because the pipeline never failed in testing, then duplicating every row in production the first time a retry happened. The fix costs one line of configuration. The bug costs a cleanup project.
Where The CDC Pipeline From Article 3 Breaks
This is the section I want to be precise about, because the pg_replication plus DuckDB MERGE INTO pattern from the previous article is mostly idempotent, and mostly is exactly the gap that bites people.
A CDC change event carries an operation type: insert, update, delete, or the initial snapshot read. The naive assumption is that applying each event through MERGE handles everything correctly. Three specific edge cases say otherwise.
The diagram below shows the most common failure: an out-of-order replay overwriting newer data with older data.
First, out-of-order delivery. If a consumer crashes and replays an older change after a newer one already landed, a blind merge overwrites correct data with stale data. The fix is a version guard on the merge condition itself.
MERGE INTO target t USING changes s ON t.id = s.id WHEN MATCHED AND s._lsn > t._lsn THEN UPDATE SET data = s.data, _lsn = s._lsn -- persist the log position so future replays are guarded WHEN NOT MATCHED THEN INSERT (id, data, _lsn) VALUES (s.id, s.data, s._lsn);
Second, duplicate or replayed delete events. A delete that gets re-applied after a row has already been re-inserted can wrongly remove a row that should exist. Deletes need the same version guard as updates, not a blanket "remove anything not present" rule unless you are working from a full snapshot.
Third, and this one surprised me when I first hit it: a crash mid-transaction causes the entire transaction to replay from its beginning, not just the unconfirmed part. Your sink has to tolerate seeing a whole transaction's worth of events twice, not just one stray row.
The practical fix for all three: a merge keyed on a stable identifier, with an explicit version or log-position guard on every write branch, including deletes. That single change is what turns "mostly idempotent" into actually idempotent.
Deduplication And Checkpoint Design
Stripe's idempotency key is the reference point everyone in this space eventually rediscovers independently. The mechanism: a client generates a key, sends it with a mutating request, and on any failure retries with the identical key. Stripe stores the result of the first request and returns it for any later request carrying the same key.
The data pipeline version of this is the same idea in different clothes. Your match key, whether a natural key or a content hash, plays the role of the idempotency key. Your checkpoint or offset store, the replication slot's log position from Article 3, plays the role of Stripe's key store.
Three deduplication patterns cover almost every case I encounter. ROW_NUMBER() partitioned by key and ordered by a timestamp, keeping only the latest row, works well as long as the ordering has no ties. Content hashing, computing a hash of the meaningful business columns, catches duplicates even when the source system reissues a row with a different envelope. Deterministic identifiers, UUIDv5 generated from a stable namespace and natural key rather than random UUIDv4, mean reimporting the same data produces the same IDs automatically.
The pattern I now default to in any pipeline touching money or counts that matter: hash the business key, guard every write with a version check, and test the retry before the bug ever has a chance to ship.
The Engineer Who Trusts Their Retries
There is a specific kind of confidence that comes from knowing a pipeline can fail safely. Not the confidence of believing it will not fail. The confidence of not caring much when it does.
This week, take the pipeline in your stack you trust the least, the one you check on manually after every run. Run it twice on the same input, deliberately. With uv, that is uv run pipeline.py twice in a row against the same source data. Compare the output. If anything differs, you have found exactly where the next incident is waiting.
I should have run that test years before I did. The fix, almost every time, was smaller than the anxiety the missing fix had been causing me.
DuckDB gave you a compute layer that does not need a cluster. Polars gave you a transformation layer that does not need pandas. CDC gave you an ingestion layer that does not need a nightly reload. Idempotency is what lets you trust all three when they fail, because they will. The engineer who designs for that failure in advance is not being pessimistic. They are the only one in the room not hoping nothing goes wrong.
Sources: Monte Carlo, "How Kargo Prevents Six-Figure Data Quality Issues," 2024. PostgreSQL documentation, "Logical Decoding Concepts," 2025. Stripe API documentation, "Idempotent Requests," 2025. DuckDB documentation, "MERGE INTO," 2025-2026. dbt documentation, "About Incremental Models," 2025.