Tracking data changes in a PostgreSQL table using the tPostgreSQLSCDELT component

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.00

Later, 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.00

You 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.