Informatica Interview Questions

Its thrusday; After coming from office just checked the mail and there was a pop-up appeared, "Interview Scheduled for IBM on Saturday"

Immediately opened a Prints of Informatica Help Files, and fortunately got question bank there.. as in it was waiting for me only.

This question bank is given by "Meenakshi Saxena", a really good resource from Mahindra Satyam working for ASBI - Cisco and was our team member. She had collected questions from different interviews attended by her mates and by her own.

IBM
Please draw the data model of your project

What is operational data source

How do you apply the incremental logic in your project

Do you have type-2 scd being used in your project? How do you deal with the expired keys of SCD-2 in the related fact tables? i.e. when you expire the rows in the dimension, what do you do with the corresponding keys in the fact? Do you replace them?

What is a surrogate key? What is the role that Surrogate key plays in the case of SCD-2

What is incremental aggregation? Please explain with example how it works

Please draw the Architecture of Informatica 8.1.1

What is Pushdown optimization? Please explain the different types of Pushdown Optimization

What are the drawbacks of PushDown optimization ?

What are the different lookup caches? Explain about Dynamic Lookup Cache. What is associated port in the lookup tranformation for dynamic lookup cache.

How do you do Version Control in Informatica?

If there are 11 versions of a mapping and if you want to keep the latest version and delete the remaining versions .. how do you do it?

What is a deployment group? What is a label?

What the difference between a Shared Tranformation and a Reusable Tranformation? How do you create ShortCuts and how do you use them ?

How can you share a tranformation between various repositories?

When you load a target flat file? how do get the column names in that file? Is that possible in Informatica 7.1.1.
How do you attain this in informatica 7.1.1. only through mapping not through windows or unix

Do we have an alternate for this in informatica 8.6.1. ?

What is confirmed dimension? Are their any confirmed dimensions being used in your project ?

Have you used Normelizer Tranformation? There are 2 informatica generated columns in Normalizer the GKey (Generated Key).. what is the significance of this key?

What is the difference between stop and abort? Under what circumstances you would stop and when you would abort? Which one is better?

There is a workflow having 3 sessions in sequence. A-->B-->C. We want session C to run only on 12 successful completions of session B. How would you do that?

Can you briefly explain the Architecture of Informatica 8.1.1.?

Do we have sorted input option in Lookup Tranformation? Can we use this option for relational sources ?
What will happen if you enable sorted input, and the condition columns are not grouped?

What will happen if you create too many partitions in Informatica ?

What are disadvantages of creating too many indexes on database tables?

What is co-related subquery?

What are different types of indexes that you have used?

What are database triggers?

How do ou delete duplicates in a table?

What are the performance tuning activities you follow in your project?

What is the difference between the result-set of Full Outer Join and Union Tranformation?
Will the result-set be the same if the columns are same in both tables?

What is the difference between Star Schema and Snow Flake Schema?

Have you ever worked with Legacy System sources?


IBM - Asked to Tushar

What is incremental aggregation, what are the scenarios it is used?

What is even wait and event raised, asked a scenario where some sessions are arranged in parallel plus sequential order. Where will we put event wait and raise tasks?

Write a query to eliminate duplicate

A map has no update strategy, still how can we update the target rows?

what is normalizer tranformation ?

How would you convert, using informatica, multiple rows in source to single row in target (de-normalize data)?

One sequence generator is connected to 2 different targets and nextval is connected to PK, what will be value of PK when first 5 rows are passes in the mapping?

What is persistent cache, what type of cache has to be used when configuring persistent cache?

How do you use dynamic lookup? Wha is the name of port created by Informtica in this? what are its values and what do they mean?

What is difference between shared object and shortcut?

What is difference between parameter and variable?

Give 3 infromatica pre-defined mapping workflow, mapping and session parameters?

What is transaction control tranformation?

The PK in target is not linked in mapping still it is getting values populated for every row, how is it possible?

Difference between types of indexes?

Flow of project - end ot end

What kind of SCD was implemented in your project? And how was it implemented?

IBM asked to Dona

Please draw the data model of your project.

How many type of tasks are there ? Explain event wait task with practical example?

How is even wait and event raise, asked a scenario where some sessions are arranged in parallel plus sequential order, where will we put event wait and raise tasks?

What are the different lookup caches.. Explain about Dynamic Lookup Cache.. what is associated port in lookup tranformation for dynamic lookup cache.

What the difference between Shared Tranformation and Reusable Tranformation? How do you create shortcuts and how do you use them?

What is indirect files loading?

What is normalizer?

How many ports does unconnected lookup return? If I do lookup on EID and I want Ename and Esal from the same table by using an unconnected lookup then how u will do it?

Why you are using fast load why not Mload in your project?

How are you taking care of exceptions in your project? Develop a mapping for that?

What is constraint based loading? How it works in Informatica?

2 Scenario based questions.

Capgemini

What are the differences between Infa 7.1 and 8.1

What are the new features of 8.1.1 that you are using in your project

What is a shared sequence generator? How does it work? If I have same seq generator used for 2 mappings and one mapping has already updated it say 10.. will the next mapping start with 0 or 11?

There are 3 different flat files... How can you load these 3 flat files in 3 different targets simultaneously.. Not in 3 different pipelines

If you get a file everyday from the user which does not have a specified structure... i.e. on one day the # columns is 5.. is t possible to use a single mapping that can handle this kind of flat file?

What are the different components of Informatica power center?

What is the difference between Repository Manager and Repository Administrator?

What do you know about the new central web service in Informatica 8.1.1 ?

How do you migrate workflows in Repository Manager? Can you take xls of only one workflow at a time or can you take xmls for multiple workflows simultaneously?

What is the difference between Bulk Load and Normal Load? What are the pre-requisites for Bulk Load?

Suppose you have a Relational Table that does not have a Primary Key defined on it. How can you do updates on this kind of a table using Informatica? Can you use update strategy ? If so..how ? {Update Sql OverRide}

Have you used Normalizer Tranformation? What is Normalixer Tranformation?

How do you do version control in Informatica?

Have you used Mapplets? Can we have Source Qualifier Tranformation in a Mapplet?

What is a Global Repository and What is a Local Repository? State all features and difference between the 2.

FCS

Can you briefly explain the Informatica Architecture?

What are different target load types that we use?

Can u bulk insert with Update strategy?

What are the other pre-requisites for bulk insert?

What is the joiner cache?

How do you tune a lookup tranformation?

Under what circumstances do we use a persistent lookup cache?

How many files will be created when we use a persistent lookup cache?

What values do we have in Lookup Index Cache and Data Cache Files?

Can we use a aggregator function in a aggregator tranformation without using a group by?

Can we use Group By in an aggregator tranformation without using an aggregator function?

What are the different performance tunning you hav done in your project?

Have you used PushDown optimization? What is pushdown optimization?

What is a star schema? and what is the snow-flake schema?

When do we go for a snow-flake schema ?

What is the basic reason for maintaining a datawarehouse?

What is a bank atm? Is that an OLTP? or OLAP? It also maintains history so why it is not a DWH?


WIPRO

What is EDW?

What is Dimensional Modeling? Why is it required?

What is the Relationship between Dim and Fact?

Difference between snowflake schema and start schema? In what situations we will go for snowflake ?

How do you maintain Version Control in Informatica in your project?

If you had scd-2 in your lookup, why you did not use dynamic lkp cache? How else did you achieve scd-2?

How to use dynamic lkp cache?

What is a difference between mapping variable and parameter?

Three different examples where mapping variable could be used?

Difference between stored proc and function?

Different type of functions?

Which index to be used if only select statements are required on a table?

How to eliminate duplicates from a table using query only?

How to fetch employees with top 5 salary from EMP table?

DELL

What are the aspects considered while deciding number of facts and dim in a model?

What is fact? How do you decide this element is a part of fact and not dim?

What is incremental loading?

What is bottleneck?

What is constraint based loading?

3 ways in which you can drop indexes on target while loading and then re-create after loading using informatica?

Trace levels?

Which tracing level is bettwer if you have to migrate one mapping to production?

What all tranformations can be set as unconnected?

What is transaction control tranformation?

How to start a failed workflow so that the previous successful sessions do not start again?

What is the syntax of parameter file?

How can we see the number of rows that were sent to and out of each tranformation after session is successful

What are different recovery methods in Informatica?

How can we achieve incremental aggregation?

3 ways to generate seq number without using seq generation tranformation?

Difference in Version 8 and 8.6?

Explain architecture of 8 (domain/node concept)

What do you mean by load balancing?

What is metadata manager?

What are the minimum requirements to run a debugger?

How to set/edit breakpoints in a debugger?

Truncate and Delete difference

Different types of Indexes. what are the differences?

Some unix questions on commands like, how to copy, concatenate files etc

TCS

Which version of Infra you worked on?

What is the difference?

Have you heard about PC9? What are new features?

What is snowflake schema?

When does a client go for snowflake schema?

What is a junk dimension, explain with example?

Give an example of ODS?

what is confirmed dimensions? Example?

What is the need of time dimension?

What is incremental aggregation?

In how many ways can you create source in Infra?

What are different type of loookup cache?

What is dynamic lookup cache? How to implement in Infra?

What happens when you get a null value from lookup and it is used in some function in expression?

Performance Tunning? How will you find bottleneck?

What will happen if we forget to check group by ports in Aggregator?

What will happen if we check sorted input in Aggregator but does not pass sorted inuputs?

What are advantages of bulk loading?

What is constraint based loading? What are advantages and disadvantages?

How to change the data type in SQL Server? cast and convert

Will unique constraint allow null twice?

What is indexing? What is the purpose?

Drop, Delete and Truncate difference?

What are triggers?