In Informatica PowerCenter, the Lookup Transformation is used to retrieve data from a lookup table based on a given input. There are two types of lookup transformations:
| Feature | Connected Lookup | Unconnected Lookup |
|---|---|---|
| Definition | Directly connected to the data flow in a mapping. | Called as a function using an expression in another transformation. |
| Invocation | Executes for every row in the pipeline. | Called only when needed using the :LKP function. |
| Input Type | Takes multiple columns as input. | Takes only one input parameter. |
| Output | Returns multiple columns to the data flow. | Returns a single value (first matching row). |
| Performance | Slower if used repeatedly, as it runs for every row. | Faster when used selectively, since it is called only when required. |
| Caching | Supports both dynamic and static caching. | Supports only static caching. |
| Use Case | Used when multiple lookup values are needed for each row. | Used when only a single value is required occasionally. |
Connected Lookup Example:
Suppose you're processing customer transactions, and you need to retrieve customer details (name, city, phone) for each transaction. A connected lookup is better because you need multiple columns.
Unconnected Lookup Example:
Suppose you only need to check if a customer exists in a reference table and return just the customer ID. An unconnected lookup is more efficient.
