Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 36 ยท Architecture and Lifecycle

Idempotent Pipeline Design

Learn why pipelines must be safe to rerun, and how upserts, partition overwrites and deduplication keys make jobs idempotent, with runnable Python examples.

Pipelines fail. A network call times out, a server restarts, someone fixes a bug and asks you to "just run it again". If rerunning a job duplicates rows or double-counts revenue, every failure becomes an incident. An operation is idempotent when running it once or many times gives the same final result. Designing jobs this way is the single most valuable habit in data engineering.

The problem: append on retry

Consider a daily job that appends yesterday's orders. If it crashes after loading but before recording success, the scheduler retries, and the orders are loaded twice.

Python A non-idempotent load duplicates data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Revenue is now 70, double the truth, and nothing raised an error. That silence is what makes it dangerous.

Technique 1: overwrite the partition

If each run owns one slice of the data (one day), make the load replace that slice: delete it, then insert, inside one transaction.

Python Delete-then-insert by partition

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Three runs, same answer. The transaction matters: without it a crash between DELETE and INSERT would leave the day empty.

Technique 2: upsert on a natural key

When rows have a stable unique identifier, use an upsert: insert new rows, update existing ones. SQLite spells it INSERT ... ON CONFLICT DO UPDATE; other databases use MERGE or similar syntax.

Python Upsert with a primary key

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The retry changed nothing. The upsert needs a deterministic key: if you generated a random id per run, every retry would create new rows.

Technique 3: deduplicate on a business key

Sources sometimes send the same event twice (network retries, at-least-once delivery). Make the pipeline drop repeats using an event id, keeping the first or the latest.

Python Deduplicate with pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Which duplicate wins is a business decision. Document it.

Technique 4: pure functions and deterministic outputs

Idempotence is easier when transformations are pure: output depends only on input, with no hidden clock or random number. Compare a job that stamps "now" with one that uses the run's logical date:

Python Use the logical date, not the clock

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Orchestrators pass a logical date (the data interval being processed) to each run precisely for this reason; a rerun of last Tuesday must process last Tuesday, not today.

Technique 5: write atomically

Even a perfect overwrite is dangerous if readers can see half-written output. The classic fix: write to a temporary location, then rename or swap it in one step. In databases, wrap the replacement in a transaction; with files, write part.tmp then rename; with warehouses, load a staging table and swap.

flowchart LR
    A["Compute result"] --> B["Write to staging"]
    B --> C{"Checks pass?"}
    C -- yes --> D["Swap into place<br/>(atomic)"]
    C -- no --> E["Discard and alert"]

Readers see the old data or the new data, never half of each.

A checklist for every job

  1. Does each run own a well-defined slice (partition, key range, time window)?
  2. Does a rerun replace that slice rather than add to it?
  3. Is the replacement atomic?
  4. Are keys deterministic?
  5. Does the code avoid reading the current clock for business logic?
  6. Can you run it twice in a test and compare the outputs?

The last item is the easiest to automate and catches most mistakes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does it mean for an operation to be idempotent?

    Idempotent operations are safe to repeat, which is essential for retries and backfills.
  2. 2. Why wrap DELETE and INSERT of a partition in one transaction?

    A transaction makes the replacement all or nothing.
  3. 3. An upsert needs which property to be reliable on retries?

    The key identifies the same row across runs; random keys would create new rows each time.
  4. 4. Why should pipeline logic use the logical date instead of today's date?

    Reruns and backfills must process the intended interval, not the current moment.

Summary

  • Pipelines must be safe to rerun; non-idempotent appends silently double-count.
  • Techniques: partition overwrite in a transaction, upserts on deterministic keys, deduplication on business keys, pure functions with logical dates, and atomic swaps.
  • Test idempotence by running a job twice and comparing results.

Next: Ingesting Files: CSV, JSON and Beyond