Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 36 ยท Orchestration and Quality

Idempotency, Backfills and Reprocessing

Make pipelines safe to rerun: idempotent loads with overwrite-by-partition and upserts, deterministic logic, backfilling history, and late-arriving data.

Every production pipeline will be run twice by accident. A retry fires after a timeout that actually succeeded. An engineer reruns yesterday's job by hand. The orchestrator restarts after a crash. If running a job twice produces double the rows, your numbers are silently wrong and nobody knows. The fix is a design property called idempotency: running the same job with the same input any number of times gives the same final result as running it once.

Idempotency also makes backfills (recomputing history after a bug fix or a new column) boring instead of terrifying.

A broken, non-idempotent load

Python Append-only load doubles on retry

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.

Pattern 1: overwrite the partition

If the target is partitioned by the same key as the run (the logical date), each run replaces its own partition: delete that day, then insert the new rows, in one transaction. The result depends only on the input for that day.

Python Delete-then-insert in one transaction

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.

In lakes and warehouses the same idea appears as INSERT OVERWRITE PARTITION, Spark's dynamic partition overwrite, replaceWhere in Delta, or MERGE.

Pattern 2: upsert by a natural key

When data is not neatly partitioned by run date, upsert on a unique key (an order id): existing rows are replaced, new rows inserted. Running twice has no extra effect.

Python Upsert makes reruns harmless

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.

Rules for idempotent jobs

  1. Do not use "now" inside the logic. Take the logical date as a parameter. WHERE order_date = CURRENT_DATE gives a different answer on every rerun; WHERE order_date = :logical_date does not.
  2. Write to a deterministic location (a path or partition derived from the date), never a random file name that accumulates copies.
  3. Stage, then swap. Write output to a temporary location, validate, and publish with an atomic rename or partition swap so readers never see half-finished data.
  4. Avoid side effects that cannot be undone (emails, payments) inside retried tasks. If you must, send an idempotency key so the receiver ignores duplicates.
  5. Make ids deterministic. Generate surrogate ids by hashing business keys rather than using random UUIDs per run, or a rerun creates different keys.
Python Deterministic ids vs random ids

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.

Backfills

A backfill runs the pipeline for past logical dates. Reasons: a bug in the transformation, a new column, a source that was down, or a brand-new pipeline that needs history.

flowchart LR
  A["Fix logic"] --> B["Choose date range"]
  B --> C["Run each date<br/>idempotently"]
  C --> D["Validate counts<br/>and totals"]
  D --> E["Publish / swap"]

Safe backfill checklist:

  • Because each run overwrites its own partition, you can run dates in parallel (with a concurrency limit so you do not overload the source).
  • Run in a staging area first and compare totals with the existing data before replacing it.
  • Make jobs depend on the data interval, not on "the previous run's side effects", unless the dependency is real (a running balance needs ordered days).
  • Throttle API sources and watch quotas and costs.
Python Backfill a date range, then verify

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 row count stays at 3 and values update: that is idempotent backfilling.

Late-arriving data

Events can arrive hours or days after the day they belong to (mobile apps syncing after being offline). Strategies:

  • Event time vs processing time: partition by event date when you want correct historical answers; partition by load date when you need cheap, append-only ingestion and plan to reconcile later.
  • Lookback window: every run reprocesses the last N days (say 3), so late rows land in the correct day. This works well with partition overwrite.
  • Watermark + corrections: in streaming, accept events up to some lateness and emit corrections (covered in the streaming chapter).
  • Reconciliation job: periodically recompute older partitions and compare with published numbers.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does an idempotent job guarantee?

    Reruns and retries must not duplicate or change the result.
  2. 2. Which query is safest for a daily job that must be rerunnable for past days?

    Using the passed logical date keeps the logic independent of when it runs.
  3. 3. What is the purpose of 'stage, then swap'?

    Writing to a temporary location and then swapping gives all-or-nothing visibility.
  4. 4. How does a lookback window help with late-arriving data?

    With partition overwrite, reprocessing recent days is safe and catches late events.

Summary

  • Idempotency means reruns give the same result; achieve it with partition overwrite or upserts.
  • Pass the logical date, use deterministic paths and ids, and stage before publishing.
  • Backfills are idempotent runs over past dates, validated before they replace production data.
  • Handle late data with lookback windows and reconciliation.

Next: Data Quality Checks