Dimensional Modeling Guide

Example: E-commerce Order

-- Fact table: one row per order line item
CREATE TABLE fct_order_lines (
    order_line_sk       BIGINT PRIMARY KEY,    -- Surrogate key
    order_id            VARCHAR(50),           -- Natural key (degenerate dimension)
    customer_sk         BIGINT REFERENCES dim_customers,
    product_sk          BIGINT REFERENCES dim_products,
    date_sk             INT REFERENCES dim_date,
    -- Measures (aggregatable)
    quantity            INT,
    unit_price          DECIMAL(10,2),
    discount_amount     DECIMAL(10,2),
    line_total          DECIMAL(10,2)
);

-- Dimension table: one row per customer
CREATE TABLE dim_customers (
    customer_sk         BIGINT PRIMARY KEY,    -- Surrogate key
    customer_id         VARCHAR(50),           -- Natural key
    customer_name       VARCHAR(255),
    email               VARCHAR(255),
    segment             VARCHAR(50),           -- 'Enterprise', 'SMB', 'Consumer'
    acquisition_channel VARCHAR(50),
    created_at          TIMESTAMP
);

Example: Date Dimension

CREATE TABLE dim_date (
    date_sk             INT PRIMARY KEY,       -- YYYYMMDD format
    date_actual         DATE NOT NULL,
    day_of_week         VARCHAR(10),           -- 'Monday', 'Tuesday', ...
    day_of_week_num     INT,                   -- 1-7
    day_of_month        INT,
    day_of_year         INT,
    week_of_year        INT,
    month_num           INT,
    month_name          VARCHAR(10),
    quarter_num         INT,
    quarter_name        VARCHAR(10),           -- 'Q1', 'Q2', ...
    year_num            INT,
    fiscal_year         INT,
    fiscal_quarter      INT,
    is_weekend          BOOLEAN,
    is_holiday          BOOLEAN,
    holiday_name        VARCHAR(50)
);

-- Usage: easily filter/group by any date attribute
SELECT 
    d.month_name,
    d.year_num,
    SUM(f.order_total) as revenue
FROM fct_orders f
JOIN dim_date d ON f.order_date_sk = d.date_sk
WHERE d.is_weekend = FALSE
GROUP BY d.month_name, d.year_num;

Example: Accumulating Snapshot (Order Fulfillment)

CREATE TABLE fct_order_fulfillment (
    order_sk                BIGINT PRIMARY KEY,
    order_id                VARCHAR(50),
    customer_sk             BIGINT,
    -- Multiple date foreign keys (milestones)
    order_date_sk           INT,
    payment_date_sk         INT,
    ship_date_sk            INT,
    delivery_date_sk        INT,
    -- Lag measures (calculated)
    days_to_payment         INT,
    days_to_ship            INT,
    days_to_delivery        INT,
    -- Measures
    order_total             DECIMAL(10,2),
    current_status          VARCHAR(50)
);

-- Row is updated as order progresses through fulfillment
-- Initially: only order_date_sk populated
-- After payment: payment_date_sk filled, days_to_payment calculated
-- After shipping: ship_date_sk filled, days_to_ship calculated
-- After delivery: delivery_date_sk filled, days_to_delivery calculated