Production Python for Data Engineers

Capstone Project — Do It Yourself

About this document

This document contains no code. It won't tell you what to type — it will tell you what to build, in what order, and why that order makes sense as one real project, not nine disconnected assignments.

Read 01-requirement-and-design.md first if you haven't. This document assumes you already know what you're building; it's the "how to actually go about it" that was missing before.

You'll still recognize where each phase's skill came from — Module 3 taught retries, Module 7 taught concurrency, and so on — but you're not building module by module anymore. You're building a system, and pulling in whichever skill the system needs next.

How to use this document

Each phase below has the same four parts:

  • Why now — what the previous phase leaves unfinished, that this phase exists to solve
  • What you're building — a concrete checklist, referencing the design document
  • Skill this draws on — which module to revisit if you've forgotten the technique; this document won't re-teach it
  • Compare against the reference solution — which file to check your decisions against, once your own version is working

Do the phases in order. Don't peek at a later phase's reference-solution file before you've attempted that phase yourself — the comparison is only valuable if you have your own honest attempt to compare it against.

Using an AI assistant while you build

You've already practiced the closed loop this course teaches — propose, run, review, iterate — inside each module's AI-assisted round. Nothing about that changes here. What's different is scale: this is one continuous project, not a single scoped task, so it's worth being deliberate about where an AI assistant genuinely helps and where it doesn't.

Where it genuinely helps:

  • Scaffolding — a pyproject.toml, a package skeleton, boilerplate you already know the shape of
  • A first draft of a function, once you've already decided what it needs to do — not before
  • Writing test skeletons for cases you've already identified
  • Explaining an unfamiliar error message
  • Repetitive, mechanical work — wiring the same pattern across several similar functions

Where it doesn't — keep these for yourself:

  • The actual design decisions. 01-requirement-and-design.md exists so those decisions are already made before you write a prompt; don't hand that thinking off.
  • Verifying anything is true. If an assistant tells you a fix works, that's a claim, not a fact — run it yourself, the same discipline as every module's manual lab.
  • Understanding why something works. If you can't explain a piece of your own capstone in an interview, an AI having written it correctly isn't worth much.

A practical approach, phase by phase: give your assistant that phase's "what you're building" checklist as context, let it propose an implementation, then use that same phase's compare against the reference solution step as your actual review step — not a rubber stamp. Checking AI-generated code against a real, working reference implementation is a more rigorous review than eyeballing a diff alone, and it's exactly the muscle this course has been building since Module 1.

Keep the guardrail log going, for the whole build, not just once. This project doesn't have a single designated "AI-assisted round" the way a recorded module did — you'll likely use AI assistance across several phases. Whenever you genuinely catch something — a reused pattern that got reinvented instead, a test that claimed more than it proved, a fallback default quietly reappearing — write it down in AI_GUARDRAIL_LOG.md, the same real, specific way you did for every module. A capstone-wide log full of real catches, spanning the whole build, is a stronger interview asset than nine separate, smaller ones.

Phase 1: Foundation — project structure and packaging

Why now: every other phase needs somewhere real to live. Before writing any logic, the project needs to actually be an installable package, not a folder of loose scripts.

What you're building:

  • A proper src/ layout for your capstone package
  • A pyproject.toml with a real build system and a CLI entry point
  • A pipeline CLI with an ingest --date command that, for now, does nothing more than print what it would do

Skill this draws on: Module 1.

Compare against the reference solution: pyproject.toml and the overall folder structure under src/pipeline/. Check specifically: does your layout match the src/ pattern, and does pip install -e . actually make your CLI runnable from any directory?

Phase 2: The data contract — what a valid record looks like

Why now: you can't build anything that touches real data until you know precisely what "valid" means. Defining this now, before any fetching or writing exists, forces you to actually read the mock API's real output first, rather than guessing at a schema later.

What you're building:

  • A pydantic model matching exactly what the mock API actually sends — refer back to Part 2 of the design document for the real response shape
  • Nested models where the data is genuinely nested, not flattened loosely into a dict
  • The narrowest correct types you can justify — Literal where a field only ever takes a small, fixed set of values, not str everywhere

Skill this draws on: Module 2.

Compare against the reference solution: models.py. Check specifically: did you model the nested payload as its own model? Did you use Literal for currency and status, based on values you actually observed, not assumed?

Phase 3: The walking skeleton — prove the wiring works, minimally

Why now: this is the phase the old, fragmented structure skipped entirely, and it's arguably the most important one. Before adding any resilience, any persistence, any scale — prove that fetching one page and validating its records actually works, end to end, in the simplest possible way. A system that's minimally correct is a much better foundation than one where every piece was built to be robust in isolation but never actually connected until the very end.

What you're building:

  • A small script (not the final CLI yet) that fetches page 1 for a real date, runs each record through your Phase 2 model, and prints how many validated successfully versus how many didn't
  • Nothing about retries, nothing about the database yet — just proof that fetching and validating genuinely work together, against the real mock API

Skill this draws on: this is integration, not a new module's skill — it's the moment Modules 1 and 2's work actually meet for the first time.

Compare against the reference solution: there's no single file to compare here — this phase doesn't exist as a separate file in the reference solution, because by the time that project was finished, this step was folded into the real fetcher. That's fine; the point of this phase is the proof, not a deliverable you keep. Move on once you've seen real validated and real rejected records printed from a real request.

Phase 4: Resilience — classify failures, add retry

Why now: your walking skeleton just made a real network call. Real network calls fail — sometimes in ways worth retrying, sometimes not. This is the first place your system needs to make that distinction for real.

What you're building:

  • An exception hierarchy distinguishing retryable failures (timeouts, rate limits) from non-retryable ones (a fetch that will never succeed no matter how many times you try it)
  • A retry helper with real exponential backoff and jitter, that only retries what's actually classified as retryable
  • Verification against the mock API's real rate limit — force a 429 and confirm your system honors the real Retry-After header, not blind guessing

Skill this draws on: Module 3.

Compare against the reference solution: exceptions.py and retry.py. Check specifically: is your RETRYABLE_EXCEPTIONS tuple genuinely narrow, deliberately excluding validation-style failures? Do you honor Retry-After directly, or are you guessing with backoff alone?

Phase 5: Idempotent persistence — write to Postgres safely

Why now: you can now fetch and validate reliably. The next real gap is: what happens when you run this twice? Nothing you've built so far prevents duplicate writes.

What you're building:

  • A writer that upserts a validated record into warehouse.records, using INSERT ... ON CONFLICT DO UPDATE — not check-then-insert
  • A real, manual proof: run your writer against the same record twice, and confirm directly in Postgres that exactly one row exists

Skill this draws on: Module 4.

Compare against the reference solution: writer.py. Check specifically: does your ON CONFLICT clause target the actual primary key, external_id? Does a second write update the row, or does it silently do nothing when it should reflect new data?

Phase 6: Prove correctness — the real test suite

Why now: everything so far has been verified by hand. That doesn't scale, and it doesn't survive you forgetting to re-check something after a later change. This is the phase where "I checked it once" becomes "there's a test that checks it every time."

What you're building:

  • Unit tests for your model validation logic — no network, no database
  • An integration test against the real Postgres container, proving your Phase 5 upsert is genuinely idempotent — not by rerunning manually, but with a real, automated assertion
  • A property-based test proving some invariant about your data holds across many generated inputs, not just a few hand-picked ones
  • At least one explicit failure-path test — feeding in something invalid and asserting the specific rejection reason, not just that something failed

Skill this draws on: Module 5.

Compare against the reference solution: the tests/ folder as a whole. Check specifically: does your integration test actually touch real Postgres, or does it quietly mock away the exact thing it's supposed to prove? Do you have a test for the case where validation should fail, not just where it should succeed?

Phase 7: Observability — structured logging

Why now: you have a system that works and is tested. If it broke right now, in a way your tests didn't anticipate, could you actually diagnose it from what it prints? Almost certainly not yet — this phase exists to fix exactly that gap before it costs you a real debugging session later.

What you're building:

  • Structured logging (not print) across every real step — fetching, validating, writing, dead-lettering
  • A run_id, generated once per run and bound to every log line from that run, so a real failure can be traced to one specific run, not guessed at from a shared log stream
  • A hard rule you can point to: no field that could be considered PII ever appears in a log line

Skill this draws on: Module 6.

Compare against the reference solution: logging_config.py, and how cli.py uses it. Check specifically: is run_id bound once, near the top, or passed manually into every call? Would a single log line, read in isolation, tell you which record failed and why?

Phase 8: Scale — bounded concurrency and chunking

Why now: everything so far has been proven against one page, or a small number of records. A real ingestion run touches hundreds of pages. This phase is where "correct" becomes "correct at the scale this will actually run at."

What you're building:

  • A fetcher that pulls multiple pages concurrently, bounded by a semaphore sized sensibly below the mock API's real rate limit
  • A generator-based chunking mechanism for processing a full date's worth of records without holding the entire dataset in memory at once
  • Real, measured proof: time a bounded-concurrent fetch against a naive sequential one, and write down the actual numbers

Skill this draws on: Module 7.

Compare against the reference solution: fetcher.py. Check specifically: is your concurrency limit tied to a real, considered number, or an arbitrary one? Did you actually measure a before/after timing difference, or are you assuming the bounded version is faster?

Phase 9: Safe configuration — typed, fail-fast settings

Why now: at this point your system has real configuration scattered through it — the mock API's URL, Postgres credentials, concurrency limits. This phase collects all of that into one place, and closes the most dangerous gap left in the whole system: what happens if something required is missing?

What you're building:

  • A single typed settings class covering every real configuration value your system needs
  • A hard rule, enforced by the type system itself: safe defaults for things like hostnames, no default at all for secrets
  • A real, deliberate test: unset your database password and confirm your system refuses to start, loudly, immediately — not a silent wrong run

Skill this draws on: Module 8.

Compare against the reference solution: settings.py. Check specifically: does every secret field genuinely have no default? Does unsetting a required value actually crash at startup, before any real work happens?

Phase 10: Full integration — the real ingest command

Why now: every piece above now exists and works on its own. This phase is where they actually become one system — the moment your pipeline ingest --date command stops being a stub and starts doing everything Phases 2 through 9 built, in the order the design document's sequence diagram describes.

What you're building:

  • The real ingest command: load settings, bind a run ID, fetch every page (bounded, retried), validate each record, upsert the valid ones, dead-letter the rest, log a final summary
  • A genuine end-to-end run against real infrastructure — not a partial demonstration of one piece

Skill this draws on: this phase doesn't belong to one module — it's where every earlier phase's work actually has to agree with every other phase's, which is exactly the kind of bug no single module's lab could have surfaced on its own. If something breaks here that worked fine in isolation, that's not a failure — that's the real value of this phase.

Compare against the reference solution: cli.py, read start to finish. Check specifically: does the order of operations match the design document's sequence diagram? Does a single malformed record anywhere in a real run still allow the rest of the run to complete?

Phase 11: Automate verification — the CI pipeline

Why now: everything above has been verified by you, running commands, watching output. This phase makes sure that verification keeps happening automatically, on every future change — including ones you make carelessly, under deadline pressure, without thinking to re-check everything by hand.

What you're building:

  • A real GitHub Actions workflow: install, lint, type-check, test — no swallowed exit codes anywhere
  • Proof, on your actual GitHub repository, that a genuinely broken change gets blocked, and a genuinely fixed one gets allowed through

Skill this draws on: Module 9.

Compare against the reference solution: .github/workflows/ci.yml. Check specifically: does every step's command exit with a real, unmodified exit code? Have you actually watched your own pipeline turn red on a real mistake, on GitHub itself, not just assumed it would?

Phase 12: Final review

Why now: this is the only phase that isn't about building something new — it's about confirming, honestly, that everything above actually holds together as one real system.

What to do:

  1. Walk your own SPEC.md, row by row. For each one, ask yourself: do I actually know this is true, or am I assuming it?
  2. Confirm your AI_GUARDRAIL_LOG.md has real entries from real sessions, across your actual capstone build — not filled in generically.
  3. Record your final walkthrough: briefly show the system running, defend three of your own real design decisions out loud, and walk through one real moment from your guardrail log where you caught an AI-generated mistake.

Once this phase is done, you haven't completed nine separate module exercises. You've built one real, working system, end to end, and you can prove every claim about it.