# Star schema: how to design one with Kimball's four steps and SQL > A star schema joins one fact table at a declared grain to flat dimension tables. Design steps, tested SQL, SCD Type 2, snowflake vs one big table, mistakes. - URL: https://computese.com/star-schema/ - Author: Duong Quan Nguyen, CEO, Computese - Published: 2026-10-03 - Updated: 2026-10-09 - Topics: Data ## In short - A star schema is one fact table of numeric measurements at a grain you declare in one sentence, joined by surrogate keys to wide, flat dimension tables. Reports filter and group by dimension attributes and sum the facts. - Design in Kimball's four steps, in order: select the business process, declare the grain, identify the dimensions, identify the facts. A measure that does not fit the declared grain belongs in another fact table. - Keep history with a Type 2 slowly changing dimension: close the current row, insert a new one, and look up each fact's surrogate key by date. Point missing members at an unknown row instead of a NULL foreign key. - Star, snowflake and one big table are trade-offs: Kimball Group advises avoiding snowflakes, Microsoft says one table per dimension generally wins in Power BI, and dbt's guide recommends wide marts unless its Semantic Layer is in use. - Snowflake standard tables, BigQuery and Databricks do not enforce primary or foreign keys, so test the grain, the keys and the history on every load. Wrong totals often come from joins that repeat a coarser-grain measure. A star schema is a dimensional model for analytics: one central fact table holds numeric measurements at a declared grain, such as one row per sale line, and denormalized dimension tables around it describe who, what, where and when. Fact rows reach each dimension through a surrogate key, so a query joins one table per angle it needs. This guide takes Ralph Kimball's design steps from a first sketch to SQL tested on PostgreSQL 18, including a Type 2 slowly changing dimension, then compares star, snowflake and one big table, and Kimball, Inmon and Data Vault. A star schema is one way to model the gold layer of a [medallion architecture](https://computese.com/medallion-architecture/), and a [semantic layer](https://computese.com/semantic-layer/) defines shared metrics on top of it. ## What is in a star schema? Kimball Group describes [star schemas](https://www.kimballgroup.com/wp-content/uploads/2013/08/2013.09-Kimball-Dimensional-Modeling-Techniques11.pdf) as dimensional structures in a relational database, with fact tables linked to dimension tables by primary and foreign keys. Microsoft's [Power BI guidance](https://learn.microsoft.com/en-us/power-bi/guidance/star-schema) 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. | | Fact table | Dimension table | | ---------- | -------------------------------------------------------------------- | ------------------------------------------------------------------ | | Holds | Measurements from one business process event | Descriptive context: who, what, where, when, why and how | | One row is | One measurement event, at the declared grain | One member: a product, a store, a day or one version of a customer | | Columns | A foreign key per dimension, numeric facts, optional degenerate keys | One primary key and many text attributes | | Shape | Long and narrow | Wide, flat and denormalized | 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](https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-overview) calls a star schema a prerequisite for enterprise Power BI semantic models. ![A long grid of event rows sits in the centre with a line to each of four small cards around it; one row of the grid is orange.](https://computese.com/images/blog/star-schema/star-layout.797f966868-1536.webp) *Each fact row is one event at the declared grain; each dimension card describes that event from one angle.* ## How do you design a star schema in four steps? Kimball Group lists [four decisions](https://www.kimballgroup.com/wp-content/uploads/2013/08/2013.09-Kimball-Dimensional-Modeling-Techniques11.pdf), 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. | Step | The decision | Retail example | Check before moving on | | ------------------------------ | ------------------------------------------------------ | ------------------------------ | ----------------------------------------------------------------- | | 1. Select the business process | An operational activity that produces measurements | Selling items at the till | Can you name the source tables that record it? | | 2. Declare the grain | What exactly one fact row represents | One row per order line | Could two rows describe the same event? | | 3. Identify the dimensions | Who, what, where, when, why and how; one value per row | Date, store, product, customer | Does each dimension have exactly one value for each fact row? | | 4. Identify the facts | Numeric measurements consistent with the grain | Quantity and net amount | Does any fact belong to a coarser grain, like an order's freight? | 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](https://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/) 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](https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables), 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](https://www.postgresql.org/docs/18/sql-createtable.html), and `generate_series` and `to_char` are PostgreSQL functions; other engines spell these differently, so adapt the DDL. ```sql 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`](https://www.postgresql.org/docs/18/functions-srf.html) walks a timestamp range one day at a time, and the key is the date written as an integer: ```sql 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. ```sql 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. ```sql 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; ``` | year | quarter | category | region | units | net_sales | avg_price | | ---- | ------- | -------- | ------ | ----- | --------- | --------- | | 2026 | 3 | Coffee | East | 3 | 55.50 | 18.50 | | 2026 | 3 | Tea | East | 3 | 23.25 | 7.75 | | 2026 | 4 | Coffee | East | 1 | 18.50 | 18.50 | | 2026 | 4 | Tea | West | 5 | 46.25 | 9.25 | PostgreSQL [enforces](https://www.postgresql.org/docs/18/sql-createtable.html) 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: | Platform | Primary and foreign keys | | -------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- | | Snowflake, standard tables | [Optional and not enforced](https://docs.snowflake.com/en/sql-reference/constraints-overview); `NOT NULL` and `CHECK` are enforced | | BigQuery | [Not enforced](https://docs.cloud.google.com/bigquery/docs/primary-foreign-keys); the user keeps the data consistent | | Databricks | [Informational only](https://docs.databricks.com/aws/en/tables/constraints); `NOT NULL` and `CHECK` are enforced | | Microsoft Fabric Warehouse | [Allowed only as `NOT ENFORCED`](https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints), added with `ALTER TABLE` | > [!WARNING] > On those engines a duplicated order line or an orphan key loads without an error, and BigQuery warns that queries over tables that break a declared constraint might return incorrect results. Declare the keys anyway: BigQuery can use them to optimize queries, and Power BI Desktop can read unenforced foreign keys to detect relationships. Then test the grain and the keys on every load. ## Which fact table type fits, and what can you add up? Kimball Group's [technique list](https://www.kimballgroup.com/wp-content/uploads/2013/08/2013.09-Kimball-Dimensional-Modeling-Techniques11.pdf) describes four kinds of fact table by what one row represents: | Type | One row is | Updated after insert? | Example | | --------------------- | ------------------------------------------------------------------- | --------------------------------- | --------------------------------------------- | | Transaction | One measurement event at a point in space and time | No | A sale line, a payment | | Periodic snapshot | A summary of a standard period; the grain is the period | No | An account balance at month end | | Accumulating snapshot | One run of a process, with a date key for each milestone | Yes, as each milestone is reached | Order fulfilment: ordered, shipped, delivered | | Factless | Dimension keys that come together at a moment, with no numeric fact | No | A student attending a class | 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`](https://github.com/dbt-labs/dbt-utils) 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: | Business process | Date | Store | Product | Customer | Supplier | | ------------------ | :--: | :---: | :-----: | :------: | :------: | | Retail sales | x | x | x | x | | | Returns | x | x | x | x | | | Inventory snapshot | x | x | x | | | | Purchase orders | x | | x | | x | 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: ```sql 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; ``` | category | net_sales | refunds | | -------- | --------- | ------- | | Coffee | 74.00 | 55.50 | | Tea | 69.50 | 46.25 | 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](https://www.kimballgroup.com/wp-content/uploads/2013/08/2013.09-Kimball-Dimensional-Modeling-Techniques11.pdf) define the types, and [Design Tip #152](https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/) adds the hybrids: | Type | What happens on a change | History kept | Use it for | | ------------------------------------ | ------------------------------------------------------------------------------------------ | ----------------------------------- | --------------------------------------------- | | 0, retain original | Nothing; the value never changes | The original only | An original credit score, durable IDs | | 1, overwrite | The old value is replaced | None; aggregates must be recomputed | Corrections, attributes whose past is moot | | 2, add a row | A new row and surrogate key, with effective date, expiration date and current flag | All of it | Attributes reports must show as they were | | 3, add an attribute | The old value moves to a second column | One prior value | Alternate views; used relatively infrequently | | 4, mini-dimension | Fast-changing attributes move to a small dimension of their own; both keys sit in the fact | Per fact row | Volatile attributes of a very large dimension | | 6, Type 1 attributes in a Type 2 row | Type 2 plus current-value columns overwritten on every row of the same durable key | As-was and as-is | Reports that need both views | 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. ```sql 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: | customer_key | customer_id | segment | valid_from | valid_to | is_current | | ------------ | ----------- | -------- | ---------- | ---------- | ---------- | | 1 | C-1042 | Consumer | 2026-01-01 | 2026-10-10 | false | | 3 | C-1042 | Business | 2026-10-10 | 9999-12-31 | true | ![A faded customer card with a dashed outline sits on the left, an arrow with a gear leads to a solid orange card on the right, and a timeline runs beneath both.](https://computese.com/images/blog/star-schema/customer-versions.781f553302-1536.webp) *A Type 2 change keeps the old row and adds a new one, so each fact points at the version in force on its date.* 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: ```sql -- 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](https://docs.getdbt.com/docs/build/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](https://www.postgresql.org/docs/18/release-18.html) 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. | | Star | Snowflake | One big table | | ----------------- | ------------------------------------------- | -------------------------------------------- | --------------------------------------------------- | | Shape | A fact joined to flat dimensions | Dimensions normalized into sub-tables | Fact and dimension attributes in one wide table | | Joins per query | One per dimension | One per dimension plus one per extra level | None | | Attribute history | Type 2 rows in the dimension | Tracked per level | Frozen into each row when it loads | | Fits when | Shared definitions, several facts, BI tools | A huge dimension, or facts at a higher grain | One consumer, one grain, or features for a model | | Watch for | Design and stewardship effort | Confusing models and longer filter paths | A changed attribute touches every row; copies drift | The one-big-table column is where the debate lives. dbt Labs' [structure guide](https://docs.getdbt.com/best-practices/how-we-structure/4-marts) 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](https://clickhouse.com/docs/get-started/sample-datasets/star-schema) builds a flat table from the benchmark's star as an optional step and notes that many ClickHouse uses do this. BigQuery's [documentation](https://docs.cloud.google.com/bigquery/docs/best-practices-performance-nested) 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](https://parquet.apache.org/docs/file-format/data-pages/encodings/) 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](https://computese.com/revolutionary-data-compression-algorithm/) 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](https://www.kimballgroup.com/2004/03/differences-of-opinion/) 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](https://williaminmon.substack.com/p/a-tale-of-two-architectures-kimball) of his side and the Data Vault Alliance's [introduction](https://datavaultalliance.com/engineering/data-vault-2-0-an-introduction/) fill in the rest. Read all three as each camp's statement of its case. | | Kimball bus architecture | Inmon Corporate Information Factory | Data Vault | | ---------------------- | ------------------------------------------------------- | --------------------------------------------------------------- | -------------------------------------------------------------------- | | Core structure | Dimensional models by business process | A normalized enterprise data warehouse | Hubs (business keys), links (relationships), satellites (attributes) | | Atomic data lives in | Dimensional structures | The normalized warehouse | The vault | | Analysts query | The stars | Departmental dimensional marts fed from the warehouse | An information layer built to look like a star schema | | Integration comes from | Conformed dimensions and the bus matrix | Recasting source data into one corporate form | Business keys held in hubs, organized by business concept | | Stated priority | Business acceptance, one process (matrix row) at a time | A long-term infrastructure with a "single version of the truth" | An extensible platform: new data gets new hubs and satellites | 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](https://datavaultalliance.com/dv2-1-modeling-and-delivery/). 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](https://computese.com/medallion-architecture/) 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](https://help.sap.com/doc/4667b9486e041014910aba7db0e91070/4.3.3/en-US/sbo43sp2_info_design_tool_en.pdf): 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: ```sql 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 ``` ![One small order slip with a single coin fans out into three item rows that each carry a coin, ending in a tall orange bar three times the height of a grey bar.](https://computese.com/images/blog/star-schema/fan-trap.103f03b3fd-1536.webp) *A measure stored once per order is counted once per line after the join, so the total grows with the number of lines.* 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](https://docs.cloud.google.com/looker/docs/best-practices/understanding-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. | Symptom | Likely cause | Fix | | -------------------------------------------------- | -------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | A total grows when another table is joined in | A fan trap, or two fact tables joined directly | Allocate to the finer grain, or query each fact separately and merge on a conformed attribute | | Daily and monthly figures sit in one table | Mixed grain in a single fact table | One fact table per grain; roll up in queries or aggregates | | Fewer fact rows than staged rows | An inner join in the key lookup dropped rows whose dimension row had not arrived | `LEFT JOIN` to an unknown member, then compare counts on every load | | Last year's sales jump to a new region | A Type 1 overwrite on an attribute that needed history | Make the attribute Type 2 and restate the affected facts | | A dimension has an overwhelming number of versions | Type 2 on a fast-changing attribute | A mini-dimension (Type 4), or move the attribute into the fact table, [as Microsoft suggests](https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables) | | Five joins to reach a product category | A snowflaked product dimension | Flatten it into one dimension, or expose a flattening view | | Every report formats dates its own way | No date dimension | Add one and filter on its attributes, as Kimball's rules require for every fact table | | Every dashboard has its own revenue | Metric logic written inside each BI tool | Define each metric once in a [semantic layer](https://computese.com/semantic-layer/), on top of the star | 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](https://computese.com/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: ```sql -- 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`](https://github.com/dbt-labs/dbt-utils) and the key test is the built-in [`relationships`](https://docs.getdbt.com/docs/build/data-tests) test, covered in our guide to [building data analytics software](https://computese.com/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](https://computese.com/services/data-platform/): 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](https://computese.com/work/banking-regulatory-data/) 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. ## Key terms - **Star schema**: A dimensional model in a relational database: a fact table linked by primary and foreign keys to denormalized dimension tables. The layout looks like a star, with the fact table at the centre. - **Fact table**: A table of measurements from one business process, with one row per measurement event at the declared grain, a foreign key for each dimension and numeric facts. - **Dimension table**: A table of descriptive attributes for one entity, such as a product, a store or a date, used to filter and group facts. It has one primary key and is usually wide and denormalized. - **Grain**: The statement of exactly what one fact row represents, such as one row per order line. It is declared before the dimensions and the facts are chosen. - **Surrogate key**: A meaningless integer primary key assigned by the warehouse to each dimension row, so fact tables do not depend on source-system keys. The date dimension is the usual exception. - **Conformed dimension**: A dimension whose attribute names and values are identical wherever it is used, so facts from different business processes can be compared on the same row headers. - **Slowly changing dimension (SCD)**: A dimension whose attributes change over time, with a policy per attribute: Type 1 overwrites, Type 2 adds a row, Type 3 adds a column. - **Degenerate dimension**: A dimension key with no descriptive attributes of its own, such as an order number, stored in the fact table instead of a separate dimension table. - **Bus matrix**: A planning grid with business processes as rows and dimensions as columns. It shows which dimensions each process uses and where a dimension must conform. - **Fan trap**: A join that repeats a measure stored at a coarser grain on every row of a finer one, such as an order's freight on each order line, so sums come out too high. ## Common questions ### What is a star schema in simple terms? It is a way to lay out tables for reporting. One central fact table records events with numbers, such as sale lines, and flat dimension tables describe them: products, stores, dates and customers. A report sums the facts and groups them by dimension attributes, which is why BI tools handle the shape well. ### What is the difference between a fact table and a dimension table? A fact table holds measurements of a business process, one row per event at the declared grain, with a foreign key to each dimension. A dimension table holds the descriptive attributes of one entity, such as a product or a date. Queries sum the facts and use the dimensions to filter and group. ### Should I use a star schema or a snowflake schema? Use a star by default. A snowflake normalizes dimension hierarchies into sub-tables; Kimball Group advises avoiding it because users find it hard to navigate, and Microsoft says one table per dimension generally wins for Power BI. Snowflake a dimension only when it is extremely large or other facts need a key at a higher level. ### Do I still need a star schema if I can use one big table? A star remains the modelled source of truth even when a consumer receives a flat table. dbt's structure guide recommends wide, denormalized marts, and BigQuery notes that star schemas are already typically optimized for analytics. Derive the flat table from the star so definitions, history and conformed attributes stay in one place. ### What is the grain of a fact table? The grain states exactly what one fact row represents, such as one row per order line or one row per account per month. Kimball Group calls the declaration a binding contract: every dimension and fact must be consistent with it, and different grains belong in different fact tables. ### Kimball or Inmon: which approach is better? Neither wins in general. Kimball builds dimensional models by business process, integrated by conformed dimensions; Inmon builds a normalized enterprise warehouse first and feeds dimensional marts from it. Both call for enterprise integration, and Kimball Group says combining them is viable but stores atomic data twice. ### Which slowly changing dimension type should I use? Choose per attribute. Type 1 overwrites and suits corrections and attributes whose history does not matter. Type 2 adds a row and suits attributes that reports must show as they were, such as a customer segment. Keep Type 2 to those attributes, because many versions are hard to analyze. ## Sources 1. [Kimball Dimensional Modeling Techniques](https://www.kimballgroup.com/wp-content/uploads/2013/08/2013.09-Kimball-Dimensional-Modeling-Techniques11.pdf), Kimball Group 2. [Understand star schema and the importance for Power BI](https://learn.microsoft.com/en-us/power-bi/guidance/star-schema), Microsoft Learn 3. [Dimensional modeling in Fabric Data Warehouse](https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-overview), Microsoft Learn 4. [Dimensional modeling in Fabric Data Warehouse: Dimension tables](https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables), Microsoft Learn 5. [The 10 Essential Rules of Dimensional Modeling](https://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/), Kimball Group 6. [PostgreSQL 18 documentation: CREATE TABLE](https://www.postgresql.org/docs/18/sql-createtable.html), PostgreSQL Global Development Group 7. [PostgreSQL 18 documentation: Set Returning Functions](https://www.postgresql.org/docs/18/functions-srf.html), PostgreSQL Global Development Group 8. [Constraints (Snowflake)](https://docs.snowflake.com/en/sql-reference/constraints-overview), Snowflake documentation 9. [Use primary and foreign keys (BigQuery)](https://docs.cloud.google.com/bigquery/docs/primary-foreign-keys), Google Cloud documentation 10. [Constraints on Databricks](https://docs.databricks.com/aws/en/tables/constraints), Databricks documentation 11. [Primary keys, foreign keys, and unique keys in Warehouse in Microsoft Fabric](https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints), Microsoft Learn 12. [dbt-utils package README](https://github.com/dbt-labs/dbt-utils), dbt Labs on GitHub 13. [Design Tip #152: Slowly Changing Dimension Types 0, 4, 5, 6 and 7](https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/), Kimball Group 14. [Add snapshots to your DAG](https://docs.getdbt.com/docs/build/snapshots), dbt Developer Hub 15. [PostgreSQL 18 release notes](https://www.postgresql.org/docs/18/release-18.html), PostgreSQL Global Development Group 16. [Marts: Business-defined entities](https://docs.getdbt.com/best-practices/how-we-structure/4-marts), dbt Developer Hub 17. [Use nested and repeated fields (BigQuery)](https://docs.cloud.google.com/bigquery/docs/best-practices-performance-nested), Google Cloud documentation 18. [Star Schema Benchmark (SSB, 2009)](https://clickhouse.com/docs/get-started/sample-datasets/star-schema), ClickHouse documentation 19. [Parquet encodings](https://parquet.apache.org/docs/file-format/data-pages/encodings/), Apache Parquet documentation 20. [Differences of Opinion](https://www.kimballgroup.com/2004/03/differences-of-opinion/), Kimball Group (Margy Ross, 2004) 21. [A Tale of Two Architectures - Kimball vs Inmon](https://williaminmon.substack.com/p/a-tale-of-two-architectures-kimball), William Inmon (own newsletter, December 2025) 22. [Your Data Vault 2.0 Introduction](https://datavaultalliance.com/engineering/data-vault-2-0-an-introduction/), Data Vault Alliance 23. [DV2.1 Modeling and Delivery](https://datavaultalliance.com/dv2-1-modeling-and-delivery/), Data Vault Alliance 24. [Understanding symmetric aggregates](https://docs.cloud.google.com/looker/docs/best-practices/understanding-symmetric-aggregates), Google Cloud (Looker documentation) 25. [Information Design Tool User Guide: Fan traps](https://help.sap.com/doc/4667b9486e041014910aba7db0e91070/4.3.3/en-US/sbo43sp2_info_design_tool_en.pdf), SAP 26. [Add data tests to your DAG](https://docs.getdbt.com/docs/build/data-tests), dbt Developer Hub