Star and Snowflake Schema

How you lay out tables in a warehouse so analysts can actually query them.

Key Points

• A fact table stores measurable events — sales, clicks, transactions. It's usually long (many rows) and narrow (mostly foreign keys plus numeric measures).
• Dimension tables store descriptive context — who, what, where, when. Customer, product, store, date.
• Star schema: fact table in the center, flat dimension tables around it. Simple, fast to query, some redundancy in dimensions.
• Snowflake schema: dimensions are normalized, split into sub-dimension tables. Less redundancy but more joins.
• The date dimension is almost always its own table and is one of the most important — it lets you slice by day, week, month, quarter, fiscal year without doing date math in every query.

What it is

Star and snowflake schemas are ways of organizing tables in a data warehouse for analytical queries. Both have a central fact table surrounded by dimension tables. The difference is whether dimension tables are normalized (snowflake) or denormalized (star).

Why it exists

Normalized relational schemas (3NF) are great for transactions but painful for analytics. A simple business question like 'total sales by product category per quarter' might need 10 joins on a normalized schema. Star schema deliberately denormalizes so that most analytical queries need only one or two joins — fast, readable, easy.

Intuition

Example

Star schema: fact_sales(sale_id, date_id, customer_id, product_id, amount). dim_customer(customer_id, name, city, segment). dim_product(product_id, name, category, brand). dim_date(date_id, date, month, quarter, year). Snowflake variant: dim_product now only has (product_id, name, category_id). A separate dim_category(category_id, category_name, department_id) table. Another dim_department(department_id, department_name) table.

Star is simpler and faster to query. Snowflake is cleaner and avoids repeating 'Electronics' across every row in the product table. In practice most warehouses use star or a light hybrid.

When to use

• Star schema when query simplicity and speed matter most — typical BI and dashboarding use cases.
• Snowflake schema when storage matters more than query speed, or when dimension tables are very large with high cardinality.
• Both when building an analytical layer on top of a normalized operational database.

Common mistakes

• Putting too many measures in dimension tables or too much context in the fact table. Keep facts in fact tables, descriptions in dimensions.
• Not creating a proper date dimension and instead storing dates as raw timestamps in the fact table — this makes time-based slicing in BI tools much harder.
• Confusing the warehouse schema with the application schema. Normalization rules for OLTP don't directly apply here.

Notes

• Slowly Changing Dimensions (SCDs) are an important extension — how do you handle a customer who moves cities? Type 1 overwrites, Type 2 adds a new row with a version, Type 3 adds a 'previous' column. Each is a deliberate design choice.
• Modern warehouses (BigQuery, Snowflake, Redshift) are columnar, which makes wide denormalized tables fast to query. This further tips the balance toward star schema in cloud environments.

In practice

When you pull data from a warehouse for analysis, you're almost certainly working with a star or snowflake schema. Knowing the structure helps you write correct queries, understand why certain columns live in certain tables, and build models in tools like dbt.

Related concepts