Should I really be using UPSERT KEY just to APPEND data?

I have a huge dataset I've uploaded through Workbench. I now want to run a daily APPEND of new data only. I don't see a way to define an "APPEND" key, just an "UPSERT" key. My data won't really change, so I really only need inserts of any records where the defined ID is not already in Domo.

My append query looks for a change date (in my local source table) in the past 3 days. This ensures that if the Workbench job gets missed, it can pick things up on the next run. The job seems to run okay. It inserts new records from the past day, and updates 2 and 3 days ago (which really haven't changed, so they get updated to the same values they were).

Am I missing something? Is there really not a way to only add new data based on a defined ID column? (This would also let users see a true count of how many records were added each run. Now they are seeing the count of updated and inserted records)

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @JunkDoom

    Your configuration would work to assure you don’t have duplicate records. If you’re wanting to make sure you don’t pull in any record that already exist I recommend looking into the lastvalue replacement variable in workbench query. Workbench will track the last value it sees each run and you can add a condition to your query to only pull data after that value. Just make sure you’re sorting by the last value field in ascending order. You can find more information on it here at the bottom of the article. https://domohelp.domo.com/hc/en-us/articles/4407020836887-Connecting-to-ODBC-Data-in-Workbench-5-1