Informatica Interview Questions

Informatica is a software development firm that offers data integration solutions for various purposes, such as ETL, data quality, data governance, data catalog, master data management, cloud data integration, and more. We need Informatica to work with data systems that contain data from different sources and formats, and to perform certain operations and transformations on that data according to a set of rules and requirements. Informatica helps us to clean, modify, integrate, and utilize data from structured and unstructured data systems.

Informatica PowerCenter is an ETL tool that is used to connect and retrieve data from different sources and process it for various data integration projects. The main components of Informatica PowerCenter are

PowerCenter Repository: It is a centralized database that stores all the metadata, such as mappings, sessions, workflows, users, connections, etc.

PowerCenter Client: It is a graphical user interface that allows the developers and administrators to create, manage, and monitor the data integration projects.

PowerCenter Server: It is a server that executes the data integration tasks, such as extracting, transforming, and loading the data, based on the instructions from the repository.

PowerCenter Administration Console: It is a web-based interface that allows the administrators to configure and administer the PowerCenter environment, such as domains, nodes, services, security, etc.

Transformations are the objects that define the logic and rules for data manipulation and transformation in Informatica. There are two types of transformations in Informatica: active and passive.

Active transformations are the ones that can change the number of rows, the row type, or the transaction boundary of the data. For example, filter, sorter, aggregator, joiner, router, etc.

Passive transformations are the ones that do not change the number of rows, the row type, or the transaction boundary of the data. For example, expression, lookup, sequence generator, normalizer, etc.

Mapping and mapplet are both objects that contain a set of transformations to perform data integration tasks in Informatica. The difference between them is that:

Mapping is a standalone object that has one or more sources and targets, and can be executed as a session in a workflow.

Mapplet is a reusable object that has one or more transformations, but no sources and targets, and can be used as a transformation in a mapping.

Lookup is a passive transformation that is used to look up data from a flat file, a relational table, a view, or a synonym. The difference between a connected and unconnected lookup is that:

Connected lookup is connected to other transformations in the mapping through ports, and can return multiple values from the same row or multiple rows.

Unconnected lookup is not connected to other transformations in the mapping, but is invoked by an expression in another transformation, and can return only one value from the first matching row.

Joiner and lookup are both the transformations that are used to join data from different sources in Informatica. The difference between them is that:

Joiner is an active transformation that can join two heterogeneous sources, such as flat files and relational tables, based on a condition, and can return all the columns from both the sources.

Lookup is a passive transformation that can look up data from a single source, such as a flat file, a relational table, a view, or a synonym, based on a key, and can return only the columns that are specified in the lookup ports.

Aggregator and expression are both the transformations that are used to perform calculations on data in Informatica. The difference between them is that:

Aggregator is an active transformation that can perform calculations on groups of data, such as sum, count, average, etc., and can return one row per group.

Expression is a passive transformation that can perform calculations on individual rows, such as arithmetic, string, date, etc., and can return the same number of rows as the input.

Sorter and rank are both the transformations that are used to sort data in Informatica. The difference between them is that:

Sorter is an active transformation that can sort all the input rows in ascending or descending order based on one or more ports, and can return all the sorted rows.

Rank is an active transformation that can rank the input rows based on a measure column and a group by port, and can return only the top or bottom ranked rows.

Normalizer and update strategy are both the transformations that are used to modify data in Informatica. The difference between them is that:

Normalizer is a passive transformation that can modify the structure of data, such as converting multiple rows into a single row, or a single row into multiple rows, based on the metadata imported from a COBOL source.

Update strategy is an active transformation that can modify the behavior of data, such as inserting, updating, deleting, or rejecting the rows, based on a flag value or a condition.

Router and filter are both the transformations that are used to filter data in Informatica. The difference between them is that:

Router is an active transformation that can filter data based on multiple conditions, and can route the rows to different output groups.

Filter is an active transformation that can filter data based on a single condition, and can pass the rows that meet the condition and drop the rows that do not. 

Worklet is similar to Mapplet with regards to its feature of reusability and the only difference is that worklet can be applied to any number of workflows in INFORMATICA, unlike Mapplet. Worklet saves the logic and tasks at a single place to reuse.

Worklet is much similar to the Mapplet and is defined as the group of tasks that can be either reusable or non-reusable at the workflow level. It can be added to as many numbers of workflows as required. Its reusability feature saves a lot of time as reusable logic can be developed once and can be placed from where it can be reused.

 In the INFORMATICA Power center environment, Mapplets are considered as the most advantageous feature. 

Mapplets are created in Mapplet designer and are a part of the Designer tool.

It basically contains a set of transformations that are designed to be reused in multiple mapping.

Command tasks at session level can be used for the purpose of creating indexes after the load process. Index creating scripts can be brought in line with the session's workflow or the post-session implementation sequence. It is difficult to control this type of index creation after the load process at the transformation level.

A session is a teaching set that needs to be implemented to convert data from a source to a target. Session can be carried out using the session's manager or pmcmd command. Batch execution can be used to combine sessions executions either in a serial manner or in a parallel. Batches can have different sessions carrying forward in a parallel or serial manner.

Mapping variable happens when values change during the session's execution. Upon completion, the Informatica server stores the end value of a variable and is reused when session restarts. Moreover, those values that do not change during the sessions execution are called mapping parameters. Mapping procedure explains mapping parameters and their usage. Values are allocated to these parameters before starting the session.

The main features of complex mapping are as follows

1. It helps to deal with difficult requirements
2. It can result in many numbers of transformations.
3. It simplifies the complex business logic.

Basically, it makes the complex things simple so that it becomes easier to carry them out.

Aggregator transformations are handled in chunks of instructions during each run. It stores transitional values which are found in the local buffer memory. Aggregators provide extra cache files for storing the transformation values if extra memory is required. They act as a single platform for many caches.

Lookup transformations are those transformations which have admission right to RDBMS based data set. The server makes the access faster by using the lookup tables to look at explicit table data or the database. Concluding data is achieved by matching the lookup condition for all lookup ports delivered during transformations. It is a technical concept which is part of Informatica.

There is a lot of data loss that occurs when data moves from one code page to another and this can be prevented when both the pages have the same character sets. The source page must contain all the characteristics of the target page. Importantly if all the characters of source page are not present in the target page then it would be a subset and data loss will definitely occur during transformation due to the fact the two code pages are not compatible.

Expression transformation is when the values can be calculated in a single row before writing on the target in this form of transformation. It can be used to perform non-aggregate calculations. Conditional statements can also be tested before output results go to target tables. It is mainly used for testing the calculations.

Connected lookup is when the inputs are taken directly from other transformations in the pipeline. While unconnected lookup doesn't take inputs directly from other transformations, but it can be used in any transformations and can be raised as a function using LKP expression. So it can be said that an unconnected lookup can be called multiple times in mapping. Connected lookup has a singular role whereas unconnected lookup has multiple roles.