1. What is Informatica?
Informatica is a widely used data integration tool used for extracting, transforming, and loading (ETL) data from various sources to a centralized data warehouse or data mart.

2. What are the different types of transformations in Informatica?
The different types of transformations in Informatica are active and passive transformations. Active transformations change the number of rows that pass through them, while passive transformations do not affect the number of rows.

3. What are the core components of Informatica PowerCenter?
The core components of Informatica PowerCenter are PowerCenter Designer, Workflow Manager, Workflow Monitor, and PowerCenter Repository.

4. What is a session in Informatica?
A session in Informatica refers to the execution of a workflow that extracts, transforms, and loads data from source to target. It represents a set of instructions to execute a workflow.

5. What is a repository in Informatica?
A repository in Informatica is a database where metadata and other information related to the ETL process are stored. It contains details about source and target definitions, transformations, mappings, etc.

6. What is a mapping in Informatica?
A mapping in Informatica represents the flow of data from sources to targets. It defines the relationship between the source and target tables and the transformations applied to the data.

7. What is a transformation in Informatica?
A transformation in Informatica is a rule-based operation that is applied to the extracted data to meet the business requirements. It can be used to filter, aggregate, join, or modify the data.

8. What is a workflow in Informatica?
A workflow in Informatica is a series of connected tasks that define the order in which transformations and other operations are executed. It represents the control flow of data transformation and loading.

9. What is a source qualifier transformation?
A source qualifier transformation in Informatica is used to read data from a relational or flat file source. It extracts metadata from the source and allows you to control the data that is imported.

10. What is a target definition in Informatica?
A target definition in Informatica defines the structure of the target table where data will be loaded. It includes details like column names, data types, and constraints.

11. What is a lookup transformation?
A lookup transformation in Informatica is used to search and retrieve data from a table or view based on a matching condition. It performs a lookup on a source dataset to fetch related data.

12. What is the difference between the connected and unconnected lookup transformation?
In a connected lookup transformation, the input and output ports are connected directly to the transformation. In an unconnected lookup transformation, the input/output ports are not directly connected but called as a function from another transformation.

13. What is a joiner transformation?
A joiner transformation in Informatica is used to combine rows from two related sources based on a common key. It performs a join operation similar to a SQL join.

14. What is the difference between an active and a passive transformation?
An active transformation changes the number of rows that pass through it, while a passive transformation does not affect the number of rows. Active transformations may be used to filter, aggregate, or modify data.

15. What is a router transformation?
A router transformation in Informatica is used to route data rows to different outputs based on specified conditions. It is commonly used to implement conditional data transformations.

16. What is a target load order in Informatica?
The target load order in Informatica defines the order in which data is loaded into target tables in a session. It determines the dependencies between different target tables.

17. What is a mapplet in Informatica?
A mapplet in Informatica is a reusable object that allows you to encapsulate a set of transformations. It can be used across different mappings for consistent data transformation logic.

18. What is a dynamic lookup cache in Informatica?
A dynamic lookup cache in Informatica is a cache that is created and loaded during the session execution based on the lookup condition. It allows for dynamic update of cache records during the session.

19. What is a flat file source definition in Informatica?
A flat file source definition in Informatica is used to define the structure and properties of a flat file source. It includes details like file name, delimiter, and data type of each column.

20. What are reusable transformations in Informatica?
Reusable transformations in Informatica are transformations that can be shared and reused across multiple mappings. They help in maintaining consistency and reducing redundancy in mappings.

1. What is the difference between ETL and ELT?
ETL stands for Extract, Transform, and Load, where the extraction, transformation, and loading processes are performed sequentially. ELT, on the other hand, stands for Extract, Load, and Transform, where the extraction and loading are performed first and then the transformation is done within the target database.

2. What is a connected lookup and an unconnected lookup?
A connected lookup is connected directly to the main data pipeline and is used to retrieve values based on a specified condition. An unconnected lookup is not directly part of the data pipeline and is used within an expression transformation to perform a lookup function.

3. How can you improve the performance of a mapping?
Some ways to improve mapping performance include increasing session buffers, enabling partitioning and pushdown optimization, using sorted and indexed ports, and minimizing transformations by using target database-specific operations.

4. What is a surrogate key and how is it generated in Informatica?
A surrogate key is an artificial primary key used in data warehousing to uniquely identify each record. It can be generated in Informatica using different methods such as using the Sequence Generator transformation, a database sequence, or a combination of other transformations.

5. Explain the difference between a static cache and a dynamic cache.
A static cache is created once and remains constant throughout the session, allowing read-only access. A dynamic cache, on the other hand, can be updated during the session, allowing read and write access.

6. What is the difference between a router transformation and a filter transformation?
A router transformation routes input data based on multiple conditions, directing rows to different output groups. A filter transformation discards rows that do not meet a specified condition, only allowing qualifying rows to pass through.

7. What is Rank transformation used for?
The Rank transformation is used to select the top or bottom N number of records based on a specified column or expression. It can be used, for example, to identify the top-selling products or bottom-performing employees.

8. What is the difference between data-driven and non-data-driven sessions?
In a data-driven session, the session is executed once for each unique value in the source column or input file. In a non-data-driven session, the session is executed for the entire source data.

9. Explain the difference between a stored procedure transformation and a source qualifier transformation.
A stored procedure transformation is used to execute a stored procedure or function in a relational database, and it can return multiple result sets. A source qualifier transformation is used to specify the SQL query that defines the source data and can be used to filter, join, and aggregate data.

10. How can you handle slowly changing dimensions in Informatica?
Slowly changing dimensions (SCDs) can be handled using various techniques such as type 1, type 2, and type 3 SCD mappings. Type 1 retains only the latest version of the dimension record, type 2 maintains a historical version by inserting new rows with updated data, and type 3 creates additional columns to store historical data.

11. What is the difference between active and passive transformations?
Active transformations perform row-level changes and alter the number of rows passed through, while passive transformations do not change the number of rows and retain the same number of rows as the input.

12. How can you handle duplicate records in Informatica?
Duplicate records can be handled using a combination of transformations such as Sorter, Expression, and Aggregator. By sorting the data and using the appropriate logic, duplicates can be identified and processed accordingly.

13. What is a dynamic lookup?
A dynamic lookup is a type of lookup transformation where the source and target tables are determined dynamically at runtime based on parameter values or other conditions. It allows for flexibility in handling different tables within the lookup.

14. What is a reusable transformation?
A reusable transformation is a transformation that can be used in multiple mappings. It is created once and can be shared and reused across various mappings, improving efficiency and maintainability.

15. What is the difference between a mapping parameter and a mapping variable?
A mapping parameter is a constant value that can be set before the session starts and cannot be changed during the runtime. A mapping variable, on the other hand, can have different values at different points during the session and can be modified using a transformation or command task.

16. How can you debug a mapping in Informatica?
To debug a mapping in Informatica, you can enable debugging options in the session properties, use breakpoints to pause the execution at specific points, and use the debug viewer or log files to analyze the data flow and transformations.

17. What is a target load order and how is it defined in Informatica?
A target load order defines the order in which target tables or files are loaded during a session. It can be defined in Informatica by configuring the target load plan and specifying the load order for each target.

18. What is a partition point and how does it affect session performance?
A partition point is a specific transformation or source qualifier transformation in a mapping where the data can be divided into multiple partitions. Partitioning data can improve session performance by allowing parallel processing across multiple nodes or threads.

19. How can you handle data quality issues in Informatica?
Data quality issues can be handled in Informatica by using transformations such as the Expression transformation to validate and cleanse data, the Aggregator transformation to aggregate and summarize data, and the Lookup transformation to perform data lookups and matching.

20. What is the difference between a mapping parameter and a session parameter?
A mapping parameter is a parameter defined at the mapping level and is used throughout the mapping, while a session parameter is defined at the session level and can be overridden for different sessions. Mapping parameters are shared by all sessions using that mapping, while session parameters are specific to each session.