Informatica Interview Questions & Answers
There has never been a better time than this to explore a career around data warehousing, and with companies investing in tools like Informatica PowerCenter, there is a critical need for trained personnel to leverage these tools for better business insights. So we, at Mildaintrainings, have compiled a set of Question Answer type and Scenario based Informatica Interview questions, which will help you ace the Informatica interviews.
ETL is a process of Extract, Transform and Load the data into DataWarehousing in the required format for decision-making.
- IBM Datastage
- Informatica PowerCenter
- Talend Studio etc.
Informatica is an ETL tool provided by ‘Informatica Corporation’ which is used to Extract, Transform and Load all type of databases.
Informatica PowerCenter had 2 types of Tools:
Server Tools: Administrator Console, Integration Service
Client Tools: Repository Manager, Designer, Workflow Manager, Workflow Monitor
It is used to organize and secure metadata by creating folders.
The designer is used to create source definitions, target definitions, and transformations to build the mappings. Also, it is used to create mapplets which can be reused in different mappings.
It is used to store workflow metadata and connection object information in the repository. A wf contains sessions and different tasks according to the requirement. Tasks can include session, email notification, assignment task, decision task etc. Each task in a wf needs to be connected via links and also we can provide link task based on the requirements.
It is used to retrieve wf run status and session logs. A wf is a set of instructions that tells an integration service how to run the tasks. Basically, WF monitor is used to monitoring workflows and tasks.
It is a set of Source and Target definitions linked by transformation objects that define the rules for transformation.
It is a set of instructions that describe how and when to move the data from source to target.
Its nothing but a repository object that generates, modifies or passes data.
Active and Passive Transformation?
An active transformation can change the number and position of rows that pass through it. For Eg. Aggregator, Rank, Filter etc.
A passive transformation doesn’t change the number of rows that pass through it. For Eg. Expression, Sequence Generator etc.
Connected and Unconnected Transformation?
A connected transformation is connected to source definition or target definition or other transformation in a mapping
An unconnected transformation is not connected to any of the Informatica objects in a mapping.
Source Qualifier Transformation
An SQ is an active and connected transformation that reads the rows from a relational database or flat file source while running a session.
- SQ can be configured to joindata originating from same source database.
- We can specify the ofsorted ports and the integration service adds an ‘Order By’ clause to the default SQ query.
- We can use source filter to specify a certain condition.
- Select Distinct option can be used to only select unique records.
- Preand Post SQL statements can be executed before and after the SQ query.
- Also, we can write User Defined SQL query to override the default query in the SQ.
Q13). What happens if in the Source Filter property of SQ transformation we include keyword WHERE For Eg. WHERE EMP_ID > 10 ?
Normally, we use a Source filter to reduce the number of source records. If we include the string WHERE in the source filter, the Integration Service fails the session. In the above case, the correct syntax will be EMP_ID > 10.
The expression is a Passive and connected transformation which is used to calculate values in a single row. Also, it can be used to test conditional statements before writing the output results to target tables or other transformations.
For eg. we might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers or vice versa etc.
By using Date Conversion Function
IIF( IS_DATE( Column1 ) = 1, TO_DATE( Column1 , ‘YYYY-MM-DD’ ), NULL )
Sorter is an Active and Connected transformation which is used to sort data in ascending or descending order according to specified sort keys.
In sorter T/R we can select the “distinct” option in the sorted property. When Sorter transformation is configured to treat output rows as distinct, it assigns all ports as part of the sort key. The Integration Service discards duplicate rows compared during the sort operation. Since the number of Input Rows will vary as compared to the Output rows, it is treated as an Active transformation.
Since in a Flat File the distinct option in the source qualifier will be disabled, hence we can use a Sorter Transformation and check the Distinct option to get the unique rows. When we select the distinct option all the columns will the selected as keys, in ascending order by default.
An aggregator is an Active and Connected transformation which is used to perform aggregate calculations like AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN, PERCENTILE, STDDEV, SUM, and VARIANCE.
- Conditional Clauses: The conditional clause can be any clause that evaluates TRUE or FALSE.
For eg. SUM (SALARY, DEPT_ID = 10)
- Non-Aggregate expressions.
- Nested Aggregation Expression: One aggregate function within another aggregate function.
- MAX (COUNT (EMP))
- Use Sorted input which reduces the amount of data cached and improves session performance.
- Filter the unnecessary data before aggregating it.
- connecting only the necessary input/output ports to subsequent transformations in order to reduce the size of a data cache.
- If the input data is not sorted correctly, the session will fail.
- In some cases, if the input data is properly sorted, the session may fail also if the sort order by ports and the group by ports of the aggregator are not in the same order.
By using the Group By checkbox on the ports having duplicate occurring data.
A Filter transformation is an Active and Connected transformation which is used to test the data based on a certain condition that passes through it. If the condition is satisfied then the data will be passed to the next transformation or target else it will be dropped.
Q25). What is the difference between Source Qualifier transformations Source filter option and filter transformation?
SQ Source Filter
Source Qualifier transformation filters rows while reading from the source.
Filter transformation filters rows from within a mapping
Limits the records extracted from a source
Limits the records sent to a target
It can only filter rows from relational sources
Filters rows coming from any type of source system
The filter condition in the Source Qualifier transformation only uses standard SQL
Filter Transformation can define a condition using any statement or transformation function that returns either a TRUE or FALSE