Let’s discuss " Informatica joiner Transformation versus lookup transformation ”. While discussing this we will even get solutions for below brain storming questions also. Well known as interview questions. Though i very much believe this is not the right way to take an interview.
NOTE: Informatica version 9 onwards lookup is an active transformation and this post is applicable for version 8 and below.
1. Which is better in terms of performance Informatica joiner transformation or Lookup ?
2. Why do you need Informatica joiner transformation , when joining can be done through lookup also ?
3. Which join can be achieved through lookup which can’t be achieved through Informatica joiner transformation ?
4. If I want to perform equi join on a flat file then informatica joiner transformation is better then look up not ?
5. Have you ever thought, Lookup and joiner both can do join but which is better for joining (lookup or joiner) ?
Let’s analyze this. Writing down few significant aspects. Difference, individual capabilities or limitations is our baseline.
If it is only about join, than obviously joiner is better than look up. It is because lookup works row by row where else joiner works block by block. When joining through lookup you can only join flat file or relational table. Joiner can join the entire sources supported by informatica. Definitely they can be heterogeneous also. So Look up is not a better Solution. Yes, Joiner is always better considering performance aspect also.
Informatica Joiner Transformation versus Lookup
- Joiner is active where else lookup is a passive Informatica transformation.
- Joiner can’t do non equi join where else lookup can do non equi join.
- Lookup cannot do left or right outer join. Look up can only and only perform equi join.
- No equi join can’t be achieved through joiner at the same time it’s possible through lookup. Very often this gets asked in informatica interview questions.
- Multiple rows can result out with joiner. Unlike lookup, Which returns one matching row. You can even choose top or bottom from lookup properties.
- NOTE : In informatica 9 lookup can return more than one value. There is an option in informatica version 9 known as “use all values”. This option can be selected in lookup properties.