1
0
In the world of data warehousing, keeping track of changes to your Snowflake warehouse size is crucial. Unexpected adjustments can impact performance and potentially incur additional costs. This blog post introduces a solution for automated warehouse size change monitoring and email alerts using Snowflake Streams and Tasks.
Imagine you’re a data analyst managing a busy Snowflake account. You rely on a designated warehouse to handle your data processing needs. However, you’re concerned about unauthorized modifications to the warehouse size, which could disrupt your workflows.
This code snippet provides an automated solution for this scenario:
- Warehouse Audit Tracking: It creates a table (WH_AUDIT_TABLE) to store historical records of warehouse size changes. This table is populated by a scheduled task (WH_CHANGE_TRACK) that captures relevant information from Snowflake’s information schema.
- Real-time Monitoring with Streams: A stream (WH_AUDIT_TABLE_STM) is created on top of the WH_AUDIT_TABLE. Streams are lightweight, real-time data pipelines that allow us to react to changes as they happen.
- Automated Email Notifications: Another scheduled task (WH_CHANGE_MAIL) triggers whenever new data appears in the stream (WH_AUDIT_TABLE_STM). This triggers a custom JavaScript procedure (SEND_MAIL) that:
- Queries the stream for recent WH size changes.
- Formats the data into an HTML table for easy readability.
- Sends an email notification to a designated stakeholder (e.g., administrator) with the details of the changes.
Benefits of this Approach:
- Proactive Monitoring: You’ll be automatically notified of any WH size changes, allowing you to investigate and take appropriate action if necessary.
- Improved Security: This system helps identify potential unauthorized access or misconfigurations related to warehouse size.
- Cost Optimization: By being aware of unexpected changes, you can potentially avoid unnecessary charges associated with larger warehouse sizes.
- Streamlined Communication: Automated email alerts ensure stakeholders are informed promptly about critical warehouse modifications.
Technical Implementation:
Technical Implementation:
1.Create Audit Table.
create or replace table WH_AUDIT_TABLE as SELECT query_id,query_text, role_name, warehouse_name, start_time, end_time FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY()) where 1=2;
2. Create TASK to load Audit table.
CREATE TASK WH_CHANGE_TRACK
WAREHOUSE = COMPUTE_WH
SCHEDULE = '1 MINUTE'
AS
insert into WH_AUDIT_TABLE (query_id,query_text, role_name, warehouse_name, start_time, end_time)
SELECT query_id,query_text, role_name, warehouse_name, start_time, end_time
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY()) Q
where database_name is null and query_text like 'alter WAREHOUSE%'
and current_timestamp()>dateadd('hour',-1,current_timestamp())
and not exists
(SELECT '1' from WH_AUDIT_TABLE W where Q.Query_id = w.query_id);
3. Create STREAM on Audit Table.
create or replace stream WH_AUDIT_TABLE_STM on table WH_AUDIT_TABLE;
4. Create TASK to check Stream and send Email about Warehouse changes.
CREATE OR REPLACE TASK WH_CHANGE_MAIL
WAREHOUSE = COMPUTE_WH
SCHEDULE = '2 minute'
WHEN
SYSTEM$STREAM_HAS_DATA('WH_AUDIT_TABLE_STM')
AS
CALL SEND_MAIL();
5- Create SEND_EMAIL Procedure.
SEND_EMAIL Proc
6. Resume Both TASKS
alter task WH_CHANGE_TRACK RESUME;
alter task WH_CHANGE_MAIL RESUME;
7. Now change the size of Warehouses and verify the Audit table and respective Stream:
- “WH2” changes to “Large” from “XSMALL”;
- “COMPUTE_WH” changes to “SMALL” from “XSMALL”;
8.Verify the Audit table.
9. Verify the Stream.
10: Mail sent to Admin with Warehouse changes details.
Conclusion
This code demonstrates how Snowflake Streams and Tasks can be leveraged to build a robust warehouse change monitoring system. By implementing this solution, you gain valuable insights into your Snowflake environment and ensure optimal resource utilization.
I hope this blog post empowers you to proactively monitor your Snowflake warehouses and maintain control over your data resources!
