# What is a star schema, and how do fact and dimension tables work?

A way to arrange warehouse tables for reporting: a fact table of events and their numbers, joined by keys to dimension tables that describe them, such as customer, product and date.

- Page: https://tecyfy.com/concepts/star-schema
- Subject: Data engineering in the AI era (https://tecyfy.com/concepts)
- Level: beginner
- Also called: Dimensional modelling, Dimensional modeling, Fact table, Dimension table, Kimball model

A star schema arranges the tables of a data warehouse for reporting. In the middle is a fact table, one row per event with its numbers, such as each line of each sale with its quantity and amount. Around it are dimension tables that describe the events: who bought (customer), what (product), where (store) and when (date). Each fact row holds a key to each dimension. Drawn on a page, the fact table is the centre and the dimensions are the points, which gives the star its name. It is the core of dimensional modelling, the approach the Kimball Group set out, and Microsoft calls it a mature approach widely used by relational warehouses.

## Facts and dimensions

**Fact tables** record a business process: orders, payments, shipments, page views. They are long and narrow, mostly keys and numbers, and they keep growing. **Dimension tables** hold the words: a customer's name, city and segment; a product's category and brand; a date's weekday, month and holiday flag. They are short and wide, and they supply every filter and grouping in a report. "Sales by category for weekends in March" reads the amounts from the fact table and takes category, weekend and March from 2 dimensions.

Not every number adds up. An amount sold is additive: it sums across customers, products and days. A bank balance is semi-additive: it sums across accounts but not across days, since adding Monday's balance to Tuesday's means nothing. A ratio such as margin percent is non-additive, so Kimball's advice is to store its parts and divide at the end.

## How a star is designed

Kimball's design takes 4 decisions, in order:

1. **Choose the business process**, such as retail sales.
2. **Declare the grain**: exactly what one fact row is, such as one product on one receipt. Starting at the lowest level the process records keeps every later question answerable, and different grains never share a fact table.
3. **Choose the dimensions** that describe each row at that grain.
4. **Choose the facts**, the numbers each row measures.

Each dimension gets its own key, a plain integer assigned by the warehouse, rather than the source system's id. That lets a customer have several rows over time, as with [slowly changing dimensions](https://tecyfy.com/concepts/slowly-changing-dimensions) type 2, and lets 2 source systems' ids live side by side.

## Star or snowflake

Splitting a dimension further, so product points to a category table that points to a department table, gives a snowflake schema. It stores each category name once, but Kimball advises against it: business users find it harder to follow and queries can be slower, while a flat product table holds exactly the same information.

When 2 fact tables, such as sales and returns, share the same customer and product dimensions, those dimensions are conformed, and a report can put sales and returns side by side on the same rows. That shared set is what turns separate stars into one warehouse.

## Why it matters for AI

An assistant that answers questions from a [warehouse](https://tecyfy.com/concepts/data-warehouse) writes SQL, and a star schema is the easiest shape to write correct SQL against: one join from the facts to each dimension, with clear names. A [semantic layer](https://tecyfy.com/concepts/semantic-layer) on top of a star defines each measure once, such as revenue, so the assistant and every dashboard compute it the same way.

## Related

- [Slowly Changing Dimensions](https://tecyfy.com/concepts/slowly-changing-dimensions): The ways a warehouse handles a change to a descriptive record, such as a customer's address: type 1 overwrites it, type 2 adds a new dated version, and type 3 keeps the previous value in an extra column.
- [Data Warehouse](https://tecyfy.com/concepts/data-warehouse): A database built for analysis: cleaned, structured data from many systems, kept in one place and shaped for fast reports and dashboards.
- [Semantic Layer](https://tecyfy.com/concepts/semantic-layer): One shared definition of business terms and metrics, such as revenue or active customer, that reports, dashboards and AI assistants all read instead of writing their own.
- [Medallion Architecture](https://tecyfy.com/concepts/medallion-architecture): Organising a lakehouse in 3 layers of rising quality: bronze for raw data, silver for cleaned and joined data, and gold for tables shaped for reports and AI.

## Sources

- [Star Schemas and OLAP Cubes](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube/)
- [Four-Step Dimensional Design Process](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/four-4-step-design-process/)
- [Grain](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/grain/)
- [Additive, Semi-Additive, Non-Additive Facts](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/additive-semi-additive-non-additive-fact/)
- [Dimension Surrogate Keys](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/dimension-surrogate-key/)
- [Snowflaked Dimensions](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/snowflake-dimension/)
- [Conformed Dimensions](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/conformed-dimension/)
- [Understand star schema and the importance for Power BI](https://learn.microsoft.com/en-us/power-bi/guidance/star-schema)