Skip to main content

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 changes
    • timestamp → Uses an updated_at column
  • check_cols → Columns to monitor for changes


How dbt Stores Historical Versions​

dbt automatically adds these columns:

  • dbt_valid_from → When the version became active
  • dbt_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_cols change
  • Update dbt_valid_to for 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)