CREATE TABLE dim_emp (
surrogate_key NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
empid NUMBER,
name VARCHAR2(100),
salary NUMBER(10,2),
start_date DATE,
end_date DATE,
is_active CHAR(1)
);
INSERT INTO dim_emp
(EMPID, NAME, SALARY, START_DATE, END_DATE, IS_ACTIVE)
VALUES
(1, 'John Doe', 50000, DATE '2025-01-01', NULL, 'Y');
INSERT INTO dim_emp
(EMPID, NAME, SALARY, START_DATE, END_DATE, IS_ACTIVE)
VALUES
(2, 'Jane Smith', 60000, DATE '2025-02-01', NULL, 'Y');
CREATE TABLE stg_emp (
id INT,
name VARCHAR(100),
salary DECIMAL(10, 2),
start_date DATE
);
INSERT INTO stg_emp(id, name, salary, start_date)
VALUES (1, 'John Doe', 55000, DATE '2026-01-17'); — Updated row
INSERT INTO stg_emp(id, name, salary, start_date)
VALUES (3, 'Alice Johnson', 70000, DATE '2026-03-03'); ---- New row
My approach to make SCD type 2 table below
IS_ACTIVE, START_DATE, and END_DATE are columns to make this a type 2 SCD table.
empid --- is natural key in dim_emp
id ---- is unique identified in stg_emp
Insert are working but with update “is_active” not populated to 'N' and changed row with start_date isn't correct affected
BEGIN;
-- 1. Expire existing records that have changed
UPDATE dim_emp d
SET end_date = CURRENT_DATE - 1,
IS_ACTIVE = 'N'
FROM stg_emp s
WHERE d.EMPID = s.ID
AND d.IS_ACTIVE = 'Y'
AND (d.NAME <> s.NAME
OR d.SALARY <> s.SALARY);
-- 2. Insert new records (for new companies OR changed companies)
INSERT INTO dim_emp (
EMPID,
NAME,
SALARY,
START_DATE,
END_DATE,
IS_ACTIVE
)
SELECT
s.ID,
s.NAME,
s.SALARY,
CURRENT_DATE,
DATE '9999-12-31',
'Y'
FROM stg_emp s
LEFT JOIN dim_emp d
ON s.ID = d.EMPID
AND d.IS_ACTIVE = 'Y'
WHERE d.EMPID IS NULL -- New Company
OR (d.NAME <> s.NAME
OR d.SALARY <> s.SALARY); -- Changed Company
COMMIT;