Informatica Interview Questions on Transformations

The transformations which used mostly are listed in the below table

Aggregator	Active/Connected
Expression	Passive/Connected
Filter	Active/Connected
Joiner	Active/Connected
Lookup	Passive/Connected or Unconnected
Normalizer	Active/Connected
Rank	Active/Connected
Router	Active/Connected
Sequence Generator	Passive/Connected
Sorter	Active/Connected
Source Qualifier	Active/Connected
SQL	Active or Passive/Connected
Stored Procedure	Passive/Connected or Unconnected
Transaction Control	Active/Connected
Union	Active/Connected
Update Strategy	Active/Connected

1. What is a transformation?
A transformation is a repository object that generates, modifies, or passes data.

2. What is an active transformation?

An active transformation is the one which changes the number of rows that pass through it.
Example: Filter transformation

3. What is a passive transformation?

A passive transformation is the one which does not change the number of rows that pass through it.
Example: Expression transformation

4. What is a connected transformation?

A connected transformation is connected to the data flow or connected to the other transformations in the mapping pipeline.
Example: sorter transformation

5. What is an unconnected transformation?

An unconnected transformation is not connected to other transformations in the mapping. An unconnected transformation is called within another transformation and returns a value to that transformation.
Example: Unconnected lookup transformation, unconnected stored procedure transformation

6. What are multi-group transformations?

Transformations having multiple input and output groups are called multi-group transformations.
Examples: Custom, HTTP, Joiner, Router, Union, Unstructured Data, XML source qualifier, XML Target definition, XML parser, XML generator

7. List out all the transformations which use cache?

Aggregator, Joiner, Lookup, Rank, Sorter

8. What is blocking transformation?

Transformation which blocks the input rows are called blocking transformation.
Example: Custom transformation, unsorted joiner

9. What is a reusable transformation?

A reusable transformation is the one which can be used in multiple mappings. Reusable transformation is created in transformation developer.

10. How do you promote a non-reusable transformation to reusable transformation?

Edit the transformation and check the Make Reusable option

11. How to create a non-reusable instance of reusable transformations?

In the navigator, select an existing transformation and drag the transformation into the mapping workspace. Hold down the Ctrl key before you release the transformation.

12. Which transformation can be created only as reusable transformation but not as non-reusable transformation?

External procedure transformation.

13. What is the difference between Reusable transformation and Mapplet?

Ans. Any Informatica Transformation created in the in the Transformation Developer or a non-reusable promoted to reusable transformation from the mapping designer which can be used in multiple mappings is known as Reusable Transformation. When we add a reusable transformation to a mapping, we actually add an instance of the transformation. Since the instance of a reusable transformation is a pointer to that transformation, when we change the transformation in the Transformation Developer, its instances reflect these changes.

A Mapplet is a reusable object created in the Mapplet Designer which contains a set of transformations and lets us reuse the transformation logic in multiple mappings. A Mapplet can contain as many transformations as we need. Like a reusable transformation when we use a mapplet in a mapping, we use an instance of the mapplet and any change made to the mapplet is inherited by all instances of the mapplet.

14. What are the transformations that are not supported in Mapplet?

Ans. Normalizer, Cobol sources, XML sources, XML Source Qualifier transformations, Target definitions, Pre- and post- session Stored Procedures, Other Mapplets.

16. What is the difference between STOP and ABORT?

Ans. When we issue the STOP command on the executing session task, the Integration Service stops reading data from source. It continues processing, writing and committing the data to targets. If the Integration Service cannot finish processing and committing data, we can issue the abort command.

In contrast ABORT command has a timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session.

17. Can we copy a session to new folder or new repository?

Ans. Yes we can copy session to new folder or repository provided the corresponding Mapping is already in there.

18. What type of join does Lookup support?

Ans. Lookup is just similar like SQL LEFT OUTER JOIN.

19.How can we update a record in target table without using Update strategy?

A target table can be updated without using 'Update Strategy'. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as "Update as Update" and check the "Update" check-box.

Let's assume we have a target table "Customer" with fields as "Customer ID", "Customer Name" and "Customer Address".Suppose we want to update "Customer Address" without an Update Strategy. Then we have to define "Customer ID" as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.

     

21 Which transformation uses cache?

1.   Lookup transformation

2.   Aggregator transformation

3.   Rank transformation

4.   Sorter transformation

5.   Joiner transformation

21.       Can every transformation reusable? How?

Except source qualifier transformation, all transformations support reusable property. Reusable transformation developed in two ways.

1. In mapping which transformation do you want to reuse, select the transformation and double click on it, there you got option like make it as reusable transformation option. There you need to check the option for converting non reusable to reusable transformation. but except for source qualifier trans.

2. By using transformation developer