Modeling for analytics

Pro lesson Intermediate

Tracking history

Slowly changing dimensions: remember what was true at the time.

10 min9 hands-on steps

In this lesson you will

  • See how overwriting a dimension quietly rewrites past reports
  • Keep history with Type 2 rows, and apply a change safely
  • Report "as it was" and "as it is now" from the same table

Words you'll meet

Slowly changing dimension
A dimension whose descriptions change now and then, like a customer's city.
Type 1
Handle a change by overwriting the old value. Simple, but the old value is gone.
Type 2
Handle a change by closing the old row and adding a new one, so every version is kept.
valid_from / valid_to
The first and last day a Type 2 row was true. is_current marks the row that is true today.
BETWEEN
x BETWEEN a AND b is true when x is from a to b, both ends included.
As-of join
A join that matches each fact to the version of the dimension row that was true on the fact's date.

The steps

Each step teaches one idea, then has you try it hands-on in your browser, and checks your work.

  1. 1The quarter is closed
  2. 2Ada moves
  3. 3The past changed
  4. 4Type 1 or Type 2?
  5. 5Read the history
  6. 6Apply a change, Type 2
  7. 7As it was
  8. 8As it is now
  9. 9Challenge

This lesson is part of LearnDE Pro

$12 a month or $100 a year: every lesson, spaced-repetition flashcards, and Learn by teaching. Refund on request within 14 days.