Dimensional Modeling Deep Dive — The Kimball Way

Ralph Kimball published “The Data Warehouse Toolkit” in 1996, and his dimensional modeling methodology remains the gold standard for analytics data warehouses nearly three decades later. Not because nothing better has been invented — but because dimensional models are intuitive, performant, and survive organizational change.

The core insight is simple: business users think in terms of measurements (facts) and context (dimensions). “How much revenue did we generate (fact) by region (dimension) last quarter (dimension)?” Dimensional modeling structures data to match this natural way of asking questions.

This article goes beyond the basics of star schemas. We will cover advanced fact and dimension types, the planning process, and build a complete model for an e-commerce business.

Fact tables — the measurements

Fact tables store the quantitative measurements of business processes. They are typically the largest tables in the warehouse — billions of rows is common. Every fact table has two types of columns: foreign keys to dimension tables, and numeric measures.

Transactional fact tables

The most common type. One row per business event at the most granular level.

-- fct_order_lines: one row per item in an order
CREATE TABLE fct_order_lines (
    -- Dimension keys (foreign keys)
    date_key          INT REFERENCES dim_date(date_key),
    customer_key      INT REFERENCES dim_customer(customer_key),
    product_key       INT REFERENCES dim_product(product_key),
    store_key         INT REFERENCES dim_store(store_key),
    promotion_key     INT REFERENCES dim_promotion(promotion_key),

    -- Degenerate dimension (no separate table needed)
    order_number      VARCHAR(20),

    -- Facts (measures)
    quantity          INT,
    unit_price        DECIMAL(10,2),
    discount_amount   DECIMAL(10,2),
    net_amount        DECIMAL(10,2),
    tax_amount        DECIMAL(10,2),
    gross_amount      DECIMAL(10,2)
);

Transactional facts are additive — you can sum net_amount across any combination of dimensions and get a meaningful result. Revenue by region, by product, by month — all valid aggregations.

Some measures are semi-additive (can be summed across some dimensions but not all). Account balances can be summed across customers but not across time — you cannot add January’s balance to February’s balance.

Some are non-additive (cannot be summed at all). Unit price, ratios, and percentages fall here. You average or weight them instead.

Periodic snapshot fact tables

Capture the state of a process at regular intervals. Think of it as taking a photograph of your business every day, week, or month.

-- fct_inventory_daily: one row per product per warehouse per day
CREATE TABLE fct_inventory_daily (
    date_key          INT REFERENCES dim_date(date_key),
    product_key       INT REFERENCES dim_product(product_key),
    warehouse_key     INT REFERENCES dim_warehouse(warehouse_key),

    -- Snapshot measures
    quantity_on_hand  INT,           -- Semi-additive
    quantity_on_order INT,           -- Semi-additive
    reorder_point     INT,
    days_of_supply    DECIMAL(5,1),  -- Non-additive
    inventory_value   DECIMAL(12,2)  -- Semi-additive
);

When to use: When the business question is “what was the state at a point in time?” rather than “what happened?” Inventory levels, account balances, pipeline stages, and subscriber counts are all periodic snapshot candidates.

The key difference from transactional facts: periodic snapshots have predictable row growth. If you have 10,000 products across 5 warehouses and snapshot daily, you add exactly 50,000 rows per day.

Accumulating snapshot fact tables

Track the lifecycle of a process that has a defined beginning and end, with milestones along the way.

-- fct_order_fulfillment: one row per order, updated as it progresses
CREATE TABLE fct_order_fulfillment (
    order_key           INT,

    -- Milestone date keys (each references dim_date)
    order_date_key      INT REFERENCES dim_date(date_key),
    payment_date_key    INT REFERENCES dim_date(date_key),
    ship_date_key       INT REFERENCES dim_date(date_key),
    delivery_date_key   INT REFERENCES dim_date(date_key),
    return_date_key     INT REFERENCES dim_date(date_key),  -- NULL if not returned

    -- Other dimensions
    customer_key        INT REFERENCES dim_customer(customer_key),
    warehouse_key       INT REFERENCES dim_warehouse(warehouse_key),

    -- Lag measures (days between milestones)
    days_to_payment     INT,
    days_to_ship        INT,
    days_to_deliver     INT,
    total_fulfillment_days INT,

    -- Amount measures
    order_amount        DECIMAL(10,2),
    shipping_cost       DECIMAL(10,2)
);

The critical difference: Accumulating snapshots are updated as the process progresses. When an order ships, you update ship_date_key and days_to_ship. This makes them the only fact table type that gets UPDATEd.

When to use: Order fulfillment, loan applications, insurance claims, student enrollment — any process with a defined lifecycle and measurable milestones.

Dimension types — the context

Dimensions provide the “who, what, where, when, why, how” context for facts. Most dimensions are straightforward tables of descriptive attributes. The advanced types handle real-world complexity.

Conformed dimensions

A conformed dimension is shared across multiple fact tables, with the same keys, attributes, and values. This is what makes cross-process analysis possible.

-- dim_customer is conformed across all business processes
-- Used by: fct_order_lines, fct_returns, fct_support_tickets, fct_marketing_touches
CREATE TABLE dim_customer (
    customer_key      INT PRIMARY KEY,  -- Surrogate key
    customer_id       VARCHAR(20),      -- Natural key
    customer_name     VARCHAR(100),
    email             VARCHAR(200),
    segment           VARCHAR(20),      -- Enterprise, SMB, Consumer
    acquisition_date  DATE,
    region            VARCHAR(50),
    country           VARCHAR(50)
);

Without conformed dimensions, you cannot answer “Which customers who contacted support also returned products?” because the two fact tables would use different customer definitions. The bus matrix (covered below) is the tool for planning conformed dimensions.

Junk dimensions

When you have several low-cardinality flags or indicators, creating separate dimension tables for each is wasteful. A junk dimension combines them into a single table.

Instead of this:

fct_orders → dim_is_gift (Y/N)
           → dim_is_expedited (Y/N)
           → dim_payment_type (credit/debit/paypal)
           → dim_gift_wrap (Y/N)

Create this:

-- dim_order_flags: all combinations of low-cardinality attributes
CREATE TABLE dim_order_flags (
    order_flag_key    INT PRIMARY KEY,
    is_gift           BOOLEAN,
    is_expedited      BOOLEAN,
    payment_type      VARCHAR(20),
    has_gift_wrap     BOOLEAN
);

-- Only 2 × 2 × 3 × 2 = 24 possible rows
-- Much cleaner than 4 separate dimension tables

Degenerate dimensions

Some dimensional attributes live in the fact table itself — no separate dimension table is needed. The classic example is the order number.

-- The order_number is a degenerate dimension
-- It has no descriptive attributes worth a separate table
CREATE TABLE fct_order_lines (
    order_number  VARCHAR(20),  -- Degenerate dimension
    line_number   INT,
    product_key   INT,
    quantity      INT,
    amount        DECIMAL(10,2)
);

Use degenerate dimensions when: the attribute is a grouping key with no additional attributes, like transaction IDs, invoice numbers, or receipt numbers.

Role-playing dimensions

A single physical dimension table used multiple times in the same fact table, each time playing a different role.

-- dim_date is used three times with different roles
SELECT
    f.order_amount,
    order_date.full_date     AS order_date,
    order_date.month_name    AS order_month,
    ship_date.full_date      AS ship_date,
    delivery_date.full_date  AS delivery_date
FROM fct_order_fulfillment f
JOIN dim_date AS order_date    ON f.order_date_key = order_date.date_key
JOIN dim_date AS ship_date     ON f.ship_date_key = ship_date.date_key
JOIN dim_date AS delivery_date ON f.delivery_date_key = delivery_date.date_key
WHERE order_date.year = 2024
  AND ship_date.day_of_week = 'Monday';

In dbt, you implement role-playing dimensions with views:

-- models/dims/dim_order_date.sql
SELECT * FROM {{ ref('dim_date') }}

-- models/dims/dim_ship_date.sql
SELECT * FROM {{ ref('dim_date') }}

Outrigger dimensions

A dimension table that hangs off another dimension (not directly off a fact table). Use sparingly — they add JOIN complexity.

-- dim_product has a foreign key to dim_brand
-- dim_brand is an outrigger dimension
CREATE TABLE dim_brand (
    brand_key     INT PRIMARY KEY,
    brand_name    VARCHAR(100),
    parent_company VARCHAR(100),
    brand_tier    VARCHAR(20)
);

CREATE TABLE dim_product (
    product_key   INT PRIMARY KEY,
    product_name  VARCHAR(200),
    category      VARCHAR(50),
    brand_key     INT REFERENCES dim_brand(brand_key)  -- Outrigger
);

The bus matrix

The bus matrix is Kimball’s planning tool for designing an enterprise data warehouse. It maps business processes (rows) to dimensions (columns), showing where dimensions are shared (conformed).

                    │ Date │ Customer │ Product │ Store │ Employee │ Promotion │
────────────────────┼──────┼──────────┼─────────┼───────┼──────────┼───────────┤
Retail Sales        │  X   │    X     │    X    │   X   │    X     │     X     │
Inventory           │  X   │          │    X    │   X   │          │           │
Returns             │  X   │    X     │    X    │   X   │    X     │           │
Customer Support    │  X   │    X     │    X    │       │    X     │           │
Marketing Campaigns │  X   │    X     │         │       │          │     X     │

Each X indicates that a business process uses that dimension. Columns with multiple X’s are conformed dimensions — they must have identical definitions everywhere.

The bus matrix prevents dimensional silos. Without it, the marketing team might define “customer” differently from the sales team, making cross-functional analysis impossible.

How to build one:

1. List all business processes (these become fact tables).
2. For each process, identify all relevant dimensions.
3. Look for shared dimensions — these become conformed dimensions.
4. Prioritize: build the most-shared dimensions first.

Grain — the most important decision

Grain defines what a single row in a fact table represents. Get it wrong, and everything downstream is incorrect.

The rule: Declare the grain before adding any facts or dimensions. Every fact and dimension must be consistent with the declared grain.

Examples of grain declarations:

• “One row per order line item” (transactional)
• “One row per product per warehouse per day” (periodic snapshot)
• “One row per order from placement to delivery” (accumulating snapshot)

A grain mistake in action

Suppose you declare the grain as “one row per order” but your source data has multiple payment methods per order:

Order 101: Visa $50, PayPal $30 → Total should be $80

If you join orders to payments without handling the grain, you get:

Order 101: $80 (from Visa row) + $80 (from PayPal row) = $160  ← WRONG

Revenue just doubled. This is the most common dimensional modeling mistake, and it always comes down to violating the grain. The fix: either make the grain “one row per order per payment method,” or aggregate payments before joining.

Bridge tables for many-to-many

When a fact has a many-to-many relationship with a dimension, you need a bridge table.

Example: A patient visit can have multiple diagnoses. A diagnosis applies to multiple patient visits.

-- Bridge table resolves M:N between visits and diagnoses
CREATE TABLE bridge_visit_diagnosis (
    visit_key       INT,
    diagnosis_key   INT,
    weighting_factor DECIMAL(3,2),  -- For proportional allocation
    PRIMARY KEY (visit_key, diagnosis_key)
);

-- Usage: join through the bridge
SELECT
    d.diagnosis_name,
    SUM(f.visit_cost * b.weighting_factor) AS allocated_cost
FROM fct_patient_visits f
JOIN bridge_visit_diagnosis b ON f.visit_key = b.visit_key
JOIN dim_diagnosis d ON b.diagnosis_key = d.diagnosis_key
GROUP BY d.diagnosis_name;

The weighting_factor is crucial — it prevents double-counting. If a visit with $1000 cost has two diagnoses, each gets $500 (weight = 0.5) instead of both getting $1000.

Mini-dimensions for rapidly changing attributes

Some dimension attributes change frequently — customer income bracket, credit score, age band. Tracking these with SCD Type 2 creates explosion in dimension table size.

Mini-dimensions extract the volatile attributes into a separate, small table:

-- Mini-dimension: demographics profile
CREATE TABLE dim_customer_demographics (
    demo_key       INT PRIMARY KEY,
    age_band       VARCHAR(20),    -- '18-24', '25-34', etc.
    income_band    VARCHAR(20),    -- 'Low', 'Medium', 'High'
    credit_score_band VARCHAR(20)  -- 'Poor', 'Fair', 'Good', 'Excellent'
);

-- Fact table references both the main dimension and mini-dimension
CREATE TABLE fct_purchases (
    customer_key      INT REFERENCES dim_customer(customer_key),
    customer_demo_key INT REFERENCES dim_customer_demographics(demo_key),
    product_key       INT,
    purchase_amount   DECIMAL(10,2)
);

The mini-dimension has a limited number of rows (age bands times income bands times credit score bands). The fact table gets the current demographic profile key at the time of the transaction.

Complete example: e-commerce data warehouse

Let us model a complete e-commerce business using everything covered above.

Bus matrix

                │ Date │ Customer │ Product │ Warehouse │ Promotion │ Channel │
────────────────┼──────┼──────────┼─────────┼───────────┼───────────┼─────────┤
Orders          │  X   │    X     │    X    │     X     │     X     │    X    │
Inventory       │  X   │          │    X    │     X     │           │         │
Returns         │  X   │    X     │    X    │     X     │           │    X    │
Page Views      │  X   │    X     │    X    │           │           │    X    │

Dimension tables

-- dim_date: role-playing for order, ship, delivery dates
-- dim_customer: conformed across all processes
-- dim_product: conformed, with outrigger to dim_brand

-- dbt model: models/dims/dim_product.sql
WITH source AS (
    SELECT * FROM {{ ref('stg_products') }}
),

brands AS (
    SELECT * FROM {{ ref('stg_brands') }}
),

final AS (
    SELECT
        {{ dbt_utils.generate_surrogate_key(['s.product_id']) }} AS product_key,
        s.product_id,
        s.product_name,
        s.category,
        s.subcategory,
        b.brand_name,
        b.brand_tier,
        s.unit_cost,
        s.weight_kg,
        s.is_active,
        s.created_at
    FROM source s
    LEFT JOIN brands b ON s.brand_id = b.brand_id
)

SELECT * FROM final

Fact tables

-- models/facts/fct_order_lines.sql
-- Grain: one row per order line item

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),

order_items AS (
    SELECT * FROM {{ ref('stg_order_items') }}
),

final AS (
    SELECT
        -- Dimension keys
        {{ dbt_utils.generate_surrogate_key(['o.order_date']) }} AS date_key,
        {{ dbt_utils.generate_surrogate_key(['o.customer_id']) }} AS customer_key,
        {{ dbt_utils.generate_surrogate_key(['oi.product_id']) }} AS product_key,
        {{ dbt_utils.generate_surrogate_key(['o.warehouse_id']) }} AS warehouse_key,
        COALESCE(
            {{ dbt_utils.generate_surrogate_key(['o.promotion_id']) }},
            {{ dbt_utils.generate_surrogate_key(["'no_promotion'"]) }}
        ) AS promotion_key,

        -- Degenerate dimensions
        o.order_number,
        oi.line_number,

        -- Junk dimension key
        {{ dbt_utils.generate_surrogate_key([
            'o.is_gift', 'o.is_expedited', 'o.payment_type'
        ]) }} AS order_flag_key,

        -- Measures
        oi.quantity,
        oi.unit_price,
        oi.discount_amount,
        (oi.quantity * oi.unit_price) - oi.discount_amount AS net_amount,
        ((oi.quantity * oi.unit_price) - oi.discount_amount) * 0.08 AS tax_amount,
        ((oi.quantity * oi.unit_price) - oi.discount_amount) * 1.08 AS gross_amount
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'cancelled'
)

SELECT * FROM final

This model brings together transactional facts, conformed dimensions, degenerate dimensions, and a junk dimension — all the patterns discussed above, working together in a real-world scenario.

Next steps

• Data Modeling for Analytics — star and snowflake schema foundations.
• Data Warehouse Concepts — the warehouse architectures that host dimensional models.
• dbt Fundamentals — implement dimensional models in dbt.
• Data Observability — monitor your dimensional models in production.