Was wondering if there is anything like dynamic lookup concept where duplicates from source can be prevented from loading into target table?
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col | col9 | col10
Intermediate table (For SCD-2 identifying columns are col1, col2, col4)
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col | col9 | col10 | start_dt | end_dt
Target table (For SCD-2 identifying columns are col3, col5 and col9)
col3 | col5 | col9 | start_dt | end_dt
So basically while populating intermediate table, if changes are found in col1, col2 and col4 then only new record will be inserted and old record will be retired. But here there is every possibility that for both new and old records, col3, col5 and col9 will remain the same. So while we populate target table, we might populate multiple duplicates inspite of these records being different in both source and intermediate table. So is there any mechanism to avoid duplicates from being populated in the intial load of the target table?
In case of informatica, i can use dynamic lookup and avoid duplicates from getting populated.
--For SCD-2 identifying columns are col1, col2, col4
select normalize col1, col2, col4, period( start_dt, end_dt )