Kimball Star Schema: Dimensional Modeling Explained

1. Kimball vs Star Schema: What Is the Difference?

There is no choice to make here, and that is the whole answer. Kimball is a design method. Dimensional modeling is the name of the technique at the center of that method. A star schema is the physical table layout the technique produces. You do not pick one of the three: you follow the method, and a star schema is what lands in the warehouse. The same goes for "star schema vs dimensional model": the star is the default physical form of a dimensional model, and a snowflake or a wide table are the two other forms the same logical model can take.

2. What Is Dimensional Modeling in the Kimball Method?

Dimensional modeling is a technique for designing data warehouses that prioritizes query simplicity and performance over storage efficiency. Created by Ralph Kimball in the 1990s and set out in The Data Warehouse Toolkit, it remains the default for analytics because it models data the way business users think about it. In a modern lakehouse, this is what your Gold layer looks like: dimensional models sit downstream of the cleaned Silver tables in a medallion architecture.

The core principle: separate the "what happened" from the "context"

3. Fact Table vs Dimension Table in a Star Schema

The two building blocks of every star schema. Get this split right and everything else follows.

-- 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
);

Additive, semi-additive, non-additive facts

Not every number in a fact table can be summed across every dimension. Kimball sorts measures into three kinds, and the distinction is what stops a dashboard from reporting a number nobody can reproduce.

4. How to Design a Star Schema: Kimball's Four Steps

A star schema places a fact table at the center, surrounded by dimension tables. The name comes from the shape: fact in the middle, dimensions radiating outward like points of a star. Kimball gets there with four decisions taken in a fixed order, and the order matters: each step narrows the next one.

Where the star sits inside the warehouse, and how many of them you end up with, is a separate decision from how each one is shaped; the data warehouse design guide covers the layering and naming conventions around it. If you want to see the shape before you write the DDL, the star schema diagram generator takes a list of facts and dimensions and lays out the join paths.

The four steps on a worked example

To sketch your own model, the star schema generator draws a fact-and-dimensions diagram from a one-line description that you can then edit table by table.

5. Kimball Star Schema vs Snowflake Schema

A snowflake schema is the same dimensional model with its dimensions normalized. Instead of one dim_product carrying category and department as columns, you get dim_product joined to dim_category joined to dim_department. Nothing about the facts changes. What changes is the number of joins an analyst has to write and the number the engine has to run.

-- Star: revenue by department, one join
SELECT p.department, SUM(f.line_total)
FROM fct_order_lines f
JOIN dim_products p ON f.product_sk = p.product_sk
GROUP BY p.department;

-- Snowflake: same report, three joins
SELECT dp.department_name, SUM(f.line_total)
FROM fct_order_lines f
JOIN dim_products    p  ON f.product_sk  = p.product_sk
JOIN dim_categories  c  ON p.category_sk = c.category_sk
JOIN dim_departments dp ON c.department_sk = dp.department_sk
GROUP BY dp.department_name;

The storage argument for snowflaking rarely survives arithmetic. A retailer with 300,000 products and three billion order lines keeps roughly one dimension row per ten thousand fact rows, so normalizing the category text out of the product dimension saves a fraction of a percent of total warehouse storage. On a columnar engine it saves even less, because dictionary encoding already stores each repeated category string once per block. The join you added, on the other hand, is paid on every query an analyst runs.

Two other cases are usually accepted. An outrigger, where one dimension references another small dimension (a customer dimension pointing at a date dimension for the account opening date), keeps the calendar attributes in one place instead of copying twenty columns. And a dimension that arrives already normalized from an MDM system is sometimes left that way and exposed to analysts through a flattened view, which gives the star experience over snowflaked storage. To see either layout drawn out, the snowflake schema diagram generator renders the normalized branches next to the fact table.

6. Types of Dimensions in the Kimball Method

Not all dimensions are created equal. Understanding these patterns helps you model correctly from the start.

7. Conformed Dimensions and the Kimball Bus Matrix

A dimension is conformed when several fact tables use the same dimension table, or dimension tables whose keys and attribute values mean exactly the same thing. That is what turns a pile of separate stars into one warehouse: if fct_orders and fct_support_tickets both join dim_customer, then "Enterprise segment" means the same customers on both sides and the two processes can be compared. Conformed dimensions are the Kimball answer to the question that kills most warehouses, which is why two teams report two different numbers for the same word.

The bus matrix: plan the whole warehouse on one page

Before a single table exists, draw a grid. Rows are business processes, each of which becomes one fact table. Columns are the dimensions. Mark every cell where a process uses a dimension. The columns with several marks are the dimensions that must conform, and they are the ones to build first, because everything else depends on them. The rows give you a delivery order: ship one star, then the next, each reusing dimensions that already exist.

Read the matrix above and the build order writes itself: date, customer, and product are used by nearly every process, so they are conformed dimensions and get built first, with a single owner and a single definition. Store and promotion are used by fewer processes and can wait.

Multi fact star schemas: drill across, never join fact to fact

A warehouse with several fact tables sharing conformed dimensions is sometimes called a fact constellation or galaxy schema. It is normal and expected. The rule that comes with it is absolute: never join two fact tables directly. Their grains differ, so the join multiplies rows and every measure on both sides inflates. Kimball's technique is drilling across: query each star separately, group both results by the same conformed dimension attributes, then join the two summarized result sets on those attributes.

-- WRONG: joining two fact tables multiplies rows
-- SELECT SUM(o.line_total), SUM(r.refund_amount)
-- FROM fct_order_lines o JOIN fct_returns r ON o.product_sk = r.product_sk

-- RIGHT: aggregate each star, then join on conformed attributes
WITH orders AS (
    SELECT d.year_num, d.month_num, p.category,
           SUM(f.line_total) AS revenue
    FROM fct_order_lines f
    JOIN dim_date     d ON f.date_sk    = d.date_sk
    JOIN dim_products p ON f.product_sk = p.product_sk
    GROUP BY 1, 2, 3
),
returns AS (
    SELECT d.year_num, d.month_num, p.category,
           SUM(f.refund_amount) AS refunds
    FROM fct_returns f
    JOIN dim_date     d ON f.date_sk    = d.date_sk
    JOIN dim_products p ON f.product_sk = p.product_sk
    GROUP BY 1, 2, 3
)
SELECT COALESCE(o.year_num, r.year_num)   AS year_num,
       COALESCE(o.month_num, r.month_num) AS month_num,
       COALESCE(o.category, r.category)   AS category,
       COALESCE(o.revenue, 0)             AS revenue,
       COALESCE(r.refunds, 0)             AS refunds
FROM orders o
FULL OUTER JOIN returns r
  ON  o.year_num  = r.year_num
  AND o.month_num = r.month_num
  AND o.category  = r.category;

8. The Kimball Date Dimension

Every dimensional model has one, and it is the dimension people get wrong most often. The point is not to store the date, the fact table already has a key for that. The point is to store everything a report might want to group or filter by that SQL cannot derive from a date alone: fiscal periods that do not follow the calendar, company holidays, trading days, the name of the week in the language the report is written in.

The date dimension is also the classic role-playing dimension: an order fact joins it three or four times as order_date, ship_date, delivery_date. Build one physical table and expose one view per role, so each role can carry its own column names in the BI tool.

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;

9. Slowly Changing Dimensions (SCD Types 0 to 6)

Dimension attributes change over time. A customer moves, a product gets recategorized, an employee changes departments. How you handle these changes is critical for historical accuracy, and Kimball numbers the options so a team can agree on one per attribute rather than per table.

-- SCD Type 2: Customer changes segment from 'SMB' to 'Enterprise'
-- BEFORE: 1 row
customer_sk | customer_id | segment    | effective_date | expiry_date | is_current
1           | C001        | SMB        | 2023-01-01     | 9999-12-31  | TRUE

-- AFTER: 2 rows (old row expired, new row added)
customer_sk | customer_id | segment    | effective_date | expiry_date | is_current
1           | C001        | SMB        | 2023-01-01     | 2024-06-15  | FALSE
2           | C001        | Enterprise | 2024-06-15     | 9999-12-31  | TRUE

-- Historical query: What segment was customer in when they ordered?
SELECT
    o.order_id,
    c.segment as segment_at_order_time
FROM fct_orders o
JOIN dim_customers c ON o.customer_sk = c.customer_sk
-- customer_sk in fact table points to correct historical version

10. Types of Fact Tables

Different business processes call for different fact table designs. Choose the right type based on the nature of your events.

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

11. Star Schema vs One Big Table (OBT)

The modern objection to Kimball is that columnar warehouses removed the reason for the star: since the engine reads only the columns a query touches, why not flatten the fact and all its dimension attributes into one wide table and skip the joins. That is the one big table pattern, and it is genuinely faster for a single dashboard. The tradeoffs are not about speed, they are about what happens on the second and third dashboard.

The workable position is not either-or. Keep the star as the model of record, where the grain is declared, the dimensions conform, and history is handled once. Materialize wide tables downstream from it, one per dashboard or per BI dataset, and treat them as a cache: derived, disposable, rebuilt from the star. That way the wide table gets its speed and the star keeps the definitions honest.

12. Best Practices Checklist

13. Frequently Asked Questions