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.
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! 
hi…its not ok…i require more abt this interview process
can i have informatica help file with informatica being installed.
i am interested in informatica. can i get informatica help with out being installed
hi,
is it true that in interview unix shell prog is given equally imp for informatica consultants
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
very useful info especially for people new to informatica… thanks!!
hi,
when can i expect the answers for the above questions..
what is the package for informatica-consultants with 2yrs exp..
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
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.
Hi ,,
Now I.m studying data warehouse.. Where i can i get some manuels or help file to study Informatca 7.1 …
guyz i’ve taken data warehousing coaching recently..i need a buk which explains eac and every thing about ETL process
what are confirmed dimensions ? give examples.
hi,
now,I am attending interviews on informatica.so i want all the faqs with answers
Hi
i need a faq on informatica, if any have it pls forward to my mail khalandar.n@gmail.com thank you
i need a faq on informatica if any have it pls forward to my mail id
nagaraju_etl@yahoo.co.in
how to populate the date dimension in informatica
Hi Guys,
I have completed trainning on Informatica.I want all FAQs regarding Informatica with answer.
Thanks.
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.
thx but here is not answers … pls send my mails ..ok
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
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
Thanks sanjay.I need answer for 2nd question
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.
How we can load the data in two target on the two different server by informatica?
I have modified the base mapping after that, how can i refresh the short cut mapping?
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.