"Design Review III" — The Whole Platform, and the Interview Playbook

Skim Card

One thing to remember: a data platform is defended by the reconciliations it can rerun on demand; speed, serving and AI are copies that must reach the same answer.

• Close on exceptions, not green runs: Q3 closed with zero across six reconciliations, 683,490 bridge records classified, none unexplained.
• Every fast copy must reach the batch answer: Flink matched 774 of 775 store-days, the crash test every alert, three latency tiers row by row.
• Sized from measurements: 254 MiB of Iceberg a day, about 45 Kafka events a second; streams pay for idle memory and small files.
• Name the check for every failure, streaming and AI included: aborted transactions, mid-day prices, killed TaskManagers, leaked futures, pence reported as pounds.
• In the interview, walk fourteen steps in order, from clarifying questions to evolution; the boxes come after the numbers and the grain.
• Say what isn’t finished: contract roles bind one server, compaction undoes pruning, a replay resurrected an erased customer, streaming jobs start by hand.

Use …	When …
Batch over event time, reconciled to an outside source	The figure is signed, paid on or disputed
Streaming into the lake every minute, compacted	People check a screen between tasks
A seconds tier (real-time OLAP, Flink alerts)	A decision is lost if it waits a minute
Governed metric tools for agents	Answers must match finance’s

Verified against: PyIceberg 0.12.0 over Iceberg 1.12 tables in Apache Polaris 1.8.0, DuckDB 2.0.0, Trino 484 with Open Policy Agent 1.21.4, Cube Core 1.8.15, Python 3.13 · measurements from Parts 14–34 · vendor docs · 31 October 2026

Jump to: The review · The close · Self-host or buy · Playbook · Three worked prompts

Story Opening

Farah’s invitation said “Q3 close”, and under it, in Ingrid’s hand: “and the last review.”

Rohan opened with the streaming jobs, since they were the newest. Ingrid didn’t let that start. “Same order as the first time, for the whole platform now. What they asked for, how big, what we built, what breaks it, what isn’t done. Then Farah closes the quarter, live. Do it as if a stranger were interviewing you, because one will.”

The Review

Part 14 reviewed storage and engines; Part 25 the batch pipelines, and its tables still hold. This review adds what Season 3 built (streams, serving, data for models and agents) and asks one question of all of it: does every fast or convenient copy reach the answer the reconciled batch reaches?

1. Clarify: new products, new readers

Card-testing fraud signals, the brief’s last Season 3 product, were designed (Part 28’s drill) but not built for Larkspur.

2. Estimate: from the lab’s measurements

Three readings. Still not big data: 450 events a second is light work for a small Kafka cluster, so three brokers are for durability, not throughput. Streaming pays differently: the hourly tier’s cost is seconds of CPU; the others pay for memory that never sleeps and, in the lake, for files (metadata three times the data). Operations is the constraint: the lab never held batch, streams and serving at once.

3. Choose: the platform, end to end

4. Justify: Ingrid’s questions

“Why keep the batch? Stream everything.” Because the record needs inputs that arrive days later: settlement files, closed-day controls, restatements. The stream buys freshness and must earn trust by reaching the batch answer: Part 28 matched 774 of 775 store-days (the 775th a re-send from the day before), Part 29 matched its 9,833 alerts after a killed TaskManager, Part 30’s three tiers matched row by row. Elsewhere: card networks authorise in milliseconds and settle in a daily batch, and the batch is what gets reconciled.

“Every BI tool has a semantic layer. Why Cube?” Part 31 found four ad hoc revenue answers on the same tables with the same rights, and one from Cube’s definition that equalled dbt’s report on all 378 country-days. Part 33 showed agents need it most: given tables and SQL, the model answered revenue wrongly or not at all; given metric tools, 30 of 30 right.

“A second copy in ClickHouse? You preached one copy.” Only where seconds matter, with its own rules said out loud: duplicates collapse only at merge time, so exact answers need FINAL (Part 30). Everything else reads the lake. And every tier is a copy: ClickHouse, the minute tables, pgvector and the feature table each need a reconciliation and an erasure path.

5. Failure modes, and what catches each

Part 25’s table covers the batch. Season 3 added these:

6. Trade-offs, said out loud

• Exactly-once costs latency. Sinks publish at checkpoints; a ten-second interval means up to ten seconds of staleness (Part 29).
• Governed agents are narrower. Leadership got three metrics and no free SQL (Part 33).
• Self-hosting the whole stack is a team. Kafka, Flink, ClickHouse, Cube and the catalog each need an owner on call (Part 34).

7. The evolution path

Not finished, in the order Ingrid would fix them:

1. The net revenue contract’s roles bind only the MCP server (Part 33): a GB-scoped Cube token reads all three countries’ revenue. Push the rule down: an OPA policy on the revenue tables, or a semantic-layer policy.
2. Compaction undoes pruning. Part 19’s maintenance merges gold.fct_sales_line’s day files and Part 24’s tile then reads everything; partition or sort the dbt facts by day.
3. Two of Part 24’s three savings await deploy: the incremental report and window-scoped tests.
4. Erasure has gaps. Part 16’s CDC replay rebuilt the documented subject into three tables again (repaired in the close below; the durable fix is the suppression list in the CDC path). Kafka segments, the PostgreSQL WAL, laptop files and Trino’s query logs are still outside it (Part 23).
5. The agent never gave the conversion total, 0 of 20 runs, even after a tool description asked for it; nothing runs that evaluation when Cube changes (Part 34 left it unbuilt).
6. Only revenue runs under Airflow. Streaming jobs are started by hand on a session cluster, and the GitHub workflow runs only the host-level gate (its first run passed).
7. Part 25’s last three items stand: controls start on 30 August, Indian and Singaporean cash has no outside source, the supplier scorecard counts checked deliveries only.

The five promises, scored

This part strengthens completeness: the close accounts for every settlement record.

The Close

Farah’s Q3 figure was approved in Part 25. Closing means asking once more, against today’s lakehouse, whether anything needs explaining. An exception is one item a person must explain before the period closes: an unexplained amount, a settlement record on one of Part 21’s BUG lines, a failed control verdict, a total or report version off the board pack, a restatement unapproved or off the pack, a country-day where the served metric differs from dbt’s report. Each mismatch counts once. The close covers UK revenue, the one figure with an outside source (the PSP), plus the served metrics. scripts/part35_close.py in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-35) counts them with Parts 21, 25 and 31’s own code and writes nothing.

First, a repair. Part 16’s replay rebuilds shop.orders and its two lake replicas from regenerated source data, which brought back CGB0000113, the customer erased in Part 23 (one order, two change rows). reapply refuses anyone not in the erasure register, then runs Part 23’s own tombstone and clean steps only where rows came back, adding no register rows:

make up-cube                                                # core, Trino + OPA, Cube
uv run python -m scripts.part35_close reapply CGB0000113    # postgres shop.orders, raw.orders, raw.orders_changes
uv run python -m scripts.part23_privacy find CGB0000113
uv run python -m scripts.part23_privacy prove CGB0000113
uv run python -m scripts.part35_close close --served

find then showed 0 rows in all nine locations, and prove decoded 997 objects (818 MiB) with 0 still holding the subject. Until the suppression list reaches the CDC path, every Part 16 replay needs reapply. Then the close:

Q3 2026 close, read-only: reconciliation exceptions (2026-Q3)
   0  waterfall: board deck -> correct, five causes        correct 29,790,674.06, unexplained 0.00
   0  bridge: PSP settlement -> gold, per record           683,490 records classified, 0 on BUG lines
   0  daily controls: latest verdict per check             934 verdicts over 29 run days
   0  report: quarter tag, published, main                 3 versions = 29,790,674.06
   0  restatement 2026-Q3 GB                               approved, 29,999,232.43 -> 29,790,674.06
   0  served: Cube metric vs dbt report, per country-day   378 country-days, 3 board-pack figures
   0  exceptions; 2.4 s
UK net revenue Q3 29,790,674.06 (board deck 29,999,232.43, restated); conversion 134,223 / 2,161,515 = 6.21%

The 683,490 records are every classified line of the bridge (636,533 matched to the penny, the rest timing and FX); 2.4 s with Cube’s cache warm, 23.7 s on the first, cold run after Cube started. Controls are the latest stored verdict per check, day and country, written by Part 21’s daily runs from 30 August (July–August are covered by the bridge); the close doesn’t re-run control, which would overwrite them. Part 25’s read-only signoff passed 8 of 8 in 2.4 s (stock 88.5%, fill 93.1%, OTIF 77.7%), and history still ends at the approval snapshot 6985501091157430496: nothing was approved or restated again. tests/test_part35.py checks the counting rules on made-up inputs (each mismatch one exception), the zero-exception close, and that reapply refuses a subject never erased and writes nothing where the subject is still gone (12 passed in 7.6 s; containers peaked at 3.91 GiB for the whole sequence, Trino 2.27).

Self-Host or Buy

Judgement, built from the Landscape tables of Parts 26–33 (verified 30–31 October 2026), for the layer Season 3 added; Parts 14 and 25 cover storage and batch.

On every row, the reconciliation, the metric definitions and the agent’s permissions stay yours. Larkspur’s verdict: keep the open lake and Cube; buy the stream first if nobody wants to own Flink.

The Interview Playbook

Data system design prompts look different and share a skeleton. A backend design answer starts from services and APIs; a data design answer starts from the decisions people make and the records they need, because grain, layout and freshness follow from both. Walk these fourteen steps in order, out loud. In 45 minutes, a workable split (judgement) is 8 for steps 1–3, 20 for 4–10, 12 for 11–14, the rest for questions. Each step names the parts that taught it.

The method, as a checklist. Print it.

1. Clarify (Parts 14, 25, 30)

• Who reads what, to make which decision, by when, in which time zone?
• Which number gets signed, paid on or disputed, and against which outside source?

2. Requirements in numbers (Parts 14, 22, 23)

• Freshness per reader, retention per table, concurrency at peak, erasure deadline, budget?

3. Estimate (Parts 3, 14, 25)

• Rows a day × measured bytes a row, at today’s volume and ten times? Peak events a second?
• Files a day from commit cadence; which table nears ~1 GiB a day first?

4. Model the grain (Part 4, 5)

• One sentence per fact: what one row is. Snapshots semi-additive? Attributes as of the event?

5. Storage and table format (Parts 7, 8, 9)

• Raw kept as landed? Which open table format and catalog can every engine use?

6. Partitioning and layout (Parts 10, 24)

• Partition by how readers filter; does compaction keep the sort they prune on?

7. Ingestion (Parts 15, 16, 21, 27)

• Per source: files, API cursor, CDC or stream? What finds deletes? Which key makes a repeat harmless?

8. Processing, batch or stream (Parts 18, 26–30)

• The slowest tier that meets each decision? Event time, watermark and a lateness policy per output?
• Which sinks commit with the checkpoint, and do readers read only committed data?

9. Quality, completeness, reconciliation (Parts 20, 21)

• Contracts where data changes owner; counts, sums and hash totals per hop; an outside source per figure?

10. Serving (Parts 13, 31)

• One definition per metric; a serving tier for the peak; tenant isolation below the API?

11. Governance (Parts 23, 33)

• Personal data classified and enforced in every engine; erasure through copies, logs and indexes; agents through governed tools?

12. Cost (Parts 24, 30)

• Bytes, requests and CPU per query and run; what does idle cost; a unit cost per report?

13. Failure modes (Parts 25, 29)

• For each failure, the check that catches it and how you’d know the number is wrong?

14. Evolution (Parts 14, 25, 34)

• What changes at ten times, how reversible is it, and what isn’t finished?

Design Drill: Three Worked Prompts

Prompt 1 (public transport): the question from Part 0

Part 0 promised a full answer to this one.

The question: “A metropolitan transit authority wants a ridership platform. Card readers at station gates and on 4,000 buses report taps, the buses also send GPS pings every ten seconds, and 300 stations have footfall sensors. Planners combine all of that with the timetable and route network, which changes every quarter. They want ridership by route, stop and hour that counts every tap exactly once, kept for seven years, and operations wants yesterday’s figures by 6 a.m. Walk me through the design, and tell me how you’d prove nothing was lost or counted twice.”

Clarify first (with assumed answers)

• How late can taps arrive? Bus readers upload at the depot overnight; up to 12 hours.
• Do readers number their taps? Yes, a per-reader sequence.
• What is a journey? Taps by one card within 60 minutes, as planners define it.
• Outside source? The fare-clearing house’s daily totals per operator.
• Is a card ID personal data? Yes: a journey history reveals home and work.

Estimate: 5 million taps a day at ~40 bytes is 200 MB; 4,000 buses × 6,480 pings (18 hours) is 26 million pings, ~650 MB; footfall from 300 stations × 10 sensors × 1,440 minutes is 4.3 million counts, ~65 MB at 15 bytes. Under 1 GB a day, ~2.3 TB over seven years. One Trino cluster or a warehouse; no Spark.

The shape of a strong answer

• Grain: one row per tap (card, reader, sequence, event time, ingestion time); journeys are a derived table with the 60-minute rule written down, as Larkspur’s sessions contract was (Part 20). Boardings and journeys are different metrics with different names.
• Service day, not calendar day: GTFS, the common timetable format, writes trips after midnight as times past 24:00:00, so a 00:40 tap usually belongs to the previous service day; GTFS leaves the boundary to each agency (Part 5’s business date).
• Dedup key: reader ID + sequence, never the message ID; re-sends after an outage get one verdict each (Part 21).
• Bus taps have no stop: join the bus’s GPS position as of the tap time, and the route pattern from the timetable version in force that day (versioned dimension, as-of join; Parts 4, 28).
• Late taps: a microbatch lookback sized from measured depot delays; figures provisional for two days, closed days changed only by restatement (Part 18).
• Prove it: sequence gaps per reader (a missing number is a lost tap), daily totals against the clearing house, a waterfall for any difference (Part 21). Footfall sensors are estimates: a plausibility check, not reconciliation.
• Layout: taps months(service_date); pings days(event_ts) at ~650 MB a day.
• Governance: keyed pseudonyms for cards; card-level rows kept shorter than seven years, aggregates for seven (Part 23).
• Ingestion, storage, serving, cost: depot uploads and GPS batches land as files (no Kafka for a 6 a.m. batch), loaded into Iceberg through a REST catalog; planners read boardings and journeys from one semantic layer (Part 31); one Trino cluster or a warehouse, metered per query, is most of the bill at ~1 GB a day (Part 24).

Trade-offs to say out loud: two days of provisional figures against the 6 a.m. deadline; shorter card-level retention limits replays.

What would change it: live crowding on platforms adds a minute tier from gate taps (Part 30); the batch stays the record.

Common weak answers: counting taps as journeys; deduplicating by message ID; joining today’s timetable to last year’s taps.

Prompt 2 (logistics): tracking, SLAs and an assistant

The question: “A parcel carrier moves 6 million parcels a day through 40 hubs and 1,500 depots; each parcel is scanned 8–12 times, and couriers’ handhelds go offline in rural areas. Customers want live tracking and a delivery window; operations wants hub throughput by the minute; retailer contracts carry penalties when on-time delivery falls below 95% a month, and retailers dispute the figure. Product wants an assistant for ‘where is my parcel?’. Design the platform.”

Clarify first (with assumed answers)

• On time against what? The promised window at the time of booking, not a later re-promise.
• How late can scans arrive? Handhelds hold up to two days of scans.
• Do retailers send a manifest? Yes, a file of parcels handed over each day.
• Who may ask the assistant what? A signed-in customer, about their own parcels.

Estimate: 60 million scans a day, ~700 a second on average, perhaps 3,000 at the evening peak; at ~60 bytes, 3.6 GB a day, 2.6 TB over two years of dispute retention.

The shape of a strong answer

• Backbone: Kafka keyed by parcel ID, so a parcel’s scans stay ordered; a registry refusing breaking changes; each scan carries device time and receive time, with clock drift corrected per device (Parts 26, 27).
• Tracking is a point read, not analytics: Flink keeps each parcel’s latest state and writes a key-value store the tracking page reads (Part 29). Reconcile that store with the lake daily.
• Hub throughput: one-minute windows with a short watermark; late scans may miss a tile, never the record (Parts 26, 30).
• The penalty figure is batch: on-time computed over event time after the two-day lateness, defined once and served per retailer with row isolation (Part 31). Each month ships with the parcel list behind it, and every manifest parcel never inducted is an exception (Part 21’s bridge, per record).
• Delivery-window model: features joined at availability time (Part 32).
• Grain, storage, cost, personal data: one row per scan, and one per parcel delivery for the SLA; Iceberg with days(scan_ts) at 3.6 GB a day; streams run always-on for tracking only, retailer reports hourly (Part 30); addresses and delivery photos in a restricted table, deleted after the dispute window (Part 23).
• Assistant: governed tools only (parcel_status for the caller’s parcels, identity fixed outside the model); delivery notes are untrusted text, so no links or tool calls from them (Part 33).

Trade-offs to say out loud: a key-value copy beside the lake; device time is better than receive time but drifts; penalties wait two days for late scans.

What would change it: same-day delivery makes the window model’s freshness matter more than the history’s depth; penalties stay batch.

Common weak answers: computing on-time in the stream and calling it final; serving tracking from the warehouse; handing the assistant SQL.

Prompt 3 (gaming): anti-cheat, live ops and cohorts

The question: “An online multiplayer game has 8 million daily players in three regions. Game servers emit match events and every player’s position each second. Anti-cheat wants suspicious players flagged within a minute of a match ending, live ops wants concurrent players by region in seconds, designers want two-year retention cohorts, and matchmaking wants skill features. EU players can ask for erasure. Design it.”

Clarify first (with assumed answers)

• Does anti-cheat ban automatically? No: flags go to a review queue.
• How long do analysts need positions? Replays for 30 days; heatmaps from 10-second samples.
• Peak concurrency? About 15% of daily players, 1.2 million.

Estimate: 8 million players × 3 matches × 1,200 seconds is ~29 billion positions a day, ~580 GB at 20 bytes: positions decide the design. Peak 1.2 million a second, ~60 MB/s at ~50 bytes a message; one message per server tick, holding every player, cuts the message count.

The shape of a strong answer

• Retention is the cost lever: full-rate positions for 30 days, 10-second samples kept longer; match events and a player-day table (8 million rows a day) kept two years (Part 24).
• Anti-cheat: Flink keyed by match, firing on the match-end event with a timer as fallback; features to a review queue and to Iceberg as evidence, exactly once (Part 29).
• Concurrent players is a distinct count across servers: per-server counts double-count players who move; use HyperLogLog sketches merged per region (Parts 6, 26), in a seconds tier (Part 30).
• Ingestion, storage, serving: servers produce to a Kafka cluster per region, keyed by match, schemas in a registry (Part 27); Iceberg on object storage with days(event_ts) on match events; cohorts in batch over the player-day table through Trino or a warehouse, live ops from the OLAP store (Part 30).
• Skill features: one definition for training and serving; as-of joins for training (Part 32).
• Completeness: each server’s match sequence numbers; matches started vs ended per server.
• Erasure: a pseudonymous player ID with a mapping table; crypto-shredding with legal sign-off; evidence for bans kept only as legal allows (Part 23).

Trade-offs to say out loud: sampling loses replays after 30 days; per-region clusters make global live ops an aggregation of aggregations.

What would change it: automatic bans raise the bar to exactly-once evidence with human-readable reasons, and appeals need the replay kept.

Common weak answers: landing every position forever; summing per-server concurrency; one pipeline for anti-cheat and cohorts.

Further Reading

1. Martin Kleppmann and Chris Riccomini, Designing Data-Intensive Applications, 2nd edition (O’Reilly, 2026): the chapters on stream processing and derived data.
2. Joe Reis and Matt Housley, Fundamentals of Data Engineering (O’Reilly, 2022): the data engineering lifecycle, the skeleton of the playbook.
3. Akidau et al., “The Dataflow Model”, PVLDB 8(12), 2015. https://www.vldb.org/pvldb/vol8/p1792-Akidau.pdf
4. Armbrust, Ghodsi, Xin, Zaharia, “Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics”, CIDR 2021. https://www.cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf
5. GTFS Schedule reference (service days, times past 24:00:00). https://gtfs.org/documentation/schedule/reference/
6. Model Context Protocol specification, version 2026-07-28. https://modelcontextprotocol.io/specification/2026-07-28

Story Closing

Farah read the seven zeros, then the line under them. “Last year this took three weeks and a spreadsheet. Now it’s a command, and it says nothing is left to explain.” Farah signed the close.

“Nothing we know how to check,” Ingrid said, and pointed at the list of seven unfinished items. “That list is the next year’s work.”

Three seasons ago, three numbers had two answers each, and Rohan was asked to fix the queries. Season 1 built where the bytes live, Season 2 the pipelines that close the gaps record by record, Season 3 the streams, the metrics and the agents that read the same governed data. Every number now has one answer, and a way to prove it.

Rohan has an interview next month, on the other side of the table. The first question is already written: a transit authority, four thousand buses, every tap exactly once.

This is Part 35 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 3: Real Time, Serving and AI.