Learning ETL

    Resource Monitors — Our First Order Of Business

    I don’t plan to build anything large in Snowflake, because I don’t want to run up a big bill.  That said, I do recognize that I will incur some cost with my Snowflake enterprise subscription (and so will you.)  If you skipped the earlier post about cost, you can catch up here.

    A Resource Monitor is an object that can be set up in Snowflake to notify the administrator (ACCOUNTADMIN) that an account or a warehouse (either/or) is approaching a certain usage quota specified by the Resource Monitor configuration.  Like just about everything else in Snowflake, there are a lot of options.  Let me begin by first showing you the statement I ran to create my Resource Monitor.  I logged into Snowflake as the ACCOUNTADMIN (the default account, unless you have specified something else) and clicked on Projects on the upper left.  This took me to a link called Worksheets, and I created a new Worksheet by clicking the blue “+” sign on the upper right.

    Got a blank worksheet?  Great!  Try something like this (you can use the blue triangle on the upper right to run the statements individually.)

    USE ROLE ACCOUNTADMIN; 

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

ALTER WAREHOUSE COMPUTE_WH SET RESOURCE_MONITOR = DontOverdoIt;

    Okay, now, what did we just do there?  Let’s have a look.

    First, we made sure we were using Role ACCOUNTADMIN.  Only an ACCOUNTADMIN can configure a resource monitor.  Other roles may recevie privileges to view a Resource Monitor, but to configure and assign a Resource Monitor, you must be an ACCOUNTADMIN.

    I then created a Resource Monitor called DontOverdoIt.  I set the Credit_Quota to 10 (and you could use any number you want.)  I figure ten credits is about $30.00, give or take, so I should be pretty safe for the moment.

    The Frequency property is set to Monthly, but I could set it to Daily, Weekly, Yearly, or Never.  This property tells me when Snowflake is going to “reset” my quota (and I don’t mind paying $30.00 per month for this.)  If you set it to Never, Snowflake will just keep going until it hits that quota limit.

    The Start_TimeStamp property tells Snowflake when to start adding up the credits.  You can set a date now or in the future.  You cannot set a date in the past.

    Finally, we have to tell Snowflake what to do when the quota is reached.  First, notice the 100 Percent in the last part (the Trigger portion.)  This means that when we reach 100 percent of the 10-credit quota, we will do something (Suspend — we will get to that in a moment.)  We could set the trigger to 90 percent, 80 percent, 5 percent — suppose you had a quota of 100 credits, and you wanted to know when you hit 90 credits so that you could make some decisions (like begging your IT Department for more credits) you could simply configure your Resource Monitor for 90 percent of your 100 credit limit.

    Okay, so you have hit your credit quota.  What happens next?  Well, there are three possibilities:  NOTIFY, which is just like it sounds.  Snowflake will send a message that says “Hey, you are at or near your limit!”  Notifications are sent to all ACCOUNTADMIN users who are set up to receive notifications.  Email can also be used, but it must be configured.  NOTIFY AND SUSPEND will send a notification and suspend all queries after those that are running have finished.  NOTIFY AND SUSPEND IMMEDIATELY will cancel everything running at the moment, and stop it dead in its tracks.  I’m not going to be doing anything really large, so I will let things finish first, and then suspend (big spender, I know.)

    Okay, now our Resource Monitor is created.  Next, we have to apply it to something.  That’s where the ALTER statement comes in.  A Resource Monitor may be assigned to an account or a warehouse.  I am assigning it to the default warehouse — I want to know if I am going to spend more than $30.00.  Please note that you may only assign one Resource Monitor to each account, and one Resource Monitor to each Warehouse.  You can assign a Resource Monitor to more than one warehouse, and more than one account.

    Okay, now that we have this set up, how do we view it?  In the lower left corner of your screen, go to Admin, Cost Management, and click on Resource Monitors.  Double-click DONTOVERDOIT (it will be all caps) and have a look.  I bet we have not used our 10 credits yet!

     

     

    david