Data Modelling: Normalisation, Star Schemas & SCDs

Designing data so it's correct and easy to use. Learn entities, keys (primary, foreign, natural, surrogate), normalisation (1NF, 2NF, 3NF) for OLTP, dimensional modelling for analytics (facts, dimensions, grain, star and snowflake schemas), conformed dimensions, fact types (transaction, snapshot, accumulating), slowly changing dimensions (types 1, 2, 3), Data Vault and One Big Table, and modelling in dbt — with runnable SQLite code implementing an SCD type-2 customer dimension and a point-in-time join.

A good data model makes correct answers easy and wrong answers hard. Operational databases are normalised to avoid update anomalies; analytical warehouses are modelled dimensionally so business users can slice facts by descriptive attributes.

Modelling also captures history: when a customer moves city, should old sales count under the old city or the new one? Slowly changing dimensions answer this explicitly.

An analogy

A star schema is like a receipt (fact) surrounded by reference cards (dimensions): the receipt records what was bought, how many and for how much; the cards describe who bought it, where, when and what the product is.

Keys and normalisation

Primary keys uniquely identify rows; foreign keys reference other tables. Normalisation splits data to remove redundancy: a customer's city stored once in a customers table, not repeated on every order. This prevents update anomalies in OLTP systems but requires many joins for analytics.

Dimensional modelling (Kimball)

Design steps:

Slowly changing dimensions

Type 0: never change. Type 1: overwrite (no history — fine for corrections). Type 2: add a new row with effective dates and current flag (full history). Type 3: add a 'previous value' column (limited history). Type 2 requires surrogate keys and point-in-time joins; snapshot tables (dbt snapshots) automate it.

Other modelling approaches

Fact types: transaction, periodic snapshot (daily balances), accumulating snapshot (order lifecycle milestones), factless facts (events without measures). Conformed dimensions shared across facts enable drill-across. Data Vault (hubs, links, satellites) suits auditable enterprise integration; One Big Table (wide denormalised) suits BI tools and columnar engines. Semantic layers/metrics stores define KPIs once for consistency.

Formulas

Worked example: Declare the grain

Given: Online orders with multiple items

Find: Fact grain.

Answer: One row per order line (order_id, product)

Worked example: 3NF violation

Given: orders(order_id, customer_id, customer_city)

Find: Problem.

Answer: Transitive dependency — move city to customers

Worked example: Additivity

Given: Account balance fact

Find: Can we SUM across dates?

Answer: No — semi-additive; sum across accounts, not time

SCD type 2 in a star schema (SQLite)

import sqlite3
# Star schema + slowly changing dimension (SCD type 2)
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE dim_customer(sk INTEGER PRIMARY KEY, customer_id INT, city TEXT, valid_from TEXT, valid_to TEXT, is_current INT);
CREATE TABLE fact_sales(sale_id INT, customer_sk INT, sale_date TEXT, amount REAL);
INSERT INTO dim_customer VALUES (1, 101, 'Pune', '2025-01-01', '9999-12-31', 1);
INSERT INTO fact_sales VALUES (1, 1, '2025-06-01', 1000);
""")
# Customer 101 moves to Mumbai on 2026-01-01: close old row, insert new version
db.execute("UPDATE dim_customer SET valid_to='2025-12-31', is_current=0 WHERE customer_id=101 AND is_current=1")
db.execute("INSERT INTO dim_customer VALUES (2, 101, 'Mumbai', '2026-01-01', '9999-12-31', 1)")
db.execute("INSERT INTO fact_sales VALUES (2, 2, '2026-03-01', 1500)")
for row in db.execute("SELECT * FROM dim_customer ORDER BY sk"):
    print("dim:", row)
q = """SELECT d.city, SUM(f.amount) FROM fact_sales f JOIN dim_customer d ON d.sk = f.customer_sk
       GROUP BY d.city ORDER BY d.city"""
for row in db.execute(q):
    print("sales by city at time of sale:", row)
# dim: (1, 101, 'Pune', '2025-01-01', '2025-12-31', 0)
# dim: (2, 101, 'Mumbai', '2026-01-01', '9999-12-31', 1)
# sales by city at time of sale: ('Mumbai', 1500.0)
# sales by city at time of sale: ('Pune', 1000.0)

Type 2 keeps history by closing the old dimension row and adding a new version with a new surrogate key. Facts point to the version valid at the time of the sale, so 2025 sales stay in Pune and 2026 sales count in Mumbai. A type 1 update would have overwritten Pune and silently moved historical sales to Mumbai.

Common mistakes

Applications

Key takeaways

Going further