Star Schema

A dimensional database design with a central fact table surrounded by denormalized dimension tables — the standard physical form of a dimensional model.

Last updated: 2026-04-12

Overview

In a star schema, the fact table sits at the center and foreign keys point out to dimension tables. When drawn as an entity-relationship diagram the shape resembles a star. Each dimension table is fully denormalized — all attributes of the entity are in one flat table, even if that produces some redundancy.

Star schemas prioritize query speed and simplicity over storage efficiency.

Structure

         DimDate
            |
DimCustomer — FactSales — DimProduct
            |
        DimStore

• Fact table: Contains foreign keys to all dimension tables + numeric measures (revenue, quantity, cost)
• Dimension tables: Contain descriptive attributes (customer name, address, product category, store region)

Why Denormalize?

Joining is expensive. A star schema minimizes joins: typically one pass through the fact table plus simple lookups into dimension tables. OLAP queries (GROUP BY, aggregate) run far faster than against a normalized 3NF schema.

Snowflake Schema

A snowflake schema normalizes dimension tables into sub-dimensions (e.g., City → State → Country as separate tables). Saves disk space, but adds joins and hurts query performance. Rarely preferred in modern analytics.

Constellation Schema (Bus Schema)

Multiple fact tables sharing conformed dimension tables. This is the standard enterprise data warehouse design. The conformed dimensions act as the integration layer — any fact table using the same DimDate or DimCustomer can be joined together.

Grain

Every fact table has a declared grain: the atomic event each row represents. Example grains:

• “One row per individual retail transaction line item”
• “One row per customer per calendar month”
• “One row per insurance claim at each processing stage”

Mixing grains in a single fact table is one of the most common dimensional modeling errors.

Connections

• dimensional-modeling — methodology that produces star schemas
• fact-table-types — the different patterns for the center of the star
• slowly-changing-dimensions — how dimension rows handle history over time

Sources

• Dimensional Modeling — Joakim Dalby — added 2026-04-12