Informatica Interview Questions and Answers
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
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 )
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.
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
| 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. |
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.
| 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. |
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.
| 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. |
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.
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.