CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Jerin A Mathews
Jerin A Mathews

Reputation: 8712

How to do bulk upsert operation in snowflake?

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.

Upvotes: 3

Views: 4034

Answers (2)

CMe
CMe

Reputation: 692

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

Upvotes: 1

Simon D
Simon D

Reputation: 6229

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.

Upvotes: 2

Related Questions