Top 10 Informatica Interview Questions and Answers

Informatica is a data integration tool used for extracting, transforming, and loading data from various sources.

• Used for data integration and ETL processes

• Supports various data sources such as databases, flat files, and cloud applications

• Provides a graphical user interface for designing and managing data integration workflows

• Offers advanced features such as data profiling, data quality, and metadata management

Router and filter transformations are used in Informatica for data filtering, but they have different functionalities.

• Router transformation is used to route data based on specified conditions to multiple output groups.

• Filter transformation is used to filter rows based on specified conditions and pass only the matching rows to the output.

• Router transformation can have multiple output groups, each with its own condition.

• Filter transformation has a single output group.

• Router tra...read more

I rate my SQL skills as advanced and my Informatica skills as intermediate.

• Advanced SQL skills include complex queries, stored procedures, and performance tuning.

• Intermediate Informatica skills include ETL development, mapping design, and workflow scheduling.

• I have experience with both SQL Server and Oracle databases.

• I am constantly learning and improving my skills in both areas.

I have used various transformations in Informatica such as Filter, Router, Expression, Aggregator, Joiner, Lookup, and Sorter.

• Filter transformation is used to filter rows based on a condition.

• Router transformation is used to route data to different targets based on conditions.

• Expression transformation is used to perform calculations or manipulate data.

• Aggregator transformation is used to perform aggregate calculations like sum, average, etc.

• Joiner transformation is used to jo...read more

Datastage and Informatica are both ETL tools used for data integration, but they have differences in terms of features and capabilities.

• Datastage is developed by IBM and is known for its parallel processing capabilities, while Informatica is developed by Informatica Corporation and is known for its strong data quality features.

• Datastage has a more user-friendly interface compared to Informatica, making it easier for beginners to use.

• Informatica offers more advanced features f...read more

Informatica is a software development company that provides data integration and management solutions.

• Informatica offers a wide range of products for data integration, data quality, data governance, and more.

• It is known for its PowerCenter tool, which is used for ETL (Extract, Transform, Load) processes.

• Informatica helps organizations to efficiently manage and analyze their data to make informed business decisions.

SCD Type 1 in IICS involves overwriting existing data with new data without maintaining historical changes.

• In IICS, use the Mapping Designer to create a mapping that loads data from source to target.

• Use a Lookup transformation to check if the record already exists in the target table.

• If the record exists, update the existing record with new data using an Update Strategy transformation.

• If the record does not exist, insert the new record into the target table.

• Ensure that the ma...read more

To select the last 10 lines from a file in Informatica mapping, use the Source Qualifier transformation with a SQL override.

• Add a Source Qualifier transformation to the mapping.

• In the Source Qualifier transformation, specify the file as the source and set the appropriate file properties.

• Write a SQL override in the Source Qualifier transformation to select the last 10 lines from the file.

• Use the ROWNUM pseudocolumn or the ROW_NUMBER() function to order the rows in descending o...read more

Loads in Informatica refer to the process of moving data from source to target in a data warehouse.

• Loads involve extracting data from source systems

• Transforming the data as needed

• Loading the data into the target data warehouse or database

• Loads can be full, incremental, or delta depending on the requirements

• Example: Loading customer data from a CRM system into a data warehouse for analysis

Indirect file loading is a process of loading data from a file that is not directly connected to the target system.

• Indirect file loading involves using an intermediate file to load data into the target system.

• This method is useful when the source file is not compatible with the target system.

• Indirect file loading can be achieved using Informatica's indirect file method or by using a custom script.

• Examples of indirect file loading include loading data from a mainframe system o...read more

Active and Passive transformation in Informatica and SQL query in a project

• Active transformation changes the number of rows that pass through it, while passive transformation does not

• Active transformation can change the data values, while passive transformation does not

• Examples of active transformation include Filter, Router, and Update Strategy, while passive transformation includes Expression and Sequence Generator

• SQL query in a project can be used to extract, manipulate, a...read more

SCD stands for Slowly Changing Dimensions and lookups in Informatica are used to perform data transformations by looking up data from a reference table.

• SCD is used to track changes to dimension data over time.

• There are three types of SCD - Type 1, Type 2, and Type 3.

• Lookups in Informatica are used to perform data transformations by looking up data from a reference table.

• Lookups can be connected to different types of sources like flat files, databases, etc.

• Example: In a Type 2...read more

Command task is used to execute shell commands or scripts in Informatica.

• Command task can be used to execute Unix or Windows commands.

• It can be used to run scripts or batch files.

• The output of the command can be captured and used in subsequent tasks.

• Command task can be scheduled to run at a specific time or interval.

• It can be used to perform tasks like file system operations, database operations, etc.

Different types of transformations in Informatica include active and passive transformations, as well as union and joiner transformations.

• Active transformations are those which change the number of rows that pass through them, such as Filter or Router transformations.

• Passive transformations do not change the number of rows that pass through them, such as Expression or Lookup transformations.

• Union transformation combines data from multiple pipelines into a single pipeline.

• Join...read more