Slowly Changing Dimensions (SCD) Implementation Patterns Cheat Sheet

Updated 2026-05-23

Slowly Changing Dimensions (SCDs) are a family of design patterns in dimensional data modeling — first formalized by Ralph Kimball — that govern how a data warehouse responds when descriptive attributes of dimension records change over time. Choosing the wrong SCD type silently corrupts historical reporting: a fact joined to an overwritten dimension loses the state that was true when the transaction occurred. The core trade-off is storage and complexity versus historical fidelity — and the patterns range from "never change anything" (Type 0) through full bi-temporal bookkeeping that tracks both real-world time and system-record time independently.

What This Cheat Sheet Covers

This topic spans 14 focused tables and 82 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Core SCD Types OverviewTable 2: SCD Type 2 Schema Design PatternsTable 3: MERGE INTO Pattern for SCD Type 2Table 4: dbt Snapshot ImplementationTable 5: Delta Lake MERGE for SCD2 (Databricks)Table 6: Apache Iceberg SCD ImplementationTable 7: Snowflake SCD ImplementationTable 8: Surrogate Key Generation StrategiesTable 9: Handling Late-Arriving DimensionsTable 10: Bi-Temporal SCD ImplementationTable 11: Fact Table Joining Patterns with SCD2 DimensionsTable 12: SCD Performance and PartitioningTable 13: SCD Selection Guide and Common GotchasTable 14: Modern Platform SCD Patterns

Table 1: Core SCD Types Overview

The seven canonical SCD types each represent a distinct policy for handling attribute changes. Understanding when each is appropriate is more important than memorizing their mechanics.

Type	Example	Description
SCD Type 2 (add row)	`INSERT new row; UPDATE old row SET end_date = today, is_current = FALSE`	• Most widely used pattern. Preserves full history by inserting a new row for each change while closing the prior row with an `end_date` • enables accurate point-in-time reporting
SCD Type 1 (overwrite)	`UPDATE customers SET address = '123 New St' WHERE customer_id = 101`	• Destroys history — overwrites the existing value in place • use only when correcting bad data or when history is genuinely irrelevant
SCD Type 0 (retain original)	Column `date_of_birth` is never updated after initial insert	• The attribute value never changes once set • facts are always grouped by the original value • Suitable for truly immutable attributes such as SSN or original credit score
SCD Type 3 (add column)	`ALTER TABLE employees ADD COLUMN prev_department VARCHAR(100);` `UPDATE employees SET prev_department = current_department, current_department = 'Finance'`	• Stores only one prior value in a new column • trades full history for simpler queries • suited to exactly-two-value comparisons (current vs. previous).

Table 1: Core SCD Types Overview

The seven canonical SCD types each represent a distinct policy for handling attribute changes. Understanding when each is appropriate is more important than memorizing their mechanics.

Type	Example	Description
SCD Type 2 (add row)	`INSERT new row; UPDATE old row SET end_date = today, is_current = FALSE`	• Most widely used pattern. Preserves full history by inserting a new row for each change while closing the prior row with an `end_date` • enables accurate point-in-time reporting
SCD Type 1 (overwrite)	`UPDATE customers SET address = '123 New St' WHERE customer_id = 101`	• Destroys history — overwrites the existing value in place • use only when correcting bad data or when history is genuinely irrelevant
SCD Type 0 (retain original)	Column `date_of_birth` is never updated after initial insert	• The attribute value never changes once set • facts are always grouped by the original value • Suitable for truly immutable attributes such as SSN or original credit score
SCD Type 3 (add column)	`ALTER TABLE employees ADD COLUMN prev_department VARCHAR(100);` `UPDATE employees SET prev_department = current_department, current_department = 'Finance'`	• Stores only one prior value in a new column • trades full history for simpler queries • suited to exactly-two-value comparisons (current vs. previous).