How to Design a Kimball Star Schema for Analytics

Why dimensional design, not just more SQL

When every dashboard joins the same six normalized tables, queries are slow and each analyst re-derives the same joins slightly differently. A Kimball star schema solves this at the modeling layer: you shape the data once into a central fact table of measurements surrounded by dimension tables of context. Reporting then becomes a few predictable joins radiating out from the fact - the "star" shape.

Designing one is a deliberate four-step sequence, not ad-hoc SQL. Get the order right and the integrity tests fall out for free.

Step 1 - declare the grain

The grain is the single question: what does one row in the fact table mean? Here the grain is one row per order line. Every measure and foreign key must be true at that grain - never mix order-level and line-level rows in one fact table. Declaring the grain first drives every later decision.

Step 2 - design the dimensions with surrogate keys

Each dimension gets a surrogate key - a synthetic id independent of the source system's natural key. md5(natural_key::text) is the classic deterministic surrogate: collision-free at this scale and stable across rebuilds. Keep the natural key as an attribute for lineage.

-- models/marts/dim_customers.sql
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') }}

A date dimension is generated, not sourced - one row per day beats raw timestamps for time analysis. Use generate_series and a YYYYMMDD integer surrogate:

-- models/marts/dim_date.sql
WITH dates AS (
    SELECT generate_series('2024-01-01'::date,
                           '2024-12-31'::date,
                           '1 day'::interval)::date AS d
)
SELECT
    to_char(d, 'YYYYMMDD')::int AS date_sk,
    d                           AS full_date,
    extract(year  FROM d)::int  AS year,
    extract(month FROM d)::int  AS month,
    extract(dow   FROM d)::int  AS day_of_week
FROM dates

Step 3 - build the fact at the declared grain

The fact table holds foreign keys to each dimension plus numeric measures. Derive each FK the same way the dimension derived its surrogate key so they match exactly:

-- models/marts/fct_orders.sql (grain: one row per order line)
SELECT
    md5(ol.id::text)                        AS order_sk,
    md5(ol.customer_id::text)               AS customer_sk,   -- FK
    md5(ol.product_id::text)                AS product_sk,    -- FK
    to_char(ol.order_date, 'YYYYMMDD')::int AS date_sk,       -- FK
    ol.quantity,
    ol.quantity * p.price                   AS gross_amount,
    ol.discount                             AS discount_amount,
    ol.quantity * p.price - ol.discount     AS net_amount
FROM {{ source('raw', 'order_lines') }} ol
JOIN {{ source('raw', 'products') }} p ON p.id = ol.product_id

Step 4 - prove integrity with relationships tests

A star schema is only correct if every FK in the fact resolves to a real dimension row. dbt's built-in relationships test is exactly that referential-integrity check - it fails the build on any orphan key. Declare one per FK in schema.yml, plus unique + not_null on every surrogate key and a grain description on each model:

version: 2
models:
  - name: fct_orders
    description: "Order line fact. Grain: one row per order line."
    columns:
      - name: order_sk
        tests: [not_null, unique]
      - name: customer_sk
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_sk

Then run models and tests together in one command:

cd dbt_project
dbt build --profiles-dir ..

A green dbt build means the schema materialized AND every referential-integrity test passed - no orphan FKs. That exit-0 is the proof the design is sound.