Informatica ETL Interview Questions and Answers

Question.1 Can you use mapping without source qualifier?

Answer:  Not possible, If source RDEMS/DBMS/Flat file use SQ or use normalizer if the source cobol feed.

 Question 2.When do you use a normalizer?

Answer:  Normalized can be used in Relational to denormilize data. [sociallocker]

Question.3 What are stored procedure transformations. Purpose of sp trar sformation. How did you go about using your project?

Answer:  Connected and unconnected stored procedure.

Unconnected stored procedure used for data base level activities such as pre and post load Connected stored procedure used in informatica level for example passing one parameter as input and capturing return value from the stored procedure.

 Normal – row wise check

Pre-Load Source – (Capture source incremental data for incremental aggregation)

Post-Load Source – (Delete Temporary tables)

Pre-Load Target – (Check dick space available)

Post-Load Target — (Drop and recreate index)

Question.4  What is lookup and difference between Types of lookup. What exactly happens when a lookup is cached. How does a dynamic: lookup cache work.

Answer:  Lookup transformation used for check values in the source and target tables(primary key values). There are 2 type connected and unconnected transformation

Connected lookup returns multiple values if condition true Where as unconnected return a single values through return port.

Connected lookup return default user value if the condition does not mach Where as unconnected return null values Lookup cache does: Read the source/target table and stored in the lookup cache .

Question.5  How did you schedule sessions in your project?

Answer:  Run once (set 2 parameter date and time when session should start) Run Every (Informatica server run session at regular interval as we configured, parameter Days, hour, minutes, end on, end after, forever)

Customized repeat (Repeat every 2 days, daily frequency hr, min, every week, every month) Run only on demand(Manually run) this not session scheduling.

Question.6  Explain lookup cache, various caches?

Answer:  Lookup transformation used for check values in the source and target tables(primary key values). Various Caches:

Persistent cache (we can save the lookup cache files and reuse them the next time process the lookup transformation)

Re-cache from database (if the persistent cache not synchronized with lookup table you can- configure the lookup transformation to rebuild the lookup cache)

Static cache (When the lookup condition is true, Informatica server return a value from lookup cache and it’s does not update the cache while it processes the lookup transformation)

Dynamic cache (Informatica server dynamically inserts new rows or update existing rows in the cache and the target. Suppose if we want lookup a target table we can use dynamic cache)

 Shared cache (we can share lookup transformation between multiple transformations in a mapping.2 lookup in a mapping can share single lookup cache) .

Question.7 Which path will the cache be created?

Answer:  User specified directory. If we say c:\ all the cache files created in this directory.

Question.8 where do you specify all the parameters for lookup caches?

 Answer:  Lookup property sheet/tab.

Question.9 How do you remove the cache files after the transformation?

 Answer:  After session complete, DTM remove cache memory and deletes caches files. In case using persistent cache and Incremental aggregation then caches files will be saved.

Question.10 What is the use of aggregator transformation?

Answer:  To perform Aggregate calculation Use conditional clause to filter data in the expression Sum(commission, Commission >2000) Use non-aggregate function if (max(quantity) > 0, Max(quantity), 0))

Question.11 What are the contents of index and cache files?

 Answer:  Index caches files hold unique group values as determined by group by port in _the transformation. Data caches files hold row data until it performs necessary calculation.

Question.12 How do you call a store procedure within a transformation?

Answer:  In the expression transformation create new out port in the expression write: sp. stored procedure name (arguments).

Question.13  Is there any performance issue in connected & unconnected lookup? If yes, How?

Answer:  Yes Unconnected lookup much more faster than connected lookup why because in unconnected not connected to fly other transformation we are calling it from other transformation so it minimize lookup cache value Where as connected transformation connected to other transformation so it keeps values in the lookup cache.

Question.14 What is dynamic lookup?

Answer:  When we use target lookup table, Informatica server dynamically insert new values or it updates if the values exist and passes to target table.

Question. 15 How lnfcrmatica read data if source have one relational and flat file?

Answer:  Use joiner transformation after source qualifier before other transformation.

Question.16 How you will load unique record into target flat file from source f13t files has duplicate data?

Answer:   There are 2 we can de this either we can use R. ink transformation or oracle external table in rank transformation using group by port (Group it e records) and then set no. of rank 1. Rank transformation return one value from the group. That the values wit De a unique one.

Question.17 Can you use flat file for repository?

Answer:  No, We cant

Question.18 Without Source Qualifier and joiner how will you join tables?

Answer:  Yr -IPA% In session level we have option user defined join. Where we can write join condition.

Question.19 Update strategy set DD_Update but in session level have insert. What will happens?

Answer:  Insert take place. Because this ‘option override the mapping level option . [/sociallocker]