Informatica Interview Questions

Top Informatica Interview Questions

Question 1: What are the types of transformations in Informatica?

Informatica transformation can be divided into two types:

  1. Active: It can change the number of rows, i.e., Rowsinput <> RowsOutput
  2. 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
For Ex:

  • 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?

Aggregator:
                    Index cache stores group data
                    Data cache stores row data
Lookup:
                   Index cache stores conditions
                    Data cache stored data from lookup
Rank:
                    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?

  1. If sequence generator transformation was connected directly before the Joiner transformation
  2. 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?

Aggregator Transformation:
We can achieve this by using groupby on duplicate fields.
Lookup Transformation:
We can achieve this using dynamic cache. Insert Else Update option will help to eliminate the duplicate records in source.
Rank Transformation:
We can use group by port with the setting of rank = 1.
Sorter Transformation:
By selecting distinct in sorter property
Source Qualifier:
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,
insert (0),
update (1),
delete (2), and
reject (3).
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.

Leave a Reply

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close