MERGE Statement

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

-- 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

Summary

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