Top Informatica Interview Questions and Answers

Informatica PowerCenter, called Informatica for short, is an ETL (extraction, transformation, and loading) tool that’s widely used in building enterprise data warehouses. According to iDataLabs, more than 21,000 companies in the U.S. and elsewhere use Informatica, such as the U.S. Air Force, the Chicago Cubs, and eBay—in fact, many companies on the Fortune 100 list use Informatica.

According to Glassdoor, the average salary for an Informatica professional services senior consultant is $111,375, plus more than $16,000 in additional pay. And with businesses around the world in such a wide range of fields using Informatica—including financial services, healthcare, the nonprofit sector, manufacturing, insurance, and publishing—the number of needed Informatica professionals can only be expected to grow in the future.

Wondering what Informatica questions you might be asked in an interview? Here we’ll tell you the top Informatica interview questions and answers, at both the basic and expert level, to help you prepare for that higher-paying job.

1. What are the advantages of Informatica over other ETL tools?

Informatica is the world’s most popular data integration tool. It interoperates with the widest range of different standards, systems, and applications; it’s fast; and it is designed to adapt to constant change in the field/market, the organization, and the system. Also, you can easily monitor jobs, and it’s easy to identify the causes of failed jobs.

2. What are the main components of Informatica?

The main features of Informatica are the client tools/applications, server, repository server, and repository.

3. What can Informatica be used for in an organization?

Informatica can be used for data migration—for example, a company is transitioning from an older mainframe system to a new database system; data warehousing—an ETL tool would be needed for moving data from the production system to the warehouse; data integration—incorporating data from multiple databases or file-based systems, for example; and cleaning up data.

4. What is an enterprise data warehouse?

An enterprise data warehouse is a single unified database that holds an organization’s business information and distributes it throughout the company. There are variations, but it likely includes a unified approach to sorting and presenting data, and data can be classified and accessed according to the subject.

5. Describe an Informatica workflow.

In the workflow manager, you build a workflow by logically connecting tasks to execute code (for example, scripts). The final workflow will automatically run all the tasks within it in the specified order.

6. What is a domain?

A domain is composed of relationships and nodes covered by one organizational point.

7. What are some of the types of transformation?

Some transformation types are the aggregator, expression, filter, joiner, lookup, rank, router, and normalizer.

8. What’s the difference between active and passive transformation?

An active transformation can change the number of rows that pass through it, can change the transaction boundary and can change the actual row type. A passive transformation doesn’t change either the number of rows that pass through it or the row type and doesn’t change the transaction boundary.

9. Why might router transformation be better than filter transformation?

With router transformation, you can have better performance, and it’s less complex and more efficient than filter transformation.

10. Why would you want to partition a section?

It improves the server’s efficiency; other transformations are carried out in parallel.

11. What’s the difference between a mapping parameter and a mapping variable? 

Mapping variables, as the name implies, are values that change during a session’s execution. Values that don’t change are called parameters. 

12. How would you self-join in an Informatica mapping?

To self-join, place one transformation minimum between the source qualifier and the joiner in one branch minimum. You must pre-sort the data and then configure the joiner to accept sorted input.

13. What are the different join types within a joiner transformation?

There are four join types: normal join, master outer join, detail outer join and full outer join.

14. What are the different dimensions in Informatica?

Three dimensions are available in Informatica: junk, degenerative and conformed. 

15. What is the difference between a session and a batch?

A session is a set of commands by which the server moves data to the target. A batch is a set of individual tasks.

16. How many sessions can be grouped in a batch?

There is no limit to the number of sessions that can comprise a batch. But the fewer the sessions, the easier the migration.

17. Describe the modes of data movement.

In the normal mode of data movement, a separate DML stmt is prepared and executed. In the bulk mode, a DML stmt is prepared and executed for multiple records at a time, improving efficiency.

18. What is the aggregator cache used for?

It stores transitional files found in the local buffer memory, and stores transformation values if extra memory is required.

19. What is the persistent lookup cache?

This data is stored on the server, saving time because a database query doesn’t need to happen when a lookup occurs.

20. What are mapplets?

In the Mapplet Designer, you create mapplets, which are reusable objects that contain a set of transformations.

21. Describe the differences between a mapplet and a mapping.

Mapplets contain only transformations, can be reused and are developed for complex calculations. Mappings include source, target and transformations; are not reusable; and are used for less complex calculations than mapplets, such as for what data to move to a target.

22. How does a pipeline partition improve performance?

A pipeline partition lets you divide a pipeline into different reader/transformation/writer threads. The integration service can run the different partitions within the mapping at the same time, increasing efficiency.

23. What are some other types of partitioning aside from pipeline partitioning?

Other types of partitioning include database partitioning, round-robin partitioning, key-range partitioning, pass-through partitioning, hash user-keys partitioning and hash auto-keys partitioning 

24. Describe the differences between an SQL override and a lookup override

When you want to limit the number of rows entering a mapping pipeline, you’d use an SQL override. When you want to limit the number of lookup rows to avoid scanning an entire table, you’d use the lookup override. Lookup override provides only one record even if multiple records for a condition exist. Also, SQL override doesn’t use the “order by” clause—you have to manually enter it in the query.

25. What are the configurable commit types?

There are three configurable commit types: target-based, source-based and user-defined.

Hopefully, these Informatica interview questions and answers have given you a better sense about what to expect in your job search. Interested in learning more about Informatica, to potentially earn a higher salary or just be more valuable to your company or organization? Sign up for Simplilearn’s Informatica PowerCenter training and get started today!