Stage 2 · Lesson 7 of 9

Slowly changing dimensions

Details in a dimension change over time. A customer moves city, or a product changes category. (SCD) are the standard ways to handle this.

Type 1 overwrites the old value, so even reports about the past show the new value. Type 2 adds a new row for each change. The row has valid_from and valid_to dates and a flag for the current version.

That way, each sale joins to the version that was true at the time of the sale. Type 3 keeps only one extra column with the previous value. Type 2 is the one you will build most often, and do it for you. For each attribute, decide whether its history matters to the business.

Common trapbuilding Type 2 everywhere, then joining facts to all versions of a customer and counting revenue twice.

A question to check yourself, an AI practice session and 1 cheat sheet entry 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