Slowly Changing Dimensions

Slowly Changing Dimensions addresses changes to the descriptive characteristics of business entities in analytical data models. A customer can move to a different region, a product can change category, and an employee can transfer departments. The central design question is whether historical analysis should reflect the old value, the current value, or both. Despite the name, the technique does not require changes to occur infrequently; change frequency mainly affects storage, processing cost, and model suitability.

Choose a change policy for each relevant attribute rather than assuming every column needs identical treatment. Type 0 retains the original value and ignores subsequent changes to that attribute. Type 1 overwrites the stored value without retaining its previous value in the dimension. It is appropriate for corrections or attributes whose history is unnecessary. For example, correcting a misspelled customer name usually does not justify creating another historical customer version.

Type 2 preserves history by creating a new dimension row when tracked attributes change. Each version normally receives a distinct surrogate key, while the business key continues to identify the same source entity. Effective-start and effective-end timestamps describe when the version applies, and a current-row flag can simplify current-state queries. If customer C42 moves from East to West on July 1, the East version ends on July 1 and the West version begins then. Half-open validity intervals, where the start is inclusive and the end is exclusive, avoid ambiguity at the boundary.

Fact rows should reference the dimension version appropriate to the business event time. A June sale for C42 should retain its association with East even if the sale is loaded after the July move. Pipelines can resolve the surrogate key using the business key and the event timestamp during loading. Alternatively, suitable models can use time-range joins, although these require careful query design. Joining historical facts to only the current customer row incorrectly reclassifies earlier sales.

Type 3 stores limited history in additional columns, such as current_region and previous_region. It supports a defined comparison but does not preserve an unlimited sequence of changes. Hybrid approaches are possible when different reporting requirements coexist. On exam questions, overwriting points toward Type 1, preserving every accepted historical version points toward Type 2, and retaining a fixed previous value points toward Type 3.

In cloud pipelines, change data capture or periodic snapshots supply candidate changes. Transformation logic compares incoming records with the applicable stored state, using selected-column comparisons or consistently calculated hashes. Hash inputs require stable handling of nulls, data types, and formatting. Only actual changes to tracked attributes should create new versions. Change data capture transports source changes; it does not by itself choose or implement the correct SCD policy.

A Type 2 load commonly stages and deduplicates incoming records, expires affected current versions, and inserts replacements within an atomic transaction or table-format commit. A MERGE statement can assist, but its availability does not automatically make the process a correct Type 2 implementation. Preserve ordering when multiple changes for one entity arrive together, make retries idempotent, and validate that validity intervals do not overlap and that each active business entity has exactly one current version. Late-arriving changes may require splitting an existing interval and reassessing affected fact references.

Type 2 increases row counts, transformation work, and temporal-join complexity in exchange for historical fidelity. Separate genuine business changes from corrections, define how deletions are represented, and distinguish business-effective time from ingestion time. SCD is also different from schema evolution: adding or changing a column alters table structure, whereas changing a customer's region alters an entity's attributes. Both may require pipeline updates, but they solve different problems.