Informatica Interview Questions and Answers (2023)

In the world of data warehousing, if you need to bring data from multiple different data sources into one, centralized database, you must first:

• EXTRACT data from its original source
• TRANSFORM data by deduplicating it, combining it, and ensuring quality, to then
• LOAD data into the target database

ETL tools enable data integration strategies by allowing companies to gather data from multiple data sources and consolidate it into a single, centralized location. ETL tools also make it possible for different types of data to work together.

Lookup transformation is used to look up a source, source qualifier, or target to get the relevant data. It is a kind of join operation in which one of the joining tables is the source data, and the other joining table is the lookup table.

Informatica is a data integration tool based on ETL architecture. It provides data integration software and services for various businesses, industries and government organizations including telecommunication, health care, financial and insurance services.

An active transformation can change the number of rows that pass through the transformation. For example, the Filter transformation is active because it removes rows that do not meet the filter condition. A passive transformation does not change the number of rows that pass through the transformation.

The Sorter transformation is an Active transformation because its attribute, Distinct provides a facility to remove duplicates from the input rows.

The source qualifier transformation converts the source data types in the Informatica native data types. That's why there is no need to alter the data types of the ports.

A shared lookup cache is a static lookup cache that is shared by multiple Lookup transformations in a mapping. Use a shared lookup cache to decrease the amount of time required to build the cache.

If your lookup table changes less frequently, you can use persistent cache. Re-cache is done when persistent cache is not in sync with the lookup table.

There are two ways to filter rows in Informatica:

• Source Qualifier Transformation: It filters rows while reading data from a relational data source. It minimizes the number of rows while mapping to enhance performance. Also, Standard SQL is used by the filter condition for executing in the database.
• Filter Transformation: It filters rows within mapped data from any source. It is added close to the source to filter out the unwanted data and maximize performance. It generates true or false values based on conditions.

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.

A surrogate key is basically an identifier that uniquely identifies modeled entities or objects in a database. Not being derived from any other data in the database, surrogate keys may or may not be used as primary keys.

OLAP applies complex queries to large amounts of historical data, aggregated from OLTP databases and other sources, for data mining, analytics, and business intelligence projects. In OLAP, the emphasis is on response time to these complex queries. Each query involves one or more columns of data aggregated from many rows. Examples include year-over-year financial performance or marketing lead generation trends.

There are 3 main types of OLAP servers are as following:

• Relational OLAP (ROLAP): Star Schema based
• Multidimensional OLAP (MOLAP): Cube based
• Hybrid OLAP (HOLAP)

A target load order group is the collection of source qualifiers, transformations, and targets linked together in a mapping. You can set the target load order if you want to maintain referential integrity when inserting, deleting, or updating tables that have the primary key and foreign key constraints.

Target Designer in Informatica allows us to modify the existing target table, create new target table and can import different target definitions like flat files, XML, .xls etc.

A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags, e.g. non-generic comments or just simple yes/no or true/false indicators.

The Rank Transformation in Informatica is an active, connected transformation used to select a bottom or top range of data. While string value ports can be ranked, the Informatica Rank Transformation is most commonly used to rank numeric port values.

The Sequence Generator transformation is a passive and connected transformation that generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.

The Integration Service uses cache memory to process Aggregator transformations with unsorted input. When you run the session, the Integration Service stores data in memory until it completes the aggregate calculations.

Stored procedure transformation can be used to create and drop indexes before and after loading into the target.

A mapping parameter represents a constant value that you can define before running a session. A mapping parameter retains the same value throughout the entire session.

Unlike a mapping parameter, a mapping variable represents a value that can change through the session. The Informatica Server saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

Informatica PowerCenter Repository Manager is a GUI based Administrative client component, which allows user to create new domains and used to organize the meta data stored in the Repository. Folders in Repository are used to organize the meta data.

In the Workflow Manager, you define a set of instructions to execute tasks such as sessions, emails, and shell commands. This set of instructions is called a workflow.

Target load order (or) Target load plan is used to specify the order in which the integration service loads the targets. You can specify a target load order based on the source qualifier transformations in a mapping. If you have multiple source qualifier transformations connected to multiple targets, you can specify the order in which the integration service loads the data into the targets.

Dimensional Modeling (DM) is a data structure technique optimized for data storage in a Data warehouse. The purpose of dimensional modeling is to optimize the database for faster retrieval of data. The concept of Dimensional Modelling was developed by Ralph Kimball and consists of “fact” and “dimension” tables.

A Fact Table is a central table in a star schema of a data warehouse. A fact table stores quantitative information for analysis and is often denormalized. A fact table works with dimension tables and it holds the data to be analyzed and a dimension table stores data about the ways in which the data can be analyzed.

There are 3 types of fact tables:

• Transaction fact tables
• Periodic snapshot tables
• Accumulating snapshot tables

DECODE searches a port for a value you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a DECODE function.

SUBSTR Function is used to extract a portion of the string.

SUBSTR(string Input,int Start,[int Length])

Compatibility between code pages is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. If the code pages are identical, then there will not be any data loss. One code page can be a subset or superset of another.

Aggregator transformation is an active transformation. It is used to perform calculations on the data such as sums, averages, counts, etc. The integration service stores the group of data and row data in the aggregate cache.

The Expression Transformation in Informatica is a passive transformation that is used to perform non-aggregate calculations on the source data. It means you can use this Informatica Expression transformation to perform calculations on a single row.

Joiner transformation is an active and connected transformation. It provides the option of creating joins in Informatica. By using the joiner transformation, the created joins are similar to the joins in databases. The joiner transformation is used to join two heterogeneous sources.

Using incremental aggregation, we apply captured changes in the source data (CDC part) to aggregate calculations in a session. If the source changes incrementally and we can capture the changes, then we can configure the session to process those changes. This allows the Integration Service to update the target incrementally, rather than forcing it to delete previous loads data, process the entire source data and recalculate the same data each time you run the session.

A mapplet is a reusable object that you create in the Mapplet Designer. It contains a set of transformations and lets you reuse the transformation logic in multiple mappings.

A reusable transformation is a single transformation that can be reusable. Mapplets are re-usable components and is a set of transformation / Business logic which can be reused across various mappings.

A surrogate key is any column or set of columns that can be declared as the primary key instead of a "real" or natural key. Sometimes there can be several naturalkeys that could be declared as the primary key, and these are all called candidate keys. So a surrogate is a candidate key.

A session is a set of instructions that tells the Integration Service how and when to move data from sources to targets. A session is a type of task, similar to other tasks available in the Workflow Manager.

The Command Task in Informatica is used to run Unix or Shell commands during the Workflow. You can use this task to move, copy, or remove files after the data is loaded.

A predefined event is a file-watch event. When you use the Event-Wait task to wait for a predefined event, you specify an indicator file for the Integration Service to watch. The Integration Service waits for the indicator file to appear.

A user-defined event is the sequence of tasks in the branch from the Start task to the Event-Raise task. When the Integration Service runs the Event-Raise task, the Event-Raise task triggers the user-defined event. To use an Event-Raise task, you must first declare the user-defined event.

The Sequence Generator transformation is a passive and connected transformation that generates numeric values. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.

Connected lookup receives input values directly from mapping pipeline, whereas UnConnected lookup receives values from the lookup expression from another transformation. A mapping in Informatica may contain Source, Transformations and Targets connected together are considered as a pipeline.