Star Schema Diagram: Fact and Dimension Tables, with a Sales Example

A product manager asks for revenue by category and region for last quarter, against the same quarter a year ago. In the order database that means joining five tables, building a calendar on the fly and remembering that customers move between regions. Every new question needs new joins, and two analysts get two numbers.

A star schema fixes this by reshaping the data around one business process: a fact table of measurements in the middle, dimension tables describing them around it. This post designs a sales star schema step by step, from the grain to the SQL, including slowly changing dimensions, star versus snowflake, and columnar warehouses.

What a star schema is

A star schema has two kinds of tables:

• Fact tables record measurements of a business event, such as an order line: foreign keys to the dimensions plus numeric measures. They are narrow and long.
• Dimension tables hold the context, the who, what, where and when: wide, flat, short tables of text attributes such as category or region.

The Kimball Group, which named many of these techniques, describes it as fact tables linked to dimension tables by primary and foreign keys. Microsoft's Power BI guidance puts the appeal simply: dimension tables enable filtering and grouping, fact tables enable summarization.

Design it in four steps

Kimball's four-step dimensional design process is four decisions, in order:

1. Select the business process: retail sales, in stores and a web shop.
2. Declare the grain: one row per order line, meaning one product on one order. Kimball calls the grain a binding contract, declared before any dimension or fact.
3. Identify the dimensions: date, product, store, customer and promotion, each with one value per order line.
4. Identify the facts: quantity, unit price, discount, net amount and cost, all true at that grain.

Start at the atomic grain, the lowest level the source captures; summaries come from atomic rows, never the reverse. Never mix grains: a shipping fee charged per order but stored on every line is counted once per line. Allocate it to the lines with a business rule, as Kimball advises, or give it its own fact table.

The example tables

The dimensions are deliberately flat: dim_product carries brand, subcategory and category in one row, and dim_store treats the web shop as one more store with channel = 'online'. order_number is a degenerate dimension: the header's context lives in other dimensions, so only the number remains, still useful for grouping baskets.

Unit price is the classic trap: summed across lines it means nothing, so divide additive parts instead, as in SUM(net_amount) / SUM(quantity).

Surrogate keys and the date dimension

Each dimension gets a surrogate key, a meaningless integer assigned in sequence from 1, because a dimension that tracks history has several rows per natural key and sources often disagree on keys. Keep the natural key (sku, store_code, customer_id) as an attribute for tracing rows to the source.

The date dimension is the exception: stable and predictable, so its key can be meaningful, such as 20261120, which also helps partitioning. Filter and group on its attributes (year, quarter, is_holiday), not on arithmetic over the key. Twenty years is only about 7,305 rows (20 × 365.25).

Fact foreign keys should never be null: a null key drops the row from inner joins, and totals silently shrink. Use default dimension rows for unknown or not applicable values; here, promotion_key 0 means "No promotion".

Slowly changing dimensions

The slowly changing dimension (SCD) type decides what reports show after an attribute changes:

Kimball's types 4 to 7 add mini-dimensions and hybrids of these.

Type 2 is the workhorse. Customer C-1042 moves from Austin to Denver on 20 November 2026:

Earlier sales keep key 1001 and stay in Austin; later sales get 2417. The fact load picks the row whose range contains the order date (valid_from <= order_date < valid_to). Kimball's type 2 definition asks for at least an effective date, an expiration date and a current-row indicator; a far-future valid_to instead of null keeps range filters simple. dbt snapshots implement type 2 for you.

To report all history by the current city, join to the current row with the same customer_id. Change data capture feeds type 2 well, because every update arrives as an event; see the change data capture diagram.

Star schema vs snowflake schema vs one big table

Kimball advises against snowflaked dimensions: they are harder for business users to navigate, can hurt query performance, and a flattened dimension holds exactly the same information. Power BI's guidance agrees.

One big table needs no joins, and columnar compression keeps its repeated values cheap. The cost is maintenance: corrections become rewrites and new attributes rebuilds, so generate it from the star.

A query the model makes easy

SELECT d.year_month,
       p.category,
       SUM(f.net_amount)                                AS revenue,
       SUM(f.net_amount - f.cost_amount)                AS gross_margin,
       SUM(f.net_amount) / NULLIF(SUM(f.quantity), 0)   AS avg_selling_price
FROM fact_sales AS f
JOIN dim_date    AS d ON d.date_key    = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
JOIN dim_store   AS s ON s.store_key   = f.store_key
WHERE d.year = 2026
  AND s.channel = 'online'
GROUP BY d.year_month, p.category
ORDER BY d.year_month, revenue DESC;

Every join goes from the fact to one dimension on one integer key; filters sit on dimension attributes and sums on fact columns. A new question changes the WHERE and GROUP BY, not the joins, which is why BI tools generate such queries reliably.

Performance in columnar warehouses

Columnar warehouses read only the columns a query uses, which suits a narrow fact table, and small dimensions join cheaply: in a broadcast join, BigQuery sends the smaller table to every worker. With INT64 keys and quantities at 8 logical bytes and NUMERIC amounts at 16, the query above reads 64 bytes per order line from the fact, about 32 GB for 500 million lines, however wide the table is.

• Prune by date. Partition, cluster or sort the fact on date_key. BigQuery prunes partitions when the partitioning column itself has a constant filter, so large scans should also filter date_key with constants, not only dim_date.year.
• Declare keys, even unenforced. BigQuery uses primary and foreign keys declared NOT ENFORCED to eliminate and reorder joins, and Snowflake eliminates joins for constraints marked RELY. Neither checks the data, and violated constraints can return wrong results, so test keys in the pipeline.
• Co-locate in Redshift. Redshift's guidance is to distribute the fact and one dimension on their common key, use ALL distribution for some other dimensions, or let AUTO choose.

Star schemas usually live in the gold layer of a medallion architecture; the data pipeline architecture diagram shows how data gets there, and ETL vs ELT where transformations run.

The sales star schema diagram

How to read it:

• fact_sales in the center holds five foreign keys, the degenerate order_number, line_number and five measures.
• Crow's feet on every line: zero or many fact rows at one end, exactly one dimension row at the other. Labels name the join keys.
• Teal cards are dimensions, with dim_date on top and the rest to the left and right.
• The amber card, dim_customer, keeps history as SCD type 2.
• The Markdown block states the grain and which measures you may sum.

Drawing star schema diagrams clearly

• Write the grain on the diagram. Reviewers ask for it first, and a misread grain causes double counting.
• Put the fact in the middle and every dimension one hop away. A line between two dimensions is a snowflake; draw it on purpose or flatten it.
• Mark key roles: PK, FK, NK for natural keys and DD for degenerate dimensions.
• Show the SCD type on dimensions that keep history.
• Leave out load jobs, staging tables and columns nobody filters by.
• Draw one business process per diagram. When fact tables share conformed dimensions, add a bus matrix: processes as rows, dimensions as columns.

See the ER diagram example for crow's foot notation.

Build it in Archboard

1. Sign in with Google or GitHub and create a diagram.
2. If the tables exist, paste their CREATE TABLE statements into the SQL to ER diagram tool, or DBML into the DBML to ER diagram tool, for a first draft.
3. Add an Entity from the Entity Relation pack named fact_sales, one attribute per line: five keys marked FK, order_number marked DD, line_number, then the measures. Give it a blue accent.
4. Add five more Entity nodes: dim_date above the fact, dim_customer and dim_promotion on the left, dim_product and dim_store on the right. Mark surrogate keys PK and natural keys NK; use teal accents, amber for dim_customer.
5. Connect fact_sales to each dimension with crow's feet, zero or many at the fact and exactly one at the dimension, and label each line with its key.
6. Add Core Text notes: a key legend, the SCD type 2 rule, the smart date key, the default promotion row and the web shop.
7. Add a Markdown block from the Core pack below the fact with the grain and the additivity rules.

The example uses 7 diagram nodes, within the Free plan.

FAQ

What is a star schema in simple terms? A central fact table of measurements, such as sales amounts, linked to dimension tables that describe them, such as date, product and customer. Drawn out, it looks like a star.

What is the difference between a fact table and a dimension table? A fact table stores measurements of events at a declared grain, plus foreign keys. A dimension table stores the attributes you filter and group by, such as category or region.

What is the difference between a star schema and a snowflake schema? A star keeps each dimension in one flat table; a snowflake normalizes it into chains such as product, subcategory and category. Stars need fewer joins and are easier to use.

Is a star schema normalized? Not fully. Dimension tables are deliberately denormalized, repeating values such as category names on every product row, while the fact table stays narrow.

Do star schemas still matter in columnar warehouses? Yes. Wide tables scan cheaply in columnar engines, but a star still gives you one place to fix an attribute, history through slowly changing dimensions and consistent answers across reports.

Start free — one seat, no credit card.