Best 65+ Informatica Interview Question & Answers 2021

Informatica development is a domain with countless opportunities. If you are good at Informatica, you can grab any lucrative job in the sector. 

With a 29.4% share in the market, Informatica is no doubt a realm with excellent opportunities even in the future. Learning Informatica development is a different opinion, but if you already know Informatica and now thinking of getting a job, we are here to help you get the same. 

We have compiled a long list of frequently asked Informatica interview questions along with their answers. These questions are enough to give you a clear understanding of Informatica technology and help you perform well at the interview. 

Top Informatica Interview Question & Answers in 2021

Here, is the list of mostly asked Informatica Interview Questions and this list includes two types of questions- scenario-based Informatica questions and advanced Informatica questions. Nevertheless, all these questions are suitable for beginners, intermediates, or advanced Informatica learners to crack the job interview. 

So without wasting any more time, let’s dive into the main content. 

1. Explain ETL?
ETL is the abbreviation for extract, transform, and load. As the name suggests, it is the process of extraction, transformation, and loading data in the proposed format into Datawarehousing. The aim of doing so is to facilitate decision making. 

2. Name some ETL tools?
Various tools are available in the market to carry out the ETL process. Some of the popular ETL tools are as follows:

• IBM Datastage
• Talend Studio 
• Informatica PowerCenter
• Abinitio

3. What do you know about Informatica?
It is a popular tool used for the ETL process. Thus this tool helps extract, transform, and loading any sort of database in proposed formats into Datawarehouse. The ownership of the tool belongs to Informatica Cooperation. 

4. List some advantages or uses of Informatica Powercenter. 
Informatica power center is a popular ETL tool capable of fetching data from distinct sources that may be heterogeneous and processing the fetched data. 

This tool is helpful when an organization needs to migrate from one database system to another. When organizations want to set up their data warehouse, it is also utilized for data filtration or purification. 

5. Explain enterprise data warehousing. 
When data of an enterprise is created at a single point of access, it is called enterprise data warehousing. This data is available to be accessed globally through a sole source connected to the server. 

6. What do you mean by Lookup transformation?
Lookup transformation is the method utilized in order to obtain relevant data from the database. It could be utilized to find a target, a source qualifier, or other sources. 

Through lookup transformation, we can find Different sorts of files like relational tables, synonym, view, and flat-file, etc. 

Lookup transformation could be cited as active or passive. Lookup transformation is compared with lookup transformation input values in the case of Mapping.  

7. What is Database?
A database is a collection of sensibly affiliated data. A database is generally smaller in size when compared to data warehouses.

8. What do you mean by Data warehouse?
Data warehouse, on the other hand, is a collection of different sorts of data. Different Data can be accessed from the data warehouse depending upon the need of the customer or user.

9. What is the domain?
A domain is a single organizational point by which all nodes and relationships are covered. 

10. Name different ports used for creating lookup transformation. 
The following are the ports that are utilized for creating lookup transformation:

• Lookup ports
• Return port
• Input port
• Output port

11. How will you filter rows in Informatica? 
In order to filter rows in Informatica, we can either use source qualifier transformation or filter transformation. 

12. Explain filtering rows in Informatica using source qualifier transformation. 
The data from the relational data source is read when it filters rows. Improves performance by minimizing the number of rows while Mapping. Moreover, to execute the database, the filter condition uses standard SQL. 

13. Explain filtering rows in Informatica using filter transformation. 
Filter transformation is a method to filter rows within mapped data. The source of the data can be anything. It could eliminate unwanted data and boost performance. This method can generate true or false values depending upon the condition. 

14. What do you know about Informatica PowerCenter tools?
Two types of tools are there in Informatica PowerCenter as follows:

• Server tools
• Client tools

15. What does server tools in Informatica PowerCenter include?
Server tools contain administration console and integration service. 

16. What client tools in Informatica PowerCenter incorporates?
It includes the Repository Manager, Workflow Monitor, Workflow Manager, 

Designer. 

17. What do you know about the Repository manager in Informatica PowerCenter?
The repository manager in Informatica PowerCenter is utilized to create folders to organize and protect metadata. 

18. Explain the designer in Informatica. 
The designer is ultimately used to construct the mappings by creating source definitions, target definitions, and transformations in Informatica PowerCenter. It could also develop mapplets. The mapplets are reusable in various mappings. 

19. Explain the Workflow manager. 
Workflow manager stores workflow metadata and connection object information. The workflow manager stores these in the repository. 

20. Tell us what do you know about SessionSession?
The SessionSession illustrates how and when to shift data from source to target. Thus it is a set of instructions that do so. 

21. What do you know about the workflow monitor in Informatica?
Workflow monitor, as the name suggests, is used to monitoring workflows and tasks and retrieve wf run status and also session logs. Wf instructs integration services on how to run tasks, Wf thus simply is a set of instructions that do so. 

22. How will you explain Mapping?
Mapping is a pair of target definitions and sources linked by transformation objects that formulates the rules for transformation. 

23. What do you mean by the transformation in Informatica?
A transformation is a repository object used for generating, passing, or modifying data. 

24. Differentiate between Active transformation and Passive transformation. 
Active transformation is capable of altering the position and number of rows passing through it. Rank and filter are two of the examples of Active transformation. 

A passive transformation, on the other hand, is not capable of altering the number of rows passing through it. A few examples of passive transformation are sequence generator and expression. 

25. Differentiate between connected transformation and unconnected transformation in Informatica. 
Connected transformation in Mapping is a transformation connected to other transformation, target definition, or source definition. 

Unconnected transformation in Mapping, on the other hand, is not connected to any of the Informatica objects. 

26. Can you differentiate between connected lookup and unconnected lookup in Informatica?
Connected lookup participates in data flow and obtains input from the pipeline. A connected search is capable of utilizing both static and dynamic cache. It is capable of reinstating multiple output ports. All lookup values are cached by connected lookup, and user-defined default values are also supported in the Connected search. 

Unconnected lookup obtains input from the LKP’s outcome. Unconnected lookup does not support dynamic cache. Only single-column values are returned by unconnected lookup. Unconnected lookup does not cache all lookup values but only lookup output ports in the return port and lookup conditions. Unconnected lookup has no allowance for user-defined default values. 

27. What is the maximum number of lookup parameters that could exist in the unconnected lookup?
There is no maximum number of lookup parameters. There is only one return value, but the number of lookup parameters in an unconnected lookup can be anything. 

28. List several lookup caches in Informatica. 
Lookups in Informatica can be classified as cached or uncached. The cached lookups can be further classified into dynamic and static. All different type of lookup caches in Informatica is given below: 

• Persistent cache
• Shared cache
• Recache
• Static cache
• Dynamic cache

29. Name all the types of transformations you know about. 

• Sorter transformation
• Update strategy transformation
• XML source qualifier transformation
• Aggregator transformation
• Expression transformation
• Filter transformation
• Router transformation
• Sequence generator transformation
• Stored procedure transformation
• Joiner transformation
• Lookup transformation
• Normalizer transformation
• Rank transformation

30. What output files Informatica creates during the SessionSession running?
The following are the files created during SessionSession running by Informatica:

• Informatica server log
• Session log file
• Session detail file
• Performance detail file
• Reject file
• Control file 
• Post-session email
• Indicator file
• Output file
• Cache files 

31. Name the different types of groups in the router transformation. 
The types of groups in router transformation are given below:

• Default group
• Input group
• Output group

32. Differentiate between powerhouse and repository server.
The powerhouse server can be called the primary regulating server helping in the integration processes of several distinct procedures among the different factors of the server’s database repository. 

The repository server is responsible for confirming repository integrity, consistency, and uniformity. 

33. On what factor does the number of repositories in Informatica depend upon?
The number of repositories in Informatica depends upon the number of ports of the Informatica. 

34. How partitioned is SessionSession useful?
The significant advantage of the partitioned SessionSession is that it enhances the operation of the server and increases efficiency. 

35. What is the difference between joiner and lookup transformations?

• In joiner transformation, the query can’t be overridden, whereas, in lookup transformation, the question can be replaced. 
• In joiner transformation, a single operator ‘=’ is there, but in the lookup transformations, we have every operator. 
• It is not possible to limit the number of rows when reading relational tables in case of joiner transformation, but the same is possible in Lookup transformation.  
• The table can be joined using join in joiner transformation. 

36. Name several types of lookup transformations. 
The types of Lookup transformation are as follows:

• Flat file lookup(relational)
• Pipeline lookup
• Cached and uncached lookup
• Connected and unconnected lookup

37. Explain SQ transformation in Informatica. 
The transformation which is connected and active and reads the rows from the relational or flat-file source while running a session is called SQ transformation. 

38. List the tasks that one can perform utilizing the SQ?
There are several tasks we can accomplish by making use of SQ. They are as follows:

• Data originating from identical database sources can be joined by configuring SQ. 
• The source filter can be utilized to specify specific conditions. 
• To select unique records, we can make use of a select distinct option. 
• After the SQL query, we can execute pre and post SQL statements. 
• We can override the default query in SQ by writing user-defined SQL queries.  

39. What do you know about the expression transformation?
We use expression transformation to calculate values in a single row. Expression transformation is a passive and connected transformation. Expression transportation is also useful in checking conditions statements before we write output results to target the other transformations or tables. 

40. What do you know about sorter transformation?
Sorter transformation helps sort data in descending or ascending order based on particular sort keys. Sorter transformation is connected and active transformation.

41. Explain aggregator transformation. 
All the aggregate calculations like MAX, MEDIAN, MIN, PERCENTILE, AVG, COUNT, FIRST, LAST, STDDEV, SUM, and VARIANCE can be performed using aggregator transformation which is an active and connected transformation.

42. What type of expressions aggregator transformation supports?
 Aggregator transformation supports the following type of expressions:

• Conditional clauses: true and false
• Non-Aggregate expressions: IIF(DEPT_ID=10,DEPT_NM,DEPT_ID||’_’||DEPT_NM
• MAX (COUNT (EMP))
• Nested Aggregation Expression

43. Explain Filter transformation.
We use Filter transformation, which is an active and connected transformation to test data based on specific conditions passing through it. 

Once the data satisfied the specific condition passed through it, the data is promoted to the next target. Otherwise, it is dropped. 

44. What do you know about router transformation?
When we need to test the source data based on multiple conditions, we make use of router transformation, which is an active and connected transformation. 

45. What do you mean by rank transformation?
When we need to select the top values of data or bottom values of the data, we make use of this active and connected transformation called rank transformation. 

46. What do you know about Sequence Generator Transformation?
To generate numeric values, we make use of Sequence Generator Transformation. Sequence Generator Transformation is a passive and connected transformation helpful in generating distinct primary key values to recognize every record uniquely. 

47. Explain union transformation. 
Union transformation helps merge data from more than one source to a single pipeline branch. It is an active and connected transformation. Union transformation is incapable of removing duplicate rows. 

48. What do you know about the Update Strategy transform?
To maintain history data into the target table, we use the update strategy transformation. 

49. Explain pushdown optimization. 
Pushdown optimization is the process used to push transformation logic to the target database or source database. The SessionSession can be configured for pushdown optimization. In such a session, the transformation logic is converted into SQL queries by Integration Service, and then these SQL queries are sent to the database. Finally, the database executes these received SQL queries. 

50. Name different types of partitioning in Informatica. 
The different type of partitioning in Informatica is as follows:

• Key range Partition
• Round Robin Partitioning
• Hash Partitioning
• PassThrough Partition

51. Differentiate between the stop and abort. 
By using the STOP command, the integration service quits examining data from the source processing, committing, and writing the data to targets continues. The abort command can then be used in case the integration service is unable to process and determine data. 

ABORT command, on the other hand, has a specified timeout period. The limit of this timeout period is 60 seconds. The DTM process gets killed, and the SessionSession terminates in case integration service is incapable of processing and committing data in the timeout period of ABORT command that is 60 seconds. 

52. What do you mean by the surrogate key?
The surrogate key is a unique sequential number acting as an identifier. It uniquely identifies objects in a database. Surrogate keys can be used as primary keys, while it is not necessary to do so. 

53. What do you mean by sessions?
Sessions are instruction to be implemented to transform data from source to target. The Session’sSession’s manager or pm cmd command is useful for user sessions. 

54. Explain incremental aggregation. 
Incremental aggregation is the process often used to track alteration in the source, and it is also used for calculating aggregate in a session. 

55. How will you delete duplicate rows from flat files?
Using the sorter transformation, it is possible to delete duplicate rows from flat files. To do so, we should select the distinct option in sorter transformation. 

56. What is parallel processing?
When The processing is carried out using the partitioning sessions, it is called parallel processing. Parallel processing enhances hardware performance. In Informatica PowerCenter, the partitioning options help improve performance by parallel processing of data. Divison of large sets of data into smaller once enables better performance of the SessionSession. 

57. What is the difference between mapping parameters and Mapping variables?
The difference between the two is as follows:

The altering values of the Session’sSession’s execution are what we call mapping variables, whereas Mapping parameters are valued that do not change during the SessionSession. 

58. How would you find if the Mapping is right or wrong?
The debugging option in Informatica enables us to decide whether the Mapping is correct or not correct, and this does not require connecting to the SessionSession. 

59. Explain OLAP?
OLAP is the abbreviation for On-Line Analytical Processing. It is a method utilized in On-Line Analytical Processing. There are two types of OLAP- HOLAP and ROLAP. 

60. Explain the standalone command task.
The standalone command task enables shell command to run anywhere during the workflow. 

61. Name the tools present in the workflow manager. 
The following tools are available in the workflow manager:

• Worklet Designer 
• Task Designer  
• Task Developer 
• Workflow Designer  

62. When does the Informatica server the file?
The Informatica server rejects the file when the server encounters rejection of update strategy transformation. The database is also disrupted in such a case, but this happens rarely. 

63. Tell us something about the PMCMD utility command in Informatica?
PMCMD utility command enables us to perform specific tasks that could be completed by the workflow manager by establishing communication with the integration service. 

Workflows can be either started or scheduled with the help of the PMCMD utility command. 

Command-line mode and interactive mode are two modes in which PMCMD utility command runs.  

64. Explain Worklets in Informatica?
Worklets can expand and execute the task within a workflow. It is merely a collection of related tasks. A workflow is containing worklets cam be called a parent workflow. 

65. Explain the dimensional model. 
The dimensional model in Informatica is as follows:

• Data modeling: Data modeling is the procedure of formulating a database by fulfilling business requirement specifications. 
• Database Architect uses Erwin to design a warehouse database. Erwin is a data modeling tool that is GUI-based
• Schemas are a data model comprising one or more than one table. The following are the schemas for a data warehouse:

Star Schema.

Snowflake Schema.

Gallery Schema.

66. What is the location of the throughput option in the Informatica?
The throughput option in Informatica is available in the workflow monitor. When we right-click on the SessionSession, after clicking on the SessionSession, we need to click on get run property, and the throughput option will be available under source/target statistics. 

67. Explain snowflake schema. 
In snowflake schema, large tables that are denormalized are transformed or split into more than one normalized dimensions. A snowflake schema is very useful in increasing select query performance. 

Conclusion 
These were some important and frequently asked Informatica Interview questions along with their answers. These questions and answers formulated based on research done on general Informatica interview questions. 

I hope these questions, along with their answers on Informatica Interview questions, were helpful to you. 

Reading these questions and their answers carefully would enhance your Informatica knowledge and will make you ready to face the interview. 

I hope you liked reading the article and thanks for reading.