The star schema is the dominant pattern for analytical data warehouse design — a central fact table surrounded by dimension tables, optimized for the aggregation and filtering queries that BI tools generate. This guide explains how star schemas work, why they are designed this way, and when alternatives are appropriate.
The star schema is the standard design pattern for analytical data warehouse tables. It structures data as a central fact table — containing quantitative measurements and foreign keys — surrounded by dimension tables that provide the descriptive context for those measurements. The resulting entity-relationship diagram looks like a star, with the fact table at the center and dimension tables radiating outward.
Star schema design dominates enterprise data warehousing because it is optimized for the queries that BI tools generate. When Tableau or Power BI asks "total revenue by region, product category, and month," the query aggregates a measure (revenue) across dimensional attributes (region, category, month). Star schema structures data specifically for this query pattern.
Fact Tables
A fact table stores the quantitative events of the business — transactions, clicks, calls, orders, sessions. Each row in a fact table represents a measurable event at a specific grain. The grain is the definition of what one row represents.
**Choosing the grain** is the most important design decision for a fact table. Grain options for an orders fact table might be:
- One row per order line item (lowest grain, most flexible)
- One row per order (aggregated from line items)
- One row per customer per day (daily snapshot)
The grain determines what questions the fact table can answer. A table at order line item grain can answer "what was the average discount on order lines in the furniture category?" — a question that requires row-level detail. An order-grain table cannot answer that question; it has lost the line-item detail in the aggregation.
**Fact table columns** are:
- **Surrogate keys** for each dimension: integer foreign keys joining to dimension tables (customer_key, product_key, date_key, location_key)
- **Degenerate dimensions**: dimension attributes with no separate dimension table — typically transaction ID, invoice number, order number
- **Measures**: the numeric facts — revenue, quantity, cost, discount amount, session duration, click count
Fact tables are typically narrow (few columns) but very long (many rows). The most common performance optimization is ensuring the fact table has appropriate sort keys, clustering, or partitioning on the date dimension for time-bounded queries.
Dimension Tables
Dimension tables provide the descriptive context for fact table measurements. They are typically wide (many columns) and short (fewer rows than facts). A product dimension might have columns for product name, category, subcategory, brand, SKU, product manager, color, size, unit cost, and any attribute by which analysts might want to filter or group products.
**Surrogate keys** in dimension tables are system-generated integer keys used for joining to fact tables. They are preferred over natural keys (business identifiers from source systems) because natural keys change, are reused, and may be non-numeric. The surrogate key is stable regardless of changes to the source system's identifier.
**Hierarchies** in dimensions represent levels of aggregation: product subcategory → product category → product department. BI tools can traverse these hierarchies for drill-down: a Tableau viz starts at category and drills down to subcategory. Storing the full hierarchy in the dimension table means the join path from fact to any level of the hierarchy is always one step.
**Conformed dimensions** are dimensions shared across multiple fact tables. A customer dimension used by both the orders fact table and the support tickets fact table is a conformed dimension. Conforming dimensions is what enables cross-process analysis — "what is the support ticket rate for customers in their first 90 days?" requires joining an orders fact (to know acquisition date) and a support tickets fact, which is possible because both use the same customer dimension with the same grain and definitions.
Star vs Snowflake Schema
The **snowflake schema** is a variation of star schema where dimension tables are normalized — category information is split into a separate category table that the product dimension joins to, rather than storing category attributes directly in the product dimension.
Snowflake schemas reduce storage by eliminating repeated category name strings across all product rows. They also reflect source system normalization more closely.
Star schemas denormalize all dimension attributes into the dimension table, eliminating the category join. BI tools query star schemas more efficiently because each query requires only one join from fact to dimension, regardless of which dimension attributes are used. Snowflake schemas require additional joins — product joins to category, which joins to department — for every query that uses those attributes.
In practice, star schema is the standard for analytical data warehouses. The storage cost reduction from snowflake normalization is negligible on cloud data warehouses with cheap columnar storage. The performance and simplicity benefits of star schema outweigh the normalization benefits for analytical workloads.
Slowly Changing Dimensions
Dimension attributes change over time. A customer changes their region; a product changes its category; a sales rep moves to a different territory. How a dimension table handles these changes is the slowly changing dimension (SCD) design problem.
**Type 1 SCD:** Overwrite the current value. The dimension always shows the current state; history is lost. Appropriate when historical accuracy is not required — correcting a data entry error, updating a current-state attribute that should not be tracked historically.
**Type 2 SCD:** Add a new row for each change, with effective start and end dates. The dimension grows over time; each historical state is preserved as a separate row with its own surrogate key. Fact table rows retain the surrogate key of the dimension as it existed at the time of the fact — queries can show sales attributed to the customer's region at the time of purchase, not their current region. Required for any historical analysis where the dimension attribute changed meaningfully over the analysis period.
**Type 3 SCD:** Add a column for the previous value. Simple to implement; only stores one prior value. Appropriate when only the most recent change needs to be tracked — not multiple historical states.
Our data architecture services practice designs dimensional models — star schemas, SCD implementations, and conformed dimension strategies — that produce analytics-ready data warehouse layers. Contact us to discuss your data modeling requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →