Data EngineeringIntermediate16 min readUpdated March 2025

Dimensional Modeling: Star & Snowflake Schemas

Dimensional modeling organizes warehouse data into facts and dimensions for fast, intuitive analytics. Learn Kimball's star schema, snowflake schema, grain, and surrogate keys.

Facts and Dimensions

Dimensional modeling, popularized by Ralph Kimball, splits data into two table types:

- Fact tables store measurable events (sales, clicks, shipments) — numeric measures plus foreign keys to dimensions. - Dimension tables store descriptive context (customer, product, date, store) used to filter and group facts.

Star Schema

A star schema has one central fact table directly joined to denormalized dimension tables. It is simple, fast to query, and the most common warehouse design.

sql

CREATE TABLE dim_customer (
  customer_key  INT PRIMARY KEY,   -- surrogate key
  customer_id   VARCHAR(30),       -- natural/business key
  full_name     VARCHAR(120),
  country       VARCHAR(60),
  segment       VARCHAR(30)
);

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,      -- e.g. 20250131
  full_date  DATE,
  month      INT,
  quarter    INT,
  year       INT
);

CREATE TABLE fact_sales (
  sale_id       BIGINT,
  date_key      INT REFERENCES dim_date(date_key),
  customer_key  INT REFERENCES dim_customer(customer_key),
  product_key   INT,
  quantity      INT,
  revenue       DECIMAL(12,2)
);

Snowflake Schema

A snowflake schema normalizes dimensions into multiple related tables (e.g. product → subcategory → category). It reduces redundancy but adds joins, so queries are more complex and often slower. Most modern teams prefer star schemas because storage is cheap and simplicity wins.

Design Steps and Key Concepts

Kimball's four-step process and essential ideas:

Pick the business process — e.g. order fulfilment.
Declare the grain — exactly what one fact row represents (one order line per day).
Choose the dimensions — who, what, where, when.
Identify the facts — numeric, additive measures.
Surrogate keys — warehouse-generated integers that insulate from source key changes.

Key Takeaways

Facts hold measures; dimensions hold descriptive context.
Star schemas favor query simplicity and speed; snowflakes favor normalization.
Always declare the grain of a fact table before designing it.
Use surrogate keys to decouple the warehouse from source systems.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com