Data modeling

Sources: Kimball Ch. 2–7 · DDIA Ch. 2–3

Always ask: Who queries this, how often, with what tools? Kimball: model the business process, not the source system layout.

Kimball — dimensional modeling basics

Fact tables

• One row = one measurement at a declared grain (e.g. one line item on an order)
• Contain foreign keys to dimensions + numeric facts (amounts, counts)
• Grain must be atomic and consistent — never mix grains in one fact table

Dimension tables

• Descriptive context: who, what, where, when, why
• Wide, denormalized, human-readable (often include labels, hierarchies)
• Examples: date, customer, product, store

Star schema

• One fact surrounded by dimensions — looks like a star
• Snowflake = normalized dimensions (more joins, less redundancy)
• Star is preferred for simplicity and BI tool performance

Grain — the most important decision

Grain answers: "Exactly what does one row represent?" Get this wrong and every metric is wrong. Examples:

• Order line item · Daily store sales · Individual web page view

Conformed dimensions & bus architecture (Kimball)

• Conformed dimension — same dimension table (same keys, attributes) reused across multiple fact tables
• Enables drill-across — compare metrics from different processes on shared dimensions (e.g. same Date dim for sales + inventory facts)
• Enterprise bus matrix — grid of business processes × conformed dimensions; integration blueprint

Slowly changing dimensions (SCD)

• Type 1 — overwrite (no history)
• Type 2 — new row with effective dates + current flag (full history)
• Type 3 — limited history column (previous value)

DDIA perspective — schema on read vs write

• Schema-on-write (relational warehouse) — enforce structure at load; safe, governed analytics
• Schema-on-read (data lake) — structure applied at query time; flexible, messier

Star schema vs OBT (modern warehouses)

• Star (Kimball) — normalized dims, many joins; best for consistency and BI semantics
• One Big Table — pre-joined wide table; eliminates join cost on MPP engines; duplication acceptable when storage is cheap