Appending Data Capture using Domo Workbench

New Job

Creating a new job that will append the data that has changes in the RDBMS since last load.

Job setup

First do the initial load of the data using the Create New Data source.

The two key setup lines in the sql that are very important are:

1. Where "ReportDate" > '!{lastvalue:ReportDate}!'

The Where clause must contain the field that holds the value that indicate the changes since last load.

The '!{lastvalue:ReportDate}!' lastvalue contains the last value for the Query Variable as shown. (See setup of Query Variable section). It is important to note that the Query Variable must be called the same as the field in the Query that indicates the changes since last Load.

1. Order by

It is important to have an Order By clause in the SQL statement, since it will be the value from the last row that will be saved in the Lastvalue variable. If you don’t order by the column that contains the change data capture field then you might not get the changed rows in the query.

After the initial load change the job to do an “Append to Existing Data Source“ to start appending data to the existing Data Source.

Query Variable setup

The Query Variable setup is done on the separate ‘Edit Query Variables’ screen.

You have the option of setting a Job Variable or a Global Variable.

You will need to create a Job Variable that matches the ‘Name’ of the field that contains the data that controls the Changed data.

The ‘Current Value’ will indicate the last value the job loaded. The initial value must be less than the first record you want to import.

If you need to do a reload of the data you will be able to manually change the Current Value in the initial job, and then make a ‘Replace’ of the data. Once a reload job has been executed the latest loaded value will be updated to the last current value loaded.