Union Transformation

The Union Transformation object in Astera is used to combine incoming data from two or more inputs into a single output. Its functionality is similar to a union operator in SQL query. It has multiple input nodes and a single output node. It puts together two sets of data irrespective of the repetition that might occur in the datasets. In order to perform this transformation on two datasets, their cardinality must be same.

Note: An input node in a union transformation cannot receive data from two different set transformations.

Video

Use-Case

In this example, we have a customers data from two different departments: Sales and Marketing, stored in two separate Excel Workbook Source files. We want to combine this data into a single dataset using a Union Transformation object. To keep track of records coming in from each department, we will also add a new field, Category, in the layout of the Union Transformation object and pass the value using a Variables object.

How to Work with Union Transformation

1. To work with a Union Transformation object, drag-and-drop the Union Transformation object from Toolbox > Transformations > Union onto the dataflow designer.

1. Map the Customers_Marketing data to Input_1 and Customers_Sales data to Input_2 in the Union Transformation object.

1. Now, right-click on the Union Transformation object’s header and select Properties.

1. The first window is the Layout Builder window, where you can customize your layout or modify your fields. You can also provide a default value to be used in place of null values.

Add a new field, name it Category and specify its Data Type as String.

Click Next.

1. Next is the Union Transformation Properties window where there are two input nodes defined as Input_1 and Input_2 by default. You can also rename them if you want. You can also define any number of input nodes based on the number of datasets you want to combine using the Union Transformation object.

Click OK.

1. Now, map the categories of respective departments from the Variables resource object to the Category field in the Union Transformation object. This is done to identify which department a particular record is coming from.

Now, we have successfully configured the Union Transformation object.

1. Right-click on the Union Transformation object’s header and select Preview Output.

You can see that the Union Transformation has successfully combined the two datasets into a single, unified dataset.

Note: A Union Transformation will show the combined fields from both sources as a resultant, regardless of whether they are present in one or both datasets. In the final output, the dataset which does not have one or more fields will be assigned a null value in that field.

1. You can now further transform your dataset or write it to a destination.

This concludes working with the Union Transformation object in Astera.