Lesson 5 of 11 · 24 min
Slowly changing dimensions
By the end of this lesson you can recognise SCD type 1 and type 2 dimensions, report a fact by the attribute valid at the time of the event or by the current attribute, look up a version as of any date, and count entities rather than versions.
Dimension attributes change: customers move, products are re-categorised, sales reps change territory. A slowly changing dimension (SCD) is a dimension with a policy for those changes. The common types:
- Type 0, keep the original: the value never changes.
signup_dateis type 0. - Type 1, overwrite: the row is updated in place and history is lost. Fine for corrections (a misspelt name).
- Type 2, add a new row: the old row is closed and a new version is inserted with a new surrogate key. History is preserved.
- Type 3, keep the previous value in an extra column (
previous_city). Rare, and limited to one change.
dim_customer treats city and region as type 2. Each version has valid_from and valid_to (both inclusive), the current version has valid_to = '9999-12-31' and is_current = true, and every version of the same person shares the natural key customer_id.
This lesson, its 5 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons