Top Informatica Interview Questions
Question 1: What are the types of transformations in Informatica?
Informatica transformation can be divided into two types:
- Active: It can change the number of rows, i.e., Rowsinput <> RowsOutput
- Passive: It does change the number of rows.
Question 2: Can aggregator transformation support non-aggregate expressions?
Answer is Yes, surprisingly, aggregator transformation supports conditional clauses and expressions which are non-aggregate in nature
- Non- aggregate exp: [IIF(REQUIREMENT = ‘TopQuestions’,’Interview’,’RequirementNotInStackOverFlow’)]
- Conditional clause: [SUM(salary, salary>500)]
Question 3: How can we filter records?
We can filter records in many different ways,
- Source Qualifier – Filter input rows from DB
- Filter transformation – Filter rows on the basis of condition
- Router transformation – Filter rows and route them according to parent condition
- Update strategy – Multiple options available to handle and filter data
Question 4: What are the different uses of Index and data cache across transformations?
Index cache stores group data
Data cache stores row data
Index cache stores conditions
Data cache stored data from lookup
Index cache stores group information
Data cache stores raw data
Question 5: How NULL values are handled in different transformations?
- Aggregator treats it as null. One can specify to treat null values as zero or null
- Joiner does not match null values. One can replace nulls with default values and then join on those default values.
- Sorter treat Null higher than any other value. We can enable property Null treated low, then null values are kept lower than other.
Question 6: What are the limitations where we can’t use Joiner in mapping?
- If sequence generator transformation was connected directly before the Joiner transformation
- If input pipeline had Update strategy transformation
Question 7: What is the output of aggregator if we did not group by on any port?
For the input rows the last row value of the column will be returned.
Question 8: What are the different ways to get distinct records?
We can achieve this by using groupby on duplicate fields.
We can achieve this using dynamic cache. Insert Else Update option will help to eliminate the duplicate records in source.
We can use group by port with the setting of rank = 1.
By selecting distinct in sorter property
We can choose Select Distinct option for DB
There are many direct and indirect ways of achieving it. Above mentioned are a few of the direct ways.
Question 9: What is Data driven?
This is a session property. It is selected specifically when using Update Strategy as it flags source for,
delete (2), and
If treat source rows is not set to Data driven then update strategy is ignored in the mapping.
Question 10: What is a bad file?
If there is any rejection of row by writer or target, these rows are then saved in a file called reject file (bad file).
Bad file data looks like,
Row indicator: 0-1-2-3
- 0 – Insert Writer or target
- 1 – Update Writer or target
- 2 – Delete Writer or target
- 3 – Reject Writer
- Null is represented as comma.
Column indicator: D-O-N-T
- D – Valid Data. Most probably database error.
- O – Overflow Bad Data
- N – Null Bad Data
- T – Truncated Bad Data
Question 11: What are shortcuts? What are the advantages?
Shortcuts here are as same as shortcuts we use in our laptop/desktop. Say we want to use Source in multiple mappings in many folders, so, here instead of creating source definition multiple times we create shortcuts.|
There are two shortcuts:
- Local: Used in local repository
- Global: Used in global repository
Coming to advantage of shortcuts, it is simply re-usage. Without creating an object, we can reuse it with the help of shortcut.