Data Pipeline Idempotence: The Night a Backfill Doubled Our Revenue

The context

A nightly load job writes the previous day's orders into a fact table each day, later consumed by the finance team's revenue dashboards. One morning, the dashboard shows revenue nearly doubled over the last three weeks. Nothing had changed in the business logic. The culprit: an Airflow retry, the night before, on a task that wasn't idempotent.

What happened

The task did a simple INSERT from the source into the fact table, with no notion of "this range has already been loaded." That night:

1. The task starts, begins writing the day's order rows.
2. A few-second network outage between the worker and the warehouse interrupts the write. About 60% of the rows are already committed on the database side.
3. Airflow marks the task as failed and automatically retries it (retry configured for 2 attempts, standard behavior).
4. The retry starts from scratch and reinserts the entirety of the day's orders, including the 60% already present.

The problem stayed invisible for three weeks because, from its own point of view, the retry logged nothing abnormal. It had just done its job. It was the finance team that eventually noticed the discrepancy, not our monitoring.

Why idempotence is harder than it looks

The natural instinct is to want to guarantee "exactly-once" execution: every task runs exactly once, never more. In practice, in a distributed system with an orchestrator, workers, a network, and a database, exactly-once execution doesn't exist: network outages, OOM kills, scheduler restarts, and manual retries are inevitable.

What you can actually guarantee is at-least-once execution, combined with idempotent writes: the task can be replayed an arbitrary number of times, and the final result in the database must be identical to a single execution. It's a shift in posture: you stop trying to prevent retries (impossible) and focus instead on making sure they never have a cumulative effect.

The real problem: append-only inserts with no dedup key

The faulty pattern, very common because it's the simplest to write:

@task
def load_orders(execution_date):
    orders = extract_orders(execution_date)
    write_to_warehouse(orders, mode="append")  # ← chaque exécution ajoute, ne remplace jamais

Any retry, manual or automatic, duplicates everything already written during a previous attempt, whether complete or partial.

What we changed

1. Replacing append with an atomic partition swap. Each run writes into an isolated partition (one per day), then replaces the existing partition in a single atomic operation instead of appending rows:

-- ClickHouse : on charge dans une table temporaire, puis on échange
-- la partition en une opération atomique soit tout, soit rien.
INSERT INTO orders_staging SELECT * FROM input_orders WHERE order_date = '2026-08-09';
 
ALTER TABLE orders_fact
REPLACE PARTITION '2026-08-09' FROM orders_staging;

A retry, even a partial one, only rewrites the same partition with the same content. The final result no longer depends on the number of attempts.

2. An engine-level deduplication key, as a safety net. On tables where a partition swap isn't practical, we rely on ReplacingMergeTree with a business key (order_id) and a version column. Duplicates eventually get deduplicated during merges, and we can force immediate deduplication with FINAL for critical cases.

3. Idempotence tests in CI. Every new load task must pass a test that runs it twice in a row on a fixed dataset and checks that the result is strictly identical across both runs:

def test_load_orders_is_idempotent(warehouse):
    load_orders("2026-01-01")
    snapshot_1 = warehouse.query("select count(*) from orders_fact")
 
    load_orders("2026-01-01")  # même exécution, une deuxième fois
    snapshot_2 = warehouse.query("select count(*) from orders_fact")
 
    assert snapshot_1 == snapshot_2

Result

Zero duplication incidents since putting this pattern in place, despite several dozen legitimate retries (network outages, timeouts) that would previously have resulted in silently duplicated data.

The rule we apply now

Design every task as if it were going to run twice. This isn't a pessimistic assumption. In a pipeline that runs every day for years, it always ends up happening. The question isn't "can my task fail and get retried," but "what actually happens when it does."