Informatica interview questions and answers ๐Ÿ‘‡

  1. Informatica Interview Questions
  2. Advanced Informatica Interview Questions


Informatica Interview Questions

1.

What is ETL (Extract, transform, Load)?

โ†‘

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.

2.

What is Lookup transformation?

โ†‘

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.

3.

What is Informatica?

โ†‘

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.

4.

What is the difference between active and passive transformation?

โ†‘

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.

5.

Why is sorter an active transformation?

โ†‘

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

6.

What is the use of source qualifier?

โ†‘

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.

7.

What is shared cache?

โ†‘

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.

8.

What is re 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.

9.

How can we filter rows in Informatica?

โ†‘

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.
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.

11.

Define the surrogate key.

โ†‘

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.

12.

Define OLAP.

โ†‘

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.

13.

What are the different types of OLAP?

โ†‘

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)
14.

What is target load order?

โ†‘

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.

15.

Define Target Designer.

โ†‘

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.

16.

Define junk dimensions.

โ†‘

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.

17.

What is Rank Transformation?

โ†‘

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.

18.

What is Sequence Generator transformation?

โ†‘

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.

19.

What is the use of aggregator cache file?

โ†‘

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.

20.

How are indexes created after completing the load process?

โ†‘

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

21.

What is the difference between mapping parameter and mapping variable?

โ†‘

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.

22.

What is a Repository Manager?

โ†‘

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.

23.

What is Workflow Manager?

โ†‘

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.

24.

What is Load Order?

โ†‘

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.

25.

What is a Dimensional Model?

โ†‘

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.

26.

What is Fact Table? What are the different kinds of Fact 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
27.

What is Decode in Informatica?

โ†‘

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.

28.

What is SUBSTR?

โ†‘

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

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

Advanced Informatica Interview Questions

1.

What is code page compatibility?

โ†‘

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.

2.

What is Aggregator transformation?

โ†‘

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.

3.

What is Expression transformation?

โ†‘

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.

4.

What is Joiner transformation?

โ†‘

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.

5.

What is incremental aggregation?

โ†‘

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.

6.

What is a mapplet?

โ†‘

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.

7.

What is reusable transformation?

โ†‘

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.

8.

What is surrogate key?

โ†‘

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.

9.

What is a session task?

โ†‘

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.

10.

What is a command task?

โ†‘

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.

11.

What is a predefined event?

โ†‘

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.

12.

What is a user-defined event?

โ†‘

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.

13.

What is Sequence Generator transformation?

โ†‘

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.

14.

What is Connected LookUp & Unconnected LookUp?

โ†‘

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.