Home / Learn / Courses / Data modelling / Cheat sheet
All lessons

Data modelling cheat sheet

The design rules and load SQL of the course on one page, each item with a short example, what it means in one line and the lesson that explains it. Examples use SQLite and the Northbay star (fact_sales, dim_customer, dim_product, dim_date) unless they say otherwise. Print it or keep it open while you design.

Try the SQLite examples in the SQL sandbox.

The four-step design

Four steps, in this order Lesson 2.1

1 Choose the business process   taking orders, returns, stock counts
2 Declare the grain             what one row means, in business words
3 Identify the dimensions       who, what, where, when: the context
4 Identify the facts            the numbers each event measures

Grain comes before columns: decide what a row is, and the dimensions and facts follow from it.

One process, one fact table Lesson 2.1

sales      → fact_sales        (one row per order line)
returns    → fact_returns      (one row per returned line)
stock      → snap_store_day    (one row per store per day)

Each process is its own stream of events. Different processes share dimensions, not fact tables.

Facts or dimension? Lesson 2.3

Numbers you add up             → fact     quantity, sales_amount
Words you filter or group by   → dimension  brand, city, weekday
A number you group by          → dimension  price band, age band

Ask what people do with the column: sum it (fact) or slice by it (dimension).

Choosing the grain

Grain statement Lesson 2.2

"One row per product on a completed order."   ✓ real-world, exact
"Sales data."                                 ✗ not a grain
"order_id and product_id."                    ✗ columns, not meaning

Write it in business words first. The keys follow from it.

Choose the atomic grain Lesson 2.2

order line     ← atomic: the finest the tills record (start here)
order
store per day
store per month

You can always roll up from fine rows, never drill down from summed ones.

Test the grain Lesson 2.2

SELECT order_id, product_id, COUNT(*) AS rows
FROM fact_sales
GROUP BY order_id, product_id
HAVING COUNT(*) > 1          -- should return nothing

If the grain is right, its keys are unique.

The mixed-grain trap Lesson 2.2

-- shipping is per ORDER, but stored on every LINE:
SUM(shipping_fee)                  -- counted once per line: too big
-- fix: allocate it to lines, or keep it in an order-grain fact

Every measure must be true at the table's grain, or sums double count.

Fact tables

TypeOne row perExampleChanges after load?RowsLesson
Transaction event (order line, payment)fact_salesNo: append-only, corrections as reversing rowsSparse: no event, no row 3.1
Periodic snapshot thing per period (product per day)snap_store_day, month-end balancesNo: a new set of rows each periodDense: a row even when nothing moved 3.2
Accumulating snapshot item through a pipeline (order)order placed → shipped → deliveredYes: updated at each milestoneOne per item; date column per milestone 3.3
Factless event or coverage, no measuresign-ups, products on promotionUsually noCount the rows 3.4

Transaction fact Lesson 3.1

CREATE TABLE fact_sales (
  date_key INTEGER, store_key INTEGER, product_key INTEGER,
  customer_key INTEGER,        -- -1 for guests, never NULL
  order_id INTEGER,            -- degenerate dimension
  quantity INTEGER, sales_amount REAL);

Foreign keys to every dimension, then the measures. Extended amounts, not unit prices.

Periodic snapshot frame Lesson 3.2

SELECT d.full_date, s.store_id, COALESCE(SUM(x.amount), 0) AS sales
FROM dim_date d CROSS JOIN dim_store s          -- every store, every day
LEFT JOIN daily_sales x ON x.day = d.full_date AND x.store_id = s.store_id
GROUP BY d.full_date, s.store_id

A CROSS JOIN of the dimensions makes the frame; a LEFT JOIN fills it, with 0 for quiet days.

Accumulating snapshot milestone Lesson 3.3

UPDATE fact_order_pipeline
SET shipped_date_key = 20251203,
    days_to_ship     = 2               -- lag between milestones
WHERE order_id = 5021;

Rows are updated as each item moves on, so lags and "stuck" items are one query away.

Measure additivity

KindAdds up acrossExamplesHow to totalLesson
Additive every dimensionquantity, sales_amount, costSUM 3.5
Semi-additive every dimension except timestock on hand, account balance, membersSUM across stores; over time take the last value or the average 3.5
Non-additive nothingmargin %, unit price, average basket, distinct customersStore the parts; divide after summing (ratio of sums) 3.5

Ratio of sums, not average of averages Lesson 3.5

SUM(profit) / SUM(sales_amount)     -- ✓ true overall margin
AVG(margin_pct)                     -- ✗ small stores count as much as big ones

Add up the numerator and denominator first, then divide.

Semi-additive over time Lesson 3.5

SELECT store_id, SUM(on_hand) AS units      -- across stores: fine
FROM snap_store_day
WHERE full_date = '2025-12-31'              -- one day: the closing level
GROUP BY store_id

Never sum stock across days. Pick the period-end row (or average the days).

Dimension patterns

Surrogate key + unknown member Lesson 4.1

CREATE TABLE dim_customer AS
SELECT ROW_NUMBER() OVER (ORDER BY customer_id) AS customer_key,  -- surrogate
       customer_id,                                               -- natural key
       first_name || ' ' || last_name AS customer_name,
       TRIM(city) AS city
FROM customers;
INSERT INTO dim_customer VALUES (-1, NULL, 'Guest / unknown', 'Unknown');

A meaningless whole-number key, plus a -1 row that facts with no customer point to.

Key lookup in the fact load Lesson 4.1

SELECT COALESCE(d.customer_key, -1) AS customer_key, o.order_id, ...
FROM orders o
LEFT JOIN dim_customer d ON d.customer_id = o.customer_id AND d.is_current = 1

Swap natural keys for surrogate keys. LEFT JOIN plus COALESCE means no fact key is ever NULL.

Date dimension columns Lesson 4.2

date_key        20250307   integer YYYYMMDD
full_date       2025-03-07
year, quarter, month_num, month_name
weekday_num, weekday_name, is_weekend
is_holiday, is_promo
fiscal_year, fiscal_quarter          -- April start: FY2026 begins 2025-04-01

One row per day, made with a recursive CTE, so empty days still show and any calendar filter works.

Role-playing Lesson 4.4

CREATE VIEW order_date  AS SELECT date_key AS order_date_key,  full_date AS order_date  FROM dim_date;
CREATE VIEW return_date AS SELECT date_key AS return_date_key, full_date AS return_date FROM dim_date;

One physical dimension, one view (or alias) per role. In Power BI, one date table per role.

Junk dimension Lesson 4.5

CREATE TABLE dim_order_flags AS
SELECT ROW_NUMBER() OVER () AS flags_key, channel, payment_type, is_gift
FROM (SELECT DISTINCT channel, payment_type, is_gift FROM orders);

Gather several low-cardinality flags into one small dimension: one key on the fact instead of many.

Degenerate dimension Lesson 4.5

fact_sales.order_id      -- no dim_order table: nothing else to describe

A transaction number kept on the fact for grouping lines into baskets and tracing back to the source.

Conformed + drill across Lesson 4.3

WITH s AS (SELECT product_key, SUM(sales_amount) AS sales  FROM fact_sales   GROUP BY product_key),
     r AS (SELECT product_key, SUM(refund_amount) AS refunds FROM fact_returns GROUP BY product_key)
SELECT p.category_name, SUM(s.sales) AS sales, SUM(r.refunds) AS refunds
FROM s LEFT JOIN r USING (product_key) JOIN dim_product p USING (product_key)
GROUP BY p.category_name

Summarise each fact to the shared dimension first, then join. Never join two fact tables row to row.

Snowflake or outrigger Lesson 4.6

star:      fact_sales → dim_product (category_name, department_name inside)
snowflake: fact_sales → dim_product → dim_category → dim_department

Keep hierarchies flattened in one dimension unless a level is big, shared or changes on its own.

Slowly changing dimensions

TypeOn a changeHistory keptRows per customerUse forLesson
0 Nothing: the value is fixedThe original only1Sign-up date, date of birth 5.1
1 OverwriteNone1Typo fixes, attributes nobody analyses history by 5.1
2 Expire the row, insert a new versionFull1 per versionCity, region, tier, segment, category (the default) 5.1
3 Move the old value to a previous_ columnOne step1A one-off reorganisation (old region vs new region) 5.1
4 Fast-changing attributes go in a mini-dimension with its own key on the factFull, in the mini-dimension1 (main) + small mini-dimensionAge band, income band 5.1
5 Type 4 plus the current mini-dimension key on the main dimension as type 1Full + currentAs type 4Rare; a refinement of 4 5.1
6 Type 2 rows plus current_ columns overwritten on every version (1 + 2 + 3)Full + current1 per version"Then" and "now" in one query 5.1

SCD2 column set Lesson 5.1

customer_key   INTEGER   -- surrogate: one per version
customer_id    INTEGER   -- natural / durable key: same for all versions
city, loyalty_tier       -- the tracked attributes
valid_from     TEXT      -- first day this version was true
valid_to       TEXT      -- day it stopped ('9999-12-31' if current)
is_current     INTEGER   -- 1 for the current version, else 0

Ranges are half-open: valid_from ≤ date < valid_to, so each date matches exactly one version.

SCD2 load skeleton Lesson 5.2

-- 0 clean staging the same way as the dimension (TRIM, codes)
-- 1 expire current rows that changed
UPDATE dim_customer
SET valid_to = (SELECT s.load_date FROM stg_customers s
                WHERE s.customer_id = dim_customer.customer_id),
    is_current = 0
WHERE is_current = 1
  AND EXISTS (SELECT 1 FROM stg_customers s
              WHERE s.customer_id = dim_customer.customer_id
                AND (s.city IS NOT dim_customer.city            -- NULL-safe:
                  OR s.loyalty_tier IS NOT dim_customer.loyalty_tier)); -- IS DISTINCT FROM elsewhere
-- 2 insert a current row for every staged key without one (changed + new)
INSERT INTO dim_customer
SELECT (SELECT MAX(customer_key) FROM dim_customer)
         + ROW_NUMBER() OVER (ORDER BY s.customer_id),
       s.customer_id, s.city, s.loyalty_tier,
       s.load_date, '9999-12-31', 1
FROM stg_customers s
WHERE NOT EXISTS (SELECT 1 FROM dim_customer d
                  WHERE d.customer_id = s.customer_id AND d.is_current = 1);

Two statements, safe to run twice. Compare with IS NOT (SQLite) or IS DISTINCT FROM, never <>, or NULL changes are lost.

As it was, as it is, as of Lesson 5.4

-- as it was: join the version the fact points at
JOIN dim_customer d ON d.customer_key = f.customer_key
-- as it is: step through the durable key to the current version
JOIN dim_customer cur ON cur.customer_id = d.customer_id AND cur.is_current = 1
-- as of a date
JOIN dim_customer a ON a.customer_id = d.customer_id
 AND '2025-03-31' >= a.valid_from AND '2025-03-31' < a.valid_to

Same dimension, three questions. Filter on the version you join to, never on the one the fact points at.

Late-arriving dimension Lesson 5.3

INSERT INTO dim_customer (customer_key, customer_id, customer_name, is_inferred)
SELECT ..., s.customer_id, 'Not yet known', 1          -- inferred member
FROM stg_sales s WHERE NOT EXISTS (...);
-- when the real row arrives: overwrite it in place, set is_inferred = 0

Give the unknown key a real surrogate now; fill in its attributes later without changing the key.

Warehouse architectures

ApproachCore ideaShapeBest whenWatch out forLesson
Kimball Build stars one process at a time, tied by conformed dimensions (bottom-up)Star schemas + bus matrixFast delivery, BI tools, Power BIStovepipes if dimensions are not conformed 2.5
Inmon One integrated enterprise warehouse first, marts built from it (top-down)Normalised (about 3NF) EDW + dependent martsMany sources, one version of the truthSlow to first report; marts still needed 6.1
Data Vault Keys, relationships and history in separate insert-only tablesHubs, links, satellites + information martsMany changing sources, strong audit needsMany joins; build stars on top for reports 6.2
One big table Join every attribute onto the fact in advanceOne wide table per grainColumnar warehouses, a few heavy dashboardsRepeated text, costly changes, measures at the wrong grain 6.3
Medallion Layers of a lakehouse by how clean the data isBronze (raw) → silver (clean) → gold (business-ready)Databricks, Fabric, files in a lakeIt names layers, not a model: gold still needs stars 6.4

Data Vault in three tables Lesson 6.2

hub_customer   (customer_hk, customer_id, load_date, record_source)
link_order     (order_hk, customer_hk, order_id_hk, load_date, record_source)
sat_customer   (customer_hk, load_date, hash_diff, city, loyalty_tier, record_source)
-- hash key: MD5(UPPER(TRIM(customer_id)))
-- new sat row only when hash_diff differs from the latest one

Hubs hold keys, links hold relationships, satellites hold history. Nothing is updated.

Staging → core → marts

Three layers Lesson 7.1

sources → stg_*         one per source table: rename, cast, TRIM; no joins
        → dim_*, fact_* the conformed star (core)
        → mart_*        shaped for one team or dashboard

Each layer reads only from the layer below. Sources are touched only by staging.

As views Lesson 7.1

CREATE VIEW stg_customers AS
SELECT customer_id, TRIM(city) AS city, COALESCE(loyalty_tier, 'None') AS loyalty_tier
FROM customers;
CREATE VIEW mart_sales_month AS
SELECT d.year, d.month_num, SUM(f.sales_amount) AS sales
FROM fact_sales f JOIN dim_date d USING (date_key)
GROUP BY d.year, d.month_num;

Arrows point one way (a DAG): a fix in staging flows to everything downstream.

Loading: upserts, MERGE and idempotent loads

Upsert in SQLite / PostgreSQL Lesson 7.2

INSERT INTO dim_product (product_id, product_name, unit_price, updated_at)
SELECT product_id, product_name, unit_price, '2025-12-31'
FROM stg_products
WHERE true                               -- SQLite: lets ON follow a SELECT
ON CONFLICT (product_id) DO UPDATE SET
  product_name = excluded.product_name,
  unit_price   = excluded.unit_price,
  updated_at   = excluded.updated_at
WHERE dim_product.unit_price   <> excluded.unit_price
   OR dim_product.product_name <> excluded.product_name;

Needs a PRIMARY KEY or UNIQUE on the conflict column. The final WHERE skips unchanged rows.

MERGE in T-SQL (SQL Server, Azure SQL) Lesson 7.2

MERGE dbo.dim_product AS t
USING stg.products AS s
   ON t.product_id = s.product_id
WHEN MATCHED AND (t.unit_price <> s.unit_price
              OR t.product_name <> s.product_name) THEN
    UPDATE SET product_name = s.product_name, unit_price = s.unit_price,
               updated_at = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (product_id, product_name, unit_price, updated_at)
    VALUES (s.product_id, s.product_name, s.unit_price, SYSUTCDATETIME())
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET is_deleted = 1;           -- soft delete
-- end with a semicolon; the source must have one row per key

Three cases: matched and changed, new in source, missing from source. Snowflake, BigQuery and Databricks are similar.

Idempotent fact load (delete-insert) Lesson 7.3

BEGIN;
DELETE FROM fact_order_lines
WHERE order_date BETWEEN '2025-12-01' AND '2025-12-31';   -- the batch window
INSERT INTO fact_order_lines
SELECT ... FROM stg_order_lines
WHERE order_date BETWEEN '2025-12-01' AND '2025-12-31';
COMMIT;

Run it twice, get the same rows. The transaction means a failed insert also undoes the delete.

Audit columns and load log Lesson 7.3

load_id        which run wrote the row (→ load_log)
loaded_at      when that run happened
record_source  which system sent it

load_log (load_id, table_name, batch_from, batch_to,
          rows_read, rows_deleted, rows_inserted, status, started_at)

Answer "which load wrote this?" and undo one bad load without touching the rest.

Warehouse test checklist

Each test is a query for bad rows: zero rows (or 0) means pass. Run them after every load, and stop the pipeline on any failure.

Unique keys Lesson 7.4

SELECT product_key, COUNT(*) FROM dim_product
GROUP BY product_key HAVING COUNT(*) > 1

A duplicate dimension key doubles every fact that joins to it.

No NULL fact keys Lesson 7.4

SELECT COUNT(*) FROM fact_sales WHERE customer_key IS NULL

Missing values belong on the -1 unknown member, not NULL.

No orphans Lesson 7.4

SELECT COUNT(*) FROM fact_sales f
LEFT JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE d.customer_key IS NULL

Every fact key must exist in its dimension, or inner joins drop rows silently.

Accepted values Lesson 7.4

SELECT COUNT(*) FROM dim_customer
WHERE COALESCE(loyalty_tier, '?') NOT IN ('Bronze', 'Silver', 'Gold', 'None')
   OR city <> TRIM(city)

COALESCE so NULLs fail too: NOT IN skips them.

Reconcile with the source Lesson 7.4

SELECT (SELECT COUNT(*) FROM order_items i JOIN orders o USING (order_id)
        WHERE o.status = 'completed')                      AS source_lines,
       (SELECT COUNT(*) FROM fact_sales)                     AS fact_lines,
       (SELECT COUNT(*) FROM fact_sales f
        JOIN dim_product p USING (product_key))              AS after_join

All three should match. A bigger after_join is fan-out from a duplicate key.

SCD2: one current row per key Lesson 5.2

SELECT customer_id FROM dim_customer
WHERE is_current = 1
GROUP BY customer_id HAVING COUNT(*) > 1

Two current versions means a load ran badly or twice.

SCD2: no overlapping ranges Lesson 5.2

SELECT a.customer_id FROM dim_customer a
JOIN dim_customer b ON b.customer_id = a.customer_id
 AND b.customer_key <> a.customer_key
 AND a.valid_from < b.valid_to AND b.valid_from < a.valid_to

Overlaps make a fact match two versions and double count.

Cost and speed

Let the warehouse skip data Lesson 7.6

WHERE order_date >= '2025-12-01' AND order_date < '2026-01-01'  -- ✓ prunes partitions
WHERE strftime('%Y-%m', order_date) = '2025-12'                -- ✗ reads every partition
SELECT * ...                                                  -- ✗ reads every column

Filter the partition column with a plain range and name only the columns you need.

Bytes scanned Lesson 7.6

bytes ≈ table size × share of columns read × share of partitions read

On pay-per-scan warehouses, that is the bill. Summary tables shrink the first term.

See also the data warehouse glossary and the SQL cheat sheet.