advanced

MERGE Statement

By DataPlayArena Technical Review BoardUpdated 2026-07-237 min readRuntime Verified

Overview

The MERGE statement (or "Upsert") performs conditional INSERT, UPDATE, and DELETE operations in a single atomic query pass by comparing a target table with a source dataset.

Learning Objectives

  • Write unified MERGE INTO target USING source ON keys queries.
  • Handle WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT conditions.
  • Implement incremental dimension and fact table loading in ETL pipelines.

Detailed Concept Explanation

In data warehousing, you frequently need to synchronize a target table with incoming source data:

  • If a record exists in both target and source $ ightarrow$ UPDATE target attributes.
  • If a record exists in source but not target $ ightarrow$ INSERT a new row into target.

MERGE executes this entire operation atomically in one pass.


Code Examples

SQL

sql
-- Upsert staging data into target customer dimension table
MERGE INTO customers t
USING staging_customers s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET 
    t.email = s.email,
    t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, created_at)
  VALUES (s.customer_id, s.name, s.email, CURRENT_TIMESTAMP);

Best Practices

  • Ensure Source Uniqueness: Ensure the source dataset contains no duplicate join keys before executing MERGE. Duplicate source keys cause MERGE to throw a cardinality violation error.

Interview Perspective

Note

Interview Question: What is an Upsert in database pipelines?

"Upsert" means updating a record if it already exists or inserting it if it does not. In standard SQL, this is implemented using MERGE INTO ... USING ... ON ... WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT.


Summary

MERGE combines INSERT and UPDATE into a single atomic "Upsert" statement for data pipeline synchronization.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.