Welcome To TechBrothersIT: Snowflake SQL Tutorial 136 - A Statement That Inserted Zero Rows Still Emptied My Stream

Snowflake SQL Tutorial 136 - A Statement That Inserted Zero Rows Still Emptied My Stream

Snowflake SQL · Episode 136

In this post we are going to cover consuming a stream in Snowflake, and the CHANGES clause.

Episode 135 was about what a stream records. This one is about what moves it. A stream's bookmark advances when the stream appears in a DML statement, and that sentence has a trap in it that costs people data.

We will start with one table, one audit table and one stream, 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.

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

On this page

Let's create some sample data
Let's give it two changes and then consume them
Now the one that matters: a statement that records nothing
So what counts as a DML? A SELECT is free, a CTAS is not
When exactly does the offset move? Let's use a transaction
Two streams on one table, and a stream driving a MERGE
The same answer, twice: the CHANGES clause
And what CHANGES refuses
And the cleanup

Let's create some sample data

Two tables. A source with three rows in it, and an empty audit table with two extra columns for the stream's own metadata.

Sample data

-- Sample data for this post.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

-- The table the changes happen to.
CREATE OR REPLACE TABLE SALES_T136 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

-- And the table we are going to write the changes into.
CREATE OR REPLACE TABLE AUDIT_T136 (
    SALE_ID    INT,
    REGION     VARCHAR(10),
    AMOUNT     NUMBER(10,2),
    THE_ACTION VARCHAR(10),
    WAS_UPDATE BOOLEAN
);

INSERT INTO SALES_T136 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50), (3, 'east', 75.25);

Now a stream on the source.

Query

CREATE OR REPLACE STREAM STRM_T136 ON TABLE SALES_T136;

Result1 row · 1 column

status
Stream STRM_T136 successfully created.

Query

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

Zero, because a stream starts at now. That is episode 135's result and we are taking it as read from here on.

Let's give it two changes and then consume them

One insert and one delete.

The insert

INSERT INTO SALES_T136 (SALE_ID, REGION, AMOUNT) VALUES (4, 'west', 310.00);

Result1 row · 1 column

number of rows inserted
1

The delete

DELETE FROM SALES_T136 WHERE SALE_ID = 1;

Result1 row · 1 column

number of rows deleted
1

What the stream offers

SELECT SALE_ID, AMOUNT,
       METADATA$ACTION   AS THE_ACTION,
       METADATA$ISUPDATE AS IS_PART_OF_AN_UPDATE
FROM STRM_T136
ORDER BY SALE_ID;

Result2 rows · 4 columns

SALE_ID	AMOUNT	THE_ACTION	IS_PART_OF_AN_UPDATE
1	100.00	DELETE	FALSE
4	310.00	INSERT	FALSE

Two rows. A delete of sale one and an insert of sale four, and neither of them is part of an update.

Now the consume. This is just an INSERT ... SELECT with the stream as the source.

Query

INSERT INTO AUDIT_T136 (SALE_ID, REGION, AMOUNT, THE_ACTION, WAS_UPDATE)
SELECT SALE_ID, REGION, AMOUNT, METADATA$ACTION, METADATA$ISUPDATE
FROM STRM_T136;

Result1 row · 1 column

number of rows inserted
2

Two rows inserted.

Query

SELECT SALE_ID, REGION, AMOUNT, THE_ACTION, WAS_UPDATE
FROM AUDIT_T136
ORDER BY SALE_ID;

Result2 rows · 5 columns

SALE_ID	REGION	AMOUNT	THE_ACTION	WAS_UPDATE
1	north	100.00	DELETE	FALSE
4	west	310.00	INSERT	FALSE

Both of them, with the action and the update flag stored alongside the data. Now let's ask the stream again.

The stream

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

And the function

SELECT SYSTEM$STREAM_HAS_DATA('STRM_T136') AS THE_STREAM_HAS_DATA;

Result1 row · 1 column

THE_STREAM_HAS_DATA
FALSE

Zero, and FALSE. The bookmark has moved.

And one thing worth checking before we go on.

Query

SELECT COUNT(*)    AS ROWS_IN_THE_SOURCE,
       SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T136;

Result1 row · 2 columns

ROWS_IN_THE_SOURCE	TOTAL_AMOUNT
3	635.75

Three rows and 635.75, exactly as it was. Consuming a stream reads the source table. It does not write to it and it does not clear anything out of it.

Now the one that matters: a statement that records nothing

Let's give the stream something again.

Query

INSERT INTO SALES_T136 (SALE_ID, REGION, AMOUNT) VALUES (5, 'north', 45.00);

Result1 row · 1 column

number of rows inserted
1

The stream

SELECT SALE_ID, AMOUNT, METADATA$ACTION AS THE_ACTION
FROM STRM_T136
ORDER BY SALE_ID;

Result1 row · 3 columns

SALE_ID	AMOUNT	THE_ACTION
5	45.00	INSERT

Sale five, an insert. And the audit table right now:

Query

SELECT COUNT(*) AS ROWS_IN_THE_AUDIT_TABLE FROM AUDIT_T136;

Result1 row · 1 column

ROWS_IN_THE_AUDIT_TABLE
2

Two rows. Remember that number.

Now the same consume as before, with one thing added. A WHERE clause that nothing in this stream matches.

Query

INSERT INTO AUDIT_T136 (SALE_ID, REGION, AMOUNT, THE_ACTION, WAS_UPDATE)
SELECT SALE_ID, REGION, AMOUNT, METADATA$ACTION, METADATA$ISUPDATE
FROM STRM_T136
WHERE SALE_ID < 0;

Result1 row · 1 column

number of rows inserted
0

Zero rows inserted.

The audit table

SELECT COUNT(*) AS ROWS_IN_THE_AUDIT_TABLE FROM AUDIT_T136;

Result1 row · 1 column

ROWS_IN_THE_AUDIT_TABLE
2

Still two rows. Nothing was written. So the change must still be sitting in the stream, waiting.

The stream

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

And the function

SELECT SYSTEM$STREAM_HAS_DATA('STRM_T136') AS THE_STREAM_HAS_DATA;

Result1 row · 1 column

THE_STREAM_HAS_DATA
FALSE

Zero, and FALSE.

Read that again. The change is gone, and nothing wrote it down.

The offset does not care whether you used the rows

The bookmark moved because the stream appeared in a DML statement, not because the DML achieved anything. A filter that matches nothing, a merge whose conditions all fail, an insert into a table with a constraint that rejects every row — any of those spends the stream exactly as thoroughly as a successful load does.

And it happens even when there was nothing to give in the first place. This pair comes from a second, shorter run of the script, against its own table, which is why the names have a B on the end. A row is inserted and deleted before anybody looks, so the read is net zero while the function still says yes.

Query

SELECT (SELECT COUNT(*) FROM DEMO_DB.PUBLIC.STRM_T136B)          AS ROWS_A_READ_RETURNS,
       SYSTEM$STREAM_HAS_DATA('DEMO_DB.PUBLIC.STRM_T136B')       AS WHAT_HAS_DATA_SAYS;

Result1 row · 2 columns

ROWS_A_READ_RETURNS	WHAT_HAS_DATA_SAYS
0	TRUE

Zero rows and TRUE, together, which is episode 135's finding. Now run a DML over that stream.

Query

INSERT INTO DEMO_DB.PUBLIC.AUDIT_T136B (SALE_ID, THE_ACTION)
SELECT SALE_ID, METADATA$ACTION FROM DEMO_DB.PUBLIC.STRM_T136B;

Result1 row · 1 column

number of rows inserted
0

Query

SELECT (SELECT COUNT(*) FROM DEMO_DB.PUBLIC.STRM_T136B)    AS ROWS_A_READ_RETURNS,
       SYSTEM$STREAM_HAS_DATA('DEMO_DB.PUBLIC.STRM_T136B') AS WHAT_HAS_DATA_SAYS,
       (SELECT COUNT(*) FROM DEMO_DB.PUBLIC.AUDIT_T136B)   AS ROWS_IN_THE_AUDIT_TABLE;

Result1 row · 3 columns

ROWS_A_READ_RETURNS	WHAT_HAS_DATA_SAYS	ROWS_IN_THE_AUDIT_TABLE
0	FALSE	3

Zero rows inserted, and the function has flipped to FALSE. Even a stream with nothing to give can be spent.

So what counts as a DML? A SELECT is free, a CTAS is not

Let's give the stream one row to work with.

Query

INSERT INTO SALES_T136 (SALE_ID, REGION, AMOUNT) VALUES (6, 'south', 88.25);

Result1 row · 1 column

number of rows inserted
1

Read it once

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
1

Read it again

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
1

One, and one. Now twice inside a single statement, as two subqueries.

Query

SELECT (SELECT COUNT(*) FROM STRM_T136) AS THE_FIRST_READ,
       (SELECT COUNT(*) FROM STRM_T136) AS THE_SECOND_READ;

Result1 row · 2 columns

THE_FIRST_READ	THE_SECOND_READ
1	1

Both reads see the row, and nothing is spent. A SELECT really is free.

Now CREATE TABLE AS SELECT, straight off the stream.

Query

CREATE OR REPLACE TABLE CTAS_T136 AS SELECT * FROM STRM_T136;

Result1 row · 1 column

status
Table CTAS_T136 successfully created.

What the new table got

SELECT COUNT(*) AS ROWS_IN_THE_NEW_TABLE FROM CTAS_T136;

Result1 row · 1 column

ROWS_IN_THE_NEW_TABLE
1

And the stream

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

The new table got its row, and the stream reads zero. CREATE TABLE AS SELECT consumed it.

It is written like DDL and it moves the offset like a DML. If you are working out what is safe to run against a stream, that is the one that will catch you.

And two statements a stream will not take at all

UPDATE a stream

UPDATE STRM_T136 SET AMOUNT = 1 WHERE SALE_ID = 1;

Query failed — Snowflake returned an error

001759 (42601): SQL compilation error:
UPDATE statement's target must be a table

DELETE from a stream

DELETE FROM STRM_T136 WHERE SALE_ID = 1;

Query failed — Snowflake returned an error

001759 (42601): SQL compilation error:
DELETE statement's target must be a table

001759 both times, in the same words with the verb swapped. A stream is readable, and the only way to write through it is to read it inside a statement that writes somewhere else.

When exactly does the offset move? Let's use a transaction

This is the question worth being careful about, and the first attempt in the demo script could not answer it. The CTAS above had already emptied the stream one statement earlier, so the consume inside the transaction had nothing to consume.

BEGIN

BEGIN;

Result1 row · 1 column

status
Statement executed successfully.

The consume

INSERT INTO AUDIT_T136 (SALE_ID, REGION, AMOUNT, THE_ACTION, WAS_UPDATE)
SELECT SALE_ID, REGION, AMOUNT, METADATA$ACTION, METADATA$ISUPDATE
FROM STRM_T136;

Result1 row · 1 column

number of rows inserted
0

The stream, inside the transaction

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

ROLLBACK

ROLLBACK;

Result1 row · 1 column

status
Statement executed successfully.

And the stream afterwards

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

Zero rows inserted, zero offered, zero afterwards. Three zeroes that look like an answer and are not one. A test whose answer cannot be no has not been run.

So here is the same test, written so the answer can be no

A second script, its own table and its own stream, and the count printed at every step. This is why the names below have a B on the end.

The stream, before BEGIN

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM DEMO_DB.PUBLIC.STRM_T136B;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
3

The audit table, before BEGIN

SELECT COUNT(*) AS ROWS_IN_THE_AUDIT_TABLE FROM DEMO_DB.PUBLIC.AUDIT_T136B;

Result1 row · 1 column

ROWS_IN_THE_AUDIT_TABLE
0

Three rows waiting, and an empty audit table. Three is the number to watch.

Now begin a transaction and consume the stream inside it.

BEGIN, then the consume

BEGIN;

INSERT INTO DEMO_DB.PUBLIC.AUDIT_T136B (SALE_ID, THE_ACTION)
SELECT SALE_ID, METADATA$ACTION FROM DEMO_DB.PUBLIC.STRM_T136B;

And without leaving the transaction, ask both sides what they hold.

The audit table, inside the transaction

SELECT COUNT(*) AS ROWS_IN_THE_AUDIT_TABLE FROM DEMO_DB.PUBLIC.AUDIT_T136B;

Result1 row · 1 column

ROWS_IN_THE_AUDIT_TABLE
3

The stream, inside the same transaction

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM DEMO_DB.PUBLIC.STRM_T136B;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
3

The audit table holds three rows, and the stream still offers its three. Inside the transaction the rows are in both places at once.

Now roll the whole thing back, and ask the stream what it has.

Query

SELECT SALE_ID, METADATA$ACTION AS THE_ACTION
FROM DEMO_DB.PUBLIC.STRM_T136B
ORDER BY SALE_ID;

Result3 rows · 2 columns

SALE_ID	THE_ACTION
3	INSERT
4	INSERT
5	INSERT

Three rows, sales three, four and five, all inserts. Exactly what it held before the transaction started. The rollback put the bookmark back.

So let's do the whole thing again and commit this time.

BEGIN, the same consume, COMMIT

BEGIN;

INSERT INTO DEMO_DB.PUBLIC.AUDIT_T136B (SALE_ID, THE_ACTION)
SELECT SALE_ID, METADATA$ACTION FROM DEMO_DB.PUBLIC.STRM_T136B;

COMMIT;

The audit table, after COMMIT

SELECT SALE_ID, THE_ACTION
FROM DEMO_DB.PUBLIC.AUDIT_T136B
ORDER BY SALE_ID;

Result3 rows · 2 columns

SALE_ID	THE_ACTION
3	INSERT
4	INSERT
5	INSERT

And the function

SELECT SYSTEM$STREAM_HAS_DATA('DEMO_DB.PUBLIC.STRM_T136B') AS THE_STREAM_HAS_DATA;

Result1 row · 1 column

THE_STREAM_HAS_DATA
FALSE

The audit table keeps the three rows and the function says FALSE. The offset moves on commit, not when the statement runs.

What to do about it

Which is the useful half of this whole episode. Put the consume in a transaction with whatever else has to succeed with it, and a failure anywhere in that transaction gives you the rows back. Leave it on its own and you are relying on one statement never going wrong.

Two streams on one table, and a stream driving a MERGE

A second stream on the same source table.

Query

CREATE OR REPLACE STREAM OTHER_T136 ON TABLE SALES_T136;

Result1 row · 1 column

status
Stream OTHER_T136 successfully created.

One change

INSERT INTO SALES_T136 (SALE_ID, REGION, AMOUNT) VALUES (7, 'east', 7.00);

Result1 row · 1 column

number of rows inserted
1

What each offers

SELECT (SELECT COUNT(*) FROM STRM_T136)  AS THE_FIRST_STREAM,
       (SELECT COUNT(*) FROM OTHER_T136) AS THE_SECOND_STREAM;

Result1 row · 2 columns

THE_FIRST_STREAM	THE_SECOND_STREAM
1	1

One and one. Both streams are holding the same change. Now let's consume only the first one.

Query

INSERT INTO AUDIT_T136 (SALE_ID, REGION, AMOUNT, THE_ACTION, WAS_UPDATE)
SELECT SALE_ID, REGION, AMOUNT, METADATA$ACTION, METADATA$ISUPDATE
FROM STRM_T136;

Result1 row · 1 column

number of rows inserted
1

Query

SELECT (SELECT COUNT(*) FROM STRM_T136)  AS THE_FIRST_STREAM,
       (SELECT COUNT(*) FROM OTHER_T136) AS THE_SECOND_STREAM;

Result1 row · 2 columns

THE_FIRST_STREAM	THE_SECOND_STREAM
0	1

Zero and one. The second stream still has its row, because each stream carries its own offset. Two consumers on one table do not interfere.

And now the thing people actually build

A mirror table, seeded from the source as it stands.

The mirror

CREATE OR REPLACE TABLE MIRROR_T136 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

Result1 row · 1 column

status
Table MIRROR_T136 successfully created.

Seed it

INSERT INTO MIRROR_T136 (SALE_ID, REGION, AMOUNT)
SELECT SALE_ID, REGION, AMOUNT FROM SALES_T136;

Result1 row · 1 column

number of rows inserted
6

Both sides

SELECT (SELECT COUNT(*) FROM SALES_T136)  AS ROWS_IN_THE_SOURCE,
       (SELECT COUNT(*) FROM MIRROR_T136) AS ROWS_IN_THE_MIRROR;

Result1 row · 2 columns

ROWS_IN_THE_SOURCE	ROWS_IN_THE_MIRROR
6	6

Six rows each. Agreed. Now a fresh stream to drive the merge, and then one of each kind of change.

The stream

CREATE OR REPLACE STREAM MERGESTRM_T136 ON TABLE SALES_T136;

Result1 row · 1 column

status
Stream MERGESTRM_T136 successfully created.

An insert

INSERT INTO SALES_T136 (SALE_ID, REGION, AMOUNT) VALUES (8, 'west', 8.00);

Result1 row · 1 column

number of rows inserted
1

An update

UPDATE SALES_T136 SET AMOUNT = 999.99 WHERE SALE_ID = 2;

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
1	0

A delete

DELETE FROM SALES_T136 WHERE SALE_ID = 3;

Result1 row · 1 column

number of rows deleted
1

Query

SELECT SALE_ID, AMOUNT,
       METADATA$ACTION   AS THE_ACTION,
       METADATA$ISUPDATE AS IS_PART_OF_AN_UPDATE
FROM MERGESTRM_T136
ORDER BY SALE_ID, THE_ACTION;

Result4 rows · 4 columns

SALE_ID	AMOUNT	THE_ACTION	IS_PART_OF_AN_UPDATE
2	250.50	DELETE	TRUE
2	999.99	INSERT	TRUE
3	75.25	DELETE	FALSE
8	8.00	INSERT	FALSE

Four rows for three changes, because the update is the pair again. Both halves of it are flagged TRUE, and the real delete and the real insert are not.

Which is what lets one MERGE handle all three. The update's DELETE half is filtered out in the USING clause, so what reaches the merge is one row per change.

Query

MERGE INTO MIRROR_T136 m
USING (
    SELECT SALE_ID, REGION, AMOUNT, METADATA$ACTION AS THE_ACTION
    FROM MERGESTRM_T136
    WHERE NOT (METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE = TRUE)
) s
ON m.SALE_ID = s.SALE_ID
WHEN MATCHED AND s.THE_ACTION = 'DELETE' THEN DELETE
WHEN MATCHED AND s.THE_ACTION = 'INSERT' THEN
    UPDATE SET m.REGION = s.REGION, m.AMOUNT = s.AMOUNT
WHEN NOT MATCHED AND s.THE_ACTION = 'INSERT' THEN
    INSERT (SALE_ID, REGION, AMOUNT) VALUES (s.SALE_ID, s.REGION, s.AMOUNT);

Result1 row · 3 columns

number of rows inserted	number of rows updated	number of rows deleted
1	1	1

One inserted, one updated, one deleted. One statement, three kinds of change.

Query

SELECT COALESCE(s.SALE_ID, m.SALE_ID) AS SALE_ID,
       s.AMOUNT                       AS IN_THE_SOURCE,
       m.AMOUNT                       AS IN_THE_MIRROR,
       CASE WHEN s.SALE_ID IS NULL        THEN 'MIRROR ONLY'
            WHEN m.SALE_ID IS NULL        THEN 'SOURCE ONLY'
            WHEN s.AMOUNT <> m.AMOUNT     THEN 'DIFFERENT'
            ELSE                               'same'
       END                            AS VERDICT
FROM       SALES_T136  s
FULL OUTER JOIN MIRROR_T136 m ON s.SALE_ID = m.SALE_ID
ORDER BY SALE_ID;

Result6 rows · 4 columns

SALE_ID	IN_THE_SOURCE	IN_THE_MIRROR	VERDICT
2	999.99	999.99	same
4	310.00	310.00	same
5	45.00	45.00	same
6	88.25	88.25	same
7	7.00	7.00	same
8	8.00	8.00	same

Six rows, and every verdict reads same. A full outer join is the right way to check this, because it shows you a row that is on one side only as well as a row whose value disagrees.

And the stream

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM MERGESTRM_T136;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

The same answer, twice: the CHANGES clause

There is a way to ask the same question with no stream object at all. Let's use a plain table with nothing watching it.

A plain table

CREATE OR REPLACE TABLE PLAIN_T136 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

Result1 row · 1 column

status
Table PLAIN_T136 successfully created.

Two rows in it

INSERT INTO PLAIN_T136 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 10.00), (2, 'south', 20.00);

Result1 row · 1 column

number of rows inserted
2

First, let's ask the catalog whether change tracking is on.

Query

SELECT TABLE_NAME, CHANGE_TRACKING
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'PLAIN_T136';

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 19
invalid identifier 'CHANGE_TRACKING'

000904, invalid identifier CHANGE_TRACKING. INFORMATION_SCHEMA.TABLES has no such column, so you cannot ask it that question by that name.

So let's just try the clause and see what it says.

Query

SELECT SALE_ID, AMOUNT
FROM PLAIN_T136
CHANGES (INFORMATION => DEFAULT)
AT (OFFSET => -5);

Query failed — Snowflake returned an error

091930 (2F003): SQL compilation error: Change tracking is not enabled or has been missing for the time range requested on table 'DEMO_DB.PUBLIC.PLAIN_T136'.

091930, change tracking is not enabled or has been missing for the time range requested. Fair enough. Let's turn it on.

Query

ALTER TABLE PLAIN_T136 SET CHANGE_TRACKING = TRUE;

Result1 row · 1 column

status
Statement executed successfully.

And ask the catalog again.

Query

SELECT TABLE_NAME, CHANGE_TRACKING
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'PLAIN_T136';

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 19
invalid identifier 'CHANGE_TRACKING'

The same 000904. It is on, and the catalog still will not say so, because the column the question names does not exist either way. SHOW TABLES has a change_tracking column; the catalog view does not.

Now let's ask what changed

Keep a statement id, then make one change of each kind.

Keep the moment

SET QID_BEFORE = LAST_QUERY_ID();

Result1 row · 1 column

status
Statement executed successfully.

An insert

INSERT INTO PLAIN_T136 (SALE_ID, REGION, AMOUNT) VALUES (3, 'east', 30.00);

Result1 row · 1 column

number of rows inserted
1

An update

UPDATE PLAIN_T136 SET AMOUNT = 99.00 WHERE SALE_ID = 1;

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
1	0

A delete

DELETE FROM PLAIN_T136 WHERE SALE_ID = 2;

Result1 row · 1 column

number of rows deleted
1

Query

SELECT SALE_ID,
       AMOUNT,
       METADATA$ACTION    AS THE_ACTION,
       METADATA$ISUPDATE  AS IS_PART_OF_AN_UPDATE
FROM PLAIN_T136
CHANGES (INFORMATION => DEFAULT)
AT (STATEMENT => $QID_BEFORE)
ORDER BY SALE_ID, THE_ACTION;

Result4 rows · 4 columns

SALE_ID	AMOUNT	THE_ACTION	IS_PART_OF_AN_UPDATE
1	10.00	DELETE	TRUE
1	99.00	INSERT	TRUE
2	20.00	DELETE	FALSE
3	30.00	INSERT	FALSE

Four rows, and the shape is familiar: the update is a DELETE and an INSERT both flagged TRUE. Exactly what a stream would have given us.

And now the difference. Let's ask the very same question a second time.

Query

SELECT COUNT(*) AS ROWS_IT_STILL_REPORTS
FROM PLAIN_T136
CHANGES (INFORMATION => DEFAULT)
AT (STATEMENT => $QID_BEFORE);

Result1 row · 1 column

ROWS_IT_STILL_REPORTS
4

Still four. CHANGES does not consume. There is no bookmark to move, because there is no stream object. You name the window yourself, every time.

The rest of the clause, quickly

APPEND_ONLY works here as a mode too.

Query

SELECT SALE_ID,
       AMOUNT,
       METADATA$ACTION AS THE_ACTION
FROM PLAIN_T136
CHANGES (INFORMATION => APPEND_ONLY)
AT (STATEMENT => $QID_BEFORE)
ORDER BY SALE_ID;

Result1 row · 3 columns

SALE_ID	AMOUNT	THE_ACTION
3	30.00	INSERT

One row, just the insert. Now a window whose start and end are the same statement.

Query

SELECT COUNT(*) AS ROWS_IN_THAT_WINDOW
FROM PLAIN_T136
CHANGES (INFORMATION => DEFAULT)
AT (STATEMENT => $QID_BEFORE)
END (STATEMENT => $QID_BEFORE);

Result1 row · 1 column

ROWS_IN_THAT_WINDOW
0

Zero. An empty window is allowed and gives you nothing, which is the correct answer rather than an error.

And a row that arrives and leaves inside one window.

INSERT INTO PLAIN_T136 (SALE_ID, REGION, AMOUNT) VALUES (99, 'nowhere', 1.00);

Result1 row · 1 column

number of rows inserted
1

Out

DELETE FROM PLAIN_T136 WHERE SALE_ID = 99;

Result1 row · 1 column

number of rows deleted
1

Query

SELECT COUNT(*) AS ROWS_MENTIONING_NINETY_NINE
FROM PLAIN_T136
CHANGES (INFORMATION => DEFAULT)
AT (STATEMENT => $QID_BEFORE)
WHERE SALE_ID = 99;

Result1 row · 1 column

ROWS_MENTIONING_NINETY_NINE
0

Never mentioned. Net, exactly like a stream. Whatever else CHANGES is, it is not an audit trail either.

And what CHANGES refuses

Three quick ones. First, the clause with no AT at all.

Query

SELECT SALE_ID FROM PLAIN_T136 CHANGES (INFORMATION => DEFAULT);

Result0 rows · 1 column

SALE_ID

Accepted, and it returns nothing. No error, no rows, no window. Worth knowing, because it is the one that will sit in a pipeline looking fine.

An INFORMATION value that is not a thing

SELECT SALE_ID FROM PLAIN_T136
CHANGES (INFORMATION => EVERYTHING)
AT (OFFSET => -5);

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 2 at position 24 unexpected 'EVERYTHING'.
syntax error line 2 at position 34 unexpected ')'.

001003, a syntax error. INFORMATION takes DEFAULT or APPEND_ONLY, and anything else does not reach the planner.

And a table that is not there

SELECT SALE_ID FROM NOSUCHTABLE_T136
CHANGES (INFORMATION => DEFAULT)
AT (OFFSET => -5);

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'NOSUCHTABLE_T136' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE NOSUCHTABLE_T136.

And the cleanup

Streams, then tables, and then a check that we are not sitting in a transaction we forgot to close.

Query

SELECT CURRENT_DATABASE()    AS THE_DATABASE,
       CURRENT_SCHEMA()      AS THE_SCHEMA,
       CURRENT_TRANSACTION() AS THE_OPEN_TRANSACTION;

Result1 row · 3 columns

THE_DATABASE	THE_SCHEMA	THE_OPEN_TRANSACTION
DEMO_DB	PUBLIC	NULL

DEMO_DB, PUBLIC, and CURRENT_TRANSACTION() is a real NULL. That NULL is the whole point of the grid: it is what no open transaction looks like, and it is not an empty string.

Streams left

SHOW STREAMS LIKE '%\\_T136';

Result0 rows · 15 columns · scroll →

created_on	name	database_name	schema_name	owner	comment	table_name	source_type	base_tables	type	stale	mode	stale_after	invalid_reason	owner_role_type

Tables left

SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND ENDSWITH(TABLE_NAME, '_T136');

Result1 row · 1 column

PROBE_TABLES_LEFT
0

And the second script's own check

SELECT CURRENT_DATABASE()    AS THE_DATABASE,
       CURRENT_SCHEMA()      AS THE_SCHEMA,
       CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION,
       (SELECT COUNT(*) FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'PUBLIC'
          AND ENDSWITH(TABLE_NAME, '_T136B')) AS PROBE_TABLES_LEFT;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	ANY_OPEN_TRANSACTION	PROBE_TABLES_LEFT
DEMO_DB	PUBLIC	NULL	0

Nothing left on either side, and no transaction open.

Key points

A stream is consumed by appearing in a DML statement, not by being read.
Consuming reads the source table and never writes to it. It stayed at three rows and 635.75 throughout.
A DML that inserts zero rows still moves the offset. The audit table stayed at 2 rows and the stream went to 0.
So a change can be lost with nothing anywhere recording it.
A SELECT is free, even twice inside one statement.
CREATE TABLE AS SELECT consumes a stream. It reads like DDL and moves the offset like a DML.
UPDATE and DELETE aimed at a stream are refused with 001759.
Inside the consuming transaction the audit table holds the rows and the stream still offers them.
ROLLBACK gives the stream's rows back. COMMIT is what moves the offset.
Two streams on one table advance independently.
A stream can drive a MERGE once the update's DELETE half is filtered out by METADATA$ISUPDATE.
CHANGES needs no stream object and does not consume. Asked twice, it answered 4 twice.
INFORMATION_SCHEMA.TABLES has no CHANGE_TRACKING column, before or after you turn it on.

Next in this series

Episode 137 — tasks. The only object in this course that acts with no session behind it: how one arrives, what EXECUTE TASK really does, and three ways a task can look fine and never run.

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. Some grids come from a second, shorter run of that script against its own tables, whose names end in B, and the post says so where they appear. The first transaction test is shown as what it was — an attempt whose answer could only be zero — rather than being quietly dropped. Every count in the text is read off one of the grids on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake stream management documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 136 - A Statement That Inserted Zero Rows Still Emptied My Stream

Let's create some sample data

Let's give it two changes and then consume them

Now the one that matters: a statement that records nothing

So what counts as a DML? A SELECT is free, a CTAS is not

And two statements a stream will not take at all

When exactly does the offset move? Let's use a transaction

So here is the same test, written so the answer can be no

Two streams on one table, and a stream driving a MERGE

And now the thing people actually build

The same answer, twice: the CHANGES clause

Now let's ask what changed

The rest of the clause, quickly

And what CHANGES refuses

And the cleanup

Key points

No comments:

Post a Comment