thinQmaster Virtual testing partner Adaptive practice exams that find your weak spots and drill them. Every answer explained. From $69
Enroll

thinQmaster

Find your exam

Five questions free, before you decide.

Step 1 · choose a vendor

In one of our camps? Your practice exams come with the tuition — they are on your study page already.

Data & Analytics · Intermediate

SCD Type 2

A slowly changing dimension method that inserts a new row for each change, with effective dates and a current-row flag, preserving full history.

Definition

Each version gets its own surrogate key, so facts link to the version that was valid when they occurred. Columns such as ValidFrom, ValidTo and IsCurrent manage versions. Implementation usually uses MERGE, Snowflake streams or Databricks APPLY CHANGES / AUTO CDC.

Example

Customer 1001 has two rows: City='Austin' (2019-2025) and City='Denver' (2025-current).

Exam tip

Type 2 requires a surrogate key; the natural key repeats across versions.

Related terms