Interview questions for Informatica

Founded in 1993 and headquartered in Redwood City, California, Informatica is a software development company that provides its users with enterprise data integration and cloud data management solutions. The company provides products for ETL, data masking, data quality, data replication, data virtualization, master data management, etc. Its products were newly launched but quickly gained popularity.
Informatica, a powerful ETL tool developed by Informatica Corporation, is widely used by companies to build data warehouses. In the current era, Informatica is the most sought-after product worldwide, making it an excellent career prospect. Having a career with Informatica offers you plenty of opportunities and benefits that come with working for a leading organization.

1. What do you mean by Enterprise data warehouse?

Data warehouses (DW) or Enterprise Data Warehousing (EDW), a form of the corporate repository, generally store and manage enterprise data and information collected from multiple sources. Enterprise data is collected and made available for analysis, business intelligence, to derive valuable business insights, and to improve data-driven decision-making. Data contained here can be accessed and utilized by users (with privileges) across the organization. With EDW, data is accessed through a single point and delivered to the server via a single source.

2. What is ETL (Extract, transform, Load) and write some ETL tools?

Essentially, ETL means to extract, transform, and load. The ETL process involves extracting, transforming, and loading data from different databases into the target database or file. It forms the basis of a data warehouse. Here are a few ETL tools:
• IBM Datastage
• Informatica PowerCenter
• Abinitio
• Talend Studio, etc.

It performs the following functions:
• Obtains data from sources
• Analyze, transform, and cleans up data
• Indexes and summarizes data
• Obtains and loads data into the warehouse
• Monitors changes to source data needed for the warehouse
• Restructures keys

3. In Informatica Workflow Manager, how many repositories can be created?

Depending upon the number of ports that are required, repositories can be created. In general, however, there can be any number of repositories.

4. What are the types of lookup transformation?

There are four different types of lookup transformation:
• Relational or flat-file lookup: It performs a lookup on relational tables.
• Pipeline lookup: It performs a lookup on application sources.
• Connected or unconnected lookup: While the connected lookup transformation receives data from the source, performs a lookup, and returns the result to the pipeline, the unconnected lookup happens when the source is not connected. It returns one column to the calling transformation.
• Cached or uncached lookup: Lookup transformation can be configured to cache lookup data, or we can directly query the lookup source every time a lookup is invoked.

5. How do pre- and post-session shell commands function?

A command task can be called a pre-session or post-session shell command for a session task. Users can run it as a pre-session command, a post-session success command, or a post-session failure command. Based on use cases, the application of shell commands can be changed or altered.

9. What can we do to improve the performance of Informatica Aggregator Transformation?

Aggregator performance improves dramatically if records are sorted before passing to the aggregator and if the ‘sorted input’ option under Aggregator Properties is checked. The record set should be sorted on those columns that are used in the Group By operation. It is often a good idea to sort the record set in the database level, e.g., inside a source qualifier transformation, unless there is a chance that the already sorted records from the source qualifier can again become unsorted before reaching the aggregator.

10. How can we update a record in the 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 at the Informatica level, and then we need to connect the key and the field we want to update in the mapping target. At the session level, we should set the target property as ‘Update as Update’ and check the ‘Update’ check box.
Let us assume, we have a target table ‘Customer’ with fields as ‘Customer ID,’ ‘Customer Name,’ and ‘Customer Address.’ Suppose if we want to update ‘Customer Address’ without an Update Strategy, then we have to define ‘Customer ID’ as the primary key at the 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.

11. How do pre- and post-session shell commands function?

A command task can be called a pre-session or post-session shell command for a session task. Users can run it as a pre-session command, a post-session success command, or a post-session failure command. Based on use cases, the application of shell commands can be changed or altered.

12. What can we do to improve the performance of Informatica Aggregator Transformation?

Aggregator performance improves dramatically if records are sorted before passing to the aggregator and if the ‘sorted input’ option under Aggregator Properties is checked. The record set should be sorted on those columns that are used in the Group By operation. It is often a good idea to sort the record set in the database level, e.g., inside a source qualifier transformation, unless there is a chance that the already sorted records from the source qualifier can again become unsorted before reaching the aggregator.

13. How can we update a record in the 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 at the Informatica level, and then we need to connect the key and the field we want to update in the mapping target. At the session level, we should set the target property as ‘Update as Update’ and check the ‘Update’ check box.
Let us assume, we have a target table ‘Customer’ with fields as ‘Customer ID,’ ‘Customer Name,’ and ‘Customer Address.’ Suppose if we want to update ‘Customer Address’ without an Update Strategy, then we have to define ‘Customer ID’ as the primary key at the 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.