Idempotent Pipelines

How the question arrives

The interviewer rarely says the word idempotent. The question shows up as a story: the orchestrator retried the 02:00 load at 02:40 after a timeout, and finance now sees revenue 1.8 times what the ledger shows. Or it is framed as a design prompt: this job writes to the fact table, what happens if it runs twice? Both are the same question. The score comes from 2 things in order: first the diagnosis of where a second run changes the result, then the pattern that removes that change. Candidates who jump to 'I would add deduplication' before naming what duplicates are produced lose the diagnosis points, and those points carry most of the weight at this level.

Diagnose before you fix

A pipeline stops being idempotent at a specific line, and the interviewer wants it named. The most common is an append-only INSERT into the target, where run 2 adds the same rows again. A surrogate key generated by a sequence gives the same order 2 different ids. An aggregate computed as target plus delta lands the delta twice. And a side effect outside the warehouse leaves the second run nothing to compare against: once the email has gone out, there is no row to check, and the same holds for a payment call or a file moved to an archive folder.

Step	What run 2 does	Idempotent?
INSERT INTO fact_orders SELECT ... FROM staging	adds the same 50,000 rows again	No
DELETE the partition, then INSERT it	removes run 1's rows, writes identical rows	Yes
UPDATE totals SET revenue = revenue + delta	adds the delta a second time	No
Write orders/dt=2024-11-24/part-0.parquet	overwrites the same path	Yes
Call the payment API to capture a charge	charges the customer twice	No, unless keyed

The 3 production patterns

The pattern to lead with is partition overwrite. The job owns 1 partition, usually a date or an hour. It deletes everything in that partition and rewrites it from source. Run 2 produces the same partition as run 1 because the output is a pure function of the input slice. The SQL is short, and interviewers like seeing it because it shows you know the unit of work.

DELETE FROM fact_orders
WHERE order_date = DATE '2024-11-24';

INSERT INTO fact_orders
SELECT
  order_id,
  customer_id,
  order_date,
  amount_cents
FROM staging_orders
WHERE order_date = DATE '2024-11-24';

Pattern 2 is merge on a natural key, for targets where a partition is the wrong unit, such as a customer dimension fed by a change feed. The write says: if this customer_id exists, update it, otherwise insert it. Run 2 finds every row already present and updates it to the same values. The cost is a lookup per row, which is why partition overwrite wins for large fact tables and merge wins for dimensions and late corrections.

Pattern 3 handles the side effects. Any call that leaves the warehouse carries an idempotency key, a deterministic id built from the business event, and the receiving system records the keys it has seen. Payment providers expose this on their APIs for the same reason: a client retry after a network timeout must never create a second charge. In your own services the same idea is a table of processed keys checked before the action fires.

Each node carries its own idempotency strategy, so a retry at any point replays safely

orders_db (source, transactional database)
extract_daily (transform, orchestrator task); retries: 3; backoff: exponential, 5 min base; idempotency: snapshot by logical date
fact_orders (storage, warehouse table); idempotency: partition overwrite on order_date
notify_finance (consumer, email service); idempotency: key = run_date + report_id

orders_db -> extract_daily
extract_daily -> fact_orders
fact_orders -> notify_finance

Where the follow-up goes

The follow-up that separates tiers is 'what about a stream?' Here the unit is an event rather than a partition, and the pattern is deduplication by event id inside a bounded window, because a replayed consumer group re-reads events it already processed. The window matters: an id store that grows forever becomes the next outage, so you state the retention, typically a few hours past the longest expected replay. The query below is what dedup looks like at the batch boundary, keeping the latest copy of each event.

SELECT
  event_id,
  order_id,
  status,
  event_ts
FROM (
  SELECT
    event_id,
    order_id,
    status,
    event_ts,
    row_number() OVER (
      PARTITION BY event_id
      ORDER BY event_ts DESC
    ) AS rn
  FROM raw_order_events
  WHERE event_ts >= TIMESTAMP '2024-11-24 00:00:00'
)
WHERE rn = 1;

Add 2 further points. First, separate the 2 claims: exactly-once describes the delivery path, idempotency describes your write, and the second is the one you control. Second, the scope of the retry matters. A task-level retry inside the orchestrator is cheap to make safe, while a human re-running a backfill over 90 days needs the same guarantee, so the partition the job touches comes from the run's logical date rather than from now().

Do

Name the exact step that breaks under a second run before proposing any fix
Derive the partition from the run's logical date so backfills replay the same slice
Put an idempotency key on every call that leaves the warehouse
State the retention of any dedup id store

Avoid

Say 'add deduplication' without saying what produces the duplicates
Compute aggregates as target plus delta
Generate surrogate keys from a sequence inside a retryable task
Claim exactly-once when you mean an idempotent write

About this lesson

This section is part of the How Data Moves: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.