Implementing SCD TypeII using Mload

To implement SCD type II using Mload, we need to know the primary key of the data. A comparison with primary key on source with the corresponding target field will help us in understanding the data already exist or not in the target. Now the entire method can be done in two steps.

1) Read the source file and compare with the primary key. For those data where a match is found, the corresponding rows will be expired by setting the EFFECTIVE_END_DT to CURRENT_DATE - 1.

2) As a second step, insert all the rows from the source to target.

Sample Mload Script to Implement SCD Type II:

Note the EFFECTIVE_START_DT has a default value of CURRENT_DATE and EFFECTIVE_END_DT has 9999-12-31 at database level in the below example. As well Field1 is considered to be the primary key at source.