Tracking data changes in a Snowflake table using the tJDBCSCDELT component
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.
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.
|
1 2 3 4 |
#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.
|
1 2 3 4 5 |
#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 is changed from
Browntester 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.