MERGE: The Upsert Pattern

Snowflake · Data Types & SQL Fundamentals

Theory

MERGE applies a source of changes to a target table in one statement. You give a join condition (ON target.emp_id = source.emp_id) and clauses for each case: WHEN MATCHED THEN UPDATE or DELETE for rows found in both, and WHEN NOT MATCHED THEN INSERT for source rows with no match. Each clause can have an extra AND condition, for example WHEN MATCHED AND source.is_deleted THEN DELETE.

If one target row matches several source rows, the result would be ambiguous, so Snowflake raises an error by default (ERROR_ON_NONDETERMINISTIC_MERGE). For example, merging staging rows for Ravi (new salary) and Karan (new hire) updates Ravi and inserts Karan in one pass.

Why we use it

Without MERGE you need separate UPDATE and INSERT statements, with the risk of missing or double-processing rows between them. MERGE does both in one atomic statement, which is why incremental loads rely on it.

When to use it

Use MERGE to load changed data (often from a stream) into a target, for upserts and for applying deletes. For pure append data, a plain INSERT is simpler and cheaper. Deduplicate the source first so each key appears once.

Matching Source to Target

MERGE INTO target USING source ON <join condition> compares every source row to the target by a key column; what happens next depends on whether a matching target row was found.

WHEN MATCHED vs WHEN NOT MATCHED

WHEN MATCHED THEN UPDATE changes an existing row's columns; WHEN NOT MATCHED THEN INSERT adds it as new. A MATCHED branch can also DELETE, useful for propagating source deletions (common when the source is a Stream).

Why This Beats Separate INSERT/UPDATE

Without MERGE you'd need to first check row existence, then branch into an INSERT or an UPDATE yourself — MERGE does that check-and-branch in one atomic statement, which is both simpler and avoids race conditions between the check and the write.

How it works

  1. Read a source row
  2. Join it to the target with the ON condition
  3. Does a target row match? Yes: Apply WHEN MATCHED: UPDATE or DELETE. No: Apply WHEN NOT MATCHED: INSERT.
  4. Do several source rows match one target row? Yes: Error by default: deduplicate the source. No: Continue with the next row.
  5. Commit all changes together

Example

MERGE INTO employees AS target
USING staging_employees AS source
ON target.emp_id = source.emp_id
WHEN MATCHED THEN
  UPDATE SET target.salary = source.salary, target.department = source.department
WHEN NOT MATCHED THEN
  INSERT (emp_id, name, department, salary)
  VALUES (source.emp_id, source.name, source.department, source.salary);

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic