02. Implementing SCD Type 2
️ What is SCD Type 2?
Slowly Changing Dimension (SCD) Type 2 is a technique to track historical changes in data by creating a new record every time a change occurs.
This ensures historical accuracy for analytical reporting.
Example use case:
If a customer's email changes, we retain the old email with a validity period and insert the new email as a new record.
Why SCD Type 2?
Keeps full history of attribute changes
Useful for auditing and trend analysis
Essential for data warehouses and dimensional modeling
How to Implement SCD Type 2 in dbt?
In dbt, we use Snapshots to implement SCD Type 2.
Snapshots automatically handle:
- Valid-from timestamp
- Valid-to timestamp
- Detecting changes
Example Snapshot for SCD Type 2
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='check',
check_cols=['first_name', 'last_name', 'email']
)
}}
select * from {{ source('app', 'customers') }}
{% endsnapshot %}
Key Parameters Explained:
-
unique_key→ Primary key of the source table (e.g.,customer_id) -
strategy:check→ Compares column values for changestimestamp→ Uses anupdated_atcolumn
-
check_cols→ Columns to monitor for changes
How dbt Stores Historical Versions
dbt automatically adds these columns:
dbt_valid_from→ When the version became activedbt_valid_to→ When the version expired (null if current)dbt_updated_at→ Last updated timestamp
Running the Snapshot:
dbt snapshot
dbt will:
- Insert a new record if any
check_colschange - Update
dbt_valid_tofor the old record
Query Example for Historical Data
select * from snapshots.customers_snapshot
where customer_id = 123
order by dbt_valid_from desc;
Best Practices:
Always include unique_key
Use check strategy for multiple column changes
Schedule snapshots regularly (daily or hourly)