Assembler Upsert Date Column

user095063

Hi. I need to take a snapshot of today's data in order to compare it with tomorrow's data once it will be available. The problem is that the SQL dataflow and Magic ETL only can use Replace method. I need to find a way how to append the data each day to the already existing historical table.

I created Assembler and I chose Assembler Type as Upsert. I got an error message. Please see below the setup and error message.

MarkSnodgrass

Just in case you aren't already aware, source datasets can be set to replace or append. They don't all have to be replace.

Additionally, you can use recursive dataflows to create an appended dataset. See these two KB articles:

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

https://domohelp.domo.com/hc/en-us/articles/360043428133-Creating-a-Recursive-Snapshot-SQL-DataFlow

GrantSmith

Hi @user095063

This is because you already have a dataset with the date listed multiple times. There are a few options to do an upsert using Magic ETL 1, Magic ETL 2.0 and MySQL. Note that as your dataset grows these will take longer to run.

Magic ETL 1.0: Creating a Recursive/Snapshot Magic ETL DataFlow

Magic ETL 2.0: Creating a Recursive/Snapshot DataFlow in Magic ETL v2 (Beta)

MySQL: Creating a Recursive/Snapshot SQL DataFlow

If you don't care about potential duplicates you could configure the Dataset Copy connector and set it to append and have it run on a daily basis then use a dataset view to append your historical and now live/historical-ish dataset if you can't recreate your historical dataset. (You may be able to configure the connector to first pull the historical dataset then change it to pull the daily dataset instead on a daily basis)

jaeW_at_Onyx

https://www.youtube.com/watch?v=YgJLijj7vmw&t=488s

you can hack Domo ETLs and make a dataflow APPEND instead of Replace.