Star schema

A central fact table (events with foreign keys and measures) surrounded by dimension tables (descriptive attributes). Simple joins, fast aggregations, easy for BI tools to understand.

fact_orders: order_id, date_key, customer_key, product_key, amount, quantity
dim_date:    date_key, year, quarter, month, day, is_weekend
dim_customer: customer_key, name, segment, region
dim_product:  product_key, name, category, brand

Slowly Changing Dimensions (SCD)

  • SCD Type 1 — overwrite. No history. Simple but loses context.
  • SCD Type 2 — add a new row with effective_date and end_date. Preserves full history. Standard choice for important dimensions.
  • SCD Type 3 — add a "previous_value" column. Tracks one prior value only.

Grain declaration

Define the grain (one row = one what?) before designing the fact table. The grain determines which dimensions attach and prevents double-counting in aggregations.