Schema Evolution: load a feed that changes its columns out from under you

The data in this project is synthetic — daily feed files generated locally by data/make_data.py, from a hidden canonical table. That is deliberate: grading a reconciliation needs the known-correct unified table, and the drift (renames, an added column, retyped amounts) is injected in controlled ways real feeds actually exhibit. No signup, no downloads, no GPU.

Before you start

New to schema drift? Start here.

A daily feed is a data source that drops a new file every day, and you append each to a growing table. The problem: over time the schema drifts. Someone renames a column (cust → customer_id), a new column appears (channel gets tracked starting in March), or a field arrives in a new type (amount as "1,234.50" text instead of a number, or as integer cents).

Do the obvious thing — pd.concat the raw files — and it fails silently:

a rename becomes two columns (cust and customer_id), each half-empty;
an added column leaves the older days as NaN;
a retype makes the column object dtype, so df["amount"].sum() is wrong or errors.

No exception — just a corrupted table you might not notice. The fix is to reconcile every day to one canonical schema: a rename map, type coercion, and filling columns that a given day lacks.

A tiny example — two days that should stack cleanly:

day1:  cust        date        amount
day2:  customer_id date        amount_str
naive concat -> columns: cust, customer_id, amount, amount_str   (all half-empty)
reconciled   -> columns: customer_id, date, amount               (whole)

What you’ll learn: see exactly how a naive concat corrupts a drifting feed, then build a loader that reconciles each day to a canonical schema — and extend it when a new feed drifts in ways your first map didn’t cover. Section 3 lists exactly what you’ll be able to do.

New to Python too? See Start Here — it starts from zero, and shows how to run this in your browser with nothing to install.

1. The Brief

Every morning a partner drops yesterday’s transactions as a CSV, and every few weeks they quietly change the format — rename a field, add one, switch amounts to a new representation. Your warehouse table has to keep loading correctly through all of it. You’ll show how a naive append corrupts the table, build a loader that reconciles each day to a canonical schema, and then harden it against a feed that drifts in new ways.

Schema evolution is one of the most common and most under-tested realities of data engineering — the pipeline that “worked yesterday” and silently produced garbage today. A reconciliation layer (a canonical schema plus a registry of how each source maps to it) is the standard defense, and the skill is identical whether the feed is CSVs, an API, or Kafka topics.

2. Difficulty & time

Difficulty 4 / 10. One tool (pandas), but a genuine design decision: define a canonical schema and a registry that maps every observed variant onto it, rather than reacting file-by-file. Calibrated against the ledger (G10): a peer of maverick-spend (4) and ab-test-readout (4), which likewise add one conditional layer of judgment to a data task; and newsvendor-stocking (4). It sits above the single-mechanic 3s like duplicate-invoices, because the Part B twist — a feed whose drift your Part A map doesn’t cover — forces you to generalise the reconciliation, not just apply it.

Time is separate from difficulty: Part A is about an hour; Part B is 2–3 hours. The scripts run in under a second.

3. What you’ll be able to do after

Spot silent concat corruption — split columns from a rename, nulls from an added column, object dtype from a retype.
Reconcile to a canonical schema — a rename registry, type coercion, and add-missing handling.
Coerce messy types — parse "1,234.50", integer cents, and "$1,234.50" to a clean float.
Generalise a loader — extend the registry to cover new drift without breaking the days that already worked.

The finished result

By the end of Part A you’ll have watched a naive append quietly corrupt a table, then rebuilt it whole with a canonical-schema loader:

naive concat of 5 days:  7 columns  (a rename split `cust`/`customer_id`; `amount` has 300 nulls,
                                      so its sum silently misses the string-format days)
reconciled to canonical: 5 clean columns, 750 rows, amount a float with 0 nulls
                         -> total $1,894,621.55, nothing dropped

The naive version looks fine and sums wrong; the reconciled one keeps every row and every dollar.

4. Prereqs & time box

You can write a function and use a pandas DataFrame (read_csv, concat, astype, string methods). No data-engineering background needed — the primer above covers the vocabulary.

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, which runs 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.
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. One command creates both feeds:

python data/make_data.py

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

Part A — feed_a_day1.csv … feed_a_day5.csv (750 rows total). The id column is cust then customer_id; channel appears from day 2; amount is a float then amount_str.
Part B — feed_b_day1.csv … feed_b_day8.csv (1,200 rows). Adds a new id name (client) and two new amount formats (amount_cents, amount_usd).

Canonical target schema (both): customer_id, date, amount (float), region, channel.

6. Part A — Guided Build

You’ll reconcile the 5-day feed. Run the whole thing, or (better) one # %% cell at a time — see Start Here:

python part_a/reconcile.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.

The full, commented script is in part_a/reconcile.py. The spine:

Step 1 — Five days, five schemas. Print each day’s columns.

Checkpoint: the id column renames cust → customer_id; channel is missing on day 1; amount becomes amount_str on days 4–5.

Step 2 — The naive concat. pd.concat the raw days and inspect.

Checkpoint: 7 columns; cust has 450 nulls and customer_id 300 (the rename split it); amount has 300 nulls (the string days), so its sum silently misses them.

Step 3 — Reconcile to canonical. Rename registry + amount parser + add-missing.

Checkpoint: one canonical schema, 750 rows, amounts parsed to float.

Step 4 — Verify. Row count, dtype, total.

Checkpoint: 750 rows, amount float with 0 nulls, total $1,894,621.55, day-1 channel filled blank. Writes part_a/unified_a.csv.

Why this and not that

Why concat corrupts silently. pd.concat aligns by column name. A renamed column has two names, so it becomes two columns; a retyped column mixes types into object. Nothing raises — you get a table that looks fine and sums wrong.
Why a canonical schema + registry. Reacting to each file ad hoc doesn’t scale past a few variants. Declaring the target schema once and mapping every source name/type onto it makes the loader auditable and extensible — which is exactly what Part B tests.
Why coerce, not drop. The string and (in Part B) cents/currency amounts are real data. Parse them to the canonical float; dropping the rows or the column loses money you were paid.

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

Function	What it does here	Docs
`pd.concat(frames)`	Stack the days (and expose the naive-merge damage)	concat
`df.rename(columns=...)` / a name map	Map a day’s id column to the canonical name	rename
`s.str.replace(...)`	Strip commas / `$` before parsing an amount	str.replace
`s.astype(float)`	Coerce a cleaned amount to float	astype
`df.reindex(columns=...)`	Force the canonical column set, adding missing as null	reindex

If something looks off

The naive concat (Step 2) looks broken — extra columns, lots of nulls. Did I do it wrong? No — that broken table is the lesson. pd.concat aligns by column name, so a renamed column becomes two half-empty ones and a retyped amount goes null. Step 3 is where you fix it.
The day-1 channel is blank, not missing. Intended — channel didn’t exist on day 1, so the loader fills it blank rather than dropping those rows. Blank means “not tracked yet.”
A checkpoint number doesn’t match. The data is seeded, so the counts and the $1,894,621.55 total are exact. Re-run python data/make_data.py, then re-run Part A.
Setup problems (Python not found, pip, FileNotFoundError, 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.

Same core skill, a feed that drifts further than your Part A map covers. Reconcile the 8-day feed_b (1,200 rows) to the canonical schema and write the unified table. The starter reuses Part A’s registry unchanged and reconciles only ~3 of 8 days — feed_b adds a new id name (client) and two new amount formats (amount_cents integer cents, amount_usd like "$1,234.50").

The transfer: in Part A you handled the drift you saw; here new sources appear, and your loader has to be extended to map them onto the same canonical schema — without breaking the days that already worked.

Acceptance criteria (checkable)

Your solution writes into part_b/out/:

unified.csv — the reconciled canonical table (customer_id, date, amount, region, channel).
report.json — with keys rows, achieved_metric (you can leave achieved_metric null; the grader scores you).

Then:

python part_b/starter.py
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.

You’re done when the tests pass: your unified table reproduces ≥ 90% of the true canonical rows (the reference reaches 1.00), and the row count matches. Part A’s registry alone reconciles ~38% and fails.

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

Map every id column name (cust, client, customer_id) to canonical customer_id.
Parse every amount format (amount, amount_str, amount_cents, amount_usd) to a float.
Keep every row (1,200), and emit exactly the canonical columns.
Fill channel for the day that predates it, rather than dropping those rows.

Hints

Hint 1 — where Part A falls short

Run the starter and diff its columns/values against a day that uses client or amount_cents. Those days come through with a missing id and a null amount — the registry doesn’t know their names.

Hint 2 — extend the registry

Add "client": "customer_id" to the id-name map, and add branches to the amount parser: amount_cents is integer cents (divide by 100), amount_usd is "$1,234.50" (strip $ and , then float).

Hint 3 — key off the column name (still not the code)

Each day’s amount column is named for its format, so pick the parser by which amount column is present. Same for the id: find whichever of the known id names is in the frame and rename it.

8. Self-check

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

pytest reports row accuracy ≥ 0.90 (aim for 1.00 — every format is exactly reversible).
Your unified customer_id has no ?/null placeholders and amount has no nulls — every day’s id and amount were mapped.
Re-running from python data/make_data.py reproduces the same numbers — the data is seeded.

And the tell-tale: your total amount should be about $2.95M. If it’s far lower, some days’ amounts came through null (an unparsed format) and quietly dropped out of the sum.

9. Stretch

A schema registry file. Move the id/amount mappings into a small YAML/JSON registry the loader reads, so onboarding a new source is a config change, not a code change.
Fail loud on the unknown. Detect a column name the registry doesn’t recognise and raise with a clear message, instead of silently nulling it — the difference between a safe pipeline and a silent one.
(Genuinely hard) A column split. Add a feed where region arrives as "West/US" (region and country combined) and must be split into two canonical columns — reconciliation that changes the shape, not just the name/type.

10. Ship it

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

State the finding in one sentence an engineer gets: “a naive concat silently dropped ~40% of the revenue when the feed’s amount format changed; a canonical-schema loader recovered all $2.95M.”
Show the naive concat’s broken columns next to the reconciled canonical table.
Include the registry (id-name map + amount parser) and one paragraph on why appending raw files corrupts silently. That paragraph is what signals you understand schema drift, not just concat.

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	Synthetic drifting daily transaction feed (Part A 5 days/750 rows; Part B 8 days/1,200 rows)
Type	Synthetic
Generated by	`data/make_data.py` (this repo), seeds `20260737` (A) and `20260738` (B)
Source URL	Not applicable — generated, not fetched
License	Not applicable — synthetic, no third-party rights
Access / generation date	2026-07-18
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 drift was injected on purpose — reconciling it demonstrates the technique, not a claim about any real feed.

Next up

Finished this one? Continue the Data Engineering track:

Vendor Dedupe: one row per real company from a messy supplier master → · Browse all courses