Implementing SCD Type 2

✅ Key Concepts

Column	Purpose
valid_from	Start date of the record version
valid_to	End date (‘9999-12-31’ if current)
is_current	Boolean flag for active version

---

🛠️ Steps to Implement

1. Detect Changes: Compare source (staging) and target (dimension) data on business keys and attributes.
2. Expire Old Records: Set valid_to = current_timestamp - 1 second, is_current = false.
3. Insert New Records: New values with valid_from = current_date, valid_to = '9999-12-31', is_current = true.

---

Example Scenario of SCD Type 2 using MERGE Statement

Sample Dataset

We will be working on this source to replicate SCD Type 2;

-- Create source data table
CREATE TABLE customer_data (
    customer_id INT,
    name TEXT,
    email TEXT,
    city TEXT,
    source TEXT,
    PRIMARY KEY (customer_id, source)
);

-- Insert initial and staging records
INSERT INTO customer_data (customer_id, name, email, city, source)
VALUES
    (101, 'John Doe', 'john@example.com', 'Kathmandu', 'initial'),
    (102, 'Jane Smith', 'jane@example.com', 'Pokhara', 'initial'),
    (103, 'Sam Lee', 'sam@example.com', 'Biratnagar', 'initial'),
    (101, 'John Doe', 'john.d@example.com', 'Sydney', 'staging'),
    (102, 'Jane Smith', 'jsmith@example.com', 'Pokhara', 'staging'),
    (104, 'Lisa Ray', 'lisa@example.com', 'Lalitpur', 'staging');

customer_id	name	email	city	source
101	John Doe	john@example.com	Kathmandu	initial
102	Jane Smith	jane@example.com	Pokhara	initial
103	Sam Lee	sam@example.com	Biratnagar	initial
101	John Doe	john.d@example.com	Sydney	staging
102	Jane Smith	jsmith@example.com	Pokhara	staging
104	Lisa Ray	lisa@example.com	Lalitpur	staging

Prepare SCD Type 2 Table

-- Create SCD2-compatible dimension table
CREATE TABLE customer_dim (
    customer_id INT,
    name TEXT,
    email TEXT,
    city TEXT,
    valid_from TIMESTAMP,
    valid_to TIMESTAMP,
    is_current BOOLEAN,
    PRIMARY KEY (customer_id, valid_from)
);
-- Initial Load for SCD2 from 'initial' source
INSERT INTO customer_dim (customer_id, name, email, city, valid_from, valid_to, is_current)
SELECT customer_id, name, email, city, CURRENT_TIMESTAMP, '9999-12-31', true
FROM customer_data
WHERE source = 'initial';

Expire Old Records

-- Define staging data from 'staging' source
WITH staging_customer AS (
    SELECT customer_id, name, email, city
    FROM customer_data
    WHERE source = 'staging'
)
-- MERGE for SCD Type 2: expire changed rows
MERGE INTO customer_dim AS target
USING staging_customer AS source
ON target.customer_id = source.customer_id
   AND target.is_current = TRUE
WHEN MATCHED AND (
    target.name IS DISTINCT FROM source.name
 OR target.email IS DISTINCT FROM source.email
 OR target.city IS DISTINCT FROM source.city
) THEN
    UPDATE SET
        valid_to = CURRENT_TIMESTAMP - INTERVAL '1 second',
        is_current = FALSE
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, city, valid_from, valid_to, is_current)
    VALUES (source.customer_id, source.name, source.email, source.city, CURRENT_TIMESTAMP, '9999-12-31', TRUE);

In Slowly Changing Dimensions, you want to detect any change, even if one value was NULL before and is now filled in — or vice versa. IS DISTINCT FROM ensures you accurately detect changes, including NULLs.

• target.name <> source.name — → NULL (which behaves like FALSE in WHERE)
• target.name IS DISTINCT FROM source.name — → TRUE ✅

Insert new version of changed rows*

WITH changed AS (
    SELECT s.*
    FROM staging_customer s
    JOIN customer_dim d
      ON s.customer_id = d.customer_id
     AND d.is_current = FALSE
    WHERE NOT EXISTS (
        SELECT 1
        FROM customer_dim c
        WHERE c.customer_id = s.customer_id
          AND c.is_current = TRUE
          AND c.name = s.name
          AND c.city = s.city
    )
)
INSERT INTO customer_dim (customer_id, name, email, city, valid_from, valid_to, is_current)
SELECT customer_id, name, email, city, CURRENT_TIMESTAMP, '9999-12-31', TRUE
FROM changed;

Result

customer_id	name	email	city	valid_from	valid_to	is_current
101	John Doe	john@example.com	Kathmandu	2025-06-26 18:26:27.434231	2025-06-26 18:26:26.441332	False
101	John Doe	john.d@example.com	Sydney	2025-06-26 18:26:27.444725	9999-12-31 00:00:00	True
102	Jane Smith	jane@example.com	Pokhara	2025-06-26 18:26:27.434231	2025-06-26 18:26:26.441332	False
102	Jane Smith	jsmith@example.com	Pokhara	2025-06-26 18:26:27.444725	9999-12-31 00:00:00	True
103	Sam Lee	sam@example.com	Biratnagar	2025-06-26 18:26:27.434231	9999-12-31 00:00:00	True
104	Lisa Ray	lisa@example.com	Lalitpur	2025-06-26 18:26:27.441332	9999-12-31 00:00:00	True