Append with removing rows of data

Jmoreno

I am wondering if there is way to setup the following scenario. I have an append that runs in the morning and all the new data gets added properly, however, there are situations where some of the source data is no longer needed for example if I started with 110 rows of data yesterday and throughout the day 10 were removed, then I only need 100 rows of data now. But what would happen if I also had some new rows in the source as well... for example 5 new rows, then my total show be 105

I attached a little chart for better visibility

I know a full replace will take care of this, but we are trying to minimize our row processing budget. Any solution for this or just simply the replace IS the only solution.?

Thank you

GrantSmith

Have you looked into using a recursive dataflow to "partition" your data and filter out any old records and replace them with new records?

https://domohelp.domo.com/hc/en-us/articles/360057087393-Creating-a-Recursive-Snapshot-DataFlow-in-the-New-Magic-ETL

If you're using workbench have you looked into partitions?

https://domohelp.domo.com/hc/en-us/articles/360062446514-Workbench-5-1-Partition-Support

Jmoreno

For the first one wouldn't that still require me to bring in all the data into DOMO then apply the recursion in the ETL? Which at that point I might as well just do a replace of all the data at once.

For the second one, the thing is I am not keeping history in these sources. Therefore DOMO will have more data than what I want to bring in, so when doing the append with the upsert, I was thinking if there was a way that DOMO could detect "Oh, this unique id no longer exists in the data being brought in, delete record from dataset"

GrantSmith

You'd bring in the dataset that's already in Domo, compare to the records you wish to update and filter out the ones you don't want. You'd only be importing the updated partitions into Domo and Domo would have the existing dataset already on hand

The only way Domo can handle removed records is to do a full replace or a partition where it'll fully replace part of your dataset.