Slowly changing dimension

A slowly changing dimension is a data-warehouse design pattern for attributes such as an organization's name or classification that change less often than transactional observations. Common variants overwrite the previous value, add a new versioned row, or preserve a limited prior value in another field. The choice determines which historical questions the warehouse can answer. The label describes a modeling technique, not a claim that the underlying world changes slowly.

In practice, versioned rows need effective intervals, a stable entity key, and a clear rule for attaching facts to the right version. Late-arriving changes can revise a past interval and expose a difference between the date an attribute applied and the date the warehouse learned it. An overwrite variant is simpler but loses earlier values. A versioned variant retains more history yet can still omit transaction time or evidence behind the change.

For a defensible report, dimension history helps explain which descriptive attributes were associated with a transaction at its date. It should not be confused with full point-in-time reproducibility: source versions, classification rules, and information availability may remain outside the dimension. Bitemporal data separates valid and recorded periods explicitly; a slowly changing dimension may implement only part of that distinction. The chosen variant should match the question the report must answer.

A Type 2 dimension tracks one time axis, usually the date the warehouse noticed the change, not necessarily the date the change took effect in the world. If a customer moved in March and the warehouse learned of it in May, the new row's effective date is either March, which rewrites what was known in April, or May, which misstates when the move happened. Choosing either loses information that a bitemporal design keeps. Corrections are also awkward: when a row is found to be wrong, the pattern does not say whether to edit it, which destroys the record of what the report showed, or to add a correcting row with its own dates.

Use Type 2 where the question is analytic and the late-arrival problem is minor, and design with bitemporal columns where the reconstruction of past beliefs matters, as in the compliance and audit uses discussed in the article on bitemporal data versus temporal knowledge graphs. The system-versioned tables described in the Microsoft documentation on temporal tables can supply the transaction-time axis that a plain Type 2 table lacks.

Ask which type each dimension uses and why. Ask what the effective date means: the date of the change in the world or the date of the load. Ask how corrections are handled and whether an earlier report can be reproduced after one. Test with a backdated change and see whether any past figure moves. Confirm that fact rows reference the dimension version that applied.