Slowly changing dimensions in practice: what type 2 really costs

Type 1 overwrites. The attribute holds the current value, history is lost, and every report restates the past using today values. This is the right default for corrections, typos, and attributes nobody analyzes historically.

Type 2 adds a row. Each version carries valid_from, valid_to, and a current flag, and the dimension gets a surrogate key that identifies the version rather than the entity. This is the right choice for attributes where the business asks how things were at the time, typically segment, territory, price band, org unit, and account manager.

Type 3 keeps a previous value column. It answers exactly one question, what was it before the last change, and it is worth using only for a planned one-off reorganization where the business genuinely wants to report both ways for a transition period.

Types 4 and 6 exist for specific pressures. Type 4 moves fast-changing attributes into a mini-dimension so the main dimension does not explode. Type 6 combines a versioned row with a current-value column on that row, which makes both the as it was and the as it is now question answerable without a second join. Type 6 is more common in practice than its obscurity suggests.

The decision is per attribute, not per table. A customer dimension can be type 1 on name and email, type 2 on segment and billing country, and type 4 on a behavioral score that changes weekly.

The fact table must carry the surrogate key of the dimension version valid at the event time, resolved during the fact load. If it carries the natural key instead, the join returns every version and the model silently multiplies rows. This is the single most common type 2 defect and it is caught by the same uniqueness test that defends the grain.

Every query that wants the current picture has to say so. Filtering on the current flag, or joining on the version that was valid on a given date, becomes a rule that ad hoc analysts and BI models must follow. A view over the dimension exposing only current rows removes most accidents at little cost.

Late-arriving dimension rows break the neat version. When a fact arrives for an entity whose dimension version is not yet loaded, either the fact waits or a placeholder version is created and corrected later. Decide which, write it down, and test it, because whichever you choose it will happen during a month end.

Deletes need a policy. A source that hard-deletes a row gives you no change event, so a type 2 dimension will show the last version as still valid forever unless the load detects absence and closes the version.

History built by snapshotting a source can only capture changes that survived until the next snapshot. A daily snapshot of a table where an attribute changed twice in one afternoon records one change, and no amount of careful modeling recovers the other. If the business needs every transition, the source has to emit change events or a change data capture stream has to feed the dimension.

This is worth saying out loud during design, because the usual assumption is that type 2 means complete history. It means sampled history at the cadence you chose. Write the cadence next to the history policy so nobody later builds an audit report on a promise the pipeline never made.

Change detection also needs a rule. Comparing a timestamp column trusts the source to update it, which many sources do unreliably. Comparing the tracked columns themselves is more reliable and has the useful side effect of forcing you to name them, which is the type 2 decision itself. In dbt that is strategy: check with an explicit check_cols list. Note that the delete rule was renamed in dbt 1.9: hard_deletes: invalidate replaces invalidate_hard_deletes: true, and the two cannot be used together, so a project that still sets the old key is on borrowed time.

# snapshots/dim_customer_history.yml  (dbt 1.9+)
snapshots:
  - name: dim_customer_history
    relation: source('crm', 'customers')
    config:
      schema: snapshots
      unique_key: customer_id
      strategy: check
      # naming the tracked columns IS the type 2 decision
      check_cols: [segment, billing_country, account_manager_id]
      # replaces the legacy invalidate_hard_deletes: true
      hard_deletes: invalidate

# customer_name is absent from check_cols on purpose:
# type 1, latest value wins, no version row on a correction.

History is invisible in a column list, which is why it gets ignored. On the Datadef canvas a dimension node can carry its own annotation, so dim_customer reads as type 2 on segment and billing country, daily snapshot, and the edge into the fact table is labelled with the as-of join rather than left as a bare arrow. A reviewer then sees the modeling decision instead of inferring it.

For the attributes that matter most, column-level lineage shows the path from the source column through the snapshot into the versioned attribute and out to the fact, which is the fastest way to answer why a historical number changed. When the models live in a dbt project, connect the repository and the diagram plus its architecture.md regenerate daily from the branch, so a newly tracked column shows up in the picture instead of in a Slack thread.

• Kimball dimensional modeling
• Choosing fact table grain
• dbt best practices
• Data lineage best practices
• Column-level lineage