Snowflake SQL Tutorial 119 - MERGE - A Condition In The ON Clause Duplicated A Row

Snowflake SQL · Episode 119

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

We need two tables. One we already have, and one holding the rows arriving.

Sample data
-- 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.

The target
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM CUSTOMERS_T119 ORDER BY CUSTOMER_ID;
Result4 rows · 5 columns
CUSTOMER_IDNAMECITYSTATUSUPDATED_BY
1acmeLisbonopeninitial load
2borealisOsloopeninitial load
3cobaltBerlinclosedinitial load
4deltaPortoopeninitial 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.

The source
SELECT CUSTOMER_ID, NAME, CITY FROM INCOMING_T119 ORDER BY CUSTOMER_ID;
Result4 rows · 3 columns
CUSTOMER_IDNAMECITY
1acmeMadrid
3cobaltMunich
4deltaFaro
5evergreenDublin

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.

Query
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;
Result4 rows · 4 columns
SOURCE_IDTARGET_IDTARGET_STATUSWHICH_BRANCH
11openmatched
33closedmatched
44openmatched
5NULLNULLnot 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.

Query
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');
Result1 row · 2 columns
number of rows insertednumber of rows updated
13

Two columns this time, one per verb. One row inserted and three updated, which adds up to the four rows that arrived.

And the table
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM CUSTOMERS_T119 ORDER BY CUSTOMER_ID;
Result5 rows · 5 columns
CUSTOMER_IDNAMECITYSTATUSUPDATED_BY
1acmeMadridopenmerge
2borealisOsloopeninitial load
3cobaltMunichclosedmerge
4deltaFaroopenmerge
5evergreenDublinopenmerge

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.

Counted
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;
Result1 row · 3 columns
ROWS_TOTALROWS_THE_MERGE_TOUCHEDROWS_IT_LEFT_ALONE
541

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 ON clause, 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 copies
-- 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');
Both copies, before anything runs
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;
Result8 rows · 4 columns
WHICH_COPYCUSTOMER_IDCITYSTATUS
in the ON clause1Lisbonopen
in the ON clause2Osloopen
in the ON clause3Berlinclosed
in the ON clause4Portoopen
in the WHEN clause1Lisbonopen
in the WHEN clause2Osloopen
in the WHEN clause3Berlinclosed
in the WHEN clause4Portoopen

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.

Query
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');
Result1 row · 2 columns
number of rows insertednumber of rows updated
22

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.

Query
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');
Result1 row · 2 columns
number of rows insertednumber of rows updated
12

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.

The ON clause copy
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM IN_ON_T119 ORDER BY CUSTOMER_ID, UPDATED_BY;
Result6 rows · 5 columns
CUSTOMER_IDNAMECITYSTATUSUPDATED_BY
1acmeMadridopenmerge
2borealisOsloopeninitial load
3cobaltBerlinclosedinitial load
3cobaltMunichopenmerge
4deltaFaroopenmerge
5evergreenDublinopenmerge

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.

The WHEN clause copy
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM IN_WHEN_T119 ORDER BY CUSTOMER_ID, UPDATED_BY;
Result5 rows · 5 columns
CUSTOMER_IDNAMECITYSTATUSUPDATED_BY
1acmeMadridopenmerge
2borealisOsloopeninitial load
3cobaltBerlinclosedinitial load
4deltaFaroopenmerge
5evergreenDublinopenmerge

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.

Counted
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;
Result2 rows · 2 columns
WHICH_COPYROWS_FOR_CUSTOMER_3
in the ON clause2
in the WHEN clause1
And the totals
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;
Result1 row · 2 columns
ROWS_WITH_IT_IN_THE_ON_CLAUSEROWS_WITH_IT_IN_THE_WHEN_CLAUSE
65

Two and one. Six rows and five rows, from identical starting data, with three words in a different place.

The rule to take away from this post

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.

Query
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;
Query failed — Snowflake returned an error
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.

Query
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);
Query failed — Snowflake returned an error
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.

Query
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');
Query failed — Snowflake returned an error
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.

Query
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';
Query failed — Snowflake returned an error
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.

Query
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);
Result1 row · 2 columns
number of rows insertednumber of rows updated
00

Zero and zero, and not an error.

And the table
SELECT COUNT(*)                                       AS ROWS_PRESENT,
       SUM(IFF(UPDATED_BY = 'should not happen', 1, 0)) AS ROWS_IT_TOUCHED
FROM CUSTOMERS_T119;
Result1 row · 2 columns
ROWS_PRESENTROWS_IT_TOUCHED
50

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.

A view
CREATE OR REPLACE VIEW OPEN_CUSTOMERS_T119 AS
SELECT CUSTOMER_ID, NAME, CITY, STATUS, UPDATED_BY
FROM CUSTOMERS_T119 WHERE STATUS = 'open';
Result1 row · 1 column
status
View OPEN_CUSTOMERS_T119 successfully created.
Query
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;
Query failed — Snowflake returned an error
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.

Cleanup
-- 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;
The last check
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;
Result1 row · 5 columns
THE_DATABASETHE_SCHEMAPROBE_TABLES_LEFTPROBE_VIEWS_LEFTDEMO_DB_IS_READABLE
DEMO_DBPUBLIC0010

No probe tables, no probe views, and the rest of the database still readable.

Key points

  • MERGE is 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 ON clause 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 ON clause is for identity. Everything else belongs in a WHEN.
  • NOT MATCHED must be followed by INSERT. MATCHED must be followed by UPDATE or DELETE. Both refusals say so.
  • Two unconditional NOT MATCHED branches are refused at compile time with Unreachable merge case.
  • WHEN NOT MATCHED BY SOURCE is not supported here.
  • An empty source is zero and zero, and not an error. A view is not a valid target.
Next in this series

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.