Every Record Counts: Data Engineering for Backend Engineers
You can scale a payment service in your sleep, so why does the revenue dashboard disagree with itself? A three-season field guide to data engineering for backend engineers: warehouses, lakehouses and Iceberg tables, pipelines that survive re-runs, streams that respect event time, and AI that only sees numbers you can reconcile. Every record counts; we count them twice.
"The Field Guide" — How to Read This Series
Larkspur's quarterly review ends with three headline numbers and two answers for each. Rohan, a staff backend engineer, is asked to "just fix the queries". This field guide maps data engineering in 2026, introduces the retailer and its incident, starts the companion lab, reproduces all three disagreements with SQL, and states the design question the series teaches you to answer.
"Three Houses for Data" — Warehouse, Lake, Lakehouse
Rohan wants a bigger read replica. Ingrid runs an experiment instead: six million sales lines in PostgreSQL, a folder of Parquet files and an Iceberg table, the same questions asked of each and one row corrected in each. OLTP vs OLAP, row vs column storage, and how warehouses, lakes and lakehouses differ.
"Think in Pipelines" — The Data Engineer's Mental Model
Rohan's first daily sales job doubles a day's revenue on a retry and loses late till uploads. Rebuilding it teaches the data engineer's mental model: pipelines as functions, idempotency, partition-scoped overwrites, raw/silver/gold layers, and the five promises behind "every record counts".
"Bytes on Disk" — File Formats, Parquet and Object Storage
Rohan writes the same six million sales lines as CSV, JSON, Avro and Parquet and asks object storage for them back. Row vs columnar formats, what is inside a Parquet file, the sort order that lets a filter skip 49 of 50 row groups, Apache Arrow, and what object storage gives you instead of a filesystem.
"Grain Before Glory" — Dimensional Modeling
A discount report that counted each basket once per line, stock summed across days, refunds in the wrong quarter. Rohan builds Larkspur's first star schema in DuckDB: grain, facts and dimensions, fact table types, slowly changing dimensions, as-of joins and early-arriving facts.
"Other Shapes" — Data Vault, Wide Tables and Modeling Trade-offs
Whose midnight cuts the quarter, and which rate converts the euros? Rohan models Larkspur's refunds as a Data Vault and as one wide table, then fixes the primitives under every model: money, time zones, FX as of the event and bitemporal history. Two revenue defects close to the penny and stock availability drops to 88.5%.
"SQL You Will Actually Use" — Analytical SQL for Data Engineers
Rohan wants to loop over events in Java; Ingrid hands over a SQL workbook instead. Window functions, QUALIFY, gaps and islands, sessions, PIVOT, CUBE, HyperLogLog, MERGE, high-water marks and query plans in DuckDB and Trino find the 2,455 re-sent UK baskets behind £118,076.98 and explain why conversion reads 6.21% or 5.31%.
"The Table That Remembers" — Apache Iceberg in Depth
Q3 is closed, and UK till sales just moved by £3,494.82. Ingrid opens an Iceberg table file by file: a catalog pointer, JSON metadata, Avro manifests, Parquet. Snapshots, optimistic commits and a real conflict between Spark and PyIceberg, schema and partition evolution without rewrites, time travel, tags and branches, copy-on-write vs merge-on-read, and a format v3 table with VARIANT and deletion vectors.
"Format Wars, Ceasefire" — Delta, Hudi, Paimon, DuckLake and Interoperability
Rohan builds a feature matrix to pick a table format and every row says yes. Ingrid puts one gold table in Iceberg, Delta Lake and DuckLake and runs one late basket through each: six files, four files, or two inlined rows and a snapshot record in PostgreSQL. Delta logs and checkpoints, Hudi and Paimon for upserts, DuckLake's SQL catalog and data inlining, UniForm and XTable, and why the catalog decides more than the format.
"Who Owns the Truth" — Catalogs and the Iceberg REST Protocol
Finance wants to query gold sales, and Rohan offers them a read key for the bucket. Ingrid shows what else that key reads. The catalog as control plane: from Hive Metastore to the Iceberg REST protocol, Polaris principals, roles and grants, credential vending through STS, four engines on one table, and an analyst refused on raw by the catalog and by the storage itself.
"Where the Bytes Live" — Partitioning, File Layout and Table Maintenance
Four years of sales lines are coming, and Rohan wants partitions by day, country and store, like indexes. Ingrid builds the same 72.6 million rows three ways and measures. Partition goals and anti-goals, Iceberg transforms, event date vs ingestion date, sort orders and Z-order, the small-file problem, compaction, snapshot expiry, orphan files, and a partition-strategy playbook.
"You Might Not Need a Cluster" — DuckDB, Polars and Single-Node Analytics
Rohan wants a Spark cluster for four years of sales lines. Ingrid runs the same Iceberg table on a laptop with DuckDB and Polars instead, and profiles it against Spark. Vectorised execution, out-of-core processing, Arrow hand-offs, DuckDB 2.0 and Polars 2.x over a REST catalog, and the signals that say one machine is no longer enough.
"Shuffle, Spill and Skew" — Distributed Batch with Spark 4.1
A campaign will book 30% of Larkspur's sales lines to one store ID, and Rohan's answer is more executors. Ingrid rehearses it in PySpark instead: stages and lazy plans, Catalyst, shuffles, broadcast vs sort-merge joins, AQE, skew, salting and spill, measured from the Spark UI; plus ANSI mode, Spark Connect, Declarative Pipelines and Spark 4.2.
"Engines at Scale" — Trino, Cloud Warehouses and Real-Time OLAP
Three hundred people open the stores dashboard at 9 a.m. Trino, federation with PostgreSQL, ClickHouse, cloud warehouse billing, real-time OLAP, caches and materialised views, measured under load.
"Design Review I" — The Storage and Compute Layer
Season 1 ends with a design review. Rohan defends Larkspur's storage, table format, catalog, partitioning and engines against finance's volumes, retention, freshness, concurrency and cost, in interview order: clarify, estimate from the lab's own files, choose, justify, trade-offs, and what would change the decision. Plus a self-host vs managed matrix, a printable checklist and a worked drill.
"Getting Data In" — Batch Ingestion: Files, APIs and Databases
A supplier renames one column and Larkspur's scorecard says it delivered nothing for six weeks. Season 2 opens with ingestion as a contract with systems you don't control: full vs incremental extraction, the three traps of a high-water mark, landing zones and an immutable raw zone, pagination, rate limits and retries, schema drift and load metadata, built with dlt into Iceberg through Polaris.
"Changes, Not Snapshots" — CDC into the Lakehouse
Rohan wants to append Debezium's events to the lake and call it a table. Ingrid insists on folding them into state instead: upserts, hard and soft deletes, ordering by LSN, an initial snapshot plus a stream, a history table beside a current-state table, MERGE vs equality deletes, idempotent micro-batches and compaction. Built with Debezium 3.7, Kafka 4.4 and Spark Structured Streaming into Iceberg, it finally removes the £57,064.96 of hard-deleted orders from the 0.7%.
"Transformations as Code" — dbt Fundamentals
Rohan wants to put the star schema's SQL in a repo and a cron job. Ingrid wants it treated like the order service: modules, a dependency graph, tests, review. Models, sources and refs, materialisations, snapshots, data and unit tests, docs, and dbt v1 vs v2. Built with dbt 1.12 and dbt-trino on Iceberg, it reproduces Part 5's corrected numbers to the penny; dbt-oss 2.0 then runs a slice of it on DuckDB.
"dbt at Scale" — Incremental Models, CI and Environments
Rohan wants a high-water mark on event time to stop rebuilding the sales fact every night. Ingrid makes Rohan replay two weeks of till uploads first. Incremental strategies and how each treats late data, microbatch and its lookback, full-refresh policy, dev and prod targets with their own principals, state-based slim CI with deferral, contracts and model versions. On dbt 1.12 and Trino, the naive model loses 7,146 sale lines; microbatch with a two-day lookback matches a full rebuild row for row.
"Who Runs What, When" — Orchestration with Airflow 3
Rohan wants a cron entry and a Spring service with a retry loop. Ingrid wants a record of every day the pipeline ran, a way to re-run any of them, and no business logic in the scheduler. DAGs, logical dates and data intervals, backfills, assets and asset partitioning, retries, pools, sensors and deferrable operators, Deadline Alerts and DAG versioning, with Dagster and Prefect compared. On Airflow 3.3, a month-long backfill republishes the daily revenue report and UK Q3 stays at £29,790,674.06.
"Trust, but Test" — Data Quality and Data Contracts
Rohan wants to add null checks to every dashboard query. Ingrid wants the producers and consumers to write down what the data promises, and a pipeline that tests the promise where data crosses a boundary. Quality dimensions, where to test, quarantine vs fail-fast, ODCS 3.2 data contracts tested with datacontract-cli, a local CI gate for breaking changes, and Write-Audit-Publish on an Iceberg branch in Spark. The session contract settles online conversion at 6.21%.
"Every Record Counts" — Completeness and Correctness Engineering
Farah wants the 0.7% explained to the record. Rohan proves it: the board-deck waterfall closes to the penny, every pound the payment provider settled for the UK is traced to gold revenue or to a legitimate reason, and re-sent till baskets are decided once at the door and kept out of gold. Completeness and uniqueness as engineered properties: natural dedup keys, effectively-once delivery, control and hash totals at every hop, a daily control table gating publish in Airflow, late-data policy, restatement and a replay runbook.
"Know Before They Do" — Observability, Lineage and SLAs
Part 21's gate held the revenue report back, correctly, and the first to notice was Farah. Rohan makes the data page the team instead: freshness, completeness, volume and schema as SLOs with an error budget counted in days, OpenLineage from Airflow, dbt and Spark loaded into OpenMetadata to trace the sales and stock reports to their sources, OpenTelemetry for the runs, and a "the dashboard is wrong" runbook.
"Locks on the Lake" — Security, Privacy and Compliance
A customer asks Larkspur to forget them, and Rohan reaches for one DELETE. Ingrid counts the copies first. Personal data found and tagged, masks and row filters per user through Trino and Open Policy Agent, dev locked out of production, the lab's Season 1 shortcuts closed, and one erasure carried through rows, snapshots, tags, manifests, raw files and disks until a byte-level search finds nothing, under GDPR, India's DPDP Act and Singapore's PDPA.
"The Bill Always Comes" — Cost and Performance Engineering
Finance asks what the platform will cost per month and per report, and Rohan reaches for a cluster size. Ingrid meters the platform instead: bytes stored, bytes scanned, CPU and requests, priced with list prices as inputs. The biggest lines turn out to be one dashboard tile, a nightly run rereading history, and storage no table owns. Three fixes, each measured before and after, plus FinOps unit economics and the FOCUS billing schema.
"Design Review II" — The Batch Reporting Platform, End to End
Season 2 ends where it began: with Farah and a quarter to sign. Rohan defends Larkspur's batch platform end to end in interview order, from sources and ingestion through contracts, reconciliation, governance and cost: sized from the lab's own tables, with every failure mode named against the part that caught it, what changes at ten times the volume, and what still isn't finished. Then the board pack is recomputed from the lakehouse and the restatement approved. Plus a self-host vs managed matrix, a printable checklist and a clinical-trials drill.