logo
Courses Blog Cloudyard Snowflake Updates Login

Keeping an Eye on Your Snowflake Warehouse: Automated Monitoring and Email Alerts

Mon Apr 15, 2024

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:

  1. 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.
  2. 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.
  3. 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:

: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_TRACKWAREHOUSE = 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);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();

Procedure:

CREATE OR REPLACE PROCEDURE SEND_MAIL()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sql_query = "SELECT QUERY_TEXT, ROLE_NAME, WAREHOUSE_NAME, START_TIME, END_TIME FROM WH_AUDIT_TABLE_STM";
var sqlstmt = snowflake.createStatement({ sqlText: sql_query });
var rs = sqlstmt.execute();
var msg = `<html><head><style>
  table {
    border-collapse: collapse;
    width: 100%;
    border: 2px solid #4CAF50;
  }
  th, td {
    border: 2px solid #4CAF50;
    padding: 8px;
    text-align: center;
  }
  th {
    background-color: #2196F3; /* Different color for the header */
    color: white;
  }
  td {
    background-color: #f8f8f8;
  }
</style></head><body><table><tr><th>QUERY TEXT</th><th>ROLE NAME</th><th>WAREHOUSE NAME</th><th>START TIME</th><th>END TIME</th></tr>`;
 
while (rs.next()) {
  var QTEXT = rs.getColumnValue(1);
  QTEXT = QTEXT.replace(/'/g, "''"); 
  var RNAME = rs.getColumnValue(2);
  var WAREHOUSE_NAME = rs.getColumnValue(3);
  var STIME = rs.getColumnValue(4);
  var ETIME = rs.getColumnValue(5);
  var rowColor = '#c8e6c9';  // Light green color

  msg += `<tr><td>${QTEXT}</td><td>${RNAME}</td><td>${WAREHOUSE_NAME}</td><td>${STIME}</td><td>${ETIME}</td></tr>`;
}
msg += `</table></body></html>`;

var proc = `CALL SYSTEM$SEND_EMAIL('WH_CHANGE_NOTIFICATION', 'sachin.mittal04@gmail.com', 'Daily Credit Consumption: Snowflake Account', '${msg}', 'text/html')`;

var stmt = snowflake.createStatement({ sqlText: proc });
stmt.execute();
var copy_command = `CREATE TEMPORARY TABLE if not exists STREAM_CONSUME AS SELECT * FROM WH_AUDIT_TABLE_STM`;
snowflake.createStatement({sqlText: copy_command}).execute();
 
return "Succeeded! Email sent to Admin";
$$;

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”;

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!

Sachin Mittal
Snowflake Architect.

Blog Feedback

Course Feedback will be displayed here.
Launch your GraphyLaunch your Graphy
100K+ creators trust Graphy to teach online
𝕏
Cloudlearningyard 2025 Privacy policy Terms of use Contact us Refund policy