“In the SCD Type2 we have three options to recognise the newly added rows: 1. Version number 2. Flag value 3. Effective Date Range”
To insert new rows into target you essentially need some way of identifying which rows in the source are new.
By new rows, of course, we mean rows that have not already been inserted into target before.
This can be achieved in many ways, some of which are -
1. Version number - The version number in the source is incremented everytime there is a change in a row.
When populating target from source, you compare the IDs of the rows.
If an ID exists in source but doesn't exist in target - this is a new row and must be inserted in target.
If the version number for the same ID is different in source and target - this is a modified row and must be updated in target.
2. Flag Value - Any row with flag value "set" is considered new and inserted into target.
Once you insert a row in target, you "unset" its flag value so you don't insert it again during next run.
3. Effective Date Range - All rows in source include a date value column.
The target keeps track of the date range that has already been pulled.
All rows newer that the already pulled date range are considered new and are inserted in target.