Stage 2 · Lesson 5 of 9

Facts, dimensions and grain

Dimensional modelling is a way to design a warehouse, made popular by Ralph . It splits tables into two kinds. A records events or measurements, such as order lines with a quantity and an amount.

describe the context around them: the customer, the product, the date, the store. The most important decision is the : exactly what one row of a fact table stands for, such as one line of one order. Decide the grain first.

Then choose dimensions and measures (the numbers) that fit it. Some measures are additive: you can sum them across everything. Some are , like account balances, which you cannot sum across time. Some are non-additive, like ratios.

Common trapmixing grains in one table, such as order totals next to order lines, which makes every sum wrong.

A question to check yourself, an AI practice session and 3 cheat sheet entries come with the full roadmap. Unlock

Resources for this stage

Start with

CourseFree
SQL Tutorial for Data Analysis (formerly Mode)
ThoughtSpot · Beginner
Its advanced section covers window functions, performance tuning and pivoting on realistic data

Practise

LeetCode SQL 50
Fifty graded problems; the window function and aggregation ones match what pipeline SQL needs
Select Star SQL
An interactive book on one real dataset that ends with window functions and self joins

Keep handy

Kimball Dimensional Modeling Techniques
The original definitions of grain, conformed dimensions and SCD types, straight from the source
roadmap.sh: SQL
Check which SQL topics you still lack before moving on

Also worth your time

Data Warehouse Design: Schemas, Steps, and Best Practices (opens in a new tab)Read
Star and snowflake schemas, facts and dimensions, and the steps of a warehouse design in one read