Silent Row Loss: the join that quietly drops (and duplicates) your data

The data in this project is synthetic — generated locally by data/make_data.py, with no external source. That is deliberate: the whole point is to check whether a join kept the right rows, and only synthetic data lets you know the right answer in advance. No signup, no downloads, no GPU.

Before you start

New to joins? Start here — it’s the idea the whole project rests on.

A join (pandas calls it a merge) combines two tables by matching a shared column, called a key. It’s the same idea as a spreadsheet VLOOKUP: pull matching information from one table into another.

A tiny example — you have orders, and separately a table of what each product costs:

orders                        products
order_id  product             product   price
1         apple               apple      30
2         banana              banana     10

Join the two on product and every order gains its price. Easy — when the keys line up. This project is about what happens when they don’t: a key stored as "0000123" in one table and 123 in the other, a product missing from the catalogue, a product listed twice by mistake. Then a join quietly drops rows (they vanish from the result) or duplicates them (one row silently becomes several) — and pandas raises no error. The table is simply wrong, and the row count often still looks about right.

What you’ll learn: how to run a join and then prove it did what you meant — catch the silent drops and duplicates, tell a genuinely missing row from a formatting artefact, and reconcile the numbers so a total you can defend. That skill has a name — join integrity — and section 3 lists exactly what you’ll be able to do with it.

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

1. The Brief

You will take two tables that look joinable, join them, and prove whether the result is correct — because the row count alone will lie to you. You will make a join’s losses visible, separate a real missing-data problem from a fake one caused by key formatting, and stop an accidental one-to-many fan-out before it inflates your numbers.

Anyone who builds a report from more than one table cares about this, whether they know it or not. A join that silently drops matched rows is one of the most common and most expensive data bugs there is: a monthly total that quietly excludes the rows a join couldn’t match reconciles cleanly, passes review, ships to a dashboard — and is wrong, in the direction of “looks fine.” The failure has no error message. The only defence is to check the join instead of trusting it.

2. Difficulty & time

Difficulty 3 / 10. One tool (pandas), one concept (join integrity), and a Part A whose path is fully specified. It sits above the rubric’s 1–2 anchor (“load a CSV, find why the row count changed after a join”) because the fix requires real key-normalisation and Part B is a genuine transfer with a failure mode you can walk straight into — not a near neighbour. It sits below 4 because there is no algorithmic choice and no second system. (Calibrated against the ledger per G10: about level with #idempotent-loads (also difficulty 3, a single-mechanic data-engineering build), and clearly below #reorder-point-trap and #accuracy-is-lying (both difficulty 5), which each add a simulation or modelling pipeline this project has no equivalent of. It stays above the rubric’s 1–2 band for the reasons above.)

Time is separate from difficulty: Part A is about 75 minutes of reading and running; Part B is 2–4 hours of your own work. The scripts themselves run in a couple of seconds — the time is comprehension and diagnosis, not compute.

3. What you’ll be able to do after

Detect that a join changed your row count in a way you didn’t intend — in either direction — using how="left", indicator=True, and validate=.
Diagnose why rows dropped: a genuinely unmatched key versus a key that only looks unmatched because of dtype or formatting (leading zeros, whitespace).
Collapse an unintended one-to-many join to one row per entity, deliberately, instead of discovering the fan-out three steps downstream.
Reconcile a multi-table join so that a total which looks unchanged can be proven right or wrong row by row.

The finished result

By the end of Part A you’ll have turned two messy tables into one clean, trustworthy table — every order kept, every scan matched, and the truly unscanned orders correctly flagged. The final step prints exactly this (and saves it to a CSV):

status
PACKED     1489
SHIPPED    1468
PICKED     1443
NO_SCAN     600

5000 orders in, 5000 out — none silently lost, none silently duplicated — with the 600 genuinely unscanned orders labelled NO_SCAN instead of vanishing. “Same count in as out, and provably so” is the whole point.

4. Prereqs & time box

You can write a for loop and a function, and you’ve seen a pandas DataFrame at least once. No prior experience with joins is needed — the primer above covers the idea.

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, nothing that can go wrong with your computer. This is the quickest way to start, and Part A uses only pandas, which runs fine 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: 60–90 minutes. Hard cap. Past 90 minutes you’re fighting your setup, not the join — skip ahead and come back.
Part B — Your Turn: 2–4 hours. Hard cap.

5. Setup & data

The data is synthetic and generated locally — see the banner above and SOURCES.md for full provenance. Once you’re set up (section 4), one command creates every table:

python data/make_data.py

That writes five CSVs into data/ (git-ignored; regenerate anytime, byte-for-byte identical because the seed is fixed):

Part A — orders.csv (one row per order, the system of record) and scans.csv (warehouse handheld scan events, zero to three per order).
Part B — sales.csv, products.csv, stores.csv.

The generative story: a mid-size third-party logistics operator during a peak fortnight. Orders flow through a warehouse; handhelds record pick/pack/ship scans, except when they don’t (a dropped packet in the freezer aisle), and the scan system doesn’t always format the order reference the same way the order system does. Every defect is documented in data/make_data.py.

6. Part A — Guided Build

You want to attach each order’s latest scan status to the order. Two tables, one key, what could go wrong. Run the full script and read along:

python part_a/diagnose_joins.py

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

▶ Try it now — run the real Part A right here in your browser. This one button makes the data and runs Part A for you — you do not need to install anything, and you do not need to type any of the python … commands anywhere on this page (those are only for running on your own computer instead). First run loads Python + this project’s libraries: usually ~10–20 s, a little longer for machine-learning projects.

The full, commented script is in part_a/diagnose_joins.py. The steps below are the spine of it. Every pandas function used here is linked in the “Functions you’ll meet” table at the end of this section — click through any time you want the full reference.

Step 1 — Load identifiers as strings. A CSV carries no dtypes. Let pandas guess and the order id "0000123" becomes the integer 123 — the leading zeros are gone and the key will never match again. So we pin the key columns to str at read time with read_csv’s dtype:

orders = pd.read_csv("data/orders.csv", dtype={"order_id": str})
scans  = pd.read_csv("data/scans.csv",  dtype={"order_ref": str})

Checkpoint: orders has 5000 rows with 12 null ship_date; scans has 8825 rows.

Step 2 — The naive inner join, and why its row count is a lie. We attach scans to orders with a plain inner merge:

naive = orders.merge(scans, left_on="order_id", right_on="order_ref", how="inner")

Checkpoint: 8119 rows — more than the 5000 orders you started with — yet only 4283 distinct orders survive. The join inflated and dropped at the same time, and a bare len() hides both facts.

Step 3 — Make the loss visible. An inner join throws away the evidence of what it dropped. A left join with indicator=True keeps every order and labels each row left_only / both / right_only.

diag = orders.merge(scans, left_on="order_id", right_on="order_ref",
                    how="left", indicator=True)
unmatched = diag.loc[diag["_merge"] == "left_only", "order_id"].nunique()

Checkpoint: 717 orders look unscanned. Hold that number — it’s wrong, and the next step shows why.

Step 4 — Normalise the keys, then re-check. Some scan references lost their zero-padding ("123") and some carry whitespace (" 0000123 "). Same order, different formatting. Canonicalise both sides — strip whitespace with str.strip and re-pad with str.zfill — before comparing, and prove the cardinality with validate=:

def norm(s): return s.astype(str).str.strip().str.zfill(7)
orders["order_key"] = norm(orders["order_id"])
scans["order_key"]  = norm(scans["order_ref"])
matched = orders.merge(scans, on="order_key", how="inner", validate="1:m")

Checkpoint: 8825 rows now match (every scan). The truly unscanned orders fall to 600. The gap — 117 orders — was pure formatting, not missing data. If you had trusted the raw join you would have “found” 117 problems that don’t exist.

Step 5 — Collapse the one-to-many, on purpose. You still have up to three scans per order. sort_values by scan time, keep the last with drop_duplicates, and prove uniqueness with validate="1:1".

latest = (matched.sort_values("scan_ts")
          .drop_duplicates("order_key", keep="last"))
enriched = orders.merge(latest[["order_key", "status", "scan_ts"]],
                        on="order_key", how="left", validate="1:1")

Checkpoint: latest has 4400 rows (one per scanned order); enriched has 5000 (no order lost), of which 600 are NO_SCAN.

Why this and not that

Why a left join with indicator=True to diagnose, not an inner join. The inner join is what you’ll ship, but it’s the worst thing to debug with, because it has already deleted the rows you need to see. Diagnose with a left join that keeps everything and labels it; switch to inner only once you know what inner will remove.
Why validate= instead of eyeballing the row count. A row count tells you the net effect of drops and fan-out together, and those can cancel. validate="1:m" / "1:1" asserts the cardinality you expect — how many rows on each side are allowed to match (one-to-one? one row matching many?) — and raises the moment that’s violated: a tripwire, not a post-mortem. Step 6 in the script shows it refusing the fan-out outright.
Why normalise keys as strings, not cast to int. Casting "0000123" and "123" to int would make them match — and would also silently mangle any id that is legitimately a string, and can’t touch the whitespace case at all. Strip-and-pad fixes the format without changing what the key is.

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

Function	What it does here	Docs
`pd.read_csv(..., dtype=...)`	Loads a CSV; `dtype={"order_id": str}` preserves a key’s leading zeros	read_csv
`df.merge(..., how=, on=)`	Joins two tables on a key column	merge
`merge(..., indicator=True)`	Adds a `_merge` label (`left_only`/`both`/`right_only`) so drops are visible	merge
`merge(..., validate="1:m")`	Asserts the join’s cardinality; raises on an unexpected fan-out	merge
`s.str.strip()`	Removes surrounding whitespace from a text key	str.strip
`s.str.zfill(7)`	Pads a text key with leading zeros to a fixed width	str.zfill
`df.sort_values(col)`	Orders rows (here, by scan time)	sort_values
`df.drop_duplicates(subset, keep="last")`	Keeps one row per key — the latest scan	drop_duplicates
`s.value_counts()`	Counts rows per category (the status breakdown)	value_counts
`s.fillna(x)`	Replaces nulls (missing status → `NO_SCAN`)	fillna
Going deeper	pandas’ own guide to joining tables	Merge, join, concatenate

If something looks off

A checkpoint number doesn’t match. The data is seeded, so every number here is exact. If yours differs, you almost certainly skipped python data/make_data.py (or edited it) — re-generate the data and re-run. The other usual cause is not reading the id columns as strings in Step 1: without dtype={...: str} the leading zeros are lost and the keys stop matching.
Step 6 prints a red MergeError — did I break something? No — that error is the point of Step 6. The code deliberately asks validate="1:1" for a join that is really one-to-many, so pandas refuses it. Seeing the error means the guardrail works; the script catches it and finishes normally with “Part A complete.”
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 domain, more tables, and a nastier failure mode. A retail chain wants one clean enriched sales table: every sale line item with its product details and its store details attached. You’re given sales.csv, products.csv, and stores.csv.

Here’s the trap. If you join all three and check the final row count against the original sales count, it will look almost unchanged — and it will still be wrong. Two independent defects happen to cancel in the total while corrupting the rows underneath. Your job is to find both, fix both, and prove the result.

This is the same core skill as Part A — validate the join, don’t trust it — applied to three tables, a different domain, and a fan-out that hides a drop.

Acceptance criteria (checkable)

Your solution writes two files into part_b/out/:

enriched.csv — the corrected table: exactly one row per sale that has both a valid product and a valid store, with product and store attributes attached.
report.json — with keys duplicate_skus, fan_out_extra_rows, dropped_no_product, dropped_no_store, final_rows.

Then:

python part_b/starter.py          # your finished version
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 the test.

You’re done when all checks pass. They verify you identified the duplicated catalogue key, counted the fan-out and the dropped rows correctly, produced a table with no duplicate sale_id and no unmatched keys, and reached row-resolution accuracy ≥ 0.99 (the reference solution reaches 1.0).

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

One row per valid sale. No sale_id may appear twice.
Every row in enriched.csv has a non-null product and store — no join left a hole behind.
You must decide which of the duplicated catalogue rows is canonical and be able to say why. There is a defensible choice; make it deliberately.
Your reported counts must reconcile: len(sales) equals final_rows plus the sales you dropped. If they don’t add up, you’ve either double-counted or missed a defect.

Hints

Hint 1 — where to look

Join one table at a time and check the row count after each merge, not just at the end. A total that barely moves is not evidence that a join was clean — it’s exactly what you’d see if one join added rows and another removed about as many.

Hint 2 — the two tools

Before joining to the catalogue, ask whether its key is unique: products["sku"].duplicated(keep=False) shows duplicates, and validate="m:1" will raise on them. For the store join, how="left", indicator=True tells you exactly which sales found no store.

Hint 3 — the mechanism (still not the code)

Two errors are cancelling in the total. A duplicated SKU in the catalogue fans every sale of that SKU into more than one row; meanwhile an equal number of sales point at a store code that isn’t in the store dimension and get dropped. Fix them separately — deduplicate the catalogue (choose the canonical row), then drop the unmatched-store sales — and reconcile the counts. Don’t try to do both in one chained merge.

8. Self-check

You don’t need the answer key. You’re done when all of these hold:

Every row in enriched.csv has a non-null product and store.
No sale_id appears more than once.
Your drops reconcile: len(sales) == final_rows + dropped_no_store + dropped_no_product, with no sale counted in two buckets.
Re-running from python data/make_data.py reproduces the same numbers — the data is seeded, so a stable pipeline is a reproducible one.

And the tell-tale: if your final row count equals the original sales count exactly, be suspicious. That’s the trap doing its work — the fan-out and the drop cancelling — not a sign the join was clean.

9. Stretch

Make it reusable. Wrap the diagnosis into validate_join(left, right, on, how, expected_rows=None) that returns a small report (matched, left_only, right_only, fan-out) and raises when the cardinality isn’t what you claimed.
Composite keys. Rework a join so the key is two columns, not one, and show that your row-count checks still hold.
(Genuinely hard) The duplicate isn’t always a bug. Treat the duplicated catalogue entry as a slowly-changing dimension: give the two rows valid_from / valid_to dates and join each sale to the catalogue row that was in effect on its sale date (an as-of / interval join). Now “which row is canonical” depends on the sale, not on a global rule — and a plain drop_duplicates gives the wrong answer.

10. Ship it

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

State the problem in one sentence a non-engineer gets: a join silently dropped sales while the total looked unchanged — here’s how I caught it.
Show the before/after: the naive row count next to the reconciled one, and the reconciliation that proves the second is right.
Include your validate_join helper and one paragraph on the masking trap (a fan-out and a drop cancelling in the total). That paragraph is the part that signals you’ve actually been bitten by this before.

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
Dataset	Silent Row Loss — synthetic 3PL shipments (Part A) and retail sales (Part B)
Type	Synthetic
Generated by	`data/make_data.py` (this repo), seed `20260717`
Source URL	Not applicable — generated, not fetched
License	Not applicable — synthetic, no third-party rights
Access / generation date	2026-07-17
Redistribution	Permitted — synthetic; output regenerated from the seeded script, never committed
robots.txt checked	Not applicable — no scraping
ToS URL	Not applicable — no external source

Synthetic data teaches the mechanic; it does not prove a finding about the real world. The defects were injected on purpose — recovering them demonstrates the technique, not a claim about how often real systems carry them.