In this Informatica interview questions list, you will come to know the top questions asked in the Informatica job interview. You will learn the difference between a database and a data warehouse, Informatica Workflow Manager, mapping parameter vs mapping variable, and more. Learn Informatica from Intellipaat Informatica Certification Course and fast-track your career!
1 How do you load more than 1 Max Sal in each Department through Informatica or write sql query in oracle?
You can use this kind of query to fetch more than 1 Max salary for each department.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY, RANK () OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) SAL_RANK FROM EMPLOYEES)
We can use the Rank transformation to achieve this.
Use Department_ID as the group key.
In the properties tab, select Top, 3.
The entire mapping should look like this.
This will give us the top 3 employees earning maximum salary in their respective departments.
How do you remove Duplicate records in Informatica? And how many ways are there to do it?
There are several ways to remove duplicates.
v. When you change the property of the Lookup transformation to use the Dynamic Cache, a new port is added to the transformation. NewLookupRow.
The Dynamic Cache can update the cache, as and when it is reading the data.
If the source has duplicate records, you can also use Dynamic Lookup cache and then router to select only the distinct one.
3 What is a Joiner Transformation and why it is an Active one?
A Joiner is an Active and Connected transformation used to join source data from the same source system or from two related heterogeneous sources residing in different locations or file systems.
The Joiner transformation joins sources with at least one matching column. The Joiner transformation uses a condition that matches one or more pairs of columns between the two sources.
The two input pipelines include a master pipeline and a detail pipeline or a master and a detail branch. The master pipeline ends at the Joiner transformation, while the detail pipeline continues to the target.
In the Joiner transformation, we must configure the transformation properties namely Join Condition, Join Type and Sorted Input option to improve Integration Service performance.
The join condition contains ports from both input sources that must match for the Integration Service to join two rows. Depending on the type of join selected, the Integration Service either adds the row to the result set or discards the row.
The Joiner transformation produces result sets based on the join type, condition, and input data sources. Hence it is an Active transformation.