What is resource monitor

Resource Monitor Introduction

We hardly talk about resource monitors and its role in snowflake, but there are so much to cover when it comes to snowflake resource monitor, and there is lack of good content in the web. In the pay-as-you go era, having a good understanding about Resource Monitor is must.

Snowflake offers resource monitors as a means of assisting in cost management and preventing unforeseen credit usage brought on by operating warehouses. While it’s running, a virtual warehouse uses up Snowflake credits.

A resource monitor can only suspend user-managed virtual warehouses based on credit usage thresholds, but it can track credit usage by both user-managed and virtual warehouses utilised by cloud services. After a user-managed warehouse is suspended, credit consumption for cloud services may still be allowed.

The amount of credits used is determined on the size and duration of the warehouse.

Limits may be established for a certain timeframe or range of dates. The resource monitor can cause a number of actions, such as issuing alarm alerts and/or halting user-managed warehouses, when certain limitations are reached and/or are approaching.

Only account administrators (i.e., users with the ACCOUNTADMIN role) are able to establish resource monitors, but account administrators can decide to allow users with other privileges to view and change resource monitors using SQL.

Every database in an account that uses Snowflake has a schema called INFORMATION SCHEMA that implements the Information Schema.

The power of Snowflake Information Schema is not well known and the usefulness it brings to data developers and data ops engineers are not discussed anywhere. (Watch complete video )

We have published a detailed video on resource monitor and this video is a comprehensive & practical guide with hands-on excercise on snowflake resource monitor. It will help you to answer the following questions

1. 🙋 Does resource monitor applicable only for a virtual warehouse (compute)?
2. 🙋 Difference between account and warehouse level resource monitors?
3. 🙋 What is the role of frequency & trigger in resource monitors?
4. 🙋 How notification works for a resource monitor?
5. 🙋 Which role can create resource monitor objects in snowflake?
6. 🙋 Can multiple resource monitors be associated with a virtual warehouse?
7. 🙋 What is a dormant resource monitor?
8. 🙋 What is “customised schedule” in a resource monitor?
9. 🙋 What are the different actions supported by resource monitor?
10. 🙋 What happens to virtual warehouse when suspend action is triggered?

You can watch the complete hands on video tutorial on resource monitor

SQL For Create Warehouse Level Resource Monitor

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE RESOURCE MONITOR "RM_1CREDIT_DAILY_WH_LEVEL" WITH CREDIT_QUOTA = 1 
 TRIGGERS 
 ON 95 PERCENT DO SUSPEND 
 ON 100 PERCENT DO SUSPEND_IMMEDIATE 
 ON 90 PERCENT DO NOTIFY 
 ON 85 PERCENT DO NOTIFY 
 ON 80 PERCENT DO NOTIFY 
 ON 75 PERCENT DO NOTIFY 
 ON 70 PERCENT DO NOTIFY;

ALTER WAREHOUSE "COMPUTE_WH" SET RESOURCE_MONITOR = "RM_1CREDIT_DAILY_WH_LEVEL";

SQL For Create Account Level Resource Monitor

1
2
3
4
5
6
7
CREATE RESOURCE MONITOR "ACCOUNT_LEVEL_RM" WITH CREDIT_QUOTA = 100 
 TRIGGERS 
 ON 100 PERCENT DO SUSPEND 
 ON 90 PERCENT DO SUSPEND_IMMEDIATE 
 ON 80 PERCENT DO NOTIFY 
 ON 70 PERCENT DO NOTIFY;
ALTER ACCOUNT SET RESOURCE_MONITOR = "ACCOUNT_LEVEL_RM";

SQL For Alter Warehouse Level Resource Monitor

1
2
3
4
5
ALTER RESOURCE MONITOR "RM_1CREDIT_DAILY_WH_LEVEL" SET CREDIT_QUOTA = 1 
 TRIGGERS 
 ON 95 PERCENT DO SUSPEND 
 ON 100 PERCENT DO SUSPEND_IMMEDIATE 
 ON 80 PERCENT DO NOTIFY;

SQL For Resource Monitor with Daily Schedule

1
2
3
4
5
6
7
8
9
10
11
12
CREATE RESOURCE MONITOR "WAREHOUSE_LEVEL_RM" 
    WITH CREDIT_QUOTA = 100, 
    frequency = 'DAILY', 
    start_timestamp = 'IMMEDIATELY', 
    end_timestamp = null 
 TRIGGERS 
 ON 95 PERCENT DO SUSPEND 
 ON 100 PERCENT DO SUSPEND_IMMEDIATE 
 ON 90 PERCENT DO NOTIFY 
 ON 85 PERCENT DO NOTIFY 
 ON 80 PERCENT DO NOTIFY;
ALTER WAREHOUSE "COMPUTE_WH" SET RESOURCE_MONITOR = "WAREHOUSE_LEVEL_RM";

SQL For Resource Monitor with Monthly Schedule

1
2
3
4
5
6
7
8
9
10
11
12
CREATE RESOURCE MONITOR "WAREHOUSE_LEVEL_RM" 
    WITH CREDIT_QUOTA = 100, 
    frequency = 'MONTHLY', 
    start_timestamp = 'IMMEDIATELY', 
    end_timestamp = null 
 TRIGGERS 
 ON 95 PERCENT DO SUSPEND 
 ON 100 PERCENT DO SUSPEND_IMMEDIATE 
 ON 90 PERCENT DO NOTIFY 
 ON 85 PERCENT DO NOTIFY 
 ON 80 PERCENT DO NOTIFY;
ALTER WAREHOUSE "COMPUTE_WH" SET RESOURCE_MONITOR = "WAREHOUSE_LEVEL_RM";

SQL For Resource Monitor with Weekly Schedule

1
2
3
4
5
6
7
8
9
10
11
12
    CREATE RESOURCE MONITOR "WAREHOUSE_LEVEL_RM" 
    WITH CREDIT_QUOTA = 100, 
    frequency = 'WEEKLY', 
    start_timestamp = 'IMMEDIATELY', 
    end_timestamp = null 
 TRIGGERS 
 ON 95 PERCENT DO SUSPEND 
 ON 100 PERCENT DO SUSPEND_IMMEDIATE 
 ON 90 PERCENT DO NOTIFY 
 ON 85 PERCENT DO NOTIFY 
 ON 80 PERCENT DO NOTIFY;
ALTER WAREHOUSE "COMPUTE_WH" SET RESOURCE_MONITOR = "WAREHOUSE_LEVEL_RM";

SQL For Resource Monitor with Yearly Schedule

1
2
3
4
5
6
7
8
9
10
11
12
CREATE RESOURCE MONITOR "WAREHOUSE_LEVEL_RM" 
    WITH CREDIT_QUOTA = 100, 
    frequency = 'YEARLY', 
    start_timestamp = 'IMMEDIATELY', 
    end_timestamp = null 
 TRIGGERS 
 ON 95 PERCENT DO SUSPEND 
 ON 100 PERCENT DO SUSPEND_IMMEDIATE 
 ON 90 PERCENT DO NOTIFY 
 ON 85 PERCENT DO NOTIFY 
 ON 80 PERCENT DO NOTIFY;
ALTER WAREHOUSE "COMPUTE_WH" SET RESOURCE_MONITOR = "WAREHOUSE_LEVEL_RM";