Selecting only newer records from a remote datasource to append to a Domo datasource

JohnnyN
JohnnyN Member
in Connectors

Hi here…
When setting up a query to grab data from a remote datasource and append to data in DOMO. Is there a way to grab newer records based on the data that was previously imported.

Page: DATA / <DATASET NAME> / SETTINGS / QUERY

I'd like to run something like:
SELECT *
FROM myRemoteDataSchema.myDataSet ← remote dataset
WHERE dateField > (Select max(dateField) from domo.myDataSet) ← current Domo dataset

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @JohnnyN What kind of connector are you using to pull this data? If possible, I'd suggest setting the update method of your dataset to Append, then aligning your query and update schedule so that it always pulls data since the last time the dataset ran.

    For example, if you have your connector set to run once a day then you could write your query to only pull data from the previous day.

    SELECT *
    FROM myRemoteDataSchema.myDataSet 
    WHERE dateField = CURRENT_DATE() - 1

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    @JohnnyN - Depending on how you are bringing the data into Domo, you could also change your dataset to be an upsert. If you search the knowledge base for upsert you should be able to see which connectors have this option.

    https://domo-support.domo.com/s/global-search/upsert?language=en_US

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @JohnnyN What kind of connector are you using to pull this data? If possible, I'd suggest setting the update method of your dataset to Append, then aligning your query and update schedule so that it always pulls data since the last time the dataset ran.

    For example, if you have your connector set to run once a day then you could write your query to only pull data from the previous day.

    SELECT *
    FROM myRemoteDataSchema.myDataSet 
    WHERE dateField = CURRENT_DATE() - 1

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    @JohnnyN - Depending on how you are bringing the data into Domo, you could also change your dataset to be an upsert. If you search the knowledge base for upsert you should be able to see which connectors have this option.

    https://domo-support.domo.com/s/global-search/upsert?language=en_US

  • JohnnyN
    JohnnyN Member
    edited June 2023

    Hi @MichelleH Yup I am appending I have had this method running already. But we had a problem down stream where yesterdays data failed to populated in our datawarehouse. Meaning when the import runs tomorrow I would need to fetch WHERE dateField = CURRENT_DATE() - 2

    If I could understand what date was last fetched… maybe max(_BATCH_LAST_RUN_) I could do the math to get the section of records to Append to DOMO.

    Hope that makes sense.

  • MichelleH
    MichelleH Coach

    @JohnnyN In that case I would follow @ST_-Superman-_'s suggestion in trying an upsert dataset so you can bring in a rolling X number of days without duplicating.

  • JohnnyN
    JohnnyN Member

    @MichelleH OK I didnt hear "rolling X number of days " previously. Yes that sounds like a plan.

  • JohnnyN
    JohnnyN Member

    @MichelleH Yup… I'm rolling 4 days worth of data and merging into DOMO. This should cover me for outage over the W/E maybe. As the data is an audit. Most of the historical data selected should be ignored and only new records inserted from the last inserted record.

    Seems to be working well. Thanks

Categories