Automating Snowflake Useful resource Monitor Administration
Managing Snowflake useful resource quotas effectively is essential for price management and efficiency optimization. On this article, I’ll stroll you thru a totally automated resolution utilizing Snowflake’s native options, together with tagging, warehouse metering historical past, Cortex ML forecasting, and duties. It will assist to dynamically replace useful resource monitor quotas for digital warehouses primarily based on historic utilization patterns each month.
To start, we categorize Snowflake warehouses by surroundings utilizing tags. This helps us phase utilization and apply environment-specific quotas.
-- Instance: Tagging warehouses
CREATE DATABASE SAMPLE_DB;
CREATE SCHEMA SAMPLE_DB.SAMPLE_SCHEMA;
CREATE TAG SAMPLE_DB.SAMPLE_SCHEMA.ENVIRONMENT ALLOWED_VALUES = ('DEV','UAT','PRD');
ALTER WAREHOUSE WH_DEV SET TAG SAMPLE_DB.SAMPLE_SCHEMA.ENVIRONMENT = 'DEV';
ALTER WAREHOUSE WH_UAT SET TAG SAMPLE_DB.SAMPLE_SCHEMA.ENVIRONMENT = 'UAT';
ALTER WAREHOUSE WH_PRD SET TAG SAMPLE_DB.SAMPLE_SCHEMA.ENVIRONMENT = 'PRD';
This tagging permits us to filter and analyze utilization knowledge by surroundings (DEV, UAT, PRD) in subsequent steps.
We use the WAREHOUSE_METERING_HISTORY view to extract credit score consumption knowledge. The aim is to construct a month-level view of utilization per surroundings for the previous 12 months.
Not too long ago, we began encountering points utilizing views instantly with ML.FORECAST. A help case is underway and I’ll replace the article as quickly as Snowflake engineering staff fixes it. As a workaround, we switched to TRANSIENT tables to retailer the coaching knowledge.
-- Creating transient desk for coaching knowledge
CREATE OR REPLACE TRANSIENT TABLE SAMPLE_DB.SAMPLE_SCHEMA.WH_DEV_USAGE_TRAINING AS
SELECT
B.TAG_VALUE AS ENVIRONMENT,
TO_TIMESTAMP_NTZ(DATE_TRUNC('MONTH', A.START_TIME)) AS USAGE_MONTH,
SUM(A.CREDITS_USED) AS CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY A
INNER JOIN SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES B
ON A.WAREHOUSE_NAME = B.OBJECT_NAME
AND B.TAG_NAME = 'ENVIRONMENT' AND B.OBJECT_DELETED IS NULL
AND B.DOMAIN = 'WAREHOUSE' AND B.TAG_VALUE = 'DEV'
WHERE A.START_TIME >= DATEADD(MONTH, -12, CURRENT_TIMESTAMP())
GROUP BY ALL;--Repeat these steps for UAT and PRD
⚠️ Be aware: Pay shut consideration to timestamp codecs and guarantee constant truncation to month-level granularity.
Now we use SNOWFLAKE.ML.FORECAST to foretell credit score consumption for the present month. The forecast length dynamically adjusts primarily based on the variety of days within the month (e.g., 28 for February, 31 for August).
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST SAMPLE_DB.SAMPLE_SCHEMA.FR_DEV_WH(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'SAMPLE_DB.SAMPLE_SCHEMA.WH_DEV_USAGE_TRAINING'),
TIMESTAMP_COLNAME => 'USAGE_MONTH',
TARGET_COLNAME => 'CREDITS'
)'';-- Instance: Forecasting for present month
CALL SAMPLE_DB.SAMPLE_SCHEMA.FR_DEV_WH!FORECAST(FORECASTING_PERIODS => 31);
This generates day by day forecasts which we later combination to find out month-to-month quotas.
We sum the forecasted credit by surroundings and use these values to replace or create useful resource screens.
-- Creating useful resource screens
-- Instance for DEV
CREATE OR REPLACE RESOURCE MONITOR RM_DEV
WITH CREDIT_QUOTA = (SELECT SUM($3) FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())),
TRIGGERS ON 80 PERCENT DO NOTIFY
TRIGGERS ON 90 PERCENT DO NOTIFY
TRIGGERS ON 100 PERCENT DO SUSPEND;--You should use question output as credit score quota worth as a substitute of precise SQL question
Repeat for UAT and PRD environments.
Now since we have now forecasted the credit score quota, it’s time to put it into motion. To do that, we have to alter the warehouse with resource_monitor
ALTER WAREHOUSE DEV1_S_VW set RESOURCE_MONITOR = RM_DEV;
ALTER WAREHOUSE DEV2_S_VW set RESOURCE_MONITOR = RM_DEV;
ALTER WAREHOUSE WH_DEV set RESOURCE_MONITOR = RM_DEV;
ALTER WAREHOUSE UAT1_S_VW set RESOURCE_MONITOR = RM_UAT;
ALTER WAREHOUSE UAT2_S_VW set RESOURCE_MONITOR = RM_UAT;
ALTER WAREHOUSE WH_UAT set RESOURCE_MONITOR = RM_UAT;
ALTER WAREHOUSE PRD1_S_VW set RESOURCE_MONITOR = RM_PRD;
ALTER WAREHOUSE PRD2_S_VW set RESOURCE_MONITOR = RM_PRD;
ALTER WAREHOUSE WH_PRD set RESOURCE_MONITOR = RM_PRD;
To automate this course of month-to-month, we schedule the process utilizing a Snowflake Activity. Since ML forecasting might be compute-intensive, we advocate utilizing a bigger warehouse.
-- Creating the duty
CREATE OR REPLACE TASK TASK_UPDATE_RESOURCE_MONITOR
WAREHOUSE = BIG_WH
SCHEDULE = 'USING CRON 0 0 1 * * UTC' -- Runs on 1st of each month
AS
CALL UPDATE_RESOURCE_MONITOR_PROCEDURE();
