Most organizations rely on Snowflakeās robust data cloud, but as usage grows, so does the complexity of managing it. Manual processes for alerting, monitoring, and provisioning are error-prone, slow, and can drive up costs.
This is where phDataās Provision Tool helps with automation.
By automating Snowflake alerting and monitoring through an Infrastructure as Code approach, the Provision Tool empowers you to define all your monitoring resources in a single, version-controlled template, eliminating tedious manual SQL, reducing error risk, and dramatically improving your operations.
In this blog, weāll explain how automating with the Provision Tool makes it simple to get real-time alerts, control credit usage, and provision resourcesāno matter how complex your Snowflake footprint becomes.
WhatĀ is the Provision Tool?
The Provision Tool is a declarative resource provisioning tool for the Snowflake Data Cloud in the phData Toolkit, enabling operations teams to manage Snowflake objects, such as users, roles, privileges, alerts, resource monitors, and other resources.
It allows users to manage Snowflake objects’ lifecycles (creating, updating, and destroying) using a templated approach. This provides a well-structured information architecture that produces an HTML report after object provisioning.
You can also integrate it with ticket management systems, such as Jira and ServiceNow, allowing self-service provisioning for Snowflake objects and access requests based on your approval workflow.
The Provision Tool offers the following benefits:
Declarative, template-based provisioning ensures that output is consistent, scalable, and traceable, while also reducing errors.
Support for massive scale: provision hundreds of thousands of resources without any decline in performance.
Simple integration with CI/CD tools like GitHub, GitLab, and Bitbucket for approval-based provisioning.
This article will primarily focus on implementing Snowflakeās alerting and monitoring capabilities using the Provision Tool.
Alerting & Monitoring in Snowflake using the Provision Tool
phDataās Provision Tool uses Infrastructure as Code (IaC) methodology to define all resources needed for alerting and monitoring.
Create Resources
The following YAML code snippet in the Provision Tool resource file will create the specified Snowflake objects with a single command: toolkit provision apply
Database
Schema
Role
Notification Integration
Alert
Resource Monitor
Warehouse
Grant privileges to the role on different objects
# create database GOVERNANCE
databases:
- name: governance
# create schema GOVERNANCE.ALERT
schemas:
- name: alert
database: governance
# create ROLE to manage alerts
roles:
- name: alert_admin
# grant ALERT_ADMIN to provision tool service account role
roleGrants:
- name: alert_admin
toRoles:
- provision_admin
privilegeGrants:
# grant USAGE privilege to ALERT_ADMIN role on database GOVERNANCE
- objectType: database
objectName: governance
roleName: alert_admin
privilege: usage
# grant privileges to ALERT_ADMIN role on schema GOVERNANCE.ALERT
- objectType: schema
objectName: governance.alert
roleName: alert_admin
privilege:
- usage
- create alert
# privilege grants to the ALERT_ADMIN role to manage alerts
- objectType: account
roleName: alert_admin
owner: accountadmin
privilege:
- create integration
- execute alert
# grant USAGE privilege to ALERT_ADMIN role on integration ALERT_EMAIL_INTEGRATION
- objectType: integration
objectName: alert_email_integration
roleName: alert_admin
privilege: usage
# grant privileges to ALERT_ADMIN role on warehouse ALERT_WH
- objectType: warehouse
objectName: alert_wh
roleName: alert_admin
privilege: usage
# create notification integration ALERT_EMAIL_INTEGRATION
notificationIntegrations:
- name: alert_email_integration
integrationType: email
properties:
enabled: true
default_subject: 'Snowflake Alert: AWS Sandbox Account'
comment: Notification integration used for email alerts
default_recipients:
- first.last@example.com
# create alert USER_LOGIN_ALERT
alerts:
- name: user_login_alert
database: governance
schema: alert
warehouse: alert_wh
# hourly at minute 0
schedule: USING CRON 0 * * * * UTC
condition: |
SELECT *
FROM snowflake.account_usage.login_history
WHERE user_name = 'JDOE'
AND event_timestamp >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
AND is_success = 'YES'
action: |
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
'{ "text/plain": "Snowflake Alert: User login" }',
'{
"alert_email_integration": {
"subject": "Snowflake Alert: User login"
}
}'
)
state: resume
properties:
comment: Alerts if the user was used in the last hour
# create resource monitor WEEKLY_RESOURCE_MONITOR
resourceMonitors:
- name: weekly_resource_monitor
creditQuota: 100
startTimestamp: immediately
frequency: weekly
# update the user list to get notified
notifyUsers:
- JDOE
triggers:
- threshold: 75
action: notify
- threshold: 100
action: suspend
- threshold: 110
action: suspend_immediate
# create warehouse ALERT_WH
warehouses:
- name: alert_wh
properties:
warehouse_size: xsmall
resource_monitor: weekly_resource_monitor
auto_suspend: 60
auto_resume: true
initially_suspended: true
warehouse_type: standard
min_cluster_count: 1
max_cluster_count: 1
scaling_policy: standard
enable_query_acceleration: false
comment: Warehouse for alerts
The Provision Tool compares the Snowflake account with the objects defined above and translates the snippet into Snowflake queries (seen below). You only need to ensure the correctness of the SQL statements included in the alert’s condition and action sections.
The tool will also identify the dependency between queries and arrange them in the correct order of execution.
ROLE
(+) CREATE ROLE IF NOT EXISTS ALERT_ADMIN
RESOURCE MONITOR
(+) CREATE RESOURCE MONITOR IF NOT EXISTS WEEKLY_RESOURCE_MONITOR
CREDIT_QUOTA = 100
FREQUENCY = WEEKLY
START_TIMESTAMP = IMMEDIATELY
NOTIFY_USERS = (JDOE)
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND
ON 110 PERCENT DO SUSPEND_IMMEDIATE
WAREHOUSE
(+) CREATE WAREHOUSE IF NOT EXISTS ALERT_WH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = true
INITIALLY_SUSPENDED = true
WAREHOUSE_TYPE = 'STANDARD'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
SCALING_POLICY = 'STANDARD'
ENABLE_QUERY_ACCELERATION = false
COMMENT = 'Warehouse for alerts'
(Ī) ALTER WAREHOUSE IF EXISTS ALERT_WH SET RESOURCE_MONITOR = weekly_resource_monitor
DATABASE
(+) CREATE DATABASE IF NOT EXISTS GOVERNANCE
NOTIFICATION INTEGRATION
(+) CREATE NOTIFICATION INTEGRATION IF NOT EXISTS ALERT_EMAIL_INTEGRATION
TYPE = EMAIL
ENABLED = true
DEFAULT_SUBJECT = 'Snowflake Alert: AWS Sandbox Account'
COMMENT = 'Notification integration used for email alerts'
DEFAULT_RECIPIENTS = ('first.last@example.com')
SCHEMA
(+) CREATE SCHEMA IF NOT EXISTS GOVERNANCE.ALERT
ALERT
(+) CREATE OR REPLACE ALERT GOVERNANCE.ALERT.USER_LOGIN_ALERT
WAREHOUSE = ALERT_WH
SCHEDULE = 'USING CRON 0 * * * * UTC'
COMMENT = 'Alerts if the user was used in the last hour'
IF(EXISTS(
SELECT *
FROM snowflake.account_usage.login_history
WHERE user_name = 'JDOE'
AND event_timestamp >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
AND is_success = 'YES'
))
THEN
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
'{ "text/plain": "Snowflake Alert: User login" }',
'{
"alert_email_integration": {
"subject": "Snowflake Alert: User login"
}
}'
)
(Ī) ALTER ALERT IF EXISTS GOVERNANCE.ALERT.USER_LOGIN_ALERT RESUME
PRIVILEGE GRANT
(+) GRANT usage ON database GOVERNANCE TO ROLE ALERT_ADMIN
(+) GRANT usage, create alert ON schema GOVERNANCE.ALERT TO ROLE ALERT_ADMIN
(+) GRANT create integration, execute alert ON account TO ROLE ALERT_ADMIN
(+) GRANT usage ON integration ALERT_EMAIL_INTEGRATION TO ROLE ALERT_ADMIN
(+) GRANT usage ON warehouse ALERT_WH TO ROLE ALERT_ADMIN
ROLE GRANT
(+) GRANT ROLE ALERT_ADMIN TO ROLE PROVISION_ADMIN
Update Resources
To modify any of the objects created previously, simply update the resource file for that specific object. For example, if you’d like to alert another user when credit usage hits the threshold, add a new entry under notifyUsers and execute the toolkit provision apply command again.
# update resource monitor WEEKLY_RESOURCE_MONITOR
resourceMonitors:
- name: weekly_resource_monitor
creditQuota: 100
startTimestamp: immediately
frequency: weekly
# update the user list to get notified
notifyUsers:
- JDOE
- JACK -- new user added
triggers:
- threshold: 75
action: notify
- threshold: 100
action: suspend
- threshold: 110
action: suspend_immediate
The Provision Tool will calculate the difference between Snowflakeās current state and the resource file, including the above change, then execute the following SQL statement:
RESOURCE MONITOR
(Ī) ALTER RESOURCE MONITOR IF EXISTS WEEKLY_RESOURCE_MONITOR SET
CREDIT_QUOTA = 100
FREQUENCY = WEEKLY
START_TIMESTAMP = IMMEDIATELY
NOTIFY_USERS = (JDOE, JACK)
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND
ON 110 PERCENT DO SUSPEND_IMMEDIATE
Delete Resources
To destroy the resource, simply set the destroy flag to true for that object in the resource file and run the toolkit provision apply command.
For example:
# destroy resource monitor WEEKLY_RESOURCE_MONITOR
resourceMonitors:
- name: weekly_resource_monitor
destroy: true -- destroy flag is set to true
creditQuota: 100
startTimestamp: immediately
frequency: weekly
# update the user list to get notified
notifyUsers:
- JDOE
- JACK
triggers:
- threshold: 75
action: notify
- threshold: 100
action: suspend
- threshold: 110
Provision Tool will translate this YAML code snippet into the following SQL:
RESOURCE MONITOR
(-) DROP RESOURCE MONITOR IF EXISTS WEEKLY_RESOURCE_MONITOR
Closing
As shown above, you can automate and consistently provision multiple Snowflake objects, such as databases, roles, alerts, warehouses, and resource monitors, by executing a single command in the phData Provision Tool. We recommend exploring the Provision Tool available in the phData Toolkit and the Toolkit Snowflake Demo Github repository that demonstrates using the tool with CICD.Ā
You can also try out the tutorial, which requires no setup to understand the tool.Ā
If you are facing challenges with repetitive manual Snowflake user onboarding and access requests or struggling to manage the growing Snowflake objects manually, the Provision Tool can easily accommodate you by accelerating provisioning through multi-threading.Ā
This makes it well-suited for environments with significantly large and complex information architectures.Ā
Learn more about our other automation solutions today!
Streamline your operations with outāofātheābox automation tools designed to handle everything from provisioning to access and auditingāno custom scripts required.
