1.1 Operational vs Analytical Systems
Key observation: analysts and scientists both read data that users and backend services generated, and they do not modify it (they may create derived datasets).
Row vs column store
- 3
- of 100 cols
- 80 GB
- read
The row store reads all 100 columns to answer a query about 3. 97% of that I/O is wasted. This is fine for OLTP, where you fetch a few whole records by key — and ruinous for a scan over the whole table.
1.1 The people, because the split is a people-split first
| Role | What they do | Which system |
|---|---|---|
| Backend engineer | builds services that read & modify data | Operational |
| Business analyst | reports for management (BI) | Analytical |
| Data scientist | novel insight, ML/AI features | Analytical |
| Data engineer | integrates operational ↔ analytical, owns the data infra | The bridge |
| Analytics engineer | models/transforms data so analysts & scientists can use it | Analytical |
Key observation: analysts and scientists both read data that users and backend services generated, and they do not modify it (they may create derived datasets). That read-only, derived nature is exactly why the systems can be split.
1.2 OLTP vs OLAP — the access-pattern difference
OLTP (online transaction processing). Historically a "transaction" was a commercial transaction (a sale, a payroll run). The name stuck even for social posts and game moves, because the access pattern stayed the same:
- Point query — look up a small number of records by key
- Insert / update / delete individual records driven by user input
- Interactive, so latency-sensitive
OLAP (online analytical processing). An analytical query scans a huge number of records and computes aggregates (count, sum, avg) rather than returning individual records.
Example analytical questions from a supermarket chain:
- What was the total revenue of each store in January?
- How many more bananas than usual did we sell during the promotion?
- Which brand of baby food is most often bought together with brand X diapers?
| OLTP — a needle out of a haystack | OLAP — weigh the whole haystack |
|---|---|
SELECT * FROM orders WHERE order_id = 4712; | SELECT store, sum(amount) FROM orders WHERE date BETWEEN … GROUP BY store; |
| One row, fetched by key. All columns, one record. | Two columns across ~109 rows. Almost no columns, almost every record. |
| Latency per request is what matters; the working set is small and hot. | Bytes scanned is what matters; the working set is the whole table. |
The comparison table (worth internalizing — Table 1-1):
| Property | Operational (OLTP) | Analytical (OLAP) |
|---|---|---|
| Main read pattern | Point queries by key | Aggregate over many records |
| Main write pattern | Create/update/delete individual records | Bulk import (ETL) or event stream |
| Human user | End user of web/mobile app | Internal analyst, decision support |
| Machine user | Checking whether an action is authorized | Detecting fraud/abuse patterns |
| Query shape | Fixed, predefined in application code | Arbitrary, ad-hoc exploration |
| Query volume | Many small queries | Few queries, each complex |
| Data represents | Latest state (current point in time) | History of events over time |
| Dataset size | GB → TB | TB → PB |
Two consequences that follow directly:
- OLTP users are not allowed to write raw SQL (permission leakage + one bad query tanks everyone's latency). OLAP users are given arbitrary SQL, or a BI tool (Tableau, Looker, Power BI) that generates it.
- "Latest state" vs "history of events" is the deepest difference. It drives the storage layout in Ch 4, and the whole immutability philosophy in Ch 12–13.
1.3 The third category: real-time / product analytics
Analytical workload (aggregates over many rows), but embedded in a user-facing product, so it needs OLTP-grade latency.
- Systems: Apache Pinot, Apache Druid, ClickHouse
- Ingest in real time, optimized for low-latency query response
- Contrast traditional OLAP: ingest in batches, optimized for high-throughput query processing
This is the category most modern "analytics dashboard in the product" features land in.
1.4 Data warehousing
The problem it solves. In the late 1980s/early 1990s companies stopped running analytics on their OLTP databases. Why running analytics directly on OLTP systems fails:
- Data silos — the data of interest is spread across many operational systems, so you can't join it in one query. A large enterprise has dozens-to-hundreds of OLTP systems (website, point-of-sale, warehouse inventory, vehicle routing, supplier management, HR…), each complex, each with its own team, each operating independently.
- Wrong schema — layouts good for OLTP are bad for analytics (see star schemas, Ch 3).
- Performance interference — analytical queries are expensive; running them on OLTP hurts real users.
- Network/compliance isolation — OLTP systems often sit in a network analysts may not access.
A data warehouse is a separate database holding a read-only copy of data from all the OLTP systems, which analysts can query to their hearts' content without affecting OLTP.
ETL vs ELT. ETL = extract, transform, then load. ELT = swap the last two: load raw, transform inside the warehouse. ELT is now more common because warehouse compute got cheap and elastic.
ETL from SaaS. When the source is an external SaaS product (CRM, email marketing, credit card processing) you have no DB access — only the vendor's API. Specialist connector services do this: Fivetran, Singer, Airbyte. Bringing SaaS data into your own warehouse enables analyses the SaaS API can't do.
1.5 HTAP — and why it does not kill the warehouse
HTAP (hybrid transactional/analytical processing) aims to serve OLTP and analytics in one system, no ETL.
Critical realism from the book: many HTAP systems internally consist of an OLTP system coupled with a separate analytical system, hidden behind a common interface. The distinction doesn't disappear; it gets hidden. So you still need to understand it to reason about performance.
And structurally HTAP can't replace a warehouse:
- Good practice = each operational service owns its own database → potentially hundreds of operational DBs.
- An enterprise wants one warehouse so analysts can join across systems in a single query.
HTAP's real niche: one application that must both scan many rows analytically and read/update individual records at low latency. Canonical example: fraud detection.
Wider trend this is an instance of: the greater the scale, the more specialized systems become. General-purpose systems handle small volumes fine; "one size fits all" stops being true as you scale (Stonebraker & Çetintemel).
1.6 Data warehouse → data lake
Warehouses use a relational model queried via SQL. Great for analysts. Bad for data scientists, who need:
- Feature engineering — turning rows/columns into a vector or matrix of numbers (features) to train an ML model, in a way that maximizes model performance. Needs custom code that's awkward in SQL.
- NLP on text (e.g. extracting sentiment or topics from product reviews), computer vision on images — extracting structured info from unstructured data.
Despite efforts to add ML operators to SQL, data scientists largely prefer Pandas, scikit-learn, R, Spark.
A data lake is a centralized repository holding a copy of any data that might be useful for analysis, obtained from operational systems via ETL — but it just contains files, imposing no particular file format, data model, or schema.
- Files may be database records encoded as Avro or Parquet (Ch 5), or text, images, video, sensor readings, sparse matrices, feature vectors, genome sequences — anything.
- Cheaper than relational storage, because it uses commoditized object storage.
The sushi principle: "raw data is better." ETL generalized into data pipelines, and the lake became an intermediate stop on the way to the warehouse. The lake holds the raw form, so each consumer transforms it into the shape that suits them rather than being forced through one team's schema decision.
1.7 Beyond the data lake
Three forces reshaping the analytical side:
- Governance / privacy / compliance — GDPR, CCPA; the DataOps Manifesto captures the operational maturity push.
- Streams, not just files and tables — with files you rerun the analysis daily; stream processing lets analytics respond in seconds. That matters for e.g. identifying and blocking fraudulent or abusive activity.
- Reverse ETL — pushing analytical outputs back into operational systems. Example: an ML model trained in the analytical system, deployed to production to generate "people who bought X also bought Y." Tools: TFX, Kubeflow, MLflow.
1.8 Systems of record vs derived data — the single most useful lens in the book
System of record (source of truth): holds the authoritative/canonical version. New data is written here first. Each fact appears exactly once (normalized). If another system disagrees, the system of record is by definition correct.
Derived data system: the result of transforming data from another system. If you lose it, you can re-create it from the source. Examples: caches, denormalized values, indexes, materialized views, transformed representations, ML models trained on a dataset.
Derived data is technically redundant — it duplicates information — but it's essential for read performance, and you can derive several datasets from one source to view the data from different angles.
Crucially: most databases, storage engines, and query languages are not inherently one or the other. A database is just a tool. Whether it's a system of record or derived data depends on how you use it. Being explicit about which data derives from which brings clarity to otherwise confusing architectures.
The gap the book keeps returning to: many databases assume your app will only ever use that one database, and make it hard to propagate updates to other systems. Data pipelines (Ch 11) and CDC (Ch 12) are the answer.