A pipeline moves data; a model decides what the data means. Get the model wrong and every query downstream fights it: joins that fan out, metrics that disagree between dashboards, a history nobody can reconstruct. Get it right and most analytics questions become a short query on an obvious table. This module covers the two modelling traditions you will meet — normalised schemas for systems that write, dimensional schemas for systems that read — and the decisions that matter in practice: choosing a grain, handling change over time with slowly changing dimensions, keys, and the modern variations that columnar warehouses make possible.
- Explain normalisation and why transactional systems use it
- Design a star schema: declare a grain, separate facts from dimensions, choose surrogate keys
- Handle change over time with the right slowly changing dimension type
- Model events, snapshots and accumulating facts appropriately
- Decide when to denormalise, when to use wide tables, and how to keep one definition per metric
Normalised: the shape of a system that writes
Operational databases are normalised: each fact is stored once, in the table that owns it, and related by keys. A customer's address lives in one row; an order references the customer by id; changing the address touches one place. The normal forms formalise this: no repeating groups (1NF), every non-key column depends on the whole key (2NF), and on nothing but the key (3NF). The payoff is write integrity: no update anomalies, no inconsistent copies. The cost is that answering a question means joining five tables, and the schema reflects how the application works, not how the business thinks.
CREATE TABLE customers (
id bigint PRIMARY KEY,
email text NOT NULL UNIQUE,
created_at timestamptz NOT NULL
);
CREATE TABLE products (
id bigint PRIMARY KEY,
sku text NOT NULL UNIQUE,
name text NOT NULL,
price numeric(12,2) NOT NULL
);
CREATE TABLE orders (
id bigint PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
ordered_at timestamptz NOT NULL,
status text NOT NULL CHECK (status IN ('placed','paid','shipped','refunded'))
);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders(id),
product_id bigint NOT NULL REFERENCES products(id),
quantity int NOT NULL CHECK (quantity > 0),
unit_price numeric(12,2) NOT NULL, -- price at time of order, not a lookup
PRIMARY KEY (order_id, product_id)
);Notice unit_price on the item: it is deliberately a copy, because the product's price changes and the order must remember what was charged. Normalisation is a default, not a law; the exceptions are chosen for a reason and documented.
Dimensional: the shape of a system that reads
Analytics wants the opposite trade: fast, simple reads over many rows, and a schema that matches how people ask questions ("revenue by product category by month by region"). The star schema delivers that. A fact table holds measurements at a declared grain, one row per event or per thing measured, with numeric measures and foreign keys. Dimension tables hold the descriptive context (who, what, where, when) with one row per entity and many descriptive columns. Queries filter and group by dimension attributes and aggregate fact measures; joins are always fact-to-dimension on a single key.
The first and most important decision is the grain: exactly what one row of the fact table represents. "One row per order line" and "one row per order" are different tables with different measures, and mixing grains in one table is the root of most fan-out and double-counting. Declare the grain in the table's documentation and make the fact table's primary key express it.
CREATE TABLE dim_date (
date_key int PRIMARY KEY, -- 20260301
date date NOT NULL,
year int, quarter int, month int, month_name text, day_of_week text, is_weekend boolean, fiscal_period text
);
CREATE TABLE dim_customer (
customer_key bigint PRIMARY KEY, -- surrogate
customer_id bigint NOT NULL, -- natural key from the source
email text, country text, segment text, signup_date date,
valid_from timestamptz NOT NULL, valid_to timestamptz, is_current boolean NOT NULL
);
CREATE TABLE dim_product (
product_key bigint PRIMARY KEY, product_id bigint NOT NULL,
sku text, name text, category text, brand text
);
CREATE TABLE fact_sales (
-- grain: one row per order line
order_id bigint NOT NULL,
line_no int NOT NULL,
date_key int NOT NULL REFERENCES dim_date(date_key),
customer_key bigint NOT NULL REFERENCES dim_customer(customer_key),
product_key bigint NOT NULL REFERENCES dim_product(product_key),
quantity int NOT NULL,
unit_price numeric(12,2) NOT NULL,
line_amount numeric(12,2) NOT NULL,
PRIMARY KEY (order_id, line_no)
);Surrogate keys (customer_key) are integers the warehouse assigns, independent of the source system's ids. They make history possible (one customer id, several versions), insulate the warehouse from source key changes, and keep fact tables narrow.
Change over time: slowly changing dimensions
A customer moves from Berlin to Lisbon. Should last year's orders now count as Portuguese revenue? The answer depends on the question, and slowly changing dimension (SCD) types are the standard ways to encode the choice. Type 1 overwrites: the dimension holds only the current value; history is lost, which is right for corrections (a typo in a name). Type 2 adds a new row per version with validity dates and a current flag; facts reference the version that was current when they happened, so historical reports are stable. Type 3 keeps a previous-value column for a limited comparison. Type 2 is the default for anything the business analyses over time.
-- customer 1042 moved to Lisbon on 2026-03-01
UPDATE dim_customer
SET valid_to = '2026-03-01', is_current = false
WHERE customer_id = 1042 AND is_current;
INSERT INTO dim_customer (customer_key, customer_id, email, country, segment, signup_date, valid_from, valid_to, is_current)
SELECT nextval('dim_customer_key_seq'), customer_id, email, 'PT', segment, signup_date, '2026-03-01', NULL, true
FROM dim_customer WHERE customer_id = 1042 AND valid_to = '2026-03-01';
-- point-in-time lookup when loading a fact
SELECT customer_key FROM dim_customer
WHERE customer_id = 1042
AND valid_from <= '2026-02-14' AND (valid_to IS NULL OR valid_to > '2026-02-14');Type 2 tables grow with change and need care: a valid_to of NULL for the current row, non-overlapping ranges, exactly one current row per natural key — all worth testing (the data quality module does exactly this). In modern warehouses the snapshot feature of transformation tools automates Type 2 from a source table, which is how most teams implement it today.
Kinds of facts, and events
Not every fact table is a list of transactions. A transaction fact has one row per event (a sale, a click); it is the most common and the easiest to reason about. A periodic snapshot has one row per entity per period (account balance per day, inventory per warehouse per week); it answers "what was the state at time t" without replaying events. An accumulating snapshot has one row per process instance with a column per milestone (order placed, paid, shipped, delivered), updated as milestones occur; it makes funnel and lag analysis trivial.
| Fact type | Grain | Typical question |
|---|---|---|
| Transaction | One row per event | How much did we sell yesterday by category? |
| Periodic snapshot | One row per entity per period | What was every account's balance on the 1st? |
| Accumulating snapshot | One row per process, milestone columns | Median days from order to delivery, by region? |
Event streams (product analytics, logs) are transaction facts at very fine grain, often with a flexible JSON payload. Model them with a stable core (event name, timestamp, user, session, device) as columns and the variable properties in a structured column, then build narrower, typed tables for the events the business actually analyses. Keep the raw events immutable and append-only; every downstream table can be rebuilt from them.
Modern variations and one definition per metric
Columnar warehouses changed some trade-offs. Wide, denormalised tables (One Big Table) with hundreds of columns are cheap to scan and remove joins for dashboards; they work well as a final presentation layer built from a star, not as the model itself. Nested and repeated columns (arrays, structs) let a line-item fact live inside its order row. The snowflake variant normalises dimensions (product to category to department) and mostly adds joins for little benefit; keep dimensions flat unless a sub-dimension is shared and large.
Whatever the shape, the property that matters most is one definition per metric. "Revenue" computed three ways in three dashboards is the most common trust failure in analytics. Define each metric once in the transformation layer (a model or a metrics layer), with its grain, filters and NULL handling stated, and have every consumer read that definition. The transformation module builds this; the modelling decision is to design the tables so the definition is simple to express.
fact_sales
grain: one row per order line (order_id, line_no)
measures: quantity, unit_price (as charged), line_amount = quantity * unit_price
keys: date_key (order date), customer_key (SCD2 as of order date), product_key
excludes: cancelled orders (status = 'cancelled'); refunds are separate rows in fact_refunds
source: orders + order_items, loaded incrementally by ordered_at
metric: revenue = SUM(line_amount) WHERE status IN ('paid','shipped') -- the ONE definition