Analytics, streaming, warehouses, lakes and data mesh

Two different workloads: OLTP and OLAP

Everything in this chapter so far has been about online transaction processing (OLTP): many small, fast reads and writes of individual records, such as placing an order, posting a payment or updating a courier's position. Analytics asks a different kind of question. What was revenue by cuisine and region, week by week, for the last two years? touches millions of rows, reads only a few columns of each, and changes nothing. This is online analytical processing (OLAP), and it is so different from OLTP that running both on the same database usually serves neither well: one large analytical query can saturate the disks and buffer cache that the checkout depends on, and the normalised schema that keeps transactions correct forces analysts to join a dozen tables.

The standard architectural answer is to separate the workloads. Operational systems keep their own databases, owned by their services as Section 7.1 described. Data is copied, continuously or periodically, into an analytical platform designed for scans: columnar storage that reads only the needed columns, heavy compression, massively parallel query engines and schemas shaped for analysis. The rest of this section is about how that platform is designed, how data reaches it, how streaming changes the picture and who owns the result.

Data warehouses and dimensional modelling

A data warehouse is a database built for analysis that integrates cleaned data from many operational systems and keeps its history. Two schools shaped how warehouses are designed, and practitioners still argue about them. Bill Inmon, often called the father of the data warehouse (Building the Data Warehouse, 1992), advocates a top-down design: an enterprise-wide, normalised, integrated warehouse is built first as the single source of truth, and departmental data marts are then derived from it. Ralph Kimball (The Data Warehouse Toolkit, first published 1996, third edition with Margy Ross in 2013) advocates a bottom-up design: build dimensional models one business process at a time, and integrate them through shared, conformed dimensions (one agreed definition of customer, date or restaurant used by every model). Inmon's approach gives stronger integration at a higher up-front cost; Kimball's delivers usable results sooner but needs discipline to keep the dimensions conformed. Many modern platforms blend the two: a cleaned, integrated layer feeding dimensional models for consumption.

Kimball's star schema remains the most widely taught analytical model. A central fact table records business events at a declared grain (one row per order line, per payment, per courier ping), with numeric measures such as quantity, amount and minutes, and foreign keys to dimension tables. Each dimension holds descriptive attributes used to filter and group: dates (with weekday, month and bank-holiday flags precomputed), restaurants (name, cuisine, city), customers and dishes. Queries become one fact table joined to a few small dimensions, which columnar engines execute very efficiently. Attributes that change over time, such as a restaurant moving city, are handled by slowly changing dimension techniques; the most common, type 2, adds a new dimension row with validity dates, so historical facts still point to the attributes that were true at the time.

-- Revenue and average delivery time by cuisine and weekday for last quarter.
-- One join per dimension; the fact table is scanned, never updated.
SELECT r.cuisine,
       d.weekday,
       SUM(f.line_total_pence) / 100.0 AS revenue_gbp,
       AVG(f.delivery_minutes)          AS avg_delivery_min
FROM   fact_order_line f
JOIN   dim_date       d ON d.date_key       = f.date_key
JOIN   dim_restaurant r ON r.restaurant_key = f.restaurant_key
WHERE  d.quarter = '2026-Q2'
  AND  r.region  = 'North West'
GROUP  BY r.cuisine, d.weekday
ORDER  BY revenue_gbp DESC;
-- Caveat: delivery_minutes is per order, so averaging it over order LINES
-- weights big orders more. Grain decisions matter (see the worked example).

Getting data in: ETL, ELT and change data capture

Data reaches the warehouse through pipelines. In classic ETL (extract, transform, load), a separate tool extracts data from source systems, cleans and reshapes it on its own servers, and loads only the result. Since cloud warehouses made storage cheap and compute elastic, ELT has become common: extract and load raw data first, then transform it inside the warehouse with SQL, often managed as version-controlled, tested models with a tool such as dbt. ELT keeps the raw data, so transformations can be fixed and rerun; its risk is that raw personal data spreads further than it needs to.

How data is extracted matters as much. Nightly full copies are simple but slow and stale. Change data capture (CDC) reads the database's own replication log (PostgreSQL's write-ahead log, MySQL's binlog) with a tool such as Debezium and streams every insert, update and delete as an event, with low latency and little load on the source. Whatever the mechanism, reading another service's tables directly couples the analytics platform to that service's private schema, exactly the problem Section 7.1 described. The more robust pattern is for each service to publish data deliberately, through events or an agreed export, under a data contract.

Data lakes, lakehouses and open table formats

A data lake (the term is credited to James Dixon, 2010) stores raw data from many sources in its original form, usually as files on cheap object storage such as Amazon S3, often in columnar formats like Apache Parquet. It defers the schema until the data is read, which suits data scientists who want raw detail and suits data whose use is not yet known. Lakes earned a reputation for becoming data swamps: without ownership, documentation and quality checks, nobody trusts what is in them. Files on object storage also lack what a warehouse provides: transactions, updates and deletes, schema enforcement and consistent reads while data is being written.

The lakehouse (Armbrust, Ghodsi, Xin and Zaharia, CIDR 2021) adds those warehouse features on top of open files in the lake. The enabling technology is the open table format: Delta Lake (created at Databricks), Apache Iceberg (created at Netflix) and Apache Hudi (created at Uber). Each keeps a metadata layer (a transaction log or a tree of manifest files) that records which data files make up each version of a table. That gives atomic commits, concurrent writers with optimistic concurrency, schema evolution, row-level updates and deletes (needed for erasure requests), and time travel: querying a table as it was at an earlier version. Because the format is open, several engines (Spark, Trino, Flink, and increasingly the cloud warehouses themselves) can read and write the same tables, which reduces lock-in. Many lakehouses organise data in layers, popularised by Databricks as the medallion architecture: bronze for raw ingested data, silver for cleaned and conformed data, gold for business-level models such as star schemas.

Stream processing: event time, windows and watermarks

Batch pipelines answer questions about yesterday. Some questions cannot wait: is this card being used fraudulently right now, are couriers in Manchester running late this evening, which restaurants should be paused because their kitchens are overwhelmed? Stream processors such as Apache Flink, Kafka Streams and Spark Structured Streaming compute continuously over unbounded streams of events. The essential concepts come from Google's Dataflow model (Akidau and colleagues, VLDB 2015).

The first is the difference between event time, when something actually happened, and processing time, when the system observes it. They differ because of network delays, retries, batching and devices that go offline: a courier's phone in a lift records a handover at 19:03 but delivers the event at 19:13. Any result that should reflect reality (orders per five minutes, average delivery time per hour) must be computed by event time, or it will shift whenever the pipeline is slow and be wrong when it is replayed.

The second is windows, which cut an unbounded stream into finite pieces to aggregate: tumbling windows are fixed and non-overlapping (every five minutes), sliding windows overlap (the last ten minutes, updated every minute) and session windows group bursts of activity per key separated by a gap of inactivity (one customer's browsing session).

The third is the watermark: the processor's moving estimate that all events up to a given event time have probably arrived. When the watermark passes the end of a window, the window's result can be emitted. Watermarks are heuristics, so some events still arrive after their window has closed. The pipeline needs an explicit policy for such late data: drop it, send it to a side output for later correction, or keep windows open for an allowed lateness and emit updated results. Waiting longer gives more complete answers; emitting sooner gives fresher ones. That latency-completeness trade-off is the stream-processing version of a quality attribute trade-off and should be decided with the business.

Lambda and Kappa architectures

How should batch and streaming results be combined? Nathan Marz's 2011 essay How to beat the CAP theorem described what became known as the Lambda architecture: an immutable master dataset feeds a batch layer that periodically recomputes accurate views from all data, and a speed layer that computes approximate views of recent data in real time; queries merge the two, and each batch run replaces the speed layer's approximations. Lambda gives accuracy and low latency, and the batch layer acts as a safety net for streaming bugs. Its cost is that the same logic must be written and kept identical in two different frameworks.

In 2014 Jay Kreps, one of the creators of Apache Kafka, questioned this design and proposed the Kappa architecture, a name he offered half in jest. Use only a stream processor, and retain the input in a log long enough to replay it. To change the logic, start a second instance of the new job reading from the beginning of the log into a new output table, and switch readers to it once it has caught up. One code base, and reprocessing is just replaying. Kappa depends on long log retention and on stream processors good enough to handle event time and state correctly, which modern engines provide. Today most new designs lean towards Kappa-style streaming with a lakehouse for history, while Lambda-style designs persist where batch recomputation over very long histories is cheaper or where existing batch pipelines are trusted.

Data mesh: ownership of analytical data

As organisations grew, central data teams became bottlenecks: every new source and every new question queued behind one team that understood neither the source systems nor the business questions very well. In 2019 Zhamak Dehghani, then at Thoughtworks, proposed the data mesh (later expanded in her 2022 book Data Mesh). It applies to analytical data the same decentralisation that microservices applied to operational systems, and it rests on four principles.

Domain ownership: the teams closest to the data, such as orders, payments or couriers, own and serve their analytical data, just as they own their services. Data as a product: each team publishes data products that are discoverable, documented, trustworthy, addressable and secure, with an owner and quality objectives, treating other teams as customers. Self-serve data platform: a platform team provides the storage, pipelines, catalogue and access control that make publishing a data product cheap, so each domain does not reinvent them. Federated computational governance: global rules (identifiers, privacy classifications, interoperability standards) are agreed jointly and enforced automatically by the platform rather than by a central review board.

Data mesh is an organisational design as much as an architecture, and opinions differ on when it pays. Its advocates point to scale: it removes the central bottleneck and puts data quality where the knowledge is. Its critics point out that it needs mature domain teams, a strong platform and real investment, and that smaller organisations are usually better served by a well-run central platform with clear ownership of each dataset. Conway's law (Section 3.7) applies directly: the data architecture will mirror whichever structure the organisation actually adopts.

Quality, lineage, catalogues and privacy

Analytical data is only useful if people trust it. Data quality checks run inside pipelines and test freshness (did today's load arrive?), volume (did the row count drop by half?), schema (did a column change type?), validity (are amounts positive and postcodes well formed?) and uniqueness. Failing checks should stop a pipeline or quarantine data rather than silently publish it. Data lineage records where each dataset came from and which transformations produced it, so that when a figure looks wrong, or a source schema changes, you can trace the impact upstream and downstream; the OpenLineage specification standardises how pipelines report it. A data catalogue makes datasets discoverable, with descriptions, owners, classifications and lineage in one place.

Analytics platforms concentrate data, which makes them attractive to attackers and risky under data protection law. Apply data minimisation (do not copy personal fields that no analysis needs), pseudonymise identifiers where possible, classify columns so that access policies follow the data, enforce retention periods with automated deletion, and make sure erasure requests reach the lake and warehouse as well as the operational systems; open table formats help because they support row-level deletes. Section 8.7 covers the obligations in detail.