Warehouses, Lakes, Streams and Scale

The shop database is built to run the shop: insert an order, update a stock count, look up one customer — thousands of tiny transactions a minute. The questions an analyst or an ML engineer asks are different: "revenue by month and category for three years", "every click of every user last quarter as training data". Those scan millions of rows and would slow the shop down. So companies copy the data into systems built for reading at scale: warehouses, lakes and lakehouses, fed in batches or as streams.

This page builds a small star schema from the shop and queries it, partitions a table on a real PostgreSQL server, and then maps the rest of the landscape — the words you will meet in every data and ML platform job.

What you will learn

• OLTP vs OLAP, and why analytical data lives in a separate system
• Fact and dimension tables, star vs snowflake schemas, data marts — built and queried on the shop
• Slowly changing dimensions: type 1 (overwrite) vs type 2 (keep history), with a point-in-time join
• Batch vs streaming, Kafka, Spark, lakes vs warehouses vs lakehouses, and the cloud platforms
• Scaling: partitioning (run on PostgreSQL), sharding, replication, vertical vs horizontal scaling, governance, schema evolution and cost

OLTP vs OLAP

The shop's tables are OLTP: they follow the normalization rules from Database Design: Keys, ER Diagrams and Normalization. A data warehouse is the OLAP side: a central, cleaned, historical copy of data from many systems (shop, payments, support, marketing), loaded by the pipelines from ETL and ELT: Building a Reliable Data Pipeline.

Fact and dimension tables

A warehouse is organised around business events. A fact table stores one row per event at a declared grain ("one row per order line"), with numbers you add up (measures: quantity, revenue) and keys to dimension tables, which describe the who, what, when and where. Draw it and you get a star:

                    dim_date
                 (date_key, year, quarter,
                  year_month, is_weekend)
                          │
  dim_customer ───── fact_sales ───── dim_product
 (customer_key,    (date_key, customer_key,    (product_key, product_id,
  customer_id,      product_key, order_id,      product_name, category,
  city, valid_from,  order_status, quantity,     list_price)
  valid_to)          revenue, discount)

In a snowflake schema the dimensions are normalized further — dim_product would point to a separate dim_category. That saves a little space and costs an extra join in every query; most teams prefer the star. A data mart is a subject-sized slice (a sales mart, a marketing mart) built for one team.

Building the dimensions

The date dimension is generated, not copied: one row per day, with the attributes people group by. Note the weekend: in Bangladesh it is Friday and Saturday (%w = 5 and 6). Dimension tables get their own surrogate key; the source's natural key is kept alongside:

CREATE TABLE dim_date (
    date_key   INTEGER PRIMARY KEY,          -- 20260412
    full_date  TEXT NOT NULL,
    year       INTEGER NOT NULL,
    quarter    INTEGER NOT NULL,
    year_month TEXT NOT NULL,
    is_weekend INTEGER NOT NULL
);

INSERT INTO dim_date
WITH RECURSIVE d(day) AS (
    SELECT '2026-01-01'
    UNION ALL
    SELECT date(day, '+1 day') FROM d WHERE day < '2026-06-30'
)
SELECT CAST(strftime('%Y%m%d', day) AS INTEGER),
       day,
       CAST(strftime('%Y', day) AS INTEGER),
       (CAST(strftime('%m', day) AS INTEGER) + 2) / 3,
       strftime('%Y-%m', day),
       CASE WHEN strftime('%w', day) IN ('5', '6') THEN 1 ELSE 0 END
FROM d;

CREATE TABLE dim_product (
    product_key  INTEGER PRIMARY KEY,
    product_id   INTEGER NOT NULL,           -- natural key from the shop
    product_name TEXT NOT NULL,
    category     TEXT NOT NULL,
    list_price   INTEGER NOT NULL
);

INSERT INTO dim_product (product_id, product_name, category, list_price)
SELECT p.product_id, p.name, COALESCE(c.name, 'Uncategorised'), p.price
FROM products AS p
LEFT JOIN categories AS c ON c.category_id = p.category_id
ORDER BY p.product_id;

CREATE TABLE dim_customer (
    customer_key INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    name         TEXT NOT NULL,
    city         TEXT NOT NULL,
    valid_from   TEXT NOT NULL,
    valid_to     TEXT NOT NULL DEFAULT '9999-12-31',
    is_current   INTEGER NOT NULL DEFAULT 1
);

INSERT INTO dim_customer (customer_id, name, city, valid_from)
SELECT customer_id, name, COALESCE(city, 'Unknown'), joined_on
FROM customers
ORDER BY customer_id;

SELECT (SELECT COUNT(*) FROM dim_date) AS days,
       (SELECT SUM(is_weekend) FROM dim_date) AS weekend_days,
       (SELECT COUNT(*) FROM dim_product) AS products,
       (SELECT COUNT(*) FROM dim_customer) AS customers;

+------+--------------+----------+-----------+
| days | weekend_days | products | customers |
+------+--------------+----------+-----------+
|  181 |           52 |       11 |         8 |
+------+--------------+----------+-----------+

Warehouses replace NULLs in dimensions with explicit labels ('Unknown', 'Uncategorised') so reports group them visibly instead of dropping them.

Slowly changing dimensions

On 1 April 2026 Nadia (customer 1) moves from Dhaka to Chattogram. Should her January order count as Dhaka revenue or Chattogram revenue?

Type 2 is two statements:

UPDATE dim_customer
SET valid_to = '2026-04-01', is_current = 0
WHERE customer_id = 1 AND is_current = 1;

INSERT INTO dim_customer (customer_id, name, city, valid_from)
VALUES (1, 'Nadia Rahman', 'Chattogram', '2026-04-01');

SELECT customer_key, customer_id, city, valid_from, valid_to, is_current
FROM dim_customer
WHERE customer_id = 1
ORDER BY customer_key;

+--------------+-------------+------------+------------+------------+------------+
| customer_key | customer_id | city       | valid_from | valid_to   | is_current |
+--------------+-------------+------------+------------+------------+------------+
|            1 |           1 | Dhaka      | 2025-11-03 | 2026-04-01 |          0 |
|            9 |           1 | Chattogram | 2026-04-01 | 9999-12-31 |          1 |
+--------------+-------------+------------+------------+------------+------------+

The validity range is half-open: valid_from is included, valid_to is not. This is exactly the point-in-time idea from Preparing ML-Ready Data with SQL and pandas: a type 2 dimension lets you rebuild "what did we know about this customer on that date?" — which is what a leak-free feature needs.

Loading the fact table

Each order line becomes one fact row. The customer key is found with a point-in-time join: the dimension row whose validity range contains the order date. The discount is computed once here, so no report has to repeat the logic:

CREATE TABLE fact_sales (
    date_key     INTEGER NOT NULL,
    customer_key INTEGER NOT NULL,
    product_key  INTEGER NOT NULL,
    order_id     INTEGER NOT NULL,           -- a "degenerate" dimension: no table of its own
    order_status TEXT NOT NULL,
    quantity     INTEGER NOT NULL,
    revenue      INTEGER NOT NULL,
    discount     INTEGER NOT NULL,
    FOREIGN KEY (date_key) REFERENCES dim_date (date_key),
    FOREIGN KEY (customer_key) REFERENCES dim_customer (customer_key),
    FOREIGN KEY (product_key) REFERENCES dim_product (product_key)
);

INSERT INTO fact_sales
SELECT CAST(strftime('%Y%m%d', o.order_date) AS INTEGER),
       dc.customer_key,
       dp.product_key,
       o.order_id,
       o.status,
       oi.quantity,
       oi.quantity * oi.unit_price,
       oi.quantity * (dp.list_price - oi.unit_price)
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN dim_customer AS dc
  ON dc.customer_id = o.customer_id
 AND o.order_date >= dc.valid_from AND o.order_date < dc.valid_to
JOIN dim_product AS dp ON dp.product_id = oi.product_id;

SELECT COUNT(*) AS fact_rows, SUM(revenue) AS revenue, SUM(discount) AS discount
FROM fact_sales;

+-----------+---------+----------+
| fact_rows | revenue | discount |
+-----------+---------+----------+
|        20 |  103550 |      450 |
+-----------+---------+----------+

20 fact rows for 20 order lines: the grain holds. The 450 taka of discount is order 9's keyboard, sold at 4050 instead of 4500.

Querying the star

Every star query has the same shape: start at the fact, join the dimensions you need, filter and group by their attributes, sum the measures.

SELECT d.quarter, p.category, SUM(f.quantity) AS units, SUM(f.revenue) AS revenue
FROM fact_sales AS f
JOIN dim_date AS d ON d.date_key = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
WHERE f.order_status <> 'cancelled'
GROUP BY d.quarter, p.category
ORDER BY d.quarter, revenue DESC;

SELECT c.city AS city_at_sale, SUM(f.revenue) AS revenue
FROM fact_sales AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE f.order_status <> 'cancelled'
GROUP BY c.city
ORDER BY revenue DESC, city_at_sale;

+---------+-------------+-------+---------+
| quarter | category    | units | revenue |
+---------+-------------+-------+---------+
|       1 | Courses     |     2 |   18000 |
|       1 | Electronics |     4 |    7200 |
|       1 | Books       |     4 |    6100 |
|       1 | Accessories |     2 |    3700 |
|       2 | Electronics |     4 |   24350 |
|       2 | Courses     |     2 |   18000 |
|       2 | Accessories |     3 |    5200 |
|       2 | Books       |     1 |    2500 |
+---------+-------------+-------+---------+
+--------------+---------+
| city_at_sale | revenue |
+--------------+---------+
| Chattogram   |   41000 |
| Dhaka        |   20100 |
| Khulna       |   19950 |
| Unknown      |    4000 |
+--------------+---------+

Nadia's April order (order 10, 18,500 taka) counts for Chattogram; her January and February orders stay in Dhaka. To report by current city instead (type 1 behaviour), join through the natural key to the row with is_current = 1. Type 2 lets you answer both questions; type 1 only the second.

Batch, streams and where data lives

How data reaches the analytical side is the other big design choice:

  apps / databases ──CDC──┐
  click events ───────────┼──▶ Kafka (event log) ──▶ stream processing ──▶ real-time features, alerts
  IoT, logs ──────────────┘          │                (Flink, Spark Structured Streaming)
                                     ▼
                               data lake (files: Parquet on S3 / GCS / ADLS)
                                     │  batch jobs (Spark, dbt, SQL)
                                     ▼
                               warehouse / lakehouse tables ──▶ BI dashboards, ML training sets

• Batch processes a bounded chunk on a schedule ("last night's orders"). Simple, cheap, minutes to hours of delay. Streaming processes each event as it arrives; real-time means milliseconds to seconds, near-real-time means seconds to minutes (micro-batches). Choose streaming only when someone acts on fresher data: fraud checks, live recommendations, monitoring an LLM app.
• Apache Kafka is a durable, partitioned, append-only log of events. A topic is split into partitions spread across brokers; producers append to them, and consumers read at their own pace, each remembering its position (offset). Consumers in one consumer group share a topic's partitions to work in parallel, while separate groups each read the whole stream independently. Events stay for a configured retention period, so a new consumer can replay history. CDC tools (such as Debezium) turn every database change into a Kafka event.
• Apache Spark splits data into partitions and processes them in parallel on a cluster, with a DataFrame API and Spark SQL. Use it when data no longer fits one machine. Below that, DuckDB — an in-process analytical database — queries Parquet and CSV files with SQL at impressive speed on a laptop.

# DuckDB on a laptop: SQL straight over a folder of Parquet files
duckdb -c "SELECT year_month, SUM(revenue) FROM 'sales/*.parquet' GROUP BY 1 ORDER BY 1"

The big cloud platforms, conceptually: BigQuery (Google, serverless, priced by bytes scanned or reserved slots), Snowflake (runs on AWS, Azure or Google Cloud; storage is separate from compute "virtual warehouses" that you resize, start and stop, billed per second while running), Amazon Redshift (AWS, clusters or serverless), Databricks (lakehouse on Spark and Delta Lake, strong for ML). They all speak SQL with dialect differences, store data in columns and (largely) separate storage from compute, so you can scale or pause compute without moving the data.

Scaling a database

Partitioning: run it on PostgreSQL

Partitioning splits one big table into pieces by a key, usually date, all on the same server (sharding, below, spreads data across servers). Queries that filter on the key skip the other pieces (partition pruning), and dropping a whole old month is instant. PostgreSQL supports it natively:

-- PostgreSQL
CREATE TABLE sales_events (
    event_id    INTEGER NOT NULL,
    event_date  DATE NOT NULL,
    customer_id INTEGER NOT NULL,
    amount      INTEGER NOT NULL
) PARTITION BY RANGE (event_date);

CREATE TABLE sales_events_2026q1 PARTITION OF sales_events
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE sales_events_2026q2 PARTITION OF sales_events
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

INSERT INTO sales_events
SELECT p.payment_id, p.paid_on, o.customer_id, p.amount
FROM payments AS p
JOIN orders AS o ON o.order_id = p.order_id;

SELECT tableoid::regclass::text AS partition, COUNT(*) AS events, SUM(amount) AS amount
FROM sales_events
GROUP BY 1
ORDER BY 1;

EXPLAIN (COSTS OFF)
SELECT SUM(amount) FROM sales_events WHERE event_date >= '2026-05-01';

+---------------------+--------+--------+
| partition           | events | amount |
+---------------------+--------+--------+
| sales_events_2026q1 |      8 |  35000 |
| sales_events_2026q2 |      5 |  35050 |
+---------------------+--------+--------+
+----------------------------------------------------+
| QUERY PLAN                                         |
+----------------------------------------------------+
| Aggregate                                          |
|   ->  Seq Scan on sales_events_2026q2 sales_events |
|         Filter: (event_date >= '2026-05-01'::date) |
+----------------------------------------------------+

Rows went to the right partition automatically, and the plan touches only sales_events_2026q2. Warehouses do the same: in BigQuery a query filtered on the partition column is billed only for the partitions it reads.

The other scaling tools

Governance, schema evolution and cost

• Governance and catalogs. A catalog (Unity Catalog, AWS Glue, DataHub, OpenMetadata) records which tables exist, who owns them, what the columns mean, where they came from (lineage) and who may read them. Mark PII columns so they are masked for analysts and kept out of training data unless allowed.
• Schema evolution. Sources change: a column is added, renamed or retyped. Prefer additive changes (new nullable columns), version your models, and let pipelines fail loudly on unexpected changes rather than silently load NULLs. Formats like Iceberg and Delta record schema versions and support "time travel" to read a table as it was — handy for reproducing an ML training set.
• Cost and performance. In the cloud you pay for bytes scanned and compute time. Select only the columns you need (with column storage, SELECT * really costs money), filter on partition columns, cluster by common filters, pre-aggregate hot dashboards, and stop idle compute.

Common mistakes

• Running heavy analytics on the production database. One big report slows checkout for everyone. Fix: a read replica for light reporting, a warehouse for everything else.
• Not declaring the grain. Mixing order-level and line-level rows in one fact table double-counts (the fan-out trap from Joining Many Tables, and the Duplicate-Row Trap). Fix: write the grain down — "one row per order line" — and check COUNT(*) against the source.
• Overwriting attributes that history depends on. Type 1 on city moves last year's revenue to this year's city and leaks today's values into old ML features. Fix: type 2 with valid_from/valid_to, and join on the date.
• Streaming because it sounds modern. A Kafka cluster for a dashboard viewed once a day is cost and complexity with no benefit. Fix: start with batch; stream when a decision needs fresher data.
• Partitioning on a column you do not filter by. Pruning never happens and every query opens every partition. Fix: partition by the column in most WHERE clauses (usually the event date), with partitions neither tiny nor huge.

Try it yourself

1. Easy: Using the star schema, show revenue for weekend vs weekday sales (non-cancelled orders).
2. Medium: Report revenue by customers' current city (type 1 view): join each fact's customer row to the current row of the same customer_id.
3. Hard: On PostgreSQL, add a partition for the third quarter of 2026, insert one event dated 2026-08-15, and show the row count per partition. What happens if you insert an event dated 2027-01-05?

Answers

-- 1.
SELECT CASE d.is_weekend WHEN 1 THEN 'weekend' ELSE 'weekday' END AS day_type,
       COUNT(DISTINCT f.order_id) AS orders,
       SUM(f.revenue) AS revenue
FROM fact_sales AS f
JOIN dim_date AS d ON d.date_key = f.date_key
WHERE f.order_status <> 'cancelled'
GROUP BY day_type
ORDER BY day_type;

-- 2.
SELECT cur.city AS current_city, SUM(f.revenue) AS revenue
FROM fact_sales AS f
JOIN dim_customer AS c ON c.customer_key = f.customer_key
JOIN dim_customer AS cur ON cur.customer_id = c.customer_id AND cur.is_current = 1
WHERE f.order_status <> 'cancelled'
GROUP BY cur.city
ORDER BY revenue DESC, current_city;

-- PostgreSQL
-- 3. An event outside every partition fails: "no partition of relation ... found for row".
CREATE TABLE sales_events_2026q3 PARTITION OF sales_events
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
INSERT INTO sales_events VALUES (100, '2026-08-15', 3, 2500);
SELECT tableoid::regclass::text AS partition, COUNT(*) AS events
FROM sales_events
GROUP BY 1
ORDER BY 1;
INSERT INTO sales_events VALUES (101, '2027-01-05', 3, 900);

+---------------------+--------+
| partition           | events |
+---------------------+--------+
| sales_events_2026q1 |      8 |
| sales_events_2026q2 |      5 |
| sales_events_2026q3 |      1 |
+---------------------+--------+
Error: no partition of relation "sales_events" found for row

Summary

• OLTP systems run the business with small normalized transactions; OLAP systems answer big questions over history, usually in a separate, column-oriented warehouse.
• A star schema is a fact table at a declared grain, surrounded by dimension tables with surrogate keys; queries join, filter by attributes and sum measures.
• Type 1 dimensions overwrite; type 2 keep history with validity ranges and a point-in-time join — the same idea that keeps ML features leak-free.
• Batch is the default; streaming (Kafka, Flink, Spark Structured Streaming) is for decisions that need fresh data. Lakes hold raw files, warehouses hold modelled tables, lakehouses combine them.
• Scale with partitioning (pruning), replicas, sharding and columnar storage; keep it governed with catalogs, careful schema evolution and an eye on bytes scanned.

Next: SQL Interview and Problem-Solving Practice brings the whole tutorial together in graded problems on the shop data, each with a solution and the reasoning behind it.