The Reconcile Transformation object in Astera enables users to identify and reconcile new, updated, or deleted information entries within the existing data source.

It can be applied in a wide variety of business scenarios that require a user to identify changes in multiple data records and capture them efficiently to drive critical business decisions.

Sample Use Case

Consider an example where we have sample data of complaints filed by customers regarding the products and services provided by a company. Assume that source file 1 contains details and status of complaints on January 1st, 2018, and source file 2 contains details and status of complaints on February 1st, 2018.

We want to track the progress of the resolved complaints during that one month. To do so, we will reconcile the information contained in the source data files and capture changes using the Reconcile Transformation object.

Using the Reconcile transformation

1. Drag and drop the appropriate source objects and point them towards the files that you want to reconcile. In this example, we will be working with an Excel Workbook Source.

2. Drag and drop the Reconcile Transformation object from Toolbox> Transformations> Reconcile on the data flow designer.

This is what a Reconcile Transformation object looks like:

You can see the transformation object contains three child nodes (Output, Input_1, and Input_2) under the parent node, Reconcile.

3. Expand the input nodes to map fields from the source files.

4. Map the data fields from the source objects that you want to reconcile to the respective input node in the Reconcile Transformation object.

5. Right-click on the Reconcile Transformation object’s header and select Properties.

6. This will open the Reconcile Transformation Properties window where you will see the following options:

• Case Sensitive – Check this option, if you want to derive a case-sensitive output

• Sort Input 1 – Check this option, if the incoming data from source 1 is not sorted

• Sort Input 2– Check this option, if the incoming data from source 2 is not sorted

You can choose the Reconcile Output Type from the following options:

• Side By Side Element With Change Flag – If you want to get values from both sources presented side by side, with a separate column presenting the reconciled output by putting a flag – true, in case of an update, and false if it remains unchanged.

• Original Layout – If you want to get the reconciled output for each record and corresponding information in the reconciled field.

• Original Layout With Changed Element Collection – Applies when working with hierarchical data, to reconcile the information contained in child nodes.

Once you have selected the preferred Output Type, you can specify the records to be shown in the output by applying the Record filter and Inner Node Filter. You may choose one, multiple, or all of the following options by check marking the box.

7. Click Next to proceed to the Layout Builder window. Here you will have to specify a Key. This will be the common identifier in both the source files that will identify and reconcile records. In this case, we want to reconcile the progress on complaints made against each complaint_ID; therefore, we will select Complaint_ID as our Key.

8. Now go to the Survivor Value drop-down list to set the Survivor Value for each data field. Survivor Values are the values from your source datasets that you want to retain in the output.

You may select from the following Survivor Value options:

• First – If you want to derive the output value from the first source

• Second – If you want to derive the output value from the second source

• First If Not Null, Otherwise Second – If you want to output a value from the first source if the record is not null, otherwise from the second source.

• Second If not Null, Otherwise First – If you want to output a value from the second source if it is not null, otherwise from the first source.

• Higher – If the input values are integers, and you want to choose the higher value

• Lower – If the input values are integers, and you want to select the lower value

• Expression – If you want to derive the output value based on a formula expression

9. Click Next to proceed to the General Options window, then click OK.

• General Options Window: This window shares options common to most objects in the dataflow.

• Clear Incoming Record Messages: When this option is checked, any messages coming in from objects preceding the current object will be cleared. This is useful when you need to capture record messages in the log generated by the current object and filter out any record messages generated earlier in the dataflow.

• Do Not Process Records with Errors: When this option is checked, records with errors will not be output by the object. When this option is off, records with errors will be output by the object, and a record message will be attached to the record. This record message can then be fed into downstream objects in the dataflow, for example, a destination file that will capture record messages or a log that will capture messages and collect statistics as well.

• The Comments input allows you to enter comments associated with this object.

10. Now, right-click on the Reconcile Transformation object’s header and select Preview Output to get the reconciled output.

You will get one of the following outputs according to the output type selected in the Reconcile Transformation Properties window:

• Side by Side Element with Change Flag

• Original Layout

• Original Layout With Changed Element Collection

Usage and Benefits

Reconcile Transformation objects can be applied in a variety of business cases, particularly those where monitoring the changes in assorted data records is crucial in driving critical business decisions. Here are some of the benefits and uses of the Reconcile transformation object:

• Reconciles data by deriving old and new values for specific fields in the source data

• Allows users to choose from various layout options to reconcile changes in the most appropriate way

• Works effectively with structured and unstructured (hierarchical) data formats

• Offers the flexibility to select the information to be retained through different survivor value options