In the introduction you met two kinds of SQL workload through the restaurant analogy: the waiter taking single orders (operational) and the owner crunching a month of sales (analytical). Those two jobs are so different that the industry built separate kinds of databases for each. This chapter explains what those systems are, why you should not mix them, and where the big cloud warehouses fit in.
Understanding this split matters because it shapes every architecture decision a data engineer makes: which database to query, why a "simple" report can take a production system down, and when reaching for a full warehouse is smart versus overkill.
By the end of this chapter, you should be able to:
OLTP stands for Online Transaction Processing. This is the waiter from the introduction: an OLTP system handles many small, fast reads and writes, one row at a time. Think of a webshop recording an order, a banking app moving money between accounts, or a booking site reserving a seat.
<aside> 💡 What is a "transaction"? In database design, a transaction refers to any single, atomic unit of database work, such as updating a profile picture, posting a comment, or registering a user, rather than just financial exchanges.
</aside>
OLTP systems share a few traits:
Azure Database for PostgreSQL, the shared class database, is a row-oriented system in this family. It is a perfectly good OLTP database.
OLAP stands for Online Analytical Processing. This is the restaurant owner: an OLAP system answers questions that scan huge numbers of rows and squash them into a few summary numbers. "What was the average fare per pickup zone last month?" touches every one of the ~57K rows in nyc_taxi.raw_trips and returns a handful of results.
OLAP systems are optimized the opposite way:
fare_amount reads just that column.<aside> 💡 OLTP answers "what is true right now for this one record?" OLAP answers "what is the pattern across all records?" The same business runs both: an order system (OLTP) and a sales dashboard (OLAP).
</aside>
It is tempting to point your big aggregation query straight at the production database. The data is right there. The problem is that the production database is busy serving live users, and a heavy analytical scan competes with them for the same resources.
Concretely, a query that scans every row to compute a monthly total can:
The result is a checkout page that times out because someone in finance ran a report. The standard fix is to copy data out of the OLTP system into a separate analytical store and run heavy queries there. That copy step is exactly the ELT flow you have been building: extract and load raw data, then transform it where analytics will not hurt anyone.
flowchart LR
app["Live app<br/>(orders, logins)"]:::oltp
oltp[("OLTP database<br/>row-oriented<br/>e.g. Azure PostgreSQL")]:::store
pipe["ELT pipeline<br/>(Weeks 6-8)"]:::pipe
olap[("OLAP warehouse<br/>columnar<br/>e.g. Snowflake")]:::store2
bi["Dashboards<br/>& reports"]:::bi
app --> oltp
oltp --> pipe --> olap --> bi
classDef oltp fill:#dae8fc,stroke:#6c8ebf,stroke-width:2px,color:#000
classDef store fill:#f5f5f5,stroke:#666,stroke-width:2px,color:#000
classDef pipe fill:#fff2cc,stroke:#d6b656,stroke-width:2px,color:#000
classDef store2 fill:#d5e8d4,stroke:#82b366,stroke-width:2px,color:#000
classDef bi fill:#e1d5e7,stroke:#9673a6,stroke-width:2px,color:#000
The single biggest reason OLAP systems are fast at analytics is columnar storage. To see why, picture how nyc_taxi.raw_trips is laid out on disk under each model.
A row-oriented store keeps all the values of one trip next to each other:
[pickup_time, zone, fare, distance] [pickup_time, zone, fare, distance] ...
A column-oriented store keeps all the values of one column next to each other:
[pickup_time, pickup_time, ...] [zone, zone, ...] [fare, fare, ...] [distance, distance, ...]
Now run SELECT AVG(fare_amount) FROM nyc_taxi.raw_trips. You only care about one column out of many. The row store has to read every full row off disk just to pick out the fare each time, dragging along pickup time, distance, and every other column you do not need. The column store reads only the fare_amount block and ignores the rest. Less data off disk means a faster query.
Columnar storage has a second win: because a column holds one data type with lots of repeated values (the same handful of zone IDs, fares clustered around common amounts), it compresses extremely well. Smaller data on disk means even less to read. This is why a columnar warehouse can scan billions of rows for an aggregation in seconds, while the same query on a row store crawls.
<aside> ⚠️ Columnar storage is great for analytics but bad for OLTP. Updating one whole trip means touching every column block. That is why you do not want a single database trying to be both: each layout is tuned for the opposite access pattern.
</aside>
Recall from the joins chapter that you can preview any query plan using EXPLAIN. To see the actual performance of a query (such as execution time and real row counts), you can extend it with EXPLAIN ANALYZE. This actually executes the query and prints the real performance statistics alongside the execution plan.
You can inspect the execution plan and see the scan cost (the overhead of reading extra columns off disk) a row store pays right now on your database:
<aside>
⌨️ Hands on: Run EXPLAIN ANALYZE SELECT AVG(fare_amount) FROM nyc_taxi.raw_trips; against your schema. Find the Seq Scan on nyc_taxi.raw_trips line and its reported cost (a planner metric representing estimated CPU and disk effort, not financial cost) and row count: that full-table scan, reading every column off disk just to average one, is exactly the work a columnar warehouse avoids by reading only the fare_amount block.
</aside>
This split between layouts is not a recent invention: columnar storage and the data warehouse both predate the cloud by decades. The History of SQL & Warehouses page tells that story (C-Store, Vertica, and Bill Inmon's 1990s warehouse).
<aside>
💡 In the wild: You can run a real columnar engine on your laptop. DuckDB is an open-source, column-oriented analytical database: the same scan-optimized design the big cloud warehouses use, packed into a single file you can pip install. Open its repo to see a production OLAP engine, or point it at a Parquet file to feel a columnar AVG() run.
</aside>
A cloud data warehouse is a managed OLAP system you rent instead of run. The two names you will hear constantly across the industry are Snowflake and Databricks. Both are cloud-agnostic: they run on Azure, AWS, or GCP. They differ in detail but share the columnar, scan-optimized design described above.
Two features define this generation of warehouses:
<aside> 💡 Separation of compute and storage is the headline feature. It is why two analysts can run heavy queries at the same time without slowing each other down: each gets its own compute, reading from the same shared storage.
</aside>
So why does this course use Azure PostgreSQL, a row-oriented OLTP database, for analytical SQL? Because at the class's scale it is the right tool. nyc_taxi.raw_trips has ~57K rows and nyc_taxi.raw_zones has 265. Postgres scans that in milliseconds. A columnar warehouse would be pure overhead: more setup, more cost, and no noticeable speedup at this size.
Postgres is a stepping stone. It speaks standard SQL, so every join, CTE, and aggregation you write here transfers directly to Snowflake or Databricks. The concepts are identical; only the engine underneath changes. When your data grows past what a single Postgres server can comfortably scan, you move the same SQL to a columnar warehouse and it keeps working.
<aside> ⚠️ Out of scope: This chapter does not have a columnar warehouse to practice on, so we keep warehouse internals (partitioning, clustering, distribution keys) light on purpose. You will feel the difference for real once you work with warehouse-scale data on the job.
</aside>
A full cloud warehouse is not always the answer. It costs money, takes setup and operational care, and adds complexity. For a small team with low data volume and simple reporting needs, a plain Postgres database (or even well-organized files) is cheaper and simpler.
Reach for a warehouse when you have genuine analytical scale or concurrency: hundreds of millions of rows, many analysts querying at once, or queries that bog down a single server. Until then, a row-oriented database like Azure PostgreSQL is often the pragmatic choice, which is exactly why this course uses one.
<aside> 🤓 Curious Geek: the warehouse that bills you nothing while idle
Some warehouses take pay-per-use to its limit. Snowflake suspends its virtual warehouses (compute clusters) after a configurable idle period, so when nobody is querying you pay only for cheap storage. The trade-off is that a careless SELECT * over a giant table can consume credits fast, because you pay by compute-second. The skill of writing tight queries that touch only the columns you need (the columnar advantage from earlier) becomes a cost skill, not just a speed skill.
</aside>
There is no warehouse to query in this chapter, so practice classification instead. For each workload below, decide whether it is OLTP or OLAP, and say why in one sentence:
<aside> 💡 Using AI to help: Paste your five answers into an LLM and ask it to challenge your reasoning, for example "Is #2 really OLAP? What makes it different from #5?" ⚠️ Ensure no PII, passwords, or sensitive company data is included.
</aside>
SELECT AVG(fare_amount) FROM nyc_taxi.raw_trips faster than the same query on a row-oriented store?Want to check your understanding before moving on? Try the interactive quiz for this chapter.
<aside> 🚀 Try it in the widget: Interactive Quiz: OLAP, OLTP & Warehouses
</aside>
https://lasse.be/simple-hyf-teach-widget/mcq.html?bank=week_9_ch4_olap_vs_oltp_quiz&embed=1
If the OLTP-versus-OLAP split still feels fuzzy, this short video lays it out from a different angle.
<aside> 🎬 Struggling with this concept? Watch this beginner-friendly video:
</aside>
https://www.youtube.com/watch?v=iw-5kFzIdgY
Next up: Data Modeling Concepts, where you learn the raw, staging, and mart layers, plus grain, keys, facts, dimensions, and the star schema that turns the ideas in this chapter into a concrete table design.