Welcome To TechBrothersIT: Snowflake SQL Tutorial 135 - A Stream Said It Had Data And Then Returned Zero Rows

Snowflake SQL Tutorial 135 - A Stream Said It Had Data And Then Returned Zero Rows

Snowflake SQL · Episode 135

In this post we are going to cover streams in Snowflake.

A stream is a bookmark into a table's change tracking. You read from it like a table, and what you get back is the net difference between where the bookmark sits and now. It is not a log of everything that happened, and it is not a trigger that fires when something does.

We will start with a table that already has rows in it 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 135 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Now let's put a stream on it
Let's look at what the stream object actually is
Now let's insert a row, with the stream watching
What does an UPDATE look like to a stream?
Can a stream tell you a row was ever there?
Append-only is a mode, not a third kind of stream
Can we put a stream on a view?
What happens when the base table goes?
Where does stream metadata actually live?
A few more things a stream will not do
And the cleanup

Let's create some sample data

Four rows, all of them put there before anybody is watching. That matters in a moment.

Sample data

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

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

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

Query

SELECT COUNT(*)    AS ROWS_IN_THE_TABLE,
       SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T135;

Result1 row · 2 columns

ROWS_IN_THE_TABLE	TOTAL_AMOUNT
4	735.75

Four rows and 735.75.

And before we make anything, let's see what the catalog says about this table.

Query

SHOW TABLES LIKE 'SALES\\_T135' IN SCHEMA PUBLIC;

Result1 row · 27 columns · scroll →

created_on	name	database_name	schema_name	kind	comment	cluster_by	rows	bytes	owner	retention_time	automatic_clustering	change_tracking	search_optimization	search_optimization_progress	search_optimization_bytes	is_external	enable_schema_evolution	owner_role_type	is_event	is_hybrid	is_iceberg	is_dynamic	is_immutable	is_interactive	row_timestamp	error_logging
2026-10-02 08:46:30.738000-07:00	SALES_T135	DEMO_DB	PUBLIC	TABLE			4	1024	ACCOUNTADMIN	1	OFF	OFF	OFF	NULL	NULL	N	N	ROLE	N	N	N	N	N	N	OFF	OFF

Twenty seven columns. The one to find is change_tracking, and right now it reads OFF. Have a look at the comment and cluster_by cells while you are in there: those are empty strings, and the grid marks them as empty. The two search_optimization cells beside them are real NULLs. Both look like nothing and they are not the same thing.

Now let's put a stream on it

One statement, no options.

Query

CREATE OR REPLACE STREAM STRM_T135 ON TABLE SALES_T135;

Result1 row · 1 column

status
Stream STRM_T135 successfully created.

Creating a stream turns change tracking on for the table underneath it. The obvious next move is to ask the catalog view to confirm that, and this is where the demo ran into a wall.

Query

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

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.

Which leaves this run with one reading of change_tracking, the SHOW TABLES one from before the stream existed, and none from after. So this post is not going to tell you what the flag said afterwards. Re-run SHOW TABLES on your own account if you want to watch it change.

Now the important question. There are four rows in the table. What does a brand new stream offer us?

Query

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T135;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

Zero.

Query

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

Result1 row · 1 column

THE_STREAM_HAS_DATA
FALSE

And the function agrees. FALSE.

Because the bookmark starts at now, not at the beginning. A stream knows nothing about the rows that were already in the table when you made it. Obvious once you say it, and it catches everybody who expected a backlog.

Let's look at what the stream object actually is

SHOW STREAMS is how you find them.

Query

SHOW STREAMS LIKE '%\\_T135';

Result1 row · 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
2026-10-02 08:46:32.774000-07:00	STRM_T135	DEMO_DB	PUBLIC	ACCOUNTADMIN		DEMO_DB.PUBLIC.SALES_T135	Table	DEMO_DB.PUBLIC.SALES_T135	DELTA	false	DEFAULT	2026-10-16 08:46:31.492000-07:00	N/A	ROLE

Fifteen columns. The ones worth reading are table_name, source_type, type, stale, mode and stale_after.

type is DELTA and mode is DEFAULT. Keep those two in mind, because we are going to come back to them when people start talking about three kinds of stream.

And stale_after reads 2026-10-16, while the table's own retention_time in the grid above was 1. A stream's deadline is its own, not the table's.

Now let's insert a row, with the stream watching

One row, after the stream exists.

Query

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

Result1 row · 1 column

number of rows inserted
1

And read the stream with SELECT *, so we can see everything it gives us.

Query

SELECT * FROM STRM_T135;

Result1 row · 6 columns · scroll →

SALE_ID	REGION	AMOUNT	METADATA$ACTION	METADATA$ISUPDATE	METADATA$ROW_ID
5	north	45.00	INSERT	FALSE	4448469ba9be1b2f287e96e524074242281cd27c

Six columns. Three of them are our table — SALE_ID, REGION and AMOUNT. The other three are the stream: METADATA$ACTION, METADATA$ISUPDATE and METADATA$ROW_ID.

The action is INSERT, METADATA$ISUPDATE is FALSE, and the row id is a hash. Hold on to that row id, it earns its keep shortly.

Now let's read the same stream again, without consuming anything.

Query

SELECT COUNT(*) AS ROWS_THE_STREAM_STILL_OFFERS FROM STRM_T135;

Result1 row · 1 column

ROWS_THE_STREAM_STILL_OFFERS
1

Query

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

Result1 row · 1 column

THE_STREAM_HAS_DATA
TRUE

Still one row, and still TRUE. Reading a stream does not change it. A SELECT is free.

What does an UPDATE look like to a stream?

Let's change one of the original rows. Sale two, from 250.50 to 999.99.

Query

UPDATE SALES_T135 SET AMOUNT = 999.99 WHERE SALE_ID = 2;

Result1 row · 2 columns

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

One row updated. Now ask the stream.

Query

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

Result3 rows · 5 columns

SALE_ID	REGION	AMOUNT	THE_ACTION	IS_PART_OF_AN_UPDATE
2	south	250.50	DELETE	TRUE
2	south	999.99	INSERT	TRUE
5	north	45.00	INSERT	FALSE

Three rows, for one insert and one update. The update arrived as a pair: a DELETE carrying the old value and an INSERT carrying the new one, both marked TRUE in IS_PART_OF_AN_UPDATE.

The row from the earlier plain insert is still marked FALSE.

That flag is the whole point of it. Without it you could not tell this pair from a real delete and a real insert that happened to land in the same batch.

Grouped by action and flag

SELECT METADATA$ACTION   AS THE_ACTION,
       METADATA$ISUPDATE AS IS_PART_OF_AN_UPDATE,
       COUNT(*)            AS HOW_MANY
FROM STRM_T135
GROUP BY 1, 2
ORDER BY 1, 2;

Result3 rows · 3 columns

THE_ACTION	IS_PART_OF_AN_UPDATE	HOW_MANY
DELETE	TRUE	1
INSERT	FALSE	1
INSERT	TRUE	1

One delete that is part of an update, one insert that is part of an update, and one insert that is not.

And now the row id earns its keep

Let's delete a row for real as well, so there is something to compare against.

Query

DELETE FROM SALES_T135 WHERE SALE_ID = 4;

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 STRM_T135
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
4	310.00	DELETE	FALSE
5	45.00	INSERT	FALSE

Four rows now. Sale four is a DELETE with the flag FALSE, because that one really was a delete.

Now ask for the row id alongside the action.

Query

SELECT SALE_ID,
       METADATA$ACTION  AS THE_ACTION,
       METADATA$ROW_ID  AS THE_ROW_ID
FROM STRM_T135
ORDER BY SALE_ID, THE_ACTION;

Result4 rows · 3 columns

SALE_ID	THE_ACTION	THE_ROW_ID
2	DELETE	f7fd3276706738e29add7a2c90afe5840ab5e6bd
2	INSERT	f7fd3276706738e29add7a2c90afe5840ab5e6bd
4	DELETE	ea4edd668675523e3d2aaf2d866d939bd847a975
5	INSERT	4448469ba9be1b2f287e96e524074242281cd27c

The delete and the insert for sale two carry the same row id. The same hash, twice, because it is one row before and after. Sale four has its own, and sale five has another.

So you can pair the two halves of an update up yourself, in SQL, instead of guessing from the values.

Can a stream tell you a row was ever there?

This is the question this episode exists for.

Let's use a second stream, so the first one is not disturbed.

Query

CREATE OR REPLACE STREAM NETSTRM_T135 ON TABLE SALES_T135;

Result1 row · 1 column

status
Stream NETSTRM_T135 successfully created.

Query

SELECT COUNT(*) AS ROWS_THE_NEW_STREAM_OFFERS FROM NETSTRM_T135;

Result1 row · 1 column

ROWS_THE_NEW_STREAM_OFFERS
0

Zero, as we now expect. Now a row that is not going to survive.

Query

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

Result1 row · 1 column

number of rows inserted
1

Query

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

Result1 row · 3 columns

SALE_ID	AMOUNT	THE_ACTION
99	1.00	INSERT

There it is. Sale 99, an insert. Now let's delete that same row, before anybody has consumed anything.

Query

DELETE FROM SALES_T135 WHERE SALE_ID = 99;

Result1 row · 1 column

number of rows deleted
1

And ask the stream what it has to say about it.

Query

SELECT COUNT(*) AS ROWS_THE_NET_STREAM_OFFERS FROM NETSTRM_T135;

Result1 row · 1 column

ROWS_THE_NET_STREAM_OFFERS
0

Zero rows. The row arrived and left, and the stream has nothing to say about either event.

Now ask the function.

Query

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

Result1 row · 1 column

THE_STREAM_HAS_DATA
TRUE

TRUE.

A read returns nothing while SYSTEM$STREAM_HAS_DATA says yes, at the same moment, on the same stream. Those are not the same question. The function answers at the change log level. The read answers at the net level.

And the table itself

SELECT COUNT(*) AS ROWS_IN_THE_TABLE FROM SALES_T135;

Result1 row · 1 column

ROWS_IN_THE_TABLE
4

Net means net

A stream cannot tell you that a row was ever there. It is not an audit trail, and nobody should build one on it. If you need to know that sale 99 existed for ten seconds, a stream will not give you that, however often you read it.

And the same thing happens for an update that changes nothing

A third stream, and then a row set to the value it already holds.

Query

CREATE OR REPLACE STREAM SAMESTRM_T135 ON TABLE SALES_T135;

Result1 row · 1 column

status
Stream SAMESTRM_T135 successfully created.

Query

UPDATE SALES_T135 SET AMOUNT = 100.00 WHERE SALE_ID = 1;

Result1 row · 2 columns

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

One row updated, says the statement. And the stream?

Query

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM SAMESTRM_T135;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
0

And what they are

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

Result0 rows · 4 columns

SALE_ID	AMOUNT	THE_ACTION	IS_PART_OF_AN_UPDATE

Zero rows, and an empty grid. The UPDATE reported one row and the net change is nothing, so there is nothing to offer.

Append-only is a mode, not a third kind of stream

People talk about three kinds of stream. The catalog does not, and it is worth seeing why.

Let's make an append-only stream on the same table.

Query

CREATE OR REPLACE STREAM APPSTRM_T135 ON TABLE SALES_T135 APPEND_ONLY = TRUE;

Result1 row · 1 column

status
Stream APPSTRM_T135 successfully created.

Query

SHOW STREAMS LIKE 'APPSTRM\\_T135';

Result1 row · 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
2026-10-02 08:46:43.129000-07:00	APPSTRM_T135	DEMO_DB	PUBLIC	ACCOUNTADMIN		DEMO_DB.PUBLIC.SALES_T135	Table	DEMO_DB.PUBLIC.SALES_T135	DELTA	false	APPEND_ONLY	2026-10-16 08:46:41.852000-07:00	N/A	ROLE

type is DELTA, exactly like the ordinary stream. The column that differs is mode, which reads APPEND_ONLY. So append-only is a mode on a delta stream.

DESCRIBE STREAM says the same thing. These two grids come from a second, shorter run of the script against its own table, SALES_T135B, which is why the names in them have a B on the end.

DESCRIBE on an ordinary stream

DESCRIBE STREAM DEMO_DB.PUBLIC.STRM_T135B;

Result1 row · 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
2026-10-02 09:50:38.381000-07:00	STRM_T135B	DEMO_DB	PUBLIC	ACCOUNTADMIN		DEMO_DB.PUBLIC.SALES_T135B	Table	DEMO_DB.PUBLIC.SALES_T135B	DELTA	false	DEFAULT	2026-10-16 09:50:38.100000-07:00	N/A	ROLE

DESCRIBE on the append-only one

DESCRIBE STREAM DEMO_DB.PUBLIC.APPSTRM_T135B;

Result1 row · 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
2026-10-02 09:50:44.458000-07:00	APPSTRM_T135B	DEMO_DB	PUBLIC	ACCOUNTADMIN		DEMO_DB.PUBLIC.SALES_T135B	Table	DEMO_DB.PUBLIC.SALES_T135B	DELTA	false	APPEND_ONLY	2026-10-16 09:50:38.100000-07:00	N/A	ROLE

Fifteen columns each, the same fifteen SHOW STREAMS gives. Both say DELTA. Only mode differs.

So what does the mode actually do?

Let's insert one row and delete a different one, and then ask both kinds of stream.

The insert

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

Result1 row · 1 column

number of rows inserted
1

The delete

DELETE FROM SALES_T135 WHERE SALE_ID = 3;

Result1 row · 1 column

number of rows deleted
1

The append-only stream

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

Result1 row · 3 columns

SALE_ID	AMOUNT	THE_ACTION
6	88.25	INSERT

One row. The insert. The delete is simply not its business.

An ordinary stream over the same two statements

SELECT METADATA$ACTION AS THE_ACTION, COUNT(*) AS HOW_MANY
FROM SAMESTRM_T135
GROUP BY 1
ORDER BY 1;

Result2 rows · 2 columns

THE_ACTION	HOW_MANY
DELETE	1
INSERT	1

A delete and an insert. Same two statements, two different answers, and the only difference between the streams is one word in the CREATE.

And the third kind, which is not available here

Query

CREATE OR REPLACE STREAM INSSTRM_T135 ON TABLE SALES_T135 INSERT_ONLY = TRUE;

Query failed — Snowflake returned an error

091938 (0A000): Streams of type INSERT_ONLY can only be created on external tables or Iceberg tables with an external catalog integration.

091938. A useful refusal, because it names the two places INSERT_ONLY does work: external tables, and Iceberg tables with an external catalog integration. It is not a mode you can put on an ordinary table.

Can we put a stream on a view?

Yes, and what it sees is worth checking. Let's make a view that only shows the north rows, and a stream on that.

The view

CREATE OR REPLACE VIEW SALESVIEW_T135 AS
SELECT SALE_ID, REGION, AMOUNT FROM SALES_T135 WHERE REGION = 'north';

Result1 row · 1 column

status
View SALESVIEW_T135 successfully created.

The stream

CREATE OR REPLACE STREAM VIEWSTRM_T135 ON VIEW SALESVIEW_T135;

Result1 row · 1 column

status
Stream VIEWSTRM_T135 successfully created.

Now one row the view can see, and one it filters out.

A north row

INSERT INTO SALES_T135 (SALE_ID, REGION, AMOUNT) VALUES (7, 'north', 7.00);

Result1 row · 1 column

number of rows inserted
1

A west row

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

Result1 row · 1 column

number of rows inserted
1

Query

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

Result1 row · 4 columns

SALE_ID	REGION	AMOUNT	THE_ACTION
7	north	7.00	INSERT

One row, the north one. The west row never appears. A stream over a view sees exactly what the view sees, and nothing else.

And a stream that starts in the past

A stream can be created as of an earlier statement, the same way a clone can. We keep a statement id, insert a row after it, and then make the stream.

Keep the moment

SET QID_NOW = LAST_QUERY_ID();

Result1 row · 1 column

status
Statement executed successfully.

A row

INSERT INTO SALES_T135 (SALE_ID, REGION, AMOUNT) VALUES (9, 'east', 9.00);

Result1 row · 1 column

number of rows inserted
1

The stream

CREATE OR REPLACE STREAM PASTSTRM_T135 ON TABLE SALES_T135
BEFORE (STATEMENT => $QID_NOW);

Result1 row · 1 column

status
Stream PASTSTRM_T135 successfully created.

Query

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

Result1 row · 3 columns

SALE_ID	AMOUNT	THE_ACTION
9	9.00	INSERT

One row waiting, the moment the stream exists. Sale 9, an insert. So "a stream starts at now" is the default rather than a rule.

What happens when the base table goes?

Let's drop the table all of these streams are watching.

Query

DROP TABLE SALES_T135;

Result1 row · 1 column

status
SALES_T135 successfully dropped.

And ask whether the stream object survived.

Query

SHOW STREAMS LIKE 'STRM\\_T135';

Result1 row · 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
2026-10-02 08:46:32.774000-07:00	STRM_T135	DEMO_DB	PUBLIC	ACCOUNTADMIN		No privilege or table dropped	Table	No privilege or table dropped	DELTA	true	DEFAULT	NULL	N/A	ROLE

It is still listed. Three columns have changed though. stale now reads true, table_name and base_tables read No privilege or table dropped, and stale_after is now a real NULL rather than a date. The comment cell beside it is still an empty string, which the grid marks as empty.

Now let's read it.

Query

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T135;

Query failed — Snowflake returned an error

091901 (01000): Base table 'DEMO_DB.PUBLIC.SALES_T135' dropped, cannot read from stream 'STRM_T135' in line 1 position 47.

091901, Base table … dropped, cannot read from stream. The object survives and it is useless.

Let's bring the table back.

Query

UNDROP TABLE SALES_T135;

Result1 row · 1 column

status
Table SALES_T135 successfully restored.

Query

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T135;

Result1 row · 1 column

ROWS_THE_STREAM_OFFERS
9

Nine rows. UNDROP fixes a stream.

And now the one worth remembering

Let's replace the table with a new one of the same name and the same columns.

Query

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

Result1 row · 1 column

status
Table SALES_T135 successfully created.

Query

SELECT COUNT(*) AS ROWS_THE_STREAM_OFFERS FROM STRM_T135;

Query failed — Snowflake returned an error

091901 (01000): Base table 'DEMO_DB.PUBLIC.SALES_T135' dropped, cannot read from stream 'STRM_T135' in line 1 position 47.

The same 091901. A replaced table is a new table as far as the stream is concerned, whatever it is called and whatever shape it is.

And the listing

SHOW STREAMS LIKE 'STRM\\_T135';

Result1 row · 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
2026-10-02 08:46:32.774000-07:00	STRM_T135	DEMO_DB	PUBLIC	ACCOUNTADMIN		No privilege or table dropped	Table	No privilege or table dropped	DELTA	true	DEFAULT	NULL	N/A	ROLE

Still listed, still stale true, still looking fine from a distance.

The one to remember

UNDROP repairs a stream and CREATE OR REPLACE does not. So a deploy script that rebuilds a table with CREATE OR REPLACE breaks every stream on it permanently, and the catalog will not warn you. SHOW STREAMS and the stale column are the only places it shows.

Where does stream metadata actually live?

The obvious guess is the information schema, so let's try it.

Query

SELECT COUNT(*) AS STREAM_ROWS_IN_THE_CATALOG
FROM DEMO_DB.INFORMATION_SCHEMA.STREAMS;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.INFORMATION_SCHEMA.STREAMS' 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 DEMO_DB.INFORMATION_SCHEMA.STREAMS.

002003, it does not exist. The same thing happens from inside the database with a schema filter on it.

Query

SELECT COUNT(*) AS STREAM_ROWS_IN_THE_CATALOG
FROM INFORMATION_SCHEMA.STREAMS
WHERE STREAM_SCHEMA = 'PUBLIC';

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.INFORMATION_SCHEMA.STREAMS' 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 DEMO_DB.INFORMATION_SCHEMA.STREAMS.

So let's ask which information schema views have the word STREAM in their name at all.

Query

SELECT TABLE_NAME
FROM DEMO_DB.INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
  AND TABLE_NAME ILIKE '%STREAM%'
ORDER BY TABLE_NAME;

Result1 row · 1 column

TABLE_NAME
STREAMLITS

One hit, STREAMLITS, which is a different product entirely. There is no streams view in the information schema.

There is one a level up, though, and it is worth knowing about.

Query

SELECT COUNT(*) AS STREAMS_IN_ACCOUNT_USAGE
FROM SNOWFLAKE.ACCOUNT_USAGE.STREAMS
WHERE DELETED IS NULL;

Result1 row · 1 column

STREAMS_IN_ACCOUNT_USAGE
1

SNOWFLAKE.ACCOUNT_USAGE.STREAMS does exist, and at that moment it reported one undeleted stream. That is an account-level view with its own latency, so it is for auditing rather than for checking what you just made.

For right now, it is SHOW STREAMS, and RESULT_SCAN if you want a grid you can query.

SHOW STREAMS

SHOW STREAMS LIKE '%T135B';

Result2 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
2026-10-02 09:50:44.458000-07:00	APPSTRM_T135B	DEMO_DB	PUBLIC	ACCOUNTADMIN		DEMO_DB.PUBLIC.SALES_T135B	Table	DEMO_DB.PUBLIC.SALES_T135B	DELTA	false	APPEND_ONLY	2026-10-16 09:50:38.100000-07:00	N/A	ROLE
2026-10-02 09:50:38.381000-07:00	STRM_T135B	DEMO_DB	PUBLIC	ACCOUNTADMIN		DEMO_DB.PUBLIC.SALES_T135B	Table	DEMO_DB.PUBLIC.SALES_T135B	DELTA	false	DEFAULT	2026-10-16 09:50:38.100000-07:00	N/A	ROLE

The same result, as a query

SELECT "name", "table_name", "type", "mode", "stale"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result2 rows · 5 columns

name	table_name	type	mode	stale
APPSTRM_T135B	DEMO_DB.PUBLIC.SALES_T135B	DELTA	APPEND_ONLY	false
STRM_T135B	DEMO_DB.PUBLIC.SALES_T135B	DELTA	DEFAULT	false

Two rows, and the five columns we actually wanted. Note the double quotes around the column names: SHOW output is lower case, so RESULT_SCAN needs them quoted.

A few more things a stream will not do

Four refusals, quickly.

A stream on a table that is not there

CREATE OR REPLACE STREAM NOSOURCE_T135 ON TABLE NOSUCHTABLE_T135;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Table 'NOSUCHTABLE_T135' 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_T135.

A stream on a stream

CREATE OR REPLACE STREAM ONASTREAM_T135 ON TABLE STRM_T135;

Query failed — Snowflake returned an error

091063 (42601): Stream target type "STREAM" unsupported. Expected one of the following: Table, View, External Table, Directory Table, Event Table, Dynamic Table, Interactive Table.

091063, and the message is worth keeping: it names every target type a stream does accept.

HAS_DATA on a stream that is not there

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

Query failed — Snowflake returned an error

001046 (22023): SQL compilation error:
Invalid value ['NOSUCHSTREAM_T135'] for function 'SYSTEM$STREAM_HAS_DATA', parameter 1: must be a valid stream name

001046. The function will not quietly answer FALSE for a name that does not exist, which is the right way round.

INSERT into a stream

INSERT INTO STRM_T135 (SALE_ID) VALUES (1);

Query failed — Snowflake returned an error

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

001759, INSERT statement's target must be a table. A stream is readable, not writable.

And a mode that is not a thing

CREATE OR REPLACE STREAM BADMODE_T135 ON TABLE SALES_T135 APPEND_ONLY = MAYBE;

Query failed — Snowflake returned an error

001008 (22023): SQL compilation error:
invalid value [MAYBE] for parameter 'APPEND_ONLY'

And the cleanup

Streams are cheap but they are not free, and a stale one sitting on a table is a trap for the next person. So the script drops every one of them and then checks.

Where we are

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

Result1 row · 2 columns

THE_DATABASE	THE_SCHEMA
DEMO_DB	PUBLIC

Streams left

SHOW STREAMS LIKE '%\\_T135';

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, '_T135');

Result1 row · 1 column

PROBE_TABLES_LEFT
0

An empty listing and a zero. Nothing of this episode's is left on the account.

Key points

A stream's bookmark starts at now. Four rows were already in the table and the stream offered 0.
A stream read adds three columns: METADATA$ACTION, METADATA$ISUPDATE and METADATA$ROW_ID.
Reading a stream consumes nothing. The same read twice gave 1 both times.
An UPDATE arrives as a DELETE and an INSERT, both with METADATA$ISUPDATE TRUE, and both halves carry the same METADATA$ROW_ID.
A read can return zero rows while SYSTEM$STREAM_HAS_DATA says TRUE. The read is net, the function is not.
So a stream cannot tell you a row was ever there. It is not an audit trail. An update that changes nothing reads as zero rows too.
Append-only is a mode on a DELTA stream, not a third type. INSERT_ONLY needs an external or Iceberg table (091938).
stale_after is the stream's own deadline, not the table's retention.
A stream on a view sees exactly what the view sees.
A stream can be created as of a past statement, and then it has rows waiting the moment it exists.
INFORMATION_SCHEMA has no streams view. STREAMLITS is a different product. SHOW STREAMS plus RESULT_SCAN is the answer.
Dropping the base table leaves the stream object listed and unreadable (091901). UNDROP repairs it. CREATE OR REPLACE does not.

Next in this series

Episode 136 — consuming a stream. What actually moves the bookmark, why a statement that inserts zero rows still empties a stream, and the CHANGES clause, which answers the same question without a stream object at all.

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. Two grids come from a second, shorter run of that script against its own table, SALES_T135B, and the post says so where they appear. One grid is deliberately left out: the one that prints the statement id held in a session variable, because a query id belongs to one account's own history. This episode has no reading of change_tracking from after the stream was created, because the statement that would have given it was refused, and no claim is made about it. 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 change tracking using table streams documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 135 - A Stream Said It Had Data And Then Returned Zero Rows

Let's create some sample data

Now let's put a stream on it

Let's look at what the stream object actually is

Now let's insert a row, with the stream watching

What does an UPDATE look like to a stream?

And now the row id earns its keep

Can a stream tell you a row was ever there?

And the same thing happens for an update that changes nothing

Append-only is a mode, not a third kind of stream

So what does the mode actually do?

And the third kind, which is not available here

Can we put a stream on a view?

And a stream that starts in the past

What happens when the base table goes?

And now the one worth remembering

Where does stream metadata actually live?

A few more things a stream will not do

And the cleanup

Key points

No comments:

Post a Comment