Top Informatica Interview questions

Welcome to the best collection of Informatica Interview Questions with detailed answers that you can rely on. Read and understand all the questions and their answers here .If you have any doubt in understanding any answer ,we will be happy to provide you help.

You can be assured of this series of interview questions will cover the most of questions you can expect in a informatica interview.

Question 1
What is the difference between connected and unconnected Lookup?

Connected Lookup	Unconnected Lookup
Connected lookup participates in dataflow and receives input directly from the pipeline	Unconnected lookup receives input values from the result of a LKP: expression in another transformation
Connected lookup can use both dynamic and static cache	Unconnected Lookup cache can NOT be dynamic
Connected lookup can return more than one column value ( output port )	Unconnected Lookup can return only one column value i.e. output port
Connected lookup caches all lookup columns	Unconnected lookup caches only the lookup output ports in the lookup conditions and the return port
Supports user-defined default values (i.e. value to return when lookup conditions are not satisfied)	Does not support user defined default values
More Visible	Less Visible in the flow

Question 2
How will you improve performance of  Aggregator Transformation?
Filter the unnecessary data before aggregating it. Place a Filter transformation in the mapping before the aggregator transformation to reduce unnecessary aggregation.Connect only the necessary input/output ports to following transformations, thereby reducing the size of the data cache.Use Sorted input which reduces the amount of data cached and improves session performance.

Question 3
How can you  delete duplicate record using Informatica Aggregator?
We can check the Group By checkbox on the ports having duplicate occurring data. we can have the choose  last or the first of the duplicate column value records.

Question 4
What is the sequence of execution  of the ports in an expression?
In expression transformation ports are executed Top to  Bottom in a physical ordering fashion, and it is done in the following groups:All input ports values  are pushed first.Second all variables are executed (top to bottom physical ordering in the expression).In the end  all output expressions are calculated to push values to output ports

Please refer to Informatica Port Ordering for details

Question 5 
How does Joiner transformation treat NULL value matching?
The Joiner transformation does not match null values.if we need to join rows with null values then we can replace null input with default values in the Ports tab of the joiner, and then join on the default values.

Question 6
What is the use of sorted input in joiner transformation?
Informatica  recommends  to join sorted data when possible. session performance can be improved by configuring the Joiner transformation to use sorted input. When we configure the Joiner transformation to use sorted data, it improves performance by minimizing disk input and output. It is more visible while working with large data sets.

Unsorted Joiner transformation - Assign master source the source with fewer rows when informatica runs a session, 

Sorted Joiner transformation --Assign source with less duplicate data ,the Joiner transformation compares each row of the master source against the detail source. The fewer unique rows in the master, the fewer iterations of the join comparison occur, which improves  the join process.

Question 7
What is the difference between Static and Dynamic Lookup Cache?
We can configure a Lookup transformation to cache the underlying lookup table. In case of static  lookup cache the Integration Service caches the lookup table at the start of the session and does not update the lookup cache while it processes the Lookup transformation. Rows are not added dynamically in the cache.

In case of dynamic lookup cache the Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is synchronized with the target. It caches the rows as and when it is passed.

Question 8 
How can we  return multiple port values from unconnected lookup in Informatica?
Informatica Unconnected Lookup by default supports only one return port. In order to return multiple ports we can write a Lookup SQL override with the required ports values concatenated into a single string as return port value.

Call the Unconnected lookup from the expression transformation and use various output ports to retrieve the lookup values based on the concatenated return value. We need to use SUBSTR, INSTR functions to extract the column values from the concatenated return field.

Question 9
Is Sorter an Active Transformation,If yes the why ?
Yes,Sorter is active transformatiom , You  can select the “distinct” option in the sorter property. 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 compared during the sort operation. The number of Input rows will differ as compared with the Output rows and hence it is an Active transformation.

Question 10
How can you update a record in target table without using Update strategy?
Target table can also be updated without using “Update Strategy”. To achieve this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the  Target. In the session level, we should set the target property as “Update as Update” and enable the “Update” checkbox.

Question 11
Can we have an Informatica mapping with two pipelines, where one flow is having a Transaction Control transformation and another not. Explain why?
No it is not feasible. Whenever we have a Transaction Control transformation in a mapping, the session  commit type is ‘User Defined’. Whereas for a pipeline without the Transaction Control transform, the session expects the commit type to be either Source based or Target based.Hence we cannot have both the pipelines in a single mapping; rather we have to develop single mappings for each of the pipelines. or have a dummy transaction control.

Question 12
How can you  send a session failure mail with the workflow or session log as attachment?
We need to design an Informatica email task to send email communication in the event of session failure and used email variable %g to attach the corresponding session log.
Email Variables:
(%g) - To attach session log.
(%a<>) - To attach any file, Absolute path needs to be given 

Question 13
How do you handle delimiter character which is coming as part of the data in a delimited source file?
To handle the same flat-files in Informatica,  we need to use the below options as per the data file format while defining the file structure.
1. Select Optional Quotes to Double or Single Quote. The column delimiters within the quote characters are ignored.
2. Escape Character used to escape the delimiter or quote character.
Escape character preceding the delimiter character in an unquoted string or the quote character in a quoted string is treated as regular character.

Question 14
What are the transformations which converts one to many rows i.e. increases the I/P: O/P row count. Also what is the name of its reverse transformation?
Normalizer and Router Transformations are two active transformations which can increase the number of input rows to output rows.
Aggregator Transformation performs the opposite action of Normalizer transformation.

Question 15
Name few  transformations that work with pushdown optimization to push logic to the database?
Aggregator, Expression, Filter, Joiner, Lookup, Router, Sequence Generator, Sorter, Source Qualifier, Target, Union, Update Strategy

Question 16
Explain the difference between stopping and aborting a workflow session task?
A stop command tells the Integration Service to stop reading session data, but will continue writing and committing data to targets.
A abort command works exactly like the stop command, however it will tell the Integration to stop processing and committing data to targets after 60 seconds. If all processes are not complete after this time out period, the session gets terminated.

Question 17
What factors play a part in determining a commit point?
1. Commit interval
2. Commit interval type
3. Size of the buffer blocks
Question 18
Describe three dynamic partitioning configurations that cause a session to run with one partition?
1. Setting dynamic partitioning to the number of nodes in the grid, and the session does not run on a grid.
2. You create a user-defined SQL statement or a user-defined source filter.
3. You use dynamic partitioning with an Application Source Qualifier

Question 19
Informatica  union transformation works as UNION ALL output, how can you  get the UNION output?
1. Pass the data from union transformation to sorter.
2. Enable distinct option in sorter transformation which eliminates the duplicate data.
3. Then connect it to the target transformation

Question 20
Describe 5 real time session failure scenarios and how did you solve them ?
Setting the session level property Stop on errors as 1. If exceeds the error records more than 1, the session get failed.
If you configure wrong relational connections in workflow manager then the session get failed.
If you enable bulk load without dropping indexes in Target table then the session get failed.
If you do not provide any mapping level variable initial value then the session get failed.
While sql override in source qualifier transformation, you miss any function in query, then mapping is valid but the session failed.
The target table has primary key, but you are loading duplicated data then session get failed.

Question 21 
Can we join two tables based on a join column having different data type?
For example table 1 CUSTID (string) and table 2 CUSTNUM (number)
Yes possible in this case. If we are using Joiner, we should be able to do this explicit conversion in an expression transformation before joining the tables.

Question 22
What are the changes we observe when we promote a non-reusable Sequence Generator to a reusable one? And what happens if we set the Number of Cached Values to 0 for a reusable transformation?
When we convert a non-reusable sequence generator to reusable one we observe that the Number of Cached Values is set to 1000 by default and the Reset property is disabled.When we try to set the Number of Cached Values property of a Reusable Sequence Generator to 0 in the Transformation Developer we encounter the following error message:The number of cached values must be greater than zero for reusable sequence transformation.

Question 23
What are the ways in informatica  to write flat file column names in target?
We have  two options available in session properties to take care of handle this requirement. To achieve this , Go to Mapping Tab Target Properties and you need to choose the header option as Output Field names OR Use Header Command output File.

Option 1, will create your output file with a header record and the column heading names will be same as your Target transformation port names.
Option 2, we can create our command to generate the header record text. We can use an 'echo' command here to get this created. Here is an example
echo '"Customer Id"|"Customer Name"'
We  recommend using the second option as it gives more flexibility for writing the column names.

Question 24
What are  the different dynamic partitioning configurations which can make a session to run with one partition?
1. When we  set dynamic partitioning equal to the number of nodes in the grid, and the session does not run on a grid.
2. We  create a user-defined SQL statement or a user-defined source filter.
3. We use dynamic partitioning with an Application Source Qualifier

Question 25
What is parallel processing or partitioning a session and what is benefit of it?
Partitioning a session means solo implementation sequences within the session.
Partitioning option will increase the performance through parallel data processing.
It will let you split the large data set into smaller subsets which can be processed in parallel to get a better session performance

Question 26
What are  the different dynamic partitioning configurations which can make a session to run with one partition
You set dynamic partitioning to the number of nodes in the grid, and the session does not run on a grid.
You create a user-defined SQL statement or a user-defined source filter.
You use dynamic partitioning with an Application Source Qualifier.

Question 27
Under which situations you  will use the Source Filter, Select Distinct and Number of Sorted Ports properties of Source Qualifier transformation.
Source Filter option is used basically to reduce the number of rows the Integration Service queries, so as to improve performance.
Select Distinct option is used when we want the Integration Service to select unique values from a source filtering out unnecessary data earlier in the data flow, will improve performance.
Number Of Sorted Ports option is used when we want the source data to be in a sorted fashion, so as to use the same in some following transformations like Aggregator or Joiner, those when configured for sorted input will improve the performance.

Question 28
What is Persistent Lookup Cache?
If the cache generated for a Lookup needs to be preserved for subsequent use then persistent cache is used.It will not delete the index and data files. It is useful only if the lookup table remains constant. Lookups are cached by default in Informatica. Lookup cache can be either non-persistent or persistent. The Integration Service saves or deletes lookup cache files after a successful session run based on, whether the Lookup cache is checked as persistent or not.

Question 29
What are the restrictions of Union Transformation?
All input groups and the output group must have matching ports. The precision, data type, and scale must be identical across all groups.
We can create multiple input groups, but only one default output group.
The Union transformation does not remove duplicate rows.
We cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.
The Union transformation does not generate transactions 

Question 30 How can we load particular number of records records  out of ‘N’ records from source dynamically, without using filter and sequence generator transformation? 
Take a mapping parameter say $$COUNTNT to pass the number of records we want to load dynamically by changing in the parameter file each time before session run.Next after the Source Qualifier use an Expression transformation and create one output port say CNTR with value CUME (1).Next use an Update Strategy with condition IIF ($$COUNT >= CNTR, DD_INSERT, DD_REJECT).

Question 31
Is it possible to have a mapplet within a mapplet and worklet within a worklet? 
Informatica does not support mapplet within a mapplet transformation but it supports worklet within a worklet.

Question 32 What is default block buffer size? 
64K

Question 33 What happens to a mapping if we alter the data types between Source and its corresponding Source Qualifier? 
The Source Qualifier transformation displays the Informatica data types. The transformation data types determine how the source database binds data when the Integration Service reads it.Now if we alter the data types in the Source Qualifier transformation or the data types in the Source definition and Source Qualifier transformation do not match, the Designer marks the mapping as invalid when we save the mapping.

Question 34 How to make the persistent lookup cache in sync with lookup table? 
To make the persistent cache in sync with the lookup table simply enable Re-cache option of the lookup transformation to rebuild the lookup cache from lookup table again.

Question 35 Will session fail if the SELECT list COLUMNS in the Custom override SQL Query and the Output Ports order in Source Qualifier transformation do not match? 
Mismatch or changing the order of the list of selected columns in the SQL Query override of Source Qualifier to that of the connected transformation output ports may result is unexpected value result for ports if data types matches by chance, else will lead to session failure. 
Question 36 Can we use mapping/workflow variables in standalone email task? 
No, we can only use pre-defined built in variables

Question 37
What are the characteristics of active transformation? 
A transformation is active when it satisfies either of the following 4 conditions: 
1. No of rows @Source is not equal to No of rows @Target. 
2. Transactions boundaries for the input change. 
3. Row type changes. 
4. It changes the order of data

Question 38 Can we use $PMTargetName@numAffectedRows/@numAppliedRows/$PMTargetName@numRejectedRows in post session variable assignment? 
We cant use these variable in post session variable assignment but we can use these variable in post session command tasks

Question 39 Can we connect to Microsoft SQL Server database with Kerberos authentication from Informatica? 
Yes we can connect to Microsoft SQL Server database with Kerberos authentication from Informatica 10 ,It requires few configuration changes

Question 40 What is informatica domain ? 
When you install and run the Informatica services, the installation is known as a node. The node becomes part of an Informatica domain. A domain is a grouping of one or more nodes. The domain forms the environment upon which the Informatica service processes run. A gateway node can also be a master gateway node.

Question 41 What is informatica EBF ? 
Bug Fix

Question 42 What is purpose of  option Synchronize Dynamic Cache in lookup? 
It can be used when running multiple sessions updating the same target simultaneously to avoid integrity issue.This synchronization behavior is different only in case of insert as compared to Dynamic cache.For update it is same as Dynamic cache that is when rows marked for update are received the dynamic cache will be updated and NewLookupRow= 2.

Question 43 How can you limit use of parameter files in informatica ? 
It can be done by defining parameters in metadata table and then create a mapping to assign variable using setvariable and then these values can be passed to different session using post session assignment .

Question 44 How can you issue Operating system command in middle of mapping? 
This can be achieved by using java transformation and using process builder java built in

Question 45 
How does Sorted Input improves  Aggregator Transformation Performance?
Integration Service creates the index and data caches files in memory to process the Aggregator transformation. If the Integration Service requires more space as allocated for the index and data cache sizes in the transformation properties, it stores overflow values in cache files i.e. paging to disk.One way to increase session performance is to increase the index and data cache sizes in the transformation properties.But when we check Sorted Input the Integration Service uses memory to process an Aggregator transformation it does not use cache files.

Question 46 
Can we call Stored Procedure in Source Qualifier query? 
It is possible to use a stored procedure as a source as long as it returns a result set. The result set returned to the PowerCenter by Microsoft SQL Server stored procedures are the last select statement executed in the procedure source code.Stored procedure of SQL server called directly in SQL Override at SQ transformation. It is not possible to call a stored procedure of Oracle in the Source Qualifier SQL override. When you call the stored procedure a variable needs to be passed to collect the OUT TYPE of REF CURSOR,which is not possible to do in a Source Qualifier.A workaround is possible for oracle stored procedure using pre session task.

Question 47 
What is the difference between $ and $$ 
$ represents session parameter/variable, you can declare this in the session level $$ represents mapping parameter/variable, you can declare this under mapping designer tool

Question 48 What is the priority in source qualifier if we give filter condition (EMPNAME =10) and also sql override (EMPNO=20) 
If we double click on source qualifier, we can see both the properties filter condition and sql override. The highest priority is sql override, it takes the condition EMPNO=20. If we don't provide sql override then it will take value from the filter condition.

Question 49 
Can we connect more than one source to a single source qualifier? 
We can connect more than one source to a single source qualifier. When you drag multiple sources,for each source you can see one source qualifier, you need manually delete all source qualifier except one and then all other sources ports you can connect to one source qualifier

Question 50 What is impacted mapping? 
If any of the components in the mapping changed, example sources or targets or reusable transformations or mapplets then mapping becomes impacted. It gives in yellow color triangle symbol. We have to validate the mapping to disappear impacted symbol.

Question 51 
What types of real-time data can be processed with Informatica PowerCenter?
1. Messages and message queues. Examples include WebSphere MQ, JMS, MSMQ, SAP, TIBCO, and webMethods sources. 
2. Web service messages. Example includes receiving a message from a web service client through the Web Services Hub. 
3. Change data from PowerExchange change data capture source

Question  52 What is the use of Shared Folder? 
Shared folders allow users to create shortcuts to objects in the folder.If you have an object that you want to use in several mappings or across multiple folders, we can place the object in a shared folder.we can then access the object from other folders by creating a shortcut to the object. Shortcuts inherit changes to their shared object. Shared folders in global repositories can be used by any folder in the domain Once you make a folder shared, you cannot reverse it.

Question 53 What are the different ways to migrate from one environment to another in Informatica? 
1.We can export repository and import into the new environment 
2. We can use informatica deployment groups 
3. We can Copy folders/objects 
4. We can Export each mapping to xml and import in new environment

Question 54 What is difference between mapping parameter and variable ?
A Mapping parameter is a static value that you define before running the session and it value remains till the end of the session.when we run the session PowerCenter evaluates the value from the parameter and retains the same value throughout the session. When the session run again it reads from the file for its value. A Mapping variable is dynamic or changes anytime during the session. PowerCenter reads the intial value of the variable before the start of the session and changes its value by using variable functions and before ending the session its saves the current value (last value held by the variable). Next time when the session runs the variable value is the last saved value in the previous session.

Question 55 What is DTM?
 DTM (Data Transformation Manager) is the process associated with the session taskThe Load Manager creates one DTM process for each session in the workflow. The DTM process performs the following tasks:

1.Reads session information from the repository. 
2.Expands the server, session, and mapping variables and parameters. 
3.Creates the session log file. Validates source and target code pages. 
4.Verifies connection object permissions. 
5.Runs pre-session shell commands, stored procedures and SQL. 
6.Creates and runs mapping, reader, writer, and transformation threads to extract, transform, and load data.
7. Runs post-session stored procedures, SQL, and shell commands. Sends post-session email.

Question 56 How to delete duplicate record in Informatica? 
Following are ways to remove duplicate records 
1. In source qualifier use select distinct
2. Use Aggregator and group by all fields 
3. Override SQL query in Source qualifier

Question 57 What are different type of repositories that can be created using Informatica Repository Manager? 
1. Standalone Repository : A repository which functions individually and is unrelated to any other repositories. 
2. Global Repository : This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts. 
3. Local Repository : Local repository is within a domain . Local repository can connect to a global repository using global shortcuts and can use objects in it’s shared folders.

Question 58 How to find all invalid mappings in a folder? 
Use following query SELECT MAPPING_NAME FROM REP_ALL_MAPPINGS WHERE SUBJECT_AREA='YOUR_FOLDER_NAME' AND PARENT_MAPPING_IS_VALIED 

Question 59 What are the data movement modes in informatica? 
Data movement modes determines how power center server handles the character data. We choose the data movement in the informatica server configuration settings. 
Two types of data movement modes available in informatica. 
1.ASCII mode 2.Unicode mode.

Question 60 What are Limitations on joiner transformation ? 
1.Both pipelines begin with the same original data source. 
2.Both input pipelines originate from the same Source Qualifier transformation. 
3.Both input pipelines originate from the same Normalizer transformation. 
4.Both input pipelines originate from the same Joiner transformation.
5.Either input pipelines contains an Update Strategy transformation. 
6.Either input pipelines contains a Sequence Generator transformation.

Question 61 
What will happen if we connect only current value port from seq generator to next transformation (without connecting nextval) 
Each target will get the value 1.

Question 62
Can we merge two target files of same structure  in session/mapping ?
We can achieve this by using below method 

1. Make sure that at the session level both the targets are having the same name.
2. Choose Merge Type as sequential merge for both targets
3. For second target (as per  Target load plan at the designer ), select button "Append if Exists".

Question 63 

Can we turn columns into rows ? If yes then what are different methods available and what are pros and cons of each

Yes ,We can achieve this is using below transformations
1.       Router Transformation
2.       Normalizer Transformation        
3.       Java Transformation
Java Transformation is most flexible  as it can handle any number of fields.

Please refer to article Columns to rows for details

Question 64
How will you make sure data is not duplicated in the target when the source has duplicate records, using lookup transformation?
Using Dynamic lookup cache we can ensure duplicate records are not inserted in the target. That is through Using Dynamic Lookup Cache of the target table and associating the input ports with the lookup port and checking the Insert Else Update option will help to eliminate the duplicate records in source and hence loading unique records in the target.

Question 65
What are the three areas where the rows can be flagged for particular operation?
In Mapping – Update Strategy
In Session - Treat Source Rows As

In Session - Target Insert / Update / Delete Options.

Question 66
How can you find a history / metrics f the load sessions that have occurred in Informatica?
The tables which house this information are OPB_LOAD_SESSION, OPB_SESSION_LOG, and OPB_SESS_TARG_LOG. OPB_LOAD_SESSION contains the single session entries, OPB_SESSION_LOG contains a historical log of all session runs that have taken place. OPB_SESS_TARG_LOG keeps track of the errors, and the target tables which have been loaded. Keep in mind these tables are tied together by Session_ID. If a session is deleted from OPB_LOAD_SESSION, it's history is not necessarily deleted from OPB_SESSION_LOG,nor from OPB_SESS_TARG_LOG. Unfortunately - this leaves un-identified session ID's in these tables. How ever, when you can join them together, you can get the start and complete times from each session.


Keep an eye on this page we will come with more and more Informatica Interview Questions

Please follow next set of questions at More Informatica Interview Questions 25-50

We will be adding more and more informatica interview questions soon