Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 36 ยท Modeling and Warehousing

Dimensional Modeling and the Star Schema

Design a star schema with fact and dimension tables: choose the grain, build surrogate keys, load a fact table and answer business questions with joins.

Raw data arrives shaped like the source system: normalized tables built for an app, nested JSON, event logs. Analysts need something different, a model where common questions ("revenue by month and product category") are simple, fast and consistent. Dimensional modeling, popularized by Ralph Kimball, is the best-known answer, and the star schema is its basic shape.

This lesson builds a small star schema from scratch with Python and SQLite, so you can see every table, key and join.

Facts and dimensions

  • A fact table records measurable events: sales, clicks, shipments. Each row holds numeric measures (quantity, amount) and foreign keys to dimensions.
  • A dimension table describes the context: who, what, where, when. It holds descriptive attributes (customer name, product category, calendar month) used to filter and group.

Drawn with the fact in the middle and dimensions around it, the shape looks like a star:

flowchart TB
  F["fact_sales<br/>date_key, product_key, customer_key, quantity, amount"]
  D1["dim_date"] --> F
  D2["dim_product"] --> F
  D3["dim_customer"] --> F
  D4["dim_store"] --> F

Step 1: declare the grain

The grain is the exact meaning of one row in the fact table, and it comes first. Examples: "one row per order line", "one row per page view", "one row per account per day". Every later decision follows from the grain, and mixing grains in one table is the classic modeling bug (double-counted totals).

For our example the grain is one row per order line.

Step 2: choose dimensions and measures

For order lines: date, product, customer. Measures: quantity and amount. Measures should be additive (sums make sense) when possible. A price is not additive, but amount = quantity * unit_price is. Ratios and percentages should be computed from additive parts, never stored and averaged.

Step 3: surrogate keys

Dimension tables get surrogate keys: meaningless integers generated by the warehouse, separate from the source's business key (customer_id in the CRM). They keep joins fast, isolate you from source key changes and enable history tracking (next lesson).

Python Build the star schema

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every question has the same shape: join the fact table to the dimensions you need, group by dimension attributes, aggregate the measures. That uniformity is the benefit.

Loading a fact table: key lookup

ETL code has to turn business keys from the source into surrogate keys before inserting facts. A missing dimension row is a data-quality problem you must handle on purpose.

Python Resolve surrogate keys while loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The usual pattern is a reserved -1 "Unknown" row in every dimension. The fact still loads, the problem is counted, and a later job repairs the key when the dimension row arrives (a late-arriving dimension).

Kinds of fact tables

Type Grain Example
Transaction One row per event Order line, payment
Periodic snapshot One row per entity per period Account balance at day end
Accumulating snapshot One row per process, updated as it progresses Order: ordered, shipped, delivered dates
Factless Events with no measures Student attended class

Useful dimension patterns

  • Date dimension: pre-built calendar with year, quarter, month, weekday, holiday flags and fiscal periods. Never compute calendar logic in every query.
  • Conformed dimensions: the same customer or date dimension shared by several fact tables, so results from different business processes line up.
  • Degenerate dimension: an identifier such as an invoice number kept in the fact table with no dimension of its own.
  • Junk dimension: a small table that combines low-cardinality flags.
  • Role-playing dimension: one date dimension used as order date, ship date and due date through different foreign keys.

Star vs snowflake

A snowflake schema normalizes dimensions further (product points to category, category to department). It saves a little storage but adds joins. Modern columnar warehouses make storage cheap and joins costly to read, so most teams keep dimensions flat.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the grain of a fact table?

    The grain is the precise meaning of a row; all dimensions and measures must be consistent with it.
  2. 2. Why use surrogate keys in dimensions?

    Surrogate keys are warehouse-owned integers independent of source business keys.
  3. 3. A source row refers to a product that is not in dim_product yet. A good approach is to

    An Unknown member keeps the fact and makes the data issue visible and fixable.
  4. 4. Which measure is NOT safely additive?

    Summing prices makes no sense; use amount or compute averages from additive parts.

Summary

  • Declare the grain first, then dimensions and additive measures.
  • Facts hold measures and foreign keys; dimensions hold descriptive attributes with surrogate keys.
  • Resolve business keys to surrogate keys on load and use an Unknown member for misses.
  • Conformed dimensions, a date dimension and role-playing keys make the model reusable.

Next: Slowly Changing Dimensions