Star Schema vs Snowflake Schema: Data Modeling for Analytics

Star schema vs snowflake schema is a choice about how much to normalize your dimension tables. In a star schema, a central fact table joins directly to wide, denormalized dimension tables, which keeps queries simple and fast. In a snowflake schema, those dimensions are normalized into multiple related tables, which reduces redundancy at the cost of more joins. For most analytics warehouses today, the star schema is the sensible default, with selective snowflaking where it clearly helps.

What is dimensional modeling?

Dimensional modeling is a technique for organizing analytical data around business processes, such as orders, shipments, or page views. It splits data into two kinds of tables:

• Fact tables record measurable events. Each row is one occurrence of the process, with numeric measures like quantity, amount, or duration, plus foreign keys to dimensions.
• Dimension tables hold descriptive context: who, what, where, and when. Examples include customer, product, store, and date.

Analysts filter and group by dimension attributes and aggregate fact measures. "Revenue by product category by month" is a textbook dimensional query: revenue comes from the fact table, category from the product dimension, and month from the date dimension.

What is the grain of a fact table?

The grain is the precise definition of what one row in a fact table represents. For example, "one row per order line item" or "one row per product per store per day." Declaring the grain is the most important modeling decision because it determines which measures and dimensions make sense.

Mixing grains in one table, such as storing order-level shipping cost on line-item rows, leads to double counting when people sum it. When two measures have different natural grains, they usually belong in separate fact tables.

What is a star schema?

A star schema places a fact table at the center with dimension tables around it, each joined by a single key. Dimensions are denormalized: a product dimension carries its category and brand names directly instead of pointing to separate category and brand tables.

               dim_date
                  |
dim_customer -- fct_sales -- dim_product
                  |
               dim_store

Every analytical query is at most one join away from the fact table for each dimension, which makes SQL easy to write and gives query optimizers a predictable pattern.

What is a snowflake schema?

A snowflake schema normalizes dimensions into sub-dimensions. The product dimension might reference a subcategory table, which references a category table, which references a department table. Drawn out, the diagram branches like a snowflake.

               dim_date
                  |
dim_customer -- fct_sales -- dim_product -- dim_subcategory -- dim_category
     |
dim_geography

This removes repeated text values and keeps each attribute in exactly one place, similar to the goals of normalization in operational databases.

Star schema vs snowflake schema compared

Building a star schema in SQL

Here is a minimal star schema for retail sales at the grain of one row per order line:

CREATE TABLE dim_date (
  date_key      INT PRIMARY KEY,        -- e.g. 20260714
  full_date     DATE NOT NULL,
  year          SMALLINT NOT NULL,
  month         SMALLINT NOT NULL,
  month_name    VARCHAR(10) NOT NULL,
  day_of_week   VARCHAR(10) NOT NULL
);

CREATE TABLE dim_product (
  product_key   INT PRIMARY KEY,        -- surrogate key
  product_id    VARCHAR(20) NOT NULL,   -- natural key from source
  product_name  VARCHAR(200) NOT NULL,
  brand         VARCHAR(100),
  category      VARCHAR(100),
  subcategory   VARCHAR(100)
);

CREATE TABLE dim_customer (
  customer_key  INT PRIMARY KEY,
  customer_id   VARCHAR(20) NOT NULL,
  customer_name VARCHAR(200),
  city          VARCHAR(100),
  region        VARCHAR(100),
  segment       VARCHAR(50)
);

CREATE TABLE fct_sales (
  order_id      VARCHAR(20) NOT NULL,
  line_number   INT NOT NULL,
  date_key      INT NOT NULL REFERENCES dim_date (date_key),
  product_key   INT NOT NULL REFERENCES dim_product (product_key),
  customer_key  INT NOT NULL REFERENCES dim_customer (customer_key),
  quantity      INT NOT NULL,
  net_amount    DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (order_id, line_number)
);

And a typical analytical query against it:

SELECT
  d.year,
  d.month,
  p.category,
  c.region,
  SUM(f.quantity)   AS units,
  SUM(f.net_amount) AS revenue
FROM fct_sales f
JOIN dim_date d     ON f.date_key = d.date_key
JOIN dim_product p  ON f.product_key = p.product_key
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE d.year = 2026
GROUP BY d.year, d.month, p.category, c.region
ORDER BY d.year, d.month, revenue DESC;

In a snowflake version, p.category would require two extra joins through subcategory and category tables. If join behavior is unfamiliar, the SQL joins guide covers it in depth.

Why surrogate keys matter

Notice the dimensions use surrogate keys (product_key) alongside natural keys from the source (product_id). Surrogate keys decouple the warehouse from source system identifiers and make it possible to track history. With a slowly changing dimension of type 2, when a customer moves regions you insert a new dimension row with a new surrogate key and validity dates, so past sales stay attributed to the old region while new sales use the new one.

When to use a star schema

Choose a star schema when:

• Analysts and BI tools query the model directly and simplicity matters.
• Dimensions are small to moderate in size, so redundancy has little storage impact.
• You run on a columnar warehouse, where repeated values in a column compress well.
• You want predictable query performance with minimal joins.

Columnar compression is a big reason star schemas dominate modern warehouses. Repeating a category name across many product rows usually costs little once compressed, so the storage argument for snowflaking is weaker than it once was.

When to use a snowflake schema

Snowflaking can still be the right call when:

• A dimension is very large and some attributes are shared by huge numbers of rows and change independently.
• Hierarchies are deep and maintained by separate teams, such as a product taxonomy managed as its own reference dataset.
• The same sub-dimension, like geography, is reused across many dimensions and you want a single source of truth.
• Storage or update costs on a row-oriented database are a real constraint.

A pragmatic middle ground is to keep normalized tables in an upstream layer and publish denormalized star-schema marts for consumption. You get tidy maintenance upstream and simple querying downstream. This pairs well with ETL vs ELT workflows where the warehouse builds marts from staged data.

Key takeaways

• Dimensional models split data into fact tables (measurable events) and dimension tables (descriptive context).
• Always declare the grain of a fact table before adding measures or dimensions.
• Star schemas use denormalized dimensions for simple, fast queries and are the default for most analytics.
• Snowflake schemas normalize dimensions to reduce redundancy but add joins and complexity.
• Use surrogate keys and slowly changing dimensions to preserve history accurately.

Frequently asked questions

Is a star schema faster than a snowflake schema?

Usually, because queries need fewer joins and optimizers handle the pattern well. On modern columnar warehouses the difference can be small for simple queries, but star schemas remain easier to write and reason about.

Is a star schema normalized or denormalized?

A star schema is denormalized at the dimension level. Fact tables remain narrow with foreign keys and measures, while dimensions deliberately repeat attributes like category or region to avoid extra joins.

Can I mix star and snowflake schemas?

Yes. Many warehouses use mostly star-shaped marts and snowflake only a few dimensions, such as a shared geography table. The goal is simplicity for consumers, not purity of either pattern.

What is a fact table in data modeling?

A fact table stores measurable events at a declared grain, such as one row per order line. It contains numeric measures and foreign keys to dimension tables that describe the context of each event.