Star schema

A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.

Overview

A star schema is the most common layout for analytical data warehouses. One fact table sits at the center, holding foreign keys to each dimension plus numeric measures (revenue, quantity, duration). Around it sit dimension tables — denormalized, descriptive tables like dim_customer, dim_product, and dim_date — each joined to the fact table by a single foreign key. Every dimension is exactly one join away from the fact table, which is what gives the schema its star shape and what makes it fast and easy to query.

Ralph Kimball popularized the star schema as the core deliverable of dimensional modeling: business users can browse dimension attributes, filter, and roll measures up or down without navigating a maze of joins. Because dimensions are denormalized (a dim_product table might repeat category and department text on every row instead of normalizing them into separate tables), read queries stay simple — a handful of joins instead of dozens — at the cost of some storage and update complexity, which is an acceptable trade for analytics workloads that are read-heavy.

Related terms

Dimension table →

A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.

Fact constellation schema →

A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.

Snowflake schema →

A snowflake schema is a dimensional model where dimension tables are normalized into multiple related sub-tables instead of one flat table, reducing redundancy at the cost of extra joins.

Fact table →

A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.

Dimensional modeling →

Dimensional modeling is a data warehouse design methodology, developed by Ralph Kimball, that organizes data into fact tables (measures) and dimension tables (descriptive context) optimized for business-user querying.

Conformed dimension →

A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.

FAQS

A star schema keeps dimension tables denormalized (flat), so each dimension joins to the fact table directly. A snowflake schema normalizes dimensions into related sub-tables, trading query simplicity for reduced redundancy.

Star schemas minimize the number of joins needed to answer analytical questions, which makes queries easier to write and faster to execute — the priorities for reporting and BI workloads, as opposed to the write-optimized, normalized models used in transactional systems.