SQL Academy · Lesson

Star and Snowflake Schemas

Model data for fast analytics.

What Is a Data Warehouse Schema?

In a transactional (OLTP) database you normalize data to avoid redundancy. In a data warehouse you often denormalize intentionally — trading storage for query speed. Two classic patterns for organizing warehouse tables are the Star Schema and the Snowflake Schema.

Both revolve around a central fact table surrounded by dimension tables. The difference is how far you normalize those dimensions.

Fact Tables and Dimension Tables

A fact table stores measurable events — sales, clicks, shipments. It is wide (many rows) and contains numeric measures plus foreign keys to dimensions.

A dimension table describes the context of each event: who, what, when, where. Dimensions are narrower (fewer rows) but richer in descriptive columns.

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  revenue      NUMERIC(12, 2) NOT NULL
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_price   NUMERIC(10, 2)
);

All lessons in this course

← Back to SQL Academy