Informatica interview questions

Top Informatica Interview Questions You Must Prepare

There has never been a better time than this to explore a career around data warehousing, and with companies investing in tools like Informatica Power Center, there is a critical need for trained personnel to leverage these tools for better business insights. So we, at Icronix, have compiled a set of Question Answer type and Scenario based Informatica Interview questions, which will help you face the Informatica interviews.

Over the years, the data warehousing ecosystem has changed. Data warehouses aren’t just bigger than a few years ago, they’re faster, support new data types, and serve a wider range of business-critical functions. But the most important change has been in their ability to provide actionable insights to enterprises and reshape the way companies look at innovation, competition and business outcomes. The fact that data warehousing is today one of the most critical components of an enterprise, has led to tremendous growth in job opportunities and roles.  If you are exploring a job opportunity around Informatica, here is a list of Informatica interview questions that will help you crack your Informatica interview. However, if you have already taken an Informatica interview, or have more questions, we urge you to add them in the comments tab below to help the community at large.

1. What are the differences between connected lookup and unconnected lookup?

Connected Lookup	Unconnected Lookup
1. It receives input from the pipeline & participates in the data flow.	1. It receives input from the result of an LKP.
2. It can use both, dynamic and static cache.	2. It can’t be dynamic.
3. It can return more than one column value i.e. output port.	3. It can return only one column value.
4. It caches all lookup columns.	4. It caches only the lookup output ports in the return port & lookup conditions.
5. It supports user-defined default values.	5. It doesn’t support user-defined default values.

2. What is Lookup transformation?

• Lookup transformation is used to look up a source, source qualifier, or target in order to get relevant data.
• It is used to look up a ‘flat file’, ‘relational table’, ‘view’ or ‘synonym’.
• Lookup can be configured as Active or Passive as well as Connected or Unconnected transformation. 
• When the mapping contains the lookup transformation, the integration service queries the lookup data and compares it with lookup input port values. One can use multiple lookup transformations in a mapping.
• The lookup transformation is created with the following type of ports:
  • Input port (I)
  • Output port (O)
  • Look up Ports (L)
  • Return Port (R)

3. How many input parameters can exist in an unconnected lookup?

Any number of input parameters can exist. For instance, you can provide input parameters like column 1, column 2, column 3, and so on. But the return value would only be one.

4. Name the different lookup cache(s)?

Informatica lookups can be cached or un-cached (no cache). Cached lookups can be either static or dynamic. A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after completing session run or if it deletes it.

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

5. Is ‘sorter’ an active or passive transformation?

It is an active transformation because it removes the duplicates from the key and consequently changes the number of rows.

6. What are the various types of transformation?

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

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

Active Transformation:- An active transformation can perform any of the following actions:

• Change the number of rows that pass through the transformation: For instance, the Filter transformation is active because it removes rows that do not meet the filter condition.
• Change the transaction boundary: For e.g., the Transaction Control transformation is active because it defines a commit or roll back transaction based on an expression evaluated for each row.
• Change the row type: For e.g., the Update Strategy transformation is active because it flags rows for insert, delete, update, or reject.

Passive Transformation: A passive transformation is one which will satisfy all these conditions:

• Does not change the number of rows that pass through the transformation
• Maintains the transaction boundary
• Maintains the row type

8. Name the output files created by Informatica server during session running.

• Informatica server log: Informatica server (on UNIX) creates a log for all status and error messages (default name: pm.server.log). It also creates an error log for error messages. These files will be created in the Informaticahome directory.
• Session log file: Informatica server creates session log files for each session. It writes information about sessions into log files such as initialization process, creation of SQL commands for reader and writer threads, errors encountered and load summary. The amount of detail in the session log file depends on the tracing level that you set.
• Session detail file: This file contains load statistics for each target in mapping. Session detail includes information such as table name, number of rows written or rejected. You can view this file by double clicking on the session in the monitor window.
• Performance detail file: This file contains session performance details which tells you where performance can be improved. To generate this file, select the performance detail option in the session property sheet.
• Reject file: This file contains the rows of data that the writer does not write to targets.
• Control file: Informatica server creates a control file and a target file when you run a session that uses the external loader. The control file contains the information about the target flat file such as data format and loading instructions for the external loader.
• Post session email: Post session email allows you to automatically communicate information about a session run to designated recipients. You can create two different messages. One if the session completed successfully and another if the session fails.
• Indicator file: If you use the flat file as a target, you can configure the Informatica server to create an indicator file. For each target row, the indicator file contains a number to indicate whether the row was marked for insert, update, delete or reject.
• Output file: If a session writes to a target file, the Informatica server creates the target file based on file properties entered in the session property sheet.
• Cache files: When the Informatica server creates a memory cache, it also creates cache files. For the following circumstances, Informatica server creates index and data cache files.

9. How do you differentiate dynamic cache from static cache?

The differences are shown in the table below:

10. What are the types of groups in router transformation?

• Input group
• Output group
• Default group

11. What is the difference between STOP and ABORT options in Workflow Monitor?

On issuing the STOP command on the session task, the integration service stops reading data from the source although it continues processing the data to targets. If the integration service cannot finish processing and committing data, we can issue the abort command.

ABORT command has a timeout period of 60 seconds. If the integration service cannot finish processing data within the timeout period, it kills the DTM process and terminates the session

12. How can we store previous session logs?

If you run the session in the time stamp mode then automatically session log out 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

You can find these properties in the session/workflow Properties.

13. What are the similarities and differences between ROUTER and FILTER?

The differences are:

Advantages of Router transformation over Filter transformation:

• Better Performance; because in mapping, the Router transformation Informatica server processes the input data only once instead of as many times, as you have conditions in Filter transformation.
• Less complexity; because we use only one Router transformation instead of multiple Filter transformations.
• Router transformation is more efficient than Filter transformation.

For E.g.:

Imagine we have 3 departments in source and want to send these records into 3 tables. To achieve this, we require only one Router transformation. In case we want to get same result with Filter transformation then we require at least 3 Filter transformations.

Similarity:

A Router and Filter transformation are almost same because both transformations allow you to use a condition to test data.

14. Why is sorter an active transformation?

When the Sorter transformation is configured to treat output rows as distinct, it assigns all ports as part of the sort key. The integration service discards duplicate rows that were compared during the sort operation. The number of input rows will vary as compared to the output rows and hence it is an active transformation.

15. When do you use SQL override in a lookup transformation?

You should override the lookup query in the following circumstances:

1. Override the ORDER BY clause. Create the ORDER BY clause with fewer columns to increase performance. When you override the ORDER BY clause, you must suppress the generated ORDER BY clause with a comment notation.
   Note: If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.
2. A lookup table name or column names contains a reserved word. If the table name or any column name in the lookup query contains a reserved word, you must ensure that they are enclosed in quotes.
3. Use parameters and variables. Use parameters and variables when you enter a lookup SQL override. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the SQL statement, or use a parameter or variable as the SQL query. For example, you can use a session parameter, $ParamMyLkpOverride, as the lookup SQL query, and set $ParamMyLkpOverride to the SQL statement in a parameter file. The designer cannot expand parameters and variables in the query override and does not validate it when you use a parameter or variable. The integration service expands the parameters and variables when you run the session.
4. A lookup column name contains a slash (/) character. When generating the default lookup query, the designer and integration service replace any slash character (/) in the lookup column name with an underscore character. To query lookup column names containing the slash character, override the default lookup query, replace the underscore characters with the slash character, and enclose the column name in double quotes.
5. Add a WHERE clause. Use a lookup SQL override to add a WHERE clause to the default SQL statement. You might want to use the WHERE clause to reduce the number of rows included in the cache. When you add a WHERE clause to a Lookup transformation using a dynamic cache, use a Filter transformation before the Lookup transformation to pass rows into the dynamic cache that match the WHERE clause.
   Note: The session fails if you include large object ports in a WHERE clause.
6. Other. Use a lookup SQL override if you want to query lookup data from multiple lookups or if you want to modify the data queried from the lookup table before the Integration Service caches the lookup rows. For example, use TO_CHAR to convert dates to strings.

16. What are data driven sessions?

When you configure a session using update strategy, the session property data driven instructs Informatica server to use the instructions coded in mapping to flag the rows for insert, update, delete or reject. This is done by mentioning DD_UPDATE or DD_INSERT or DD_DELETE in the update strategy transformation.

“Treat source rows as” property in session is set to “Data Driven” by default when using a update strategy transformation in a mapping.

17. What are mapplets?

•A Mapplet is a reusable object that we create in the Mapplet Designer.•It contains a set of transformations and lets us reuse that transformation logic in multiple mappings.

18. What is the difference between Mapping and Mapplet?

19. How can we delete duplicate rows from flat files?

We can make use of sorter transformation and select distinct option to delete the duplicate rows.

20. What is the use of source qualifier?

The source qualifier transformation is an active, connected transformation used to represent the rows that the integrations service reads when it runs a session. You need to connect the source qualifier transformation to the relational or flat file definition in a mapping. The source qualifier transformation converts the source data types to the Informatica native data types. So, you should not alter the data types of the ports in the source qualifier transformation.

The source qualifier transformation can be used to perform the following tasks:

INTERVIEW QUESTIONS

1. Explain your Project?
2. What are your Daily routines?
3. How many mapping have you created all together in your project?
4. In which account does your Project Fall?
5. What is your Reporting Hierarchy?
6. How many Complex Mappings have you created? Could you please me the situation for which you have developed that Complex mapping?
7. What is your Involvement in Performance tuning of your Project?
8. What is the Schema of your Project? And why did you opt for that particular schema?
9. What are your Roles in this project?
10. Can I have one situation which you have adopted by which performance has improved dramatically?
11. Were you involved in more than two projects simultaneously?
12. Do you have any experience in the Production support?
13. What kinds of Testing have you done on your Project (Unit or Integration or System or UAT)? And Enhancement’s were done after testing?
14. How many Dimension Tables are there in your Project and how are they linked to the fact table?
15. How do we do the Fact Load?
16. How did you implement CDC in your project?
17. How does your Mapping in File to Load look like?
18. How does your Mapping in Load to Stage look like?
19. How does your Mapping in Stage to ODS look like?
20. What is the size of your Data warehouse?
21. What is your Daily feed size and weekly feed size?
22. Which Approach (Top down or Bottom Up) was used in building your project?
23. How do you access your source’s (are they Flat files or Relational)?
24. Have you developed any Stored Procedure or triggers in this project? How did you use them and in which situation?
25. Did your Project go live? What are the issues that you have faced while moving your project from the Test Environment to the Production Environment?
26. What is the biggest Challenge that you encountered in this project?
27. What is the scheduler tool you have used in this project? How did you schedule jobs using it?

DATA WAREHOUSING QUESTIONS

1. What is a data-warehouse?

2. What are Data Marts?

3. What is ER Diagram?

4. What is a Star Schema?

5. What is Dimensional Modeling?

6. What Snow Flake Schema?

7. What are the Different methods of loading Dimension tables?

8. What are Aggregate tables?

9. What is the Difference between OLTP and OLAP?

10. What is ETL?

11. What are the various ETL tools in the Market?

12. What are the various Reporting tools in the Market?

13. What is Fact table?

14. What is a dimension table?

15. What is a lookup table?

16. What is a general purpose scheduling tool? Name some of them?

17. What are modeling tools available in the Market? Name some of them?

18. What is real time data-warehousing?

19. What is data mining?

20. What is Normalization? First Normal Form, Second Normal Form, Third Normal Form?

21. What is ODS?

22. What type of Indexing mechanism do we need to use for a typical Data warehouse?

23. Which columns go to the fact table and which columns go the dimension table? (My user needs to see <data element<data element broken by <data element<data element>

All elements before broken = Fact Measures

All elements after broken = Dimension Elements

24. What is a level of Granularity of a fact table? What does this signify?(Weekly level summarization there is no need to have Invoice Number in the fact table anymore)

25. How are the Dimension tables designed? De-Normalized, Wide, Short, Use Surrogate Keys, Contain Additional date fields and flags.

26. What are slowly changing dimensions?

27. What are non-additive facts? (Inventory, Account balances in bank)

28. What are conformed dimensions?

29. What is VLDB? (Database is too large to back up in a time frame then it’s a VLDB)

30. What are SCD1, SCD2 and SCD3?

INFORMATICA EXPERIENCED INTERVIEW QUESTIONS

1. Difference between Informatica 8x and 9x?

2. Difference between connected and unconnected lookup transformation in Informatica?

3. Difference between stop and abort in Informatica?

4. Difference between Static and Dynamic caches?

5. What is Persistent Lookup cache? What is its significance?

6. Difference between and reusable transformation and mapplet?

7. How the Informatica server sorts the string values in Rank transformation?

8. Is sorter an active or passive transformation? When do we consider it to be active and passive?

9. Explain about Informatica server Architecture?

10. In update strategy Relational table or flat file which gives us more performance? Why?

11. What are the output files that the Informatica server creates during running a session?

12. Can you explain what error tables in Informatica are and how we do error handling in Informatica?

13. Difference between constraint base loading and target load plan?

14. Difference between IIF and DECODE function?

15. How to import oracle sequence into Informatica?

16. What is parameter file?

17. Difference between Normal load and Bulk load?

18. How u will create header and footer in target using Informatica?

19. What are the session parameters?

20. Where does Informatica store rejected data? How do we view them?

21. What is difference between partitioning of relational target and file targets?

22. What are mapping parameters and variables in which situation we can use them?

23. What do you mean by direct loading and Indirect loading in session properties?

24. How do we implement recovery strategy while running concurrent batches?

25. Explain the versioning concept in Informatica?

26. What is Data driven?

27. What is batch? Explain the types of the batches?

28. What are the types of Metadata repository stores?

29. Can you use the mapping parameters or variables created in one mapping into another mapping?

30. Why did we use stored procedure in our ETL Application?

31. When we can join tables at the Source qualifier itself, why do we go for joiner transformation?

32. What is the default join operation performed by the look up transformation?

33. What is hash table Informatica?

34. In a joiner transformation, you should specify the table with lesser rows as the master table. Why?

35. Difference between Cached lookup and Un-cached lookup?

36. Explain what DTM does when you start a work flow?

37. Explain what Load Manager does when you start a work flow?

38. In a Sequential batch how do I stop one particular session from running?

39. What are the types of the aggregations available in Informatica?

40. How do I create Indexes after the load process is done?

41. How do we improve the performance of the aggregator transformation?

42. What are the different types of the caches available in Informatica? Explain in detail?

43. What is polling?

44. What are the limitations of the joiner transformation?

45. What is Mapplet?

46. What are active and passive transformations?

47. What are the options in the target session of update strategy transformation?

48. What is a code page? Explain the types of the code pages?

49. What do you mean rank cache?

50. How can you delete duplicate rows without using Dynamic Lookup? Tell me any other ways using lookup   delete the duplicate rows?

51. Can u copy the session in to a different folder or repository?

52. What is tracing level and what are its types?

53. What is a command that used to run a batch?

54. What are the unsupported repository objects for a mapplet?

55. If your workflow is running slow, what is your approach towards performance tuning?

56. What are the types of mapping wizards available in Informatica?

57. After dragging the ports of three sources (Sql server, oracle, Informix) to a single source qualifier, can we map these three ports directly to target?

58. Why we use stored procedure transformation?

59. Which object is required by the debugger to create a valid debug session?

60. Can we use an active transformation after update strategy transformation?

61. Explain how we set the update strategy transformation at the mapping level and at the session level?

62. What is exact use of ‘Online’ and ‘Offline’ server connect Options while defining Work flow in Work flow monitor? The system hangs when ‘Online’ Server connect option. The Informatica is installed on a Personal laptop.

63. What is change data capture?

64. Write a session parameter file which will change the source and targets for every session i.e. different source and targets for each session run?

65. What are partition points?

66. What are the different threads in DTM process?

67. Can we do ranking on two ports? If yes explain how?

68. What is Transformation?

69. What does stored procedure transformation do in special as compared to other transformation?

70. How do you recognize whether the newly added rows got inserted or updated?

71. What is data cleansing?

72. My flat file’s size is 400 MB and I want to see the data inside the FF without opening it? How do I do that?

73. Difference between Filter and Router?

74. How do you handle the decimal places when you are importing the flat file?

75. What is the difference between $ & $$ in mapping or parameter file? In which case they are generally used?

76. While importing the relational source definition from database, what are the Metadata of source U import?

77. Difference between Power mart & Power Center?

78. What kinds of sources and of targets can be used in Informatica?

79. If a sequence generator (with increment of 1) is connected to (say) 3 targets and each target uses the NEXTVAL port, what value will each target get?

80. What do you mean by SQL override?

81. What is a shortcut in Informatica?

82. How does Informatica do variable initialization? Number/String/Date

83. How many different locks are available for repository objects?

84. What are the transformations that use cache for performance?

85. What is the use of Forward/Reject rows in Mapping?

86. How many ways you can filter the records?

87. How to delete duplicate records from source database/Flat Files? Can we use post Sql to delete these records. In case of flat file, how can you delete duplicates before it starts loading?

88. You are required to perform “bulk loading” using Informatica on Oracle, what action would perform at Informatica + Oracle level for a successful load?

89. What precautions do you need take when you use reusable Sequence generator transformation for concurrent sessions?

90. Is it possible negative increment in Sequence Generator? If yes, how would you accomplish it?

91. Which directory Informatica looks for parameter file and what happens if it is missing when start the session? Does session stop after it starts?

92. Informatica is complaining about the server could not be reached? What steps would you take?

93. You have more five mappings use the same lookup. How can you manage the lookup?

94. What will happen if you copy the mapping from one repository to another repository and if there is no identical source?

95. How can you limit number of running sessions in a workflow?

96. An Aggregate transformation has 4 ports (l sum (col 1), group by col 2, col3), which port should be the output?

97. What is a dynamic lookup and what is the significance of NewLookupRow? How will use them for rejecting duplicate records?

98. If you have more than one pipeline in your mapping how will change the order of load?

99. When you export a workflow from Repository Manager, what does this xml contain? Workflow only?

100. Your session failed and when you try to open a log file, it complains that the session details are not available. How would do trace the error? What log file would you seek for?

101. You want to attach a file as an email attachment from a particular directory using ‘email task’ in Informatica, How will you do it?

102. You have a requirement to alert you of any long running sessions in your workflow. How can you create a workflow that will send you email for sessions running more than 30 minutes. You can use any method, shell script, procedure or Informatica mapping or workflow control?