Home / Learn / Data & AI / Data modelling
All lessons

Data modelling and warehousing: star schemas, facts, dimensions and history

Design the tables behind every dashboard. Normal forms, Kimball star schemas, facts and dimensions, slowly changing dimensions, Data Vault and the lakehouse, and how a warehouse is loaded and tested, with live SQL to build each piece yourself.

For analysts, BI developers and data engineers. Know basic SQL first (Modules 1-4 of SQL for analysts). Free, no sign-up. All 38 lessons, about 18.3 hours.

Quick reference: glossary · cheat sheet · SQL sandbox

What you will be able to do

  • Design a star schema from a business process: choose the grain, the facts and the dimensions, and plan a whole warehouse with a bus matrix.
  • Handle history with slowly changing dimensions, and answer "as it was then" questions correctly.
  • Know the other approaches: normal forms, Inmon, Data Vault, one big table and the lakehouse, and when each fits.
  • Load and test a warehouse: staging layers, MERGE, audit columns and tests for keys and orphans.
  • Leave with a portfolio project: the Northbay warehouse, designed, built and tested by you, then moved to a cloud warehouse.

Every lesson has diagrams, live SQL boxes where you build each piece in your browser, and a 3-question quiz. Know basic SQL first: SQL for analysts, Modules 1-4. For the tools that run warehouses in production (dbt, Dagster, Azure SQL), go on to Pipeline to Dashboard.

0 of 38 lessons done

Without the tick, your progress lasts until you close this tab. Either way it stays in this browser: there is no sign-up and nothing is sent to us.

All modules

Part A: Why and how we model

1 Why model data at all 4 lessons

  1. 1.1 Two kinds of database: running the shop and understanding it
  2. 1.2 Normal forms: 1NF, 2NF and 3NF
  3. 1.3 Why analytics breaks the rules
  4. 1.4 Reading and drawing ER diagrams

2 Kimball basics 5 lessons

  1. 2.1 Start from a business process
  2. 2.2 Declare the grain
  3. 2.3 Facts and dimensions
  4. 2.4 The star schema
  5. 2.5 The bus matrix: planning a whole warehouse

Part B: Facts and dimensions in depth

3 Fact tables 5 lessons

  1. 3.1 Transaction fact tables
  2. 3.2 Periodic snapshot fact tables
  3. 3.3 Accumulating snapshot fact tables
  4. 3.4 Factless fact tables
  5. 3.5 Additive, semi-additive and non-additive measures

4 Dimension tables 6 lessons

  1. 4.1 Surrogate keys
  2. 4.2 The date dimension
  3. 4.3 Conformed dimensions
  4. 4.4 Role-playing dimensions
  5. 4.5 Junk and degenerate dimensions
  6. 4.6 Snowflakes and outriggers

5 History: slowly changing dimensions 4 lessons

  1. 5.1 SCD types 0, 1, 2, 3 and 6
  2. 5.2 SCD type 2 in SQL, by hand
  3. 5.3 Late-arriving facts and dimensions
  4. 5.4 Point-in-time questions: "as it was" vs "as it is"

Part C: Warehouses in practice

6 Beyond Kimball 4 lessons

  1. 6.1 Inmon and the enterprise warehouse
  2. 6.2 Data Vault: hubs, links and satellites
  3. 6.3 One big table and wide tables
  4. 6.4 Lakes, lakehouses and medallion layers

7 Building and running a warehouse 6 lessons

  1. 7.1 Staging, core and marts
  2. 7.2 Loading with MERGE and upserts
  3. 7.3 Audit columns, load logs and idempotent loads
  4. 7.4 Testing a warehouse: counts, keys and orphans
  5. 7.5 The semantic layer and the Power BI star schema
  6. 7.6 Partitioning, clustering and cost

8 Capstone and interviews 4 lessons

  1. 8.1 Capstone part 1: design the Northbay warehouse
  2. 8.2 Capstone part 2: build, load and test it
  3. 8.3 Capstone part 3: put it in a cloud warehouse
  4. 8.4 Data modelling interview questions, answered