Loading…

Informatica Interview Questions and Answers

By Umesh Singh
Last update: 05 Jun 2020, 16 Questions

The Informatica Powercenter, commonly known as Informatica, is an ETL/ Data Integration tool that is widely used to connect and fetch data from various heterogeneous sources and process data. If you wish to make a career using this ETL/Data Integration tool, we have created a list of the most frequently asked Informatica Interview Questions to help you crack the interviews easily and get that dream job.

Informatica is the by-product of the company, also called Informatica, which is a Software Development Company offering high-quality Data Integration Tools. Some of its products are based on ETL, data masking, data replica, and more.

Most Frequently Asked Informatica Interview Questions

1. How to remove duplicate rows in Informatica? If you want to delete duplicate records then you can use this query.

Example

                                                    

Select rowNumber FROM tableName
WHERE rowNumber  
       IN (SELECT rowNumber
             FROM (SELECT rowNumber  
                        , ROWNUMBER() OVER(PARTITION BY c1,c2,c3) dup
                     FROM tableName)
            WHERE dup> 1 )

2. What is the use of lookup in Informatica?

In Informatica, the Lookup is basically a massive transformation that is used for looking up any source, source qualifier, or any target for getting relevant data. To put it down simply, it is a type of join operation where one of the joining tables is the data source, and the other is the lookup table.

3. What are the different types of transformations in informatica?

Based on connectivity or number of rows, the transformations in Informatica can be classified as:
Based on Number of Rows:

  • Active Transformations
  • Passive Transformations

Based on Connectivity:

  • Connected Transformations
  • Unconnected Transformations

Besides that, here is a complete list of all the transformations present in Informatica

  • Source Qualifier Transformation
  • Aggregator Transformation
  • Router Transformation
  • Joiner transformation
  • Rank Transformation
  • Sequence Generator Transformation
  • Transaction Control Transformation
  • Lookup and Re-usable transformation
  • Normalizer Transformation
  • Performance Tuning for Transformation
  • External Transformation
  • Expression Transformation
4. What is the difference between Joiner and Lookup transformation in Informatica?
S.no JOINER TRANSFORMATION LOOKUP TRANSFORMATION
1. It is usually used for joining data from two more tables or source qualifiers. It is used on a single query or table record for searching records that match with incoming records.
2. The join type can be left, right, inner or outer join. The Lookup can be connected or unconnected transformation.
3. It is used when the table size is large. It is used when the table size is not too large.
5. Why do we use Lookup Transformation in Informatica?

Here are the following uses of Lookup Transformation in Informatica:

  • To retrieve a value from the lookup table based on the value from the source.
  • To get multiple rows from a lookup table.
  • Retrieving a value and then using it for various calculations.
  • For relational or flat line lookup.
6. What is the difference between a repository server and a powerhouse?
S.no Repository Server Powerhouse Server
1. It is used for controlling the complete repository containing tables, charts, and other various procedures. It is used for governing the implementation of various processes on the factors in a repository server.
2. Its primary function is to maintain integrity and consistency within a repository. It is concerned with maintaining the workflows and sessions in a repository.
7. What are the mapping parameters and variables in Informatica?

Mapping parameters and variables basically represent values in mappings and mapplets.

Mapping Parameters

  • They are used to represent a static value which can be defined before starting a session.
  • It has the same value throughout the entire session.

Mapping Variables

  • Here, the values change between the sessions.
  • The latest value of a mapping variable is saved by the Integration Service in the repository at the end of each session.
  • It can be used to override a saved value within a parameter file.
8. What is the difference between $$$sessstarttime and sessstarttime in Informatica?
S.no $$$SessStartTime SESSSTARTTIME
1. Type of In-built variable Also a type of in-built variable.
2. Returns the session start time as a string value It returns the session start time with date and date timestamp.
3. Generally used in SQ overrides. They were generally used in expressions.
9. How to run parallel sessions in Informatica?

There is a flag in the workflow of Informatica to help you run parallel sessions. Here are the steps to achieve it successfully:

  • Firstly, open the workflow monitor and click on the Edit Workflow/Worklet present in the right-click command menu for the workflow.
  • Now, inside the general tab, you must enable the to Configure Concurrent Execution property.
  • Lastly, open the Configure Concurrent Execution dialogue box. There shall be two options:

         a. Allow the concurrent run with the same instance name

            Here,the workflow name remains the same for each parallel execution.

        b. Allow the concurrent run only with a unique instance name.

            In this, you will have to create at least one instance name inside the lower half of the dialogue box. You can also specify the parameter file for each Instance created.

 

10. What are the different types of cache in Informatica?

In Informatica, there are seven types of caches:

  • Data Cache
  • Index Cache
  • Static cache
  • Dynamic cache
  • Persistent Cache
  • Re cache, also known as Refill Cache
  • Shared Cache.