knaxuslearn · build · ship

Roadmaps / Data / The milestone project

Read first

The milestone project

Scenario. An online shop has a Postgres database of orders, customers and products, and a stream of click events. Analysts need daily revenue and conversion tables, a live view of orders in the last hour and the ability to answer "what did we know on 3 March" questions. There are 5 million orders in the core scope (50 million for the senior scope) and 2,000 click events a second. Targets: a re-run of any day produces byte-identical results in the table; daily revenue reconciles with the source database to within 0.01 percent; change capture lag is 10 s or less at p95; events up to 2 hours late are counted in the right day; a bad row (negative price, missing key) is caught by a test and never reaches the reporting tables; a deletion request removes a user from every table within 24 hours; a 90-day backfill finishes in a time you set and report.

PhaseCheckpointDone when
1StoredOrders are written as Parquet in an Iceberg table, partitioned by day. A table shows query time and bytes scanned for a CSV, plain Parquet and a partitioned and sorted table, with your predictions beside the measured values. A schema change (a new nullable column) does not break an old reader. A seeded small-files problem (10,000 tiny files) is measured, then fixed by compaction, with before and after query times.
2Batch correctA daily job builds the revenue and conversion tables with dbt or SQL. Running a day twice gives identical results (a hash check). Revenue reconciles to the source within 0.01 percent. Tests catch a seeded negative price, a duplicate order and a missing foreign key before the report table is published. A 90-day backfill runs on a copy and the time is reported.
3StreamingChanges from Postgres reach the lake through Debezium, Kafka and a streaming job. Lag is reported at p50 and p95 over a 10-minute run at 2,000 events a second. Late events within 2 hours land in the right day, and a test sends 1,000 late events to prove it. A kill of the streaming job and a restart yields no lost and no duplicate rows (a row count and key check). A seeded duplicate-delivery bug is caught by that check.
4Governed and cheapEach table has an owner, a freshness target and a data contract that a test enforces. A lineage graph shows where revenue comes from. A deletion request for a fake user is carried through every table and verified. A cost table gives the bytes scanned and the estimated cost of the five most expensive queries and one fix is applied and measured. A memo says what you would change for a team of ten.
WWork version (Staff and Principal)On a real pipeline: add one gate (reconciliation against the source, a data contract test or a freshness alert), fix what it finds and record the result. Pass: a target agreed in writing beforehand is met, or the number of bad-data incidents on the chosen tables falls at least 5 times over the same period. The ADR is signed by a named stakeholder, scored by someone other than you, and a recheck is booked three months later. Timebox: six weeks alongside Phases 3 and 4.

Evidence to hand in. A repository whose README opens with the problem and the numbers above; one ADR (architecture decision record) per decision, each naming a rejected option; raw query outputs and test reports; a one-page design doc; a diagram of the flow from source to reporting table. Skeletons: Checkpoint 1 is the three-way table below with your numbers. Checkpoint 2 is the reconciliation report and the hash comparison of two runs. Checkpoint 3 is the lag report and the kill-restart check. Checkpoint 4 is the lineage graph and the deletion verification.

LevelWhat you build and hand in
FresherCore scope: 5 million orders, one engine, change capture for one table, the four checkpoints, ADRs 1 to 4.
SeniorAdds 50 million orders, a streaming job with late data, a second engine for comparison, a benchmark in CI and ADRs 5 to 7.
StaffAdds written decisions with numbers: a data contract policy, a cost model and a platform migration plan.
PrincipalAdds a decision on a data platform for several teams, with a measured outcome from the work version and a checklist other teams reuse.

Do this at work. A solo lab is not promotion evidence. Take a real pipeline and run Checkpoint W on it. Start with a baseline over a month: bad-data incidents, the time to find each and the number of tables with no owner. Make one change. Measure again. A promotion committee accepts the real decision and who made it, the named stakeholder who relied on it, before and after numbers, and a result checked again three months later.

Solo version. With no real pipeline, use a public dataset (for example a public taxi trip dataset) and a generated change feed from a small Postgres database. Ask a peer or mentor to check your reconciliation. Generate orders and late events with a seeded random generator.

Laptop scale. 5 million orders fit on a laptop. 50 million do with Parquet and a laptop engine if you have the disk. A real cluster is not needed for the checkpoints; report the machine you used and extrapolate cost per terabyte with the engine's published pricing, not a guess. Nothing needs cloud spend.

Honest sizing. A basic pipeline from a CSV to a report table takes an afternoon. The checkpoints add re-runnability proofs, reconciliation, change capture with failure tests, lineage and deletion, which is where the hours go. Phase 3 is the heaviest for a fresher (Kafka, change capture and a streaming job together), so use the extra fresher week there.

Worked reference: what a "3 Proven" hand-in for Checkpoint 1 looks like

The numbers are illustrative. The shape (columnar formats and partitioning cut scanned bytes by orders of magnitude, and many tiny files undo the gain) is what you should expect. Hand in your own measurements.

5 million orders, query: revenue for one dayCSVParquet, one fileIceberg, daily partitions, sortedIceberg, 10,000 tiny files
Bytes scanned640 MB38 MB2.1 MB2.4 MB
Query time, median of 10 runs4.8 s0.52 s0.09 s1.9 s
Files opened11210,000
Storage on disk640 MB120 MB126 MB180 MB

ADR-2 excerpt: Partition orders by day and compact files nightly. The daily partition cut the bytes scanned for a one-day query from 38 MB to 2.1 MB and the time to 0.09 s. The tiny-files case scanned about the same bytes but opened 10,000 files, so the time rose to 1.9 s, and storage grew by half from per-file overhead. A nightly compaction restores the fast case. Rejected: no partitioning, because most queries filter on a day. Rejected: partitioning by hour, which at this volume makes files of a few hundred kilobytes and recreates the tiny-files problem. Revisit when daily volume grows tenfold, or when most queries filter by customer and not by day.