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
- Let's start with one MERGE that does all three jobs
- Now let's look at branch order
- First the delete branch written above the update
- Now let's swap the two branches over
- So how clever is that check?
- Can a DELETE branch carry a SET clause?
- Now a source that matches the same row twice
- Let's read the parameter rather than guess at it
- Now let's run that MERGE with the check as it comes
- Now let's turn the check off and run the identical statement
- So here is the fix, and it is not a setting
- One last thing: the first run of this file was broken
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 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');SELECT SKU, QTY, SHELF, NOTE FROM STOCK_T120 ORDER BY SKU;| SKU | QTY | SHELF | NOTE |
|---|---|---|---|
| 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 |
SELECT SKU, QTY, ACTION FROM FEED_T120 ORDER BY SKU;| SKU | QTY | ACTION |
|---|---|---|
| A100 | 25 | upsert |
| C300 | 0 | upsert |
| D400 | 0 | remove |
| F600 | 15 | upsert |
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.
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');| number of rows inserted | number of rows updated | number of rows deleted |
|---|---|---|
| 1 | 2 | 1 |
Three numbers back, a column per verb. That is the whole report.
SELECT SKU, QTY, SHELF, NOTE FROM STOCK_T120 ORDER BY SKU;| SKU | QTY | SHELF | NOTE |
|---|---|---|---|
| A100 | 25 | front | updated by merge |
| B200 | 0 | front | initial load |
| C300 | 0 | back | updated by merge |
| E500 | 3 | front | initial load |
| F600 | 15 | unsorted | inserted 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.
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;| ROWS_TOTAL | ROWS_UPDATED | ROWS_INSERTED | THE_REMOVED_SKU_STILL_HERE |
|---|---|---|---|
| 5 | 2 | 1 | 0 |
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 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');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;| WHICH_COPY | SKU | QTY | NOTE |
|---|---|---|---|
| delete branch first | A100 | 12 | initial load |
| delete branch first | C300 | 7 | initial load |
| delete branch first | D400 | 40 | initial load |
| update branch first | A100 | 12 | initial load |
| update branch first | C300 | 7 | initial load |
| update branch first | D400 | 40 | initial 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.
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';| number of rows updated | number of rows deleted |
|---|---|
| 2 | 1 |
SELECT SKU, QTY, NOTE FROM ORDER_A_T120 ORDER BY SKU;| SKU | QTY | NOTE |
|---|---|---|
| A100 | 25 | updated instead of deleted |
| C300 | 0 | updated 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.
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;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.
SELECT SKU, QTY, NOTE FROM ORDER_B_T120 ORDER BY SKU;| SKU | QTY | NOTE |
|---|---|---|
| A100 | 12 | initial load |
| C300 | 7 | initial load |
| D400 | 40 | initial load |
Untouched. Three rows, original quantities, original notes. The statement never ran at all.
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;| D400_WITH_DELETE_FIRST | D400_WITH_UPDATE_FIRST | ROWS_WITH_DELETE_FIRST | ROWS_WITH_UPDATE_FIRST |
|---|---|---|---|
| 0 | 1 | 2 | 3 |
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.
SELECT SKU, QTY, NOTE FROM ORDER_C_T120B ORDER BY SKU;| SKU | QTY | NOTE |
|---|---|---|
| A100 | 25 | updated instead of deleted |
| C300 | 0 | updated 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?
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';| number of rows updated |
|---|
| 2 |
No. Accepted, and two rows updated.
SELECT SKU, QTY, NOTE FROM ORDER_C_T120B ORDER BY SKU;| SKU | QTY | NOTE |
|---|---|---|
| A100 | 25 | wide branch |
| C300 | 0 | wide 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.
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');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.
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?
MERGE INTO STOCK_T120 AS T
USING FEED_T120 AS S
ON T.SKU = S.SKU
WHEN MATCHED THEN
DELETE SET T.QTY = 0;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 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');SELECT SKU, QTY, SOURCE_SYSTEM FROM DUPFEED_T120B ORDER BY SOURCE_SYSTEM;| SKU | QTY | SOURCE_SYSTEM |
|---|---|---|
| A100 | 111 | till |
| A100 | 222 | warehouse |
SELECT SKU, QTY, FROM_SYSTEM FROM DUPTARGET_T120B ORDER BY SKU;| SKU | QTY | FROM_SYSTEM |
|---|---|---|
| A100 | 12 | initial load |
One SKU, two quantities, and one target row to put them in.
SELECT COUNT(*) AS PAIRS_THE_JOIN_MAKES
FROM DUPTARGET_T120B T, DUPFEED_T120B D
WHERE T.SKU = D.SKU;| 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 LIKE 'ERROR_ON_NONDETERMINISTIC_MERGE' IN SESSION;| key | value | default | level | description | type |
|---|---|---|---|---|---|
| ERROR_ON_NONDETERMINISTIC_MERGE | true | true | raise an error when attempting to merge-update a row that joins many rows | BOOLEAN |
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.
SELECT "key" AS THE_PARAMETER, "value" AS THE_VALUE, "default" AS THE_DEFAULT
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));| THE_PARAMETER | THE_VALUE | THE_DEFAULT |
|---|---|---|
| ERROR_ON_NONDETERMINISTIC_MERGE | true | true |
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.
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;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.
SELECT SKU, QTY, FROM_SYSTEM FROM DUPTARGET_T120B ORDER BY SKU;| SKU | QTY | FROM_SYSTEM |
|---|---|---|
| A100 | 12 | initial load |
Exactly as it was. The refusal changed nothing.
Now let's turn the check off and run the identical statement
ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = FALSE;| status |
|---|
| Statement executed successfully. |
SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_MERGE' IN SESSION;| key | value | default | level | description | type |
|---|---|---|---|---|---|
| ERROR_ON_NONDETERMINISTIC_MERGE | false | true | SESSION | raise an error when attempting to merge-update a row that joins many rows | BOOLEAN |
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.
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;| number of rows updated |
|---|
| 1 |
One row updated. So what landed in it?
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;| SKU | THE_QTY_NOW | THE_SYSTEM_IT_CAME_FROM | CANDIDATE_TILL | CANDIDATE_WAREHOUSE |
|---|---|---|---|---|
| A100 | 111 | till | 111 | 222 |
The till value won, and the last two columns show both candidates side by side so you can see what it chose between.
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 SET ERROR_ON_NONDETERMINISTIC_MERGE = TRUE;| status |
|---|
| Statement executed successfully. |
Now QUALIFY with ROW_NUMBER(), partitioned by the key and ordered by the column we want to win.
SELECT SKU, QTY, SOURCE_SYSTEM
FROM DUPFEED_T120B
QUALIFY ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY SOURCE_SYSTEM) = 1;| SKU | QTY | SOURCE_SYSTEM |
|---|---|---|
| A100 | 111 | till |
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.
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;| number of rows updated |
|---|
| 1 |
SELECT SKU, QTY AS THE_QTY_NOW, FROM_SYSTEM AS THE_SYSTEM_IT_CAME_FROM
FROM FIXED_T120B ORDER BY SKU;| SKU | THE_QTY_NOW | THE_SYSTEM_IT_CAME_FROM |
|---|---|---|
| A100 | 111 | till |
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.
INSERT INTO DUPTARGET_T120 (SKU, QTY, FROM_SYSTEM)
VALUES ('A100', 12, 'initial load');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.
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;| 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.
SELECT SKU, QTY, FROM_SYSTEM FROM DUPTARGET_T120 ORDER BY SKU;| SKU | QTY | FROM_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.
-- 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;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;| THE_DATABASE | THE_SCHEMA | PROBE_TABLES_LEFT | OBJECTS_STILL_LEFT_FROM_EP120 | DEMO_DB_IS_READABLE |
|---|---|---|---|---|
| DEMO_DB | PUBLIC | 0 | 0 | 10 |
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
MERGEcan 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.
MERGEtakes 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
DELETEbranch cannot carry aSETclause. ERROR_ON_NONDETERMINISTIC_MERGEistrueby default here, where Episode 117'sUPDATEequivalent isfalse. 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
levelcell inSHOW PARAMETERSis an empty string, and means nothing has overridden the default.
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.