SCD Type 2 (Slowly Changing Dimension Type 2) is a data warehouse design pattern that maintains complete historical records of dimensional data by storing multiple versions of each dimension record with explicit validity windows. This approach enables organizations to track how dimension attributes change over time while preserving the ability to analyze data “as it was” at any point in the past ¹⁾

SCD Type 2 differs from other slowly changing dimension approaches by creating new rows rather than updating existing ones when attribute values change. Each row version includes temporal markers that define when that version was active. The core implementation uses START_AT and END_AT columns (or equivalent timestamp fields) to establish validity windows for each record version ²⁾

The pattern guarantees that exactly one current version exists for each dimension entity at any given time. The current version typically has a NULL or maximum timestamp value in the __END_AT column, indicating it remains valid through the present moment. Historical versions contain both start and end timestamps, creating a complete audit trail of dimensional changes ³⁾

Implementing SCD Type 2 requires careful handling of several technical challenges. Row versioning demands that each new attribute change generates a new row with an incremented version number or surrogate key while maintaining the relationship to the original dimension entity. Organizations must establish a clear mechanism for identifying when a change has occurred and determining the effective date of that change.

Late-arriving updates present a particular implementation challenge. These are corrections or updates that arrive after subsequent transactions have already been recorded. SCD Type 2 systems must handle these without corrupting the historical record. This typically involves logic to close out incorrectly dated versions and insert corrected versions with appropriate validity windows ⁴⁾

The pattern requires robust Change Data Capture (CDC) mechanisms to identify which attributes have actually changed versus which remain constant. This prevents creating unnecessary row versions for records where no meaningful change occurred. Many modern data platforms support automated CDC to detect and track these changes without manual specification ⁵⁾

SCD Type 2 is widely used in enterprise data warehouses for dimensions that require historical analysis. Customer dimensions commonly use this pattern to track address changes, phone number updates, or segment classifications over time. This enables reporting that answers questions like “Which customers were in the premium segment when this order was placed?” rather than only showing current segment assignments.

Product dimensions frequently employ SCD Type 2 to maintain pricing history, supplier changes, or category reclassifications. Employee dimensions track organizational changes, title promotions, and department transfers. These implementations allow historical analysis while supporting current-state reporting without complex date-based lookups in fact tables.

The primary advantage of SCD Type 2 is complete auditability and historical accuracy. Analytics queries can easily reconstruct the state of dimensional data at any historical point by filtering on validity windows. This eliminates the need for complex temporal joins or historical reconstruction logic in downstream queries.

However, SCD Type 2 introduces storage overhead since each change multiplies the number of rows for a dimension. Dimension tables can grow significantly over time, particularly for dimensions with frequent changes. Query performance may degrade without proper indexing on the validity window columns. Additionally, the pattern adds complexity to ETL/ELT processes, requiring careful logic to detect changes, generate versions, and manage effective dates ⁶⁾

• SCD Type 1 vs SCD Type 2
• Slowly Changing Dimensions (SCD)
• Variant Data Type