Credit Consumption Notification by Role

Imagine you’re responsible for overseeing the usage of Snowflake credits across different roles within your organization. You need a streamlined way to monitor credit consumption by role over specific periods to identify any anomalies or trends. This stored procedure, SendEmailWithCreditDetails, automates the process of notifying users about Snowflake credit usage within a specified timeframe.Consider you are managing a bustling Snowflake environment with multiple warehouses and roles. Keeping track of credit consumption across various departments and projects can be a daunting task. Manually monitoring usage reports and identifying potential overruns can be time-consuming and reactive.This blog dives deep into the SendEmailWithCreditDetails stored procedure, your trusty sidekick in the fight against surprise credit bills. We’ll dissect its functionalityFunctionality:

1. Data Retrieval: The procedure executes three queries to gather relevant information:
   • Total warehouse elapsed time (WH_TOT_TIME).
   • Role-wise elapsed time within each warehouse (ROL_TOT_TIME).
   • Total credits used per warehouse and period (WH_CREDIT).
2. Credit Usage Calculation: It calculates the percentage of credits used by each role within a warehouse by dividing the role’s elapsed time by the total warehouse elapsed time and multiplying by the total credits used for that warehouse in the period.
3. Email Construction: The procedure constructs an HTML table using JavaScript, dynamically populating it with the retrieved data. It also implements conditional formatting to highlight periods where credit usage exceeds a certain threshold (e.g., 100%).
4. Email Sending: Finally, it leverages Snowflake’s SYSTEM$SEND_EMAIL stored procedure to send an email notification containing the credit usage details in a formatted table to a designated recipient (e.g., sachin.mittal04@gmail.com in this example).

You can configure the procedure to run periodically (e.g., daily or weekly) to monitor credits usage within your Snowflake account. This automated notification system keeps stakeholders informed about credits consumption patterns and potential cost implications. Benefits:

Efficient Monitoring: Automates the process of tracking credit usage across different roles, saving time and effort. 

Timely Notifications: Enables proactive identification of excessive credit usage or unusual patterns, facilitating prompt action. 

Reduced Manual Work: Automates credit usage monitoring and notification, freeing up time for other tasks..