"The Field Guide" — How to Read This Series

Skim Card

One thing to remember: an analytics number you can’t trace back to its records and reconcile with an outside source is a rumour, however good the dashboard looks.

• Most wrong numbers come from correct SQL over records nobody can account for: duplicates, deletes, dates and definitions.
• Learn concepts before tools; each concept here comes with a Larkspur use case and one from another industry.
• Run every number yourself: the lab reproduces each claim, and each part ends with an interview-style Design Drill.
• The lab runs on a laptop: S3-compatible storage, an Iceberg REST catalog and PostgreSQL in about 0.9 GB of container memory.
• The incident is reproducible: UK revenue £29,999,232.43 vs £29,790,674.06, stock availability 97.2% vs 88.5%, conversion 6.21% vs 5.31%.
• Every defect behind those gaps is in a manifest with its count and effect; the revenue gap splits into five exact amounts.

Verified against: DuckDB 2.0.0, Apache Polaris 1.8.0, SeaweedFS 4.51, PostgreSQL 18.6, Python 3.13 · 28 October 2026

Jump to: The map · The incident · Run the lab · The question

Story Opening

The quarterly business review had a slide titled “Q3 at a glance”, and every number on it was argued with before the meeting ended.

UK net revenue was £30.00 million. Farah Siddiqui, the finance controller, had spent the previous week reconciling the payment provider’s settlement files in a spreadsheet, and they supported £29.79 million, through adjustments nobody could tie back to individual orders. “It’s only 0.7%,” someone said. “On a quarter, 0.7% is a restatement,” Farah said, and wrote it down.

The stores dashboard showed 97% stock availability. Three regional managers had photos of empty shelves on their phones.

The product team reported 6.2% online conversion. Marketing’s dashboard, built from the same clickstream, said 5.3%, and marketing had already asked for budget to fix the drop.

Rohan Desai built Larkspur’s order and payment services, knows Postgres, Kafka and idempotent consumers, and has never been paged for a dashboard. That afternoon the CTO’s request landed in Rohan’s inbox: “take a look at the queries, they’re probably just wrong.”

Rohan read them. They were mostly correct SQL. They ran against read replicas, nightly WHERE updated_at > yesterday extracts, a spreadsheet of exchange rates and two notebooks nobody would admit to owning. Each one answered a slightly different question, and none of them could say which records it had counted.

Rohan took the list to Ingrid Solberg, the principal data engineer who had just joined from adtech. Ingrid looked at it for a minute.

“These queries aren’t wrong. They’re unaccountable. Fixing them won’t help. You need a platform where every number can be traced back to the records it came from.” Then the line the whole team would hear many more times: “A number you can’t reconcile is just a rumour.”

Who this series is for

You’re a senior backend engineer. You’ve spent eight to fifteen years on services in Java and Spring, Go or Node. You know PostgreSQL’s isolation levels, Kafka’s consumer groups, REST, Kubernetes, and why retries need idempotency keys. You’ve never owned an analytics platform.

Maybe you’re preparing for a system design interview where the prompt is “design a reporting system”. Maybe you’re moving into a data platform team, or your product needs data features and the data team is three quarters behind. In each case the gap is the same: you’re an expert in distributed systems and a beginner in analytics, and many of your instincts point the wrong way.

The series treats you as both. It won’t explain what a transaction is. It will explain why a columnar file can skip most of a table without a B-tree index, why “just add an updated_at column” loses data, and why the answer to “how many sessions were there?” depends on a definition someone has to own.

Demand for these skills is real, though the figures vary with who counts. One analysis of about 110,000 data engineering job postings (huntr, July 2026) found Python in 74% and SQL in 67% of them. Treat that as direction, not precision.

What you’ll be able to do

The thread through all three seasons is interview-grade design confidence: given a prompt with streaming sources, static sources, years of history and an accuracy requirement, you’ll know which questions to ask and which choices to defend.

Prerequisites

How each part is built

Every part follows the same order, so you can skip to what you need:

1. Skim Card: the thesis and claims in under a minute, with the versions checked.
2. Story Opening: a Larkspur problem where a backend instinct misleads.
3. The Quick Map: Backend instinct → Data engineering reality → Why it matters.
4. Concepts, with use cases: definition, what breaks without it, Larkspur, another industry.
5. Under the Hood: the mechanism.
6. Hands-On: runnable code with real output.
7. The Landscape: open source, AWS, Google Cloud, Azure, Snowflake, Databricks.
8. Tips & Gotchas and, in hands-on-heavy parts, Troubleshooting.
9. Design Drill: an interview prompt from another industry, with a model answer outline.
10. Further Reading and the Story Closing.

Parts 14, 25 and 35 are design reviews: full interview walkthroughs with sizing, a checklist and a second worked example.

Anything that isn’t generally available is labelled inline: (preview), (experimental), (incubating). Vendors rename products often; the posts use the names in force when each part was written and mention old names only as “formerly”.

The map of data engineering in 2026

Every data platform moves records from the systems that produce them to the people and programs that consume them. The stages have stable names even when the tools change:

You already know the left edge (you build the sources) and part of the right edge (your services call APIs and caches). The middle is where your instincts mislead you: storage built for scanning instead of point lookups, transformations that must be re-runnable over years of history, and “correct” meaning “matches the outside world”, not “the transaction committed”.

Where each part lands:

Larkspur

Larkspur is an omnichannel retailer and marketplace in India, the UK and Singapore: about 600 stores, an app and a website, and a marketplace of third-party sellers. It’s fictional, but it produces almost every kind of data a data engineer meets, which is why the series uses it.

Until Season 2 adds databases and APIs and Season 3 adds Kafka, a generator writes everything Season 1 needs as landed raw files, standing in for what ingestion will later produce. The same seed produces the same business events in every mode, so numbers carry over between seasons.

The people:

• Rohan Desai, staff backend engineer, moved to the new Data Platform team after the review. Your stand-in.
• Ingrid Solberg, principal data engineer, ex-adtech, has run petabyte lakes and been paged for “the dashboard is wrong” more often than anyone can count.
• Farah Siddiqui, finance controller, who asks the uncomfortable questions: “why does yesterday’s revenue keep changing?”

Three numbers, two answers each

The incident isn’t hand-waved. The companion lab generates four months of Larkspur data (1 June to 4 October 2026) with documented defects: things real systems do, injected deliberately, each with a count and an exact effect on the numbers. Here’s what the review was arguing about, computed from the raw files.

UK net revenue, Q3 2026. The board deck’s query and the correct one, both plain SQL over the same files, in pence:

-- sql/incident/uk_revenue_legacy.sql
┌──────────────────────┐
│ uk_net_revenue_minor │
│    decimal(38,0)     │
├──────────────────────┤
│           2999923243 │
└──────────────────────┘

-- sql/incident/uk_revenue_correct.sql
┌──────────────────────┐
│ uk_net_revenue_minor │
│        int64         │
├──────────────────────┤
│           2979067406 │
└──────────────────────┘

£29,999,232.43 against £29,790,674.06: the board deck is 0.70% above the figure Farah could support with the settlement files. Larkspur UK is cashless, so every UK payment passes through the payment service provider (PSP), and its daily files are the outside check. They don’t equal net revenue line for line: fees, chargebacks and settlement delays all differ legitimately. Separating those legitimate differences from real bugs, record by record, is the subject of Part 21. The bugs behind the £208,558.37:

The first three inflate the number and the last two pull it down slightly; together they’re exactly £208,558.37. Reconciliation means finding all five, not just the big one.

Stock availability, Q3 2026. The dashboard’s number, the same snapshots counted per day, and the share of store-SKU-days where the shelf had stock at closing:

-- sql/part00/stock_availability.sql
┌───────────────┬──────────────────────┬────────────────────┐
│ dashboard_pct │ per_day_snapshot_pct │ shelf_at_close_pct │
│    double     │        double        │       double       │
├───────────────┼──────────────────────┼────────────────────┤
│          97.2 │                 91.1 │               88.5 │
└───────────────┴──────────────────────┴────────────────────┘

The dashboard adds up on-hand quantities across each week and calls any week with stock “available”. Stock on hand is a balance, not a flow; adding balances across days answers no question anyone asked, and counting per day instead drops the figure from 97.2% to 91.1%. Then 216 of the 600 stores take their stock snapshot at 06:00, after the overnight delivery, so their snapshot shows stock that wasn’t on the shelf the evening before; correcting for that gives 88.5%. Part 4 covers the first problem, Part 5 the second. Banks meet the same trap with account balances: the average daily balance is a meaningful number, the sum of a month’s daily balances is not.

Online conversion, Q3 2026. Same events, two definitions of a session:

-- sql/part00/conversion.sql
┌───────────┬──────────┬────────────────┐
│   team    │ sessions │ conversion_pct │
│  varchar  │  int64   │     double     │
├───────────┼──────────┼────────────────┤
│ product   │  2161516 │           6.21 │
│ marketing │  2525785 │           5.31 │
└───────────┴──────────┴────────────────┘

The product team starts a new session after 30 minutes of inactivity and filters bots by user agent. Marketing counts one session per visitor per UTC day and keeps every hit, including 474,085 crawler requests that each arrive as a new visitor. Neither query has a SQL bug. They encode different definitions, including whether a bot is a visitor, and nobody owns the definition of a session. Streaming services fight the same battle over what counts as a “view”. Part 6 sessionizes properly, Part 20 writes the definition into a data contract, and Part 31 puts one metric definition in a semantic layer.

Notice the instincts behind each gap, because they’re yours too. The event ID looked like the key, so re-sent baskets counted twice; transit card readers that re-send taps after an outage cause the same double count. A delete leaves nothing behind for an updated_at poller to find. A stock count looked like something you can add up the way you add up sales. And “session” seemed too obvious to need a definition.

Every defect, with its count and effect, is listed in docs/injected-defects.md in the companion folder, and make test checks these queries against the generator’s manifest.

Running the companion lab

The code lives in the every-record-counts folder of the companion-workspaces repository, and each part is tagged every-record-counts/part-<N>.

Start the core profile: S3-compatible object storage (SeaweedFS), the Iceberg REST catalog (Apache Polaris) and PostgreSQL 18. From a clean machine it takes about 15 seconds after the images are pulled:

make up-core

Its output, with the Creating, Starting and Waiting progress lines left out:

docker compose --profile core up -d --wait seaweedfs postgres polaris
 Container every-record-counts-postgres-1 Healthy
 Container every-record-counts-polaris-bootstrap-1 Exited
 Container every-record-counts-seaweedfs-1 Healthy
 Container every-record-counts-polaris-bootstrap-1 Exited
 Container every-record-counts-postgres-1 Healthy
 Container every-record-counts-seaweedfs-1 Healthy
 Container every-record-counts-polaris-1 Healthy
docker compose --profile core run --rm --no-deps polaris-setup
catalog larkspur created
catalog_admin can manage content in larkspur

The bootstrap job writes Polaris’s root credentials into PostgreSQL and exits; the setup job creates the larkspur catalog. Neither stays running, which is why make waits only for the three long-running services.

Generate the raw zone and copy it into the larkspur-raw bucket:

make data
make upload

[   0.0s] reference data
[   0.1s] sales
[   6.1s] POS events and the UK revenue calibration
[   7.2s] inventory
[  10.0s] suppliers
[  10.0s] clickstream
[  27.2s] PSP settlements
[  27.6s] writing data/raw
[  57.5s] done: UK Q3 net revenue legacy £29,999,232.43 vs correct £29,790,674.06 (+0.700%)
uploaded 3458 files, 623 MB to s3://larkspur-raw/raw/ in 6s

Then reproduce the incident. scripts/duck.py opens DuckDB, points a set of views at the bucket and runs SQL files, printing each result (the outputs above, with the timing lines trimmed):

uv run python scripts/duck.py sql/incident/uk_revenue_legacy.sql sql/incident/uk_revenue_correct.sql \
  sql/part00/stock_availability.sql sql/part00/conversion.sql

The views it creates live in sql/raw/views.sql. Two lines in that file deserve a note now, because they catch everyone once:

SET TimeZone = 'UTC';  -- otherwise ::DATE on a TIMESTAMPTZ uses your laptop's zone

CREATE OR REPLACE VIEW raw_pos_events AS
SELECT * FROM read_json(getvariable('raw_root') || '/pos/events/*/*.jsonl.gz', hive_partitioning = true);

DuckDB’s session time zone defaults to your machine’s, so on a laptop in Bengaluru the same query would cut UTC days at 18:30. And read_json over a glob turns a folder of gzipped files into a table, with upload_date taken from the folder names. Part 3 explains why that convenience isn’t a table in the transactional sense.

make test runs the generator’s tests and the Part 0 checks (they skip themselves if the stack is down):

.........                                                                [100%]
9 passed in 19.02s

Profiles and memory. Services are grouped into Compose profiles, and each part says which ones to start. Every long-running service has a memory limit, and the JVM services size their heaps from it.

The core profile peaked at 0.86 GB while the incident queries ran (DuckDB runs on your machine, outside Docker). The lab was measured on a MacBook Pro M3 Pro with a 9.8 GB Docker VM, and every part lists its measured peak.

Tip — Check how much memory your Docker VM has (docker info --format '{{.MemTotal}}') before starting a heavy profile; other projects’ containers count against it. The lab’s budget assumes you stop what you don’t need.

Glossary seed

Short definitions now; each term links to (or names) the part that explains it properly. Later parts add to this list.

The question this series teaches you to answer

Interviews for data and platform roles keep returning to one kind of prompt. Here’s the version this series is built to answer. Part 35 works through it in full, and every Design Drill on the way is a smaller piece of it.

“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.”

A strong answer clarifies before it designs (how late can a bus’s taps arrive? what is a “trip” when a passenger changes buses? how does the timetable change mid-quarter?), estimates volumes, declares the grain of each table, picks storage, a table format and partitioning for how the data will be queried, and separates event time from arrival time. It removes taps that readers re-send after an outage using a declared key, not the message ID. It re-processes late taps inside a window that still meets the 6 a.m. deadline. And it proves completeness by reconciling against a source that didn’t come through the same pipeline, such as each reader’s own tap counter or the fare-clearing house’s daily totals. If that list reads like jargon today, the series is for you. By Part 14 you’ll be able to defend the storage half, by Part 25 the batch pipeline, and by Part 35 the whole design.

Story Closing

Rohan didn’t fix the queries. The rest of the week went on running each one, logging the files it read, and writing one paragraph per number explaining why the answers differed. By Friday the stock and conversion gaps had explanations. The revenue gap could only be narrowed down: duplicates, probably; deletes, maybe; something with refunds.

Ingrid read the paragraphs and nodded at the revenue one. “Good. You’ve found out that you can’t find out. That’s where the job starts.”

Ingrid drew three boxes on the whiteboard and labelled them warehouse, lake and lakehouse. “Before we argue about tools, you need to know why your Postgres instincts are going to fight you. Tomorrow you’ll ask the same question three ways and measure the difference.”

In Part 1, Rohan answers one revenue question with PostgreSQL, with DuckDB over a folder of Parquet files, and with Trino over an Iceberg table, and learns why analytical systems store data the way they do.

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