Welcome To TechBrothersIT: Snowflake SQL Tutorial 109 - A Materialized View Is A Much Smaller Language

Snowflake SQL Tutorial 109 - A Materialized View Is A Much Smaller Language

Snowflake SQL · Episode 109

In this post we are going to cover materialized views in Snowflake.

A materialized view sounds like a view that has been worked out in advance, and that description is what gets people into trouble. It is a third kind of object with a query language of its own, and that language is much smaller than the one an ordinary view takes.

So this post is not mostly about how to write one. It is the list of queries it refuses, in Snowflake's own words, because that list is the real specification. We will start with the smallest one there is and build up to the eight refusals, and then the two things I predicted would be refused and was wrong about.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

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

On this page

Let's create some sample data
Let's start with the smallest materialized view there is
Let's ask the catalog what kind of object that is
Now the shape it is actually built for
Now the refusals. Let's start with a join
Can it take a HAVING clause?
Can it take a COUNT(DISTINCT)?
Can it be built on a view, or on another one of itself?
Two I predicted would be refused, and was wrong about
But created is not the same claim as works
Now the sharp one: a CTE that does HAVING's job
Does it keep up with the base table on its own?
So is it ever behind? SHOW has a column for that
What happens when the base table goes away?
And what happens if we read one?
So let's UNDROP the table
One last thing: the mess this episode left behind

Let's create some sample data

One table. Six orders across three regions, with a customer name on each so we have something to count distinctly later on.

Sample data

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

CREATE OR REPLACE TABLE ORDERS_T109 (
    ORDER_ID INT,
    REGION   VARCHAR(20),
    CUSTOMER VARCHAR(30),
    AMOUNT   NUMBER(10,2)
);

INSERT INTO ORDERS_T109 (ORDER_ID, REGION, CUSTOMER, AMOUNT)
SELECT 1, 'north', 'Ana',  100.00
UNION ALL SELECT 2, 'north', 'Ben',  250.00
UNION ALL SELECT 3, 'south', 'Cleo',  75.50
UNION ALL SELECT 4, 'south', 'Dan',  310.00
UNION ALL SELECT 5, 'east',  'Eve',   40.00
UNION ALL SELECT 6, 'east',  'Finn',  60.00;

Query

SELECT ORDER_ID, REGION, CUSTOMER, AMOUNT FROM ORDERS_T109 ORDER BY ORDER_ID;

Result6 rows · 4 columns

ORDER_ID	REGION	CUSTOMER	AMOUNT
1	north	Ana	100.00
2	north	Ben	250.00
3	south	Cleo	75.50
4	south	Dan	310.00
5	east	Eve	40.00
6	east	Finn	60.00

Materialized views need the Enterprise edition or above, so if any statement below is refused on your account with a message about the edition, that is why.

Let's start with the smallest materialized view there is

Two columns, one table, no filtering and no grouping. If this account has materialized views at all, this will work.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_PROBE_T109 AS
SELECT ORDER_ID, AMOUNT FROM ORDERS_T109;

Result1 row · 1 column

status
Materialized view MV_PROBE_T109 successfully created.

Created, and the status line calls it a materialized view rather than a view or a table. Let's read it.

Query

SELECT ORDER_ID, AMOUNT FROM MV_PROBE_T109 ORDER BY ORDER_ID;

Result6 rows · 2 columns

ORDER_ID	AMOUNT
1	100.00
2	250.00
3	75.50
4	310.00
5	40.00
6	60.00

Six rows, straight away. Reading one looks like reading a table, the same as a view does.

Let's ask the catalog what kind of object that is

This is where a materialized view stops looking like a view.

Query

SELECT TABLE_NAME, TABLE_TYPE, IS_INSERTABLE_INTO
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'MV_PROBE_T109';

Result1 row · 3 columns

TABLE_NAME	TABLE_TYPE	IS_INSERTABLE_INTO
MV_PROBE_T109	MATERIALIZED VIEW	NO

TABLE_TYPE is MATERIALIZED VIEW, in its own words. Not BASE TABLE and not VIEW. And IS_INSERTABLE_INTO is NO, which is a promise worth remembering for later in the post.

Now let's ask INFORMATION_SCHEMA.VIEWS about the same object.

Query

SELECT COUNT(*) AS ROWS_IN_INFORMATION_SCHEMA_VIEWS
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'MV_PROBE_T109';

Result1 row · 1 column

ROWS_IN_INFORMATION_SCHEMA_VIEWS
0

Zero rows. As far as that catalog is concerned there is no such object. So a materialized view is a third kind of thing, and it has a SHOW command of its own.

Query

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Result1 row · 22 columns · scroll →

Twenty-two columns come back. Scroll sideways for invalid, invalid_reason and behind_by.

created_on	name	reserved	database_name	schema_name	cluster_by	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	comment	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:25:28.263000-07:00	MV_PROBE_T109		DEMO_DB	PUBLIC		6	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	false	NULL	0s		CREATE OR REPLACE MATERIALIZED VIEW MV_PROBE_T109 AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109	false	OFF	ROLE

There is a lot in there and most of it is other business. Four columns are the ones we will keep coming back to: rows, invalid, invalid_reason and behind_by. Note that rows has a number in it, unlike a view, because this object really does store its result.

A SHOW is not a query, so you cannot put a WHERE clause on it. What you can do is read its output back as a result set.

Query

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 22 columns · scroll →

created_on	name	reserved	database_name	schema_name	cluster_by	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	comment	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:25:28.263000-07:00	MV_PROBE_T109		DEMO_DB	PUBLIC		6	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	false	NULL	0s		CREATE OR REPLACE MATERIALIZED VIEW MV_PROBE_T109 AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109	false	OFF	ROLE

Same row, now as something we can select columns out of. That is the trick we use for the rest of the post, because twenty-two columns is too many to read at once.

Now the shape it is actually built for

A materialized view earns its keep when it holds an aggregate that would otherwise be recomputed all day. Let's write that one: a count and a sum, grouped by region, over one table.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_REGION_TOTALS_T109 AS
SELECT REGION,
       COUNT(*)    AS ORDERS,
       SUM(AMOUNT) AS TOTAL
FROM ORDERS_T109
GROUP BY REGION;

Result1 row · 1 column

status
Materialized view MV_REGION_TOTALS_T109 successfully created.

Query

SELECT REGION, ORDERS, TOTAL FROM MV_REGION_TOTALS_T109 ORDER BY REGION;

Result3 rows · 3 columns

REGION	ORDERS	TOTAL
east	2	100.00
north	2	350.00
south	2	385.50

Three rows, one per region. Accepted without complaint, and this is the happy path. Everything from here on is the edges.

Now the refusals. Let's start with a join

Let's give it a second table to join to.

Query

CREATE OR REPLACE TABLE REGIONS_T109 (
    REGION    VARCHAR(20),
    MANAGER   VARCHAR(30)
);

Result1 row · 1 column

status
Table REGIONS_T109 successfully created.

Query

INSERT INTO REGIONS_T109 (REGION, MANAGER)
SELECT 'north', 'Nadia'
UNION ALL SELECT 'south', 'Sam'
UNION ALL SELECT 'east',  'Eli';

Result1 row · 1 column

number of rows inserted
3

Now a materialized view over the two of them. This is an ordinary inner join on a matching column, the kind Episode 110 builds without any complaint at all.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_JOINED_T109 AS
SELECT O.ORDER_ID, O.REGION, R.MANAGER, O.AMOUNT
FROM ORDERS_T109 O
JOIN REGIONS_T109 R ON R.REGION = O.REGION;

Query failed — Snowflake returned an error

002212 (42601): SQL compilation error: error line 0 at position -1
Invalid materialized view definition. More than one table referenced in the view definition

002212, and read the wording: “More than one table referenced in the view definition”. It is not objecting to the word JOIN. It is objecting to the second table.

Now a window function. Row number over partition by region, which is the first thing most people reach for.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_RANKED_T109 AS
SELECT ORDER_ID, REGION, AMOUNT,
       ROW_NUMBER() OVER (PARTITION BY REGION ORDER BY AMOUNT DESC) AS RN
FROM ORDERS_T109;

Query failed — Snowflake returned an error

002216 (42601): SQL compilation error: error line 1 at position 36
Invalid materialized view definition. window functions not allowed in view definition.

Refused, with a different code, 002216, and a message that names the feature: window functions not allowed in view definition.

And an ORDER BY inside the definition.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_ORDERED_T109 AS
SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 ORDER BY AMOUNT DESC;

Query failed — Snowflake returned an error

002239 (0A000): SQL compilation error: error line 2 at position 41
Invalid materialized view definition: ORDER BY not allowed in view definition.

Refused again, and a different code again, 002239. Three refusals, three codes. Each of these is a separate rule rather than one general rule about complexity.

Can it take a HAVING clause?

This is the one I did not know the answer to, and it is a fair question: GROUP BY is allowed, so why would the clause that filters the groups not be?

A note on the name in the next few grids. The first run of this demo left this question and the next one unmarked, because I did not know the answers, and the capture harness of that evening stored nothing for an unmarked failure. The messages were lost. The harness was fixed the same night and a follow-up file asked both questions again with the right markers, on a table called ORDERS_T109C holding the same six orders.

Setup for the ORDERS_T109C run

-- The ORDERS_T109C run: the same six orders, on a name of its own.
CREATE OR REPLACE TABLE ORDERS_T109C (
    ORDER_ID INT,
    REGION   VARCHAR(20),
    CUSTOMER VARCHAR(30),
    AMOUNT   NUMBER(10,2)
);

INSERT INTO ORDERS_T109C (ORDER_ID, REGION, CUSTOMER, AMOUNT)
SELECT 1, 'north', 'Ana',  100.00
UNION ALL SELECT 2, 'north', 'Ben',  250.00
UNION ALL SELECT 3, 'south', 'Cleo',  75.50
UNION ALL SELECT 4, 'south', 'Dan',  310.00
UNION ALL SELECT 5, 'east',  'Eve',   40.00
UNION ALL SELECT 6, 'east',  'Finn',  60.00;

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_TOTALS_T109C AS
SELECT REGION, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL
FROM ORDERS_T109C
GROUP BY REGION;

Result1 row · 1 column

status
Materialized view MV_TOTALS_T109C successfully created.

Query

SELECT REGION, ORDERS, TOTAL FROM MV_TOTALS_T109C ORDER BY REGION;

Result3 rows · 3 columns

REGION	ORDERS	TOTAL
east	2	100.00
north	2	350.00
south	2	385.50

Same three rows as before, on the new table. Now let's add a HAVING to the definition.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_HAVING_T109C AS
SELECT REGION, SUM(AMOUNT) AS TOTAL
FROM ORDERS_T109C
GROUP BY REGION
HAVING SUM(AMOUNT) > 100;

Query failed — Snowflake returned an error

002222 (42601): SQL compilation error: error line 1 at position 36
Invalid materialized view definition. having clause not allowed in view definition.

002222, and the message says it in those words: “having clause not allowed in view definition”. So GROUP BY is in the language and HAVING is not.

Which is annoying rather than fatal, because you can filter the materialized view instead of filtering inside it.

Query

SELECT REGION, TOTAL FROM MV_TOTALS_T109C WHERE TOTAL > 100 ORDER BY REGION;

Result2 rows · 2 columns

REGION	TOTAL
north	350.00
south	385.50

Two regions, which is the answer the HAVING would have given. The filter runs at read time rather than being stored, so the object keeps all three regions and your query picks.

Can it take a COUNT(DISTINCT)?

The other one I did not know. Let's count the distinct customers per region.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_DISTINCT_T109C AS
SELECT REGION, COUNT(DISTINCT CUSTOMER) AS CUSTOMERS
FROM ORDERS_T109C
GROUP BY REGION;

Query failed — Snowflake returned an error

002221 (42601): SQL compilation error: error line 2 at position 15
Invalid materialized view definition. distinct aggregate 'COUNT(DISTINCT ORDERS_T109C.CUSTOMER)' not allowed in view definition.

002221, and the message names the exact expression it objects to: “distinct aggregate 'COUNT(DISTINCT ORDERS_T109C.CUSTOMER)' not allowed in view definition”. It even qualifies the column name for us.

Let's make sure it is the DISTINCT and not the COUNT, by writing the same shape without it.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_PLAIN_COUNT_T109C AS
SELECT REGION, COUNT(*) AS ORDERS
FROM ORDERS_T109C
GROUP BY REGION;

Result1 row · 1 column

status
Materialized view MV_PLAIN_COUNT_T109C successfully created.

Query

SELECT REGION, ORDERS FROM MV_PLAIN_COUNT_T109C ORDER BY REGION;

Result3 rows · 2 columns

REGION	ORDERS
east	2
north	2
south	2

Accepted, and it works. So COUNT is fine and COUNT(DISTINCT ...) is not.

And one more, because it is the obvious way round the problem.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_APPROX_T109C AS
SELECT REGION, APPROX_COUNT_DISTINCT(CUSTOMER) AS CUSTOMERS
FROM ORDERS_T109C
GROUP BY REGION;

Result1 row · 1 column

status
Materialized view MV_APPROX_T109C successfully created.

APPROX_COUNT_DISTINCT is accepted. So the refusal is specifically about the DISTINCT keyword in an aggregate, not about counting distinct things in general.

Can it be built on a view, or on another one of itself?

Back to the first run. Let's make an ordinary view and try to build a materialized view on it.

Query

CREATE OR REPLACE VIEW V_BIG_ORDERS_T109 AS
SELECT ORDER_ID, REGION, AMOUNT FROM ORDERS_T109 WHERE AMOUNT > 70;

Result1 row · 1 column

status
View V_BIG_ORDERS_T109 successfully created.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_ON_A_VIEW_T109 AS
SELECT ORDER_ID, AMOUNT FROM V_BIG_ORDERS_T109;

Query failed — Snowflake returned an error

002214 (42601): SQL compilation error: error line 2 at position 29
Invalid materialized view definition. 'V_BIG_ORDERS_T109' should be a table.

002214, and the message is almost friendly: “'V_BIG_ORDERS_T109' should be a table”. The thing underneath has to be a table.

And on another materialized view?

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_ON_AN_MV_T109 AS
SELECT REGION, TOTAL FROM MV_REGION_TOTALS_T109;

Query failed — Snowflake returned an error

002212 (42601): SQL compilation error: error line 0 at position -1
Invalid materialized view definition. More than one table referenced in the view definition

Refused as well, and with the join's code and the join's message, more than one table referenced. They do not stack.

Last one, which by now should surprise nobody. The catalog told us IS_INSERTABLE_INTO was NO, so let's see what happens if we try anyway.

Query

INSERT INTO MV_PROBE_T109 (ORDER_ID, AMOUNT) SELECT 99, 1.00;

Query failed — Snowflake returned an error

001761 (42601): SQL compilation error:
INSERT operations are not allowed on materialized views

001761. Not allowed on materialized views, in those words.

That is all eight of them. Here they are in one list, with the code each one came back with.

What we asked for	Code	What Snowflake said
a `JOIN`	`002212`	more than one table referenced in the view definition
a window function	`002216`	window functions not allowed in view definition
an `ORDER BY` in the definition	`002239`	`ORDER BY` not allowed in view definition
a `HAVING` clause	`002222`	having clause not allowed in view definition
`COUNT(DISTINCT ...)`	`002221`	distinct aggregate not allowed in view definition
built on an ordinary `VIEW`	`002214`	it should be a table
built on another `MATERIALIZED VIEW`	`002212`	more than one table referenced in the view definition
`INSERT` into one	`001761`	`INSERT` operations are not allowed on materialized views

Every one of those messages is in a grid above this list, exactly as Snowflake returned it.

Query

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 5) = '_T109'
ORDER BY TABLE_NAME;

Result7 rows · 2 columns

TABLE_NAME	TABLE_TYPE
MV_CTE_T109	MATERIALIZED VIEW
MV_PROBE_T109	MATERIALIZED VIEW
MV_REGION_TOTALS_T109	MATERIALIZED VIEW
MV_UNIONED_T109	MATERIALIZED VIEW
ORDERS_T109	BASE TABLE
REGIONS_T109	BASE TABLE
V_BIG_ORDERS_T109	VIEW

And this is what is left in the schema after all that: the two tables, the ordinary view, and the materialized views that were accepted. Have a look at how many of those there are, because two of them are not supposed to be.

Two I predicted would be refused, and was wrong about

I had these two marked as expected failures when I wrote the script. First a UNION ALL of two selects from the same table.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_UNIONED_T109 AS
SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'north'
UNION ALL
SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'south';

Result1 row · 1 column

status
Materialized view MV_UNIONED_T109 successfully created.

Created. Accepted. Not refused at all.

And a CTE: a WITH clause, then a select from it.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_T109 AS
WITH BIG AS (SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE AMOUNT > 100)
SELECT ORDER_ID, AMOUNT FROM BIG;

Result1 row · 1 column

status
Materialized view MV_CTE_T109 successfully created.

Also accepted. So my rule of thumb — anything beyond a plain aggregate over one table will be refused — was wrong twice in a row.

But created is not the same claim as works

That is worth saying carefully. The two statements above returned a success message, and nothing in the first run ever selected from either object. A materialized view that is created and cannot be read would still look like that.

So a second file built both again and read them back. Same six orders, on a table called ORDERS_T109B.

Setup for the ORDERS_T109B run

-- The ORDERS_T109B run: the same six orders again, to read the two
-- accepted objects back.
CREATE OR REPLACE TABLE ORDERS_T109B (
    ORDER_ID INT,
    REGION   VARCHAR(20),
    CUSTOMER VARCHAR(30),
    AMOUNT   NUMBER(10,2)
);

INSERT INTO ORDERS_T109B (ORDER_ID, REGION, CUSTOMER, AMOUNT)
SELECT 1, 'north', 'Ana',  100.00
UNION ALL SELECT 2, 'north', 'Ben',  250.00
UNION ALL SELECT 3, 'south', 'Cleo',  75.50
UNION ALL SELECT 4, 'south', 'Dan',  310.00
UNION ALL SELECT 5, 'east',  'Eve',   40.00
UNION ALL SELECT 6, 'east',  'Finn',  60.00;

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_UNIONED_T109B AS
SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE REGION = 'north'
UNION ALL
SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE REGION = 'south';

Result1 row · 1 column

status
Materialized view MV_UNIONED_T109B successfully created.

Query

SELECT ORDER_ID, AMOUNT FROM MV_UNIONED_T109B ORDER BY ORDER_ID;

Result4 rows · 2 columns

ORDER_ID	AMOUNT
1	100.00
2	250.00
3	75.50
4	310.00

Four rows: the north and south orders, which is what the two halves of the union asked for.

Query

SELECT COUNT(*) AS ROWS_IN_THE_UNION_MV, SUM(AMOUNT) AS TOTAL_IN_IT
FROM MV_UNIONED_T109B;

Result1 row · 2 columns

ROWS_IN_THE_UNION_MV	TOTAL_IN_IT
4	735.50

Counted and summed, so there is no arguing about it. It genuinely works.

Query

SELECT GET_DDL('VIEW', 'MV_UNIONED_T109B') AS THE_STORED_DEFINITION;

Result1 row · 1 column

THE_STORED_DEFINITION
create or replace materialized view MV_UNIONED_T109B( ORDER_ID, AMOUNT ) as SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE REGION = 'north' UNION ALL SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE REGION = 'south';

And the stored definition is the union, written out. Snowflake did not quietly rewrite it into something else.

Now the CTE one.

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_T109B AS
WITH BIG AS (SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE AMOUNT > 100)
SELECT ORDER_ID, AMOUNT FROM BIG;

Result1 row · 1 column

status
Materialized view MV_CTE_T109B successfully created.

Query

SELECT ORDER_ID, AMOUNT FROM MV_CTE_T109B ORDER BY ORDER_ID;

Result2 rows · 2 columns

ORDER_ID	AMOUNT
2	250.00
4	310.00

Result1 row · 1 column

THE_STORED_DEFINITION
create or replace materialized view MV_CTE_T109B( ORDER_ID, AMOUNT ) as WITH BIG AS (SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE AMOUNT > 100) SELECT ORDER_ID, AMOUNT FROM BIG;

Two rows, which are the two orders above the threshold the WITH clause set. Also genuinely works.

Now the sharp one: a CTE that does HAVING's job

Here is the question that makes the whole list worth having. HAVING was refused. A CTE is allowed. So what happens if we write a CTE that aggregates and then filters the aggregate, which is precisely the job HAVING was refused for?

Query

CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_AGG_T109B AS
WITH PER_REGION AS (
    SELECT REGION, SUM(AMOUNT) AS TOTAL FROM ORDERS_T109B GROUP BY REGION
)
SELECT REGION, TOTAL FROM PER_REGION WHERE TOTAL > 100;

Result1 row · 1 column

status
Materialized view MV_CTE_AGG_T109B successfully created.

Accepted.

Query

SELECT REGION, TOTAL FROM MV_CTE_AGG_T109B ORDER BY REGION;

Result2 rows · 2 columns

REGION	TOTAL
north	350.00
south	385.50

And it returns the two regions over the threshold, which is the answer the refused HAVING would have given.

There is no rule of thumb. There is a list.

So there is no rule of thumb here, and that is the point of this post. The same job is refused one way and accepted another way, and the only thing that tells you which is which is the list.
My own rule of thumb would have been wrong twice. Try the statement, read the message, and keep the message.

Query

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 6) = '_T109B'
ORDER BY TABLE_NAME;

Result4 rows · 2 columns

TABLE_NAME	TABLE_TYPE
MV_CTE_AGG_T109B	MATERIALIZED VIEW
MV_CTE_T109B	MATERIALIZED VIEW
MV_UNIONED_T109B	MATERIALIZED VIEW
ORDERS_T109B	BASE TABLE

Query

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Result3 rows · 22 columns · scroll →

Scroll sideways for invalid and behind_by.

created_on	name	database_name	schema_name	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:38:39.997000-07:00	MV_CTE_AGG_T109B	DEMO_DB	PUBLIC	2	1024	DEMO_DB	PUBLIC	ORDERS_T109B	2026-09-28 18:38:36.441000-07:00	2026-09-28 18:38:36.441000-07:00	ACCOUNTADMIN	false	NULL	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_AGG_T109B AS WITH PER_REGION AS ( SELECT REGION, SUM(AMOUNT) AS TOTAL FROM ORDERS_T109B GROUP BY REGION ) SELECT REGION, TOTAL FROM PER_REGION WHERE TOTAL > 100	false	OFF	ROLE
2026-09-28 18:38:38.371000-07:00	MV_CTE_T109B	DEMO_DB	PUBLIC	2	1024	DEMO_DB	PUBLIC	ORDERS_T109B	2026-09-28 18:38:36.441000-07:00	2026-09-28 18:38:36.441000-07:00	ACCOUNTADMIN	false	NULL	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_T109B AS WITH BIG AS (SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE AMOUNT > 100) SELECT ORDER_ID, AMOUNT FROM BIG	false	OFF	ROLE
2026-09-28 18:38:36.775000-07:00	MV_UNIONED_T109B	DEMO_DB	PUBLIC	4	1024	DEMO_DB	PUBLIC	ORDERS_T109B	2026-09-28 18:38:36.441000-07:00	2026-09-28 18:38:36.441000-07:00	ACCOUNTADMIN	false	NULL	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_UNIONED_T109B AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE REGION = 'north' UNION ALL SELECT ORDER_ID, AMOUNT FROM ORDERS_T109B WHERE REGION = 'south'	false	OFF	ROLE

Query

SELECT "name", "rows", "behind_by", "invalid"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%T109B'
ORDER BY "name";

Result3 rows · 4 columns

name	rows	behind_by	invalid
MV_CTE_AGG_T109B	2	0s	false
MV_CTE_T109B	2	0s	false
MV_UNIONED_T109B	4	0s	false

All three objects are holding rows, none of them is invalid, and behind_by reads 0s on each. Healthy, by the catalog's own account.

Does it keep up with the base table on its own?

Back to the first run and its aggregate. Here is where the totals stand before we touch anything.

Query

SELECT REGION, ORDERS, TOTAL FROM MV_REGION_TOTALS_T109 ORDER BY REGION;

Result3 rows · 3 columns

REGION	ORDERS	TOTAL
east	2	100.00
north	2	350.00
south	2	385.50

Now let's add a seventh order to the base table. A big one, in the north.

Query

INSERT INTO ORDERS_T109 (ORDER_ID, REGION, CUSTOMER, AMOUNT)
SELECT 7, 'north', 'Gus', 1000.00;

Result1 row · 1 column

number of rows inserted
1

And ask the materialized view again. No refresh written anywhere, no waiting.

Query

SELECT REGION, ORDERS, TOTAL FROM MV_REGION_TOTALS_T109 ORDER BY REGION;

Result3 rows · 3 columns

REGION	ORDERS	TOTAL
east	2	100.00
north	3	1350.00
south	2	385.50

North has three orders now and a much bigger total. It kept up on its own.

Let's run the same aggregate against the base table, so there is something to compare it with.

Query

SELECT REGION, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL
FROM ORDERS_T109
GROUP BY REGION
ORDER BY REGION;

Result3 rows · 3 columns

REGION	ORDERS	TOTAL
east	2	100.00
north	3	1350.00
south	2	385.50

The same three rows. Now the two of them in one row.

Query

SELECT (SELECT SUM(TOTAL) FROM MV_REGION_TOTALS_T109)  AS TOTAL_FROM_THE_MV,
       (SELECT SUM(AMOUNT) FROM ORDERS_T109)           AS TOTAL_FROM_THE_TABLE;

Result1 row · 2 columns

TOTAL_FROM_THE_MV	TOTAL_FROM_THE_TABLE
1835.50	1835.50

Identical. The stored result and the live aggregate agree.

So is it ever behind? SHOW has a column for that

This is the part I like about materialized views. The catalog does not pretend the staleness question away. It has a column for it.

Query

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Result4 rows · 22 columns · scroll →

Scroll sideways for invalid, invalid_reason and behind_by.

created_on	name	database_name	schema_name	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:25:34.020000-07:00	MV_CTE_T109	DEMO_DB	PUBLIC	2	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	false	NULL	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_T109 AS WITH BIG AS (SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE AMOUNT > 100) SELECT ORDER_ID, AMOUNT FROM BIG	false	OFF	ROLE
2026-09-28 18:25:28.263000-07:00	MV_PROBE_T109	DEMO_DB	PUBLIC	6	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	false	NULL	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_PROBE_T109 AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109	false	OFF	ROLE
2026-09-28 18:25:31.401000-07:00	MV_REGION_TOTALS_T109	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	false	NULL	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_REGION_TOTALS_T109 AS SELECT REGION, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS_T109 GROUP BY REGION	false	OFF	ROLE
2026-09-28 18:25:32.825000-07:00	MV_UNIONED_T109	DEMO_DB	PUBLIC	4	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	false	NULL	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_UNIONED_T109 AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'north' UNION ALL SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'south'	false	OFF	ROLE

Query

SELECT "name", "rows", "behind_by", "refreshed_on", "invalid", "invalid_reason"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%T109'
ORDER BY "name";

Result4 rows · 6 columns · scroll →

name	rows	behind_by	refreshed_on	invalid	invalid_reason
MV_CTE_T109	2	10s	2026-09-28 18:25:27.304000-07:00	false	NULL
MV_PROBE_T109	6	10s	2026-09-28 18:25:27.304000-07:00	false	NULL
MV_REGION_TOTALS_T109	3	10s	2026-09-28 18:25:27.304000-07:00	false	NULL
MV_UNIONED_T109	4	10s	2026-09-28 18:25:27.304000-07:00	false	NULL

behind_by says 10s on every one of them, and invalid is false. So it does admit to lag, and it admits to it in seconds, in a column, without being asked.

That matters because the answers above were already correct. A materialized view can be behind and still be giving you the right number, and this column is how you find out which situation you are in.

Have a look at invalid_reason too. Those cells are real NULLs, which the grid shows differently from an empty cell: there is no reason recorded, because nothing is wrong.

The whole trade in two sentences

That is the trade, stated in the catalog rather than in the marketing. You get a stored, maintained result and a column that tells you how stale it is. In exchange you get a query language with eight things missing from it.

What happens when the base table goes away?

Let's drop the table all four of these are built on.

Query

DROP TABLE ORDERS_T109;

Result1 row · 1 column

status
ORDERS_T109 successfully dropped.

Query

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 5) = '_T109'
ORDER BY TABLE_NAME;

Result6 rows · 2 columns

TABLE_NAME	TABLE_TYPE
MV_CTE_T109	MATERIALIZED VIEW
MV_PROBE_T109	MATERIALIZED VIEW
MV_REGION_TOTALS_T109	MATERIALIZED VIEW
MV_UNIONED_T109	MATERIALIZED VIEW
REGIONS_T109	BASE TABLE
V_BIG_ORDERS_T109	VIEW

The base table is gone from that list, and every materialized view is still in it. None of them noticed, the same way a view does not notice.

But SHOW tells the truth.

Query

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Result4 rows · 22 columns · scroll →

Scroll sideways for invalid and invalid_reason.

created_on	name	database_name	schema_name	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:25:34.020000-07:00	MV_CTE_T109	DEMO_DB	PUBLIC	2	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_T109 AS WITH BIG AS (SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE AMOUNT > 100) SELECT ORDER_ID, AMOUNT FROM BIG	false	OFF	ROLE
2026-09-28 18:25:28.263000-07:00	MV_PROBE_T109	DEMO_DB	PUBLIC	6	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_PROBE_T109 AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109	false	OFF	ROLE
2026-09-28 18:25:31.401000-07:00	MV_REGION_TOTALS_T109	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_REGION_TOTALS_T109 AS SELECT REGION, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS_T109 GROUP BY REGION	false	OFF	ROLE
2026-09-28 18:25:32.825000-07:00	MV_UNIONED_T109	DEMO_DB	PUBLIC	4	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.	10s	CREATE OR REPLACE MATERIALIZED VIEW MV_UNIONED_T109 AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'north' UNION ALL SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'south'	false	OFF	ROLE

Query

SELECT "name", "invalid", "invalid_reason"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%T109'
ORDER BY "name";

Result4 rows · 3 columns

name	invalid	invalid_reason
MV_CTE_T109	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.
MV_PROBE_T109	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.
MV_REGION_TOTALS_T109	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.
MV_UNIONED_T109	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.

Four materialized views, all of them now invalid, and the reason recorded in plain words: the base table was dropped, so the object was marked invalid.

That is better than a view gives you. A broken view looks healthy in the catalog and nothing says otherwise. A broken materialized view says so, in a column, with the reason.

And what happens if we read one?

The first run never tried, so this grid is from the ORDERS_T109C run, which did the same drop on its own three objects.

Query

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 6) = '_T109C'
ORDER BY TABLE_NAME;

Result4 rows · 2 columns

TABLE_NAME	TABLE_TYPE
MV_APPROX_T109C	MATERIALIZED VIEW
MV_PLAIN_COUNT_T109C	MATERIALIZED VIEW
MV_TOTALS_T109C	MATERIALIZED VIEW
ORDERS_T109C	BASE TABLE

Query

-- The follow-up run dropped its own base table here.
DROP TABLE ORDERS_T109C;

Query

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 6) = '_T109C'
ORDER BY TABLE_NAME;

Result3 rows · 2 columns

TABLE_NAME	TABLE_TYPE
MV_APPROX_T109C	MATERIALIZED VIEW
MV_PLAIN_COUNT_T109C	MATERIALIZED VIEW
MV_TOTALS_T109C	MATERIALIZED VIEW

Query

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Result3 rows · 22 columns · scroll →

Scroll sideways for invalid and invalid_reason.

created_on	name	database_name	schema_name	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:46:58.197000-07:00	MV_APPROX_T109C	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109C	2026-09-28 18:46:55.063000-07:00	2026-09-28 18:46:55.063000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109C' dropped. Marked Materialized View as invalid.	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_APPROX_T109C AS SELECT REGION, APPROX_COUNT_DISTINCT(CUSTOMER) AS CUSTOMERS FROM ORDERS_T109C GROUP BY REGION	false	OFF	ROLE
2026-09-28 18:46:57.143000-07:00	MV_PLAIN_COUNT_T109C	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109C	2026-09-28 18:46:55.063000-07:00	2026-09-28 18:46:55.063000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109C' dropped. Marked Materialized View as invalid.	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_PLAIN_COUNT_T109C AS SELECT REGION, COUNT(*) AS ORDERS FROM ORDERS_T109C GROUP BY REGION	false	OFF	ROLE
2026-09-28 18:46:55.363000-07:00	MV_TOTALS_T109C	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109C	2026-09-28 18:46:55.063000-07:00	2026-09-28 18:46:55.063000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109C' dropped. Marked Materialized View as invalid.	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_TOTALS_T109C AS SELECT REGION, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS_T109C GROUP BY REGION	false	OFF	ROLE

Query

SELECT "name", "rows", "invalid", "invalid_reason"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%T109C'
ORDER BY "name";

Result3 rows · 4 columns

name	rows	invalid	invalid_reason
MV_APPROX_T109C	3	true	Base table 'ORDERS_T109C' dropped. Marked Materialized View as invalid.
MV_PLAIN_COUNT_T109C	3	true	Base table 'ORDERS_T109C' dropped. Marked Materialized View as invalid.
MV_TOTALS_T109C	3	true	Base table 'ORDERS_T109C' dropped. Marked Materialized View as invalid.

Same picture on three objects instead of four. Now let's read one.

Query

SELECT REGION, ORDERS, TOTAL FROM MV_TOTALS_T109C ORDER BY REGION;

Query failed — Snowflake returned an error

002037 (42601): SQL compilation error:
Failure during expansion of view 'MV_TOTALS_T109C': SQL compilation error: Materialized View MV_TOTALS_T109C is invalid. Invalidation reason: Base table 'ORDERS_T109C' dropped. Marked Materialized View as invalid.

002037, failure during expansion, and then the invalidation reason handed straight back inside the error. The catalog and the error message are telling us the same sentence, which is more than most failures manage.

So let's UNDROP the table

The table is recoverable, and we covered that in Episode 106. Let's ask for it back.

Query

UNDROP TABLE ORDERS_T109;

Result1 row · 1 column

status
Table ORDERS_T109 successfully restored.

Query

SELECT REGION, ORDERS, TOTAL FROM MV_REGION_TOTALS_T109 ORDER BY REGION;

Result3 rows · 3 columns

REGION	ORDERS	TOTAL
east	2	100.00
north	3	1350.00
south	2	385.50

It works again. Three rows, with north still on its seventh order, and nothing was rebuilt or refreshed by us.

And the flag? Here is the follow-up run asking the same question after its own UNDROP.

Query

-- ...and asked for it back.
UNDROP TABLE ORDERS_T109C;

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Query

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Result3 rows · 22 columns · scroll →

Scroll sideways for invalid and invalid_reason.

created_on	name	database_name	schema_name	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:46:58.197000-07:00	MV_APPROX_T109C	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109C	2026-09-28 18:46:55.063000-07:00	2026-09-28 18:46:55.063000-07:00	ACCOUNTADMIN	false	Base table 'ORDERS_T109C' was un-deleted. Marked Materialized View as valid.	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_APPROX_T109C AS SELECT REGION, APPROX_COUNT_DISTINCT(CUSTOMER) AS CUSTOMERS FROM ORDERS_T109C GROUP BY REGION	false	OFF	ROLE
2026-09-28 18:46:57.143000-07:00	MV_PLAIN_COUNT_T109C	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109C	2026-09-28 18:46:55.063000-07:00	2026-09-28 18:46:55.063000-07:00	ACCOUNTADMIN	false	Base table 'ORDERS_T109C' was un-deleted. Marked Materialized View as valid.	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_PLAIN_COUNT_T109C AS SELECT REGION, COUNT(*) AS ORDERS FROM ORDERS_T109C GROUP BY REGION	false	OFF	ROLE
2026-09-28 18:46:55.363000-07:00	MV_TOTALS_T109C	DEMO_DB	PUBLIC	3	1024	DEMO_DB	PUBLIC	ORDERS_T109C	2026-09-28 18:46:55.063000-07:00	2026-09-28 18:46:55.063000-07:00	ACCOUNTADMIN	false	Base table 'ORDERS_T109C' was un-deleted. Marked Materialized View as valid.	0s	CREATE OR REPLACE MATERIALIZED VIEW MV_TOTALS_T109C AS SELECT REGION, COUNT(*) AS ORDERS, SUM(AMOUNT) AS TOTAL FROM ORDERS_T109C GROUP BY REGION	false	OFF	ROLE

Query

SELECT "name", "invalid", "invalid_reason"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%T109C'
ORDER BY "name";

Result3 rows · 3 columns

name	invalid	invalid_reason
MV_APPROX_T109C	false	Base table 'ORDERS_T109C' was un-deleted. Marked Materialized View as valid.
MV_PLAIN_COUNT_T109C	false	Base table 'ORDERS_T109C' was un-deleted. Marked Materialized View as valid.
MV_TOTALS_T109C	false	Base table 'ORDERS_T109C' was un-deleted. Marked Materialized View as valid.

invalid is back to false, and look at the reason column. It was not cleared. It was rewritten: the base table was un-deleted, so the object was marked valid. The catalog keeps a sentence about what last happened to it either way.

One last thing: the mess this episode left behind

Every demo file in this course ends by counting what it left in the account. Here is what this one's last query said.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T109') AS PROBE_OBJECTS_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	PROBE_OBJECTS_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	2	10

Two objects left. Not zero.

The reason is the thing I got wrong earlier. I predicted the union and the CTE would be refused, so I never wrote a DROP for either of them. They were accepted, and the cleanup at the bottom of the file does not mention them.

Cleanup, as the first run wrote it

-- Put the schema back the way we found it.
DROP MATERIALIZED VIEW IF EXISTS MV_PROBE_T109;
DROP MATERIALIZED VIEW IF EXISTS MV_REGION_TOTALS_T109;
DROP MATERIALIZED VIEW IF EXISTS MV_UNIONED_T109;
DROP MATERIALIZED VIEW IF EXISTS MV_CTE_T109;
DROP VIEW  IF EXISTS V_BIG_ORDERS_T109;
DROP TABLE IF EXISTS ORDERS_T109;
DROP TABLE IF EXISTS REGIONS_T109;

So a follow-up file went looking for them.

Query

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 5) = '_T109'
ORDER BY TABLE_NAME;

Result2 rows · 2 columns

TABLE_NAME	TABLE_TYPE
MV_CTE_T109	MATERIALIZED VIEW
MV_UNIONED_T109	MATERIALIZED VIEW

Query

SHOW MATERIALIZED VIEWS IN SCHEMA PUBLIC;

Result2 rows · 22 columns · scroll →

Scroll sideways for invalid and invalid_reason.

created_on	name	reserved	database_name	schema_name	cluster_by	rows	bytes	source_database_name	source_schema_name	source_table_name	refreshed_on	compacted_on	owner	invalid	invalid_reason	behind_by	comment	text	is_secure	automatic_clustering	owner_role_type
2026-09-28 18:25:34.020000-07:00	MV_CTE_T109		DEMO_DB	PUBLIC		2	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.	10s		CREATE OR REPLACE MATERIALIZED VIEW MV_CTE_T109 AS WITH BIG AS (SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE AMOUNT > 100) SELECT ORDER_ID, AMOUNT FROM BIG	false	OFF	ROLE
2026-09-28 18:25:32.825000-07:00	MV_UNIONED_T109		DEMO_DB	PUBLIC		4	1024	DEMO_DB	PUBLIC	ORDERS_T109	2026-09-28 18:25:27.304000-07:00	2026-09-28 18:25:27.304000-07:00	ACCOUNTADMIN	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.	10s		CREATE OR REPLACE MATERIALIZED VIEW MV_UNIONED_T109 AS SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'north' UNION ALL SELECT ORDER_ID, AMOUNT FROM ORDERS_T109 WHERE REGION = 'south'	false	OFF	ROLE

Query

SELECT "name", "rows", "invalid", "invalid_reason"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" LIKE '%T109'
ORDER BY "name";

Result2 rows · 4 columns

name	rows	invalid	invalid_reason
MV_CTE_T109	2	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.
MV_UNIONED_T109	4	true	Base table 'ORDERS_T109' dropped. Marked Materialized View as invalid.

There they are. Both invalid, because the base table they were built on was dropped at the end of the first run, and both still holding their stored rows.

Query

DROP MATERIALIZED VIEW IF EXISTS MV_UNIONED_T109;

Result1 row · 1 column

status
MV_UNIONED_T109 successfully dropped.

Result1 row · 1 column

status
MV_CTE_T109 successfully dropped.

Query

SELECT COUNT(*) AS OBJECTS_STILL_LEFT_FROM_EP109
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND RIGHT(TABLE_NAME, 5) = '_T109';

Result1 row · 1 column

OBJECTS_STILL_LEFT_FROM_EP109
0

Zero now. And the other two runs tidied up after themselves as well.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T109')  AS EP109_OBJECTS_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 6) = '_T109B') AS EP109B_OBJECTS_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)        AS DEMO_DB_IS_READABLE;

Result1 row · 5 columns

THE_DATABASE	THE_SCHEMA	EP109_OBJECTS_LEFT	EP109B_OBJECTS_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	0	10

Result1 row · 5 columns

THE_DATABASE	THE_SCHEMA	PROBE_OBJECTS_LEFT	EP109_OBJECTS_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	0	10

Nothing left from any of the three runs, and the demo database still reads.

Why the last query of a demo file matters

A cleanup check nobody reads is not a check. This one got read, which is the only reason the two objects above are not still sitting in that account being maintained.
If you take one working habit from this post rather than one fact, make it that: end a probe file by counting what it left behind, and read the answer.

Key points

The catalog calls it a MATERIALIZED VIEW in its own words, with IS_INSERTABLE_INTO set to NO.
INFORMATION_SCHEMA.VIEWS does not list it at all. It is a third kind of object, with its own SHOW MATERIALIZED VIEWS.
An aggregate over one table is the shape it is built for, and it works immediately.
Eight things are refused, each with its own message: a JOIN, a window function, an ORDER BY in the definition, a HAVING clause, COUNT(DISTINCT ...), one built on a view, one built on another materialized view, and an INSERT into one.
HAVING is refused in those words, and COUNT(DISTINCT ...) is refused by name. APPROX_COUNT_DISTINCT is accepted.
A UNION ALL is accepted, and a CTE is accepted, and both of them genuinely return rows when you read them back.
A CTE that aggregates and then filters the aggregate is accepted — the same job HAVING was refused for. There is no rule of thumb, there is a list.
Add a row to the base table and the materialized view keeps up on its own, with no refresh written anywhere.
SHOW has a behind_by column, which read 10s here while the answers were already correct.
Drop the base table and every materialized view on it is marked invalid, with the reason recorded, and a read hands that reason back as the error.
UNDROP the table and they work again, with invalid back to false and the reason rewritten to say the table was un-deleted.
End every probe file by counting what it left behind. This one left two objects, and that is only known because the count was read.

Next in this series

Episode 110 — dynamic tables. The object that takes the join, the window function and the ORDER BY this one refused, where you declare how stale the answer may be and never write a refresh.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The episode ran three times and the post says where it switches: the main narrative and six of the refusals are the ORDERS_T109 run, HAVING, COUNT(DISTINCT ...) and the read of an invalidated object are the ORDERS_T109C run, and reading the accepted objects back is the ORDERS_T109B run. Setup statements for the second and third runs appear as plain code with no grid. The count of eight refusals is the eight refused definitions and writes listed in the table above, each with the code it returned; the ninth error pane on this page is a failed read of an invalidated object rather than a refusal, so it is not in that count. Every count in the text is read off one of the grids on this page. Nothing is timed: behind_by is Snowflake's own column, quoted as it came back, and not a measurement of anything this course did. Behaviour follows the Snowflake materialized view documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 109 - A Materialized View Is A Much Smaller Language

Let's create some sample data

Let's start with the smallest materialized view there is

Let's ask the catalog what kind of object that is

Now the shape it is actually built for

Now the refusals. Let's start with a join

Can it take a HAVING clause?

Can it take a COUNT(DISTINCT)?

Can it be built on a view, or on another one of itself?

Two I predicted would be refused, and was wrong about

But created is not the same claim as works

Now the sharp one: a CTE that does HAVING's job

Does it keep up with the base table on its own?

So is it ever behind? SHOW has a column for that

What happens when the base table goes away?

And what happens if we read one?

So let's UNDROP the table

One last thing: the mess this episode left behind

Key points

No comments:

Post a Comment