Data Flow help

imelendez · 2021-07-07T21:07:56+00:00

There was an error rendering this rich post.

Hello, Experts,

I am humbly reaching out for some help.

I have a vendor sending a snapshot of a database table every day and not sending me diffs. I am trying to determine the diffs. The connector type is SFTP CSV. The file they send daily is overwritten daily. I currently have the data connector appending the data from the previous day to the new file they sent over that day.

Is this the correct approach? Should I have the connector setup as Append vs Replace or vice versa?

What success should look like as an output dataset that appends updated records? Below, you will find an image of 6 records that are the same as an individual in the file.

As you can see, it's the same row the only difference is the row_id which is really when the file was run and appended per the connector. So for example, imagine that these 6 rows are John Smith, what we care to see is the original John Smith record and only any new/updated changes. So if there were three changes I would only see three new records.

Thank you so much in advance. We are happy to answer any questions.

Find more posts tagged with

Magic ETL

Data Flows

Accepted answers

MarkSnodgrass

@imelendez if the rows are exactly the same, does it matter which one it keeps? If there is another column that is a date entered type column, then the rows would become unique and then both would be kept.

If you have rows that are the same and the only difference is a date entered type column, you could use the group by tile and choose min or max on the date column to get the first or last record (whichever one you are wanting to keep) and then your columns that you are looking for to be unique in the select list. This would function the same as remove duplicates but allow you to decide if you want the latest or earliest record.

All comments

MarkSnodgrass

Since you are using append in your connector, you can create a Magic ETL to give you the unique rows you are looking for. You can use the Remove Duplicates tile in Magic ETL to give you a distinct list of rows.

https://domohelp.domo.com/hc/en-us/articles/360043427373-Magic-ETL-Tiles-Edit-Data#3.

Another approach, but more involved, is to build a recursive dataflow. Here is the KB article for it.

https://domohelp.domo.com/hc/en-us/articles/360043428113-Creating-a-Recursive-Snapshot-Magic-ETL-DataFlow

Hope this helps

imelendez

Thanks for that Mark! I am currently removing duplicates and using a DOB column to remove duplicates. My question to you is, let’s say John Smith comes in a month from now through the daily pool, won’t it delete the original John Smith and leave the latest? How does remove duplicates decide? If it does, would this probably not be the best course of action?

MarkSnodgrass

imelendez

We tested what you recommended (removing duplicates) by adding in a new value to the run and adding in more date constraints to the remove dupes object and it worked like a charm.

Thank you so much @MarkSnodgrass ! You are the GOAT!

jaeW_at_Onyx

@imelendez i'm late to the party, but this video has been there for ages: https://www.youtube.com/watch?v=JNQFZCj8JcQ