Star Schema
A star schema places a central fact table of measurable events next to denormalized dimension tables, enabling simple joins and fast analytical aggregation.
Definition
A star schema is a dimensional model with one central fact table holding numeric measures and foreign keys, surrounded by dimension tables that describe the who, what, where, and when. Each dimension is typically denormalized into a single table, so queries join the fact to dimensions in one hop.
Context and scope
Star schemas dominate data warehouses and BI tools because they make common aggregations predictable and easy for both people and query optimizers to reason about.
Common misconceptions
- A star schema is only for legacy warehouses, while modern columnar engines make dimensional modeling irrelevant.
- Dimension tables should be fully normalized to avoid any duplication.
Anti-patterns
- Building a snowflake of deeply nested dimension tables for a dashboard that only needs three attributes.
- Mixing grain levels in one fact table so a single row can represent both an order and an order line.
Related concepts
Related examples
Authoritative source
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
How it works
A star schema separates measurements from context. The fact table has one row per event at a declared grain, such as one order line, plus numeric measures like quantity and revenue, and foreign keys to dimensions. Dimension tables hold descriptive attributes: product category, customer region, store, and date. A date dimension lets analysts filter by fiscal quarter without parsing timestamps.
The shape resembles a star because each dimension joins directly to the fact. Denormalizing a dimension, for example putting category and department on the product dimension, avoids a chain of joins. The cost is duplicated descriptive data, which changes rarely and compresses well in columnar storage.
Grain is the contract
Declare the grain before adding measures. If the fact table mixes order-level and line-level rows, summing revenue double counts. Every measure must be valid at that grain, and every dimension key must be present. A fact table without a clear grain becomes a collection of unrelated numbers that different reports interpret differently.
When to use it
Use a star schema for reporting and BI over stable business processes: sales, shipments, sessions, payments. It suits aggregate queries that filter by dimensions and scan many fact rows. For highly normalized operational writes, keep the source schema and build the star in a warehouse or mart.
Trade-offs and example
Star schemas optimize read simplicity at the expense of storage and ETL work. Dimensions can drift if the pipeline does not apply slowly changing dimension rules. A snowflake schema saves space but adds joins and complexity; a wide flat table reads even faster but loses reusable dimensions.
Example: fact_sales(order_line_id, date_key, product_key, store_key, quantity, revenue) joined to dim_product and dim_date. A query for revenue by category and quarter is two joins, and the optimizer can prune date partitions. The same data normalized across five tables would require more joins and more opportunity for a wrong filter.