"Design Review I" — The Storage and Compute Layer

Skim Card

One thing to remember: a storage and compute design is defended with requirements, an estimate from your own files and a named trigger for every choice, not with a diagram.

• Estimate from measured files: a sales line is 14.83 bytes in Parquet and 231.9 as raw JSON; full volume is 2.4 million lines a day.
• Larkspur isn’t big data: about 350 GiB after seven years, 60% of it raw. Concurrency, several engines and exact money shaped the design.
• Files per day follow commit cadence, not volume: one a day nightly, 288 every five minutes, three per month after compaction.
• Month partitions on business date under 1 TB: four files for a month’s revenue instead of 168, for 0.59 GiB rewritten on average a night.
• 3,600 morning tiles queue 92–103 s on the lab’s one Trino node, 2–3 s from a serving table; per-byte cost was never the problem.
• Season 1 explained four of five revenue steps (£151,493.41) and fixed stock (88.5%); hard-deleted orders (£57,064.96) wait for CDC.

Use …	When …
A self-hosted open lakehouse	A platform team of three or more, several engines writing, sovereignty or cost control at scale
Iceberg with a managed REST catalog and rented engines	Open tables matter, but nobody wants to patch a catalog at 2 a.m.
A managed warehouse	One main engine, SQL users, little platform staff
One machine (DuckDB, Polars)	Tens of GB, one team, no concurrency spike

Verified against: PyIceberg 0.12.0 over Iceberg 1.12.0 tables in Apache Polaris 1.8.0, Python 3.13 · measurements from Parts 2–13 · vendor docs · 28 October 2026

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

Story Opening

Farah’s requirements arrived on one page: how much data, how long to keep it, how fresh, how many people at once, and a last line that said “cost: tell us”. Rohan’s answer was thirty slides, the first one an architecture diagram with eleven boxes.

Ingrid moved the diagram to the end. “In an interview and in this room, the boxes come fourth. First what they asked for, then how big it is, then what we chose and why, then what would change our minds. Every choice names the part where we measured it. If we didn’t measure it, say so.”

The Review

A backend design review starts from services and their APIs. A data platform review starts from the questions people will ask and the bytes those questions touch, because storage layout and engine choice follow from both. The walkthrough below is the order a strong interview answer takes; the numbers are Larkspur’s.

1. Clarify: what Farah’s team asked for

2. Estimate: sizing from the lab’s own files

scripts/part14_sizing.py in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-14) derives every number from what the lab holds: the raw zone on disk (decompressed and counted), and Iceberg manifests through Polaris for Part 10’s sales table and Part 7’s clickstream table. It plans the review’s queries with PyIceberg’s scan(row_filter=...).plan_files(), which prunes by partition and file statistics, and sums each planned file’s column_sizes for the columns the query names. It reads no data files. Only the core profile runs (containers peaked at 0.92 GiB, the script at 381 MiB resident).

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

lab basis: 126 POS business days, 94 clickstream days, 96 stock snapshots (seed 42); full volume = lab x 50 (snapshots x 1)

per row                 lab/day    full/day  raw gz B  raw JSON B  Parquet B
POS lines                48,063   2,403,144      25.1       231.9      14.83
clickstream events       59,679   2,983,951      30.4       330.2      26.67
stock snapshot rows     121,450     121,450         -           -       1.95
POS lines: raw JSON is 16x the Iceberg Parquet bytes, gzip 1.7x

full volume                 per day  per month       kept  at retention
sales lines (Iceberg)      34.0 MiB   1.01 GiB    7 years      84.8 GiB
clickstream (Iceberg)      75.9 MiB   2.26 GiB  25 months      56.4 GiB
stock snapshot (x1)         0.2 MiB   0.01 GiB    7 years       0.6 GiB
raw POS (gzip JSONL)       57.6 MiB   1.71 GiB    7 years     143.8 GiB
raw clickstream (gzip)     86.6 MiB   2.57 GiB  25 months      64.3 GiB
total                                                         349.9 GiB

sales lines, files into the open month partition (one data file per commit, Part 10)
commit cadence     files/day  files/month  after compaction
nightly batch              1           30                 3
hourly                    24          730                 3
every 5 minutes          288        8,766                 3
nightly rebuild of 3 business dates, average rewritten: month partitions 0.59 GiB, day partitions 102 MiB

scans planned on part10.pos_monthly (manifests only), then x 50
query                 files  lab file MiB  lab column MiB  full column GiB
revenue (Sep)             4          20.2            1.72             0.08
store promo (12 mo)      11          70.6            1.03             0.05
store tile (4 wk)         1           6.3            0.51             0.02
history (4 yr)          197        1027.5           98.54             4.81

9 a.m.: 300 managers x 12 tiles = 3,600 queries
  Trino over Iceberg            34.9-39.3 q/s at 32 clients -> 91.6-103.2 s to drain
  ClickHouse serving table    1,399-1,671 q/s at 32 clients -> 2.2-2.6 s to drain
  every tile from the lines at full volume: at most 89.9 GiB of columns a morning
  per-byte yardstick at USD 6.25/TiB: a month of such mornings USD 16, the 4-year history query USD 0.03 a run

at 30% growth a year
  sales day partition reaches 1 GiB   in 13.0 years
  clickstream day reaches 1 GiB       in 9.9 years

The stock snapshot’s 1.95 bytes per row is the raw zstd Parquet file, not an Iceberg table; snapshots scale with store-SKUs, not baskets, so they aren’t multiplied. Bytes per row come from Part 10’s scaled-down sales files (8 MiB, 2 MiB row groups) and Part 7’s one-day clicks_v2. Part 3’s single DuckDB file held 6,055,922 of the same 13-column lines in 41.0 MB, about 6.8 bytes each, so the estimate errs high; Season 2’s load-metadata columns will add some back. The per-TiB rate is Part 13’s BigQuery on-demand price, used only as a yardstick. What the estimate says:

• Seven years fit on one disk. 350 GiB at retention, and the raw zone is 208 GiB of it. Larkspur’s design isn’t driven by volume; it’s driven by 9 a.m. concurrency, several engines and exact money.
• The ratios are measured, not quoted. Raw JSON is 16 times the Parquet bytes for a sales line and 12 times for a click; gzip gets JSON within 1.7 times of Parquet, but Part 3 showed why queries still can’t use it: every scan decompresses and parses everything.
• Files per day are a commit decision. Part 10’s 41 micro-batches left October’s partition with 42 data files and 15 delete files after one day; compaction left one. At five-minute commits, the open month gathers 8,766 files before compaction.
• The planner’s column bytes are honest. For the September revenue and four-year history queries they come to 1.72 and 98.54 MiB; DuckDB actually fetched 1.9 and 107.5 MiB in Part 11, the difference being footers and metadata.
• The bill isn’t the problem; the queue is. Serving every morning tile from the lines would cost about USD 16 a month at per-byte rates, and keep store managers waiting a minute and a half on the lab’s single Trino (one 3 GB node on a 4-vCPU VM); more workers raise the rate, at a cost. The serving table also scales with store-days, not baskets, so its rate survives ×50; Trino over the lines doesn’t.
• Clickstream is the bigger table per day. 75.9 MiB a day at full volume, 2.26 GiB a month: month partitions, as Part 10 chose, and days only near 1 GiB a day.

3. Choose: the design

The data model under the tables is Parts 4 and 5’s: one row per basket line, refunds as their own fact, money in integer minor units with the currency on every row, local business dates.

4. Justify: Ingrid’s questions

“Your own playbook says don’t partition under 1 TB. Sales lines reach 85 GiB in seven years. Why partition?” Because pruning wasn’t the only job. Unpartitioned, a month’s revenue read 168 files; Z-ordered, still 87 of 135. Months also scope maintenance (a compaction or rebuild touches one month, and writers on different months never conflict) and make the seven-year retention a metadata drop. The price is in the estimate: a nightly overwrite rewrites whole months, 0.59 GiB on average against 102 MiB with day partitions. The worst night, early in a month, rewrites a month and two days, about the size of Part 10’s whole lab table, which local Spark wrote in 85 s. Elsewhere: an insurer partitions a few hundred gigabytes of claim events by month for the same reasons: retention drops and month-scoped restatements, not pruning.

“Why Iceberg, if Databricks would sell us Delta tomorrow?” Because Spark, DuckDB and, in Season 3, Flink all write Larkspur’s tables. Part 8 found the limits of the alternatives: Delta on S3 has one safe writer unless every writer uses the DynamoDB log store (experimental), and interop layers translate metadata for readers only. Format v2 because every engine in the lab reads it and every writer writes it.

“Four engines is four things to run. Pick one.” Each has a workload the lab measured: DuckDB the scans that fit one machine, Spark the basket-grain work that won’t at full volume, Trino the analysts and the federated join, ClickHouse the morning rush. They share one copy: DuckDB, Polars, Spark, Trino and ClickHouse produced the same digest for the same view. A managed warehouse could replace Spark and Trino; that’s a staffing decision (the matrix below), not a data one.

“Who can read raw?” Only the pipeline principal and the catalog’s admin. Two lab shortcuts remain and go before production: DuckDB’s static lake-reader key and ClickHouse logging in to Polaris as root (Part 13).

“Why should finance trust the lake over the old warehouse?” Because the model answers the board’s question exactly: Part 5’s star reproduces £29,790,674.06 to the penny from the same raw files. The lake adds what the old warehouse never had: every engine reads one committed snapshot, and a closed quarter stays readable by its tag.

5. Trade-offs, said out loud

• Open stack vs one product. A managed warehouse would meet Farah’s volume and freshness on day one, with less to operate. The lakehouse wins on several writers, exit options and Season 3’s streaming, and loses on on-call load.
• A second copy for speed. ClickHouse’s MergeTree copy of the lines took 1.71 GiB against the lake’s 1.0 GiB; the serving table is smaller but still a copy to secure, refresh and reconcile.
• Months vs days. Fewer, bigger files and simple retention, paid for with whole-month rewrites.
• Raw for seven years. Replayability, and 60% of the bytes; erasure requests (Part 23) must reach raw files too.

6. What would change the decision

The five promises, scored

The review checks all five against the storage and compute layer.

Where the three numbers stand

uv run python -m scripts.part14_sizing incident

UK net revenue Q3: board deck 29,999,232.43, correct 29,790,674.06
step                                                                     GBP  Season 1       Season 2
Duplicate POS events (till re-sends after reconnecting)           118,076.98  found, Part 6  prevent in Part 21
Cancelled online orders hard-deleted after the poller saw them     57,064.96  open           Part 16 (CDC)
Refunds dated by original purchase date instead of refund date     42,511.91  fixed, Part 4
EUR orders and refunds converted at the report-day rate            -8,797.38  fixed, Part 5
Quarter boundaries in UTC instead of Europe/London                   -298.10  fixed, Part 5
sum of steps = gap                                                208,558.37  (0.700% of correct)
explained in Season 1                                             151,493.41
still open                                                         57,064.96
stock availability: 97.2% -> 91.1% (Part 4) -> 88.5% (Part 5): fixed
conversion: 6.21% vs 5.31%: explained (Part 6); one definition in 20, 31

The star’s figure is right because it reads a full extract of the orders table; the pipelines that will feed it daily can’t see a deleted order until Part 16 reads PostgreSQL’s change stream, and Part 21 proves the 0.7% record by record against the settlement files. Stock is fixed; conversion is explained but still has two owners. uv run pytest -q tests/test_part14.py checks the estimate against Part 10’s file counts and the ledger against the manifest (5 passed in 2.7 s).

Self-Host or Buy

Judgement, not measurement, built from facts verified in Parts 1–13 (28 October 2026). It extends Part 1’s matrix with what the later parts found about maintenance and billing.

Larkspur’s verdict: the commitment is Iceberg plus the REST catalog protocol. Self-hosting the rest is reversible, and the third row of the triggers table says when to stop.

The Checklist

Storage and compute design questions to ask. Print it, take it into the review or the interview.

Requirements

• Who reads, how many at once, at what hour, through which tool?
• How fresh per consumer: final by when, provisional for how long?
• How long is each table kept; what must be erasable, and what reproducible as filed?
• Which numbers must reconcile with an outside source?

Estimate

• Rows per day, and bytes per row measured on real Parquet files, not guessed
• Per day, per month, at retention; raw vs columnar ratio
• Files per day at the planned commit cadence, and after compaction
• Bytes read by the top five queries; queries in the peak minute

Storage, format, catalog

• Is raw kept immutable, by arrival, and for how long?
• Which table format and version can every engine read and write?
• Which catalog arbitrates commits; do engines get vended or static credentials?
• Who can read raw, and are grants per job function?

Layout and maintenance

• Is the partition column the date reports filter on and corrections rewrite, at ~1 GB per partition?
• Which sort order serves the next filter, and how many files does a top query open?
• Who runs compaction, snapshot expiry and orphan cleanup, how often? Which snapshots are tagged?

Compute and serving

• Which workloads fit one machine; which need shuffles, concurrency or retries?
• Does the peak need a serving tier, and how is its freshness shown and reconciled?

Cost and operations

• What is each engine’s billing shape (per second, per byte, per capacity) and idle cost?
• Who patches and gets paged for each component?
• Does every copy beyond the lake have an owner, a refresh and a reconciliation?

Change

• What would change each decision, and how reversible is it?

Design Drill

The question: “An online learning platform serves 12 million students and 400,000 teachers in schools across several countries. The apps emit about 1.5 billion learning events a day: video progress, quiz answers, page views. Teachers open class dashboards during school hours; researchers run cohort studies over three years of history; contracts require deleting a student’s data within 30 days of leaving. Design the storage and compute layer.”

Clarify first (with the answers this worked solution assumes)

• Bytes per event as compressed Parquet? Assume 40, to be measured on a real day.
• How late do events arrive? Offline tablets sync up to three days later.
• How fresh must teacher dashboards be? Within 15 minutes during class.
• How many teachers open a dashboard, when? 20% on a school day, mostly in the first lesson hour of their time zone, about 8 tiles each.
• Residency? Student data stays in its region.
• Do leavers cluster at year end? Partly: a peak in summer, a steady trickle otherwise.

Estimate

• 1.5 B × 40 bytes = 60 GB a day, 2.5 GB an hour, ~66 TB over three years. Raw JSON is 12–16 times Parquet at Larkspur, so uncompressed landing is roughly 0.7–1 TB a day.
• Teachers: 80,000 opens × 8 tiles = 640,000 queries in an hour, ~180 a second on average, several times that at the top of the hour.
• Erasure: if 10% of students leave a year, 1.2 million a year, ~23,000 a week. Their histories span three years and thousands of schools, so a weekly physical delete touches nearly every day’s files: each rewrite approaches the whole ~66 TB.

The shape of a strong answer

• Raw: land events by arrival hour; keep raw 14 days, enough to replay the lateness window, so erasure doesn’t have to reach years of raw files.
• Tables: Iceberg v2 through a REST catalog, one catalog and bucket per region; vended credentials; schools’ analysts get row filters later.
• Layout: days(event_ts) on event time (60 GB a day is well past the ~1 GB floor), sorted by school_id, student_id within each day; binpack each closed hour, sort-compact each closed day; snapshots kept seven days.
• Erasure: design it in, not on. Events carry a pseudonymous student ID; names and other personal fields live in one small table, encrypted with a key per student. Leaving deletes the mapping row and the key (crypto-shredding), so three years of event files never need rewriting; legal must sign off that this counts as erasure. Rows that must physically go get merge-on-read deletes, folded in by the regular compaction, then snapshot expiry and orphan cleanup inside the 30 days. The serving store is rebuilt from gold, so erased students leave it too.
• Compute: Spark for nightly cohort and mastery aggregates over 66 TB; Trino or a warehouse for researchers; a real-time OLAP store holding per-class aggregates, loaded from five-minute micro-batches, for the 180-plus queries a second.
• Prove it: files planned and bytes read for the top ten queries, from manifests, before buying anything.

Trade-offs to say out loud

• Fourteen days of raw limits replays; the price of erasure that doesn’t touch archives.
• Per-region catalogs make global research a federated or aggregated job.
• Five-minute commits buy freshness and cost compaction and snapshot churn.

What would change it

• Teachers want quiz results within seconds: stream into the OLAP store (Season 3).
• Contracts don’t accept crypto-shredding as erasure: monthly physical deletes inside the 30 days, budgeted as a near-full rewrite each time.
• A single-cloud deal with a warehouse vendor: managed tables over the same Iceberg files.

Common weak answers

• Partitioning by school_id: hundreds of thousands of partitions.
• Deleting rows and forgetting snapshots, time travel, raw files and the serving copy.
• One engine for both teachers’ dashboards and researchers’ three-year scans.

Further Reading

1. Joe Reis and Matt Housley, Fundamentals of Data Engineering (O’Reilly, 2022): the chapters on storage and on data architecture.
2. Martin Kleppmann and Chris Riccomini, Designing Data-Intensive Applications, 2nd edition (O’Reilly, 2026).
3. Apache Iceberg, “Maintenance” and the table spec. https://iceberg.apache.org/docs/latest/maintenance/
4. Apache Polaris, “Access control”. https://polaris.apache.org/releases/1.8.0/managing-security/access-control/
5. Databricks, “When to partition tables on Databricks”. https://docs.databricks.com/aws/en/tables/partitions
6. 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

Story Closing

Ingrid signed the review with two conditions: the static lake key and ClickHouse’s root login go before anything leaves the lab, and the maintenance jobs get a scheduler. Of the five promises, the review found three kept; uniqueness and completeness are partly kept until Season 2.

“So my board-deck number is right now?” Farah asked.

“The query is right,” Ingrid said. “The data feeding it isn’t complete yet. Season 1 built the house. Season 2 builds the plumbing.”

Part 15 opens Season 2: Rohan ingests supplier files and the marketplace sellers’ API properly, and learns that ingestion is a contract with a system you don’t control.

This is Part 14 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 1: Foundations & the Lakehouse.