Informatica Interview Questions on Lookup Transformation

1. What is a lookup transformation?

A lookup transformation is used to look up data in a flat file, relational table, view, and synonym.

2. What are the tasks of a lookup transformation?

The lookup transformation is used to perform the following tasks?

Get a related value: Retrieve a value from the lookup table based on a value in the source.

Perform a calculation: Retrieve a value from a lookup table and use it in a calculation.

Update slowly changing dimension tables: Determine whether rows exist in a target.

3. How do you configure a lookup transformation?

Configure the lookup transformation to perform the following types of lookups:

Relational or flat file lookup

Pipeline lookup

Connected or unconnected lookup

Cached or uncached lookup

4. What is a pipeline lookup transformation?

A pipeline lookup transformation is used to perform lookup on application sources such as JMS, MSMQ or SAP. A pipeline lookup transformation has a source qualifier as the lookups source.

5. What is connected and unconnected lookup transformation?

A connected lookup transformation is connected the transformations in the mapping pipeline. It receives source data, performs a lookup and returns data to the pipeline.

An unconnected lookup transformation is not connected to the other transformations in the mapping pipeline. A transformation in the pipeline calls the unconnected lookup with a :LKP expression.

6. What are the differences between connected and unconnected lookup transformation?

• Connected lookup transformation receives input values directly from the pipeline. Unconnected lookup transformation receives input values from the result of a :LKP expression in another transformation.
• Connected lookup transformation can be configured as dynamic or static cache. Unconnected lookup transformation can be configured only as static cache.
• Connected lookup transformation can return multiple columns from the same row or insert into the dynamic lookup cache. Unconnected lookup transformation can return one column from each row.
• If there is no match for the lookup condition, connected lookup transformation returns default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged. If there is no match for the lookup condition, the unconnected lookup transformation returns null.
• In a connected lookup transformation, the cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports. In an unconnected lookup transformation, the cache includes all lookup/output ports in the lookup condition and the lookup/return port.
• Connected lookup transformation passes multiple output values to another transformation. Unconnected lookup transformation passes one output value to another transformation.
• Connected lookup transformation supports user-defined values. Unconnected lookup transformation does not support user-defined default values.

7. How do you handle multiple matches in lookup transformation? or what is "Lookup Policy on Multiple Match"?

"Lookup Policy on Multiple Match" option is used to determine which rows that the lookup transformation returns when it finds multiple rows that match the lookup condition. You can select lookup to return first or last row or any matching row or to report an error.

8. What is "Output Old Value on Update"?

This option is used when dynamic cache is enabled. When this option is enabled, the integration service outputs old values out of the lookup/output ports. When the Integration Service updates a row in the cache, it outputs the value that existed in the lookup cache before it updated the row based on the input data. When the Integration Service inserts a new row in the cache, it outputs null values. When you disable this property, the Integration Service outputs the same values out of the lookup/output and input/output ports.

9. What is "Insert Else Update" and "Update Else Insert"?

These options are used when dynamic cache is enabled.

Insert Else Update option applies to rows entering the lookup transformation with the row type of insert. When this option is enabled the integration service inserts new rows in the cache and updates existing rows when disabled, the Integration Service does not update existing rows.

Update Else Insert option applies to rows entering the lookup transformation with the row type of update. When this option is enabled, the Integration Service updates existing rows, and inserts a new row if it is new. When disabled, the Integration Service does not insert new rows.

10. What are the options available to configure a lookup cache?

The following options can be used to configure a lookup cache:

• Persistent cache
• Recache from lookup source
• Static cache
• Dynamic cache
• Shared Cache
• Pre-build lookup cache

11. What is a cached lookup transformation and uncached lookup transformation?

Cached lookup transformation: The Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation. The Integration Service stores condition values in the index cache and output values in the data cache. The Integration Service queries the cache for each row that enters the transformation.

Uncached lookup transformation: For each row that enters the lookup transformation, the Integration Service queries the lookup source and returns a value. The integration service does not build a cache.

12. How the integration service builds the caches for connected lookup transformation?

The Integration Service builds the lookup caches for connected lookup transformation in the following ways:

• Sequential cache: The Integration Service builds lookup caches sequentially. The Integration Service builds the cache in memory when it processes the first row of the data in a cached lookup transformation.
• Concurrent caches: The Integration Service builds lookup caches concurrently. It does not need to wait for data to reach the Lookup transformation.

13. How the integration service builds the caches for unconnected lookup transformation?

The Integration Service builds caches for unconnected Lookup transformations as sequentially.

14. What is a dynamic cache?

The dynamic cache represents the data in the target. The Integration Service builds the cache when it processes the first lookup request. It queries the cache based on the lookup condition for each row that passes into the transformation. The Integration Service updates the lookup cache as it passes rows to the target. The integration service either inserts the row in the cache or updates the row in the cache or makes no change to the cache.

15. When you use a dynamic cache, do you need to associate each lookup port with the input port?

Yes. You need to associate each lookup/output port with the input/output port or a sequence ID. The Integration Service uses the data in the associated port to insert or update rows in the lookup cache.

16. What are the different values returned by NewLookupRow port?

The different values are

0 - Integration Service does not update or insert the row in the cache.

1 - Integration Service inserts the row into the cache.

2 - Integration Service updates the row in the cache.

17. What is a persistent cache?

If the lookup source does not change between session runs, then you can improve the performance by creating a persistent cache for the source. When a session runs for the first time, the integration service creates the cache files and saves them to disk instead of deleting them. The next time when the session runs, the integration service builds the memory from the cache file.

18. What is a shared cache?

You can configure multiple Lookup transformations in a mapping to share a single lookup cache. The Integration Service builds the cache when it processes the first Lookup transformation. It uses the same cache to perform lookups for subsequent Lookup transformations that share the cache.

19. What is unnamed cache and named cache?

• Unnamed cache: When Lookup transformations in a mapping have compatible caching structures, the Integration Service shares the cache by default. You can only share static unnamed caches.
• Named cache: Use a persistent named cache when you want to share a cache file across mappings or share a dynamic and a static cache. The caching structures must match or be compatible with a named cache. You can share static and dynamic named caches.

20. How do you improve the performance of lookup transformation?

• Create an index on the columns used in the lookup condition
• Place conditions with equality operator first
• Cache small lookup tables.
• Join tables in the database: If the source and the lookup table are in the same database, join the tables in the database rather than using a lookup transformation.
• Use persistent cache for static lookups.
• Avoid ORDER BY on all columns in the lookup source. Specify explicitly the ORDER By clause on the required columns.
• For flat file lookups, provide Sorted files as lookup source.