How to do bulk upsert operation in snowflake?

Question

Am syncing my mongo DB data to snowflake on a daily basis using a node js script. So if a row is already existing in snowflake, then I want to replace that row with the new data, or if the row doesn't exist in snowflake then I want to insert a new row. Also, I want to do this for a lot of data.

So is there any way to do bulk upsert in snowflake? If not, then what will be the optimal way to achieve this?

The table may have millions of rows and possibly go to billions in the future.

user_3287250 · Answer

This is a typical use case for a merge statement. You can see the documentation for merge here: https://docs.snowflake.com/en/sql-reference/sql/merge.html

Using a merge statement for billions of rows can lead to some high-churn tables so it isn't ideal. It could be better if you can append to the table only and figure out the latest record with a select statement.

user_16526748 · Answer

You can bulk copy your data into a staging table then use MERGE feature in snowflake.

How to do bulk upsert operation in snowflake?

2 Answers