Informatica Interview Questions on Union Transformation

1. What is a union transformation?

A union transformation is used merge data from multiple sources similar to the UNION ALL SQL statement to combine the results from two or more SQL statements.

2. As union transformation gives UNION ALL output, how you will get the UNION output?

Pass the output of union transformation to a sorter transformation. In the properties of sorter transformation check the option select distinct. Alternatively, you can pass the output of union transformation to aggregator transformation and in the aggregator transformation specify all ports as group by ports.

3. What are the guidelines to be followed while using union transformation?

The following rules and guidelines need to be taken care while working with union transformation:

You can create multiple input groups, but only one output group.

All input groups and the output group must have matching ports. The precision, datatype, and scale must be identical across all groups.

The Union transformation does not remove duplicate rows. To remove duplicate rows, you must add another transformation such as a Router or Filter transformation.

You cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.

The Union transformation does not generate transactions.

4. Why union transformation is an active transformation?

Union is an active transformation because it combines two or more data streams into one. Though the total number of rows passing into the Union is the same as the total number of rows passing out of it, and the sequence of rows from any given input stream is preserved in the output, the positions of the rows are not preserved, i.e. row number 1 from input stream 1 might not be row number 1 in the output stream. Union does not even guarantee that the output is repeatable.