Union Transformation in IICS – IDMC

Active and Connected

Last Updated: 17 November 2023

By: Nataraj Virupaksham (Raj)

Union Transformation is Active and Connected Transformation in Informatica Cloud IICS – IDMC which is used to combine the data from different sources.

• Used to merge data from multiple pipelines into a single pipeline.
• When we have multiple sources (homogeneous/heterogeneous) with the same structure, to combine all the records from source, then we will use union tr.

  1. Union transformation is an active tr which is used to combine (add the records) from all the sources (homogeneous/ heterogenous) which are having same structure.
  2. Union transformation is having multiple input groups and single output group. Union transformation cannot have more than one output group.
  3. Union transformation is exactly opposite of router transformation.
  4. Union transformation in informatica will work as a union all set operator in oracle. Union transformation will not delete the duplicates.
  5. Union transformation + sorter transformation with distinct , will acts a union in oracle.

Why union is active transformation?
when a transformation having more than one input or more than output group, we will call that as active transformation.
Because, union transformation is having multiple input groups, that’s why union is active.

Input Groups: By Default, Union Transformation has two input groups like below.

• If you want have more than two sources, then you can create More input groups.
• To add an input group, in the Mapping canvas, connect an upstream transformation to the “New Group” group of the Union transformation.
• You can also add input groups on the Incoming Fields tab of the Union transformation.

Incoming Fields: You can rename input groups. You can rename and delete input groups on the Incoming Fields tab of the Union transformation.

Field mapping: When you work with field mappings in a Union transformation, note the following:

• You must use input groups where the fields have the identical name, type, precision, and scale.
• You must do Field Mapping for each Input Group in the Union Transformation by selecting each input group drop down like below.


Difference between Union and Joiner transformation:

Requirements Union Transformation Joiner Transformation
Remove duplicate rows No, you can use a Router or Filter transformation downstream from the union transformation to remove remove duplicates Yes
Combine records based on a join condition No, the union transportation is equivalent to a UNION ALL statement in SQL, which combines data vertically from multiple sources Yes, the joiner transformation supports normal, Right Outer, Left Outer and Full Outer JOINs
Include multiple input groups Yes, you can define multiple input groups and one output group Yes, you can define two input groups, Master and Detail
Include heterogeneous sources Yes Yes
Marge different data types All of the source columns must have similar data types. The number of columns in each source must be the same At least one column in the sources to be joined, must have the same data type
Generate transactions No Yes

Example of union transformation: An Example of Union transformation shown below. The below mapping, combines all the sources by using Union transformation, also Sorter will delete the duplicates and load the data to target.

Difference between Union and Router transformation:

Router Transformation Union Transformation
Having one input, one default and mulitple output groups Having Muliple input groups and one output group
Router will split the source data multiple targets Union will combine multiple sources and send the data single target
To join “n” source tables, only one source transformation is required To join “n” sources, “n-1” joiner transformations required
Router is having default group No default group
We should give condtion for each group No conditions

Leave a comment

Subscribe for material notification

Fill this form to get notify when we post new study materials on Raj Informatica.

Post Notification
Back to Top
Product has been added to your cart

Continue Reading Union Transformation in IICS – IDMC

Fill out this form to explore all our interview question sets.