The dbt SCD Type 2 macro

Slowly Changing Dimension Type 2 is the pattern for keeping history in a dimension: instead of overwriting a row when an attribute changes, you close the old version and open a new one, so every fact stays joinable to the dimension state that was true when the fact occurred. Kirimana ships this as a dbt macro — kiri_scd2_dim — so you declare the grain and the attributes, and the macro renders the full incremental body.

The macro

kiri_scd2_dim lives in the dbt-bridge macro set that Kirimana wraps into your project. It returns the body of a dimension model that tracks history through three bookkeeping columns:

• valid_from — when this version became current (taken from the source’s ordering column).
• valid_to — when this version was superseded; NULL while current.
• is_current — true for exactly one version per business key.

You call it from a gold dimension model configured as an incremental materialisation:

{{ config(
     materialized='incremental',
     schema='gold',
     unique_key='customer_key',
     incremental_strategy='delete+insert'
) }}

{{ kiri_scd2_dim(
     source_ref=ref('silver_customer'),
     business_keys=['customer_id'],
     attributes=['email', 'full_name', 'country_code'],
     key_column='customer_key',
     ordering_column='_kiri_ingested_at',
     surrogate_strategy='hash'
) }}

Configuration

The macro takes the following arguments:

Argument	Purpose
source_ref	The upstream model to historise — usually a ref() to a silver model.
business_keys	The natural key(s) that identify a dimension member across versions.
attributes	The columns whose changes create a new version.
key_column	The surrogate-key column name emitted per version.
ordering_column	The column that orders versions per key. Default _kiri_ingested_at.
surrogate_strategy	How the surrogate key is generated: hash is the default.

The materialisation itself is your responsibility in the config() block: materialized='incremental', an incremental_strategy of delete+insert, and a unique_key matching your key_column. On Databricks the delete+insert strategy is the assumed path; a merge-based strategy is not yet implemented.

How change detection works

The macro computes a hashdiff over the declared attributes — a NULL-safe concatenation routed through the adapter-dispatched hash function — so it can decide, per business key, whether anything the dimension cares about actually changed.

• First run. Every distinct business key gets exactly one current row: valid_from set from the ordering column, valid_to NULL, is_current true. Where the source has multiple rows for a key, the latest by ordering column wins.
• Incremental run. For each business key, the macro compares the latest source hashdiff against the current stored version:
  • A new business key is inserted as a current version.
  • An unchanged key (identical hashdiff) is a no-op.
  • A changed key closes the stale current row — setting its valid_to to the new version’s valid_from and is_current to false — and inserts the new version as current.

The surrogate key changes per version. With the hash strategy the key incorporates valid_from, so each epoch of a member gets a distinct key and a fact can join to the exact version that was live at fact time.

Late-arriving and out-of-order data

The ordering_column on the source is the single source of truth for valid_from. Source rows older than the most recent existing version for a key are skipped — they would contradict the timeline the dimension has already recorded. This keeps a late or out-of-order load from silently rewriting history.

How gold SCD2 dimensions declare it

You rarely write the whole config() + macro call by hand. When you draft a gold dimension from a ReportingGoal, ask for SCD Type 2 explicitly:

kiri suggest gold --goal customer_360 --dim customer --scd-type type_2 --write

kiri suggest gold reads the dimension from the goal’s spec and emits a gold model wired to kiri_scd2_dim with the grain, attributes, and surrogate strategy filled in. The --scd-type flag defaults to type_1 for safety — a Type 1 dimension overwrites in place and keeps no history — so you bump to type_2 deliberately, when history is a requirement rather than an accident. The --surrogate-key-strategy flag (hash, sequence, or natural) picks how the key is generated; the default follows your silver technique.

The --write flag saves the draft under models/gold/ as <goal>__<name>.sql; without it the model prints for review. Either way, what you get is a reviewable dbt model that builds with plain dbt build against your Databricks SQL warehouse — the SCD2 logic is in a shipped, tested macro, not copy-pasted boilerplate.

When to reach for it

Use kiri_scd2_dim when the business question needs point-in-time correctness: “what was this customer’s segment when the order was placed”, “which price list applied on the invoice date”. If you only ever need the current state, a Type 1 dimension is simpler and cheaper — don’t pay for history you won’t query. When you do need it, the macro is the supported path, so your history-tracking dimensions all behave the same way and all survive a late-arriving load.