Tracking data changes in a Snowflake table using the tJDBCSCDELT component

SCDELT

author
Talend Documentation Team
EnrichVersion
6.5
EnrichProdName
Talend Data Integration
Talend Open Studio for Big Data
Talend Data Management Platform
Talend Real-Time Big Data Platform
Talend Data Services Platform
Talend Big Data
Talend ESB
Talend Open Studio for MDM
Talend Big Data Platform
Talend Data Fabric
Talend Open Studio for ESB
Talend MDM Platform
Talend Open Studio for Data Integration
task
Data Governance > Third-party systems > Business Intelligence components > SCDELT components
Data Quality and Preparation > Third-party systems > Business Intelligence components > SCDELT components
Design and Development > Third-party systems > Business Intelligence components > SCDELT components
EnrichPlatform
Talend Studio

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

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