Snowflake DML and MERGE

Data changes every day. New customers sign up, prices update, and old records disappear. Data Manipulation Language, known as DML, covers the commands that add, change, and remove rows. Mastering them keeps tables accurate and pipelines reliable.

The Five Core Commands

COMMAND     WHAT IT DOES                          REAL-LIFE PICTURE
---------   -----------------------------------   -------------------------
INSERT      Adds new rows                         Writing a new line in a ledger
UPDATE      Changes values in existing rows       Correcting an old entry
DELETE      Removes selected rows                 Crossing out entries
TRUNCATE    Removes all rows, keeps the table     Wiping a whiteboard clean
MERGE       Inserts, updates, deletes in one step Syncing two address books

INSERT

INSERT adds rows from typed values or from a query. Loading from a query copies data between tables in a single statement.

-- Add one row
INSERT INTO customers (customer_id, name, city)
VALUES (4, 'Kiran', 'Pune');

-- Add several rows at once
INSERT INTO customers (customer_id, name, city)
VALUES (5, 'Dev', 'Delhi'), (6, 'Nisha', 'Jaipur');

-- Copy rows from another table
INSERT INTO customers_archive
SELECT * FROM customers WHERE city = 'Pune';

-- Replace the entire table content
INSERT OVERWRITE INTO customers_archive
SELECT * FROM customers;

UPDATE and DELETE

Both commands need a WHERE clause. Without one, every row changes or disappears.

UPDATE customers
SET city = 'Mumbai'
WHERE customer_id = 4;

DELETE FROM customers
WHERE customer_id = 6;

Snowflake never edits a stored file in place. Each change writes new micro-partitions and marks old ones as replaced. Time Travel keeps the old versions available for recovery during the retention period.

TRUNCATE Versus DELETE

DELETE without a WHERE clause removes rows and records every change. TRUNCATE empties the table instantly and keeps its structure. Use TRUNCATE for staging tables that reload fully each night.

MERGE: The Upsert Command

An upsert means update when the row exists and insert when it does not. MERGE performs that logic in one statement. Picture two address books. One holds your current contacts, and the other holds a fresh export from a phone. MERGE walks through the fresh list. It updates people already in your book, adds new people, and removes people marked as deleted.

MERGE FLOW
==========

 SOURCE (new data)           TARGET (existing table)
 +----+-------+              +----+-------+
 | 1  | Asha  |  matches --> | 1  | Asha  |  --> UPDATE
 | 2  | Ravi  |  matches --> | 2  | Rav   |  --> UPDATE
 | 7  | Tara  |  no match    |    |       |  --> INSERT
 +----+-------+              +----+-------+

MERGE INTO customers t
USING customer_updates s
  ON t.customer_id = s.customer_id
WHEN MATCHED AND s.is_deleted = TRUE THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET t.name = s.name,
             t.city = s.city
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, city)
  VALUES (s.customer_id, s.name, s.city);

The ON clause defines how rows match. Snowflake evaluates the WHEN clauses from top to bottom, so the most specific condition belongs first.

Avoiding the Duplicate Source Problem

A source with two rows for the same key creates an ambiguous match. Snowflake may raise an error or apply an unpredictable result, depending on the ERROR_ON_NONDETERMINISTIC_MERGE setting. Remove duplicates before merging.

MERGE INTO customers t
USING (
  SELECT *
  FROM customer_updates
  QUALIFY ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC) = 1
) s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (customer_id, name) VALUES (s.customer_id, s.name);

Transactions

Every DML statement runs in a transaction. Auto-commit finishes each statement immediately. Wrap several statements between BEGIN and COMMIT when they must succeed or fail together.

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 10;
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 20;
COMMIT;

ROLLBACK cancels every change inside the open transaction.

A Practical Scenario

A company receives a customer file from its sales system every night. The file holds new customers, changed addresses, and records marked as closed. An engineer loads the file into a staging table, removes duplicate keys, and runs one MERGE statement against the customer table. New people appear, changed people update, and closed people disappear. The whole job finishes in seconds and leaves the table in a consistent state.

Frequently Asked Questions

Can I undo a wrong UPDATE?

Time Travel lets you query or clone the table as it looked before the change, as long as the retention period has not passed.

Does TRUNCATE reset anything else?

TRUNCATE removes all rows and keeps column definitions, grants, and other table settings. Load history for the table resets as well.

Key Points

• INSERT, UPDATE, DELETE, TRUNCATE, and MERGE form the DML toolkit.
• UPDATE and DELETE need a WHERE clause to avoid touching every row.
• MERGE combines insert, update, and delete logic in one statement.
• Deduplicate the source before a MERGE to keep results predictable.
• Transactions group statements so they succeed or fail together.
• Time Travel protects against accidental changes for the retention period.