SCD Type 2: keeping the history

SCD Type 2 never overwrites. It closes the old row (an end date, no longer current) and adds a new row, so both versions are kept.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

When the old value matters: keep it

UPDATE customers SET end_date = '2024-06-01', is_current = FALSE WHERE customer_id = 'C101' AND is_current = TRUE;
INSERT INTO customers VALUES ('C101', 'Ada Obi', 'West', '2024-06-01', NULL, TRUE);
SELECT * FROM customers WHERE customer_id = 'C101'

Result:

customer_id	full_name	region	start_date	end_date	is_current
C101	Ada Obi	East	2023-01-01	2024-06-01	False
C101	Ada Obi	West	2024-06-01		True

Step by step

The old row stays, with an end_date, and stops being current.

UPDATE customers SET end_date = '2024-06-01', is_current = FALSE WHERE customer_id = 'C101' AND is_current = TRUE;

The new region is a new row, current from today.

UPDATE customers SET end_date = '2024-06-01', is_current = FALSE WHERE customer_id = 'C101' AND is_current = TRUE;
INSERT INTO customers VALUES ('C101', 'Ada Obi', 'West', '2024-06-01', NULL, TRUE);

Both rows are there: East until June, West from June.

UPDATE customers SET end_date = '2024-06-01', is_current = FALSE WHERE customer_id = 'C101' AND is_current = TRUE;
INSERT INTO customers VALUES ('C101', 'Ada Obi', 'West', '2024-06-01', NULL, TRUE);
SELECT * FROM customers WHERE customer_id = 'C101';

Practice

Try each one in your head first, then open the answer.

1. Show only the current rows: customer_id and region where is_current is TRUE.

SELECT customer_id, region FROM customers WHERE is_current = TRUE

2. Close C101's current row: set end_date to '2024-06-01' and is_current to FALSE. Then show all customers.

UPDATE customers SET end_date = '2024-06-01', is_current = FALSE WHERE customer_id = 'C101' AND is_current = TRUE; SELECT * FROM customers

3. Type 2 in full: close C101's current row (end_date '2024-06-01', is_current FALSE), add ('C101', 'Ada Obi', 'West', '2024-06-01', NULL, TRUE), then show C101's rows.

UPDATE customers SET end_date = '2024-06-01', is_current = FALSE WHERE customer_id = 'C101' AND is_current = TRUE; INSERT INTO customers VALUES ('C101', 'Ada Obi', 'West', '2024-06-01', NULL, TRUE); SELECT * FROM customers WHERE customer_id = 'C101'

4. Type 2 for C103 moving to 'South' on '2024-07-01': close the current row, add the new one, then show C103's rows.

UPDATE customers SET end_date = '2024-07-01', is_current = FALSE WHERE customer_id = 'C103' AND is_current = TRUE; INSERT INTO customers VALUES ('C103', 'Cara Diaz', 'South', '2024-07-01', NULL, TRUE); SELECT * FROM customers WHERE customer_id = 'C103'

5. Show customer_id and region for every row that is no longer current.

SELECT customer_id, region FROM customers WHERE is_current = FALSE

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed