Skip to main content

MERGE Operations for Incremental Processing

In modern data warehousing, we rarely drop and recreate massive tables every night. Instead, we use Incremental Processing, where we only process the data that changed since the last run.

The primary SQL command used to apply these incremental changes (Updates, Inserts, and Deletes) into a target table is the MERGE statement (often colloquially called an "Upsert").

Topics Covered​

  • MERGE Statement Syntax
  • Using MERGE with Streams
  • Handling Updates (Delete+Insert Pairs)
  • Slowly Changing Dimensions (SCD Type 1 & 2)
  • Performance Considerations

MERGE Statement Syntax​

A MERGE statement joins a source table (or stream) to a target table on a specific key. Based on whether the join finds a match, it executes different DML operations.

-- Conceptual Syntax
MERGE INTO TARGET_TABLE t
USING SOURCE_TABLE s
ON t.PRIMARY_KEY = s.PRIMARY_KEY
WHEN MATCHED THEN
UPDATE SET t.VAL = s.VAL
WHEN NOT MATCHED THEN
INSERT (PRIMARY_KEY, VAL) VALUES (s.PRIMARY_KEY, s.VAL);

You can also use conditions within the MATCHED clauses, such as WHEN MATCHED AND s.STATUS = 'DELETED' THEN DELETE.

Hands-On Demonstration: Using MERGE with Streams​

Let's simulate a scenario where a bank in India uses a Snowflake Stream to capture daily changes to customer addresses, and uses a MERGE statement in a Task to incrementally update the master customer table (an SCD Type 1 pattern).

1. Database & Schema Setup​

Copy and paste the following code into your Snowflake worksheet.

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_CDC_DB;
USE DATABASE BEEMS_CDC_DB;

CREATE SCHEMA IF NOT EXISTS MERGE_LAB;
USE SCHEMA MERGE_LAB;

2. Creating the Target Table, Source, and Stream​

-- Target Table (The Master Dimension)
CREATE OR REPLACE TABLE MERGE_LAB.CUSTOMER_MASTER (
CUSTOMER_ID INT,
FULL_NAME STRING,
CITY STRING
);

-- Insert an initial baseline record
INSERT INTO MERGE_LAB.CUSTOMER_MASTER VALUES (1, 'Rohan Sharma', 'Delhi');

-- Source Table (Where raw changes arrive)
CREATE OR REPLACE TABLE MERGE_LAB.RAW_CUSTOMER_CHANGES (
CUSTOMER_ID INT,
FULL_NAME STRING,
CITY STRING,
CHANGE_TYPE STRING -- e.g., 'INSERT', 'UPDATE', 'DELETE'
);

-- Stream on the raw changes
CREATE OR REPLACE STREAM MERGE_LAB.CUSTOMER_CHANGE_STREAM ON TABLE MERGE_LAB.RAW_CUSTOMER_CHANGES;

3. Simulating Daily Changes​

Let's insert raw changes into the source table. Rohan moves to Mumbai, and a new customer, Priya, signs up.

-- Rohan Updates his address
INSERT INTO MERGE_LAB.RAW_CUSTOMER_CHANGES VALUES (1, 'Rohan Sharma', 'Mumbai', 'UPDATE');

-- Priya joins the bank
INSERT INTO MERGE_LAB.RAW_CUSTOMER_CHANGES VALUES (2, 'Priya Banerjee', 'Kolkata', 'INSERT');

4. Executing the MERGE​

Now, we consume the stream and apply the changes to the Master table using MERGE.

(To check who is running this DML to ensure they have UPDATE/INSERT privileges on the target table, run SELECT CURRENT_USER();)

MERGE INTO MERGE_LAB.CUSTOMER_MASTER target
USING MERGE_LAB.CUSTOMER_CHANGE_STREAM stream
ON target.CUSTOMER_ID = stream.CUSTOMER_ID
-- Handle Updates
WHEN MATCHED AND stream.CHANGE_TYPE = 'UPDATE' THEN
UPDATE SET
target.FULL_NAME = stream.FULL_NAME,
target.CITY = stream.CITY
-- Handle Inserts
WHEN NOT MATCHED AND stream.CHANGE_TYPE = 'INSERT' THEN
INSERT (CUSTOMER_ID, FULL_NAME, CITY)
VALUES (stream.CUSTOMER_ID, stream.FULL_NAME, stream.CITY)
-- Handle Deletes
WHEN MATCHED AND stream.CHANGE_TYPE = 'DELETE' THEN
DELETE;

-- Verify the Target Table
SELECT * FROM MERGE_LAB.CUSTOMER_MASTER ORDER BY CUSTOMER_ID;

Notice Rohan's city is now Mumbai, and Priya is added to the master table.

Handling UPDATE-as-DELETE+INSERT Pairs​

In the previous example, the source system explicitly sent a CHANGE_TYPE string. However, if your stream is tracking changes from another Snowflake table (not an external app), Snowflake represents updates natively as two rows: one with METADATA$ACTION='DELETE' and METADATA$ISUPDATE=TRUE, and one with METADATA$ACTION='INSERT' and METADATA$ISUPDATE=TRUE.

To handle this specific Snowflake metadata in a MERGE (typically for SCD Type 1):

-- Conceptual MERGE using native stream metadata
MERGE INTO MASTER_TABLE t
USING SNOWFLAKE_STREAM s
ON t.ID = s.ID
-- Ignore the "Delete" half of an update, because the "Insert" half will overwrite it anyway
WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' AND s.METADATA$ISUPDATE = FALSE THEN
DELETE -- A pure delete
WHEN MATCHED AND s.METADATA$ACTION = 'INSERT' THEN
UPDATE SET t.VAL = s.VAL -- Applies to both pure inserts that match, and the insert-half of an update
WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT' THEN
INSERT (ID, VAL) VALUES (s.ID, s.VAL); -- Pure new records

Performance Considerations​

A MERGE statement is a heavy operation because Snowflake must scan the target table to find matching rows. If your target table has 10 billion rows, a MERGE can be incredibly slow if not optimized.

  1. Clustering Keys: You should strongly consider adding a Clustering Key on the column used in the ON clause (e.g., CLUSTER BY (CUSTOMER_ID)). This allows Snowflake to prune micro-partitions and only scan the parts of the 10-billion row table that actually contain the IDs being updated.
  2. Dynamic Tables: If your MERGE statement is overly complex and causing performance bottlenecks, consider rewriting the logic using a declarative Dynamic Table, which handles the incremental processing under the hood using an optimized engine.

Summary​

  • MERGE executes Updates, Inserts, and Deletes in a single atomic transaction.
  • When combining Streams and Tasks, MERGE is the standard way to apply CDC changes.
  • Ensure your MERGE logic explicitly handles the METADATA$ACTION and METADATA$ISUPDATE flags correctly.
  • Use Clustering Keys on the join column to prevent massive full-table scans during the merge.