Informatica Interview Questions on Joiner Transformation

1. What is a joiner transformation?

A joiner transformation joins two heterogeneous sources. You can also join the data from the same source. The joiner transformation joins sources with at least one matching column. The joiner uses a condition that matches one or more joins of columns between the two sources.

2. How many joiner transformations are required to join n sources?

To join n sources n-1 joiner transformations are required.

3. What are the limitations of joiner transformation?

You cannot use a joiner transformation when input pipeline contains an update strategy transformation.

You cannot use a joiner if you connect a sequence generator transformation directly before the joiner.

4. What are the different types of joins?

• Normal join: In a normal join, the integration service discards all the rows from the master and detail source that do not match the join condition.
• Master outer join: A master outer join keeps all the rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
• Detail outer join: A detail outer join keeps all the rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
• Full outer join: A full outer join keeps all rows of data from both the master and detail rows.

5. What is joiner cache?

When the integration service processes a joiner transformation, it reads the rows from master source and builds the index and data cached. Then the integration service reads the detail source and performs the join. In case of sorted joiner, the integration service reads both sources (master and detail) concurrently and builds the cache based on the master rows.

6. How to improve the performance of joiner transformation?

• Join sorted data whenever possible.
• For an unsorted Joiner transformation, designate the source with fewer rows as the master source.
• For a sorted Joiner transformation, designate the source with fewer duplicate key values as the master source.

7. Why joiner is a blocking transformation?

When the integration service processes an unsorted joiner transformation, it reads all master rows before it reads the detail rows. To ensure it reads all master rows before the detail rows, the integration service blocks all the details source while it caches rows from the master source. As it blocks the detail source, the unsorted joiner is called a blocking transformation.

8. What are the settings used to configure the joiner transformation

• Master and detail source
• Type of join
• Join condition