The Modern Data Stack

The modern data stack is the set of tools that turns product data into reviewed, reusable analytics tables. SQL still sits in the middle: it defines the models, tests the assumptions, and answers the business questions.

You have already written the hard part: correct analytical SQL. This lesson zooms out so you know where those queries live after they leave a playground.

OLTP And OLAP

OLTP means online transaction processing: systems built for many small, correct writes and reads. Your app database is OLTP. It protects checkout, signup, inventory, and account changes.

OLAP means online analytical processing: systems built for scanning, grouping, joining, and summarizing lots of data. A dashboard asking "revenue by cohort, tier, and month" is OLAP.

That split explains the storage trade:

• Row-store databases keep a row's values near each other, which is great when a transaction needs one user's whole record.
• Column-store systems keep a column's values near each other, which is great when an analytical query scans a few columns across many rows.

Warehouses lean into OLAP. The warehouse shape is columnar storage, separated compute/storage, and MPP—massively parallel processing, where work is split across many machines. Do not read that as "every query is fast." Read it as "the system is built for repeated analytical scans, not checkout writes."

Warehouses And Where SQL Fits

When people say "warehouse," they usually mean platforms such as Snowflake, BigQuery, Redshift, or Databricks. The brand changes; the workflow is familiar:

source apps  ->  raw landed data  ->  SQL models  ->  marts / dashboards
Postgres         files or tables       reviewed       trusted metrics
Stripe           unchanged copy        transformations BI, notebooks, ML
support tool                         tests + docs

SQL fits in the middle. The raw layer keeps the facts close to what arrived. The model layer gives those facts names, grain, types, tests, and joins. The mart layer is what analysts and dashboards read.

Here is a warehouse-shaped query in the small café seed. The CTEs act like a star schema: a central fact table for purchases, joined to a dimension table for user attributes.

WITH dim_user AS (
  SELECT u.user_id,
         u.acquisition_channel,
         u.home_branch,
         u.loyalty_tier,
         CAST(date_trunc('month', u.signup_ts) AS date) AS signup_month
  FROM cafe_users u
),
fact_purchase AS (
  SELECT e.order_id,
         e.user_id,
         CAST(date_trunc('month', e.event_ts) AS date) AS purchase_month,
         e.branch AS purchase_branch,
         e.purchase_amount
  FROM cafe_app_events e
  WHERE e.event_type = 'purchase'
),
monthly_tier_revenue AS (
  SELECT fp.purchase_month,
         du.loyalty_tier,
         count(*) AS orders,
         count(DISTINCT fp.user_id) AS purchasing_users,
         sum(fp.purchase_amount) AS revenue
  FROM fact_purchase fp
  JOIN dim_user du ON du.user_id = fp.user_id
  GROUP BY fp.purchase_month, du.loyalty_tier
)
SELECT mtr.purchase_month,
       mtr.loyalty_tier,
       mtr.orders,
       mtr.purchasing_users,
       mtr.revenue
FROM monthly_tier_revenue mtr
ORDER BY mtr.purchase_month, mtr.loyalty_tier;

 purchase_month | loyalty_tier | orders | purchasing_users | revenue
----------------+--------------+--------+------------------+---------
 2026-01-01     | bronze       |      1 |                1 |   11.00
 2026-01-01     | gold         |      2 |                1 |   37.60
 2026-01-01     | silver       |      2 |                1 |   23.25
 2026-02-01     | bronze       |      2 |                2 |   27.20
 2026-02-01     | gold         |      1 |                1 |    9.95
 2026-02-01     | silver       |      2 |                2 |   34.25
 2026-03-01     | bronze       |      2 |                2 |   22.10
 2026-03-01     | gold         |      1 |                1 |   21.30
 2026-03-01     | none         |      1 |                1 |    6.25
 2026-03-01     | silver       |      2 |                2 |   25.50
(10 rows)

The fact grain is one purchase. The dimension grain is one user. The output grain is one purchase month and loyalty tier. Nothing about the warehouse removes the need to say that out loud.

DuckDB On Your Laptop

DuckDB is an in-process analytics database: no server to manage, but built for OLAP-style queries over local files and tables. Think of it as a small warehouse workbench on your laptop, not as the app database that handles checkout writes.

DuckDB also has friendly SQL sugar. Use it when you are writing DuckDB. Do not paste it into PostgreSQL and expect it to run:

-- DuckDB sugar: not portable PostgreSQL
SELECT * EXCLUDE (raw_payload)
FROM app_events;
 
SELECT loyalty_tier,
       sum(revenue) AS revenue
FROM monthly_tier_revenue
GROUP BY ALL;
 
SELECT user_id,
       event_ts,
       event_type
FROM app_events
QUALIFY row_number() OVER (
  PARTITION BY user_id
  ORDER BY event_ts DESC
) = 1;

SELECT * EXCLUDE, GROUP BY ALL, and QUALIFY can be pleasant in notebooks and local analytics. In this course's Postgres code, you keep writing the portable shape: name columns, write the GROUP BY, and filter window results one step later in a CTE.

dbt And Analytics Engineering

dbt is a tool for analytics engineering: SQL models as reviewed files, connected by ref() dependencies, tested, documented, and run in order. The shorthand you will hear: models-as-SELECTs, a ref() DAG, tests, and SCD-2 snapshots.

A dbt model is still a SELECT. It is just a SELECT that lives in source control:

-- dbt model: models/marts/fct_purchase.sql
SELECT e.order_id,
       e.user_id,
       date_trunc('month', e.event_ts) AS purchase_month,
       e.purchase_amount
FROM {{ ref('stg_cafe_app_events') }} AS e
WHERE e.event_type = 'purchase';

Tests make model promises executable:

models:
  - name: fct_purchase
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: user_id
        tests: [not_null]

This is the cultural shift: analytics SQL stops being a private query tab and becomes reviewed code. The same query skills matter, but now your teammates can see, test, and reuse the model.

Star Schemas And SCD-2

A star schema has facts in the center and dimensions around them. Facts are events or measurements: purchases, sessions, shipments. Dimensions describe the nouns: users, products, branches, dates.

The star shape is boring on purpose:

              dim_user
                 |
dim_date  --  fact_purchase  --  dim_branch
                 |
              dim_product

SCD-2 means slowly changing dimension type 2: instead of overwriting a dimension row when an attribute changes, you keep history with validity dates. A user moving from bronze to silver can be represented as two dimension rows:

user_id | loyalty_tier | valid_from | valid_to
--------+--------------+------------+-----------
101     | bronze       | 2026-01-03 | 2026-02-07
101     | silver       | 2026-02-07 | NULL

That history lets a February purchase join to the user's February tier, not today's tier. dbt snapshots are one common way teams maintain this kind of SCD-2 history.

ELT Versus ETL

ETL means extract, transform, load: clean data before it lands in the analytical store. ELT means extract, load, transform: land raw data first, then transform it inside the warehouse.

ETL: source -> transform outside warehouse -> load clean tables -> dashboards
 
ELT: source -> load raw tables/files --------> transform with SQL -> dashboards

Modern warehouse teams often favor ELT because the warehouse is built to run analytical transformations, and raw landed data gives you a replayable starting point. ETL still fits when data must be cleaned, filtered, or protected before it enters the warehouse. The practical question is not which acronym is fashionable; it is where the transformation is reviewed, tested, and repeatable.

Part V showed how SQL answers analytical questions. Section 18 turns back toward application code: drivers, pools, transactions, injection safety, ORMs, and migrations from the program that calls the database.