Implementation of slowly changing Dimension Type-3 Adding a new column In SQL Server

In this approach usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current/new' column and the old one into 'old/previous' column. Generally speaking the history is limited to the number of column created for storing historical data.

For example we can have the city name or state name.

See the below.

ID	Name	Current_City	Previous_City
1	Bagesh Kumar Singh	Chennai	Madras
2	Mahesh	Mumbai	Bombay

If we want to implement this approach we need to alter the dimension table and need to make the changes in the load script or load ETL package.

Let’s see the demo

We have DimCustomer_SCD_3 table with below columns.

CREATE TABLE dimcustomer_scd_3_stg   (      dimcustomer_scd_3_stg_id INT IDENTITY(1, 1) PRIMARY KEY,      cus_id               INT,      cus_nm               VARCHAR(50),      Current_City         varchar(20)   ) CREATE TABLE dimcustomer_scd_3   (      dimcustomer_scd_3_id INT IDENTITY(1, 1) PRIMARY KEY,      cus_id               INT,      cus_nm               VARCHAR(50),      Current_City         varchar(20)   )

See the table 

Now business want to store the previous city name in that case we need to alter the both table and also we need to make the changes in to the load script or load ETL package.

Altering the stg and main table.

ALTER TABLE dimcustomer_scd_3   ADD previous_city VARCHAR(20) NULL; ALTER TABLE dimcustomer_scd_3_stg   ADD previous_city VARCHAR(20) NULL;

Now we are updating the logic to load the previous city name. we are inserting the below records into the stage table for load.

INSERT INTO dimcustomer_scd_3_stg             (cus_id,              cus_nm,              current_city,              previous_city) VALUES      (1,              'Bagesh Kumar Singh',              'chennai',              'Madras'); INSERT INTO dimcustomer_scd_3_stg             (cus_id,              cus_nm,              current_city,              previous_city) VALUES      (2,              'Mahesh',              'Mumbai',              'Bombay'); INSERT INTO dimcustomer_scd_3_stg             (cus_id,              cus_nm,              current_city,              previous_city) VALUES      (3,              'Ramesh',              'Patna',              NULL);

See the records in the both table. 

Below is the script use to populate the data. If we get the previous city value then we are updating it if not the keeping as null. 

MERGE dimcustomer_scd_3 TT using dimcustomer_scd_3_stg ST ON ( tt.cus_id = st.cus_id ) WHEN NOT matched THEN   INSERT ( cus_id,            cus_nm,            current_city,            previous_city )   VALUES ( cus_id,            cus_nm,            current_city,            previous_city ) WHEN matched THEN   UPDATE SET tt.current_city = ST.current_city,              tt.previous_city = ST.previous_city;

Running this script. 

Record updated successfully.