fbpx
HOMEBLOGSINFORMATICA INTERVIEW QUESTIONS

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.

Q1). What is ETL and what are ETL Tools?

ETL is a process of Extract, Transform and Load the data into DataWarehousing in the required format for decision-making.

ETL TOOLS:

  • IBM Datastage
  • Informatica PowerCenter
  • Abinitio
  • Talend Studio etc.

Q2). What is Informatica?

Informatica is an ETL tool provided by ‘Informatica Corporation’ which is used to Extract, Transform and Load all type of databases.

Q3). What is Informatica PowerCenter Tools?

Informatica PowerCenter had 2 types of Tools:

Server Tools: Administrator Console, Integration Service

Client Tools: Repository Manager, Designer, Workflow Manager, Workflow Monitor

Q4). What is Repository Manager?

It is used to organize and secure metadata by creating folders.

Q5). What is Designer?

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.

Q6). What is Workflow Manager?

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.

Q7). What is Workflow Monitor?

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.

Q8). Define Mapping?

It is a set of Source and Target definitions linked by transformation objects that define the rules for transformation.

Q9). Define Session?

It is a set of instructions that describe how and when to move the data from source to target.

Q10). Define Transformation?

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

Q11). What is SQ 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.

Q12). What are the tasks can be performed using SQ?

  • 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.

 Expression Transformation

Q14). What is an Expression Transform?

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.

Q15). How do we convert a Date field coming as data type string from a flat file?

By using Date Conversion Function

IIF( IS_DATE( Column1 ) = 1, TO_DATE( Column1 , ‘YYYY-MM-DD’ ), NULL )

Sorter Transformation

Q16). What is a Sorter Transformation?

Sorter is an Active and Connected transformation which is used to sort data in ascending or descending order according to specified sort keys.

Q17). Why is Sorter an Active Transformation?

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.

Q18). How to select distinct rows for flat file sources?

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.

Aggregator Transformation

Q19). What is an Aggregator Transformation?

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.

Q20). What are all expressions supported by Aggregator Transformation?

  • Conditional Clauses: The conditional clause can be any clause that evaluates TRUE or FALSE.

For eg. SUM (SALARY, DEPT_ID = 10)

  • Non-Aggregate expressions.

IIF(DEPT_ID=10,DEPT_NM,DEPT_ID||’_’||DEPT_NM)

  • Nested Aggregation Expression: One aggregate function within another aggregate function.
  • MAX (COUNT (EMP))

Q21). How to improve the performance of Aggregator Transformation?

  • 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.

Q22). Because of which condition selecting Sorted Input in aggregator may fail the session?

  • 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.

Q23). How can we delete duplicate records using Aggregator?

By using the Group By checkbox on the ports having duplicate occurring data.

Filter Transformation

Q24). What is a Filter Transformation?

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

Filter Transformation

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

 

Corporate
close slider

Your Name (required)

Your Email (required)

Contact Number

Course

Location

Company

No Of Participant

Query