"Design Review II" — The Batch Reporting Platform, End to End

Skim Card

One thing to remember: a batch platform is defended by the failures it catches and the numbers it can recompute on demand, not by a green run grid.

• Size from your own tables: Larkspur’s sources write about 254 MiB of Iceberg a day at full volume, 2.5 GiB at ten times.
• For every failure, name the check that catches it and the run that proved it. A design without that table is a diagram.
• Sign numbers, not runs: the quarter was approved only after all eight checks of the board pack passed, recomputed from the lakehouse.
• Approval is a commit: proposed became approved in one overwrite carrying who and when; the proposal stays in history, and a re-run writes nothing.
• At ten times the volume, change layout and cadence before tools: day partitions for clickstream, partitions on the dbt facts, tests scoped to each run.
• Say what isn’t finished: only revenue runs under Airflow, two cost fixes await deploy, and erasure doesn’t reach Kafka segments or query logs.

Use …	When …
An open lakehouse with your own control plane (contracts, controls, SLOs)	Several engines, numbers that must reconcile with an outside source, a platform team of three or more
One cloud’s managed pipeline services over the same Iceberg tables	Open tables matter, nobody wants to run Airflow, Kafka Connect and a catalog
A warehouse platform’s built-in pipelines, quality and lineage	One main engine, SQL-first teams; reconciliation logic still yours

Verified against: PyIceberg 0.12.0 over Iceberg 1.12 tables in Apache Polaris 1.8.0, DuckDB 2.0.0, Python 3.13 · measurements from Parts 15–24 · vendor docs · 29 October 2026

Jump to: The review · The sign-off · Self-host or buy · Checklist · Design Drill

Story Opening

Farah booked the room for an hour and wrote one line in the invitation: “Q3 sign-off. Bring the numbers, not the pipelines.”

Rohan brought the Airflow grid anyway: a month of green squares. Ingrid closed the laptop. “Green says the jobs ran. Farah signs figures. Same order as last time: what they asked for, how big, what we built and why, what breaks it, each claim with the part that measured it. Then we recompute the board pack, live.”

The Review

Part 14 reviewed where the bytes live and which engines read them. This one covers what Season 2 added: ingestion, modelling, scheduling, tests, reconciliation, governance and cost. A backend reviewer asks “what happens when this service is down?”; a data reviewer also asks “and how would anyone know the number is wrong?”, because most data failures don’t throw.

1. Clarify: five products, five readers

Part 14’s constraints stand (seven years of sales, 25 months of clickstream, 30% growth a year). Season 2 added three: an erasure request answered within a month (UK GDPR, Part 23), a monthly cost Farah can read per report (Part 24), and lateness under a day (worst measured upload 16 h 28 min, Part 18).

2. Estimate: a day of each source, from the tables

Part 14 sized storage. Pipelines ask how many rows, changes and verdicts arrive a day, and which day nears a gigabyte first. scripts/part25_review.py in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-25) counts each table’s rows per day from its own date column and takes bytes per row from the current snapshot’s summary. CDC changes are dated by their business timestamps, because Part 16’s replay committed them all on one day, and the initial snapshot reads are left out. Core only:

make up-core
uv run python -m scripts.part25_review estimate

rows and Iceberg bytes a day; full = lab x 50 (stock, suppliers x 1), 10x Larkspur = full x 10
source                        lab/day  days  B/row    full/day     MiB      10x/day      MiB
POS events (raw)               18,272   128   42.5     913,598    37.0    9,135,984    370.5
POS dispositions (silver)      18,272   128   56.9     913,598    49.6    9,135,984    495.8
sale lines (gold fact)         52,431   127   15.7   2,621,550    39.4   26,215,504    393.8
order changes (CDC)             2,864    98   26.5     143,175     3.6    1,431,750     36.2
payment changes (CDC)          17,355    97   21.1     867,725    17.5    8,677,253    174.6
refund changes (CDC)              545    96   26.9      27,250     0.7      272,500      7.0
PSP settlement rows            22,607    89   30.6   1,130,334    33.0   11,303,343    329.9
clickstream events             59,679    94   25.5   2,983,946    72.6   29,839,457    725.7
stock snapshot rows           121,450    96    2.0     121,450     0.2    1,214,500      2.4
supplier delivery rows            190   124   18.8         190     0.0        1,902      0.0
all of the above                                                 253.6                2535.8
largest day at 10x: clickstream events, 0.71 GiB

(1.4 s, 448 MiB resident; full volume = lab × 50 as in Part 14, stock and supplier rows scale with store-SKUs, not baskets.) What it says:

• Still not big data. About 254 MiB of new Iceberg data a day at full volume, 2.5 GiB at ten times; Part 14’s 350 GiB at retention becomes about 3.4 TiB.
• Proof costs bytes. One verdict per POS event (56.9 B) outweighs the event it judges (42.5 B), and settlement lines come close to the sales fact. Records kept as evidence are design, not overhead.
• Payments change most. 867,725 changes a day at full volume against 143,175 for orders (authorise, capture, void); each MERGE adds delete files (Part 16), so compaction cadence follows payments.
• Clickstream nears the line first. At ten times, a clickstream day is 0.71 GiB, approaching Part 14’s ~1 GiB-a-day trigger for day partitions.
• Work scales with history, not with the day. 90% of the bytes Part 24’s daily run read went to tests and report rebuilds over all history; that breaks before ingestion does.

3. Choose: the platform, end to end

Serving stays as Part 14 designed it: Trino for analysts through the published tag, a ClickHouse serving table for the 9 a.m. store tiles.

4. Justify: Ingrid’s questions

“Every vendor sells data quality and lineage. Why write your own controls?” The data-quality and lineage products checked in Parts 20–22 count and compare: row counts, freshness, anomalies, row matches. None we verified classifies a settlement difference by fees, chargebacks, timing and FX (Part 21 couldn’t verify reconciliation offerings on GCP, Azure, Snowflake or Databricks, and finance reconciliation suites exist outside this stack). The bridge’s reasons are Larkspur’s to write; the control run costs 3–4 s a day. Buy the generic checks if they save time, and keep the reconciliation in code. Elsewhere: a bank’s general-ledger feed is checked against the core banking system’s trial balance by the bank’s own rules, whatever monitoring tool sits on top.

“Why a tag to publish when you built WAP?” Readers of main get WAP (Part 20); the dbt report is written by Trino, which can’t write branches, so its readers opt in to the published tag. One rule either way: nothing reaches a reader before its checks.

“One lost till blocks the whole report. Isn’t the gate too strict?” Farah prefers a late number to a wrong one; the 06:00 SLO and its budget make the lateness visible and paged (Part 22). A team that wants provisional figures gets its own tag, never a weaker check on finance’s.

5. Failure modes, and what catches each

6. Trade-offs, said out loud

• Your own control plane is code to own. Controls, bridge, contracts CI and SLO checks are Larkspur’s code, in exchange for checks that know the ledger.
• Every kept copy is an erasure target. Raw files, change history, dispositions and tags enable replays and audits; an erased customer must leave each one (Part 23).
• One daily batch serves one time zone. A batch of the UTC day starts at 00:00 UTC, while Singapore’s 06:00 is 22:00 UTC the evening before; India and Singapore need their own runs or deadlines (Part 22).
• The lab couldn’t hold the platform at once. Trino and Airflow took 5.6 GiB, OpenMetadata another 2.2, on a 9.8 GB VM, so lineage travelled as files: self-hosting’s operating cost in miniature.

7. What changes at 10x, and what isn’t finished

Not finished, from the progress log of this season, in the order Ingrid would fix them:

1. Only the revenue product runs under Airflow. Supplier ingestion (Part 15), the CDC job (Part 16), clickstream landing and sessions (Part 20) ran as commands; each needs its own DAG or asset trigger.
2. Part 14’s partitioning never reached the dbt facts. gold.fct_sales_line is unpartitioned (Part 24 counted 135 data files), so Part 24’s tile pruned on date_key instead; nightly compaction merges its day files, and that pruning depends on fixing this. dbt-trino’s properties config can pass Iceberg’s partitioning.
3. Two of Part 24’s three savings are modelled, deploy pending: the incremental report lives in dev, and production’s run doesn’t pass test_since.
4. Erasure doesn’t reach Kafka segments until they age out, the PostgreSQL WAL’s before images, Part 16’s replay, laptop files, the case file or Trino’s query logs (Part 23). The end-of-season run re-ran Part 19’s ingest under Airflow; the erasure held.
5. The controls table starts on 30 August. July and August are covered by the record-level bridge, not by daily control rows.
6. Two generator assumptions: card authorisations reach the ledger at sale time even when a till is offline (store-and-forward terminals would break ledger_vs_gold_sales), and Indian and Singaporean cash has no outside source like the PSP (bank deposits would be one).
7. The supplier scorecard reports on checked deliveries only: 93.1% fill, 93.4% once the quarantined rows are resent (Part 4, manifest).

The five promises, scored

This part strengthens consistency: one approved figure per period, readable by a tag that never moves.

The Sign-Off

Part 21 recorded the restatement of the board deck’s £29,999,232.43 to £29,790,674.06 in gold.restatements as proposed, approval due here. Approval is real in the lab, not just in the story: signoff recomputes every figure in governance/board-pack-2026-q3.json (the pack Farah signs) with Parts 17, 20 and 21’s own queries, and only if all eight checks pass does --approve move the restatement to approved. gold.restatements was re-created by Part 21’s restate just before this capture, so its history starts there. Core only, containers at 1.72 GiB:

uv run python -m scripts.part25_review signoff --approve --by 'Farah Siddiqui, finance controller'

Q3 2026 board pack, recomputed from the lakehouse
pass  waterfall: board deck -> correct  29,790,674.06, unexplained 0.00
pass  bridge: PSP settled -> gold       29,790,674.06 supported, 0 bug records
pass  report: tag, published, main      29,790,674.06 / 29,790,674.06 / 29,790,674.06
pass  daily controls, latest verdicts   934 rows over 29 run days, 0 FAIL
pass  restatement in gold.restatements  29,999,232.43 -> 29,790,674.06, proposed
pass  conversion (sessions contract)    134,223 / 2,161,515 = 6.21%
pass  stock availability at close       88.5% (as reported 91.1%)
pass  supplier fill rate / OTIF         93.1% / 77.7%
8 of 8 passed; 2.1 s
approved by Farah Siddiqui, finance controller at 2026-10-29T16:55:41+00:00: 2 new snapshot(s) on gold.restatements, now 6985501091157430496

The £208,558.37 (0.700%) between the two revenue figures is Part 21’s waterfall, five causes to the penny; conversion is 134,223 of 2,161,515 sessions, one fewer than Part 20 counted because Part 23 erased a customer, still 6.21%. The approval is a PyIceberg overwrite of the one row with the status changed and larkspur.approved-by, larkspur.approved-at and larkspur.approval-checks in the snapshot summary: a delete and an append in one commit. history reads each snapshot:

 7808835985582895569  2026-10-29T16:55:24+00:00  append    proposed
 1871385338162466851  2026-10-29T16:55:41+00:00  delete    (none)    Farah Siddiqui, finance controller
 6985501091157430496  2026-10-29T16:55:41+00:00  append    approved  Farah Siddiqui, finance controller

The proposal stays readable by time travel; no reader could load the delete snapshot: both snapshots arrived in one metadata commit. The same command again recomputes the pack and prints already approved by … (snapshot 6985501091157430496); nothing written. Part 21’s restate, which rewrote the row as proposed on every run, now leaves an approved restatement alone. If any figure differs from the pack, approve refuses before writing. tests/test_part25.py proves the refusal, the single approval and both no-op re-runs on a copy of the row, never on the documented approval (5 passed in 4.7 s).

Self-Host or Buy

Judgement, built from the Landscape tables of Parts 15–24 (verified 28–29 October 2026): who runs the pipelines around Part 14’s tables.

On every row the reconciliation stays yours. Larkspur’s verdict: tables as Part 14 chose (open Iceberg, REST catalog); below three platform engineers, try the second row first.

The Checklist

Batch pipeline design questions to ask, in review order. Print it.

Products and readers

• Who reads each data product, by when, in which time zone, through which tool?
• Which numbers must match an outside source, and how are closed periods changed?
• What’s the lateness of each source, measured, not assumed?

Estimate

• Rows and changes per day per source, at today’s volume and at ten times?
• Which table nears a partition threshold (~1 GiB a day) first?
• Does nightly work scale with the day, or with all of history?

Ingestion

• Is raw landed as delivered, with file, line, load ID and ingestion time?
• Full extract, cursor with lag window, or CDC: which per source, and what finds deletes?
• Is each load safe to re-run? What makes a repeat harmless (key, verdict, batch ID)?
• What stops a renamed column becoming NULLs?

Transformation

• Is each source cleaned once, with a grain test on every fact?
• Is the incremental window sized from measured lateness, and compared to a full rebuild?
• Can a change be built and tested in dev without touching production?

Orchestration

• Are runs named by their data interval, and does every task replace what it owns, so retries and backfills are safe?
• Who is paged when a deadline passes?

Quality, contracts, publish

• Where does data change owner, and is each boundary a versioned contract with CI?
• Which defects quarantine, which fail the batch, at what ceiling?
• Can a reader see data before its checks pass?

Reconciliation

• Counts, sums and hash totals at every hop you control: where would a loss first show?
• Which differences are legitimate (fees, timing, FX), and is every one explained per record?
• Is a closed period tagged, and does a change to it become a recorded restatement?

Observability

• Freshness, completeness, volume and schema SLOs per product, with a budget?
• Can lineage take you from a wrong number to its sources across every engine?

Governance

• Is personal data classified where tables live, and enforced in every engine that reads it?
• Does an erasure reach raw files, history, tags, topics and logs, and survive a reload?

Cost

• Bytes, requests and CPU metered per query, run and table; idle hours counted?
• Does every folder in the bucket belong to a table someone owns?

Change and sign-off

• What changes at ten times the volume, and what isn’t finished?
• Can every figure the reader signs be recomputed from the platform, on demand?

Design Drill

The question: “A contract research organisation runs 40 clinical trials for drug makers. Clinicians at 600 hospital sites enter patient visits, lab results and adverse events into an electronic data capture (EDC) system; central labs send result files nightly; serious adverse events are also kept in a separate safety database. Sponsors get weekly safety listings and daily enrolment and data-cleaning dashboards; at the end of each trial the database is locked, and the analysis datasets must be reproducible years later. Design the batch platform.”

Clarify first (with the answers this worked solution assumes)

• How do corrections arrive? Sites edit values in the EDC, which keeps who, when and why for each change.
• How late are lab files? Up to five days; a lab sometimes resends a corrected file.
• What must reconcile? Serious adverse events between EDC and the safety database; lab values between the vendor files and the EDC.
• Who may see which treatment a patient got? Only the unblinded statistician, until the trial is unblinded.
• What happens when a patient withdraws consent? For EU trials, data obtained before withdrawal stays (Regulation (EU) No 536/2014, Art. 28(3), without prejudice to data-protection law, the GDPR); nothing new is collected.
• Deadlines? Weekly listings by Monday 08:00 in the sponsor’s time zone.

Estimate

• Assume 30,000 patients, 50 visits each, 300 data points a visit: 450 million entries over about four years (~1,000 working days). One in five edited later adds 90 million: 540 million changes, about 0.5 million a working day. At 20–30 bytes a row (Larkspur’s CDC change tables measured 21–27), 10–15 MB a day. Small data, heavy rules: correctness, audit and access, not scale, drive the design.

The shape of a strong answer

• Ingest changes, not states. Capture the EDC’s change history (its audit export or CDC on its database) into an append-only history table plus a current-state table, as at Larkspur’s orders. In the US, 21 CFR 11.10(e) requires secure, computer-generated, time-stamped audit trails; the lake mustn’t lose them.
• Lab files as contracts. One ODCS contract per vendor (units, ranges, required fields); files landed whole with checksums; unknown units quarantined under a ceiling; a corrected resend replaces its batch.
• Reconcile by name. A daily control per trial: serious adverse events in EDC vs the safety database per patient and event, lab results per vendor file vs EDC; every listing shows its unmatched count, and a listing with unexplained differences isn’t published.
• Lock is a tag. Each data cut and the final lock tag the analysis tables, with no expiry. A post-lock correction requires an unlock approved like Larkspur’s restatement, recorded, with a new tag; the old one stays.
• Blinding is access control. Treatment assignments in a separate namespace only the unblinded statistician’s principal can read, enforced in every engine; row filters by sponsor and trial; access audit kept as long as the records.
• Consent withdrawal is a suppression list for new data, not an erasure: every loader drops records dated after the withdrawal.
• Engines: one machine or a small Trino; no Spark. Per-trial asset triggers; weekly listings gated on controls and contracts.

Trade-offs to say out loud

• Every version kept forever: cheap and mandatory, at the cost of always asking “as of when?”.
• Per-sponsor isolation (catalogs or namespaces) protects confidentiality and makes cross-trial analytics an explicit, approved job.
• The EDC vendor’s reports start faster but can’t reconcile with the safety database.

What would change it: real-time safety signals from wearables (streaming, Season 3, with batch still the record), or a sponsor requiring its own cloud (one deployment per sponsor, same code).

Common weak answers

• Storing current values only: the audit trail and “as of lock” are gone.
• Treating lock as “stop the jobs” instead of an immutable, tagged snapshot.
• Forgetting blinding in the lake: one join to the randomisation table unblinds a trial.

Further Reading

1. Joe Reis and Matt Housley, Fundamentals of Data Engineering (O’Reilly, 2022): the chapters on the data engineering lifecycle and on data architecture.
2. Martin Kleppmann and Chris Riccomini, Designing Data-Intensive Applications, 2nd edition (O’Reilly, 2026): the chapters on batch processing and derived data.
3. Apache Iceberg, “Branching and Tagging”. https://iceberg.apache.org/docs/latest/branching/
4. Beyer et al., Site Reliability Engineering (O’Reilly, 2016), chapter 4, “Service Level Objectives”. https://sre.google/sre-book/service-level-objectives/
5. Regulation (EU) No 536/2014 on clinical trials (Official Journal), Article 28 (the drill’s consent answer). https://health.ec.europa.eu/system/files/2016-11/reg_2014_536_en_0.pdf
6. US Code of Federal Regulations, 21 CFR 11.10, controls for closed systems (the drill’s audit-trail rule). https://www.law.cornell.edu/cfr/text/21/11.10

Story Closing

Farah read the eight lines twice, then the history. “So my approval is a row I can’t quietly undo.”

“And the board deck’s number stays in the record, beside the one you approved,” Ingrid said.

Season 2 set out to turn the lakehouse into pipelines Larkspur could trust, and to close the three numbers. Revenue matches the settlement files to the record, stock is counted at close, conversion has one definition, and the quarter is signed. Seven items stay on the list above, in the order they’ll be fixed.

On the way out, a store manager stopped Rohan. “Your 06:00 report says the chilled aisle was fine yesterday. I need to know it’s empty while the customer is still standing there.” In Part 26, Season 3 begins with streaming: event time against processing time, watermarks, and why in a stream when it happened and when you saw it are different clocks.

This is Part 25 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 2: Pipelines in Production.