Informatica Interview Questions

Mindmajix offers Advanced Informatica Interview Questions 2024 that help you in cracking your interview & acquire a dream career as Informatica Developer.

For your better understanding, we have segregated these Informatica Interview Questions into 3 different categories.

• Basic
• Scenario-Based
• Advanced

Want to enhance your career and become a professional in Informatica, then enroll in "Informatica Training". This course will help you to achieve excellence in this domain.

 

 

Best Informatica Interview Questions and Answers

This Informatica Interview Questions blog covers all the core concepts from basic to advanced level. So utilize these Questions to improve your chances of being hired in your next interview!

1. What is the difference between Informatica vs Talend?

The following are the difference between Informatica vs Talend

Informatica	Talend
Provides only commercial data integration	Available open-source and commercial editions
Founded way back in 1993	Founded in the year 2006
Charges applicable per customer	Open source is for free
RDBMS repository stores metadata generated	Implemented on any java supported platforms
Integrating code is not so effective	Code customization is effective
No prior knowledge is required	knowledge on java is preferred
Automated deployment is not up to the mark	Deployment made easy
Transformations are re-usable	Components are re-usable

2. What is the meaning of Enterprise Data Warehousing?

Enterprise Data Warehousing is the data of the organization being created or developed at a single point of access. The data is globally accessed and viewed through a single source since the server is linked to this single source. It also includes the periodic analysis of the source.

3. What is the meaning of Lookup transformation?

To get the relevant data or information, the Lookup transformation is used to find a source qualifier, a target, or other sources. Many types of files can be searched in the Lookup transformation like for example flat files, relational tables, synonym, or views, etc. The Lookup transformation can be cited as active or passive. It can also be either connected or unconnected. In mapping, multiple lookup transformations can be used. In the mapping, it is compared with the lookup input port values.

The following are the different types of ports with which the lookup transformation is created:

1. Input port
2. Output port
3. Lookup ports
4. Return port

4. What are the points of difference between connected lookup and unconnected lookup?

Connected lookup is the one that takes up the input directly from the other transformations and also participates in the data flow. On the other hand, an unconnected lookup is just the opposite. Instead of taking the input from the other transformations, it simply receives the values from the result or the function of the LKP expression.

Connected Lookup cache can be both dynamic and static but unconnected Lookup cache can't be dynamic in nature.  The First one can return to multiple output ports but the latter one returns to only one output port. User-defined values which ads generally default values are supported in the connected lookup but are not supported in the unconnected lookup.

5. How many input parameters can be present in an unconnected lookup? 

The number of parameters that can include in an unconnected lookup is numerous. However, no matter how many parameters are put, the return value would be only one. For example, parameters like column 1, column 2, column 3, and column 4 can be put in an unconnected lookup but there is only one return value.

6. How many lookup caches are available?

Informatica lookup caches can be of different nature like static or dynamic. It can also be persistent or non-persistent. Here are the names of the caches:

1. Static Cache 
2. Dynamic Cache 
3. Persistent Cache 
4. Shared Cache 
5. Reached 

7. What is the difference between a data warehouse, a data mart, and a database?

Data warehouse consists of different kinds of data. A database also consists of data but however, the information or data of the database is smaller in size than the data warehouse. Datamart also includes different sorts of data that are needed for different domains. Examples - Different dates for different sections of an organization like sales, marketing, financing, etc.

8. What is a domain?

The main organizational point sometimes undertakes all the interlinked and interconnected nodes and relationships and this is known as the domain. These links are covered mainly by one single point of the organization.

9. What is the Cite the differences between a powerhouse and a repository server?

The powerhouse server is the main governing server that helps in the integration process of various different processes among the different factors of the server's database repository. On the other hand, the repository server ensures repository integrity, uniformity, and consistency.

10. In Informatica, how many numbers repositories are possible to be made?

The total figure of repositories created in Informatica mainly depends on the total amounts of the ports of the Informatica.

11. What are the benefits of a partitioned session?

A session is partitioned in order to increase and improve the efficiency and the operation of the server. It includes the solo implementation sequences in the session.

Informatica Scenario Based Interview Questions

12. Define parallel processing?

Parallel processing helps in further improvement of performance under hardware power. The parallel processing is actually done by using the partitioning sessions. This partitioning option of the Power Center in Informatica increases the performance of the Power Center by parallel data processing. This allows the large data set to be divided into a smaller subset and this is also processed in order to get a good and better performance of the session.

13. What are the different types of methods for the implementation of parallel processing in Informatica?

There are different types of algorithms that can be used to implement parallel processing. These are as follows:

• Database Partitioning - Database partitioning is actually a type of table partitioning information. There is a particular type of service that queries the database system or the information of the database, named the Integration Service. Basically, it looks up the partitioned data from the nodes of the database.
• Round-Robin Partitioning - With the aid of this, the Integration service does the distribution of data across all partitions evenly. It also helps in grouping data in a correct way.
• Hash Auto-keys partitioning - The hash auto keys partition is used by the power center server to group data rows across partitions. These grouped ports are used as a compound partition by the Integration Service. 
• Hash User-Keys Partitioning - This type of partitioning is the same as auto keys partitioning but here rows of data are grouped on the basis of a user-defined or a user-friendly partition key. The ports can be chosen individually that correctly defines the key. 
• Key Range Partitioning - More than one type of port can be used to form a compound partition key for a specific source with its aid, the key range partitioning. Each partition consists of different ranges and data is passed based on the mentioned and specified range by the Integration Service. 
• Pass-through Partitioning - Here, the data are passed from one partition point to another. There is no distribution of data. 

14. What are the best mapping development practices?

Best mapping development practices are as follows -  

• Source Qualifier - This includes extracting the necessary data-keeping aside the unnecessary ones. It also includes limiting columns and rows. Shortcuts are mainly used in the source qualifier. The default query options like for example User Defined Join and Filter etc, are suitable to use other than using source qualifier query override. The latter doesn't allow the use of partitioning possible all the time. 
• Expressions - It includes the use of local variables in order to limit the number of huge calculations. Avoiding data type conversions and reducing invoking external coding is also part of an expression. Using operators are way better than using functions as numeric operations are better and faster than string operation.
• Aggregator - Filtering the data is a necessity before the Aggregation process. It is also important to use sorted input. 
• Filter - The data needs a filter transformation and it is a necessity to be close to the source. Sometimes, multiple filters are also needed to be used which can also be later replied by a router.  
• Joiner - The data is required to be joined in the Source Qualifier as it is important to do so. It is also important to avoid the outer joins. A fewer row is much more efficient to be used as a Master Source.  
• Lookup - Here, joins replace the large lookup tables and the database is reviewed. Also, database indexes are added to columns. Lookups should only return those ports that meet a particular condition.

Explore Informatica Tutorial for more information

15. What are the different mapping design tips for Informatica?

The different mapping design tips are as follows:

• Standards - The design should be of a good standard. Following a standard consistently is proven to be beneficial in the long run projects. Standards include naming descriptions, conventions, environmental settings, documentation and parameter files, etc. 
• Reusability - Using reusable transformation is the best way to react to the potential changes as quickly as possible. applets and worklets, these types of Informatica components are best suited to be used. 
• Scalability - It is important to scale while designing. In the development of mappings, the volume must be correct. 
• Simplicity - It is always better to create different mappings instead of creating one complex mapping. It is all about creating a simple and logical process of design 
• Modularity - This includes reprocessing and using modular techniques for designing.

16. What is the meaning of the word ‘session’? Give an explanation of how to combine execution with the assistance of batches?

Converting data from a source to a target is generally implemented by a teaching service and this is known as a session. Usually, the session manager executes the session. In order to combine session’s executions, batches are used in two ways - serially or parallelly.

17. How many numbers of sessions are grouped in one batch?

Any number of sessions can be grouped in one batch but however, for an easier migration process, it is better if the number is lesser in one batch.

18. What is the difference between mapping parameters and mapping variables?

The mapping variable refers to the changing values of the sessions' execution. On the other hand, when the value doesn't change during the session then it is called mapping parameters. The mapping procedure explains the procedure of the mapping parameters and the usage of these parameters. Values are best allocated before the beginning of the session to these mapping parameters.

19. What are the features of complex mapping?

These are the three most important features of complex mapping.

1. Difficult requirements
2. Numerous transformations 
3. Complex logic regarding business 

20. Which option helps in finding whether the mapping is correct or not?

The debugging option helps in judging whether the mapping is correct or not without really connecting to the session.

21. What do you mean by OLAP?

OLAP or also known as On-Line Analytical Processing is the method with the assistance of which multi-dimensional analysis occurs.

22. Mention the different types of OLAP?

The different types of OLAP are

1. ROLAP
2. HOLAP

23. What is the meaning of the surrogate key?

The surrogate key is just the replacement in the place of the prime key. The latter is natural. This is a different type of identity for each consisting of different data.

Are you planning to build a career in Informatica? Sign up for this Informatica Certification Training in Hyderabad to begin your journey today!

24. What is a session task?

When the Power Centre Server transfers data from the source to the target, it is often guided by a set of instructions and this is known as the session task.

25. What is the meaning of the command task?

Command task only allows the flow of more than one shell command or sometimes the flow of one shell command in Windows while the work is running.

26. What is the meaning of a standalone command task?

The type of command task that allows the shell commands to run anywhere during the workflow is known as the standalone task.

27. Define workflow?

The workflow includes a set of instructions that allows the server to communicate for the implementation of tasks.

28. How many tools are there in the workflow manager?

There are four types of tools:

1. Task Designer 
2. Task Developer
3. Workflow Designer 
4. Worklet Designer 

29. Define target load order?

Target load order is dependent on the source qualifiers in a mapping. Generally, multiple source qualifiers are linked to a target load order.

30. Define Power Centre repository of Informatica?

Informatica Power Centre consists of the following Metadata;

• Source Definition 
• Session and session logs
• Workflow 
• Target Definition 
• Mapping 
• ODBC Connection  

Two repositories are as follows 

1. Global Repositories 
2. Local Repositories  

Mainly Extraction, Loading (ETL), and Transformation of the above-mentioned metadata are performed through the Power Centre Repository. 

For More Info: Informatica ETL Tools

31. Name the scenario in which the Informatica server rejects files?

When the server faces rejection of the update strategy transformation, it regrets files. The database consisting of the information and data also gets disrupted. This is a rare case scenario.

Informatica Advanced Interview Questions and Answers

32. How to use Normalizer Transformation in Informatica?

• This is of type an Active T/R which reads the data from COBOL files and VSAM sources (virtual storage access method)
• Normalizer T/R act like a source Qualifier T/R while reading the data from COBOL files.
• Use Normalizer T/R that converting each input record into multiple output records. This is known as Data pivoting.

33. What are the Limitations of Pushdown Optimization?

1. Rank T/R cannot be pushed
2. Transaction control T/R
3. Sorted aggregation.

Procedure:

1. Design a mapping with filter, rank, and expression T/R.

2. Create a session --> Double click the session select properties tab.

Attribute	Value
Pushdown optimization	Full

Double click the session --> Select the mapping tab from the left window --> select pushdown optimization.

35. How to use PMCMD Utility Command?

36. How do I scheduling a Workflow?

A schedule is automation of running the workflow at a given date and time.

There are 2 types of schedulers:

1. Reusable scheduler
2. Non Reusable scheduler

Reusable scheduler:

A reusable scheduler can be assigned to multiple workflows.

Non Reusable scheduler:

• A non-reusable scheduler is created specifically to the workflow.
• A non-reusable scheduler can be converted into a reusable scheduler.

The following are the 3rd party schedulers:

1. Cron (Unix-based sch/eduling process)
2. Tivoli
3. Control M
4. Autosys
5. Tidal
6. WLM (work hard manager)

- 99% of production people will do the scheduling.

• Before we run the workflow manually. Through scheduling, we run a workflow called Auto-Running

37. What is Dynamic Lookup Cache?

 

• The cache updates or changes dynamically when lookup at the target table.
• The dynamic lookup T/R allows for the synchronization of the target lookup table image in the memory with its physical table in the database.
• The dynamic lookup T/R or dynamic lookup cache is operated in only connected mode (connected lookup )
• Dynamic lookup cache support only equality conditions (=conditions)

New Lookup Row	Description
0	The integration service does not update or insert the row in the cache
1	The integration service inserts the row into the cache
2	The integration service updates the row in the cache

38. How to use PowerCenter Command-Line in Informatica?

The transformation language provides two comment specifiers to let you insert comments in the expression:

• Two Dashes ( - - )
• Two Slashes ( / / )

The Power center integration service ignores all text on a line preceded by these two comment specifiers.

• Mapping variables is used for incremental extraction.
• In mapping variables no need to change the data. It automatically changed.
• In the mapping parameter, you have to change the date and time.

1. Reads the data from XML files.
2. XML source definition associates with XML source Qualifier.
3. XML files are case-sensitive markup language.
4. Files are saved with an extension. XML.
5. XML files are hierarchical (or) parent-child relationship file formats.
6. Files can be normalized or denormalized.

42. What is Load Order?

45. What is Mapping Debugger?

• A debugger is a tool. By using this we can identify records are loaded or not and correct data is loaded or not from one T/R to another T/R.
• Session succeeded but records are not loaded. In this situation, we have to use the Debugger tool.

46. What is the functionality of F10 in Informatica?

F10 --> Next Instance

47. What T/R has No cast?

Lookup T/R

Note:- Prevent wait is available in any task. It is available only in the Event wait task.

• F5  -->  Start Debugger.
• A debugger is used to test the records are loader or not, correct data is loader or not.
• A debugger is used only to test Valid Mapping but not invalid Mapping.

48. What are Worklet and what types of workouts?

1. A worklet is defined as a group of related tasks.
2. There are 2 types of the worklet:
   • Reusable worklet
   • Non-Reusable worklet
3. Worklet expands and executes the tasks inside the workflow.
4. A workflow that contains the worklet is known as Parent Workflow.

(a) Reusable Worklet:

• Created using the worklet designer tool.
• Can be assigned to Multiple workflows.

(b) Non-Reusable Worklet:

• Created using workflow designer tool.
• Created Specific to the workflow.

49. What is Relative Mode?

Relative Time: The timer task can start the timer from the start timer of the timer task, the start time of the workflow or worklet, or from the start time of the parent workflow.

• A timer task is mainly used for scheduling workflow.
• Workflow 11 AM --> Timer (11:05 AM) --> Absolute Mode
• Anytime workflow start after 5 mins Timer --> (5 mins) will start Relative Mode.

50. What is the Difference between Filter and Router T/R?

• Create, edit and delete folders.
• Assign users to access the folders with reading, write and execute permissions.
• Backup and Restore repository objects.

52. What is Rank Transformation in Informatica?

This a type of active T/R which allows you to find out either top performance or bottom performers.

Rank T/R is created with the following types of the port:

1. Input Port (I)
2. Output Port (O)
3. Rank Port (R)
4. Variable Port (V)

53. What is meant by Informatica PowerCenter Architecture?

• Power Center Clients
• Power Center Repository.
• Power Center Domain.
• Power Center Repository Service  (PCRS)
• Power Center Integration Service (PCIS)
• Informatica administrator.

Mapping is nothing but an ETL Application.

It is a GUI-based client that allows you to create the following ETL objects.

• Session
• Workflow
• Scheduler

Session:

• A session is a task that executes mapping.
• A session is created for each Mapping.
• A session is created to provide runtime properties.
• A session is a set of instructions that tells the ETL server to move the data from source to destination.

Workflow:

Workflow is a  set of instructions that tells how to run the session tasks and when to run the session tasks.

A data integration tool that combines the data from multiple OLTP source systems, transforms the data into a homogeneous format and delivers the data throughout the enterprise at any speed.

It is a GUI-based ETL product from Informatica corporation which was founded in 1993 in Redwood City, California.

There are many products in Informatica corporation:

• Informatica Analyzer.
• Life cycle management.
• Master data

Having many products in Informatica.

Informatica power center is one of the products of Informatica.

Using Informatica power center we will do the Extraction, transformation, and loading.

Data Modeling:

• It is a process of designing the database by fulfilling business requirements specifications.
• A Data Modeler (or) Database Architect Designs the warehouse Database using a GUI based data modeling tool called “Erwin”.
• ERWin is a data modeling tool from Computer Associates (A).

Dimensional modeling consists of the following types of schemas designed for Datawarehouse:

• • Star Schema.

  • Snowflake Schema.

  • Gallery Schema.

A schema is a data model that consists of one or more tables.

59. How does Rank transformation handle string values?

Rank transformation can return the strings at the top or the bottom of a session sort order. When the Integration Service runs in Unicode mode, it sorts character data in the session using the selected sort order associated with the Code Page of IS which may be French, German, etc. When the Integration Service runs in ASCII mode, it ignores this setting and uses a binary sort order to sort character data.

Informatica Basic Interview Questions and Answers

60. Is a sorter an active or passive transformation?

The sorter is an active transformation because when it configures output rows, it discards duplicates from the key and consequently changes the number of rows.

61. Mention the types of transformations available in Informatica.

The following are the types of transformations available in Informatica:

• Source Qualifier Transformation
• Rank Transformation
• Lookup and Reusable transformation
• Router Transformation
• Aggregator Transformation
• Joiner transformation
• Sequence Generator Transformation
• Transaction Control Transformation
• Performance Tuning for Transformation
• Expression Transformation
• Normalizer Transformation
• External Transformation

62. What is the difference between active and passive transformation?

Based on the change in the number of rows, the active transformations are those which change the number of input and data rows passed to them. While passive transformations remain the same for any number of input and output rows passed to them.

63. What are the output files created by the Informatica server at runtime?

The output files created by the Informatica server at runtime are listed below:

• Informatica Server log: Informatica home directory creates a log for all the error messages and status.
• Session log file: For each session, a session log file stores the data into the log file about the ongoing initialization process, SQL commands, errors, and more.
• Session detail file: It contains load statistics for each target in mapping, including data about the name of the table, no of rows written or rejected.
• Performance detail file: It includes data about session performance.
• Reject file: Rows of data not written to targets.
• Control file: Information about target flat-file and loading instructions to the external loader.
• Post-session email: Automatically delivers session run data to designated recipients. 
• Indicator file: It contains a number to indicate whether the row was marked for insert, delete or reject, and update.
• Output file: Informatica server creates a target file based on the details entered in the session property sheet.
• Cache file: It automatically builds, when the Informatica server creates a memory cache.

64. What is the difference between static cache and dynamic cache? 

The following are the differences between static cache and dynamic cache:

Static Cache	Dynamic Cache
A static cache is a default cache, and we cannot insert or update the caches	In the dynamic cache, we can insert or update data into the lookup and pass the data to the target.
Handles multiple matches	Doesn't handles multiple matches
Suitable for relational and flat file lookup types	Suitable for relational lookup types
Relational operators like = &= can be used	Only = operator is used
Used for both connected and unconnected lookup transformation	Used for only connected lookup transformation

65. Can you tell what types of groups does router transformation contains?

Router transformation contains the following types of transformations:

1. Input group

2. Output group:

Further, the output group contains two types:

• 1. User-defined groups
  2. Default group

66. How do you differentiate stop and abort options in a workflow monitor?

The below table will detail the differences between the stop and abort options in a workflow monitor:

Stop	Abort
The stop option is used for executing the session task and allows another task to run.	The abort option turns off the task completely that is running.
While using this option, the integration service stop reading data from the source of the file	Abort waits for the services to be completed, and then only actions take place
Processes data to the targets or sources	Time out period of 60 seconds
Stops sharing resources from the processes	Stops the process and session gets terminated

67. Is it possible to store previous session logs in Informatica?

• Yes, it is possible. If any session is running in timestamp mode, then automatically session logout will not overwrite the current session log.
• Go to Session Properties –> Config Object –> Log Options.
• Select the properties as follows.
• Save session log by –> SessionRuns
• Save session log for these runs –> Change the number that you want to save the number of log files (Default is 0)
• If you want to save all of the log files created by every run, and then select the option Save session log for these runs –> Session TimeStamp.

68. What do you know about Data-Driven sessions?

• In Informatica, Data-Driven is the property that decides the way the data needs to perform when mapping includes an Update strategy transformation.
• By mentioning DD_INSERT or DD_DELETE or DD_UPDATE in the update strategy transformation, we can execute data-driven sessions.

69. What is a Mapplet in Informatica?

Ans. A reusable data object created in the Mapplet designer is called a Mapplet. It includes a collection of transformations that allows you to reuse transformation logic in different mappings.

70. What is the difference between Mapping and Mapplet?

The following are the difference between Mapping and Mapplet:

71. List the transformations used for SQL override.

The below-listed transformations are used for SQL override:

• Source Qualifier
• Lookup
• Target

72. State the differences between SQL override and Lookup override.

The differences between SQL override and Lookup override are listed below:

SQL Override	Lookup Override
Limits the no of rows that enter the mapping pipeline	Limits the no of lookup rows for avoiding table scan and saves lookup time
Manually enters the query	By default, the "Order By" clause
Supports any kind of join by writing the query	Supports only Non-Equi joins

73. What is a shared cache?

A shared cache is a static lookup cache shared by various lookup transformations in the mapping. Using a shared cache reduces the amount of time needed to build the cache.

74. Explain code page compatibility.

Compatibility between code pages used for getting accurate data movement when the Informatica Server runs in the Unicode data movement mode. There won't be any data losses if code pages are identical. One code page can be a superset or subset of another.

75. Define Expression transformation?

In Informatica, expression transformation is a passive transformation that allows performing non-aggregate calculations on the source data. It means you can perform calculations on a single row. Using this transformation, you can test data before passing it to the target table or another transformation through conditional statements. 

76. What is Aggregator transformation?

Aggregator Transformation in Informatica is an active transformation that allows you to perform calculations like sum, average, etc. We can perform aggregate operations over a group of rows, and it stores all the estimates and records in a temporary placeholder called aggregator cache memory.

77. What do you know about filter transformation?

Filter transformation in Informatica is an active transformation that changes the number of rows passed through it. It allows the rows to pass through it based on specified filter conditions and drops rows that don't meet the requirement. The data can be filtered based on one or more terms.

78. Why is union transformation active?

In Informatica, union transformation is an active transformation because it combines the two or more data streams into one. The total no of rows passing through Union is equal to no of rows passing out of it, and the sequence of rows from the input stream preserved are the same in the output stream, but we cannot preserve the position of the rows.

79. What is the use of incremental aggregation in Informatica?

Incremental aggregation usually gets created when a session gets created through the execution of an application. This aggregation allows you to capture changes in the source data for aggregating calculations in a session. If the source changes incrementally, you can capture those changes and configure the session to process them. It will allow you to update the target incrementally, rather than deleting the previous load data and recalculating similar data each time you run the session.

80. What does reusable transformation mean?

Reusable transformations are practiced numerous times in mapping. It is stored as metadata and is different from other mappings that use transformation. If any changes are performed to the reusable transformation, then all the mappings where the transformation is used get nullified. 

81. How does update strategy work in Informatica?

The update strategy is the active and connected transformation that allows to insert, delete, or update records in the target table. Also, it restricts the files from not reaching the target table.

82. Differentiate Informatica and Datastage.

Both Informatica and Datastage are powerful ETL tools. Still, the significant difference between both is Informatica forces you to organize in a step-by-step process. In contrast, Datastage provides flexibility in dragging and dropping objects based on logic flow.

Informatica	Datastage
Dynamic partitioning	Static partitioning
Supports flat-file lookups	Supports hash files, lookup file sets, etc.
It has a service-oriented architecture	It has a client-server architecture
Step by step data integration solution	Project-based integration solutions

83. Explain transaction control transformation.

Transaction Control in Informatica is an active and connected transformation that allows committing or rollbacking transactions during mapping execution. A transaction is a collection of rows bound by commit or rollback rows. Based on variation in no input rows, a transaction is defined. Commit or rollback operations ensure data availability.

The built-in variables available in this transformation are:

• TC_CONTINUE_TRANSACTION
• TC_COMMIT_BEFORE
• TC_COMMIT_AFTER
• TC_ROLLBACK_BEFORE
• TC_ROLLBACK_AFTER

84. State the differences between Informatica PowerCenter and Informatica PowerMart.

Informatica PowerCenter	Informatica PowerMart
Informatica PowerCenter processes high volumes of data	Informatica PowerMart processes small volumes of data
Supports data retrieval from ERPSources like PeopleSoft, SAP, etc.	Doesn't support data retrieval
Converts local repositories to global	Doesn't convert local repositories to global
Supports both global and local repositories	Supports only local repositories

List of Informatica Courses:

Mindmajix offers training for many other Informatica courses depending on your requirement:

Informatica Analyst	Informatica PIM
Informatica SRM	Informatica MDM
Informatica Data Quality	Informatica ILM
Informatica Big Data Edition	Informatica Multi-Domain MDM