Tracking data changes in a PostgreSQL table using the tPostgreSQLSCDELT component
This scenario describes a Job that captures the employee data changes in a PostgreSQL table using SCD (Slowly Changing Dimensions) Type 1 and Type 2 methods implemented by the tPostgreSQLSCDELT 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
1;Mark Smith;tester;11000.00
2;Thomas Johnson;developer;12000.00
3;Teddy Brown;tester;13000.00Later, the table is updated with the following renewed employee data.
#id;name;role;salary
1;Mark Smith;tester;15000.00
2;Thomas Johnson;tester;18000.00
3;Teddy Brown;writer;17000.00
4;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 13000.00 to 17000.00. Besides, a new employee record with id 4 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.