A Lookup is a Passive, Connected or Unconnected Transformation used to look up data in a relational table, view, synonym or flat file. The integration service queries the lookup table to retrieve a value based on the input source value and the lookup condition.

All about Informatica LookUp Transformation

A connected lookup receives source data, performs a lookup and returns data to the pipeline;

While an unconnected lookup is not connected to source or target and is called by a transformation in the pipeline by :LKP expression which in turn returns only one column value to the calling transformation.

Lookup can be Cached or Uncached. If we cache the lookup then again we can further go for static or dynamic or persistent cache,named cache or unnamed cache .

By default lookup transformations are cached and static.

Lookup Ports Tab

The Ports tab of Lookup Transformation contains

  • 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 input/output port in a lookup transformation.

  • Output Ports:

    Create an output port for each lookup port we want to link to another transformation. For connected lookups, we must have at least one output port. For unconnected lookups, we must select a lookup port as a return port (R) to pass a return value.

  • Lookup Port:

    The Designer designates each column of the lookup source as a 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.


    We can delete lookup ports 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 Properties Tab

Now let us have a look on the Properties Tab of the Lookup Transformation

  • Lookup Sql Override:

    Override the default SQL statement to add a WHERE clause or to join multiple tables.

  • Lookup table name:

    The base table on which the lookup is performed.

  • Lookup Source Filter:

    We can apply filter conditions on the lookup table so as to reduce the number of records. For example, we may want to select the active records of the lookup table hence we may use the condition CUSTOMER_DIM.ACTIVE_FLAG = "Y".

  • Lookup caching enabled:

    If option is checked it caches the lookup table during the session run. Otherwise it goes for uncached relational database hit. Remember to implement database index on the columns used in the lookup condition to provide better performance when the lookup in Uncached.

  • Lookup policy on multiple match:

    While lookup if the integration service finds multiple match we can configure the lookup to return the First Value, Last Value, Any Value or to Report Error.

  • Lookup condition:

    The condition to lookup values from the lookup table based on source input data. For example, IN_EmpNo=EmpNo.

  • Connection Information:

    Query the lookup table from the source or target connection. In case of flat file lookup we can give the file path and name, whether direct or indirect.

  • Source Type:

    Determines whether the source is relational database table,flat file or source qualifier pipeline.

  • Tracing Level:

    It provides the amount of detail in the session log for the transformation. Options available are Normal, Terse, Vebose Initialization, Verbose Data.

  • Lookup cache directory name:

    Determines the directory name where the lookup cache files will reside.

  • Lookup cache persistent:

    Indicates whether we are going for persistent cache or non-persistent cache.

  • Dynamic Lookup Cache:

    When checked We are going for Dyanamic lookup cache else static lookup cache is used.

  • Output Old Value On Update:

    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 used this named persistent cache file based on the base lookup table.

  • Re-cache from lookup source:

    When checked, integration service rebuilds lookup cache from lookup source when the lookup instance is called in the session.

  • Insert Else Update:

    Insert the record if not found in cache, else update it. Option is available when using dynamic lookup cache.

  • Update Else Insert:

    Update the record if found in cache, else insert it. Option is available when using dynamic lookup cache.

  • Datetime Format:

    Used when source type is file to determine the date and time format of lookup columns.

  • Thousand Separator:

    By default it is None, used when source type is file to determine the thousand separator.

  • Decimal Separator:

    By default it is "." else we can use "," and used when source type is file to determine the thousand separator.

  • Case Sensitive String Comparison:

    To be checked when we want to go for Case sensitive String values in lookup comparison. Used when source type is file.

  • Null ordering:

    Determines whether NULL is the highest or lowest value. Used when source type is file.

  • Sorted Input:

    Checked whenever we expect the input data to be sorted and is used when the source type is flat file.

  • Lookup 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 chose the option Always allowed.

In Informatica 9x version, Lookup is an Active Transformation. It has the capability to return all the records from the lookup table on multiple matches.

Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • How to Tune Performance of Informatica Aggregator Transformation

    Similar to what we discussed regarding the Performance Tuning of Joiner Transformation, the basic rule for tuning aggregator is to avoid aggregator transformation altogether...

  • Useful Informatica Metadata Repository Queries

    Informatica metadata repository stores and maintains information about all the objects in Informatica. They contain details of connection information, users, folders, mappings, sources, targets etc. These information can serve many purposes while...

  • Best Informatica Interview Questions & Answers

    Welcome to the finest collection of Informatica Interview Questions with standard answers that you can count on. Read and understand all the questions and their answers below and in the following pages to get a good grasp in Informatica. If you...

  • Implementing Informatica Persistent Cache

    You must have noticed that the "time" Informatica takes to build the lookup cache can be too much sometimes depending on the lookup table size/volume. Using Persistent Cache, you may save lot of your time. This article describes how to do it.

  • APEAR - A tool for automating Informatica Performance Tuning

    DWBIConcepts is launching APEAR – an Automated Performance Evaluation and Reporting tool for Informatica. As the name suggests, this tool will help you tune the performance of Informatica sessions fully automatically. Now don't waste your precious...

  • Calling C executable from Java Transform

    We are going to do is, to call C++ Executable from Informatica, using Passive Java Transform and capture the output of the C++ using Java and write the result to corresponding target column.

  • Informatica Java Transformation

    Feel the Power of Java programming language to transform data in PowerCenter Informatica. Java Transformation in Informatica can be used either in Active or Passive Mode.

  • Informatica Performance Tuning - Complete Guide

    This article is a comprehensive guide to the techniques and methodologies available for tuning the performance of Informatica PowerCentre ETL tool. It's a one stop performance tuning manual for Informatica.

  • All about Informatica Lookup

    A Lookup is a Passive, Connected or Unconnected Transformation used to look up data in a relational table, view, synonym or flat file. The integration service queries the lookup table to retrieve a value based on the input source value and the...

  • Using Informatica Normalizer Transformation

    Normalizer transformation is a native transformation in Informatica that can ease many complex data transformation requirements. Learn how to effectively use normalizer in this tutorial.