Slowly Changing Dimensions

Slowly changing dimension techniques specify how changes to descriptive attributes are represented. Type 1 overwrites a value; type 2 preserves a new row for each tracked version; type 3 retains selected previous or alternate values in columns. Choose per attribute according to the questions, rather than assuming every dimension needs the same policy.

A type 2 customer can have several version keys under one business identifier. An A → B → A history has three intervals, although it has only two distinct attribute values. Define whether bounds are inclusive dates or half-open timestamps, and check missing or overlapping intervals. A fact-preserving lookup should resolve each intended fact to one version.

A daily snapshot only reveals states at its observation times. Converting it to type 2 cannot recover changes reversed between observations. An open-ended current row means no later captured version, not guaranteed future truth. Type 2 validity alone also does not show when a late correction became available to a model; preserve system or availability history when that distinction matters.

Reference: Kimball type 2 history. See Data Modeling for Operational and Analytical Systems for the worked example.