How to achieve Delta/Incremental Load in Alteryx

Updating or loading only the new records in the target without doing anything on the old records is the common requirement in any ETL process. In this blog let us look on how to achieve delta\incremental update in Alteryx in an excel file with a simple example and compare the performance between local file update and In-DB update in HANA.

Requirement

How to achieve Delta/Incremental Load in Alteryx

Need to load the records that are not available in the target file or table. For example, we need to load only 5 records that are not available in target table.

Logic needed

To perform the delta load you need to restrict or filter out the data in the source that are already available in the target.

Solution

You can easily achieve this using the join tool, which can be used to filter out the records already available in the target.

You can find three different anchors in the output of join tool such as L, J and R. L anchor of the join tool will help us achieving this, which will give us the records that are not available in the target based on our condition that we have provided in the tool to compare. In our case configure the Join tool as shown below.

The new records from the join tool can be inserted into the target file using the output Data tool by configuring the tool to append the data to existing sheet.

Workflow

Using the join tool and output data tool with the above configuration. We can achieve the delta load in work flow as shown below.

If you are working with the In-DB tools the delta load has to be handled differently like what you see below:

In the above example workflow using IN-DB tools, you will not find anchors like what the default join tool has. That’s the reason you will do a left outer join on the source table with the target. So, you will get all the target records joined with source, which will provide us the records with null value for the records that are not available in target.

Using the filter tool, we can filter out the list of records which has null values in the newly joined field which will provide us the delta records that are needs to be loaded. Then we can unselect the fields from target table using the select tool and then load them to the target.