What is a star schema?
A star schema is a way of organizing analytical data into one central fact table surrounded by dimension tables that describe it. Drawn as a diagram, the fact table sits in the middle with dimension tables radiating out from it — hence the name.
Facts and dimensions
A fact table holds the events you want to measure, one row per event, at a consistent grain: one row per order line, one row per page view, one row per support ticket. Its columns are the numeric measures you’ll aggregate (quantity, amount, duration) plus foreign keys pointing at the dimensions. Fact tables are the tall ones — hundreds of millions of rows is normal.
Dimension tables hold the descriptive context you slice by: customers, products, stores, dates. One row per customer, with every attribute you might want to group or filter on. Dimensions are wide and short.
A query against a star schema then reads the same way every time — aggregate something from the fact table, group by an attribute from a dimension:
SELECT d_product.category,
SUM(f_order_items.amount) AS revenue
FROM f_order_items
JOIN d_product ON f_order_items.product_key = d_product.product_key
JOIN d_date ON f_order_items.date_key = d_date.date_key
WHERE d_date.year = 2024
GROUP BY d_product.category;
Why denormalize
This is where a star schema deliberately parts ways with normalization. In a normalized transactional database, product would be split across products, categories, subcategories, and brands, each in its own table, so that a category name is stored exactly once and can’t fall out of sync. That’s the right call when the priority is fast, correct writes.
In a star schema you flatten those into a single d_product dimension with category, subcategory, and brand as columns. The category name repeats across thousands of rows, which would be a defect in an OLTP design and is fine here:
- Fewer joins. One join instead of four. Query planners handle the star pattern well, and the SQL people write by hand is dramatically simpler.
- Writes aren’t the constraint. Analytical tables are loaded by a data pipeline on a schedule, not updated by users, so the update anomalies normalization protects against mostly don’t arise.
- Storage is cheap. On an analytical database with columnar storage, a repeated string compresses down to almost nothing.
- It’s legible. “Group orders by product category” maps onto one obvious column, which matters when non-analysts are building their own questions in a query builder.
The main variant you’ll meet is the snowflake schema, which normalizes the dimensions back out into sub-tables. It saves a little space and costs you joins and clarity — most teams building for BI stick with the star.
Related terms
Further reading
Put it to work
- Ecommerce sales dashboard — Dashboard
- Average order value — Metric
- Ecommerce analytics — Overview
