Snowflake Update/Insert

Hello,

I am new in EasyMorph, I am enjoying discovering all the available actions and how they are useful for our use cases.

Need your advice to find the best way to do the following:

Use case: We have a daily process that is moving data to Snowflake based on Last Update Date, daily process is taking all registers that were updated or modified since Yesterday, then I need to insert in case the register does not exist or update in case register exists, based on an ID column.

I found a way to do that follwoing the next steps:

  1. Move data to temporal table in Snowflake (only what was changed/modified since yesterday).
  2. Run a custom script using MERGE (Snowflake command) to update/insert data.

My question is, that is my best option?

Thank you in advance for your help

Hi Luis and welcome to the Community!

While there is no “UPSERT” action in EasyMorph, the canonical way of doing upserts is to derive two tables, and then use the “Keep/remove matching” action to keep records that already exist (and need to be updated) in one derived table, and new records (that need to be inserted) in the other derived table. Then use the “Update” action in one derived table, and “Export to database” in the other.

Also, to check whether a record ID exists in Snowflake, you can use the “Select matching DB rows” action.

Hi Dmitry,
Great, I will follow your instructions.

Thank you