In this post we are going to cover the MERGE statement in Snowflake.
MERGE takes a target table and a source of rows, joins them, and then does one of three things to each row depending on whether the join found a partner. Everybody learns it as update if it exists, insert if it does not.
That sentence hides the only question that actually matters: exists according to what? We will start by writing the join out by hand, before the word MERGE appears, and build it up step by step from there.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 119 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data
- Let's write the join out before we write the MERGE
- Now let's write the MERGE everybody writes
- Now let's only update the open ones
- Version one: the test goes in the ON clause
- Version two: the same test in WHEN MATCHED AND
- Now let's look at what each version produced
- Now the refusals, because with MERGE they are the spec
- And two things I expected to go the other way
- What happens when the source has no rows at all?
- One last thing: MERGE will not target a view
Let's create some sample data
We need two tables. One we already have, and one holding the rows arriving.
-- Sample data for this post. Run it once in a worksheet.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
-- The table we already have.
CREATE OR REPLACE TABLE CUSTOMERS_T119 (
CUSTOMER_ID INT,
NAME VARCHAR(20),
CITY VARCHAR(20),
STATUS VARCHAR(10),
UPDATED_BY VARCHAR(20)
);
INSERT INTO CUSTOMERS_T119 (CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY) VALUES
(1, 'acme', 'Lisbon', 'open', 'initial load'),
(2, 'borealis', 'Oslo', 'open', 'initial load'),
(3, 'cobalt', 'Berlin', 'closed', 'initial load'),
(4, 'delta', 'Porto', 'open', 'initial load');
-- The rows arriving.
CREATE OR REPLACE TABLE INCOMING_T119 (
CUSTOMER_ID INT,
NAME VARCHAR(20),
CITY VARCHAR(20)
);
INSERT INTO INCOMING_T119 (CUSTOMER_ID, NAME, CITY) VALUES
(1, 'acme', 'Madrid'),
(3, 'cobalt', 'Munich'),
(4, 'delta', 'Faro'),
(5, 'evergreen', 'Dublin');Here is the table we already have.
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM CUSTOMERS_T119 ORDER BY CUSTOMER_ID;| CUSTOMER_ID | NAME | CITY | STATUS | UPDATED_BY |
|---|---|---|---|---|
| 1 | acme | Lisbon | open | initial load |
| 2 | borealis | Oslo | open | initial load |
| 3 | cobalt | Berlin | closed | initial load |
| 4 | delta | Porto | open | initial load |
Four customers. Have a look at customer 3, cobalt. Its status is closed, and everything interesting in this post happens to that one row.
And here is what is arriving.
SELECT CUSTOMER_ID, NAME, CITY FROM INCOMING_T119 ORDER BY CUSTOMER_ID;| CUSTOMER_ID | NAME | CITY |
|---|---|---|
| 1 | acme | Madrid |
| 3 | cobalt | Munich |
| 4 | delta | Faro |
| 5 | evergreen | Dublin |
Four rows. Three of them we already have, with a new city each. One of them, evergreen, we have never seen before.
Let's write the join out before we write the MERGE
MERGE is a join with verbs hanging off it. So before using it, let's do the join by hand and look at it: a left join from the source to the target, on the customer id.
SELECT S.CUSTOMER_ID AS SOURCE_ID,
T.CUSTOMER_ID AS TARGET_ID,
T.STATUS AS TARGET_STATUS,
IFF(T.CUSTOMER_ID IS NULL, 'not matched', 'matched') AS WHICH_BRANCH
FROM INCOMING_T119 S
LEFT JOIN CUSTOMERS_T119 T ON T.CUSTOMER_ID = S.CUSTOMER_ID
ORDER BY S.CUSTOMER_ID;| SOURCE_ID | TARGET_ID | TARGET_STATUS | WHICH_BRANCH |
|---|---|---|---|
| 1 | 1 | open | matched |
| 3 | 3 | closed | matched |
| 4 | 4 | open | matched |
| 5 | NULL | NULL | not matched |
Three source rows found a partner. One did not, and its target columns came back NULL because there was nothing on the other side to fill them.
That is the whole vocabulary. Matched means the join found a partner. Not matched means it did not. Nothing more than that.
Now let's write the MERGE everybody writes
We join on the customer id. When a row is matched we update the city, and when it is not matched we insert it.
MERGE INTO CUSTOMERS_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN MATCHED THEN
UPDATE SET T.CITY = S.CITY, T.UPDATED_BY = 'merge'
WHEN NOT MATCHED THEN
INSERT (CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY)
VALUES (S.CUSTOMER_ID, S.NAME, S.CITY, 'open', 'merge');| number of rows inserted | number of rows updated |
|---|---|
| 1 | 3 |
Two columns this time, one per verb. One row inserted and three updated, which adds up to the four rows that arrived.
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM CUSTOMERS_T119 ORDER BY CUSTOMER_ID;| CUSTOMER_ID | NAME | CITY | STATUS | UPDATED_BY |
|---|---|---|---|---|
| 1 | acme | Madrid | open | merge |
| 2 | borealis | Oslo | open | initial load |
| 3 | cobalt | Munich | closed | merge |
| 4 | delta | Faro | open | merge |
| 5 | evergreen | Dublin | open | merge |
Five rows. Evergreen has arrived with a city, a status of open and merge in the UPDATED_BY column. Three cities changed. Borealis was not in the file at all, so it is untouched.
Let's count that rather than read it off the grid.
SELECT COUNT(*) AS ROWS_TOTAL,
SUM(IFF(UPDATED_BY = 'merge', 1, 0)) AS ROWS_THE_MERGE_TOUCHED,
SUM(IFF(UPDATED_BY = 'initial load', 1, 0)) AS ROWS_IT_LEFT_ALONE
FROM CUSTOMERS_T119;| ROWS_TOTAL | ROWS_THE_MERGE_TOUCHED | ROWS_IT_LEFT_ALONE |
|---|---|---|
| 5 | 4 | 1 |
Five rows in the table, four of them touched by the merge, one left alone. That is MERGE as everybody teaches it, and none of it is wrong.
Now let's only update the open ones
Remember customer 3 is closed. Say we do not want closed customers updated by the feed. That is a reasonable thing to want.
So we add a test: AND T.STATUS = 'open'. The question is where to put it. There are two places Snowflake will accept it, and they are not the same.
- in the
ONclause, next to the id comparison - in the branch itself, as
WHEN MATCHED AND ...
Let's make two identical copies of the table so the comparison is fair, and run one version against each.
-- Two identical copies, so the comparison is fair.
CREATE OR REPLACE TABLE IN_ON_T119 (
CUSTOMER_ID INT,
NAME VARCHAR(20),
CITY VARCHAR(20),
STATUS VARCHAR(10),
UPDATED_BY VARCHAR(20)
);
CREATE OR REPLACE TABLE IN_WHEN_T119 (
CUSTOMER_ID INT,
NAME VARCHAR(20),
CITY VARCHAR(20),
STATUS VARCHAR(10),
UPDATED_BY VARCHAR(20)
);
INSERT INTO IN_ON_T119 (CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY) VALUES
(1, 'acme', 'Lisbon', 'open', 'initial load'),
(2, 'borealis', 'Oslo', 'open', 'initial load'),
(3, 'cobalt', 'Berlin', 'closed', 'initial load'),
(4, 'delta', 'Porto', 'open', 'initial load');
INSERT INTO IN_WHEN_T119 (CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY) VALUES
(1, 'acme', 'Lisbon', 'open', 'initial load'),
(2, 'borealis', 'Oslo', 'open', 'initial load'),
(3, 'cobalt', 'Berlin', 'closed', 'initial load'),
(4, 'delta', 'Porto', 'open', 'initial load');SELECT 'in the ON clause' AS WHICH_COPY, CUSTOMER_ID, CITY, STATUS FROM IN_ON_T119
UNION ALL
SELECT 'in the WHEN clause', CUSTOMER_ID, CITY, STATUS FROM IN_WHEN_T119
ORDER BY WHICH_COPY, CUSTOMER_ID;| WHICH_COPY | CUSTOMER_ID | CITY | STATUS |
|---|---|---|---|
| in the ON clause | 1 | Lisbon | open |
| in the ON clause | 2 | Oslo | open |
| in the ON clause | 3 | Berlin | closed |
| in the ON clause | 4 | Porto | open |
| in the WHEN clause | 1 | Lisbon | open |
| in the WHEN clause | 2 | Oslo | open |
| in the WHEN clause | 3 | Berlin | closed |
| in the WHEN clause | 4 | Porto | open |
Same four rows in each, Berlin still closed in both.
Version one: the test goes in the ON clause
Here the ON clause says match on the id and the target status is open.
MERGE INTO IN_ON_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID AND T.STATUS = 'open'
WHEN MATCHED THEN
UPDATE SET T.CITY = S.CITY, T.UPDATED_BY = 'merge'
WHEN NOT MATCHED THEN
INSERT (CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY)
VALUES (S.CUSTOMER_ID, S.NAME, S.CITY, 'open', 'merge');| number of rows inserted | number of rows updated |
|---|---|
| 2 | 2 |
Two inserted, two updated. Stop at that first number. There was only one new customer in the file.
Version two: the same test in WHEN MATCHED AND
Now the same three words, moved. The ON clause is just the id, and the test hangs off the matched branch instead.
MERGE INTO IN_WHEN_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN MATCHED AND T.STATUS = 'open' THEN
UPDATE SET T.CITY = S.CITY, T.UPDATED_BY = 'merge'
WHEN NOT MATCHED THEN
INSERT (CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY)
VALUES (S.CUSTOMER_ID, S.NAME, S.CITY, 'open', 'merge');| number of rows inserted | number of rows updated |
|---|---|
| 1 | 2 |
One inserted, two updated. One is the number we expected, because one new customer arrived.
Both statements succeeded. Both reports look fine. Let's look at what is actually in the two tables.
Now let's look at what each version produced
First the ON clause version.
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM IN_ON_T119 ORDER BY CUSTOMER_ID, UPDATED_BY;| CUSTOMER_ID | NAME | CITY | STATUS | UPDATED_BY |
|---|---|---|---|---|
| 1 | acme | Madrid | open | merge |
| 2 | borealis | Oslo | open | initial load |
| 3 | cobalt | Berlin | closed | initial load |
| 3 | cobalt | Munich | open | merge |
| 4 | delta | Faro | open | merge |
| 5 | evergreen | Dublin | open | merge |
Six rows, and customer 3 is in there twice. Berlin, closed, from the initial load. And Munich, open, from the merge.
Because customer 3 is closed, the ON clause found no partner for it. No partner means not matched. And not matched means insert. So it inserted a second customer 3, and said nothing at all.
Now the WHEN clause version.
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM IN_WHEN_T119 ORDER BY CUSTOMER_ID, UPDATED_BY;| CUSTOMER_ID | NAME | CITY | STATUS | UPDATED_BY |
|---|---|---|---|---|
| 1 | acme | Madrid | open | merge |
| 2 | borealis | Oslo | open | initial load |
| 3 | cobalt | Berlin | closed | initial load |
| 4 | delta | Faro | open | merge |
| 5 | evergreen | Dublin | open | merge |
Five rows, and customer 3 appears once, still Berlin, still closed, still from the initial load. It was matched, and the condition on the branch skipped it.
Let's count customer 3 in each table rather than trusting our eyes.
SELECT 'in the ON clause' AS WHICH_COPY,
COUNT(*) AS ROWS_FOR_CUSTOMER_3
FROM IN_ON_T119 WHERE CUSTOMER_ID = 3
UNION ALL
SELECT 'in the WHEN clause', COUNT(*)
FROM IN_WHEN_T119 WHERE CUSTOMER_ID = 3;| WHICH_COPY | ROWS_FOR_CUSTOMER_3 |
|---|---|
| in the ON clause | 2 |
| in the WHEN clause | 1 |
SELECT (SELECT COUNT(*) FROM IN_ON_T119) AS ROWS_WITH_IT_IN_THE_ON_CLAUSE,
(SELECT COUNT(*) FROM IN_WHEN_T119) AS ROWS_WITH_IT_IN_THE_WHEN_CLAUSE;| ROWS_WITH_IT_IN_THE_ON_CLAUSE | ROWS_WITH_IT_IN_THE_WHEN_CLAUSE |
|---|---|
| 6 | 5 |
Two and one. Six rows and five rows, from identical starting data, with three words in a different place.
A condition in the ON clause does not filter rows out. It makes them not matched, and a not-matched row gets inserted. So the ON clause is for identity — the columns that say which target row this source row is. Everything else belongs in a WHEN.
Now the refusals, because with MERGE they are the spec
A not-matched row has nothing to update, because there is no target row yet. Let's try it anyway.
MERGE INTO CUSTOMERS_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN NOT MATCHED THEN
UPDATE SET T.CITY = S.CITY;002048 (42601): SQL compilation error: error line 5 at position 5 NOT MATCHED clause in MERGE statement must be followed by INSERT clause.
Refused, and the message states the rule in words.
And the mirror image: an insert in a matched branch.
MERGE INTO CUSTOMERS_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN MATCHED THEN
INSERT (CUSTOMER_ID) VALUES (S.CUSTOMER_ID);002047 (42601): SQL compilation error: error line 5 at position 5 MATCHED clause in MERGE statement must be followed by UPDATE or DELETE clause.
Also refused, also stating the rule. There is nothing to memorise here. Snowflake says it for you.
And two things I expected to go the other way
First, two WHEN NOT MATCHED branches with no condition on either of them. The second could never fire, because the first one takes every not-matched row. I expected a refusal but did not know what it would say.
MERGE INTO CUSTOMERS_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN NOT MATCHED THEN
INSERT (CUSTOMER_ID, NAME) VALUES (S.CUSTOMER_ID, S.NAME)
WHEN NOT MATCHED THEN
INSERT (CUSTOMER_ID, NAME) VALUES (S.CUSTOMER_ID, 'second branch');002045 (42601): SQL compilation error: error line 6 at position 5 Unreachable merge case.
Unreachable merge case. That is better than I expected. Snowflake reads the branches and refuses a dead one before running anything.
Second, WHEN NOT MATCHED BY SOURCE. That is the clause for rows which are in the target but have gone from the feed, and it is how you would handle deletions in a full refresh. I believed Snowflake had it.
MERGE INTO CUSTOMERS_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET T.UPDATED_BY = 'gone from the source';001003 (42000): SQL compilation error: syntax error line 4 at position 17 unexpected 'BY'.
It does not exist here. A syntax error on the word BY. So a full-refresh load has to be shaped another way in Snowflake, and it is better to find that out now than in a deployment.
What happens when the source has no rows at all?
A feed that arrives empty is a normal Tuesday. Let's see what MERGE makes of it.
MERGE INTO CUSTOMERS_T119 AS T
USING (SELECT 1 AS CUSTOMER_ID, 'x' AS NAME, 'y' AS CITY WHERE 1 = 0) AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN MATCHED THEN
UPDATE SET T.UPDATED_BY = 'should not happen'
WHEN NOT MATCHED THEN
INSERT (CUSTOMER_ID, NAME) VALUES (S.CUSTOMER_ID, S.NAME);| number of rows inserted | number of rows updated |
|---|---|
| 0 | 0 |
Zero and zero, and not an error.
SELECT COUNT(*) AS ROWS_PRESENT,
SUM(IFF(UPDATED_BY = 'should not happen', 1, 0)) AS ROWS_IT_TOUCHED
FROM CUSTOMERS_T119;| ROWS_PRESENT | ROWS_IT_TOUCHED |
|---|---|
| 5 | 0 |
Five rows still there, and not one of them carries the marker the matched branch would have written. Nothing happened at all.
One last thing: MERGE will not target a view
Let's make a view over the open customers and merge into it.
CREATE OR REPLACE VIEW OPEN_CUSTOMERS_T119 AS
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM CUSTOMERS_T119 WHERE STATUS = 'open';| status |
|---|
| View OPEN_CUSTOMERS_T119 successfully created. |
MERGE INTO OPEN_CUSTOMERS_T119 AS T
USING INCOMING_T119 AS S
ON T.CUSTOMER_ID = S.CUSTOMER_ID
WHEN MATCHED THEN
UPDATE SET T.CITY = S.CITY;001759 (42601): SQL compilation error: MERGE statement's target must be a table
Refused, and it is the same shape of refusal DELETE gave us in the last post. The target must be a table.
Then everything this post made gets dropped, and the last query counts what was left behind.
-- Put the schema back the way we found it.
DROP VIEW IF EXISTS OPEN_CUSTOMERS_T119;
DROP TABLE IF EXISTS CUSTOMERS_T119;
DROP TABLE IF EXISTS INCOMING_T119;
DROP TABLE IF EXISTS IN_ON_T119;
DROP TABLE IF EXISTS IN_WHEN_T119;SELECT CURRENT_DATABASE() AS THE_DATABASE,
CURRENT_SCHEMA() AS THE_SCHEMA,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
WHERE RIGHT(TABLE_NAME, 5) = '_T119') AS PROBE_TABLES_LEFT,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS
WHERE RIGHT(TABLE_NAME, 5) = '_T119') AS PROBE_VIEWS_LEFT,
(SELECT COUNT(*) FROM CUSTOMERS) AS DEMO_DB_IS_READABLE;| THE_DATABASE | THE_SCHEMA | PROBE_TABLES_LEFT | PROBE_VIEWS_LEFT | DEMO_DB_IS_READABLE |
|---|---|---|---|---|
| DEMO_DB | PUBLIC | 0 | 0 | 10 |
No probe tables, no probe views, and the rest of the database still readable.
Key points
MERGEis a join with three verbs hanging off it. Matched means the join found a partner. Not matched means it did not.- The report has one column per verb it used, and the numbers add up to the source rows it processed.
- A condition in the
ONclause does not filter rows out. It makes them not matched, and a not-matched row gets inserted. - The identical test written as
WHEN MATCHED AND ...skips the row instead. Six rows one way, five the other. - Neither version warned about it. Both reports looked fine.
- So the
ONclause is for identity. Everything else belongs in aWHEN. NOT MATCHEDmust be followed byINSERT.MATCHEDmust be followed byUPDATEorDELETE. Both refusals say so.- Two unconditional
NOT MATCHEDbranches are refused at compile time with Unreachable merge case. WHEN NOT MATCHED BY SOURCEis not supported here.- An empty source is zero and zero, and not an error. A view is not a valid target.
Episode 120 — MERGE, part two. Branch order, a DELETE branch alongside the other two, and a source that matches the same target row twice — including the dead branch Snowflake refuses and the one it quietly accepts.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The customer data is made up for teaching. The four refusals are real captured errors and are shown as they came back. All the _T119 objects are created and dropped by the script. Cells reading NULL are real nulls. Nothing here is timed and no performance claims are made anywhere in this series. Clause behaviour follows the Snowflake MERGE documentation.



No comments:
Post a Comment
Note: Only a member of this blog may post a comment.