7.8.2 — Warehouses, Lakes and the Pipelines Between Them

A board meeting has four revenue numbers on four slides. Finance took theirs from the billing system, sales from the CRM, the product team from the events pipeline, and the analyst from a spreadsheet.

Nobody is lying. Each number is correct for the definition its author used — one counted at order placement, one at payment capture, one included tax, one excluded refunds. The technical problem is that four systems each hold a partial copy of the same facts and nothing reconciles them. Everything in this chapter is machinery for producing one agreed answer.

1. Warehouse, lake, lakehouse

A data warehouse is a database built for analytics, holding modelled, cleaned, structured data. You decide the schema before loading — schema-on-write — and the load fails if the data does not fit. Snowflake, BigQuery, Redshift, Databricks SQL, Synapse. The output is trustworthy and the input is rigid: adding a source means designing its model first.

A data lake is cheap object storage holding files in whatever shape they arrived. Schema-on-read: you impose structure when you query. S3, Azure Data Lake Storage, Google Cloud Storage, holding Parquet, JSON, CSV, images, logs.

The lake's promise was "store everything now, work out what it means later". The failure mode is the data swamp: petabytes nobody can use, because nobody knows what a file contains, whether it is complete, when it was last written, or which of three similarly named folders is current. The lake did not fail on technology, it failed on governance — the discipline the warehouse enforced by refusing bad loads was simply removed.

A lakehouse is the reconciliation, and it is the current mainstream architecture. Keep the cheap open storage of a lake, and add a metadata layer that gives it the guarantees of a warehouse. That layer is a table format — Apache Iceberg, Delta Lake, or Apache Hudi — sitting over Parquet files.

What a table format actually adds:

• Atomic commits. A write either becomes visible in full or not at all, so a reader never sees a half-written table. This is the thing plain files cannot do.
• Snapshot isolation and time travel. Each commit produces a snapshot, so you can query the table as of last Tuesday, or compare two versions to find what a bad job changed.
• Schema evolution by column id rather than position, so adding, dropping and renaming columns does not corrupt old files.
• Row-level updates and deletes, needed for regulatory deletion requests on data that is otherwise append-only.
• Hidden partitioning (Iceberg): the query does not need to know the partition scheme, so partitioning can change without rewriting every query.

Iceberg has become the common denominator, readable and writable by Spark, Trino, Flink, Snowflake, BigQuery and DuckDB. That is the strategic point: your data stays in your own storage in an open format, and engines compete to query it. Compare that with a proprietary warehouse where the data is inside the product.

2. Separating storage from compute

The older warehouses — Redshift's original design, on-premises appliances — coupled the two: a cluster held the data on its own disks, so scaling capacity and scaling processing were the same knob, and the cluster ran whether or not anyone was querying.

Modern platforms separate them. Data lives in object storage; compute clusters start, read what they need, and stop. Three consequences follow, and they change how you use the system.

You pay for what you use, in seconds of compute or bytes scanned, rather than for a cluster running all night.

Workloads stop interfering. The finance team's heavy month-end job runs on its own compute against the same data as the dashboards, and neither slows the other. This is the same separation argument as Chapter 7.8.1's "do not run analytics on your transactional database", applied one level up.

Cost becomes a query-design problem. BigQuery charges by bytes scanned, so SELECT * on a wide table is expensive in money as well as time, and this is where the columnar mechanics of Chapter 7.8.1 pay directly:

• Select only the columns you need — bytes scanned is per column.
• Filter on the partition column so partitions are pruned, and check that the filter is a constant rather than a subquery, or pruning will not happen.
• Cluster by the columns you filter on most, so the min/max statistics can skip blocks.
• Materialise repeated heavy joins rather than paying for them in every dashboard refresh.
• Set a maximum bytes-billed limit on ad-hoc queries so a mistyped join cannot cost thousands.

3. MapReduce, and why it mattered

Google's 2004 MapReduce paper described how to process a dataset larger than any one machine, on unreliable commodity hardware, without the programmer thinking about failure.

You write two functions.

// Count words across a billion documents.
function map(docId, text) {
  for (const word of tokenize(text)) emit(word, 1);      // (1)
}
function reduce(word, counts) {
  emit(word, counts.reduce((a, b) => a + b, 0));         // (2)
}

(1) Map runs in parallel on every input split, on the machine that already holds that data, and emits key-value pairs. (2) Reduce receives all values for one key — the framework has gathered them — and combines them.

Between them sits the shuffle: every mapper's output is partitioned by key, sorted, and sent across the network to the reducer that owns that key. The shuffle is the expensive part, then and now, and understanding that one word explains most distributed data performance.

Why it won. Fault tolerance was automatic — a failed task is simply re-run, because map and reduce are pure functions over their inputs. That made processing on cheap unreliable machines practical for the first time, and Hadoop made it available to everyone.

Why it faded. Every stage wrote its results to disk before the next stage read them, so a five-stage job paid five full round trips to storage. Iterative work — most machine learning, most graph algorithms — was hopeless. And expressing a join or a group-by as map and reduce functions was verbose enough that whole layers (Hive, Pig) existed to generate them.

The model did not die; it moved. Map, shuffle, reduce is still exactly what happens inside a Spark job, a BigQuery query, and a Flink pipeline. What changed is that you no longer write the two functions by hand and the intermediate results no longer have to hit disk.

4. Spark

Spark's original contribution was keeping intermediate results in memory and building the whole job as a graph before running any of it.

A DataFrame is a distributed table with a schema. You describe transformations; nothing executes until an action (write, collect, count) forces it.

orders = spark.read.parquet("s3://lake/fact_orders/")     # (1)
result = (orders
    .filter(orders.placed_at >= "2026-01-01")             # (2)
    .join(dim_customer, "customer_key")                   # (3)
    .groupBy("region", "month")
    .agg(sum("revenue_minor").alias("revenue")))
result.write.mode("overwrite").parquet("s3://lake/marts/revenue/")   # (4)

(1) Reads metadata only — no data is scanned yet. (2) Lazy. (3) Also lazy; this is where a shuffle may be required. (4) The action. Only now does Spark plan and run: the optimiser pushes the filter down to the Parquet reader so pruned files are never opened, prunes columns, and chooses join strategies.

Two things determine whether a Spark job is fast, and both are about the shuffle.

A broadcast join avoids the shuffle entirely. If one side is small enough — a dimension table of ten thousand customers — Spark ships a copy to every executor and each joins locally. A shuffle join, by contrast, redistributes both sides across the network by join key. Getting a large dimension broadcast, or failing to, is often the difference between four minutes and forty.

Data skew is the other. If 40% of rows have customer_key = 'GUEST', one reducer receives 40% of the data and the job's runtime is that one task, while the rest of the cluster idles. The symptom is unmistakable: 199 tasks finish in seconds and one runs for an hour. Fixes are salting (append a random suffix to the hot key, aggregate twice) or Spark's adaptive query execution, which detects skewed partitions at runtime and splits them.

Spark's place in 2026 is narrower than it was, and the honest version matters: for SQL over tabular data, a warehouse or Trino is usually simpler and cheaper. Spark remains the right tool for complex non-SQL transformations, machine-learning pipelines, and jobs mixing code and data at large scale. And for anything under a few hundred gigabytes, a single machine with DuckDB or Polars is often faster than a cluster, because there is no shuffle and no coordination at all.

5. ETL, ELT, and why the order flipped

ETL — extract, transform, load. Transform in a separate system, load clean data into the warehouse. This is what you do when warehouse storage and compute are expensive, which they were.

ELT — extract, load, transform. Load raw data first, transform inside the warehouse using SQL.

ELT won because the economics inverted. Warehouse compute became elastic and cheap, so the most powerful engine available is the warehouse itself, and transformation logic in SQL is reviewable by analysts rather than locked in a separate framework. It also means the raw data is retained, so when a transformation turns out to be wrong you can rebuild from source instead of re-extracting from a system that has since changed.

dbt is the tool that made this a practice. You write a model as a SELECT, and the tool works out the dependency graph, materialises each model as a table or view, runs data tests, and generates documentation and lineage.

-- models/marts/fct_revenue.sql
SELECT
  d.month,
  c.region,
  SUM(o.revenue_minor) / 100.0 AS revenue
FROM {{ ref('stg_orders') }} o                    -- (1)
JOIN {{ ref('dim_customer') }} c USING (customer_key)
JOIN {{ ref('dim_date') }} d USING (date_key)
GROUP BY 1, 2

(1) ref() is what makes it a system rather than a folder of scripts: it declares a dependency, so the tool knows this model must run after stg_orders, can build the full graph, and can rebuild everything downstream of a change.

The layered convention that goes with it, named differently everywhere and identical in substance:

• Staging / bronze — raw source data, lightly typed, one model per source table, no business logic.
• Core / silver — cleaned, conformed, deduplicated; the dimensions and facts of Chapter 7.8.1.
• Marts / gold — shaped for consumption, one per business area.

The value is that a metric is defined once, in the core layer, and every dashboard reads it. That is the actual fix for the four-revenue-numbers meeting — not a tool, but a single place where "revenue" is defined and a graph that guarantees everyone downstream uses it.

6. Batch and streaming

Batch processes a bounded set on a schedule. Simple, restartable, easy to reason about, and the right answer far more often than the industry implies.

Streaming processes records as they arrive. Kafka (Chapter 10.8.2) as the transport, Flink or Spark Structured Streaming as the processor.

Streaming introduces three genuinely hard problems that batch does not have, and they are the reason to be conservative about adopting it:

Windowing. "Revenue in the last hour" needs a defined window: tumbling (fixed, non-overlapping), sliding (overlapping), or session (grouped by inactivity gaps).

Late data. A mobile client was offline and sends an event from three hours ago. Do you reopen the window, discard it, or emit a correction? Watermarks are the mechanism — a declaration that events older than X are no longer expected — and the choice of X is a direct trade between completeness and latency.

Event time versus processing time. Grouping by when the event happened is what the business means; grouping by when it arrived is what is easy. They differ, and mixing them silently produces wrong numbers.

The architectural shorthand: Lambda architecture runs a batch layer and a streaming layer in parallel and merges them, which means maintaining the same logic twice — a cost that eventually shows. Kappa architecture runs only the stream and reprocesses history by replaying it, which is simpler when the transport retains enough history.

The question to ask before either: what decision is made with this data, and how quickly? Fraud blocking and live operational dashboards genuinely need seconds. Most reporting is consumed once a day by a human. Hourly batch is a legitimate architecture, and choosing it over streaming is a senior decision rather than a lack of ambition.

7. Master data management and the golden record

Return to the opening problem, now stated exactly. The CRM has "Ana Ruiz, ana@x.com". Billing has "A. Ruiz, ana@example.com". Support has "Ana Ruiz-Martin". Are these one customer?

Master data management is the practice of maintaining one authoritative record for the entities the business shares — customer, product, supplier, location. The output is the golden record: the version everything else agrees to use.

Two hard steps.

Entity resolution — deciding which records are the same thing. Deterministic rules first (same national identity number, same email), then probabilistic matching that scores similarity across name, address, phone and date of birth. Techniques from Chapter 4.31 do real work here: edit distance for names, phonetic keys so Smith and Smyth compare, and blocking — grouping candidates by a cheap key such as postcode so you compare thousands of pairs instead of the n^2 of a full cross-product.

Then survivorship — deciding which value wins when the matched records disagree. Rules by source priority (billing wins on address, CRM wins on name), by recency, or by completeness. Write the rules down and version them, because "why does this customer show the wrong address" is a question you will be asked and must be able to answer.

Two architectural styles. A registry leaves the source systems alone and stores only the mapping between their identifiers plus the resolved rules — cheap and non-invasive. A central hub holds the mastered record and pushes it back to source systems — stronger and much more work, because it requires every system to accept being corrected.

The honest note: MDM projects fail more often than they succeed, and the reason is almost never technical. They require agreement across departments on definitions and on who owns a field, and that is an organisational negotiation with a database attached. Start with one entity and one clear pain, and be suspicious of any plan to master everything.

8. Governance: the part that decides whether any of it is used

A data catalogue lists what tables exist, what each column means, who owns it and when it last updated. Without one, the most common analytical activity is asking a colleague which table is the right one.

Lineage traces a number back through every transformation to its source. It answers "what breaks if I change this column" before the change, and "where did this wrong number come from" after it. dbt and the table formats generate much of it automatically.

Data quality tests belong in the pipeline, not in a person's memory: not null, unique, accepted values, referential integrity, and freshness. Freshness is the one people omit and the one that causes the most damage — a pipeline that silently stops publishes yesterday's numbers as today's, and nobody notices until a decision is made on them.

Access control and personal data. Warehouses support column-level masking and row-level policies, and the practical rule is to keep personal data out of the analytical layer unless a named use case requires it. Chapter 8.7 covers classification and the retention obligations.

What the interviewer will push on

"Warehouse, lake, or lakehouse?" Warehouse is schema-on-write and trustworthy but rigid; lake is schema-on-read and cheap but becomes a swamp without governance; lakehouse adds a table format (Iceberg, Delta, Hudi) over open files to get atomic commits, time travel and schema evolution on cheap storage. The tell is naming atomic commits as the specific thing plain files cannot do.

"What was MapReduce and why did it fade?" Map, shuffle, reduce, with automatic fault tolerance from re-running pure tasks — which made commodity clusters practical. It faded because every stage round-tripped through disk, making iterative work hopeless. Then the important point: the model did not die, it moved inside Spark and every warehouse engine.

"Why is my Spark job slow?" Almost always the shuffle. Check whether a small dimension is being broadcast rather than shuffled, and check for skew — 199 tasks finishing in seconds and one running for an hour is the unmistakable signature. Fix with a broadcast hint or salting.

"ETL or ELT?" ELT, because warehouse compute is elastic and cheap, transformation logic in SQL is reviewable, and keeping the raw data lets you rebuild when a transformation turns out to be wrong. That last reason is the one that separates a considered answer from a fashionable one.

"Do you need streaming?" Ask what decision is made with the data and how quickly. Fraud and live operations need seconds; most reporting is read once a day. Then name the three real costs — windowing, late data with watermarks, and event time versus processing time — and say plainly that hourly batch is a legitimate architecture.

"What is a golden record and why is it hard?" One authoritative record per shared entity, produced by entity resolution (deterministic rules, then probabilistic matching with blocking to avoid n^2 comparisons) and survivorship rules deciding which source wins per field. It is hard because it requires cross-department agreement on definitions and ownership, which is why most failures are organisational rather than technical.

One thing to volunteer: mention freshness tests. Every pipeline has null and uniqueness checks and almost none check that data actually arrived — so a silently stopped job republishes yesterday's numbers as today's and nobody notices until a decision is made on them. It is the cheapest test to add and the most expensive one to omit.

Recall

• Warehouse = schema-on-write, trustworthy, rigid. Lake = schema-on-read, cheap, becomes a data swamp without governance. Lakehouse = a table format (Iceberg, Delta, Hudi) over Parquet in open storage, adding atomic commits, time travel, schema evolution and row-level deletes.
• Separating storage from compute means paying per use, isolating workloads, and making cost a query-design problem — select fewer columns, filter on the partition column with a constant, cluster on filter columns, cap bytes billed.
• MapReduce: map, shuffle, reduce, with fault tolerance from re-running pure tasks. It faded because every stage round-tripped to disk. The model still runs inside Spark and every warehouse engine.
• Spark is lazy until an action; the optimiser pushes filters into the Parquet reader. Job speed is decided by the shuffle: broadcast the small side to avoid it, and fix skew (199 fast tasks and one slow one) with salting or adaptive execution.
• ELT beat ETL because warehouse compute got cheap, SQL is reviewable, and retaining raw data lets you rebuild when a transformation was wrong. dbt's ref() builds the dependency graph; staging → core → marts defines each metric once.
• Streaming's three real costs are windowing, late data (watermarks), and event time versus processing time. Lambda maintains the logic twice; Kappa replays instead. Hourly batch is a legitimate architecture.
• A golden record needs entity resolution (deterministic rules, then probabilistic matching with blocking to avoid n^2 comparisons) and survivorship rules that must be written down and versioned. Failures are organisational, not technical.
• Governance decides whether any of it is used: a catalogue, lineage, and quality tests — including freshness, the one nobody adds and the one that publishes yesterday's numbers as today's.

Self-test: What does a table format do that a folder of Parquet files cannot? · Why did MapReduce fade, and what survived of it? · Name the two shuffle problems that decide a Spark job's runtime · Why does retaining raw data justify ELT on its own? · What is blocking, and what does it prevent? · Which data-quality test is most often missing?

Next: Part 8 opens the other half of production engineering — the security mindset, cryptography from first principles, and the identity systems that every backend eventually has to integrate.