Star schema example (retail sales)

A star schema is the classic design for analytics: one fact table of measurements in the middle, surrounded by dimension tables that describe them. This example is a retail sales star in the Kimball style, the kind of model that sits in a Snowflake, Databricks or BigQuery warehouse behind BI dashboards.

#Design notes

• Declare the grain first. fact_sales has one row per order line. Every measure (quantity, sales, cost, margin) is true at that grain and adds up across any dimension.
• Surrogate keys everywhere. Dimensions have integer *_key columns independent of source system IDs, which keep their natural keys (sku, customer_id) as ordinary attributes.
• The date dimension is a table, not a function. dim_date has a row per day with calendar and fiscal attributes, keyed YYYYMMDD.
• Customer history is kept (SCD type 2). When a customer's segment or city changes, a new row with new valid_from/valid_to dates is added; facts point at the version current at the time of sale.
• No empty foreign keys. Sales without a promotion point at a "No promotion" row (key 0), so joins never drop rows.
• The order number is a degenerate dimension: kept on the fact for drill-through, with no table of its own.
• Dimensions are denormalized: the product carries its category and subcategory directly, so reports need one join per dimension.

To extend it: add fact_inventory_snapshot (a periodic snapshot fact), fact_returns, or a dim_employee for the cashier. Model the normalized source system first; see Conceptual, logical and physical data models.

#Diagram

Retail Sales Star Schema: 6 entities, 0 reference lists and 5 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Customer

Customers as a type 2 slowly changing dimension: a change to segment or city adds a new row, so history reports use the values at the time of sale.

#Date

One row per calendar day, with every calendar and fiscal attribute reports filter on.

#Product

Products, flattened with their category hierarchy so reports need no joins beyond the star.

#Promotion

Promotions applied to a sale, with a "No promotion" row (key 0) so the fact never has empty keys.

#Sales (fact)

One row per order line: the grain of the star. Measures are additive across every dimension.

#Store

Stores and the online channel, with their geography.

#Relationships

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- Retail Sales Star Schema — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

CREATE TABLE fact_sales (
  sales_key BIGINT NOT NULL,
  date_key INTEGER NOT NULL,
  product_key INTEGER NOT NULL,
  store_key INTEGER NOT NULL,
  customer_key INTEGER NOT NULL,
  promotion_key INTEGER NOT NULL,
  order_number TEXT NOT NULL,
  line_number INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price NUMERIC(12,2) NOT NULL,
  discount_amount NUMERIC(12,2) NOT NULL,
  sales_amount NUMERIC(12,2) NOT NULL,
  cost_amount NUMERIC(12,2) NOT NULL,
  margin_amount NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (sales_key)
);
COMMENT ON TABLE fact_sales IS 'One row per order line: the grain of the star. Measures are additive across every dimension.';
COMMENT ON COLUMN fact_sales.sales_key IS 'Surrogate key of the fact row.';
COMMENT ON COLUMN fact_sales.date_key IS 'Order date, as YYYYMMDD.';
COMMENT ON COLUMN fact_sales.customer_key IS 'The customer version current at the time of sale.';
COMMENT ON COLUMN fact_sales.promotion_key IS '0 = no promotion.';
COMMENT ON COLUMN fact_sales.order_number IS 'Degenerate dimension: the source order, with no table of its own.';
COMMENT ON COLUMN fact_sales.sales_amount IS 'quantity × unit_price − discount.';
COMMENT ON COLUMN fact_sales.margin_amount IS 'sales_amount − cost_amount.';

CREATE TABLE dim_date (
  date_key INTEGER NOT NULL,
  full_date DATE NOT NULL,
  day_of_week TEXT NOT NULL,
  month_name TEXT NOT NULL,
  calendar_month INTEGER NOT NULL,
  calendar_quarter INTEGER NOT NULL,
  calendar_year INTEGER NOT NULL,
  fiscal_period TEXT NOT NULL,
  is_weekend BOOLEAN NOT NULL,
  is_holiday BOOLEAN NOT NULL,
  PRIMARY KEY (date_key)
);
COMMENT ON TABLE dim_date IS 'One row per calendar day, with every calendar and fiscal attribute reports filter on.';
COMMENT ON COLUMN dim_date.date_key IS 'YYYYMMDD.';

CREATE TABLE dim_product (
  product_key INTEGER NOT NULL,
  sku TEXT NOT NULL,
  product_name TEXT NOT NULL,
  brand TEXT,
  category TEXT NOT NULL,
  subcategory TEXT NOT NULL,
  size TEXT,
  color TEXT,
  list_price NUMERIC(12,2),
  PRIMARY KEY (product_key)
);
COMMENT ON TABLE dim_product IS 'Products, flattened with their category hierarchy so reports need no joins beyond the star.';
COMMENT ON COLUMN dim_product.product_key IS 'Surrogate key.';
COMMENT ON COLUMN dim_product.sku IS 'Natural key from the source system.';

CREATE TABLE dim_store (
  store_key INTEGER NOT NULL,
  store_code TEXT NOT NULL,
  store_name TEXT NOT NULL,
  channel TEXT NOT NULL,
  city TEXT,
  region TEXT NOT NULL,
  country TEXT NOT NULL,
  opened_on DATE,
  PRIMARY KEY (store_key)
);
COMMENT ON TABLE dim_store IS 'Stores and the online channel, with their geography.';
COMMENT ON COLUMN dim_store.channel IS 'Store or online.';

CREATE TABLE dim_customer (
  customer_key INTEGER NOT NULL,
  customer_id TEXT NOT NULL,
  customer_name TEXT NOT NULL,
  segment TEXT NOT NULL,
  city TEXT,
  country TEXT,
  valid_from DATE NOT NULL,
  valid_to DATE NOT NULL,
  is_current BOOLEAN NOT NULL,
  PRIMARY KEY (customer_key)
);
COMMENT ON TABLE dim_customer IS 'Customers as a type 2 slowly changing dimension: a change to segment or city adds a new row, so history reports use the values at the time of sale.';
COMMENT ON COLUMN dim_customer.customer_key IS 'Surrogate key of this version.';
COMMENT ON COLUMN dim_customer.customer_id IS 'Natural key; the same across versions.';
COMMENT ON COLUMN dim_customer.valid_to IS '9999-12-31 for the current version.';

CREATE TABLE dim_promotion (
  promotion_key INTEGER NOT NULL,
  promotion_code TEXT NOT NULL,
  promotion_name TEXT NOT NULL,
  discount_type TEXT NOT NULL,
  start_date DATE,
  end_date DATE,
  PRIMARY KEY (promotion_key)
);
COMMENT ON TABLE dim_promotion IS 'Promotions applied to a sale, with a "No promotion" row (key 0) so the fact never has empty keys.';
COMMENT ON COLUMN dim_promotion.discount_type IS 'Percent, amount or none.';

-- Keys between tables
ALTER TABLE dim_date ADD CONSTRAINT uq_dim_date_full_date UNIQUE (full_date);
ALTER TABLE fact_sales ADD CONSTRAINT fk_sold_on FOREIGN KEY (date_key) REFERENCES dim_date (date_key);
ALTER TABLE fact_sales ADD CONSTRAINT fk_sale_of FOREIGN KEY (product_key) REFERENCES dim_product (product_key);
ALTER TABLE fact_sales ADD CONSTRAINT fk_sold_at FOREIGN KEY (store_key) REFERENCES dim_store (store_key);
ALTER TABLE fact_sales ADD CONSTRAINT fk_sold_to FOREIGN KEY (customer_key) REFERENCES dim_customer (customer_key);
ALTER TABLE fact_sales ADD CONSTRAINT fk_under_promotion FOREIGN KEY (promotion_key) REFERENCES dim_promotion (promotion_key);

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: Retail Sales Star Schema
---
%% Made with EntityModel.com
erDiagram
  fact_sales {
    bigint sales_key PK "Surrogate key of the fact row."
    int date_key FK "Order date, as YYYYMMDD."
    int product_key FK "Product Key"
    int store_key FK "Store Key"
    int customer_key FK "The customer version current at the time of sale."
    int promotion_key FK "0 = no promotion."
    string order_number "Degenerate dimension: the source order, with no table of its own."
    int line_number "Line Number"
    int quantity "Quantity"
    decimal unit_price "Unit Price"
    decimal discount_amount "Discount Amount"
    decimal sales_amount "quantity × unit_price − discount."
    decimal cost_amount "Cost Amount"
    decimal margin_amount "sales_amount − cost_amount."
  }
  dim_date {
    int date_key PK "YYYYMMDD."
    date full_date "Full Date"
    string day_of_week "Day Of Week"
    string month_name "Month Name"
    int calendar_month "Calendar Month"
    int calendar_quarter "Calendar Quarter"
    int calendar_year "Calendar Year"
    string fiscal_period "Fiscal Period"
    boolean is_weekend "Is Weekend"
    boolean is_holiday "Is Holiday"
  }
  dim_product {
    int product_key PK "Surrogate key."
    string sku "Natural key from the source system."
    string product_name "Product Name"
    string brand "Brand"
    string category "Category"
    string subcategory "Subcategory"
    string size "Size"
    string color "Color"
    decimal list_price "List Price"
  }
  dim_store {
    int store_key PK "Store Key"
    string store_code "Store Code"
    string store_name "Store Name"
    string channel "Store or online."
    string city "City"
    string region "Region"
    string country "Country"
    date opened_on "Opened On"
  }
  dim_customer {
    int customer_key PK "Surrogate key of this version."
    string customer_id "Natural key; the same across versions."
    string customer_name "Customer Name"
    string segment "Segment"
    string city "City"
    string country "Country"
    date valid_from "Valid From"
    date valid_to "9999-12-31 for the current version."
    boolean is_current "Is Current"
  }
  dim_promotion {
    int promotion_key PK "Promotion Key"
    string promotion_code "Promotion Code"
    string promotion_name "Promotion Name"
    string discount_type "Percent, amount or none."
    date start_date "Start Date"
    date end_date "End Date"
  }
  fact_sales }o--|| dim_date : "Sold On"
  fact_sales }o--|| dim_product : "Of Product"
  fact_sales }o--|| dim_store : "Sold At"
  fact_sales }o--|| dim_customer : "Sold To"
  fact_sales }o--|| dim_promotion : "Under"

#Frequently asked questions

#What is a star schema?

A star schema is a dimensional data model with a central fact table holding numeric measurements at a declared grain, joined by foreign keys to denormalized dimension tables (date, product, customer, store) that describe the context. Drawn as a diagram, the dimensions surround the fact like the points of a star.

#What is the difference between a star schema and a snowflake schema?

In a star schema each dimension is a single denormalized table. In a snowflake schema dimensions are normalized into several tables (product, then subcategory, then category). Stars are simpler and faster to query; snowflakes save some storage and enforce hierarchies.

#What is a slowly changing dimension (SCD) type 2?

A way to keep history in a dimension: instead of overwriting a changed attribute, add a new row with its own surrogate key and validity dates, and mark it current. Facts keep pointing at the version that was true when they happened.