Idempotent loads: why re-running your pipeline shouldn't double your data

Data: both parts use synthetic data, generated locally by data/make_data.py (seeded, byte-for-byte reproducible). Nothing is downloaded; no signup, no GPU. The whole project runs on Python’s built-in sqlite3 and pandas — no database to install. Synthetic is the right call here: to check a load is correct and idempotent you must know the true distinct records and their right final values, and with generated data you do.

Before you start

New to this? Two minutes of vocabulary, because the whole project turns on it.

A load moves rows from a source (a daily file, an event feed) into a table you report from. Two words decide whether it’s trustworthy:

Idempotent — running it again produces the same result. Load a file, then load it again by mistake (a retry, a double-click), and a good pipeline’s table looks identical. A bad one now has every row twice.
Upsert — “update if it’s there, insert if it isn’t.” You match incoming rows to existing ones on a key (here, order_id): a row you’ve seen updates in place instead of being appended again.

A tiny example — your table has order O42 with status PENDING. The load runs again. A naive “insert everything” load gives you two O42 rows and now every count, sum, and join downstream is wrong. An idempotent load leaves exactly one O42, updated to its latest status. That difference — a doubled table vs. a stable one — is what this project is about.

What you’ll learn: to load data so a re-run is safe, to merge daily/incremental batches without duplicating or losing rows, and to keep the latest value for each key even when records arrive more than once or out of order.

New to Python? Section 4 (“Where to write and run code”) starts from zero.

1. The Brief

You’ll take a perfectly ordinary daily orders file, load it into a table the obvious way — insert every row — and then simulate the single most common pipeline accident there is: the job runs twice. You’ll watch a 5,050-row load become 10,100 rows, silently corrupting every downstream number. Then you’ll rebuild the load as an idempotent upsert keyed on the order id, watch the re-run become a no-op, and extend it to fold in the next day’s new orders and status changes without ever duplicating a row.

Anyone who owns a data pipeline hits this. Retries, backfills, an operator running a job “just to be safe” — re-execution is normal, and a load that isn’t idempotent turns every re-execution into a data-corruption incident that’s invisible until a report looks wrong. Idempotency is the property that lets you re-run without fear, and it’s the difference between a pipeline you can operate and one you tiptoe around.

2. Difficulty & time

Difficulty 3 / 10. It’s about level with the seed project #silent-row-loss (difficulty 3): both are single-concept data-quality projects with one walk-into failure (there, a join that drops rows; here, a load that doubles them) and both are mostly one tool on real-shaped tabular data. It’s clearly easier than #reorder-point-trap and #accuracy-is-lying (both difficulty 5), which chain a simulation or a modelling pipeline with a genuinely non-obvious statistical idea; this project’s core idea (key + upsert = idempotent) is graspable in a sentence, and Part A’s path is fully specified. It earns a 3 rather than a 1–2 because Part B needs a real design decision — ordering an out-of-order event stream by timestamp, not arrival — and because getting “latest wins” right across duplicated, reordered batches is a genuine debug surface. Anchored to the rubric’s level-3 band and those three named ledger projects (G10).

Time is separate from difficulty: the scripts finish in well under a second (measured Part A wall-clock 0.47s). Part A is about 60 minutes of reading and running; Part B is 2–3 hours of your own work.

3. What you’ll be able to do after

Explain why an “insert everything” load corrupts a table on re-run, and demonstrate it (5,050 → 10,100 rows) rather than just asserting it.
Write an idempotent upsert keyed on a business key, so re-running a load is a no-op and duplicate source rows collapse onto their key.
Fold an incremental batch (new records + updates to existing ones) into a table without duplicating or dropping anything.
Merge an out-of-order, at-least-once event stream into one row per key holding its latest value — by ordering on the event timestamp, not on arrival order.

The finished result

By the end of Part A you’ll have proven the whole idea on your own screen — a re-run that corrupts a table, and the fix that makes re-running safe:

naive "insert everything" load:  5,050 rows  ->  re-run  ->  10,100 rows   (doubled!)
idempotent upsert on the key:    5,000 rows  ->  re-run  ->   5,000 rows   (unchanged)
then day-2 merges in cleanly:    5,800 distinct orders, none duplicated

Same input, same result — that stability is idempotency, and it’s what lets you re-run a pipeline without fear.

4. Prereqs & time box

You can write a for loop and a function, and you’ve seen a pandas DataFrame. No SQL or database background is assumed — the few lines of SQL are explained inline, and everything runs on Python’s built-in sqlite3 (no install, no server). The concepts (key, upsert, idempotency) are defined in the primer above.

Where to write and run code

Two ways to run this project — pick either:

In your browser (no install). If the project page shows a ▶ Run button, click it to run Part A right here — nothing to set up. Part A uses only pandas and Python’s built-in sqlite3, both of which run in the browser.
On your computer. The one-time setup — installing Python, downloading the code from the course site (no GitHub, no git), opening a terminal, creating a virtual environment, installing the requirements, and running Part A step by step — is covered once in Start Here. Come back here once python -c "import pandas" runs without error. Part B (writing your own solution) needs this local setup.

Time box

Part A — Guided Build: 45–75 minutes. Hard cap 90.
Part B — Your Turn: 2–3 hours. Hard cap 4.

5. Setup & data

Both datasets are synthetic, generated by one seeded command (byte-for-byte reproducible, git-ignored, never committed):

python data/make_data.py

That writes:

Part A — data/orders_day1.csv and data/orders_day2.csv: two daily order exports. Day 1 contains a few exact-duplicate rows (the export hiccupped); day 2 brings new orders and status updates to existing ones.
Part B — data/payment_events_batch1.csv … batch3.csv: three incremental batches of payment status events, delivered at-least-once (events repeat) and out of order.

The generative story and every injected defect are documented in data/make_data.py. Synthetic data teaches the mechanic here; it does not describe any real company.

6. Part A — Guided Build

Run the full script and read along:

python data/make_data.py          # once
python part_a/load_idempotent.py

Prefer the browser? If this page has a ▶ Run button, click it instead of the commands above — Part A runs in your browser with nothing to install. The STEP output and every checkpoint below are identical either way.

The full, commented script is in part_a/load_idempotent.py. It uses an in-memory SQLite database (":memory:") created fresh for each demo, so it’s self-contained.

Step 1 — Look at the day-1 file. It is not unique on the key.

Checkpoint: 5,050 rows, but only 5,000 distinct order_id — the export shipped 50 orders twice.

Step 2 — The naive load: insert everything. Then the job re-runs. A table with no key; every row is appended, every time.

Checkpoint: after one load 5,050 rows; after a re-run 10,100 rows — the table doubled. A single retry just corrupted every downstream count.

Step 3 — The idempotent load: a key plus an upsert that keeps the latest. The table has order_id as a PRIMARY KEY, and the insert uses ON CONFLICT(order_id) DO UPDATE … WHERE excluded.updated_at > orders.updated_at — a row updates in place only when the incoming one is newer.

Checkpoint: after one load 5,000 rows; after the same re-run still 5,000. The 50 duplicates collapsed onto their key, and the re-run added nothing. Same input → same result: that is idempotency.

Step 4 — Day 2 arrives: new orders and status updates. Load it the same way.

Checkpoint: day 2’s 2,000 rows resolve to 1,200 updates and 800 new orders → 5,800 distinct orders, none duplicated. One order’s status flips from its day-1 value to the newer day-2 value, in place.

Why this and not that

Why a key + upsert, not “delete everything and reload”? A full reload is also idempotent, but it throws away history you may not be able to re-fetch, locks the table while it runs, and rewrites the whole table every day even when 1% changed. An upsert touches only what changed and is safe to interleave with reads.
Why the WHERE excluded.updated_at > … guard? Without it, re-feeding an old copy of a row would overwrite a newer value. The guard makes the load keep the latest regardless of what order rows arrive in — which is exactly the problem Part B leans on.
Why does the naive re-run double instead of erroring? Because nothing declared the key. The database has no reason to think two identical rows are “the same order,” so it stores both. Declaring the key is what turns a silent duplication into a handled update.

Functions you’ll meet (and where to read more)

Function	What it does here	Docs
`pd.read_csv(..., dtype={"order_id": str})`	Load the export, keeping ids as text	read_csv
`sqlite3.connect(":memory:")`	A throwaway database, no install, no file	sqlite3
`INSERT … ON CONFLICT … DO UPDATE`	The upsert: update on key clash, else insert	SQLite UPSERT
`cursor.executemany(sql, rows)`	Apply the upsert across many rows at once	executemany
`DataFrame.drop_duplicates`	(Part B) collapse repeated events / keys	drop_duplicates

If something looks off

The idempotent load shows 5,000 rows but the file has 5,050 — did it lose data? No. The day-1 export shipped 50 orders twice (it is only unique on 5,000 order_ids). The idempotent load collapses each duplicate onto its key, so 5,000 is the correct distinct count — it’s the naive load’s 5,050 (and 10,100 on re-run) that’s wrong.
A checkpoint number doesn’t match. The data is seeded, so the counts are exact. Re-run python data/make_data.py (you may have skipped or edited it), then re-run Part A.
Setup problems (Python not found, pip, FileNotFoundError, re-activating the venv): see the troubleshooting list in Start Here.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already handles the easy version; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Different world, same core skill. A payments service emits an append-only stream of status events (authorized → captured → refunded, and so on). You get three incremental batches (data/payment_events_batch{1,2,3}.csv), and two things are true of them, the way they are for any real queue:

At least once: the same event (same event_id) can appear more than once.
Out of order: a later row in a file can carry an older event_ts than an earlier row. Arrival order is meaningless.

Your job: merge the batches into one row per payment_id holding its latest status (by event_ts), correctly and idempotently — re-feeding any event must not change the result. Same skill as Part A (a keyed, idempotent merge that keeps the latest), now against a stream where you cannot trust arrival order.

Acceptance criteria (checkable)

Fill in part_b/starter.py, then:

python part_b/starter.py            # writes part_b/out/
pytest part_b/test_solution.py -q

If pytest reports everything as skipped, it just means the output files don’t exist yet — run python part_b/starter.py first (once your solution fills in the TODOs), then re-run.

Your solution writes into part_b/out/:

payments_final.csv — one row per payment_id: payment_id, status, amount, event_ts.
report.json — distinct_payments, final_rows, final_status_accuracy, achieved_metric.

You’re done when the tests pass. The grader recomputes the true latest status of every payment straight from the raw batches and checks yours against it, so the numbers must be real. It requires: exactly one row per payment (3,000 total, no duplicates), and final-status accuracy ≥ 0.99. The reference solution scores 1.00; a merge that keeps “the last row seen” instead of the latest timestamp scores about 0.62.

Constraints — what must be true, not how to get there

One row per payment_id, no duplicates. A merge that leaves two rows for a payment isn’t idempotent.
The kept status must be the one with the greatest event_ts, not the last row in the file. Order by the timestamp.
Collapse repeated event_ids before you decide — at-least-once delivery means the same event shows up more than once.

Hints

Hint 1 — the shape of the problem

Union the three batches into one frame first. Then two clean-ups remain: remove repeated events, and pick the right row per payment. Do them in that order.

Hint 2 — the two clean-ups

Repeated events share an event_id — drop_duplicates on that column handles at-least-once delivery. For “latest wins,” sort by event_ts and keep the last row per payment_id (or group by payment_id and take the max-timestamp row).

Hint 3 — the trap (still not the code)

The batches are shuffled, so “the last row I read for this payment” is almost never its latest event — that’s why the naive approach scores ~0.62. Nothing in your logic may depend on the order rows happen to appear in; it must depend only on event_ts. That order-independence is the same property that makes Part A’s load idempotent.

8. Self-check

You don’t need the answer key. You’re done when:

You can state, in one sentence, why the naive Part A load doubled the table on re-run and the idempotent one didn’t.
Re-running your Part B merge on the same batches produces a byte-identical output (that’s idempotency — test it).
Every payment has exactly one row, and you can explain how you guaranteed the kept status is the latest by timestamp rather than by arrival.
You can point to where duplicate events were collapsed and where out-of-order events were handled.

9. Stretch

Watermark / incremental state. Instead of re-reading all batches every time, track the greatest event_ts you’ve loaded and only process events newer than that. Show it produces the same table for far less work — and stays idempotent.
Soft deletes. Add cancelled events that should remove a payment from the active table without losing its history. Decide how to represent “deleted” and keep the load idempotent.
(Genuinely hard) Late-arriving corrections with SCD-2. Instead of overwriting, keep full history: every status change becomes a row with valid_from / valid_to, so you can ask “what did this payment look like on date X.” Make the load idempotent under replays and correct when an old event arrives late.

10. Ship it

Put this in a portfolio repo and it counts. In that repo’s README:

Lead with the one-liner an ops person gets: “A retried load used to double our table — here’s the before/after (5,050 → 10,100 vs. a re-run that changes nothing), and the one property that fixed it.”
Show the naive-vs-idempotent row counts side by side, and name the key you upserted on.
State the Part B judgment in one sentence: how you guaranteed “latest wins” against an out-of-order, at-least-once stream. That sentence is what signals you can be trusted to own a pipeline.

Keep make_data.py and the tests in the repo so anyone can reproduce your numbers from zero.

11. Sources

Rendered from SOURCES.md.

Field	Value
Datasets	Synthetic — daily order exports (Part A); payment event stream (Part B)
Data type	Synthetic (seeded, reproducible)
Generated by	`data/make_data.py` — `numpy.random.default_rng(seed=20260718)`
Source / license	Not applicable — synthetic, generated in-repo; no third-party rights
Access / verification date	2026-07-18
Redistribution	Not applicable — generator committed, CSV output never committed
robots.txt / ToS	Not applicable — nothing is scraped or fetched

The data is synthetic and teaches the mechanic (idempotent and incremental loading); it does not describe any real company. Every injected defect is documented in data/make_data.py.