Lookup Transformation in Informatica
This transformation is used to lookup data in different sources. In this, input port value is compared against lookup source column based on defined condition, and result is again fed to another transformations/target.
Q: What are the types of lookup?
Mainly lookup is divided into connected or unconnected.
- Connected lookup: This has a physical hop/pipeline connected available in dataflow from which it receives input.
- Unconnected lookup: This receives input via LKP: expression, and there is not physical hop/pipeline available.
Again, each type can be further divided into cached or un-cached.
- Cache lookup: a cache will be created of lookup table, for only mapping rows IS queries. It can be further divided into:
- Static lookup cache : Integration service creates a cache of lookup table and does not change it throughout the session run
- Dynamic lookup cache: Integration service dynamically updates or inserts data in cache. Here cache is in-sync with target table. There is a new entry in set of ports as NewLookupRow, this decides what kind of data it is, 0 – No change, 1-Inserted data, and 2- updated data.
- Un-Cache lookup: no cache is created, and for all mapping rows IS queries
- One can choose cache lookup if lookup size smaller than mapping rows, and un-cache lookup if lookup size is greater than mapping rows.
- Unconnected lookup cannot by dynamic while connected lookup supports both static and dynamic. Also, it is faster than connected lookup.
- Connected lookup can return more than one column value and caches all lookup columns, while unconnected can return only single column value (as a function) and it only caches lookup output port mentioned in lookup condition.
On the basis of cache retention even after session run, lookup can be divided as;
- Persistent cache lookup: if cache generated is preserved even after session run, i.e., Integration service does not delete such cache, and it is used in subsequent runs. To make the persistent cache in sync with the lookup table simply enable Re-cache option of the lookup transformation to rebuild the lookup cache from lookup table again
- Non-persistent cache lookup: If cache is not saved after session run.