7 Difference Between Joiner and Lookup Transformation in Informatica

During the design face, there is always a debate between developers about whether to use joiner or lookup transformation.

However, both are instrumental in achieving complex join in Informatica mapping based on business requirements. There are small nitty-gritty that I wanted to highlight. It will help you in making the apt decision.

Please go through the below comparison and see which one is suitable for your requirements.

Joiner Transformation vs Lookup Transformation in Informatica

#Joiner TransformationLookup Transformation
01The joiner transformation can join two or more homogeneous or heterogeneous sources. The lookup transformation can be applied on only one source or target (multi-join using SQL is covered below).
02The joiner transformation performs inner join (or normal join), left join, right join (master outer join or detail outer join), and full outer join operations.The lookup transformation performs only the left join.
03The joiner transformation does not support nonequi (such as ‘!=’,'<=’,’>=’) join. The lookup transformation supports equi or nonequi join.
04The joiner transformation is always connected in mapping.The feature of lookup transformation offers unconnected instances of one source. It makes accessing lookup data without directly connecting lookup into the mapping.
05The joiner transformation prepares the data cache at the start of the task run. It does not change the data cache based on changes in data during the job run. The lookup offers a persistence cache, which updates source data as per changes in data during the job run.
06In multi-match scenario joiner returns all matching records.In the multi-match scenario, the developer can define whether to return the first match, last match, or random record or report an error.
07The joiner transformation does not support SQL queries. The developer can write SQL queries in lookup transformation.

Hopefully, you have understood the difference between joiner and lookup transformation.

Thanks for Reading!

Read more related articles:

Are you looking for job?

Job openings »


Leave a Comment