Informatica interview questions & FAQs

What is a source qualifier?
What is a surrogate key?
What is difference between Mapplet and reusable transformation?
What is DTM session?
What is a Mapplet?
What is a look up function? What is default transformation for the look up function?
What is difference between a connected look up and unconnected look up?
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?
What kind of Update strategies are normally used (Type 1, 2 & 3) & what are the differences?
What is the exact syntax of an update strategy?
What are bitmap indexes and how and why are they used?
What is bulk bind? How does it improve performance?
What are the different ways to filter rows using Informatica transformations?
What is referential Integrity error? How do you rectify it?
What is DTM process?
What is target load order?
What exactly is a shortcut and how do you use it?
What is a shared folder?
What are the different transformations where you can use a SQL override?
What is the difference between a Bulk and Normal mode and where exactly is it defined?
What is the difference between Local & Global repository?
What are data driven sessions?
What are the common errors while running a Informatica session?
What are worklets and what is their use?
What is change data capture?
What exactly is tracing level?
What is the difference between constraints based load ordering and target load plan?
What is a deployment group and what is its use?
When and how a partition is defined using Informatica?
How do you improve performance in an Update strategy?
How do you validate all the mappings in the repository at once?
How can you join two or more tables without using the source qualifier override SQL or a Joiner transformation?
How can you define a transformation? What are different types of transformations in Informatica?
How many repositories can be created in Informatica?
How many minimum groups can be defined in a Router transformation?
How do you define partitions in Informatica?
How can you improve performance in an Aggregator transformation?
How does the Informatica know that the input is sorted?
How many worklets can be defined within a workflow?
How do you define a parameter file? Give an example of its use.
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?
At what levels can you define parameter files and what is the order?
In a session log file where can you find the reader and the writer details?
For joining three heterogeneous tables how many joiner transformations are required?
Can you look up a flat file using Informatica?
While running a session what default files are created?
Describe the use of Materialized views and how are they different from a normal view.

Contributed by Mukherjee, Saibal (ETL Consultant)

Many readers are asking “Where’s the answer?” Well it will take some time before I get time to write it… But there is no reason to get upset… The informatica help files should have all of these answers! :-)

52 Responses to “Informatica interview questions & FAQs”

  1. goutham says:

    hi…its not ok…i require more abt this interview process

  2. Divya says:

    can i have informatica help file with informatica being installed.

  3. kanchana says:

    i am interested in informatica. can i get informatica help with out being installed

  4. goutham says:

    hi,
    is it true that in interview unix shell prog is given equally imp for informatica consultants

  5. Administrator says:

    Not necessarily. However it always helps. Most of the time you will find one or two programmer who has knowledge of UNIX scripting. Sometime the ETL Lead will know UNIX and he will take care of the shell scripts.

    Shell scripting is the easy. Don’t try to avoid it. It will help you a lot in ETL. Ultimately lots of file handling or binding multiple ETL process is done by shell scripts. Given a choice between a consultant who knows shell scripting and the one who does not; the choice is clear 🙂

  6. deepti says:

    very useful info especially for people new to informatica… thanks!!

  7. goutham says:

    hi,
    when can i expect the answers for the above questions..
    what is the package for informatica-consultants with 2yrs exp..

  8. Vinay says:

    I am glad to hear that a book on ETL is on its way to reach readers and feel proud that it is written by an Indian

    Will it focus on general etl concepts or informatica tool etl concepts in broad sense

    Try this query in google search :

    +(“index of”) +(“/ebooks”|”/book”) +(chm|pdf|zip|rar) +ETL

    to find a particular eBook file

    and the best ebook search engine i know is http://www.bookilook.com, giuciao.com

    I am vinay from hyderabad,finished Masters degree and training in Informatica and now searching for job in informatica domain.

    one more suggestion ,it is better to have forum in this website where all the discussion and queries will be at one place visible only for members who are registered and the membership must be free because i strictly feel Knowledge sharing must be free of cost

    thank you,
    Wishing you All the Best
    Vinay

  9. prasad says:

    1) Source qualifier represents the data that the informatica server reads from the source.

    2) Surrogate is an artificial key it is used as a substitution for natural primary key in order to provide data consistency.

    3) Mapplet contains more than one transormation and is a transformation logic that can be reused in multiple mappings.

    Reusable transformation is a single transformation that can be called from other transormations in the mapping.

  10. Nike says:

    Hi ,,

    Now I.m studying data warehouse.. Where i can i get some manuels or help file to study Informatca 7.1 …

  11. jakkani says:

    guyz i’ve taken data warehousing coaching recently..i need a buk which explains eac and every thing about ETL process

  12. sridhar says:

    what are confirmed dimensions ? give examples.

  13. shashi says:

    hi,
    now,I am attending interviews on informatica.so i want all the faqs with answers

  14. khalandar says:

    Hi

    i need a faq on informatica, if any have it pls forward to my mail khalandar.n@gmail.com thank you

  15. nagaraju says:

    i need a faq on informatica if any have it pls forward to my mail id
    nagaraju_etl@yahoo.co.in

  16. ramagiricnu says:

    how to populate the date dimension in informatica

  17. Akhil says:

    Hi Guys,

    I have completed trainning on Informatica.I want all FAQs regarding Informatica with answer.

    Thanks.

  18. Akhil says:

    Hi,
    I am MCA 2007 passout and i have done trinning on Informatica..I am fresher for ETL(Informatica),Can some body tell me why there is no job for fresher on ETL.Every company required at least 1yrs of experience.
    Could somebody tell me how to show Informatica trainning in my Resume.

  19. indrasenareddy says:

    thx but here is not answers … pls send my mails ..ok

  20. Sanjay Prasad says:

    I have tried my best to answer the question posted by Mr. Saibal Mukherjee.The answers are based on my experience. I am free for any discussion about my answers. If anybody else has any general Question or interview question about Informatica/Oracle/UNIX and Data Warehousing in general post it. I will try to answer it to the best of my knowledge.

    I will start from Question no. 4.

    4) DTM – Stands for Data Transformation Manager. This is a unix process /windows service which runs on Informatica server and does the actual processing of the ETL code.
    5)Mapplet is like function we use in programming language. This is used for re-usable code where we can pass certain value and get back. It can be used in desigining the mapping to get a modular/manageble/scalable code.
    6)Look at the Help file for connected and unconnected lookup. Historically this is the most widely asked question in any Informatica interview.
    7)Update Strategy is a transformation which flags row for update/insert/deletes. Technically update/insert/deletes can also be achived without using this transformation by configuring it in the session.
    Options : DD_INSERT (1) DD_UPDATE (2) DD_DELETE(3)
    8)Subject Area is generally a set of data organized to reflect a specific are of business such as finance or sales.
    9)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.
    10)Type 1 – DD_UPDATE,
    Type 2 – DD_UPDATE/DD_INSERT
    Type 3 – DD_INSERT.
    11)Look in the help files for syntax. ( I don’t know why the poster of the question is latched up with Update Strategy transformation. He must have got burnt in some of this projects.)
    12) Bitmap indexes are used to enhance the join performance in STAT design. Don’t go by the myth of cardinality(Bitmaps are used on for lower cardinality.). It is a major component to enhance the joins between facts and dimension. It becomes more important if you are using Oracle propriety star_transformation_enabled=TRUE parameter in init.ora file.
    13)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.
    14)Source Qualifier (if using a homogeneous database or if the tables are in the same instance),Joiner, Filter, Router.
    15)Look at the basic database book to find about referential integrity. You rectify it by loading the parent first and make sure that child record is not a orphan i.e. it has a valid parent record.
    16) You specify in Informatica which target instance gets loaded first or in order.
    17)Shortcuts are easy way to manage Informatica code. It is a reference to a common object in the shared folder. It is a instantiation of informatica objects. Which can be used/re-used. If the object changes it is changed at once plane. If you use copy you have to change at multiple places.
    18) Lookup Ans 4.
    19) Shared folder is a folder which is shared by everybody and contains the object on which shortcuts are created.
    20)Source Qualifier(If Relational)/Lookup (If Relational)
    21)Bulk load is used to load records in bulk to enhance performance. It bypasses all the database level logging. It can only be used when no index is defined on the table. It is a file level operation. The records are directly written at the datafiles. Normal load is a regular insert we know about. With logging, with indexes, slower and can be rolled back. In informatica it is defined at the session level.
    22)Local is all we know about. Global repository holds all the enterprise level common objects which can be re-used.
    23)Data driven sessions are those session which uses Update Strategy transformation or session are used to flag the rows for insert/update/delete. (Again a Update strategy question).
    24) To see the common error. Just run the session, it will fail first time. That is common for you.If it runs for successfully first time. There must be something wrong.
    25) Sessions can be bundled up together logically by subject area so that it can go in the workflow as one logical piece.
    26)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.
    27)Tracing level is defined as amount of information which will go in Informatica log files.
    28)Target load order is used implement constraint based loading.(Load parent before child.)
    29)Deployment Group – See the Informatica help files. It is used for code migration.
    30)Partition – When—to increase performance in big data loads and lot of cpu. How – in session. ( I am not a big fan of this. I am yet to see some substantial gains of parallel processing in Informatica. I have seen ab-initio so that may be the reason I don’t appreciate Informatica partitioning.)
    31)Performance of a Update Strategy ???? This is a fluff question. Update Strategy does not use any cache so it is just like expression transformation. How can you enhance performance of expression? (Again a Update strategy question.)
    32)In repository manager. Select all the then validate.
    33)Using a view also you can use lookup(which is nothing but a outer join).
    34)See Informatica help.
    35)N(Depends on your licensing agreement with Informatica)
    36)2
    37)In the session. For details see the help files.
    38)By checking sorted input and passing a sorted data through it.
    39)Check the sorted port in Source Qualifier.
    40)N.
    41)Question is not clear. You don’t define a parameter file. You create a file and tell the session to use it by passing its name in pmcmd or workflow or at session level.
    42)One
    43)One.(Again a Update Strategy).
    44)Question is not clear. What types of details specifically Mr. Informatica consultant is looking for.8.5 has changed the way you look at the log details.
    45) 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.
    46) Yes.
    47) Default files are. Log files and Bad files. (For other files like err, target files you have let the Informatica know.
    48) 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.

    -Sanjay Prasad

  21. Sanjay Prasad says:

    I have a question for the Moderator of this site.
    Why my post is showing as “Your comment is awaiting moderation” from last 10 days ? I don’t think anybody should have any doubts about any of the answers I posted.

    -Sanjay

  22. uma says:

    Thanks sanjay.I need answer for 2nd question

  23. Srinath Vaddepalli says:

    In a datawarehousing world, we extract the data from multiple heterogenous sources. We cannot completely rely on any of it’s business key as a primary key. Eventhough a record can be uniquely identifiable with business key, still we load the data with a new sequnce generated value as a primary key for the record. This key is considered to be a surrogate key.

  24. Rabikant Thakur says:

    How we can load the data in two target on the two different server by informatica?

  25. Mahalingam says:

    I have modified the base mapping after that, how can i refresh the short cut mapping?

  26. prem says:

    Hi sir/madam,

    You provided this questions are very good, but I need to know answers of these questions.

    So Please send these interview questions and answers to my mail.

    am waiting for your reply sir/mam.

  27. vganesh says:

    it’s good.

  28. Mahan says:

    How can you define a transformation? What are different types of transformations in Informatica?

    Transformation is a process of changing the source data in a m a manner required for target format or change rules as per business requirements. We use different transformation objects available in Informatica to achieve this. Ie the ETL process flow. The various transformations used in Informatica are. 1. Aggregator. 2. Source Qualifier Application 3. custom transformation 4. data masking 5. Expression 6. External Procedure 7. Filter 8. HTTP 9. Input 10. Java 11. Joiner 12. lookup 13. Normalizer 14. output 15.rank 16. reusable 17. router 18. sequence generator 19. sorter 20.source qualifier 21. sql transformation 22. stored procedure 23.transaction control 24. union transformation 25. unstructured data 26. update strategy 27. xml generator 28. xml parser 29. xml sequence qualifier 30. advanced external procedure 31. External transformation.

    These are the ones I know of. There may be many more others can add to the list.

  29. Mahan says:

    What exactly is tracing level?

    Tracing level is the information you want to log in log files by the Informatica process. It is set when you install the Informatica server. There are 4 tracing levels.
    1. Normal
    2.terse
    3. Verbose
    4. verbose data

  30. hazarath says:

    please send interview questions

  31. ramesh says:

    i need a resume with 2+ of experience in informatica with oracle…with good project

  32. ramesh says:

    i need a resume with 2+ of experience in informatica with oracle…with good project..send me to rrameshreddy123@yahoo.com

  33. Pradeep says:

    Hi
    Actually this is the question asked me in interview.

    I have created a Workflow and 3 sessions like session1, session2,session3 and now i want to run only session1 and session3 as i dont want to run session2 now what should i do …

  34. joya says:

    Thanks for the post

    You can find some good interview Question Answers on Informatica in the below link

    http://www.aired.in/2010/12/informatica-interview-question-answers.html

    Thanks
    Joya

  35. K.Sunil Reddy says:

    Can some one send me the Interview questions & Answers for a 3 yr experienced candidate……

    my email id mailsunilinbox@gmail.com

  36. learner says:

    why are people soo arrogant when requesting for the answers…guys this is internet..when u have the questions, cant u search for answers….some people are demanding not even requesting….too rude…learn some manners before u look for a job

  37. Murugan says:

    its good okk

  38. viraja says:

    hi

    nice questions.but i want these answers.where i can get

  39. Rajesh says:

    Hi frnds,

    Iam searching for 3+ exp in informatica , i need informatica interview question& answers. plz forward to me

    my mail id :rajeshcse_cse27@yahoo.com

  40. sekhar says:

    sir/madam can u tell the, what is dynamic lookup cache

  41. sekhar says:

    HI sir/madam,
    This is sekhar, i’m searching a job, 2+ in informatica SO,can u send the iformatica FAQ’S questions AND RESUME PREPARARION OF 2+ to my mail id is kondati.sekhar@gmail.com,
    AND GIVE YOUR VALUABLE SUGGSTIONS TO ME SIR/MAdam please,please

  42. sreekanth says:

    hai Sanjay Prasad sir,i’m searching a job, with 3+ in informatica SO,can u send the iformatica all FAQ’S questions& answers for 3 years of experiance candidates.and send them to sree3109@gmail.com

  43. RAMESH says:

    HI ANYBODY SEND ME REALTIME SENARIOUS WHAT U HAVE FACED IN UR EXP.
    ramesh2infa@gmail.com

  44. amrutha says:

    hi i am a 2011 pass out having 83%.and i completed training in informatica and cognos .i am searching for a job.is there any chances to get a job as fresher for this cadre.please help me.thanks in advance.

  45. nasreen says:

    HI
    I had 2yrs of experience in Informatica admin work,I want to move to development side of Informatica.
    can any one one suggest me how can I learn that in less minimal time (any docs , sites …)plz suggest.

  46. khan says:

    Guys!!
    Now a days informatica interviews are not that much simple which you are thinking off. i have been in this field since from 8 years, completely into ETL and that too informatica (of-course i know Obiee).

    First of all, now a days interviewers are thinking that they are einsteins and some are thinking more than him,, and they are pretending as if they know whole lot of things (like data modeling, oracle, pl/sql, scripting, etl architecture, dba activities… lot more).
    One of my friends brother is attending interviews and he is asking the questions which were asked in interviews.. to be frank i myself don’t know some of them….;

    in most of interviews they are stressing oracle, unix and pl/sql mostly. i too strongly agree that etl developer need knowledge about these and the question is up to how far??
    in one of his interview he was asked about will you take back ups in oracle? R u kidding ?? its job of DBA….what will poor etl developer do with it?
    In some interviews he was asked about 10 oracle quries… first and they send him away….; i dont know why they didn’t ask single etl/data warehouse questions… by giving big ad “wanted informatica developer” if they want very strong person in oracle, take the people who worked on oracle and train them on informatica…;

    one of question was “how do you tune the query” he said he told by using explain plan , and query the plan table to see how table/indexes are accessed and try to alter the where clauses….etc. (i think its more than sufficient , since generally etl developers in very rare cases do this kind of tunnings to queries…..) but interviewers was not satisfied with his answer and want to tune the query with out explain plan…. “R U in nuts??” u asked some thing and i told relevent answer…and now u want another answer for your f***** question.

    another question about indexes: its must and shood etl developer needs an understanding about indexes and how they are useful, thats okay… but what about “partitioning in indexes”?? and different types in it?? –Man how can you expect a ordinary etl (3 yrs exp ) know such dept? he is not a oracle developer “remember” he is a informatica developer…

    can you write a unix script to do a reverse string?? What??? r u asking man?? he is not a unix shell script writer…..;

    like this our extraordinary interviewers are asking questions……. these sick fellows are thinking that etl developer need to be a SUPERMAN or SPIDERMAN to do every thing from requirement gathering to reporting….;
    then wat about the others?

    also regarding to informatica, ” how to pass workflow logs to db tables”? its okay to put a question like this in interview, but dont expect that every developer who is working in informatica reply to this, because in general these kind of weired things are not done in all projects; if we need some thing like this in project we can go for google..or some other site to know how to workout it,, and it wont take much time for sure….;

    Interviewers are looking into the toughest questions which they even dont know until they see the answers in those sites, and firing them in interviews… pretending that they are supermen for that company…(every body know what will be the work once we are into the project) …, its not at all the good practice.

    but most of them are unable to tell why some of the developers who pass the toughest questions in interviews are unable to do efficient work in project? ooops where is it going wrong?

    dont guage the person on asking the toughest questions (probably he also read the same question and answers where you read)……

    the professional interviewers,, will always ask the questions like the architecture of his project, issues he faced, critical/complex mappings, tunning, production issues, cache, unit testing, code migrations, bug fixing.. these kind of relevent questions and try to find out his knowlege in oracle and unix…

    by the way,,, people who are reading my reply may felt why this fellow is writing all this f***; just to let you know guys how tough the informatica interviews are and how strong you need to be prepared….; also it may be a good message to the interviews to change there attitude.. and test the candidates thru right questions instead of asking f*** questions.

    Interviewers should always keep in mind that ” they are just employees of the organization and they dont own the organization, and the candidate is here to discuss his project/work/experience,… it will be always healthly like that. try to give some respect to the persons who came for interview,its humanity…

  47. khan says:

    nasreen: being in admin side is best rather than coming to dev side. hell lot of competition will be there if u join in dev. and u need to be superman; Admin though opportunities are less in the same time resources are also less. so 100% better be in admin side.

  48. student says:

    Hi
    I am Orni, looking desperately for 3+ years experience ETL job with Informatica and AB Initio tools. Can somebody please help me out?
    My id orni_00@yahoo.com

  49. naresh says:

    ,i’m searching a job, with 3+ in informatica SO,can u send the iformatica all FAQ’S questions& answers for 3 years of experiance candidates.and send them …

  50. gyana says:

    KHAN bhai well said..i am also working in CAPGEMINI…they also feel like SUPERMAN of the company..if a developer can do all things and if he has so much of knowledge then y he will come to ur organisation for interview..And what about the team members..in one project the only person will work or WHAT??
    Khan bhai..i totally agree with u..these question are only to haras the candidate nothing help….i wan to communicate further you,,..if possible please contact me by mail..i will definitly reply u KHAN BHAI….THANX

  51. Navin says:

    Well said Khan … Too bad i dint have a senior like you to guide me .. Insted i had a dick head who had exactly the characteristics you have mentioned.

Leave a Reply