Snowflake SQL Tutorial 120 - MERGE Refuses The Obvious Dead Branch And Accepts The Subtle One

Snowflake SQL · Episode 120

In this post we are going to cover the rest of MERGE in Snowflake: more than one verb in a single statement, the order you write the branches in, and a source that matches the same target row twice.

MERGE takes the first branch whose condition is true for a row. So the order you write them in is logic, not taste, and this post is mostly about what Snowflake will and will not catch when you get that order wrong.

We will start with one statement that updates, deletes and inserts at the same time, and build it up step by step.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back — including the run where I broke my own demo file, which is the last section.

Episode 120 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

Let's create some sample data

A stock table, and a change feed whose rows each carry an action: either upsert or remove.

Sample data
-- Sample data for this post. Run it once in a worksheet.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- What is on the shelves.
CREATE OR REPLACE TABLE STOCK_T120 (
    SKU    VARCHAR(10),
    QTY    INT,
    SHELF  VARCHAR(10),
    NOTE   VARCHAR(30)
);

INSERT INTO STOCK_T120 (SKU, QTY, SHELF, NOTE) VALUES
    ('A100', 12, 'front', 'initial load'),
    ('B200',  0, 'front', 'initial load'),
    ('C300',  7, 'back',  'initial load'),
    ('D400', 40, 'back',  'initial load'),
    ('E500',  3, 'front', 'initial load');

-- The change feed. Every row carries an action.
CREATE OR REPLACE TABLE FEED_T120 (
    SKU     VARCHAR(10),
    QTY     INT,
    ACTION  VARCHAR(10)
);

INSERT INTO FEED_T120 (SKU, QTY, ACTION) VALUES
    ('A100', 25, 'upsert'),
    ('C300',  0, 'upsert'),
    ('D400',  0, 'remove'),
    ('F600', 15, 'upsert');
The stock table
SELECT SKU, QTY, SHELF, NOTE FROM STOCK_T120 ORDER BY SKU;
Result5 rows · 4 columns
SKUQTYSHELFNOTE
A10012frontinitial load
B2000frontinitial load
C3007backinitial load
D40040backinitial load
E5003frontinitial load
The change feed
SELECT SKU, QTY, ACTION FROM FEED_T120 ORDER BY SKU;
Result4 rows · 3 columns
SKUQTYACTION
A10025upsert
C3000upsert
D4000remove
F60015upsert

Five items on the shelves and four changes arriving. Three of the feed rows are upserts and one asks for a row to be removed. F600 is not on the shelves at all.

Let's start with one MERGE that does all three jobs

One statement, three branches, and they are read from the top. When a row is matched and the action is remove, delete it. When a row is matched at all, update it. When a row is not matched and the action is upsert, insert it.

Query
MERGE INTO STOCK_T120 AS T
USING FEED_T120 AS S
   ON T.SKU = S.SKU
WHEN MATCHED AND S.ACTION = 'remove' THEN
     DELETE
WHEN MATCHED THEN
     UPDATE SET T.QTY = S.QTY, T.NOTE = 'updated by merge'
WHEN NOT MATCHED AND S.ACTION = 'upsert' THEN
     INSERT (SKU, QTY, SHELF, NOTE)
     VALUES (S.SKU, S.QTY, 'unsorted', 'inserted by merge');
Result1 row · 3 columns
number of rows insertednumber of rows updatednumber of rows deleted
121

Three numbers back, a column per verb. That is the whole report.

And the table
SELECT SKU, QTY, SHELF, NOTE FROM STOCK_T120 ORDER BY SKU;
Result5 rows · 4 columns
SKUQTYSHELFNOTE
A10025frontupdated by merge
B2000frontinitial load
C3000backupdated by merge
E5003frontinitial load
F60015unsortedinserted by merge

A100 and C300 were updated by the merge. F600 is new, and landed on a shelf called unsorted because the insert branch spells that value out. And D400, which asked to be removed, is gone.

Let's count it instead of reading it.

Counted
SELECT COUNT(*)                                     AS ROWS_TOTAL,
       SUM(IFF(NOTE = 'updated by merge', 1, 0))    AS ROWS_UPDATED,
       SUM(IFF(NOTE = 'inserted by merge', 1, 0))   AS ROWS_INSERTED,
       SUM(IFF(SKU = 'D400', 1, 0))                 AS THE_REMOVED_SKU_STILL_HERE
FROM STOCK_T120;
Result1 row · 4 columns
ROWS_TOTALROWS_UPDATEDROWS_INSERTEDTHE_REMOVED_SKU_STILL_HERE
5210

The last column is the one that matters. It asks whether the row that asked to be removed is still here, and the answer is zero. Put that check at the end of every change-feed merge you write.

Now let's look at branch order

The delete branch above was written first and carried a condition. The update branch below it had none. Let's find out whether that order matters.

Two identical copies again, three rows each.

Two copies
-- Two identical copies, so the comparison is fair.
CREATE OR REPLACE TABLE ORDER_A_T120 (
    SKU   VARCHAR(10),
    QTY   INT,
    NOTE  VARCHAR(30)
);

CREATE OR REPLACE TABLE ORDER_B_T120 (
    SKU   VARCHAR(10),
    QTY   INT,
    NOTE  VARCHAR(30)
);

INSERT INTO ORDER_A_T120 (SKU, QTY, NOTE) VALUES
    ('A100', 12, 'initial load'), ('C300', 7, 'initial load'), ('D400', 40, 'initial load');

INSERT INTO ORDER_B_T120 (SKU, QTY, NOTE) VALUES
    ('A100', 12, 'initial load'), ('C300', 7, 'initial load'), ('D400', 40, 'initial load');
Both copies, before anything runs
SELECT 'delete branch first' AS WHICH_COPY, SKU, QTY, NOTE FROM ORDER_A_T120
UNION ALL
SELECT 'update branch first', SKU, QTY, NOTE FROM ORDER_B_T120
ORDER BY WHICH_COPY, SKU;
Result6 rows · 4 columns
WHICH_COPYSKUQTYNOTE
delete branch firstA10012initial load
delete branch firstC3007initial load
delete branch firstD40040initial load
update branch firstA10012initial load
update branch firstC3007initial load
update branch firstD40040initial load

Same three rows in each, including D400, the one the feed wants removed.

First the delete branch written above the update

This is the same shape as the merge we started with: the conditional delete on top, the unconditional update underneath.

Query
MERGE INTO ORDER_A_T120 AS T
USING FEED_T120 AS S
   ON T.SKU = S.SKU
WHEN MATCHED AND S.ACTION = 'remove' THEN
     DELETE
WHEN MATCHED THEN
     UPDATE SET T.QTY = S.QTY, T.NOTE = 'updated instead of deleted';
Result1 row · 2 columns
number of rows updatednumber of rows deleted
21
And the table
SELECT SKU, QTY, NOTE FROM ORDER_A_T120 ORDER BY SKU;
Result2 rows · 3 columns
SKUQTYNOTE
A10025updated instead of deleted
C3000updated instead of deleted

Two updated and one deleted. D400 is gone and the other two were updated. Exactly what we asked for.

Now let's swap the two branches over

Same statement, same table, the two branches in the other order. The unconditional update is on top now.

Every matched row satisfies a branch with no condition on it, so the delete underneath can never run. I expected Snowflake to accept this and let the delete quietly die.

Query
MERGE INTO ORDER_B_T120 AS T
USING FEED_T120 AS S
   ON T.SKU = S.SKU
WHEN MATCHED THEN
     UPDATE SET T.QTY = S.QTY, T.NOTE = 'updated instead of deleted'
WHEN MATCHED AND S.ACTION = 'remove' THEN
     DELETE;
Query failed — Snowflake returned an error
002045 (42601): SQL compilation error: error line 6 at position 5
Unreachable merge case.

It does not. Unreachable merge case, at compile time, before anything ran. It read the branches and refused a dead one.

The second copy
SELECT SKU, QTY, NOTE FROM ORDER_B_T120 ORDER BY SKU;
Result3 rows · 3 columns
SKUQTYNOTE
A10012initial load
C3007initial load
D40040initial load

Untouched. Three rows, original quantities, original notes. The statement never ran at all.

The two copies side by side
SELECT (SELECT COUNT(*) FROM ORDER_A_T120 WHERE SKU = 'D400') AS D400_WITH_DELETE_FIRST,
       (SELECT COUNT(*) FROM ORDER_B_T120 WHERE SKU = 'D400') AS D400_WITH_UPDATE_FIRST,
       (SELECT COUNT(*) FROM ORDER_A_T120)                    AS ROWS_WITH_DELETE_FIRST,
       (SELECT COUNT(*) FROM ORDER_B_T120)                    AS ROWS_WITH_UPDATE_FIRST;
Result1 row · 4 columns
D400_WITH_DELETE_FIRSTD400_WITH_UPDATE_FIRSTROWS_WITH_DELETE_FIRSTROWS_WITH_UPDATE_FIRST
0123

D400 is gone from the copy where the delete branch came first, and still present in the copy where the statement was refused. That is a much better answer than the one I was expecting.

So how clever is that check?

That refusal raised a follow-up question I could not answer, so a second file asked it. Does Snowflake reason about the conditions, or only about a branch that has none?

The second file built a fresh three-row copy and ran that same delete-first merge against it, which left these two rows.

Where we start from
SELECT SKU, QTY, NOTE FROM ORDER_C_T120B ORDER BY SKU;
Result2 rows · 3 columns
SKUQTYNOTE
A10025updated instead of deleted
C3000updated instead of deleted

Now two branches that both carry a condition. The first takes quantity at least zero. The second takes quantity at least a hundred.

Anything at least a hundred is also at least zero, so the second branch is dead. But it is not unconditional. Does Snowflake spot that?

Query
MERGE INTO ORDER_C_T120B AS T
USING FEED_T120B AS S
   ON T.SKU = S.SKU
WHEN MATCHED AND S.QTY >= 0 THEN
     UPDATE SET T.NOTE = 'wide branch'
WHEN MATCHED AND S.QTY >= 100 THEN
     UPDATE SET T.NOTE = 'narrow branch';
Result1 row · 1 column
number of rows updated
2

No. Accepted, and two rows updated.

And the table
SELECT SKU, QTY, NOTE FROM ORDER_C_T120B ORDER BY SKU;
Result2 rows · 3 columns
SKUQTYNOTE
A10025wide branch
C3000wide branch

Both rows say wide branch. The narrow one never fired, and nothing warned us.

And the blatant form is refused in the other flavour too. Here is an unconditional WHEN NOT MATCHED written above a conditional one.

Query
MERGE INTO ORDER_C_T120B AS T
USING FEED_T120B AS S
   ON T.SKU = S.SKU
WHEN NOT MATCHED THEN
     INSERT (SKU, QTY, NOTE) VALUES (S.SKU, S.QTY, 'first branch')
WHEN NOT MATCHED AND S.ACTION = 'upsert' THEN
     INSERT (SKU, QTY, NOTE) VALUES (S.SKU, S.QTY, 'second branch');
Query failed — Snowflake returned an error
002045 (42601): SQL compilation error: error line 6 at position 5
Unreachable merge case.

The same error code. So the check is about the word, not the logic.

What the check does and does not do

Snowflake refuses a branch with no condition sitting above other branches of the same kind. It does not work out whether one condition swallows another.
So write your branches most specific first, and do not lean on the compiler to tell you when you have not.

Can a DELETE branch carry a SET clause?

A quick one while we are here. If the branch is a delete, can we also set a column on the way out?

Query
MERGE INTO STOCK_T120 AS T
USING FEED_T120 AS S
   ON T.SKU = S.SKU
WHEN MATCHED THEN
     DELETE SET T.QTY = 0;
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 5 at position 12 unexpected 'SET'.

No. DELETE in a MERGE branch is the whole branch. If you want to mark a row instead of removing it, that is an update branch, not a delete one.

Now a source that matches the same row twice

This is the case that bites people in production. Two systems both reported the same item, with different quantities, and both rows are in the feed.

A duplicated feed, and a target
-- A feed that names the same SKU twice, from two systems.
CREATE OR REPLACE TABLE DUPFEED_T120B (
    SKU            VARCHAR(10),
    QTY            INT,
    SOURCE_SYSTEM  VARCHAR(20)
);

INSERT INTO DUPFEED_T120B (SKU, QTY, SOURCE_SYSTEM) VALUES
    ('A100', 111, 'till'),
    ('A100', 222, 'warehouse');

-- And a target with one row in it.
CREATE OR REPLACE TABLE DUPTARGET_T120B (
    SKU          VARCHAR(10),
    QTY          INT,
    FROM_SYSTEM  VARCHAR(20)
);

INSERT INTO DUPTARGET_T120B (SKU, QTY, FROM_SYSTEM) VALUES ('A100', 12, 'initial load');
The feed
SELECT SKU, QTY, SOURCE_SYSTEM FROM DUPFEED_T120B ORDER BY SOURCE_SYSTEM;
Result2 rows · 3 columns
SKUQTYSOURCE_SYSTEM
A100111till
A100222warehouse
The target
SELECT SKU, QTY, FROM_SYSTEM FROM DUPTARGET_T120B ORDER BY SKU;
Result1 row · 3 columns
SKUQTYFROM_SYSTEM
A10012initial load

One SKU, two quantities, and one target row to put them in.

Pairs the join makes
SELECT COUNT(*) AS PAIRS_THE_JOIN_MAKES
FROM DUPTARGET_T120B T, DUPFEED_T120B D
WHERE T.SKU = D.SKU;
Result1 row · 1 column
PAIRS_THE_JOIN_MAKES
2

Two pairs from one target row. Snowflake is about to be asked to update that row twice, with two different values.

Let's read the parameter rather than guess at it

There is a session parameter for exactly this case. Let's read what this account has it set to before running anything.

SHOW PARAMETERS
SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_MERGE' IN SESSION;
Result1 row · 6 columns · scroll →
keyvaluedefaultleveldescriptiontype
ERROR_ON_NONDETERMINISTIC_MERGEtruetrueraise an error when attempting to merge-update a row that joins many rowsBOOLEAN

Value true, default true. The level cell is an empty string rather than a NULL, and the grid marks it empty. That is how SHOW PARAMETERS says nothing has overridden the default — not at session level, not anywhere.

The wide grid is awkward to read, so let's pull the three columns we care about out of it.

The same answer, tidier
SELECT "key" AS THE_PARAMETER, "value" AS THE_VALUE, "default" AS THE_DEFAULT
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 3 columns
THE_PARAMETERTHE_VALUETHE_DEFAULT
ERROR_ON_NONDETERMINISTIC_MERGEtruetrue
Two parameters, two defaults

Hold that next to the last post but one. In Episode 117 ERROR_ON_NONDETERMINISTIC_UPDATE was false by default. Here ERROR_ON_NONDETERMINISTIC_MERGE is true by default. Two parameters that look like a pair, and they are set differently. Read them. Do not carry an assumption from one to the other.

Now let's run that MERGE with the check as it comes

An ordinary matched-update merge, from the duplicated feed into the one-row target.

Query
MERGE INTO DUPTARGET_T120B AS T
USING DUPFEED_T120B AS S
   ON T.SKU = S.SKU
WHEN MATCHED THEN
     UPDATE SET T.QTY = S.QTY, T.FROM_SYSTEM = S.SOURCE_SYSTEM;
Query failed — Snowflake returned an error
100090 (42P18): Duplicate row detected during DML action
Row Values: ["A100", 12, "initial load"]

Refused, and look at what the message prints. Those are the values of the target row, not the source row. It is naming the row it could not update.

And the row
SELECT SKU, QTY, FROM_SYSTEM FROM DUPTARGET_T120B ORDER BY SKU;
Result1 row · 3 columns
SKUQTYFROM_SYSTEM
A10012initial load

Exactly as it was. The refusal changed nothing.

Now let's turn the check off and run the identical statement

ALTER SESSION
ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = FALSE;
Result1 row · 1 column
status
Statement executed successfully.
SHOW PARAMETERS
SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_MERGE' IN SESSION;
Result1 row · 6 columns · scroll →
keyvaluedefaultleveldescriptiontype
ERROR_ON_NONDETERMINISTIC_MERGEfalsetrueSESSIONraise an error when attempting to merge-update a row that joins many rowsBOOLEAN

Value false, default still true, and the level cell now reads SESSION instead of being empty. That is the column telling us somebody has overridden it, and in this case the somebody is us.

Same merge as before, not a character changed.

Query
MERGE INTO DUPTARGET_T120B AS T
USING DUPFEED_T120B AS S
   ON T.SKU = S.SKU
WHEN MATCHED THEN
     UPDATE SET T.QTY = S.QTY, T.FROM_SYSTEM = S.SOURCE_SYSTEM;
Result1 row · 1 column
number of rows updated
1

One row updated. So what landed in it?

And the row
SELECT SKU,
       QTY         AS THE_QTY_NOW,
       FROM_SYSTEM AS THE_SYSTEM_IT_CAME_FROM,
       (SELECT QTY FROM DUPFEED_T120B WHERE SOURCE_SYSTEM = 'till')      AS CANDIDATE_TILL,
       (SELECT QTY FROM DUPFEED_T120B WHERE SOURCE_SYSTEM = 'warehouse') AS CANDIDATE_WAREHOUSE
FROM DUPTARGET_T120B ORDER BY SKU;
Result1 row · 5 columns
SKUTHE_QTY_NOWTHE_SYSTEM_IT_CAME_FROMCANDIDATE_TILLCANDIDATE_WAREHOUSE
A100111till111222

The till value won, and the last two columns show both candidates side by side so you can see what it chose between.

Read that result carefully

That is one run. I am not going to tell you which source row wins, because I have seen it happen once and that is not a rule. The point of the grid is not which value landed. The point is that Snowflake had to choose and you did not get to.

So here is the fix, and it is not a setting

Turning the check off does not make the merge deterministic. It only stops Snowflake telling you that it is not. The fix is to hand MERGE a source that has one row per key, chosen on purpose.

First let's put the check back on, so nothing is hidden.

ALTER SESSION
ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = TRUE;
Result1 row · 1 column
status
Statement executed successfully.

Now QUALIFY with ROW_NUMBER(), partitioned by the key and ordered by the column we want to win.

Query
SELECT SKU, QTY, SOURCE_SYSTEM
FROM DUPFEED_T120B
QUALIFY ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY SOURCE_SYSTEM) = 1;
Result1 row · 3 columns
SKUQTYSOURCE_SYSTEM
A100111till

One row per SKU, and the till row is the one that survived — because till sorts before warehouse, which is a decision we made rather than one that happened to us.

Let's merge from that, with the check still on.

Query
MERGE INTO FIXED_T120B AS T
USING (SELECT SKU, QTY, SOURCE_SYSTEM
       FROM DUPFEED_T120B
       QUALIFY ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY SOURCE_SYSTEM) = 1) AS S
   ON T.SKU = S.SKU
WHEN MATCHED THEN
     UPDATE SET T.QTY = S.QTY, T.FROM_SYSTEM = S.SOURCE_SYSTEM;
Result1 row · 1 column
number of rows updated
1
And the row
SELECT SKU, QTY AS THE_QTY_NOW, FROM_SYSTEM AS THE_SYSTEM_IT_CAME_FROM
FROM FIXED_T120B ORDER BY SKU;
Result1 row · 3 columns
SKUTHE_QTY_NOWTHE_SYSTEM_IT_CAME_FROM
A100111till

One row updated, and the same value as before. But this time we chose it, and the check never objected. That is the difference between a coin toss and a decision.

One last thing: the first run of this file was broken

This section is about me rather than about MERGE, and it is in the post on purpose.

The first time I ran this episode's file, none of that duplicate-source section worked. I declared FROM_SYSTEM as VARCHAR(10) and then inserted 'initial load' into it, which is twelve characters.

The insert that failed
INSERT INTO DUPTARGET_T120 (SKU, QTY, FROM_SYSTEM)
VALUES ('A100', 12, 'initial load');
Query failed — Snowflake returned an error
100078 (22000): DML operation to table DUPTARGET_T120 failed on column FROM_SYSTEM with error: String 'initial load' is too long and would be truncated

Snowflake was right and I was careless. It is the same refusal Episode 115 puts on screen.

The consequence is the part worth looking at. The target table stayed empty, so every merge into it matched nothing and reported success.

The same merge, in the broken run
MERGE INTO DUPTARGET_T120 AS T
USING DUPFEED_T120 AS S
   ON T.SKU = S.SKU
WHEN MATCHED THEN
     UPDATE SET T.QTY = S.QTY, T.FROM_SYSTEM = S.SOURCE_SYSTEM;
Result1 row · 1 column
number of rows updated
0

Zero rows updated, and no refusal at all. With no row in the table there was no row that joined twice, so the parameter had nothing to object to.

And the grid that answers the episode
SELECT SKU, QTY, FROM_SYSTEM FROM DUPTARGET_T120 ORDER BY SKU;
Result0 rows · 3 columns
SKUQTYFROM_SYSTEM

Empty. Nothing was wrong with the merge statements. The table was empty, and three grids in that run came back with no rows, including the one the whole episode is built around.

So a second file fixed the column and asked the same questions again, which is where every grid in the sections above came from. Everything gets dropped at the end, and the last query counts the leftovers from both runs.

Cleanup
-- Put the schema back the way we found it.
DROP TABLE IF EXISTS STOCK_T120;
DROP TABLE IF EXISTS FEED_T120;
DROP TABLE IF EXISTS ORDER_A_T120;
DROP TABLE IF EXISTS ORDER_B_T120;
DROP TABLE IF EXISTS DUPFEED_T120;
DROP TABLE IF EXISTS DUPTARGET_T120;
DROP TABLE IF EXISTS FIXED_T120;

DROP TABLE IF EXISTS DUPFEED_T120B;
DROP TABLE IF EXISTS DUPTARGET_T120B;
DROP TABLE IF EXISTS FIXED_T120B;
DROP TABLE IF EXISTS ORDER_C_T120B;
DROP TABLE IF EXISTS FEED_T120B;
The last check
SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 6) = '_T120B') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T120') AS OBJECTS_STILL_LEFT_FROM_EP120,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;
Result1 row · 5 columns
THE_DATABASETHE_SCHEMAPROBE_TABLES_LEFTOBJECTS_STILL_LEFT_FROM_EP120DEMO_DB_IS_READABLE
DEMO_DBPUBLIC0010

Zero and zero. That is the only reason I know the broken run left nothing behind in the account. A check nobody reads is not a check, and this one got read.

Key points

  • One MERGE can update, delete and insert, and it reports a column per verb it used.
  • Put a check at the end of a change-feed merge that asks whether the rows you removed are really gone.
  • MERGE takes the first branch whose condition is true, so branch order is logic, not taste.
  • An unconditional branch written above a conditional one of the same kind is refused at compile time with Unreachable merge case.
  • A wide condition above a narrow one is accepted, and the narrow branch never fires. Nothing warns you.
  • So the check is about an unconditional branch, not about the logic of your conditions. Write the most specific branch first.
  • A DELETE branch cannot carry a SET clause.
  • ERROR_ON_NONDETERMINISTIC_MERGE is true by default here, where Episode 117's UPDATE equivalent is false. Read them, do not assume them.
  • With the check on, a source that matches a row twice is refused, the message prints the target row, and the row is left untouched.
  • With the check off the merge succeeds and Snowflake picks a value for you. Which one it picked in one run is not a rule.
  • QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) is the fix, and it works with the check left on.
  • An empty level cell in SHOW PARAMETERS is an empty string, and means nothing has overridden the default.
Next in this series

Episode 121 — transactions. BEGIN, COMMIT, ROLLBACK, autocommit, and one ordinary statement that quietly commits the transaction you had open.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo scripts for this episode. There are two of them: the first declared a column too narrow, which is the subject of the last section, and the second re-asked the same questions with the column fixed. Every grid in the duplicate-source sections comes from the second run, and the grids in the last section come from the first. The stock data is made up for teaching. The refusals are real captured errors and are shown as they came back. All the _T120 and _T120B objects are created and dropped by the scripts. Empty cells marked empty are empty strings, not nulls. Nothing here is timed and no performance claims are made anywhere in this series. Clause behaviour follows the Snowflake MERGE documentation, and the parameter is documented under ERROR_ON_NONDETERMINISTIC_MERGE.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.