Capstone Project — Requirement and Design Document

About this document

This document exists on its own, meant to be read carefully before you write a single line of the capstone. It answers three questions, in order: what are you actually building, what's already been built for you, and how should the thing you build be shaped.

You won't find any application code here. This is the spec and the design — the "what" and the "why." The "how, step by step" lives in the companion document, 03-do-it-yourself.md. A complete, working answer lives in the reference solution.

Part 1: The requirement

The scenario

You're a data engineer joining a team that needs to pull transaction records from a third-party API — one that's realistic in every way that matters: it's paginated, it occasionally times out, it rate-limits you, and a small percentage of the records it sends are simply malformed. Your job is to build a service that ingests these records reliably, every day, and lands them correctly in a warehouse table — without ever duplicating a record, without ever crashing over one bad row, and without ever silently doing the wrong thing.

This isn't a toy problem invented for this course. Every constraint described above is something you will run into on a real data engineering team, usually within your first few months.

Functional requirements

Your capstone must, given a date:

Fetch every record for that date from the mock API, correctly handling pagination — the API will not hand you everything in one response.
Validate every record against a well-defined schema before trusting it. Some records will be malformed on purpose. Your system must recognize this and handle it without crashing.
Persist every valid record into the Postgres warehouse table, such that running the exact same ingestion twice, for the exact same date, leaves the table in exactly the same state as running it once — no duplicates, no partial double-application.
Route invalid records to a dead-letter path — recorded somewhere inspectable, not silently dropped, and not allowed to crash the rest of the run.
Survive realistic upstream failures — timeouts and rate limits — by retrying only what's genuinely worth retrying, with real backoff and jitter, and failing fast on anything that will never succeed no matter how many times you try it.
Run efficiently at real scale — fetching many pages concurrently, bounded to a safe limit, without needing to hold an entire large dataset in memory at once.
Be safely configurable across environments, with zero hardcoded secrets, and a hard failure at startup if anything required is missing.
Be genuinely testable — unit tests for pure logic, integration tests against the real database, a property-based test proving an invariant, and explicit tests for your failure paths, not just your happy path.
Produce logs someone could actually debug from — structured, with a run ID tying every line from one run together, and never containing anything that shouldn't be logged.
Be automatically verified on every change, via a CI pipeline that genuinely fails when something is broken — not one that merely appears to.

Non-functional requirements

These aren't separate line items to build — they're qualities the whole system needs to have, and they'll show up in how you evaluate every design decision below:

Idempotency — the single most important property of this system. Every other requirement can be slightly wrong and cost you some rework. This one being wrong can silently corrupt real data.
Observability — if this broke at 3am, could a stranger diagnose it from the logs alone?
Resilience — real upstream systems fail in specific, recognizable ways. Your system should recognize which failures are worth retrying and which aren't.
Safety — a misconfigured deployment should fail loudly and immediately, never silently and expensively.
Testability — every claim about correctness in this document should be something you can point to a real, automated test for.

Explicitly out of scope

Worth naming directly, so you don't over-build this: you are not building a general-purpose ETL framework, a UI, a scheduler, or multi-tenant support. This is one focused ingestion service, done correctly — not a platform.

Part 2: What's already provided, and how to use it

You are not starting from an empty folder. Two pieces of real infrastructure already exist in your pp4de repo, from Step 1 of this course. This section is a complete reference for both — read it now, so you're not discovering this API's behavior by trial and error while also trying to design your system.

The mock API

A real, running FastAPI service, available at http://localhost:8000 once you run docker compose start.

Endpoint: GET /records

Parameter	Type	Default	Meaning
`date`	string, required	—	The date to fetch records for, `YYYY-MM-DD`
`page`	integer	`1`	Which page of results to fetch
`chaos`	boolean	`true`	Whether realistic flakiness is enabled at all
`force_429`	boolean	`false`	Force this specific request to return a rate-limit error
`force_timeout`	boolean	`false`	Force this specific request to hang

A successful response looks like this:


json
{
  "date": "2026-07-01",
  "page": 1,
  "page_size": 50,
  "total_records": 537,
  "total_pages": 11,
  "records": [
    {
      "external_id": "txn_20260701_000000",
      "customer_id": "cust_0283",
      "amount": 4259.39,
      "currency": "INR",
      "status": "completed",
      "source_created_at": "2026-07-01T22:38:00Z",
      "source_updated_at": "2026-07-01T22:52:07Z",
      "payload": {
        "channel": "web",
        "retry_count": 2
      }
    }
  ]
}

Behavior worth designing around, not discovering by accident:

Pagination is real. total_pages tells you how many pages exist for that date. You must fetch all of them, not just the first.
A small percentage of records are deliberately malformed — missing fields, wrong types, or invalid enum values (like a currency code that doesn't exist). This rate is roughly 3-5% and varies by run. Your system must expect this on every single fetch, not treat it as an edge case.
Rate limiting is real, not simulated after the fact — by default, 10 requests per 10-second window. Exceed it, and you'll receive a genuine 429, with a real Retry-After header telling you exactly how long to wait. Honor that header directly; don't guess with blind backoff.
Timeouts happen naturally, at a small default rate, and can be forced with force_timeout=true for testing your own handling deliberately.
chaos=false turns off delivery flakiness (rate limits, timeouts, pagination quirks) — but does not turn off malformed records. That distinction is intentional; verify it yourself rather than assuming.

Try it yourself, right now, before designing anything:


bash
curl "http://localhost:8000/records?date=2026-07-01&page=1" | python3 -m json.tool

The Postgres warehouse

A real Postgres 16 instance, available once docker compose start is running. The schema already exists — you didn't create it, and you shouldn't need to modify it:


sql
CREATE SCHEMA IF NOT EXISTS warehouse;

CREATE TABLE IF NOT EXISTS warehouse.records (
    external_id       TEXT PRIMARY KEY,
    customer_id       TEXT NOT NULL,
    amount            NUMERIC(12, 2) NOT NULL,
    currency          TEXT NOT NULL,
    status            TEXT NOT NULL,
    payload           JSONB NOT NULL DEFAULT '{}'::jsonb,
    source_created_at TIMESTAMPTZ NOT NULL,
    source_updated_at TIMESTAMPTZ NOT NULL,
    ingested_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

Notice external_id TEXT PRIMARY KEY. This has been sitting there since your very first day with this repo, waiting for the module where it would matter. It's the actual foundation your idempotency guarantee gets built on — a real database-level constraint, not something you have to invent.

Connect to it directly, to see it for yourself:


bash
docker exec -it pp4de_postgres psql -U pipeline_user -d warehouse -c "\d warehouse.records"

Part 3: Detailed design

Architecture overview

Rendering diagram...

Notice the shape of this: every real component reports to the logger, but the logger doesn't control anything — logging is a cross-cutting concern, not a step in the pipeline. Settings, similarly, gets consulted once, at the very start, not threaded awkwardly through every function.

Component responsibilities

Component	Responsible for	Explicitly not responsible for	Course module this draws on
CLI	The entry point; wiring everything else together; printing a final summary	Any actual business logic	Module 1
Settings	Declaring every required and optional config value; failing immediately if something required is missing	Anything beyond configuration	Module 8
Models	Defining what a valid record looks like; rejecting anything that doesn't match, with a clear reason	Deciding what happens to a rejected record	Module 2
Fetcher	Retrieving every page for a date; bounding concurrency; classifying and retrying failures correctly	Validating record contents; writing anything	Modules 3, 7
Dead Letter	Recording a rejected record and why, so the run can continue	Deciding whether a record should have been rejected	Module 4 (the same "give failures somewhere to go" idea)
Writer	Persisting a valid record such that re-running never duplicates it	Validating the record first	Module 4
Logger	Making every step's outcome traceable to one run, without leaking sensitive data	Making decisions based on what it logs	Module 6
Tests	Proving every claim in this document is actually true	—	Module 5
CI	Automatically re-proving all of the above, on every change	—	Module 9

The ingestion sequence

Rendering diagram...

Two things worth noticing about this sequence, since they're easy to get subtly wrong:

Validation happens after all fetching, but nothing about that order is required — you could equally validate each page as it arrives. What is required is that a validation failure never stops the records around it from being processed.
The retry loop lives entirely inside the fetch step. By the time a record reaches the validator, network-level failures have already been resolved one way or another. The validator only ever sees "this fetch succeeded" — it should never need to know anything about retries.

Design decisions worth understanding, not just copying

Why upsert instead of check-then-insert? Covered in depth in Module 4 — a check-then-insert has a real race condition under concurrent writers. INSERT ... ON CONFLICT DO UPDATE is atomic; the database itself guarantees no duplicate can ever exist, regardless of timing.

Why classify exceptions instead of catching broadly? Covered in Module 3 — a broad except Exception can't distinguish "this is worth retrying" from "this will never succeed no matter how many times you try." Getting this distinction right is what separates a resilient system from one that wastes real time and load hammering a request that was never going to work.

Why bound concurrency and chunk large batches separately? Covered in Module 7 — these solve two different problems. A semaphore bounds how much work runs at once. Chunking (via a generator) bounds how much work exists at all at any given moment, which matters independently, at real scale.

Why does Settings have no default for the database password, but a default for the host? Covered in Module 8 — some configuration is genuinely safe to guess a default for; secrets and required connection targets are not. A missing password should stop the program instantly, not let it run with something wrong.

What "done" actually means

You'll know your design and implementation are complete when every one of the ten functional requirements in Part 1 has:

Real code implementing it
A real, automated test proving it
A place in your structured logs where its outcome is visible

If any requirement is missing one of these three things, it isn't actually done yet — it just looks done.