Slowly Changing Dimensions (SCD) Implementation Patterns Cheat Sheet

Updated 2026-05-23

🧠Study flashcards on this topic76 cards · spaced repetition→

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, 76 flashcards. 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

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

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).
SCD Type 4 (mini-dimension)	Separate `mini_dim_customer_profile` table with surrogate key referenced from fact table	• Splits rapidly or frequently changing attributes into a separate mini-dimension to avoid bloating the base dimension • both base and mini-dimension surrogate keys appear in the fact table
SCD Type 6 (hybrid 1+2+3)	Type 2 row with added `current_value` column overwritten Type 1; name = 2+3+1 = 6 also 2×3×1 = 6	Combines Type 2 row versioning with a Type 1 overwritten current-value column so every historical row also shows the attribute's current value, enabling both as-was and as-is reporting in one table.
SCD Type 7 (dual keys)	Fact table holds two FKs: `dim_sk` (version surrogate) + `durable_sk` (links to current row)	• Achieves the same dual reporting as Type 6 but via two foreign keys in the fact table rather than physically overwriting columns in every historical row • avoids mass updates
SCD Type 5 (mini-dim + Type 1 outrigger)	Base dim embeds `current_profile_sk` (Type 1 overwrite) referencing the mini-dimension	• Extends Type 4 by embedding a Type 1 current-profile key in the base dimension so current mini-dimension values are accessible without joining through the fact table • Named 5 because 4+1=5.

Table 2: SCD Type 2 Schema Design Patterns

SCD Type 2 requires careful column design to support both point-in-time queries and efficient current-record lookups. Several conventions exist and each has practical implications for query performance.

Pattern	Example	Description
effective_date / end_date columns	`effective_date DATE, end_date DATE` — current row has `end_date = NULL`	• Classic date-range approach • current row identified by `end_date IS NULL` • supports range-based point-in-time joins with `BETWEEN`.

The rest of this section is part of the full cheat sheet

Table 3: MERGE INTO Pattern for SCD Type 2

The SQL MERGE INTO statement is the standard mechanism for atomically applying SCD logic — closing old rows and inserting new ones — in a single operation. This pattern works across most modern warehouses (SQL Server, Snowflake, Databricks, BigQuery, DuckDB, Teradata).

Pattern	Example	Description
Standard MERGE INTO SCD2	`MERGE INTO dim_customer t USING (...source...) s ON t.customer_id = s.join_key` `WHEN MATCHED AND t.is_current = TRUE AND s.updated > t.updated THEN UPDATE SET t.is_current = FALSE, t.valid_to = s.updated` `WHEN NOT MATCHED THEN INSERT (...)`	• Atomic UPDATE (expire old row) + INSERT (new version) in a single statement • avoids race conditions that arise from two separate DML calls

The rest of this section is part of the full cheat sheet

Table 4: dbt Snapshot Implementation

dbt's native snapshot feature automates SCD Type 2 table maintenance using a declarative configuration in a .sql file. It adds four metadata columns automatically (dbt_valid_from, dbt_valid_to, dbt_scd_id, dbt_updated_at) and handles MERGE logic internally.

Pattern	Example	Description
timestamp strategy	`config(strategy='timestamp', updated_at='updated_at', unique_key='order_id')`	• Detects changes by comparing `updated_at` column values • recommended when source table has a reliable update timestamp

The rest of this section is part of the full cheat sheet

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).
SCD Type 4 (mini-dimension)	Separate `mini_dim_customer_profile` table with surrogate key referenced from fact table	• Splits rapidly or frequently changing attributes into a separate mini-dimension to avoid bloating the base dimension • both base and mini-dimension surrogate keys appear in the fact table
SCD Type 6 (hybrid 1+2+3)	Type 2 row with added `current_value` column overwritten Type 1; name = 2+3+1 = 6 also 2×3×1 = 6	Combines Type 2 row versioning with a Type 1 overwritten current-value column so every historical row also shows the attribute's current value, enabling both as-was and as-is reporting in one table.
SCD Type 7 (dual keys)	Fact table holds two FKs: `dim_sk` (version surrogate) + `durable_sk` (links to current row)	• Achieves the same dual reporting as Type 6 but via two foreign keys in the fact table rather than physically overwriting columns in every historical row • avoids mass updates
SCD Type 5 (mini-dim + Type 1 outrigger)	Base dim embeds `current_profile_sk` (Type 1 overwrite) referencing the mini-dimension	• Extends Type 4 by embedding a Type 1 current-profile key in the base dimension so current mini-dimension values are accessible without joining through the fact table • Named 5 because 4+1=5.

Table 2: SCD Type 2 Schema Design Patterns

Pattern	Example	Description
effective_date / end_date columns	`effective_date DATE, end_date DATE` — current row has `end_date = NULL`	• Classic date-range approach • current row identified by `end_date IS NULL` • supports range-based point-in-time joins with `BETWEEN`.

Table 3: MERGE INTO Pattern for SCD Type 2

Pattern	Example	Description
Standard MERGE INTO SCD2	`MERGE INTO dim_customer t USING (...source...) s ON t.customer_id = s.join_key` `WHEN MATCHED AND t.is_current = TRUE AND s.updated > t.updated THEN UPDATE SET t.is_current = FALSE, t.valid_to = s.updated` `WHEN NOT MATCHED THEN INSERT (...)`	• Atomic UPDATE (expire old row) + INSERT (new version) in a single statement • avoids race conditions that arise from two separate DML calls

Table 4: dbt Snapshot Implementation

Pattern	Example	Description
timestamp strategy	`config(strategy='timestamp', updated_at='updated_at', unique_key='order_id')`	• Detects changes by comparing `updated_at` column values • recommended when source table has a reliable update timestamp