Any dynamic lookup concept in teradata?

Database
Enthusiast

Any dynamic lookup concept in teradata?

Hi all,

Was wondering if there is anything like dynamic lookup concept where duplicates from source can be prevented from loading into target table?

Source 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.

Thanks!

1 REPLY
Teradata Employee

Re: Any dynamic lookup concept in teradata?

--For SCD-2 identifying columns are col1, col2, col4

select normalize col1, col2, col4, period( start_dt, end_dt )
from table1;