-
Notifications
You must be signed in to change notification settings - Fork 16
How Transformations Work
This section will provide you with the answers how transformations work using examples.
Current Source:
|| ID || Name || | 12 | John Doe |
Current Target:
|| SURROGATE_KEY || ID || Name || | | | |
When record with ID=12 is read from source and there is no record with that ID on the target, new record will be added:
|| SURROGATE_KEY || ID || Name || | 1 (new) | 12 (new) | John Doe (new) |
If record changes on the source:
|| ID || Name || | 12 | Ted Mosby |
Next time this record is read from the source, target will no be affected:
|| SURROGATE_KEY || ID || Name || | 1 | 12 | John Doe |
Current Source:
|| ID || Name || | 40091 | Adventure Works Co Ltd |
Current Target:
|| SURROGATE_KEY || ID || Name || | | | |
When record with ID=40091 is read from source and there is no record with that ID on the target, new record will be added:
|| SURROGATE_KEY || ID || Name || | 1 (new) | 40091 (new) | Adventure Works Co Ltd (new) |
If record changes on the source:
|| ID || Name || | 40091 | ACME Ltd |
Next time this record is read from the source, target will be updated as well:
|| SURROGATE_KEY || ID || Name || | 1 | 40091 | ACME Ltd (upd) |
SCD2 transformation uses helper transformations SCD2 Date From, SCD2 Date To and SCD2 Is Active. SCD2 Date From is mandatory transformations and other two can be used both or just one.
Current Source:
|| ID || Name || | 331 | New York |
Current Target:
|| {"SURROGATE_KEY"} || ID || Name || {"DATE_FROM"} || {"DATE_TO"} || {"IS_ACTIVE"} || | | | | | | |
When record with ID=331 is read from source and there is no record with that ID on the target, new record will be added:
|| {"SURROGATE_KEY"} || ID || Name || {"DATE_FROM"} || {"DATE_TO"} || {"IS_ACTIVE"} || | 1 (new) | 331 (new) | New York (new) | "SCD2 Date From Insert Value" (new) | "SCD2 Date To Insert Value" (new) | "SCD2 Is Active Insert Value" (new) |
If record changes on the source:
|| ID || Name || | 331 | New Jersey |
Next time this record is read from the source, target will be updated as well:
|| {"SURROGATE_KEY"} || ID || Name || {"DATE_FROM"} || {"DATE_TO"} || {"IS_ACTIVE"} || | 1 | 331 | New York | "SCD2 Date From Insert Value" | "SCD2 Date To Update Value" (upd) | "SCD2 Is Active Update Value" (upd) | | 2 (new) | 331 (new) | New Jersey (new) | "SCD2 Date From Insert Value" (new) | "SCD2 Date To Insert Value" (new) | "SCD2 Is Active Insert Value" (new) |