Append with removing rows of data

Jmoreno
Jmoreno Member
in Workbench

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

image.png


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

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Jmoreno
    Jmoreno Member

    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
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories