Tracking data changes in a Snowflake table using the tJDBCSCDELT component
This scenario describes a Job that captures the employee data changes in a Snowflake table using SCD (Slowly Changing Dimensions) Type 1 and Type 2 methods implemented by the tJDBCSCDELT component, and writes both the current and historical data in a SCD dimension table.
For more technologies supported by Talend, see Talend components.
The input data contains various employee details including name, role, salary, and another id column is added to help ensuring the unicity of the input data.
At first, the following employee data is inserted to a new Snowflake table.
#id;name;role;salary
111;Mark Smith;tester;15000.00
222;Thomas Johnson;developer;18000.00
333;Teddy Brown;tester;16000.00Later, the table is updated with the following renewed employee data.
#id;name;role;salary
111;Mark Smith;tester;15000.00
222;Thomas Johnson;tester;18000.00
333;Teddy Brown;writer;17000.00
444;John Clinton;developer;19000.00You can see the role of Thomas Johnson is changed from developer to tester, the role of Teddy Brown is changed from tester to writer, and his salary is raised from 16000.00 to 17000.00. Besides, a new employee record with id 444 is inserted. In this scenario,
-
the existing name and role data will be overwritten by the new data, so SCD Type 1 method will be performed on them, and
-
the full history of the salary data will be retained, and a new record with the changed data will be always created and the previous record will be closed, so SCD Type 2 method will be performed on it.
For more information about SCD types, see SCD management methodology.