Module 09: OLAP and Data Warehousing

Why this matters

Every module in this track so far has one workload in mind: an application serving individual users — look up this order, update that account, insert one row, read a handful back. That's OLTP (Online Transaction Processing): many small, concurrent, row-oriented operations, and Postgres (a row store) is built for exactly this. But eventually someone asks a very different kind of question: "total revenue by region, by month, for the last two years, broken down by product category." That's OLAP (Online Analytical Processing): one huge query, scanning millions of rows, touching only a few columns, run rarely, by one person, with no urgency measured in milliseconds. Running that query against your production OLTP database doesn't just run slowly — it can lock tables and starve the connection pool (module 07) that real customers are trying to use right now. The fix isn't a bigger Postgres; it's a fundamentally different storage engine for a fundamentally different access pattern. This module builds that mental model and lets you feel the difference yourself, on the same data, in both kinds of engine.

Concepts

OLTP vs OLAP: different questions, different engines

Neither is "better" — they're optimized for opposite things, and that's the whole reason they're separate systems, not features of one database.

Row storage vs. column storage: the actual mechanical difference

Postgres (module 03) stores a table row by row on disk: all of a single row's columns sit next to each other, because OLTP mostly needs "give me this whole row" (or a few rows) fast. A columnar engine stores the same logical table column by column instead — every value of one column sits contiguously, across all rows.

  Row storage (Postgres):                 Column storage (OLAP engine):
  disk: [id1,region1,amt1,date1]          disk: [id1,id2,id3,...]
        [id2,region2,amt2,date2]                [region1,region2,region3,...]
        [id3,region3,amt3,date3]                [amt1,amt2,amt3,...]
                                                 [date1,date2,date3,...]

  "get order #4821" → one seek,           "sum(amt)" → read ONLY the amt
  one row read, fast                      column, skip id/region/date
                                           entirely — far less I/O

If your query only needs amt and date out of a ten-column table, a columnar engine reads only those two columns off disk — a row store has to read every row in full even if it only needs two fields out of ten, because rows are stored whole. This single difference is why the same aggregation query can be orders of magnitude faster in a columnar engine: it isn't a smarter query planner, it's fundamentally less data physically read off disk.

Why the two workloads fight over the same box

Put both workloads on one Postgres and they actively hurt each other:

• A multi-million-row analytical scan holds row/page locks and burns through the buffer cache (module 07's connection-pool/cache reasoning), evicting the hot pages your OLTP traffic actually needs.
• It competes for the same limited connection pool your live application depends on — one long-running analytics query can starve real user requests.
• Postgres's row-oriented storage is mechanically the wrong shape for the scan pattern analytics needs, so even with locks and pool contention aside, it's simply slower at that job than a purpose-built columnar engine would be.

The standard fix from module 08 already gave you half the answer without naming it: run analytics against a read replica, never the primary, so a runaway analytical query can't block real traffic. A data warehouse takes this further: a separate, columnar store, fed from your OLTP database (and often other sources), purpose-built for the "few huge queries" access pattern instead of merely isolating it on a replica of the same row-store engine.

ETL/ELT: how data gets from OLTP into the warehouse

Data doesn't appear in a warehouse by magic — it's extracted from source systems (your OLTP Postgres, and often other services), optionally transformed (reshaped, cleaned, aggregated), and loaded into the warehouse. Two orderings of those same three steps:

• ETL (Extract, Transform, Load) — transform before loading, using a separate processing step. Traditional; the warehouse only ever receives already-shaped data.
• ELT (Extract, Load, Transform) — load the raw data in first, then transform it inside the warehouse using the warehouse's own compute. More common with modern columnar warehouses, since they're often powerful and cheap enough to do the transformation themselves rather than needing a separate processing cluster.

Either way, this is a batch or scheduled pipeline (nightly, hourly), not a live path a user request ever touches — a different concern from this track's live OLTP path entirely, and the subject of the next new module in the background-processing track (batch pipeline orchestration).

Star schemas: modeling for OLAP instead of 3NF

Module 03 taught you to normalize to 3NF specifically to make OLTP writes safe and non-redundant. A warehouse optimizes for the opposite: fast, simple aggregation queries, and the classic shape for that is a star schema — one central fact table (the many rows: one row per order line, per event, per transaction) surrounded by several dimension tables (the "who/what/when/where" — customers, products, dates, regions) that the fact table references by foreign key.

                    dim_customer
                         │
  dim_date ── fact_order_lines ── dim_product
                         │
                    dim_region

  fact_order_lines: order_id, customer_id, product_id, date_id,
                     region_id, quantity, amount   ← one row per line item,
                                                       millions of rows

  dim_product: product_id, name, category, brand   ← thousands of rows,
                                                        rarely changes

This is intentionally less normalized than module 03's 3NF: a dimension table like dim_date might duplicate "is this a weekend," "is this a holiday," "fiscal quarter" for every calendar date, rather than deriving it at query time — trading storage (cheap, especially column-compressed) for query simplicity and speed (a plain join and GROUP BY, no derived computation per row at query time). Contrast this with the OLTP schema feeding it, which stays normalized because it optimizes for correct, non-redundant writes, not for reporting queries.

Denormalization on purpose vs. denormalization by accident

Module 03 warned against denormalizing an OLTP schema without a measured reason — redundant data there risks update anomalies you're not prepared to handle. A star schema's denormalization is different in kind: it's a deliberate design for a read-only, batch-loaded destination with no concurrent user-facing writes to keep in sync, so the update-anomaly risk that made denormalization dangerous in OLTP simply doesn't apply the same way here — the warehouse is rebuilt/refreshed by the ETL/ELT pipeline, not edited row-by-row by application code.

Command reference

This module uses DuckDB — a real embedded columnar OLAP engine (no server to run, just a Python library) — to make the row-vs-column difference tangible without standing up a cluster. Production warehouses (ClickHouse, Snowflake, BigQuery) are the same columnar idea at a much larger, distributed scale.

A star-schema load and aggregation query:

import duckdb

con = duckdb.connect()

con.execute("""
    CREATE TABLE dim_product (product_id INTEGER, name TEXT, category TEXT);
    CREATE TABLE dim_region  (region_id INTEGER, name TEXT);
    CREATE TABLE fact_order_lines (
        order_id INTEGER, product_id INTEGER, region_id INTEGER,
        quantity INTEGER, amount DECIMAL(10,2), order_date DATE
    );
""")

con.execute("INSERT INTO dim_product VALUES (1,'Widget','Hardware'), (2,'Gadget','Hardware'), (3,'Widget Pro','Hardware')")
con.execute("INSERT INTO dim_region VALUES (1,'US'), (2,'EU')")
con.executemany(
    "INSERT INTO fact_order_lines VALUES (?,?,?,?,?,?)",
    [(i, (i % 3) + 1, (i % 2) + 1, 1, 19.99, "2025-01-01") for i in range(1, 100001)],
)

result = con.execute("""
    SELECT p.category, r.name AS region, SUM(f.amount) AS revenue
    FROM fact_order_lines f
    JOIN dim_product p ON f.product_id = p.product_id
    JOIN dim_region  r ON f.region_id  = r.region_id
    GROUP BY p.category, r.name
    ORDER BY revenue DESC
""").fetchall()
print(result)

Hands-on exercises

pip install duckdb. No Docker needed for this module — DuckDB runs in-process.

1. Feel the difference: same aggregation, row store vs. columnar

Reuse (or recreate) a notes-style table with ~500,000 rows in your Postgres container from earlier modules (or any wide table you already have), and run:

-- On Postgres
EXPLAIN ANALYZE SELECT date_trunc('month', at) AS month, count(*) FROM notes GROUP BY 1;

Then load the same data into DuckDB and run the equivalent query:

import duckdb, time

con = duckdb.connect()
con.execute("CREATE TABLE notes AS SELECT * FROM read_csv('notes_export.csv')")

start = time.perf_counter()
con.execute("SELECT date_trunc('month', at) AS month, count(*) FROM notes GROUP BY 1").fetchall()
print(f"DuckDB: {time.perf_counter() - start:.4f}s")

Expected: for a table with many columns where this query only touches at, DuckDB's columnar scan reads only that one column and is noticeably faster than Postgres's row-store scan, which reads full rows off disk even though most columns are irrelevant to this query. The gap widens as you add more unrelated columns to the table — read that as the row-vs-column difference made concrete, not "DuckDB is just faster."

2. Build a star schema and query it

Using the star-schema code from the Command reference, run the category/region revenue rollup. Then add a dim_date table (date_id, full_date, month, quarter, is_weekend) and rewrite the query to group by month and quarter using dim_date instead of computing them from a raw date column at query time. Expected: the query reads as a plain join

• GROUP BY with no per-row date-math function calls — this is the payoff of pre-computing "weekend," "quarter," etc. into the dimension table once at load time instead of recomputing it on every query.

3. Prove a normalized OLTP schema and a star schema optimize for

opposite things

Take the star schema's fact_order_lines/dim_product design and try to model it the module-03 (3NF) way instead: split dim_product's category out into its own categories table referenced by dim_product, the way you would for an OLTP products table. Rewrite the category/region revenue query against this more-normalized version. Expected: the query now needs one more join (fact_order_lines → dim_product → categories) for a query pattern that runs constantly in a reporting context — a concrete example of why a warehouse deliberately denormalizes into a small, fixed number of dimension tables rather than normalizing every attribute the way an OLTP schema would.

4. Simulate an ELT load from your OLTP database

Export a real table from a Postgres container you've used earlier in this track (\copy orders TO 'orders.csv' CSV HEADER in psql, or pg_dump --table=orders --data-only), then load the raw CSV straight into DuckDB with no transformation:

con.execute("CREATE TABLE raw_orders AS SELECT * FROM read_csv('orders.csv', header=true)")

Now run a transformation inside DuckDB (the "T" happening after the load, i.e. ELT) that builds a summary table from the raw data:

con.execute("""
    CREATE TABLE order_summary AS
    SELECT customer_id, count(*) AS order_count, sum(total) AS lifetime_value
    FROM raw_orders
    GROUP BY customer_id
""")

Expected: the raw export loaded as-is, then transformed using the warehouse engine's own SQL — the ELT pattern, in miniature, versus transforming the data before it ever reached the warehouse (ETL).

5. Diagnose and fix: an analytics query is slowing down checkout

A team runs a monthly revenue-by-region report as a raw SQL query against their production Postgres — the same database serving checkout. Every time finance runs it, customer-facing checkout requests start timing out for a few minutes.

Independent challenge

No code given. You run the library-lending app used elsewhere in this track. The library's board now wants a monthly report: total books checked out per branch, per month, broken down by genre, plus average loan duration per genre. Design a star schema for this: name the fact table and its grain (what one row represents), name at least three dimension tables and what each contains, and specify which columns in the fact table are foreign keys into which dimensions. Then write the ELT pipeline in words: what gets extracted from the OLTP database, what (if anything) gets transformed before loading vs. after, and how often it runs. Finally, argue explicitly why this report should never run as a live query against the same Postgres database serving checkouts/returns, tying back to module 07's connection-pool reasoning and module 08's replica-routing rule.

Common mistakes & troubleshooting

• Running analytics directly against the production OLTP primary. As the diagnose-and-fix exercise showed, a large analytical scan competes for locks, buffer cache, and connection-pool slots with live traffic. At minimum route it to a replica (module 08); ideally give it its own columnar warehouse.
• Normalizing a star schema the way you'd normalize an OLTP schema. Exercise 3 showed the cost directly: every extra normalized dimension table is one more join in a query pattern that runs constantly in reporting. A warehouse's controlled denormalization is a deliberate trade for read speed, not a mistake to "fix" with more 3NF.
• Assuming "columnar" just means "a faster Postgres." The speedup comes from reading only the columns a query actually needs off disk — a mechanical property of column storage, not a tuning setting you can turn on in a row store. A query touching most columns of a table won't see the same advantage a narrow aggregation query does.
• Confusing a data warehouse with a bigger/replicated OLTP database. A read replica (module 08) is still row-oriented Postgres, just a second copy — it isolates analytics from production traffic but doesn't change the underlying storage shape. A warehouse is a different storage engine entirely, built for scan-heavy aggregation.
• Picking ETL/ELT based on habit rather than where the transform compute should live. ELT pushes transformation cost onto the warehouse's own compute (common when that compute is cheap/scalable); ETL does it in a separate step beforehand (useful when the warehouse shouldn't see raw/unshaped data at all, e.g. for compliance reasons). Pick deliberately, not by default.

Checkpoint quiz

Write down your answer to each question before expanding it — checking without attempting first is the single easiest way to fool yourself into thinking you've learned this.

Further reading & sources

• DuckDB documentation - the embedded columnar engine used throughout this module's exercises.
• Designing Data-Intensive Applications (Kleppmann), Ch. 3 - the definitive treatment of row-oriented vs. column-oriented storage engines.
• Kimball Group: The Data Warehouse Toolkit — star schema basics - the canonical reference for fact/dimension modeling used in this module.
• ClickHouse: Why ClickHouse is fast - a production columnar engine's own explanation of the same row-vs-column mechanics taught here.
• Snowflake: OLAP vs OLTP - a vendor's side-by-side comparison consistent with this module's framing.

Free PDF notes

Download the notes for each video on this page as a printable PDF, with every diagram and interview questions: SD-E-030: OLTP vs OLAP, SD-E-031: Change Data Capture. Each section in one file: Phase 2, Databases (PDF).

Next

10-multi-tenancy-patterns — another data-layer architecture decision this track hasn't covered yet: when your application serves many separate customers from one codebase, how do you decide whether their data lives in one shared schema, separate schemas, or entirely separate databases?