TutorialKart.comLookup Transformation in Informatica is a passive transformation used to search a lookup source and return matching data to a mapping. The lookup source can be a relational table, view, synonym, flat file, or pipeline source depending on the mapping design. A mapping can use one or more lookup transformations to fetch reference values such as department names, location details, product descriptions, status codes, or surrogate keys.
In a typical Informatica PowerCenter mapping, the Integration Service compares an input value from the pipeline with one or more lookup columns. When the lookup condition matches, selected output ports return values to the pipeline. When no match is found, the lookup output is usually NULL unless default values or additional logic are configured.
Lookup Transformation in Informatica PowerCenter
Lookup Transformation in Informatica can be used to get a related value, to perform a calculation and can update slowly changing dimension tables. We can get a value from the lookup table based on the source value, this value can be used in calculation in other Informatica transformations.
So how Informatica server queries the lookup table? Informatica Server queries the lookup table based on the ‘lookup port‘ in the transformation and compares lookup transformation port value to lookup table column values based on the lookup condition.
A lookup transformation is most useful when the mapping already has a main input flow and needs to enrich each row with reference data. For example, an employee source may contain only DeptNO, while the target requires DName and Loc. In that case, the lookup transformation can use DeptNO to retrieve the matching department name and location from a department lookup table.
Common uses of Lookup Transformation in Informatica mappings
- Retrieve related reference data: Get department name, region name, product category, or customer status from another table.
- Validate incoming source values: Check whether a key from the source exists in the lookup table.
- Derive values for target columns: Return lookup values and use them in Expression, Router, or Update Strategy transformations.
- Support dimension loading: Find existing dimension rows and handle insert or update logic in slowly changing dimension mappings.
- Improve repeated reference lookups: Use lookup cache when the same reference table is queried repeatedly during a session run.
Lookup Transformation in Informatica can be configured to perform 2 different types of lookups.
Connected mode : A connected mode lookup receives source data, performs a lookup and returns data to the pipeline.
Unconnected mode : A unconnected lookup is not connected to source (or) target and is called by transformation in the pipeline by lookup expression, which in turn returns only one column value to the calling transformation.
Connected Lookup vs Unconnected Lookup in Informatica
| Lookup type | How it is called | Returned values | Typical use |
|---|---|---|---|
| Connected lookup | Receives input ports directly from the mapping pipeline | Can return multiple output values | Enrich each incoming row with one or more reference columns |
| Unconnected lookup | Called from an expression using lookup logic | Returns one value through the return port | Use the lookup result only when a condition or expression requires it |
Use a connected lookup when the mapping needs several columns from the lookup source, such as department name and location. Use an unconnected lookup when the mapping needs only one return value or when the lookup should be called conditionally from another transformation.
Lookup Transformation Components in Informatica
When we configure lookup transformation in a mapping we use different lookup components like Lookup table, Ports, properties, condition and Metadata extensions.
Lookup table : Lookup table can be imported from the mapping source or target database using Informatica server and client.
Lookup condition : Using condition tab, we can enter the condition to determine whether input data qualifies values in the lookup table or cache.
Example : IN_DEPTNO = DEPTNO.
A lookup condition normally compares an input port from the source flow with a lookup port from the lookup source. The condition may include one column or multiple columns. For better readability, use clear naming such as IN_DEPTNO for the incoming source value and DEPTNO for the lookup table column.
-- Lookup condition example
IN_DEPTNO = DEPTNOMetadata extension : To store name and creation date with the lookup transformation, we associate information with repository metadata using metadata extension.
Lookup Transformation Port Tab in Informatica
Informatica Lookup Transformation ports tab contains options such as port name, data type an scale. It contains input port, output port and lookup port type, this lookup port type represents columns of data in the lookup table.
Input ports : Create an input port for each lookup port we want to use in the lookup condition. We must have at least one input (or) output port in a lookup transformation.
Output ports : Create an output port for each lookup ports, we want to link to another transformation. For connected lookups, we must have at least one output port and for unconnected lookup, we must select a lookup port as a return port to pass a return value.
Lookup port : The designer designates each column of the lookup source as lookup port.
Return port : An unconnected lookup transformation has one return port that returns one column of data to the calling transformation through this port.
Note : We can delete the lookup port from a relational lookup, if the mapping does not use the lookup ports which will give us performance gain. But if the lookup source is a flat file then deleting of lookup ports fails the session.
Lookup Transformation Properties in Informatica PowerCenter
Using lookup properties tab, we can configure caching properties, SQL override for the lookup, the lookup table name and can be used for tracing level for the transformation.
Lookup SQL override : We can override the default SQL statement to add WHERE clause or to join multiple tables.
Lookup table name : It is the base table on which the lookup is performed.
Connection Information : Query the lookup table form source or target connection. In each case of the flat file lookup we can give the file path and name, whether direct (or) indirect.
Source type : Source type determines whether the source is relational database table, a flat file or source qualifier pipeline.
Tracing level : It provides the amount of detailed information in the session log for the transformation options available are Normal, Target, Verbose, Initialization, verbose data.
Lookup cache persistent : It indicates whether we are going for persistent cache or non-persistent cache.
Dynamic Cache : When checked, we are going for dynamic lookup cache else static lookup cache is used.
Output old value on update : It defines whether the old value for output ports will be used to update an existing row in dynamic cache
Cache file name prefix : Lookup will use this named persistent cache file based on the base lookup table.
Re-cache from lookup source : When checked, integration service rebuild lookup cache instance is called the session.
Insert ELSE Update : Insert the record if not found in cache, else update it. Insert Else update option is available when using dynamic lookup cache.
Update ELSE Insert : Update the record if found in cache, else insert it. This option is available when using dynamic lookup cache.
Date time format : Used when source type is file to determine the data and time format of lookup columns.
Thousand separator : By default it is none, used when source type is file to determine thousand separator.
Case sensitive string comparison : Case sensitive string comparison must be checked when we want to go for case sensitive string values in lookup. Comparison is used when source type is file.
Null Ordering : It determines whether Null is the highest or lowest value. It is used when source type if file.
Sorted Input : Checked whenever we expect the input data to be sorted and is used when the source type is flat file.
Look up source is static : When checked it assumes that the lookup source is not going to change during the session run.
Pre-build lookup cache : Default option is Auto. If we want the Integration service to start building the cache whenever the session just begins. We can change the option always allowed.
Lookup Cache Types in Informatica
Lookup caching controls how the Integration Service stores and searches lookup data during the session. Choosing the correct cache option can reduce repeated database calls and improve mapping performance when the lookup source is reused for many input rows.
| Lookup cache option | Meaning in a mapping | When to use it |
|---|---|---|
| Static cache | Lookup cache is built and used without changing lookup data during the session | Reference table does not need to be updated by the mapping |
| Dynamic cache | Lookup cache can be updated as rows are inserted or updated | Dimension loading and slowly changing dimension logic |
| Persistent cache | Cache files can be reused between session runs | Large lookup data that does not change often |
| Uncached lookup | Lookup query is executed against the lookup source instead of using cache | Small number of lookups or data that must be checked directly each time |
For large lookup tables, avoid returning unused columns. Keep only the lookup ports needed for the condition and output ports needed by the mapping. Also check whether the lookup condition uses indexed columns in the database, especially when the lookup source is relational.
Lookup Transformation vs Joiner Transformation in Informatica
Lookup and Joiner transformations are often compared because both can bring data from another source into a mapping. They solve different design problems. A lookup is usually used for reference data retrieval, while a Joiner transformation is used to join two data streams in the mapping pipeline.
| Comparison point | Lookup Transformation | Joiner Transformation |
|---|---|---|
| Primary purpose | Search a lookup source and return matching values | Join two source pipelines |
| Input requirement | Uses one pipeline input and a lookup source | Requires two input pipelines |
| Common join behavior | Works like a lookup match against reference data | Supports joining two flows based on join condition |
| Best suited for | Reference tables, code descriptions, surrogate key lookups | Combining rows from two active source flows |
| Returned columns | Returns selected lookup output ports | Passes columns from both joined pipelines |
Use Lookup Transformation when the mapping needs a value from a reference source for each incoming row. Use Joiner Transformation when the mapping needs to combine two source flows as part of the transformation pipeline.
Creating Informatica Lookup Transformation with Example
In our previous Aggregator Transformation, we have created target using SqlPlus, but now we are going to create target table using Informatica PowerCenter Designer tools. To create target table open Designer tools and navigate to Targets | Create. Create Target table as per mapping flow diagram.
Now a new window will be opened to create target table, Enter a new name for this target table and select the database as Oracle.
- Click on Create button to create empty table and finally click on Done button.
Our target table has been created successfully, now we have to edit the table to add new columns by renaming the column names. Navigate to Columns and click on Add new column icon as shown below.
As shown above, our target table in Lookup Transformation in Informatica has six ports after creating click on OK button, then the target table will be appeared in the workspace as shown below.
- Go to Target Menu | Generate / Execute SQL. Now again click on Generate and execute.
- Enter Filename and click on Generate and execute button.
***** Script LKP_EMP.SQL successfully generated.
***** Running ODBC SQL script: file LKP_EMP.SQL ...
CREATE TABLE Lookup_EMP
(
DeptNO number,
DName varchar2(30),
Loc varchar2(30),
EmpNO number,
Ename varchar2(30),
Sal number
)
- Select your ODBC data source and enter username and password and finally click on connect button.
Go to mapping menu and click on create button to create new mapping and name it as m_Lookup_DName_LOC.
Click on OK button, now drag and drop the source and target tables in to the workspace are of mapping window as shown below. Now go to transformation tab to create Lookup transformation in Informatica PowerCenter.
Creating Lookup Transformation in Informatica Mapping Designer
Go to transformation tab and click on create button. Now select Lookup transformation type and enter a new name for the transformation.
- Click on create button and another new window will be opened to select the target table. Enter target ODBC username and password.
- Select the target table and click on import button, then two options will popup from rational table are from Flat file. Select Rational table and click on OK button.
- Connect DeptNo, EmpNo, Sal from SQ_EMP Source Qualifier to LKP_EMP target table. Now drag and drop DeptNo from SQ_EMP to T_LKP table and connect the Dname from T_LKP to LKP_EMP table.
- Double click on T_LKP header to edit lookup transformation and navigate to Condition tab to add new condition.
- Click on new condition icon and make sure Lookup table condition as DEPTNO and Transformation Port as DEPTNO1 as shown below.
The remaining process for creating the Session, creating the Workflow is same., but at the session properties tab, we need to select or map the Lookup Transformation connection type to Source connection, which is similar what we done to map the Source Table to Source connection and Target table is mapped to Target connection.
Session settings to check after adding Lookup Transformation
- Map the source connection, target connection, and lookup connection correctly in session properties.
- Check that the lookup table or file is available from the Integration Service machine.
- Verify that the lookup condition uses matching data types. For example, do not compare a character department number with a numeric department number without conversion.
- Review cache settings before running a large mapping. A wrong cache setting may increase database calls or create stale lookup results.
- Run the workflow and check the session log for lookup cache build messages, lookup SQL, rejected rows, and connection errors.
How Lookup Transformation works with cache in Informatica
When lookup caching is enabled, the Integration Service builds a lookup cache from the lookup source and then searches the cache for matching rows. This is useful when many input records use the same lookup reference data. If caching is disabled, the lookup query is executed against the source for each lookup request, which may be suitable only for limited cases.
For a cached lookup, the lookup condition should be selective and the lookup source should contain only the required rows and columns. If the lookup table contains a large amount of data, consider a lookup SQL override to filter the reference data before it is cached.
-- Example lookup SQL override pattern
SELECT DeptNO, DName, Loc
FROM Dept
WHERE Active_Flag = 'Y'The SQL override should return the lookup columns required by the transformation. After adding an override, validate the mapping and confirm that port names, data types, and lookup conditions still match the returned columns.
Performance tips for Lookup Transformation in Informatica
- Use only the lookup ports required for the condition and output. Unused ports increase cache size.
- Use a lookup SQL override when the lookup source has many rows but only a filtered subset is needed.
- Prefer cached lookup for repeated reference lookups when the lookup data is stable during the session.
- Use dynamic lookup cache only when the mapping must insert or update lookup cache rows during the session.
- Check database indexes on relational lookup columns used in the lookup condition.
- Avoid verbose data tracing in regular runs because it can make session logs large.
Common errors in Informatica Lookup Transformation setup
- No matching row returned: Check the lookup condition, source values, null values, and data type compatibility.
- Session fails while building cache: Check lookup connection, table permissions, file path, and cache directory space.
- Wrong value returned: Verify whether the lookup condition can match more than one row and whether the correct lookup policy is configured.
- Mapping validation error: Check that required input, output, lookup, and return ports are correctly selected.
- Unexpected case mismatch: Review case-sensitive comparison settings, especially for file lookups and character keys.
Informatica Lookup Transformation QA checklist
- The lookup source is clearly identified as relational, flat file, or pipeline source.
- The lookup condition compares correct input ports with correct lookup ports.
- The mapping uses connected lookup when multiple output values are required.
- The mapping uses unconnected lookup only when one return value is enough.
- Lookup cache settings match the data volume and update behavior of the lookup source.
- The session properties include the correct lookup connection mapping.
FAQs on Lookup Transformation in Informatica
What is a Lookup Transformation in Informatica?
A Lookup Transformation in Informatica is a passive transformation that searches a lookup source and returns matching values to the mapping pipeline. It is commonly used to retrieve reference data such as department name, location, product category, or dimension key.
When should I use a connected lookup in Informatica?
Use a connected lookup when the transformation should receive rows directly from the mapping pipeline and return one or more columns to downstream transformations or targets. It is suitable when the lookup result is needed for every row or for multiple output fields.
When should I use an unconnected lookup in Informatica?
Use an unconnected lookup when only one return value is required and the lookup should be called from another transformation, usually through an expression. This is useful when the lookup needs to run only under a specific condition.
What is lookup cache in Informatica?
Lookup cache is a stored copy of lookup data used during a session run. Instead of querying the lookup source repeatedly for every input row, the Integration Service can search the cache and return matching values more efficiently.
What is the difference between Lookup and Joiner Transformation in Informatica?
Lookup Transformation searches a lookup source to return matching reference values for an input row. Joiner Transformation combines two source pipelines using a join condition. Use lookup for reference data retrieval and joiner for joining two active data flows.