1. Introduction in IICS
The Lookup Transformation in Informatica Intelligent Cloud Services (IICS) is used to retrieve related information from a lookup source based on a matching condition.
It is commonly used in ETL pipelines when data from one source needs to be compared with or enriched using data from another source.
Example
A sales file contains Customer_ID, but not the customer name. A Lookup Transformation can retrieve the customer name from the customer master table.
2. How Lookup Works in IICS
A typical lookup process is:
Source Data
↓
Lookup Transformation
↓
Match Customer ID
↓
Customer Master
↓
Target
For every incoming record, IICS searches the lookup source for a matching value.
3. Example
Source Data
| Customer_ID | Sales |
|---|---|
| 101 | 5000 |
| 102 | 7500 |
Customer Master
| Customer_ID | Customer_Name |
|---|---|
| 101 | Rahul |
| 102 | Priya |
Using Customer_ID as the lookup condition, the target can contain:
| Customer_ID | Customer_Name | Sales |
|---|---|---|
| 101 | Rahul | 5000 |
| 102 | Priya | 7500 |
4. Lookup Configuration in IICS
A Lookup Transformation generally involves:
- Select Lookup Source
- Configure Lookup Ports
- Define Lookup Condition
- Select Required Lookup Fields
- Configure Match/No-Match Behavior
Example Lookup Condition
Source.Customer_ID = Lookup.Customer_ID5. Connected and Unconnected Lookup
Connected Lookup
A connected Lookup is directly connected to the mapping pipeline and returns lookup values to downstream transformations.
It is commonly used when lookup data needs to be added to every incoming record.
Unconnected Lookup
An unconnected Lookup is called only when required using a lookup function.
It can be useful when the lookup operation is required conditionally or at specific points in the mapping.
6. Cached Lookup
IICS can use lookup caching to improve performance when lookup data is reused multiple times.
Instead of repeatedly accessing the lookup source, the required lookup information can be held in memory/cache during processing.
This can reduce database access and improve mapping performance, particularly for frequently accessed lookup data.
7. Handling No-Match Records
Sometimes an incoming record does not have a corresponding record in the lookup source.
For example:
Source Customer_ID: 105
Lookup Customer_ID: 101, 102, 103
Since 105 does not exist in the lookup source, the record is considered a no-match.
Depending on the business requirement, the record can be:
- Passed with default values
- Sent to a separate output
- Rejected
- Processed using alternative logic
8. Real-World Use Case
Consider an enterprise sales pipeline:
Sales Data
↓
Lookup Customer Master
↓
Get Customer Name & Region
↓
Transformation
↓
Snowflake Target
The sales source may contain only a customer ID, while the customer master contains customer name, region, category, and other attributes.
The Lookup Transformation enriches the sales data before loading it into Snowflake.
9. Best Practices
- Select only the required lookup columns.
- Use appropriate lookup conditions.
- Avoid unnecessary lookups on very large datasets.
- Use caching when lookup data is repeatedly accessed.
- Handle no-match records explicitly.
- Ensure lookup keys have appropriate data types and formats.
- Validate lookup results before loading the target.
Conclusion
The Lookup Transformation is one of the most commonly used transformations in IICS for data enrichment and reference-data matching.
It allows developers to retrieve information from another source and combine it with incoming records. Proper use of lookup conditions, caching, and no-match handling can improve both the accuracy and performance of ETL pipelines.