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

JohnnyN

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

MichelleH

@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

@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

MichelleH

@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

@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

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

@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

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

JohnnyN

@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