Skip to main content
waffle.svg
Domo Knowledge Base

Using Upsert in Workbench 5

Version 5

 

Intro

Using upsert in Workbench allows you to identify a smaller subset of new or updated rows and push them to an existing Domo DataSet. Upsert is a type of SQL merge that can be used to dramatically increase the performance of your data integration in certain use cases. To use this update method, you must have a key, either primary or composite, to uniquely identify each row of data. Additionally, there must be a method to identify the subset of rows to send to Domo, such as a where clause in an ODBC job. Once the subset of rows is identified, Domo will compare the key column(s) to determine if the row already exists in your DataSet and update those records. Rows that do not exist will be appended.

Upsert typically works best when your DataSet is very large, the data is updating frequently, and your data contains a unique primary or composite key. The efficiency of upsert is realized when we don't send all rows of a DataSet every time but rather only the new or updated rows. Proper use of upsert can have significant positive impacts on performance and efficiency of your Workbench job as well as requiring fewer resources on your database or file storage system.

 

How to Use Upsert

To use the upsert function in Workbench, you should already have an existing Workbench job and a healthy DataSet in Domo. For more information on how to create a Workbench job, see Creating a Job in Workbench 5.

  1. Navigate to the Configure tab.

  2. Set the Update Method to Append

    Upsert Workbench Update Method.png

  3. Click on the Schema tab and you will now see Upsert Key.

  4. Select the checkboxes for the corresponding columns that represent the primary key.

    Tip: Date or datetime columns are not recommended for the Upsert Key as they are not always unique.


    In the following image, the `Transaction ID` is completely unique per transaction so that will be selected. Now, Workbench will always reference `Transaction ID` to see if a transaction id exists and update it or if it does not add that new row.

    Note: If you do not see Upsert Key after following these steps, contact Domo Support and provide the Domo DataSet ID to verify upsert is enabled. See Contacting Technical Support for more information.

    Upsert Key.png 

  5. Next, navigate back to the Configure tab and click edit under processing to bring up your query.

  6. Add logic to ensure you're only pulling a smaller subset of data that could be new or updated.

    In this example, we know that our data could be updated within 7 days of the transaction, so we are pulling the last 7 days' worth of transactions to capture any updated rows as well as any new ones each day.

    Workbench Upsert Query.png

  7. Click Apply.

  8. Run Preview to ensure the appropriate records are returned. 

  9. (Optional) Job scheduling should be reviewed and adjusted accordingly.

Your Workbench upsert job is now properly configured and will update or add new information based on your query.