Dimensional Modeling — Facts and Dimensions

The vocabulary every analytics team shares

The Kimball dimensional model has been the standard mental model for analytics tables for thirty years. The vocabulary — fact tables, dimension tables, grain, conformed dimensions — shows up in every warehouse, every dbt project, every BI tool. Learning it once is worth a career's worth of clear conversations.

Two kinds of table

Fact tables hold the things that happened — orders, page views, payments, sensor readings. Each row is an event or a measurement. Columns are mostly numeric (measures) plus foreign keys to dimension tables.
Dimension tables hold the descriptive context — customers, products, stores, dates. Each row is a thing, not an event. Columns are mostly attributes (name, country, category, color).

Grain — the most important word

The grain of a fact table is what one row represents. "One row per order" is a grain. "One row per order line item" is a different grain. "One row per customer per day" is yet another. Get the grain wrong and every metric you compute against the table will be subtly off, because rows aggregate at the wrong level. The first thing to write down for any new fact table is its grain — in one sentence, on top of the schema.

Conformed dimensions

A conformed dimension is a dimension table used by multiple fact tables with the same key. The customers dimension joined to the orders fact table and to the support_tickets fact table is conformed. Conformed dimensions are how you get "customer-level" metrics that combine signals from many sources without inventing reconciliation logic for each report.

Code

A small star schema in SQL — orders fact + customer/product/date dimensions·sql

-- Fact: one row per order line item (the grain)
CREATE TABLE fact_orders (
    order_id      TEXT NOT NULL,
    line_item_id  TEXT NOT NULL,
    customer_key  INTEGER NOT NULL REFERENCES dim_customers(customer_key),
    product_key   INTEGER NOT NULL REFERENCES dim_products(product_key),
    date_key      INTEGER NOT NULL REFERENCES dim_date(date_key),
    quantity      INTEGER NOT NULL,            -- measure
    amount_usd    NUMERIC(18, 2) NOT NULL,     -- measure
    discount_usd  NUMERIC(18, 2) DEFAULT 0,    -- measure
    PRIMARY KEY (order_id, line_item_id)
);

-- Dimension: one row per customer (current state, see SCD lesson for history)
CREATE TABLE dim_customers (
    customer_key  INTEGER PRIMARY KEY,         -- surrogate key, integer for joins
    customer_id   TEXT NOT NULL UNIQUE,        -- natural key from source
    name          TEXT NOT NULL,
    country       TEXT,
    tier          TEXT,                         -- gold/silver/bronze
    created_at    TIMESTAMP NOT NULL
);

-- Dimension: one row per product
CREATE TABLE dim_products (
    product_key   INTEGER PRIMARY KEY,
    sku           TEXT NOT NULL UNIQUE,
    name          TEXT NOT NULL,
    category      TEXT,
    list_price    NUMERIC(18, 2)
);

-- Dimension: one row per calendar date — the date dimension is your friend
CREATE TABLE dim_date (
    date_key   INTEGER PRIMARY KEY,           -- 20260430
    full_date  DATE NOT NULL,
    year       INTEGER NOT NULL,
    quarter    INTEGER NOT NULL,
    month      INTEGER NOT NULL,
    day_of_week INTEGER NOT NULL,
    is_weekend BOOLEAN NOT NULL
);

Exercise

For any analytics question your team asks regularly ("monthly revenue by country," "DAU by feature," "NPS by tier"), sketch the fact table and the conformed dimensions you'd need. Write the grain of the fact table on top in one sentence. The exercise is to feel that the schema falls out of the question, not the other way around.