OLTP vs OLAP: why analytics gets its own systems
If you remember one distinction from this whole guide, make it this one. Almost every tool you'll meet — warehouses, columnar file formats like Parquet, Spark, the lakehouse — exists because of a single fact: the systems that run a business are built completely differently from the systems that analyze it. Those two worlds are named OLTP and OLAP, and confusing them is the root of more bad data architectures than any other mistake.
The plain-English on-ramp
Picture two very different jobs at ShopFlow, our online store.
Job one — the checkout. A customer buys one coffee maker. The app must: look up that one product, write one row to orders, decrement stock by one, and do it in well under a second, correctly, even while thousands of other shoppers check out at the same time. It touches a tiny amount of data, very fast, many times a second. This is transactional work.
Job two — the end-of-quarter analysis. The owner asks, "What were total sales by category, by region, by month, for the last three years?" Answering that means reading millions of past orders and crunching them into a summary. It touches a huge amount of data, occasionally, in big sweeping reads. This is analytical work.
You would never build the cash register and the quarterly-analysis machine the same way — their needs are opposites. So we don't. OLTP systems are built for job one; OLAP systems are built for job two. The entire field of data engineering exists, in large part, to get data out of the OLTP world and into the OLAP world so the analysis can happen without grinding the registers to a halt.
Defining the two terms
- OLTP — Online Transaction Processing. Systems optimized for many small, fast reads and writes: the operational databases that run applications. A transaction here means one small, indivisible unit of work (record this sale; transfer this money). OLTP cares about being correct and fast for one row at a time, under heavy concurrent traffic. Example: the PostgreSQL database behind ShopFlow's checkout, holding the
ordersandorder_itemstables. - OLAP — Online Analytical Processing. Systems optimized for large analytic queries over lots of rows: scanning, filtering, and aggregating millions or billions of rows to produce summaries. OLAP cares about reading enormous amounts of data efficiently for analysis. Example: a data warehouse like BigQuery, Snowflake, or Redshift (all dated brand names for the same durable idea).
The words are clunky and decades old, but they're universal — you'll see them everywhere, so learn them now.
The workloads are opposites
| OLTP (operational) | OLAP (analytical) | |
|---|---|---|
| Typical query | Read/update one or a few rows | Scan/aggregate millions of rows |
| Read pattern | "Give me order_id 8841" | "Sum revenue by category, 3 years" |
| Write pattern | Constant small writes | Bulk loads, rarely updated |
| Concurrency | Thousands of users at once | A handful of analysts/jobs |
| Latency target | Milliseconds | Seconds to minutes is fine |
| Columns touched | Usually all columns of one row | A few columns of many rows |
| Optimized for | Correctness + speed per transaction | Throughput over huge scans |
Read that last "Columns touched" row twice — it is the key that unlocks why the storage is physically different.
Row stores vs column stores: the physical reason
Data has to be laid out somehow on disk, and there are two natural choices. This single decision is what makes OLTP and OLAP systems physically different machines.
Imagine a tiny slice of ShopFlow's orders table (with each order's product category folded in — the analytics-friendly shape):
| order_id | customer_id | category | amount |
|---|---|---|---|
| 1 | 7 | coffee | 79 |
| 2 | 9 | tea | 12 |
| 3 | 4 | coffee | 99 |
A row store keeps each row together on disk, one after another:
[1, 7, coffee, 79] [2, 9, tea, 12] [3, 4, coffee, 99]
A column store (columnar) keeps each column together on disk:
order_id: [1, 2, 3] customer_id: [7, 9, 4]
category: [coffee, tea, coffee] amount: [79, 12, 99]
Now watch how each layout serves each workload — this is the whole point of the lesson:
OLTP query — "give me everything about order_id 2." You want all columns of one row. The row store has [2, 9, tea, 12] sitting together in one place — read it in a single shot. The column store would have to hop to four different places (one per column) and reassemble the row. Row store wins for OLTP.
OLAP query — "sum amount across all orders." You want one column across all rows. The column store has amount: [79, 12, 99] packed contiguously — read just that block, ignore the other three columns entirely, and sum. The row store must read every full row (dragging customer_id, category, and order_id off disk too) just to pick out amount. On a billion-row table where you only need one column, that's the difference between reading one column and reading the entire table. Column store wins for OLAP — often by 10–100×.
There's a second columnar superpower: because a column holds values of the same type, often repeating (coffee, coffee, coffee, tea…), it compresses dramatically better than mixed-type rows. Less data on disk means less to read means cheaper, faster queries — the cost undercurrent again.
:::tip Durable vs dated "Analytics workloads read few columns across many rows, so storing data column-by-column lets you read only what you need and compress it hard" is durable — it's been true since columnar databases emerged and underpins Parquet (Chapter 2), every cloud warehouse, and the lakehouse (Chapter 10). The specific warehouse you use (Snowflake, BigQuery, Redshift, DuckDB) is dated. If columnar storage felt arbitrary when you first heard "Parquet," this is the reason it exists — keep it. :::
Why you must not run analytics on the production database
This is the most practical takeaway, and a real-world mistake juniors make constantly. It is tempting to just point the dashboard at the app's production OLTP database — the data's right there, why copy it? Three reasons you don't:
- You'll hurt the live application. A big analytical scan ("sum three years of sales") on a row-store OLTP database reads enormous amounts of data and hogs resources — exactly while real customers are trying to check out. One heavy report can slow or crash the system that makes the money. ShopFlow's checkout stalls for real customers because someone ran a quarterly report against the same database.
- It's the wrong tool, so it's slow and expensive. A row store is built for one-row lookups, not billion-row scans. The same query that's painful on OLTP is cheap and fast on a columnar OLAP system. You're using a sports car to haul gravel.
- The shapes are different. OLTP databases are normalized (data split across many small tables to avoid duplication — see Relational fundamentals), which is great for writes but means analysts must join a dozen tables to answer one question. OLAP systems use denormalized, analysis-friendly shapes (Chapter 4) so queries are simple and fast.
So the data engineer's job, in one sentence, is often: copy data out of the OLTP source, reshape it, and land it in an OLAP system where analysis is safe, fast, and cheap. That copy-and-reshape is the lifecycle from the last lesson. OLTP vs OLAP is why the lifecycle has to exist at all.
Why it matters
OLTP systems run the business — many small, fast, correct transactions, stored row-by-row so one full row is cheap to fetch. OLAP systems analyze the business — huge scans over a few columns of many rows, stored column-by-column so you read only the columns you need and compress them hard. Because their needs are opposite, they are physically different machines, and you never run analytics directly on the production OLTP database — you'd hurt the live app, use the wrong tool, and fight the wrong data shape. The entire data engineering lifecycle exists to bridge these two worlds. This is the reason columnar formats, warehouses, and the lakehouse exist; keep it as the lens for the whole guide.
We've used the words normalized and transaction and ACID loosely here. The next lesson makes them precise — the relational fundamentals that govern the OLTP source systems you'll ingest from.
Where this leads: the columnar idea becomes Parquet in Chapter 2; the denormalized OLAP shapes become dimensional modeling in Chapter 4; the "copy OLTP → OLAP" job is the whole lifecycle.