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 Transformation | Lookup Transformation |
| 01 | The 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). |
| 02 | The 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. |
| 03 | The joiner transformation does not support nonequi (such as ‘!=’,'<=’,’>=’) join. | The lookup transformation supports equi or nonequi join. |
| 04 | The 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. |
| 05 | The 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. |
| 06 | In 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. |
| 07 | The 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:
- Source qualifier transformation vs Joiner transformation in Informatica
- Active transformation vs Passive transformations in Informatica | with Examples
- Filter transformation vs Router transformations in Informatica
I have completed master in Electrical Power System. I work and write technical tutorials on the PLC, MATLAB programming, and Electrical on DipsLab.com portal.
Sharing my knowledge on this blog makes me happy. And sometimes I delve in Python programming.