Welcome To TechBrothersIT: Snowflake SQL Tutorial 157 - I Dropped A Resource Monitor That Was Guarding A Warehouse, And Snowflake Said Nothing

Snowflake SQL Tutorial 157 - I Dropped A Resource Monitor That Was Guarding A Warehouse, And Snowflake Said Nothing

Snowflake SQL · Episode 157

In this post we are going to cover resource monitors in Snowflake.

A resource monitor is a credit budget. You give it a quota, you give it triggers that say what to do as that quota is used up, and you attach it to one or more warehouses.

The thing to know about them is that a monitor guarding nothing looks almost exactly like one guarding something. Same row, same quota, same triggers. One column tells them apart.

We will start with the warehouses to monitor 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 157 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's set up two warehouses to monitor
Let's create a monitor with a quota and three triggers
Now let's attach it to a warehouse
Can one monitor cover two warehouses?
Now what is optional, and it is nearly all of it
And two columns I never typed came back filled in
Five refusals, and they are not all the same kind of no
Now altering one, and the statement I got wrong
So what if a warehouse already has a monitor?
Now the one to remember: dropping a monitor that is guarding something
And the level column, across all of them
One last thing: let's put the account back

Let's set up two warehouses to monitor

There is no sample table in this post. What we need instead is something to put a budget on.

Where we are running

-- No sample table in this post either.
-- Resource monitors are account-level objects, like warehouses.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

First, how many resource monitors does this account already have?

Query

SHOW RESOURCE MONITORS;

Result0 rows · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users

None at all, so everything you see below is ours. And here is the session.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE,
       CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_VERSION()   AS THE_VERSION;

Result1 row · 3 columns

THE_WAREHOUSE	THE_ROLE	THE_VERSION
NULL	ACCOUNTADMIN	10.36.101

CURRENT_WAREHOUSE() is still NULL, which is where the warehouse episodes left it. Nothing in this post needs to read rows, so it does not get in our way this time.

Now two warehouses of our own.

Create

CREATE OR REPLACE WAREHOUSE WH_T157
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 60
    INITIALLY_SUSPENDED = TRUE
    COMMENT             = 'episode 157 - monitored, never used';

Result1 row · 1 column

status
Warehouse WH_T157 successfully created.

Create

CREATE OR REPLACE WAREHOUSE WH2_T157
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 60
    INITIALLY_SUSPENDED = TRUE
    COMMENT             = 'episode 157 - the second one';

Result1 row · 1 column

status
Warehouse WH2_T157 successfully created.

Query

SHOW WAREHOUSES LIKE '%\\_T157';

Result2 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH2_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	Y	60	true					2026-10-06 11:10:49.029000-07:00	2026-10-06 11:10:49.029000-07:00	2026-10-06 11:10:49.051000-07:00	ACCOUNTADMIN	episode 157 - the second one	true	8	null	0	0	0	1	72652554737	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
WH_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	60	true					2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.806000-07:00	ACCOUNTADMIN	episode 157 - monitored, never used	true	8	null	0	0	0	1	72652554649	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

That grid is 38 columns wide, so here is the one column that matters right now, read back as rows.

Query

SELECT "name", "state", "size", "resource_monitor"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result2 rows · 4 columns

name	state	size	resource_monitor
WH2_T157	SUSPENDED	X-Small	null
WH_T157	SUSPENDED	X-Small	null

Both asleep, and resource_monitor says null on both. Nothing is guarding either of them.

Two spellings of nothing

Look closely at those two cells. They hold the four characters null, in lower case, which is how SHOW WAREHOUSES writes “no monitor attached”. That is a piece of text and not a real SQL NULL. A grid further down this page shows a real NULL, printed in capitals, and the two are different values from two different statements.

Let's create a monitor with a quota and three triggers

A monitor needs a name. Everything else is optional, but the useful form is a credit quota and some triggers. Here is one with a quota of one credit and three triggers at different percentages.

Create

CREATE OR REPLACE RESOURCE MONITOR RM_T157
    WITH CREDIT_QUOTA = 1
    TRIGGERS ON 50 PERCENT DO NOTIFY
             ON 100 PERCENT DO SUSPEND
             ON 110 PERCENT DO SUSPEND_IMMEDIATE;

Result1 row · 1 column

status
Resource monitor RM_T157 successfully created.

Query

SHOW RESOURCE MONITORS LIKE 'RM\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RM_T157	1.00	0.00	1.00	NULL	MONTHLY	2026-09-30 17:00:00-07:00	NULL	50%	100%	110%	2026-10-06 11:10:51.102000-07:00	ACCOUNTADMIN	NULL

That is 15 columns. Here are the nine worth reading.

Query

SELECT "name", "credit_quota", "used_credits", "remaining_credits", "level",
       "frequency", "notify_at", "suspend_at", "suspend_immediately_at"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 9 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	notify_at	suspend_at	suspend_immediately_at
RM_T157	1.00	0.00	1.00	NULL	MONTHLY	50%	100%	110%

A quota of 1.00, nothing used, 1.00 remaining. The three triggers landed in three separate columns: notify_at 50%, suspend_at 100%, suspend_immediately_at 110%.

And now the column this whole post is about. level reads NULL.

That monitor exists. It has a quota and three triggers. And it is guarding absolutely nothing, because it is not attached to anything yet. level is the only place that shows.

Now let's attach it to a warehouse

Attaching is an ALTER WAREHOUSE, not an alter of the monitor. The warehouse is the thing that gains a property.

Alter

ALTER WAREHOUSE WH_T157 SET RESOURCE_MONITOR = RM_T157;

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW WAREHOUSES LIKE 'WH\\_T157';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	60	true					2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:52.334000-07:00	ACCOUNTADMIN	episode 157 - monitored, never used	true	8	RM_T157	0	0	0	1	72652554649	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Query

SELECT "name", "state", "resource_monitor"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

name	state	resource_monitor
WH_T157	SUSPENDED	RM_T157

The warehouse now names the monitor, where it said null before.

And the monitor's own row has changed too.

Query

SHOW RESOURCE MONITORS LIKE 'RM\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RM_T157	1.00	0.00	1.00	WAREHOUSE	MONTHLY	2026-09-30 17:00:00-07:00	NULL	50%	100%	110%	2026-10-06 11:10:51.102000-07:00	ACCOUNTADMIN	NULL

Query

SELECT "name", "credit_quota", "used_credits", "level", "owner"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

name	credit_quota	used_credits	level	owner
RM_T157	1.00	0.00	WAREHOUSE	ACCOUNTADMIN

level is now WAREHOUSE. So the monitor does not tell you which warehouse it is on. It only tells you what kind of thing it is attached to. To find out which warehouses, you ask the warehouses.

Can one monitor cover two warehouses?

Let's point the second warehouse at the same monitor and find out.

Alter

ALTER WAREHOUSE WH2_T157 SET RESOURCE_MONITOR = RM_T157;

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW WAREHOUSES LIKE '%\\_T157';

Result2 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH2_T157	STARTED	STANDARD	X-Small	1	1	1	0	0	N	Y	60	true	100	0	0	0	2026-10-06 11:10:49.029000-07:00	2026-10-06 11:10:50.493000-07:00	2026-10-06 11:10:54.397000-07:00	ACCOUNTADMIN	episode 157 - the second one	true	8	RM_T157	1	0	0	0	72652554737	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
WH_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	60	true					2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:52.334000-07:00	ACCOUNTADMIN	episode 157 - monitored, never used	true	8	RM_T157	0	0	0	1	72652554649	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Query

SELECT "name", "state", "resource_monitor"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result2 rows · 3 columns

name	state	resource_monitor
WH2_T157	STARTED	RM_T157
WH_T157	SUSPENDED	RM_T157

Both warehouses name the same monitor. So one budget can cover several warehouses, and they share the quota between them.

Now what is optional, and it is nearly all of it

Before we go further, the shape of a trigger is worth getting straight. Triggers are not a list. They are three fixed columns, one per action.

Action	Column it lands in
`DO NOTIFY`	`notify_at`
`DO SUSPEND`	`suspend_at`
`DO SUSPEND_IMMEDIATE`	`suspend_immediately_at`

So three triggers of three different kinds fit. A fourth of the same kind does not, because there is nowhere to put it.

Now a monitor with a quota and no triggers at all. Is that allowed?

Create

CREATE OR REPLACE RESOURCE MONITOR RMBARE_T157
    WITH CREDIT_QUOTA = 1;

Result1 row · 1 column

status
Resource monitor RMBARE_T157 successfully created.

Query

SHOW RESOURCE MONITORS LIKE 'RMBARE\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RMBARE_T157	1.00	0.00	1.00	NULL	MONTHLY	2026-09-30 17:00:00-07:00	NULL	NULL	NULL	NULL	2026-10-06 11:10:56.583000-07:00	ACCOUNTADMIN	NULL

Query

SELECT "name", "credit_quota", "notify_at", "suspend_at",
       "suspend_immediately_at"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

name	credit_quota	notify_at	suspend_at	suspend_immediately_at
RMBARE_T157	1.00	NULL	NULL	NULL

It is. A quota of 1.00 and all three trigger columns NULL. So a monitor can watch and never act, which may be exactly what you want while you find out what something costs.

Now the other way round. Triggers and no quota.

Create

CREATE OR REPLACE RESOURCE MONITOR RMNOQUOTA_T157
    TRIGGERS ON 100 PERCENT DO SUSPEND;

Result1 row · 1 column

status
Resource monitor RMNOQUOTA_T157 successfully created.

Query

SHOW RESOURCE MONITORS LIKE 'RMNOQUOTA\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RMNOQUOTA_T157	NULL	0.00	NULL	NULL	MONTHLY	2026-09-30 17:00:00-07:00	NULL	NULL	100%	NULL	2026-10-06 11:10:57.439000-07:00	ACCOUNTADMIN	NULL

Also allowed. credit_quota NULL, remaining_credits NULL, and a suspend_at of 100%. 100% of NULL.

I do not know what that monitor would ever do. The grid does not say, so I am not going to tell you. It was accepted. That is the fact.

And a trigger at 0 percent.

Create

CREATE OR REPLACE RESOURCE MONITOR RMZERO_T157
    WITH CREDIT_QUOTA = 1
    TRIGGERS ON 0 PERCENT DO NOTIFY;

Result1 row · 1 column

status
Resource monitor RMZERO_T157 successfully created.

Query

SHOW RESOURCE MONITORS LIKE 'RMZERO\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RMZERO_T157	1.00	0.00	1.00	NULL	MONTHLY	2026-09-30 17:00:00-07:00	NULL	0%	NULL	NULL	2026-10-06 11:10:57.893000-07:00	ACCOUNTADMIN	NULL

Query

SELECT "name", "credit_quota", "notify_at", "suspend_at"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 4 columns

name	credit_quota	notify_at	suspend_at
RMZERO_T157	1.00	0%	NULL

Accepted, and the column reads 0%, exactly as written.

And two columns I never typed came back filled in

Look back at the grids above and you will find a frequency of MONTHLY and a start_time at the beginning of this month, on monitors where I wrote neither. Both are defaults.

Let's write them out on purpose and see what changes.

Create

CREATE OR REPLACE RESOURCE MONITOR RMFREQ_T157
    WITH CREDIT_QUOTA   = 1
         FREQUENCY       = MONTHLY
         START_TIMESTAMP = IMMEDIATELY
    TRIGGERS ON 100 PERCENT DO SUSPEND;

Result1 row · 1 column

status
Resource monitor RMFREQ_T157 successfully created.

Query

SHOW RESOURCE MONITORS LIKE 'RMFREQ\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RMFREQ_T157	1.00	0.00	1.00	NULL	MONTHLY	2026-10-06 11:10:59.489000-07:00	NULL	NULL	100%	NULL	2026-10-06 11:10:59.475000-07:00	ACCOUNTADMIN	NULL

Query

SELECT "name", "credit_quota", "frequency", "start_time", "end_time"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

name	credit_quota	frequency	start_time	end_time
RMFREQ_T157	1.00	MONTHLY	2026-10-06 11:10:59.489000-07:00	NULL

The frequency is the same MONTHLY as before, because that is what I asked for. The start_time is different: START_TIMESTAMP = IMMEDIATELY put the create time in it, rather than the start of the month.

And end_time is NULL, which is the monitor saying it has no end date. A quota that resets monthly, for ever.

Five refusals, and they are not all the same kind of no

First, an action word that does not exist.

Create

CREATE OR REPLACE RESOURCE MONITOR RMBADACTION_T157
    WITH CREDIT_QUOTA = 1
    TRIGGERS ON 100 PERCENT DO PANIC;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 3 at position 31 unexpected 'PANIC'.

001003, a syntax error at the word PANIC. So the action list is closed, and the grammar is what refuses you.

Second, a trigger with the percentage left out.

Create

CREATE OR REPLACE RESOURCE MONITOR RMNOPCT_T157
    WITH CREDIT_QUOTA = 1
    TRIGGERS DO SUSPEND;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 3 at position 13 unexpected 'DO'.

001003 again, this time at the word DO. The same kind of refusal.

Third, a negative credit quota.

Create

CREATE OR REPLACE RESOURCE MONITOR RMNEG_T157
    WITH CREDIT_QUOTA = -1
    TRIGGERS ON 100 PERCENT DO SUSPEND;

Query failed — Snowflake returned an error

090254 (42601): Negative credit quota not allowed.

090254, negative credit quota not allowed. That one is different in kind. It is not a syntax error: the sentence parsed perfectly well and the value was rejected.

Fourth, a frequency with no start timestamp.

Create

CREATE OR REPLACE RESOURCE MONITOR RMNOSTART_T157
    WITH CREDIT_QUOTA = 1
         FREQUENCY     = WEEKLY
    TRIGGERS ON 100 PERCENT DO SUSPEND;

Query failed — Snowflake returned an error

090259 (42601): Must specify frequency and start time together.

090259, must specify frequency and start time together. That is my favourite refusal in the episode, because it tells you the rule rather than the position of the problem.

Query

SHOW RESOURCE MONITORS LIKE 'RMNOSTART\\_T157';

Result0 rows · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users

And nothing was created, which is the half worth checking.

And fifth, a frequency that does not exist.

Create

CREATE OR REPLACE RESOURCE MONITOR RMBADFREQ_T157
    WITH CREDIT_QUOTA = 1
         FREQUENCY     = FORTNIGHTLY
    TRIGGERS ON 100 PERCENT DO SUSPEND;

Query failed — Snowflake returned an error

090257 (42601): Unsupported frequency for resource monitor.

090257, unsupported frequency for resource monitor. Another value refusal rather than a grammar one: FORTNIGHTLY parses fine, it is simply not on the list.

Now altering one, and the statement I got wrong

Raising the quota first, because that part is simple.

Alter

ALTER RESOURCE MONITOR RM_T157 SET CREDIT_QUOTA = 2;

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW RESOURCE MONITORS LIKE 'RM\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RM_T157	2.00	0.00	2.00	WAREHOUSE	MONTHLY	2026-09-30 17:00:00-07:00	NULL	50%	100%	110%	2026-10-06 11:10:51.102000-07:00	ACCOUNTADMIN	NULL

Query

SELECT "name", "credit_quota", "used_credits", "remaining_credits"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 4 columns

name	credit_quota	used_credits	remaining_credits
RM_T157	2.00	0.00	2.00

The quota moved to 2.00, nothing used, 2.00 remaining. So SET CREDIT_QUOTA behaves exactly as you would hope.

Now the triggers, and here is what I wrote. I was confident about it.

Alter

ALTER RESOURCE MONITOR RM_T157 SET TRIGGERS ON 90 PERCENT DO SUSPEND;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 44 unexpected 'ON'.
syntax error line 1 at position 58 unexpected 'DO'.
syntax error line 1 at position 68 unexpected '<EOF>'.

001003, a syntax error at the word ON, and then two more at DO and at the end of the statement. Read where the first one is: right after TRIGGERS.

TRIGGERS is not an option inside SET. It is a clause of its own, standing next to SET.

Query

SHOW RESOURCE MONITORS LIKE 'RM\\_T157';

Result1 row · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users
RM_T157	2.00	0.00	2.00	WAREHOUSE	MONTHLY	2026-09-30 17:00:00-07:00	NULL	50%	100%	110%	2026-10-06 11:10:51.102000-07:00	ACCOUNTADMIN	NULL

Query

SELECT "name", "notify_at", "suspend_at", "suspend_immediately_at"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 4 columns

name	notify_at	suspend_at	suspend_immediately_at
RM_T157	50%	100%	110%

And the triggers are exactly as they were. 50%, 100% and 110%. Nothing was half applied, because the statement never ran at all.

Here is the form that works.

Statement (no captured result)

-- The form that works: TRIGGERS is a clause of its own,
-- standing next to SET, not an option inside it.
-- This episode's capture file has no result for this statement,
-- so there is no result grid for it on this page.
ALTER RESOURCE MONITOR RM_T157 TRIGGERS ON 90 PERCENT DO SUSPEND;

What this page cannot tell you

This episode's capture file has no result for that statement, so there is no grid for it on this page. That also means the obvious follow-up question — whether TRIGGERS replaces every existing trigger or adds one — is not answered by anything on this page, and I am not going to answer it from memory. The grid above is the last word this capture has on those three columns.

One more alter refusal, on a monitor that does not exist.

Alter

ALTER RESOURCE MONITOR NO_SUCH_MONITOR_T157 SET CREDIT_QUOTA = 1;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Resource monitor 'NO_SUCH_MONITOR_T157' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on RESOURCE MONITOR NO_SUCH_MONITOR_T157.

002003, and it names the monitor.

So what if a warehouse already has a monitor?

Let's make a second monitor and attach it to the warehouse that already has the first one.

Create

CREATE OR REPLACE RESOURCE MONITOR RM2_T157
    WITH CREDIT_QUOTA = 1
    TRIGGERS ON 100 PERCENT DO SUSPEND;

Result1 row · 1 column

status
Resource monitor RM2_T157 successfully created.

Alter

ALTER WAREHOUSE WH_T157 SET RESOURCE_MONITOR = RM2_T157;

Result1 row · 1 column

status
Statement executed successfully.

Statement executed successfully. So which one does the warehouse end up with?

Query

SHOW WAREHOUSES LIKE 'WH\\_T157';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	60	true					2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.781000-07:00	2026-10-06 11:11:03.167000-07:00	ACCOUNTADMIN	episode 157 - monitored, never used	true	8	RM2_T157	0	0	0	1	72652554649	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Query

SELECT "name", "resource_monitor"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 2 columns

name	resource_monitor
WH_T157	RM2_T157

The second one. The first was not refused and it is not shared. It was replaced, silently.

So a warehouse holds one monitor and the last statement wins. Here is the whole picture before we touch anything else.

Query

SHOW WAREHOUSES LIKE '%\\_T157';

Result2 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH2_T157	STARTED	STANDARD	X-Small	1	1	1	0	0	N	Y	60	true	100	0	0	0	2026-10-06 11:10:49.029000-07:00	2026-10-06 11:10:50.493000-07:00	2026-10-06 11:10:54.397000-07:00	ACCOUNTADMIN	episode 157 - the second one	true	8	RM_T157	1	0	0	0	72652554737	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
WH_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	60	true					2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.781000-07:00	2026-10-06 11:11:03.167000-07:00	ACCOUNTADMIN	episode 157 - monitored, never used	true	8	RM2_T157	0	0	0	1	72652554649	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Query

SELECT "name", "resource_monitor"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result2 rows · 2 columns

name	resource_monitor
WH2_T157	RM_T157
WH_T157	RM2_T157

Two warehouses, two different monitors. One of them lost its original monitor without anyone being told.

Now the one to remember: dropping a monitor that is guarding something

One of those monitors is actively attached to a warehouse. Let's drop it.

Drop

DROP RESOURCE MONITOR IF EXISTS RM2_T157;

Result1 row · 1 column

status
RM2_T157 successfully dropped.

Successfully dropped. No refusal. No warning. Not even a note. And the warehouse it was guarding?

Query

SHOW WAREHOUSES LIKE 'WH\\_T157';

Result1 row · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	60	true					2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.781000-07:00	2026-10-06 11:11:03.167000-07:00	ACCOUNTADMIN	episode 157 - monitored, never used	true	8	null	0	0	0	1	72652554649	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Query

SELECT "name", "state", "resource_monitor"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

name	state	resource_monitor
WH_T157	SUSPENDED	null

resource_monitor is back to null. The thing that was supposed to stop that warehouse spending is simply gone.

Query

SHOW RESOURCE MONITORS LIKE 'RM2\\_T157';

Result0 rows · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users

The quiet one

Nothing about that sequence looks like a mistake. The drop succeeds, the warehouse carries on, and the only way to find out is to go and look afterwards. If you drop a monitor, check what was attached to it first.

And the level column, across all of them

Here is every monitor this post made, in one grid.

Query

SHOW RESOURCE MONITORS LIKE '%\\_T157';

Result5 rows · 15 columns · scroll →

name	credit_quota	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment
RMBARE_T157	1.00	1.00	NULL	MONTHLY	2026-09-30 17:00:00-07:00	NULL	NULL	NULL	NULL	2026-10-06 11:10:56.583000-07:00	ACCOUNTADMIN	NULL
RMFREQ_T157	1.00	1.00	NULL	MONTHLY	2026-10-06 11:10:59.489000-07:00	NULL	NULL	100%	NULL	2026-10-06 11:10:59.475000-07:00	ACCOUNTADMIN	NULL
RMNOQUOTA_T157	NULL	NULL	NULL	MONTHLY	2026-09-30 17:00:00-07:00	NULL	NULL	100%	NULL	2026-10-06 11:10:57.439000-07:00	ACCOUNTADMIN	NULL
RMZERO_T157	1.00	1.00	NULL	MONTHLY	2026-09-30 17:00:00-07:00	NULL	0%	NULL	NULL	2026-10-06 11:10:57.893000-07:00	ACCOUNTADMIN	NULL
RM_T157	2.00	2.00	WAREHOUSE	MONTHLY	2026-09-30 17:00:00-07:00	NULL	50%	100%	110%	2026-10-06 11:10:51.102000-07:00	ACCOUNTADMIN	NULL

Query

SELECT "name", "level", "credit_quota", "owner"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result5 rows · 4 columns

name	level	credit_quota	owner
RMBARE_T157	NULL	1.00	ACCOUNTADMIN
RMFREQ_T157	NULL	1.00	ACCOUNTADMIN
RMNOQUOTA_T157	NULL	NULL	ACCOUNTADMIN
RMZERO_T157	NULL	1.00	ACCOUNTADMIN
RM_T157	WAREHOUSE	2.00	ACCOUNTADMIN

Five monitors. The level column reads WAREHOUSE on one of them and NULL on the other four.

So four of those exist, hold a quota, and guard nothing at all. They cost nothing and they do nothing. One column is the only place that is visible, and it is the first column to read when somebody tells you a warehouse is budgeted.

And credit_quota on the monitor with no quota is NULL in this grid too, which is the real SQL NULL promised at the top of the page. Compare it with the lower-case null in the warehouse grids. Different statements, different spellings, different kinds of value.

One last thing: let's put the account back

Detach first, then drop, because that is the order that leaves nothing surprising behind.

Alter

ALTER WAREHOUSE WH_T157 UNSET RESOURCE_MONITOR;

Result1 row · 1 column

status
Statement executed successfully.

Alter

ALTER WAREHOUSE WH2_T157 UNSET RESOURCE_MONITOR;

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW WAREHOUSES LIKE '%\\_T157';

Result2 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	running	queued	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	pendings	failed	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
WH2_T157	STARTED	STANDARD	X-Small	1	1	1	0	0	N	Y	60	true	100	0	0	0	2026-10-06 11:10:49.029000-07:00	2026-10-06 11:10:50.493000-07:00	2026-10-06 11:11:06.918000-07:00	ACCOUNTADMIN	episode 157 - the second one	true	8	null	1	0	0	0	72652554737	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
WH_T157	SUSPENDED	STANDARD	X-Small	1	1	0	0	0	N	N	60	true					2026-10-06 11:10:48.781000-07:00	2026-10-06 11:10:48.781000-07:00	2026-10-06 11:11:06.751000-07:00	ACCOUNTADMIN	episode 157 - monitored, never used	true	8	null	0	0	0	1	72652554649	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

Query

SELECT "name", "state", "resource_monitor"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result2 rows · 3 columns

name	state	resource_monitor
WH2_T157	STARTED	null
WH_T157	SUSPENDED	null

Both back to null. ALTER WAREHOUSE ... UNSET RESOURCE_MONITOR is the way to take one off.

Now the drops. Two of them here, and the rest are the same statement with different names.

Drop

DROP RESOURCE MONITOR IF EXISTS RM_T157;

Result1 row · 1 column

status
RM_T157 successfully dropped.

Drop

DROP RESOURCE MONITOR IF EXISTS RMNOQUOTA_T157;

Result1 row · 1 column

status
RMNOQUOTA_T157 successfully dropped.

Drop

DROP WAREHOUSE IF EXISTS WH_T157;

Result1 row · 1 column

status
WH_T157 successfully dropped.

Drop

DROP WAREHOUSE IF EXISTS WH2_T157;

Result1 row · 1 column

status
WH2_T157 successfully dropped.

And the checks.

Query

SHOW RESOURCE MONITORS;

Result0 rows · 15 columns · scroll →

name	credit_quota	used_credits	remaining_credits	level	frequency	start_time	end_time	notify_at	suspend_at	suspend_immediately_at	created_on	owner	comment	notify_users

Query

SHOW WAREHOUSES;

Result3 rows · 38 columns · scroll →

name	state	type	size	min_cluster_count	max_cluster_count	started_clusters	is_default	is_current	auto_suspend	auto_resume	available	provisioning	quiescing	other	created_on	resumed_on	updated_on	owner	comment	enable_query_acceleration	query_acceleration_max_scale_factor	resource_monitor	actives	suspended	uuid	scaling_policy	owner_role_type	resource_constraint	generation	query_throughput_multiplier	max_query_performance_level	disabled_reasons	tables
COMPUTE_WH	STARTED	STANDARD	X-Small	1	1	1	Y	N	300	true	100	0	0	0	2026-09-21 18:36:58.764000-07:00	2026-10-06 11:05:18.848000-07:00	2026-10-06 11:05:18.848000-07:00	ACCOUNTADMIN		true	8	null	1	0	283799204	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
SNOWFLAKE_LEARNING_WH	SUSPENDED	STANDARD	X-Small	1	1	0	N	N	300	true					2026-09-21 18:33:47.899000-07:00	2026-09-21 18:33:47.899000-07:00	2026-09-21 18:33:47.913000-07:00	ACCOUNTADMIN	Created by Snowflake during account provisioning	true	8	null	0	1	283799128	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL
SYSTEM$STREAMLIT_NOTEBOOK_WH	SUSPENDED	STANDARD	X-Small	1	10	0	N	N	60	true					2026-09-21 18:33:46.101000-07:00	2026-09-21 18:33:46.115000-07:00	2026-09-21 18:33:46.136000-07:00	ACCOUNTADMIN		true	8	null	0	1	283799044	STANDARD	ROLE	STANDARD_GEN_2	2	NULL	NULL	NULL	NULL

No monitors at all, which is where we came in, and the same three warehouses the account shipped with.

Query

SELECT COUNT(*) AS WAREHOUSES_OF_MINE_LEFT
  FROM DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES
 WHERE ENDSWITH(UPPER(WAREHOUSE_NAME), '_T157');

Query failed — Snowflake returned an error

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

That closing check went looking for warehouses in INFORMATION_SCHEMA and got 002003, because warehouses are account-level objects and are not in there. The grid above it is the real proof.

Query

SELECT CURRENT_WAREHOUSE() AS THE_WAREHOUSE,
       CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA;

Result1 row · 4 columns

THE_WAREHOUSE	THE_ROLE	THE_DATABASE	THE_SCHEMA
NULL	ACCOUNTADMIN	DEMO_DB	PUBLIC

And the session is where it was: no warehouse, and the same role, database and schema.

About the numbers

No credit is deliberately spent anywhere in this post. used_credits reads 0.00 in every grid that has the column, so nothing here shows a trigger firing. What this post shows is how monitors are written, attached, read and removed.

Key points

level is the only column that tells you whether a monitor guards anything. It is NULL until the monitor is attached.
Triggers are three fixed columns rather than a list: notify_at, suspend_at and suspend_immediately_at.
A monitor with no triggers is allowed, and so is a monitor with no quota.
FREQUENCY and start_time are filled in for you if you do not write them.
Attaching a monitor is an ALTER WAREHOUSE. The warehouse names the monitor, and the monitor only says what kind of thing it is on.
One monitor can cover several warehouses, and they share its quota.
A warehouse holds one monitor. Attach a second and the first is replaced, silently.
DROP RESOURCE MONITOR on an attached monitor succeeds with no warning and leaves the warehouse unguarded.
A grammar refusal and a value refusal are different things: 001003 against 090254, 090259 and 090257.
ALTER ... SET TRIGGERS is a syntax error. TRIGGERS is a clause of its own, standing next to SET.
A failed alter changes nothing. The three trigger columns were exactly as they had been.

Next in this series

In the next post we start on access: roles. What a brand new role can do, what it cannot, and the question of what you are actually wearing.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.36.101) running the demo script for this episode. One statement is shown with no result, because this episode's capture file does not contain one for it, and the post says which question that leaves open rather than answering it from memory. used_credits reads 0.00 in every grid, so no trigger fires anywhere on this page. Everything created here is dropped here. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake resource monitor documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 157 - I Dropped A Resource Monitor That Was Guarding A Warehouse, And Snowflake Said Nothing

Let's set up two warehouses to monitor

Let's create a monitor with a quota and three triggers

Now let's attach it to a warehouse

Can one monitor cover two warehouses?

Now what is optional, and it is nearly all of it

And two columns I never typed came back filled in

Five refusals, and they are not all the same kind of no

Now altering one, and the statement I got wrong

So what if a warehouse already has a monitor?

Now the one to remember: dropping a monitor that is guarding something

And the level column, across all of them

One last thing: let's put the account back

Key points

No comments:

Post a Comment