Star schema: how to design one with Kimball's four steps and SQL

What is in a star schema?

Kimball Group describes star schemas as dimensional structures in a relational database, with fact tables linked to dimension tables by primary and foreign keys. Microsoft's Power BI guidance explains why BI tools favour the shape. Each report visual sends a query that filters, groups and summarizes, so a model needs tables for filtering and grouping (dimensions) and tables for summarizing (facts). In a one-to-many relationship the "one" side is always a dimension and the "many" side a fact. Dimension tables have relatively few rows, while fact tables can be large and keep growing.

In the example later in this guide, fact_sales has one row per order line, with a key for each of date, store, product and customer, plus a quantity and a net amount. dim_product has one row per product, with its category and subcategory flattened into the same row. A query for sales by category and region joins the fact table to two dimensions and groups by their attributes. Microsoft's Fabric guidance calls a star schema a prerequisite for enterprise Power BI semantic models.

How do you design a star schema in four steps?

Kimball Group lists four decisions, taken in order: select the business process, declare the grain, identify the dimensions and identify the facts. The order is the point. The grain declaration is a binding contract on the design, because every dimension and fact you add must be consistent with it.

Three habits keep the steps honest:

• Start at atomic grain. That is the lowest level at which the process captures data. Roll-ups presuppose the questions the business asks today, so add them later as aggregates that Kimball says should behave like indexes.
• Never mix grains. Kimball Group's ten essential rules warn that facts at several levels of detail in one table invite overstated or otherwise wrong results.
• Listen for "by". In Microsoft's Fabric guidance, a request for sales by salesperson, by month and by product category names three dimensions.

Kimball also wants the model to unfold through interactive workshops with business representatives, not to be designed in isolation.

What does a retail star schema look like in SQL?

The example is a retailer's sales with invented rows, at a grain of one row per order line. Every statement below ran in order on PostgreSQL 18.6 on October 9, 2026. Identity columns and OVERRIDING SYSTEM VALUE are documented for version 18, and generate_series and to_char are PostgreSQL functions; other engines spell these differently, so adapt the DDL.

CREATE TABLE dim_date (
    date_key   integer PRIMARY KEY,               -- 20261009
    full_date  date     NOT NULL UNIQUE,
    year       smallint NOT NULL,
    quarter    smallint NOT NULL,
    month_name text     NOT NULL,
    day_name   text     NOT NULL,
    is_weekend boolean  NOT NULL
);

CREATE TABLE dim_store (
    store_key  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    store_id   text NOT NULL UNIQUE,              -- natural key from the till system
    store_name text NOT NULL,
    region     text NOT NULL
);

CREATE TABLE dim_product (
    product_key  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku          text NOT NULL UNIQUE,
    product_name text NOT NULL,
    category     text NOT NULL,                   -- hierarchy flattened into the row
    subcategory  text NOT NULL
);

CREATE TABLE dim_customer (
    customer_key integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  text    NOT NULL,                -- natural key; repeats across versions
    full_name    text    NOT NULL,                -- Type 1: overwritten
    segment      text    NOT NULL,                -- Type 2: a new row when it changes
    valid_from   date    NOT NULL,
    valid_to     date    NOT NULL DEFAULT '9999-12-31',
    is_current   boolean NOT NULL DEFAULT true
);
CREATE UNIQUE INDEX one_current_row ON dim_customer (customer_id) WHERE is_current;

CREATE TABLE fact_sales (
    date_key     integer       NOT NULL REFERENCES dim_date,
    store_key    integer       NOT NULL REFERENCES dim_store,
    product_key  integer       NOT NULL REFERENCES dim_product,
    customer_key integer       NOT NULL REFERENCES dim_customer,
    order_number text          NOT NULL,          -- degenerate dimension
    line_number  smallint      NOT NULL,
    quantity     integer       NOT NULL,
    unit_price   numeric(10,2) NOT NULL,          -- not additive
    net_amount   numeric(12,2) NOT NULL,
    PRIMARY KEY (order_number, line_number)       -- the grain, enforced
);

The date dimension is generated once, not extracted. generate_series walks a timestamp range one day at a time, and the key is the date written as an integer:

INSERT INTO dim_date
SELECT to_char(d, 'YYYYMMDD')::integer, d::date, extract(year FROM d), extract(quarter FROM d),
       to_char(d, 'FMMonth'), to_char(d, 'FMDay'), extract(isodow FROM d) IN (6, 7)
FROM   generate_series(timestamp '2026-01-01', timestamp '2027-12-31', interval '1 day') AS g(d);

Next come the dimension rows, each dimension with an unknown member (explained below), and a few staged sales. The load looks up every surrogate key; the customer lookup is time-based, which the section on slowly changing dimensions explains.

INSERT INTO dim_store (store_id, store_name, region) VALUES
  ('S01', 'Queen West', 'East'), ('S02', 'Kitsilano', 'West');
INSERT INTO dim_product (sku, product_name, category, subcategory) VALUES
  ('SKU-100', 'Whole bean coffee 1 kg', 'Coffee', 'Beans'),
  ('SKU-200', 'Green tea 50 g',         'Tea',    'Loose leaf'),
  ('SKU-300', 'Tea infuser',            'Tea',    'Accessories');
INSERT INTO dim_customer (customer_id, full_name, segment, valid_from) VALUES
  ('C-1042', 'Customer 1042', 'Consumer', '2026-01-01'),
  ('C-2077', 'Customer 2077', 'Consumer', '2026-01-01');

-- Unknown members: key -1, allowed because the identity is GENERATED ALWAYS
INSERT INTO dim_store    OVERRIDING SYSTEM VALUE VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown');
INSERT INTO dim_product  OVERRIDING SYSTEM VALUE VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown', 'Unknown');
INSERT INTO dim_customer OVERRIDING SYSTEM VALUE
  VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown', '1900-01-01', DEFAULT, DEFAULT);

CREATE TEMP TABLE stg_sales AS
SELECT * FROM (VALUES
  ('ORD-9001', 1, DATE '2026-09-28', 'S01', 'SKU-100', 'C-1042', 3, 18.50),
  ('ORD-9001', 2, DATE '2026-09-28', 'S01', 'SKU-200', 'C-1042', 1,  9.25),
  ('ORD-9001', 3, DATE '2026-09-28', 'S01', 'SKU-300', 'C-1042', 2,  7.00),
  ('ORD-9002', 1, DATE '2026-10-09', 'S01', 'SKU-100', NULL,     1, 18.50),
  ('ORD-9003', 1, DATE '2026-10-09', 'S02', 'SKU-200', 'C-2077', 5,  9.25)
) AS v(order_number, line_number, sale_date, store_id, sku, customer_id, quantity, unit_price);

INSERT INTO fact_sales
SELECT to_char(s.sale_date, 'YYYYMMDD')::integer,
       COALESCE(st.store_key, -1), COALESCE(p.product_key, -1), COALESCE(c.customer_key, -1),
       s.order_number, s.line_number, s.quantity, s.unit_price, s.quantity * s.unit_price
FROM   stg_sales s
LEFT JOIN dim_store    st ON st.store_id   = s.store_id
LEFT JOIN dim_product  p  ON p.sku         = s.sku
LEFT JOIN dim_customer c  ON c.customer_id = s.customer_id
                         AND s.sale_date >= c.valid_from AND s.sale_date < c.valid_to;

A typical star query joins the fact table to the dimensions it needs and groups by their attributes. Average price is SUM(net_amount) / SUM(quantity), not AVG(unit_price): the table stores the additive parts and the division happens after summing.

SELECT d.year, d.quarter, p.category, s.region,
       SUM(f.quantity)   AS units,
       SUM(f.net_amount) AS net_sales,
       ROUND(SUM(f.net_amount) / NULLIF(SUM(f.quantity), 0), 2) AS avg_price
FROM   fact_sales f
JOIN   dim_date    d ON d.date_key    = f.date_key
JOIN   dim_product p ON p.product_key = f.product_key
JOIN   dim_store   s ON s.store_key   = f.store_key
WHERE  d.year = 2026
GROUP  BY d.year, d.quarter, p.category, s.region
ORDER  BY d.quarter, p.category, s.region;

PostgreSQL enforces every constraint above: in the lab it rejected a duplicate order line, a NULL customer key and a key with no dimension row. Many analytical engines let you declare key constraints, with their own syntax, but enforce none of them, as of October 2026:

Which fact table type fits, and what can you add up?

Kimball Group's technique list describes four kinds of fact table by what one row represents:

Factless tables can also show what did not happen: subtract the activity table from a coverage table of everything that could have happened. The numeric columns of the other types fall into three groups, and the group decides how a report may combine them:

• Additive facts sum across every dimension. quantity and net_amount are additive.
• Semi-additive facts sum across some dimensions but not all. Balances are the usual case: they add across every dimension except time, so a month-end report takes the closing balance, not the sum of the daily ones.
• Non-additive facts, such as ratios and unit prices, cannot be summed. Store the additive components and divide after summing, as the average price above does. Microsoft's guidance says the same of a unit price: never sum it.

How do dimensions handle keys, dates and unknown values?

• Surrogate keys. Give each dimension a meaningless integer primary key that the warehouse assigns. The natural key cannot do the job: tracking change puts several rows on one natural key, and keys from several source systems can be incompatible or poorly administered. The date dimension is the exception. Some teams hash instead of counting; dbt-utils' generate_surrogate_key builds a hashed key from the fields you name. A hash is reproducible after a full rebuild, while an identity column can hand out different numbers and orphan the keys stored in facts. Either works if every fact load goes through the same lookup.
• The date dimension. Almost every fact table needs one. Kimball's example is Easter: look it up in the calendar dimension, never compute it in SQL. The key can be the date as an integer, which helps partitioning, but filter and group on the attributes, not on the key. Time of day goes in a separate time dimension, and the date table needs a row for unknown dates. Add the attributes people group by: fiscal periods, week numbers, month names and holiday flags.
• Role-playing dimensions. An order has an order date, a ship date and a delivery date, and all three point at the one physical date dimension. Give the fact table a foreign key per role and expose each role as a view with its own column names, for example CREATE VIEW dim_ship_date AS SELECT date_key AS ship_date_key, full_date AS ship_date FROM dim_date;.
• Degenerate and junk dimensions. order_number identifies the order but describes nothing else, so it stays in fact_sales as a degenerate dimension. Scattered low-cardinality flags, such as gift-wrapped or payment type, can share one junk dimension that holds only the combinations that occur.
• Unknown members. A NULL foreign key in a fact table breaks referential integrity, so point those rows at a default dimension row instead. Microsoft's example codes are 0 for missing, -1 for unknown, -2 for not applicable and -3 for error. In the example a sale with no customer record points at customer_key = -1. Text attributes follow the same rule: "Unknown" rather than NULL, because databases handle grouping on NULL inconsistently.

How do conformed dimensions connect several stars?

Dimensions conform when their attributes have the same names and contents wherever they appear. Kimball Group calls this the essence of integration in an enterprise warehouse, because two fact tables can then share row headers in one report. The planning tool is the bus matrix: rows are business processes, columns are dimensions, and a mark shows which dimension a process uses. Scan a row to test a process and a column to see where a dimension must conform. This one is illustrative:

Implement one row at a time, Kimball advises. Conformance has a cost: someone must own the definition of a product category before two fact tables can share it.

Conformed dimensions also make drilling across work. Two fact tables must never be joined directly across their foreign keys, because the size of the result cannot be controlled. Query each fact table separately and merge the answers on a conformed attribute. The example adds a returns fact table at the product grain:

CREATE TABLE fact_returns (
    date_key      integer       NOT NULL REFERENCES dim_date,
    product_key   integer       NOT NULL REFERENCES dim_product,
    order_number  text          NOT NULL,
    line_number   smallint      NOT NULL,
    refund_amount numeric(12,2) NOT NULL,
    PRIMARY KEY (order_number, line_number)
);
INSERT INTO fact_returns VALUES (20261015, 1, 'ORD-9001', 1, 55.50), (20261015, 2, 'ORD-9003', 1, 46.25);

WITH sales AS (
    SELECT p.category, SUM(f.net_amount) AS net_sales
    FROM fact_sales f JOIN dim_product p USING (product_key) GROUP BY p.category),
returns AS (
    SELECT p.category, SUM(f.refund_amount) AS refunds
    FROM fact_returns f JOIN dim_product p USING (product_key) GROUP BY p.category)
SELECT COALESCE(s.category, r.category) AS category, s.net_sales, r.refunds
FROM   sales s FULL OUTER JOIN returns r ON r.category = s.category
ORDER  BY 1;

Joining the two fact tables directly on product_key instead reported 111.00 of coffee refunds against a true 55.50 and 92.50 of tea refunds against 46.25, and it left out 14.00 of tea sales because the tea infuser was never returned.

How do slowly changing dimensions keep history?

Attributes change, and each attribute needs a policy. Kimball Group's techniques define the types, and Design Tip #152 adds the hybrids:

Types 5 and 7 are hybrids with the same purpose as Type 6. Choose per attribute, not per table: in the example, full_name is Type 1 and segment is Type 2.

On October 10, 2026 customer 1042 moves from the Consumer segment to the Business segment and is renamed. The load closes the current row, opens a new one and overwrites the name on every version. Run it in one transaction; the date stands for the batch date.

CREATE TEMP TABLE stg_customer AS
SELECT * FROM (VALUES
  ('C-1042', 'Customer 1042 Ltd', 'Business'),
  ('C-2077', 'Customer 2077',     'Consumer')
) AS v(customer_id, full_name, segment);

BEGIN;
-- Type 1: overwrite the name on every version
UPDATE dim_customer d SET full_name = s.full_name
FROM   stg_customer s
WHERE  d.customer_id = s.customer_id AND d.full_name IS DISTINCT FROM s.full_name;

-- Type 2, step 1: close the current row when the tracked attribute changed
UPDATE dim_customer d SET valid_to = DATE '2026-10-10', is_current = false
FROM   stg_customer s
WHERE  d.customer_id = s.customer_id AND d.is_current
  AND  d.segment IS DISTINCT FROM s.segment;

-- Type 2, step 2: open a new current row for every customer that has none
INSERT INTO dim_customer (customer_id, full_name, segment, valid_from)
SELECT s.customer_id, s.full_name, s.segment, DATE '2026-10-10'
FROM   stg_customer s
WHERE  NOT EXISTS (SELECT 1 FROM dim_customer d
                   WHERE d.customer_id = s.customer_id AND d.is_current);
COMMIT;

-- A sale on October 12: stage it, then rerun the INSERT INTO fact_sales statement above
TRUNCATE stg_sales;
INSERT INTO stg_sales VALUES ('ORD-9004', 1, DATE '2026-10-12', 'S01', 'SKU-100', 'C-1042', 10, 17.00);

A second run on the same staged rows changed nothing in the lab, so the load is safe to repeat. The dimension now holds two versions of customer 1042, and the surrogate key does the work the natural key cannot:

Version ranges are half-open: valid_from is inclusive and valid_to is the next version's valid_from, which is why the fact load tests sale_date >= valid_from AND sale_date < valid_to. The October 12 sale resolved to key 3, while the September sales keep key 1. Grouping by the segment on each fact row gives the as-was view; joining to the customer's current row gives the as-is view:

-- as it was: the version in force on the sale date
SELECT c.segment, SUM(f.net_amount) AS net_sales
FROM   fact_sales f JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP  BY c.segment;

-- as it is now: the current version of the same customer
SELECT cur.segment, SUM(f.net_amount) AS net_sales
FROM   fact_sales f
JOIN   dim_customer c   ON c.customer_key = f.customer_key
JOIN   dim_customer cur ON cur.customer_id = c.customer_id AND cur.is_current
GROUP  BY cur.segment;

The as-was view reports 125.00 for Consumer and 170.00 for Business. The as-is view reports 46.25 and 248.75, because customer 1042's September sales of 78.75 move to Business with the customer. Each view also shows 18.50 for the unknown customer. Both are right. They answer different questions, and a report must say which one it shows.

Three cautions apply:

• Change detection is a batch job. dbt snapshots implement Type 2 over mutable source tables, but dbt calls them a batch approach to change capture and intends them to run between hourly and daily. A change made and reverted between two runs is never recorded.
• A late arrival means a restatement. When a retroactive change reaches a Type 2 attribute, Kimball says to insert a new row and restate the affected fact rows. Plan that job before the first one arrives.
• Let the database refuse overlaps where it can. PostgreSQL 18 added non-overlapping PRIMARY KEY and UNIQUE constraints, written WITHOUT OVERLAPS on a range column; the lab confirmed one rejects an overlapping version. Elsewhere, test for gaps and overlaps, as the last section shows.

Star schema, snowflake schema or one big table?

Kimball Group advises avoiding snowflakes, in which a dimension's hierarchy is normalized into secondary tables: users find them hard to navigate, they can hurt query performance, and a flattened dimension holds exactly the same information. Microsoft agrees for Power BI, where the benefits of a single table generally outweigh those of several. For a warehouse it names three cases for a snowflake: an extremely large dimension, keys to relate facts at a higher grain such as a subcategory target, and history tracked at the higher levels. Even then it recommends a view that flattens the result.

The one-big-table column is where the debate lives. dbt Labs' structure guide recommends wide, denormalized marts on the argument that storage is cheap and compute is expensive, and a more normalized approach when the dbt Semantic Layer is in use. ClickHouse's Star Schema Benchmark guide builds a flat table from the benchmark's star as an optional step and notes that many ClickHouse uses do this. BigQuery's documentation prefers nested and repeated fields for hierarchical data, yet says star schemas are already typically optimized for analytics, so denormalizing further might not change performance much.

Storage is part of the trade. Parquet dictionary-encodes a column: it builds a dictionary of the values it meets and stores the column as integers, so a repeated category name costs little in a wide table, until the dictionary grows too big and the encoding falls back to plain. How columnar compression works explains the mechanism.

Keep the star as the modelled source of truth and derive a flat table from it, with a view or a model, when a consumer wants one. Flattening a star is a query; unpicking a flat table into dimensions that carry history is a project. The reverse also holds: one report fed from one source, with no history to keep, does not need a warehouse. Microsoft notes that analysts can model in Power Query straight from source data, but that this approach cannot manage historical change.

Kimball, Inmon or Data Vault?

Kimball Group's 2004 comparison with Inmon's Corporate Information Factory finds common ground on enterprise integration and two fundamental differences: whether data is normalized before the dimensional models load, and whether atomic data lives in normalized or dimensional structures. William Inmon's own account of his side and the Data Vault Alliance's introduction fill in the rest. Read all three as each camp's statement of its case.

Two things stand out. First, a star schema appears in all three: Inmon puts star-schema marts on top of his warehouse, and the Data Vault Alliance says the business-facing side should look like a star schema. The real question is what sits underneath. Second, Kimball Group calls the hybrid of a normalized warehouse plus a dimensional one viable but costly, since atomic data is staged, stored and maintained twice, and worth it mainly for a team that has already built the normalized warehouse.

The Data Vault Alliance now teaches and certifies its method as version 2.1. Its statement that there is no concept of rework is the Alliance's claim, as each camp's critique of the others is theirs. A practical rule: with a handful of sources, a team that needs dashboards in weeks and one owner for conformance, model dimensionally from the start. With many changing sources, audit requirements and a platform team, a vault under the stars can pay for itself. Either way the consumption layer is a star, and in a medallion layout it lives in gold, as the medallion architecture guide describes.

Which mistakes break a star schema?

A wrong total often comes from a join, not from bad data. Order ORD-9001 has three lines and 12.00 of freight, a measure that belongs to the order, not to a line. Join an order header to the line-grain fact and the freight repeats on every line. SAP's modelling guide names this family of error a fan trap: the fanning out effect of one-to-many joins can return incorrect results, and you cannot detect it automatically. Across the four orders in the example, the join summed 59.00 of freight where the orders table holds 35.00:

CREATE TABLE order_header (order_number text PRIMARY KEY, freight numeric(10,2) NOT NULL);
INSERT INTO order_header VALUES ('ORD-9001', 12.00), ('ORD-9002', 0.00), ('ORD-9003', 8.00), ('ORD-9004', 15.00);

SELECT SUM(h.freight) FROM order_header h JOIN fact_sales f USING (order_number); -- 59.00 (wrong)
SELECT SUM(freight)   FROM order_header;                                           -- 35.00

Kimball's fix is to allocate header-level facts down to the line, with a rule agreed with the business, so they can be sliced by every dimension. Some BI tools compensate: Looker's symmetric aggregates count each order's total once, but only when a unique primary key and the correct join relationship are declared in the model. Do not count on every tool, or every query path, to rescue a mixed-grain design.

The last row matters most over time. Keep facts additive and at the grain, and keep ratios and filtered measures out of the tables and out of the dashboards, in a layer where each metric has one definition and one owner. The same shape suits assistants as much as people: one fact table and a few flat dimensions leave little to guess about how tables join. Text-to-SQL and its risks are covered in generative AI for databases.

How do you introduce a star schema without a big-bang project?

Build one star end to end before designing the enterprise:

1. Pick one business process whose numbers people dispute, such as sales or claims, and list the source tables behind each number.

2. Write the grain as one sentence, and add the process to a bus matrix.

3. List the dimensions from the "by" questions the business asks. Mark the ones other processes will reuse, and agree with the data steward, attribute by attribute, whether a change overwrites (Type 1) or adds a version (Type 2).

4. Build the dimensions first, with surrogate keys, unknown members and a date dimension. Then load the fact table through key lookups.

5. Test every load. Three queries cover most of it, each expected to return no rows:

   -- 1. Grain: a duplicate order line
   SELECT order_number, line_number FROM fact_sales GROUP BY 1, 2 HAVING count(*) > 1;
   
   -- 2. Keys: a fact row with no dimension row
   SELECT f.order_number FROM fact_sales f
   LEFT JOIN dim_customer c USING (customer_key) WHERE c.customer_key IS NULL;
   
   -- 3. History: a gap or an overlap between versions of one customer
   SELECT customer_id FROM (
     SELECT customer_id, valid_to,
            lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from) AS next_from
     FROM dim_customer) t
   WHERE next_from <> valid_to;
   

   Also compare staged rows with fact rows. In dbt, the grain test is dbt-utils' unique_combination_of_columns and the key test is the built-in relationships test, covered in our guide to building data analytics software.

6. Reconcile one figure with a report the business already trusts, and have its owner sign it off before the old report is retired.

7. Add the next process, reusing the conformed dimensions, and point dashboards and assistants at the star instead of the raw tables.

Computese does this work as part of its data platform service: dimensional and data vault modelling, dbt models that pass their tests in CI before they reach production, and new numbers reconciled with the old ones before the business signs off. A proof of concept for a bank's regulatory data on Cloudera and Iceberg kept counterparties as Type 2 versions, on synthetic data. The usual first step is a data platform assessment of your sources, reports and current pipelines.