OLTP vs OLAP: Databases for Questions

When Lora books seat G7 and when Lora asks "how did the business actually do this year?", she would describe both as "using the database". They are opposite workloads. The first touches three rows and must finish this second; the second touches every booking the Marquee ever made and could happily run overnight. The industry has names for the two: OLTP — online transaction processing, the many-tiny-reads-and-writes-right-now work of running a business — and OLAP — online analytical processing, the huge read-only questions asked over its history.

This split explains a whole second industry you have not met yet: data warehouses, and names like BigQuery, Redshift, and Snowflake. It is also the boundary post between this course and the analytics world. One honest page is enough to plant the vocabulary and show you that, on the far side of the boundary, your SQL still works.

Two Workloads, One Cinema

Picture the difference as a shop counter and an accountant's office. The counter handles customers by the second: small transactions, one after another, each one now. The accountant closes the books by the month: one enormous, careful pass over everything that happened. Both are essential, both use the same underlying records — and nobody does the taxes standing at the till, because the till would stop serving customers. That is the whole split; now the real terms carry it.

Booking G7 is OLTP: read one screening, check one constraint, insert one row into bookings, inside Chapter 7's transaction bracket, in milliseconds, because a customer is waiting. "Revenue by film, by month, across three years" is OLAP: a read-only sweep across every booking, joined to screenings and films, grouped and summed. Same data. Opposite physics — one workload is a thousand pinpricks per minute, the other is one slow, heavy scan.

Here is the year-end question, and notice that you can already read it — it is Chapter 3 and Chapter 4 doing their jobs:

SELECT f.title,
       COUNT(*) AS tickets,
       SUM(s.price) AS revenue
FROM bookings b
JOIN screenings s ON s.screening_id = b.screening_id
JOIN films f ON f.film_id = s.film_id
WHERE b.booked_at >= '2023-01-01' AND b.booked_at < '2026-01-01'
GROUP BY f.title
ORDER BY revenue DESC;

Every booking from three years, joined to its screening for the price and to its film for the title, then grouped by film and summed. The real report also splits each film by month — the month-extraction function is one of those engine accents from the last topic, so the portable version here groups by film only. The shape of the workload is what matters: no writes, enormous reads.

Why One Database Strains at Both

Chapter 8 taught you the economics of this query: it scans and sorts on a grand scale, exactly the work that makes queries expensive. Run it on the live database at 8 p.m. on a Saturday and it competes with the till for the same disk and memory — the analyst's one big question steals the milliseconds that a dozen booking customers were promised. The database is not broken; it is being asked to be the counter and the accountant's office at the same moment.

You already know the first fix, because Chapter 9 planted it: run reports on a copy. Keep a second, read-only copy of the database, refreshed on a schedule, and point every heavy question at the copy. The till keeps its milliseconds; the analyst gets a whole database to be rude to. For a business the Marquee's size, this one pattern is the complete answer for years — the smell test from Chapter 6 applies here too, and "we might need analytics at scale someday" does not pass it.

Warehouses, Named

When the analytical pain gets measured and real — hundreds of analysts, billions of rows, dashboards refreshing all day — the copy grows up into a purpose-built home: a data warehouse, a database designed only for OLAP. The names you will hear are BigQuery, Redshift, and Snowflake — sightings, not lessons; each is a cloud service whose entire job is answering enormous read-only questions fast.

Two facts about warehouses give you the whole picture. First, the data is copied in: on a schedule, records flow from the operational databases — the tills — into the warehouse. The operational database stays the source of truth; the warehouse holds analytical copies, which is the same truth-and-copies arrangement you saw with caches and search indexes. Second, warehouses store data column-wise: all the prices together, all the dates together, so a question that needs three columns reads only those three columns instead of every full row — one honest sentence on why they scan so fast, and the deeper mechanics stay in the data track.

And the fact that should genuinely reassure you: warehouses speak SQL. The year-end query above is, give or take an accent, exactly what an analyst types into BigQuery. GROUP BY, your Chapter 3 acquaintance, is the star of the show over there — analytics is grouping and aggregating nearly all day. Crossing this boundary someday would not mean starting over; it would mean pointing skills you already have at bigger questions.

The Boundary Post

This is where the analytics world begins: dashboards, metrics, the data-team job titles, and eventually the statistics and machine learning that our ML from Zero course teaches. All of it lives on the OLAP side, and all of it is fed by scheduled copies from the operational side. This book deliberately taught the operational side — the side that runs the business and holds the truth.

That order was not an accident. Every warehouse is downstream of databases like the Marquee's; every dashboard summarizes rows that some OLTP system guarded into existence. You learned the side that everything else copies from — which means the analytics door is open whenever you want to walk through it, and you will recognize the furniture.