How to append data to a table automatically?

Hi all,

 

I have been allowed to query our main table only 1 day at a time to avoid tech issues (MariaDB).

 

I am wondering how I could pull say previous day's data and then append to all the previous data pulled. Each row has a unique ID so this should be easy to avoid duplicates.

 

The other question is - what would be the best suggestion to do this manually to kick off the database? I need like the last 90 days of data and then I can switch to 1x day updates with append.

 

Thank you in advance

Best Answers

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Are you using something like Domo Workbench to query your source data and then pushing it to Domo? If so, you could limit your query to just the previous days data if you have a date column in your dataset by using a WHERE clause like: 

    WHERE TransactionDate = GETDATE()-1

     

    In Domo, you can set your dataset to append rather than replace so that the new data will just be added to the dataset.

     

    By default, Replace is the update method selected. Every time the DataSet runs, all new data brought in, based on the selections made under the Settings tab, will replace the data from the previous run.

    When using the Append method, all of the new data brought in based on the selections made under the Settings tab will be added to the bottom of the existing DataSet table.

  • Hi @MarkSnodgrass 

    I'm very new to this so I wouldn't know much about Workbench.

     

    However, I have set up MariaDB Connector with a query and it pulls in yesterday's data and it updates 1x per day pulling previous day. So far so good.

     

    So what I cannot figure out is what to do next as this is the source DataSet but how do I create the "repository" where the data will be merged in moving forward?

     

    Do I need the first time to build ETL that just saves the data into a new (final dataset)?

    And then reconnect and start merging the two together? Aka final + yesterday's data?

     

     

  • Thank you both - turned out that default instruction in the end was the correct way to go, just the data manipulations didn't seem to make sense, but now that it works, it makes a lot more sense. Thank you all!

  • trafalger
    trafalger Chicagoland, IL 🟠

    And I agree with @MarkSnodgrass, if you can change the connector to append it's a 30-second fix instead of building out a recursive dataflow. 

  • Thanks, the SQL storage was the thing that solved the problem. I was able to manually run the queries to pick up previous days and now 1x per day it will add the previous day's stats. I then run a simple ETL to de-dupe any data just in case. Sometimes the best solutions are the simplest ones ?