In Informatica, lookups are used to retrieve data based on a specified condition. There are two types of lookups: Connected and Unconnected Lookup
Contents
Here are the 7 key differences between them:
Difference between connected and unconnected lookup
| Connected Lookup | Unconnected Lookup |
| 1. Integration in Data Flow: A connected lookup is part of the mapping data flow and is connected to other transformations via transformation pipelines. | 1. Integration in Data Flow: Not part of the mapping data flow; instead, it is called as a function using the :LKP expression within another transformation. |
2. Row Processing: Processes each row of data through the lookup transformation | 2. Row Processing: Called only when necessary, typically from within an expression transformation. |
| 3. Output: Can return multiple columns and can return multiple rows if the condition matches more than one row in the lookup table. | 3. Output: Returns only one column (one value) per call, even if the condition matches multiple rows. |
| 4. Caching: Can be either cached or uncached, and the cache can be static or dynamic. | 4. Caching: Typically cached to improve performance as it’s called as needed. |
| 5. Usage: Generally used when the lookup needs to return multiple columns or is required for every row processed. | 5. Usage: Used when the lookup is conditional or when it’s required only occasionally, not for every row processed. |
| 6. Return Value: Returns data through the output ports that can be used in subsequent transformations. | 6. Return Value: Returns the value directly to the calling transformation. |
| 7. Performance: Generally slower if used for large datasets because each row has to be processed through the lookup transformation. | 7. Performance: Can be faster for large datasets since it is called conditionally and not for every row processed. |
Practical Example : Connected and Unconnected Lookup
| Connected Lookup | Unconnected Lookup |
| – Connected Lookup: If you need to enrich your incoming dataset with multiple columns from another table, you would use a connected lookup. For example, if you are processing sales data and need to add customer details like name, address, and phone number from a customer table. | – Unconnected Lookup: If you need to validate or check a specific value conditionally, you might use an unconnected lookup. For instance, if you only need to validate a customer ID and check if it exists in a customer table, you can use an unconnected lookup to return a boolean flag or specific detail only when necessary. |
Also Read :
1. What is the difference between Informatica PowerCenter and Informatica Cloud?
2.What is a Synchronization task in Informatica?
3.What is a Runtime environment in Informatica?
4.What is Check In Check Out in Informatica powercenter (Versioning)
Informatica interview questions and answers | Informatica interview questions and answers