Star Schema Diagram

This sample is a retail star schema: a central fact_sales table records every sold order line, and four dimension tables (date, product, store, customer) describe the who, what, where and when. It is the standard shape for a data warehouse or BI data mart.

1. The Diagram

Retail Sales: Star Schema fact_sales PK sales_line_id BIGINT FK date_key INT FK product_key INT FK store_key INT FK customer_key INT DD order_number VARCHAR(20) quantity INT net_amount DECIMAL(12,2) discount_amount DECIMAL(12,2) cost_amount DECIMAL(12,2) dim_date PK date_key INT full_date DATE day_of_week VARCHAR month_name VARCHAR quarter SMALLINT fiscal_year SMALLINT is_holiday BOOLEAN dim_product PK product_key INT NK sku VARCHAR product_name VARCHAR brand VARCHAR category VARCHAR subcategory VARCHAR dim_store PK store_key INT NK store_code VARCHAR store_name VARCHAR city VARCHAR region VARCHAR store_type VARCHAR dim_customer PK customer_key INT NK customer_id BIGINT segment VARCHAR city VARCHAR valid_from DATE valid_to DATE is_current BOOLEAN grain: one row per product per order line additive measures: quantity, net, discount, cost

A star schema has one fact table surrounded by denormalised dimension tables. It looks like a star, hence the name.

  1. Start in the centre with fact_sales (yellow). Its grain is one row per product per order line; every design choice follows from that sentence.
  2. Four foreign keys point outward to the dimensions. They are integer surrogate keys, not source system IDs, so history and late-arriving data can be handled.
  3. The numeric columns are measures. quantity and the amounts are additive: they can be summed across any dimension.
  4. order_number is a degenerate dimension (DD): a useful label with no attributes of its own, so it lives on the fact.
  5. dim_date holds one row per day with calendar and fiscal attributes, so reports never compute quarters in SQL.
  6. dim_customer has valid_from, valid_to and is_current: a slowly changing dimension type 2 that keeps past cities and segments.
  7. Each dimension-to-fact line is one-to-many: one product row matches many sales rows. A query filters dimensions and sums the fact.

2. How to Read It

Star Schema Notation fact table dimension PK primary or surrogate key FK foreign key to a dimension NK natural (business) key from source DD degenerate dimension stored on the fact dimension 1 to fact 0..N every dimension row can match many facts

Star schemas reuse crow's foot ER notation, with a colour or label to separate facts from dimensions. Some teams draw the fact table in the middle and omit columns, showing only names and the grain; that is fine for discussions but the column version is what engineers build from.

3. Variant: Snowflaked Dimension

Snowflaked Product Dimension fact_sales FK product_key INT ... dim_product PK product_key INT product_name VARCHAR FK subcategory_key INT dim_subcategory PK subcategory_key INT name VARCHAR FK category_key INT dim_category PK category_key INT name VARCHAR Snowflaking saves space but adds joins; Kimball recommends keeping the star flat.

In a snowflake schema the product dimension is normalised into product, subcategory and category tables. It removes repeated category names, but every report now needs extra joins and BI tools struggle more. Most modern columnar warehouses compress repeated strings cheaply, so the flat star is usually preferred.

4. Pros, Cons, and When to Use

Pros

Cons

When to use

When not to use

5. As Diagrams-as-Code

Mermaid erDiagram; Mermaid has no layout hint for a star, so it will place tables in rows:

erDiagram
    dim_date     ||--o{ fact_sales : "date_key"
    dim_product  ||--o{ fact_sales : "product_key"
    dim_store    ||--o{ fact_sales : "store_key"
    dim_customer ||--o{ fact_sales : "customer_key"
    fact_sales {
        bigint sales_line_id PK
        int date_key FK
        int product_key FK
        int store_key FK
        int customer_key FK
        varchar order_number
        int quantity
        decimal net_amount
    }
    dim_customer {
        int customer_key PK
        bigint customer_id
        varchar segment
        date valid_from
        date valid_to
    }