How to Build a Kimball Star Schema

Why a star schema

Reporting against raw, normalized tables means every dashboard joins many tables and runs slow. A star schema reshapes the data for reads: a central fact table of measurements, surrounded by dimension tables of descriptive context. Queries become a few predictable joins from the fact out to the dimensions - hence "star."

The two table types

• Fact table - the events/measurements, at one consistent grain (e.g. one row per order line). Holds foreign keys to dimensions + numeric measures (quantity, amount).
• Dimension tables - the "who / what / when / where": customers, products, dates. One row per entity, with descriptive attributes you filter and group by.

Surrogate keys

Dimensions get a surrogate key - a synthetic id, independent of the source system's natural key. It decouples the warehouse from source changes and supports slowly-changing dimensions later.

-- dim_customers
SELECT
    md5(id::text) AS customer_sk,   -- surrogate key
    id            AS customer_id,   -- natural key, kept for lineage
    name          AS customer_name,
    email
FROM {{ source('raw', 'customers') }}

The fact table

Pick the grain first, then join each dimension to get its surrogate key and keep the measures:

-- fct_order_lines (grain: one row per order line)
SELECT
    md5(ol.id::text)        AS order_line_sk,
    c.customer_sk,                       -- FK to dim_customers
    p.product_sk,                        -- FK to dim_products
    d.date_sk,                           -- FK to dim_date
    ol.quantity,                         -- measure
    ol.quantity * p.price   AS line_total  -- measure
FROM {{ source('raw', 'order_lines') }} ol
JOIN dim_customers c ON c.customer_id = ol.customer_id
JOIN dim_products  p ON p.product_id  = ol.product_id
JOIN dim_date      d ON d.date_day    = ol.ordered_on::date

The rules that keep it clean

• One grain per fact table - never mix order-level and line-level rows.
• Facts hold keys + numbers; dimensions hold text you filter/group by.
• A date dimension (one row per day, with month/quarter/weekday) beats raw timestamps for time analysis.
• Star, not snowflake - keep dimensions flat (denormalized) for fast, simple joins unless you have a strong reason to normalize.