Updated June 15, 2023
Introduction to Informatica Scenario Based Interview Questions
Informatica is an essential tool used in data warehousing, which helps manage large amounts of data and report data analysis. Below are some questions which will be helpful when you attend an interview on Informatica.
You have finally found your dream job in Informatica but are wondering how to crack the 2023 Informatica interview and what could be the probable Informatica Scenario-Based Interview Questions. Every interview is different, and the job scope is different too. Keeping this in mind, we have designed the most common Informatica Scenario based on Interview Questions and Answers to help you get success in your interview.
Informatica Scenario-Based Interview Questions
Some of the most essential 2023 Informatica Scenario-Based Interview Questions that are frequently asked in an interview are as follows:
1. How to remove duplicate records in Informatica? Explain the different ways to do it.
Answer:
There are many ways of eliminating duplicates:
- If there are duplicates in the source database, a user can use the property in the source qualifier. Users must go to the Transformation tab and checkmark the ‘Select Distinct’ option. Also, a user can use SQL override for the same purpose. The user can go to the Properties tab and, in the SQL query tab, write the distinct query.
- Users can use Aggregator and select ports as keys to get distinct values. If a user wishes to find duplicates in the entire column, all ports should be chosen as a group by key.
- The user can also use Sorter with Sort distinct property to get distinct values.
- Expression and filter transformation can also identify and remove duplicate data. If data is not sorted, then it needs to be sorted first.
- A new port is added when a property in the Lookup transformation is changed to use Dynamic cache. This cache is updated as and when data is read. If a source has duplicate records, the user can look in the Dynamic lookup cache, and then the router selects only one distinct form.
2. What is the difference between Source qualifier and filter transformation?
Answer:
Source qualifier transformation represents rows that the Integration service reads in a session. It is an active transformation. Using source qualifier, the following tasks can be fulfilled:
- When two tables from the same source database with a primary key–foreign critical transformation relationship exist, the sources can be linked to one source qualifier transformation.
- Filtering rows when the Integration service adds a where clause to the user’s default query.
- When a user wants an outer join instead of an inner one, join information is replaced by metadata specified in the SQL query.
- When sorted ports are specified, the integration service uses the order by clause to the default query.
- If a user chooses to find a distinct value, then the integration service selects particular to the specified query.
When the data we need to filter is not a relational source, the user should use Filter transformation. It helps the user to meet the specified filter condition to let go or pass through. It will drop the rows that do not meet the state, and multiple conditions can be specified.
3. Design a mapping to load the last 3 rows from a flat file into the target?
Answer:
Suppose the flat file in consideration has the below data:
Column A
Aanchal
Priya
Karishma
Snehal
Nupura
Step 1: Assign row numbers to each record. Generate row numbers using expression transformation by creating a variable port and incrementing it by 1. After this, assign this variable port to the output port. After expression transformation, the ports will be as follows.
Variable_count= Variable_count+1
O_count=Variable_count
Create a dummy output port for the same expression transformation and assign 1 to that port. This dummy port will always return 1 for each row.
Finally, the transformation expression will be as follows:
Variable_count= Variable_count+1
O_count=Variable_count
Dummy_output=1
The output of this transformation will be :
Column A O_count Dummy_output
Aanchal 1 1
Priya 2 1
Karishma 3 1
Snehal 4 1
Nupura 5 1
Step 2: Pass the above output to an aggregator and do not specify any group by the condition. A new output port should be created as O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row. This step’s final output will have a dummy port with a value as 1, and O_total_records will have the total number of records in the source. The aggregator output will be: O_total_records, Dummy_output
5 1
Step 3: Pass this output to joiner transformation and apply a join on the dummy port. The property sorted input should be checked in the joiner transformation. Only then can the user connect both expression and aggregator transformation to joiner transformation. Joiner transformation condition will be as follows:
Dummy_output (port from aggregator transformation) = Dummy_output (port from expression transformation)
The output of joiner transformation will be
Column A o_count o_total_records
Aanchal 1 5
Priya 2 5
Karishma 3 5
Snehal 4 5
Nupura 5 5
Step 4: After the joiner transformation, we can send this output to filter transformation and specify the filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2
The filter condition, as a result, will be
O_total_records – O_count <=2
The final output of filter transformation will be :
Column A o_count o_total_records
Karishma 3 5
Snehal 4 5
Nupura 5 5
4. How to load only NULL records into the target? Explain using mapping flow.
Answer:
Consider the below data as a source
Emp_Id Emp_Name Salary City Pincode
619101 Aanchal Singh 20000 Pune 411051
619102 Nupura Pattihal 35000 Nagpur 411014
NULL NULL 15000 Mumbai 451021
The target table also has a table structure as a source. We will have two tables containing NULL values and others that would not contain NULL values.
The mapping can be as follows:
SQ –> EXP –> RTR –> TGT_NULL/TGT_NOT_NULL
EXP – Expression transformation create an output port
O_FLAG= IIF ( (ISNULL(emp_id) OR ISNULL(emp_name) OR ISNULL(salary) OR ISNULL(City) OR ISNULL(Pincode)), ‘NULL’,’NNULL’)
RTR – Router transformation two groups
Group 1 connected to TGT_NULL ( Expression O_FLAG=’NULL’)
Group 2 connected to TGT_NOT_NULL ( Expression O_FLAG=’NNULL’)
5. Explain how the performance of joiner condition can be increased?
Answer:
The performance of the joiner condition can be increased by following some simple steps.
1. The user must perform joins whenever possible. When for some tables, this is not possible, then a user can create a stored procedure and then join the tables in the database.
2. Data should be sorted before applying to join whenever possible.
3. When data is unsorted, then a source with fewer rows should be considered a master source.
4. For sorted joiner transformation, a source with less duplicate key values should be considered a master source.
Recommended Article
We hope that this EDUCBA information on “Informatica Scenario based Interview Questions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
