Momentum One Academy · glossary
Definition
Slowly Changing Dimension Type 2 (SCD2) is a dimensional-modeling pattern for preserving the history of selected dimension attributes. When a tracked value changes, the load expires the current row and inserts a new version instead of overwriting the old one. The versions share a business or natural key, but each needs a distinct surrogate key. Valid-from and valid-to timestamps (or dates), plus an IsCurrent flag, make the effective period explicit. 1 2
Where it sits in Fabric
SCD2 is a design pattern, not a separate Fabric item. In Fabric Data Factory, Microsoft documents a Dataflow Gen2 approach that compares source and dimension data, identifies changes, and prepares updates. 1 Fabric’s CDC Copy Job also offers SCD Type 2 as a write method, but the current documentation labels that capability preview; it adds validity and current-version columns and can soft-delete source records. 4 A Warehouse is a natural destination for a SQL-first star schema and governed dimensional model. A Lakehouse can hold the Delta tables and support Spark-based processing or a SQL analytics endpoint for read-only querying. 5 6
When to use it
Use SCD2 when historical perspective matters: for example, you need sales attributed to the customer segment or sales region that was effective when the sale occurred. Use Type 1 instead when only the latest value matters. If an attribute changes so frequently that it would create excessive dimension versions, consider storing the changing value at fact grain instead. 2
Practical example
Customer C-123 moves from California to New York on 20 February 2026. Keep the California row with its original surrogate key, set its ValidTo to the change time, and mark it not current. Insert a new row for C-123 with a new surrogate key, ValidFrom set to that same boundary, ValidTo left open, and IsCurrent = TRUE. Fact loads then resolve the appropriate customer-version key so historical results remain tied to the correct state. 3 4
Production caution
Do not truncate and fully reload an SCD2 dimension that uses generated surrogate keys: that can invalidate fact-table references and make historical versions impossible to regenerate safely. Prefer an incremental, auditable expire-and-insert process, enforce one current row per natural key, and test late-arriving changes and deletion semantics. 3
Module 7 connection
For Module 7, keep the distinction clear: Change Data Capture identifies inserted, updated, or deleted source rows; SCD2 decides how those changes become durable, queryable dimension history. A common Fabric flow is to capture or land changes in a Lakehouse, process them with Data Factory or Spark, and publish a Warehouse dimension for reporting.
Keep learning
Ready to apply this pattern in a production-minded Fabric solution? Explore Momentum One Academy’s Fabric Production Workshop.
References
Plan your workshop