S045 · Data Modelling — Dimensional, Data Vault, OBT

Module M05: Data Engineering · Session 45 of 130 · Track: DE 🧮

What you'll be able to do after this session

• Explain Data Modelling to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S044 — NoSQL Landscape — KV, Document, Column, Graph

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · Dimensional Modeling Explained in 10 MINUTES — Kimball's Star Schema (Retail) — retail example, fact and dimensions on a whiteboard.
• 🎥 Deep dive (30–60 min) · Data Modeling Tutorial: Star Schema (aka Kimball Approach) — Kahan Data Solutions builds a warehouse from scratch — grain, conformed dimensions, SCD types.
• 🎥 Hands-on demo (10–20 min) · NoSQL Database Types Explained — contrast with operational shapes; helps you feel why analytics needs its own model.
• 📖 Canonical article · Kimball Group — Dimensional Modeling Techniques — the source of truth for facts, dimensions, SCDs, grain.
• 📖 Book chapter / tutorial · dbt Learn — Dimensional Modeling — modern take: how dimensional modelling lives inside dbt in 2026.
• 📖 Engineering blog · Airbnb — Data Quality at Airbnb — how a real DE team layers dimensional models on top of raw event data.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: How analytics warehouses are shaped.

You are running an e-commerce shop and your CEO asks: "What were sales by product category by country by month for the last two years, and split by whether the customer was new or returning?" On the OLTP database that runs your website — the one designed to make INSERT ORDER fast — this query joins 8 tables, scans 50 million rows, and locks up the checkout flow for everyone. Bad. You need a separate database, shaped differently, for analytics questions. That is a data warehouse, and the design pattern that has ruled it for 30 years is Ralph Kimball's dimensional model.

The trick is to split every business event into two categories of table. Fact tables are long, skinny, and numeric — one row per business event ("this order line was ₹499 for 2 units on this date"). Dimension tables are short and wide — descriptive attributes about the who, what, when, where ("customer 42 is named Anusha, lives in Hyderabad, joined 2025-06-08"). Analytics questions become "sum this measure from the fact, sliced by these dimensions" — which is exactly how BI tools (Power BI, Tableau, Looker) think. Query patterns are simple joins from a central fact table to surrounding dimensions — the star schema.

The gotcha to carry forever: grain, grain, grain. The first thing you decide about a fact table is exactly what one row represents: "one order," "one order line," "one payment event"? Get grain wrong, everything downstream is broken. The Kimball rule: "declare the grain in one English sentence before you type a single CREATE TABLE." If you cannot, you are not ready to model.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

A tiny star schema for retail sales:

Grain of fact_sales: one row per product sold per order line. State it once, obey it forever.

Worked example — a real question: "Show me monthly revenue by product category for stores in India in 2025."

SELECT
    d.year, d.month,
    p.category,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date    d ON f.date_key    = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store   s ON f.store_key   = s.store_key
WHERE d.year = 2025 AND s.country = 'IN'
GROUP BY d.year, d.month, p.category
ORDER BY d.year, d.month, revenue DESC;

Star schema shines here: 4-way join, all through single-column foreign keys, columnar warehouses (BigQuery, Snowflake, Redshift, Synapse) execute this in seconds even over billions of rows.

Three modelling philosophies, one table:

Slowly Changing Dimensions (SCDs) in one line: when a customer moves from Delhi to Hyderabad, do you (Type 1) overwrite? (Type 2) close the old row and insert a new one with valid_from/valid_to? (Type 3) add a previous_city column? Type 2 is by far the most common in real warehouses.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Build a mini warehouse in DuckDB (no server needed, runs in-process).

pip install duckdb
python3 - <<'PY'
import duckdb, random
from datetime import date
 
con = duckdb.connect(":memory:")
 
# ---------- Dimensions ----------
con.execute('''CREATE TABLE dim_date AS
SELECT (DATE '2024-01-01' + INTERVAL (g) DAY) AS date_key,
       date_part('year',  DATE '2024-01-01' + INTERVAL (g) DAY) AS year,
       date_part('month', DATE '2024-01-01' + INTERVAL (g) DAY) AS month,
       date_part('day',   DATE '2024-01-01' + INTERVAL (g) DAY) AS day,
       date_part('dow',   DATE '2024-01-01' + INTERVAL (g) DAY) IN (0,6) AS is_weekend
FROM generate_series(0, 730) t(g);''')
 
con.execute('''CREATE TABLE dim_product (
    product_key INT PRIMARY KEY, sku VARCHAR, name VARCHAR,
    category VARCHAR, price DECIMAL(10,2));''')
cats = ["Books","Electronics","Home","Fashion","Grocery"]
for i in range(1, 501):
    con.execute("INSERT INTO dim_product VALUES (?,?,?,?,?)",
                [i, f"SKU-{i:04d}", f"prod_{i}", random.choice(cats),
                 round(random.uniform(50,5000),2)])
 
con.execute('''CREATE TABLE dim_customer (
    customer_key INT PRIMARY KEY, name VARCHAR, city VARCHAR,
    country VARCHAR, cohort_month DATE);''')
countries = ["IN","US","DE","JP","BR"]
for i in range(1, 5001):
    con.execute("INSERT INTO dim_customer VALUES (?,?,?,?,?)",
                [i, f"cust_{i}", "city", random.choice(countries), date(2024,1,1)])
 
# ---------- Fact ----------
con.execute('''CREATE TABLE fact_sales (
    date_key DATE, product_key INT, customer_key INT,
    quantity INT, revenue DECIMAL(12,2));
INSERT INTO fact_sales
SELECT DATE '2024-01-01' + INTERVAL (CAST(random()*730 AS INT)) DAY,
       CAST(1 + random()*499 AS INT),
       CAST(1 + random()*4999 AS INT),
       CAST(1 + random()*5 AS INT),
       ROUND(random()*10000, 2)
FROM generate_series(1, 1000000);''')
 
# ---------- The star-schema BI query ----------
print(con.execute('''
SELECT d.year, d.month, p.category, ROUND(SUM(f.revenue),0) AS revenue
FROM fact_sales f
JOIN dim_date    d ON f.date_key    = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE d.year = 2024 AND c.country='IN'
GROUP BY d.year, d.month, p.category
ORDER BY d.year, d.month, revenue DESC
LIMIT 20;''').fetchdf())
PY

Observe (checklist):

1. fact_sales has 1 million skinny rows; each dim is tiny — that's normal.
2. The BI query joins 3 dims to the fact and returns in well under a second — columnar engines love this shape.
3. Every filter (d.year, c.country) is on a dimension, never on a fact — that's the point.
4. If you added a new attribute to a product (e.g., subcategory), you only touch dim_product, not fact_sales.
5. The date dimension is precomputed — no date_part() in the query — dashboards get sub-second responses.

Try this modification: add an SCD Type 2 to dim_customer — new columns valid_from, valid_to, is_current. Update a customer's city and preserve history. Now write a query "revenue by customer's city at the time of the sale." (Hint: join on date_key BETWEEN valid_from AND valid_to.)

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Every mature analytics team fights the same three battles. First: grain drift. A fact table declared as "one order line" gets a late_return_refund_amount column added, then a total_lifetime_customer_spend column — suddenly the same row is at three different grains and every dashboard silently double-counts. Fix: put refunds in a separate fact (fact_refunds), lifetime metrics in a customer dimension attribute or a periodic snapshot fact. Second: conformed dimensions. Marketing has one customer_id, finance has another, product has a third; nobody can join across domains. Fix: an explicit "conformed customer dim" owned by data engineering as source of truth. Airbnb, Uber, and Netflix all have public blog posts about this transition — usually a 12-month project.

Third: SCD Type 2 is easy to write and hard to maintain. Missing an update means historical reports quietly change every day. Always add dbt (or an equivalent) tests: not_null, unique, relationships, and "row counts by valid_from should be monotonic." Rerun them every load. dbt snapshots are the modern well-lit path.

The Kimball vs Data Vault vs OBT holy war: in 2026, most teams use a layered approach: raw → staging → dimensional (Kimball) → OBT wide tables for feature stores and ML. dbt made this pattern the default. Nobody writes Data Vault by hand anymore — if you need auditability, use it generated by tools like AutomateDV.

Cost gotcha: columnar warehouses charge per bytes-scanned (BigQuery) or per credit-second (Snowflake). A star-schema query that filters by date_key on a partitioned fact table can scan 100x less data than the "same" query written against a wide OBT. Partition by date, cluster by high-selectivity columns, and educate your BI users to always include a date filter. This alone typically cuts a warehouse bill by 40-70% in a real deployment — I've seen it firsthand at Microsoft on multi-petabyte ODSP data.

(e) Quiz + exercise · 10 min

1. What's the difference between a fact table and a dimension table? Give one example of each.
2. Define "grain" in one sentence and explain why it must be declared before designing the fact.
3. Explain Slowly Changing Dimensions Type 1 vs Type 2 vs Type 3 in one line each.
4. Why do star-schema queries perform well on columnar warehouses like BigQuery / Snowflake / Synapse?
5. When would you prefer One Big Table (OBT) over a proper star schema?

Stretch (connects to S041 — Indexes): most columnar warehouses (BigQuery, Snowflake) do not support B-tree indexes at all — yet they beat indexed row-stores on analytical queries. What are they using instead of B-trees, and why does that trade-off work for reads-heavy analytics but not for OLTP?

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is Data Modelling? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S046): Batch vs Streaming — Mental Model & Use Cases
• Previous (S044): NoSQL Landscape — KV, Document, Column, Graph
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.