Informatica Interview Questions

1. What is the use of tracing levels in transformation?
Tracing levels store information about mapping and transformations.

2. What are the various types of transformation?

Various types of transformation are: Aggregator Transformation, Expression Transformation, Filter Transformation, Joiner Transformation, Lookup Transformation, Normalizer Transformation, Rank Transformation, Router Transformation, Sequence Generator Transformation, Stored Procedure Transformation, Sorter Transformation, Update Strategy Transformation, XML Source Qualifier Transformation, Advanced External Procedure Transformation, External Transformation.

3. What is the difference between active transformation and passive transformation?

An active transformation can change the number of rows that pass through it, but a passive transformation can not change the number of rows that pass through it.

4. What is the use of control break statements?

They execute a set of codes within the loop and endloop.

5. What are the types of loading in Informatica?

There are two types of loading, normal loading and bulk loading. In normal loading, it loads record by record and writes log for that. It takes comparatively a longer time to load data to the target in normal loading. But in bulk loading, it loads number of records at a time to target database. It takes less time to load data to target.

6. What is the difference between source qualifier transformation and application source qualifier transformation?

Source qualifier transformation extracts data from RDBMS or from a single flat file system. Application source qualifier transformation extracts data from application sources like ERP.

7. How do we create primary key only on odd numbers?

To create primary key, we use sequence generator and set the 'Increment by' property of sequence generator to 2.

8. What is authenticator?

It validates user name and password to access the PowerCenter repository.

9. What is the use of auxiliary mapping?

Auxiliary mapping reflects change in one table whenever there is a change in the other table.

10. What is a mapplet?

Mapplet is the set of reusable transformation.

11. Which ETL tool is more preferable Informatica or Data Stage and why?

Preference of an ETL tool depends on affordability and functionality. It is mostly a tradeoff between the price and feature. While Informatica has been a market leader since the past many years, DataStage is beginning to pick up momentum.

12. What is worklet?

Worklet is an object that represents a set of tasks.

13. What is workflow?

A workflow is a set of instructions that tells the Informatica server how to execute the tasks.

14. What is session?

A session is a set of instructions to move data from sources to targets.

15. Why do we need SQL overrides in Lookup transformations?

In order to lookup more than one value from one table, we go for SQL overrides in Lookups.

16. What is Target Update override?

It overrides the default update statement in the target properties.

17. How are Informatica object files stored?

They are stored as XML in the Informatica Repository.

18. How do we tune the performance of Informatica transformations?

• Use Filter transformation before transforming the unwanted data. • Go for dynamic lookups as far as possible. • Use Union transformations in place of using multiple transformations with binded SQLs.

19. In what conditions we cannot use Joiner transformation?

• Both pipelines begin with the same original data source. • Both input pipelines originate from the same Source Qualifier transformation. • Both input pipelines originate from the same Normalizer transformation. • Both input pipelines originate from the same Joiner transformation. • Any of the input pipelines contains an Update Strategy transformation. • Any of the input pipelines contains a connected or unconnected Sequence Generator transformation.

20. If there is no PK or FK in the target table, how do we update or insert value into the table?

We take a dynamic lookup on the target and do a comparison with source in an expression and flag it.

21. What is the difference between reusable transformation and mapplet?

Reusable transformation is a single transformation and mapplet is a set of transformations.

22. What is the incapability of Lookup transformation?

We can not use Lookup transformation for non-RDBMS sources.

23. Where do we override native SQL in transformations?

We can override it in source qualifier and lookup transformation.

24. When we read from source and write into flat file, how to specify the column names in the flat file?

Using pre-SQL, we can specify the column names.

25. How do we track rejected or bad data?

We can design a trap mechanism using Router or filter transformation and track the rejected data.

26. How do we join two tables without joiner or SQL override?

We can join the tables using lookup tansformation and making a cartesian product.

27. What are pre-session and post-session options?

These are shell commands that informatica server performs before running the session. We should have permission and privileges to run the options in Informatica.

28. How do we copy mapping?

The steps to copy mapping are mentioned below: 1.) First, open navigator window 2.) Open the mapping in repository 3.) Click copy

29. What are the tasks done by informatica server?

Tasks done by informatica server are: a.) Managing the sessions b.) Running the workflows c.) Tuning performance d.) Scheduling of sessions and workflows

30. What is the difference between source qualifier and joiner transformation?

We need matching keys in source qualifier which we do not need in joiner transformation.

31. What are mapping parameters and mapping variables?

Mapping parameters are parameters which are constant through out the session. They are specified before running the session. Unlike parameters, variables change through out the session.

32. What is the default join that source qualifier provides?

The default join that source qualifier uses is inner equi join.

33. What is Normalizer transformation?

This is one of the transformations in Informatica. This is applicable only for cobol sources. If we want to change rows into column or column into rows, we have to go for normalizer.

34. What are the default sources supported by Informatica Powermart?

Informatica Powermart supports Relational tables, fixed width and delimited files and Cobol sources.

35. What is sql override in Informatica?

We use sql override in Informatica to improve performance of lookup transformation. We write the query which is already executed by Informatica. Here we use Join queries.

36. What is Power Analyzer in Informatica?

Informatica Power Analyzer is the only BI tool in Informatica. This is the tool in Informatica which gives advantages for data delivering, adhoc reporting and dashboard.