ADF to Snowflake sync setup UPSERT

Question

I Azure Data Factory pipeline setup to sync data into my Snowflake account. Currently I use Snowflake V2 connector, but in my Copy Data action I can't select it to be UPSERT as INSERT duplicates entries which is not correct in my case.

How can I setup UPSERT for my data copy action?

Answer 1

Hi **Lina Vaitkiene,
**The behavior you’re seeing is expected. In Azure Data Factory, the Copy activity using Snowflake V2 connector does not support UPSERT (MERGE) operations. It only performs INSERT (append) or overwrite, which is why you are getting duplicate records.

With the Snowflake V2 connector, Copy Activity is optimized for bulk loading via staging (COPY INTO). This mechanism:

• Does not evaluate row-level conditions

Does not support keys or matching logic

Cannot perform UPDATE/MERGE semantics

Therefore, UPSERT is not supported by design in Copy activity.

1. ADF Native Approach): Mapping Data Flow

Use Data Flow with Alter Row transformation

Configure conditions like: upsertIf(true())

In Sink (Snowflake):

Enable Allow Upsert

  Define key columns
  

This internally generates a MERGE statement against Snowflake and provides true UPSERT behavior.
2. Copy Activity + Staging + MERGE (Most Common in Production)

Copy data into a staging table in Snowflake

1. Execute a MERGE statement using: Pre-copy or Post-copy script

Example:

MERGE INTO target t
USING staging s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

To achieve UPSERT in ADF with Snowflake V2, you must either use Mapping Data Flow (ADF-native MERGE) or implement staging + MERGE logic in Snowflake. Copy Activity alone cannot perform UPSERT due to its bulk-load architecture.
Reference Links :
https://learn.microsoft.com/azure/data-factory/data-flow-alter-row https://learn.microsoft.com/azure/data-factory/connector-snowflake#mapping-data-flow-properties

https://learn.microsoft.com/azure/data-factory/copy-activity-overview#pre-and-post-copy-sql-statements

Hope this helps. If you have any follow-up questions, please let me know. I would be happy to help.

Please do not forget to "up-vote" wherever the information provided helps you, as this can be beneficial to other community members.

Answer 2

Thanks, in the end I did it with the staging table and upsert stored procedure make upserts to my actual table.

Answer 3

The Copy Activity doesn't support UPSERT with Snowflake V2 . Can you try swapping it for a Mapping Data Flow, add an Alter Row transformation with upsertIf(true()) and in the Sink settings, enable Allow Upsert, and set your key column. It's a bit more setup but it works well once configured.

link https://learn.microsoft.com/en-us/azure/data-factory/data-flow-alter-row