This section describes ETL concepts for accomplishing type 2 SCD using projects and jobs. The demo repository that is packaged with the product includes various ETL transformation objects related to the SCD type 2 use case, including:
Projects
Demo Product Price SCD – Initial Load
This project initializes or reinitializes the demo environment for the SCD - Update projects and job.
This project is not a part of the use case implementation.
In a production environment, the first execution of the Update New
and Modified project, on an empty target table, performs the initial
load where all source records are processed as new records. Since
the demo environment uses two different tables to simulate changes
on the source data, the original data always needs to be restored
in the target table by executing this project before running the
other 2 update projects or the job.
Demo Product Price SCD – Update New and Modified
This project updates the dimension table of the target database on a daily basis to reflect modification or addition of products in the source database. See Rules 1-3 in “Case study scenario”.
To accomplish a full update, also execute the SCD - Update Deleted project.
Demo Product Price SCD – Update Deleted
This project updates the dimension table of the target database on a daily basis to reflect deletion of products in the source database. See Rule 4 in “Case study scenario”.
To accomplish a full update, also execute the SCD - Update New and Modified project.
Job
Demo Product Price SCD – Daily Update
This job executes both the SCD - Update New and Modified and SCD - Update Deleted projects, and provides a single transformation object for performing a full update of the target dimension table. Before executing this job, execute the SCD - Initial Load project.
