Slowly Changing Dimension – Type 1 is useful when you need not to maintain the historical data. Below are the two cases explained for implementation of SCD -Type 1 using ‘Auto Correct Load’ Option available in the target table.
Case 1: New record from source
The new records from source will be passed/written to the target table as ‘Insert’ records.
Case 2: Existing record with update in non-key columns
The update value from the source will overwrite the existing values in the target table. These records will be passed as ‘update’ records.
How to implement?
- Map your source to the target table. If needed any transformations use a query/any other you like.
- Map your output schema of the last transform to the target table.
- Open the target table and navigate to the ‘Options’ tab.
- Under ‘Advanced’ section, in ‘Update Control’ set ‘Auto Correct Load’ option to ‘Yes’. This should set the option ‘Allow merge or upsert’ option to ‘Yes’ by default.
- Save your mapping. Run the job.
Screenshots of mapping:
Data in the table before implementation of SCD – Type 1:
Mapping of the Dataflow:
Query Transform Mapping:
Target Table Options:
Data in the after before implementation of SCD1:
Company Name changed from Tgw to Microsystems Inc for Customer ID 12345
Phone Number changed from 1-837-853-9045 to 1-837-853-9055 for Customer ID 12345
New entry for CustomerId 12370
How it Works?
Dataflow generates a merge statement when ‘Auto Correct Load’ option is set to yes. If you set the second option ‘Allow merge or upsert’ to No manually, the dataflow generates a transact sql code.