Dimensional Modelling for Analytics Engineers
Dimensional modelling organises analytics data into fact tables, which record events or measurements, and dimension tables, which describe the who, what and where. Deciding the grain, the exact meaning of one fact row, comes first, and slowly changing dimensions preserve history when attributes change.
Stage: Data Modelling · Part of the Analytics Engineer roadmap · Last reviewed 19 November 2026
What to learn in Dimensional Modelling
- State the grain of a fact table in a single sentence
- Separate measures from descriptive attributes into facts and dimensions
- Design a star schema for an orders dataset
- Implement a type 2 slowly changing dimension
- Spot a fan-out caused by joining at the wrong grain
Dimensional Modelling interview questions
What is the grain of a fact table and why does it matter?
The grain defines what one row represents, such as one order line. Everything else in the table must be true at that level. A wrong or mixed grain causes double counting and incorrect joins.
What is a type 2 slowly changing dimension?
Type 2 keeps history by adding a new row each time a tracked attribute changes, with valid-from and valid-to dates and a current flag. Facts can then join to the version that was true at the time.
Where Dimensional Modelling fits in the Analytics Engineer roadmap
Dimensional Modelling belongs to the Data Modelling stage (intermediate level), which takes about 5 hours. Structure data for people and machines.
Other units in this stage
Back to the full Analytics Engineer roadmap