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, but is not part of the use case implementation.
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”.
Before executing this project, execute the SCD - Initial Load project. To accomplish a full update, also execute the SCD - Update Deleted project.
When the ‘Demo Product Price SCD - Initial
Load’ project is executed for the first time, all source
records are processed as new records in target tables that are empty.
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”.
Before executing this project, execute the SCD - Initial Load project. 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.
