Star Schema
A star schema is a dimensional data modeling technique in which a central fact table, holding measurable business events, is directly connected to multiple denormalized dimension tables that provide descriptive context.
Definition
A star schema is a dimensional data modeling technique in which a central fact table, holding measurable business events, is directly connected to multiple denormalized dimension tables that provide descriptive context.
Overview
In a star schema, one fact table sits at the center — for example, a table of sales transactions with numeric measures like quantity and revenue — surrounded by dimension tables such as Customer, Product, Store, and Date, each connected directly to the fact table by a foreign key. Drawn out, the fact table in the middle with dimensions radiating outward resembles a star, which gives the pattern its name. Dimension tables in a star schema are deliberately denormalized: rather than splitting a Product dimension into separate tables for category and brand, all of that descriptive data is flattened into one wide table. This trades some data redundancy for simplicity and query speed, since analytical queries need fewer joins than they would against a fully normalized transactional schema. The star schema is the foundation of most data warehouse and data mart designs and underlies OLAP reporting, because its simple structure maps naturally onto how business users think ('total sales by product by month by region') and is easy for query engines and BI tools to optimize. Its main alternative is the snowflake schema, which normalizes dimension tables further to reduce redundancy at the cost of more joins. Dimensional modeling with star schemas is a core topic in analytics engineering, and tools like dbt are commonly used to build and maintain these models on top of a warehouse — a workflow explored in courses such as dbt & Analytics Engineering.
Key Concepts
- One central fact table surrounded by denormalized dimension tables
- Fact tables hold numeric measures; dimension tables hold descriptive attributes
- Fewer joins than normalized schemas, making analytical queries faster and simpler
- Intuitive structure that maps to how business users ask questions
- Well supported by BI tools and OLAP cubes for slicing and dicing data
- Trades storage redundancy for query simplicity and performance