Informatica Interview Questions part 1

What is a source qualifier?
Source qualifier represents the data that the informatica server reads from the source.

What is a surrogate key?

What is difference between Mapplet and reusable transformation?

What is DTM session?

What is a Mapplet?
What is up date strategy and what are the options for update strategy?

What is subject area?

What is the difference between truncate and delete statements?
Truncate is a type of DDL. No commit is needed. It resets the HWM and cleans up all the storage segments. No rollback is possible because of implicit commit. Delete can be rolled back, does not clean up the data segments so the HWM stays intact. Less efficient.

What kind of Update strategies are normally used (Type 1, 2 & 3) & what are the differences?
Type 1 – DD_UPDATE,
Type 3 – DD_INSERT.

 What are bitmap indexes and how and why are they used?

What is bulk bind? How does it improve performance?
Bulk bind is used enhance the performance of PL/SQL fetch engine. It binds a variable so that the fetches are in bulk instead of streams of one row at a time.

What are the different ways to filter rows using Informatica transformations?
Source Qualifier (if using a homogeneous database or if the tables are in the same instance),Joiner, Filter, Router.

What is target load order?
 You specify in Informatica which target instance gets loaded first or in order.

What are the different transformations where you can use a SQL override?
Source Qualifier(If Relational)/Lookup (If Relational)

What is the difference between a Bulk and Normal mode and where exactly is it defined?

What are data driven sessions?
What are worklets and what is their use?
 Sessions can be bundled up together logically by subject area so that it can go in the workflow as one logical piece.

What is change data capture?
Change Data Capture is a mechanism of capturing changed data since the last load. Striva in mainframes. It involves reading database log files. Subscriber/Prescriber views. Triggers/Date fields in oracle.

What exactly is tracing level?
Tracing level is defined as amount of information which will go in Informatica log files.

How do you validate all the mappings in the repository at once?
In repository manager. Select all the then validate.

How can you join two or more tables without using the source qualifier override SQL or a Joiner transformation?
Using a view also you can use lookup(which is nothing but a outer join).

How many repositories can be created in Informatica?
N(Depends on your licensing agreement with Informatica)

How can you improve performance in an Aggregator transformation?
By checking sorted input and passing a sorted data through it.

How does the Informatica know that the input is sorted?     
Check the sorted port in Source Qualifier.

How many worklets can be defined within a workflow?

If you join two or more tables and then pull out about two columns from each table into the source qualifier and then just pull out one column from the source qualifier into  an Expression transformation and then do a ‘generate SQL’ in the source qualifier how many columns will show up in the generated SQL.

In a Type 1 mapping with one source and one target table what is the minimum number of update strategy transformations to be used?

For joining three heterogeneous tables how many joiner transformations are required?
Thumb rule for and SQL joins and Informatica joiners. If you want to join n tables minimum number of joins you need is n-1.

While importing the relational source definition from database, what
are the Meta data of source you    import?
Source name, Database location, Column names, Data types, and Key constraints

How many ways you can update a relational source definition and what are they?
   1. Edit the definition
   2. Reimport the definition

Where should you place the flat file to import the flat file definition to the designer?
Place it in local folder

To provide support for Main frames source data, which files r used as a source  definitions?
COBOL files

Which transformation do you need while using the Cobol sources as source definitions?
Normalizer transformation which is  used to normalize the data.
Since Cobol sources often consist of De-normalized data.

How can you create or import flat file definition in to the warehouse designer?
You cannot create or import flat file definition into warehouse designer directly.
Instead you must analyze the file in source analyzer, then drag it into the warehouse designer.
When you drag the flat file source definition into warehouse designer  work space, the
warehouse designer creates a relational target definition not a file definition.
If you want to load to a file, configure the session to write to a flat file.
When the Informatica server runs the session, it creates and loads  the flat file.

What is the Mapplet?

What is a Transformation?
It is a repository object that generates, modifies or passes data.

What are the designer tools for creating transformations?
Mapping designer, Transformation developer, Mapplet designer

What are the active and passive transformations?

What are the connected or unconnected transformations?

In how many ways can you create ports?
1.Drag the port from another transformation
2.Click the add button on the ports tab.

What are the reusable transformations?

 What are the methods for creating reusable transformations?
 Two methods
         1.Design it in the transformation developer.
         2.Promote a standard transformation from the mapping designer. After you add a Transformation to the mapping, you can promote it to the status of reusable  Transformation. Once you promote a standard transformation to reusable status,you can demote it to a    Standard transformation at any time.  If you change the properties of a reusable transformation in  mapping, you can revert it to the original reusable transformation properties by clicking the revert button.

What are the unsupported repository objects for a mapplet?
          COBOL source definition
          Joiner transformations
          Normalizer transformations
          Non reusable sequence generator transformations.
          Pre or post session stored procedures
          Target definitions
          Power mart 3.5 style Look Up functions
          XML source definitions
         IBM MQ source defintions

What are the mapping parameters and mapping variables?

Can you use the mapping parameters or variables created in one mapping into another  mapping?
No. We can use mapping parameters or variables in any transformation of the same mapping or mapplet in which you have created mapping parameters or variables.

Can you use the  mapping parameters or variables created in one mapping into any other reusable transformation?
Yes. Because reusable tranformation is not contained with any mapplet or mapping.

How can U improve session performance in aggregator transformation?
Use sorted input.

What  is aggregate cache in aggregator transformation?

What are the difference between joiner transformation and source qualifier transformation?

In which conditions we cannot use joiner transformation (Limitations of joiner transformation)
Both pipelines begin with the same original data source.
Both input pipelines originate from the same Source Qualifier transformation.
Both input pipelines originate from the same Normalizer transformation.
Both input pipelines originate from the same Joiner transformation.
Either input pipelines contains an Update Strategy transformation.
Either input pipelines contains a connected or unconnected Sequence
Generator transformation.

What are the settings that you use to configure the joiner transformation?
 Master and detail source, Type of join and Condition of the join

What are the join types in joiner transformation?
Normal (Default), Master outer, Detail outer, full outer
Master Outer :All rows from Detail and only matching rows from Master
Detail outer   :All rows from Master and only matching rows from Detail.
Full outer join: keeps all rows of data from both the master and detail sources

What are the joiner caches?
When a Joiner transformation occurs in a session, the Informatica Server reads all the records from the master source and builds index and data caches based on the master rows.After building the caches, the Joiner transformation reads records from the detail source and perform joins.

What is the look up transformation?

Why use the lookup transformation ?
Get a related value. For example, if your source table includes employee ID, but you want to include the employee name in your target table to make your summary data  Easier to read. Perform a calculation. Many normalized tables include values used in a Calculation, such as gross sales per invoice or sales tax, but not the calculated value  (such as net sales).
Update slowly changing dimension tables. You can use a Lookup transformation to Determine whether records already exist in the target.

What are the types of lookup?
Connected and unconnected

Differences between connected and unconnected lookup?
What is meant by lookup caches?

What are the types of lookup caches?

Difference between uncached, static cache and dynamic cache
How the informatica server sorts the string values in Rank transformation?
When the informatica server runs in the ASCII data movement mode it sorts session data using  Binary sort order.If U configure the session use a binary sort order, the informatica server calculates the binary value of each string and returns the specified Number of rows with the highest binary values for the string.

What are the rank caches?
During the session ,the informatica server compares an in out row with rows in the data cache. If the input row out-ranks a stored row, the informatica  server replaces the  stored row with the input row. The informatica server stores group information in an index cache and row data in a data cache.

What is the Rank index in Rank transformation?

What is the Router transformation?

What are the types of groups in Router transformation?   
Input group, Output group
The designer copies property information from the input ports of the input group to create a set  of output ports for each output group.
     Two types of output groups
    User defined groups
    Default group
    you can not modify or delete default groups.

Can you look up a flat file using Informatica?

While running a session what default files are created?
 Default files are. Log files and Bad files. (For other files like err, target files you have let the Informatica know.

Describe the use of Materialized views and how are they different from a normal view.
 Normal View is not a physical object. It is created at runtime by the select statement. Materialized View is a physical object with real data. It has several use. For more details look into Oracle documentation. It is a major performance booster when you need summary,aggregated or complex joins to be calculated. It pre-calculates it and is ready for use when you need them and it is really fast. Be careful to choose the mechanism you want to refresh it.


  1. Do You Know How To Integrate The Data By Using Informatica ETL Tool?, Learn at

  2. This is the information that I was looking for.. Thanks for the efforts you put to gather such a nice content and posted here.
    Informatica training | Microsoft Dynamics CRM Training

  3. Thanks for Sharing.This is Verry usefull site.
    Hamsini Technologies is One of the Best Institute to Provide Online IT Trainings.
    We have Real-time Industry experts to provide Online,Classroom & corporate Trainings around the world.

    SAP FICO Online training

    Contact us for more details .

    SAP FICO Online training

  4. serve with the best data analysis with question on informatica you can follow us at :-For all students and individuals eager to learn informatica scenario based questions more about the data warehouse interview questions topic can easily go through the website and face all possible interview questions.

  5. Informatica is one of the most trusted and effective ETL software which is known globally. Informatica is one data warehousing ETL tool. Informatica ha very go opportuities for job across the world.
    informatica training in chennai | informatica training institute in Chennai | informatica classes in Chennai

  6. Very nice collection of information thank you for sharing this info. Know more about OBIEE Online Training

  7. your interview questions are really awesome and i have done got very useful information.This blog is really nice and interesting too.

    Best Informatica Training Institute in Chennai

  8. With this Informatica interview questions, we built strong knowledge on informatica. The Blogger mentioned all important questions of infomatica interview point of view. Informatica beginners set their platforms with this questions Informatica Online Training in Hyderabad
    Informatica Online Training in USA
    Informatica Online Training in INDIA

  9. wow really nice by posting questions with Informatica. It will be helpful for the people those who are ready to crack the interview and please also for remind what they have learned throughout concept.

    For more interview aptitude question you may refer with

    If you are making to try with the foremost recent techniques with Informatica can make use of our Informatica Training in Chennai

  10. I’ve been browsing on-line greater than three hours today, but I never discovered any attention-grabbing article like yours. It is beautiful worth sufficient for me. Personally, if all webmasters and bloggers made good content material as you did, the net will be a lot more helpful than ever before.

    Informatica Training in Chennai

  11. Thank you for your great information on informatica online trainings.
    Informatica Online Course!

  12. Very excellent post for more post please visit below for information
    Informatica Online Training

  13. Great and really helpful article! Adding to the conversation, providing more information, or expressing a new point of view...Nice information and updates. Really i like it and everyday am visiting your site..

    Informatica Training in Chennai

    Dataware Housing Training in Chennai

  14. Somebody necessarily help to make severely posts I might state. This is the first time I frequented your website page and to this point? I surprised with the research you made to create this particular post extraordinary. Well done admin..
    Oracle Training in Chennai

  15. Really Good blog post.provided a helpful information.I hope that you will post more updates like this.
    Digital marketing company in Chennai

  16. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    SEO Company in India

  17. you’re getting your information, but good topic. I need to spend some time learning more or understanding more. Thanks for fantastic info I was looking for this information for my mission.

    SAP Training in Chennai

    SAP ABAP Training in Chennai

  18. Really i enjoyed very much. And this may helpful for lot of peoples. So you are provided such a nice and great article within this.

    Dataware Housing Training in Chennai | Hadoop Training in Chennai

  19. your blog is very useful for my oracle business development.keep more updates.
    Informatica Training in Chennai

  20. Very impressive Informatica interview questions. I expect you to update more current informations.

    Informatica Training in Chennai


Related Posts Plugin for WordPress, Blogger...