Tracking data changes using Slowly Changing Dimensions (type 0 through type 3) - Cloud - 8.0

SCD

Version
Cloud
8.0
Language
日本語
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
ジョブデザインと開発 > サードパーティーシステム > ビジネスインテリジェンスコンポーネント > SCD
データガバナンス > サードパーティーシステム > ビジネスインテリジェンスコンポーネント > SCD
データクオリティとプレパレーション > サードパーティーシステム > ビジネスインテリジェンスコンポーネント > SCD
Last publication date
2024-02-21

This scenario describes a Job that stores and manages both the current and historical employee data in a MySQL table using SCD (Slowly Changing Dimensions).

For more technologies supported by Talend, see Talendコンポーネント.

The input data contains various employee details including name, age, role, and 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 MySQL table using SCD:

id;name;age;role;salary
1;Mark Smith;30;tester;11000.00
2;Thomas Johnson;32;developer;12000.00
3;Teddy Brown;33;tester;13000.00

Then the table is updated using SCD with the following renewed employee data.

id;name;age;role;salary
1;Mark Smith;31;tester;11000.00
2;Thomas Johnson;32;developer;12000.00
3;Teddy Brown;33;writer;13500.00

You can see the age of Mark Smith is updated from 30 to 31, the role of Teddy Brown is changed from tester to writer, and his salary is raised from 13000.00 to 13500.00. In this scenario,

  • we don't want to track the data changes for the name field, so we will perform Type 0 SCD on it,

  • we want the new age data to overwrite the existing data, so we will perform Type 1 SCD on it,

  • we want to retain the full history of the role data, and always create a new record with the changed data and close the previous record, so we will perform Type 2 SCD on it,

  • we want to keep the current and previous dimension values for the salary field, so we will perform Type 3 SCD on it.

For more information about SCD types, see SCD management methodology.