Track Snowflake Credit Usage per Warehouse via Email

Hey there, Darian Vance here. Let’s talk about keeping an eye on Snowflake costs. For the longest time, I was the guy who’d log into the UI every Monday morning, manually run a query, and paste the results into a Slack channel. It felt productive, but it was a total time sink. Automating this single task saved me a couple of hours a month and, more importantly, once caught a rogue data science query that was burning credits over a weekend.

This setup is my go-to for keeping an eye on things without the manual overhead. It’s a simple, robust way to get a weekly pulse on your warehouse credit usage pushed directly to your team’s inbox. Let’s build it.

Prerequisites

• A Snowflake account with a role that has permissions to query the SNOWFLAKE.ACCOUNT_USAGE schema.
• Python 3.8 or newer installed on a machine where you can schedule tasks.
• An email account that you can send mail from (with SMTP server details). For services like Gmail, you’ll need to generate an “App Password”.
• Basic familiarity with Python and environment variables.

The Step-by-Step Guide

Step 1: The Heart of the Matter – The SQL Query

First, we need the query that pulls the data. We’ll use the WAREHOUSE_METERING_HISTORY view. This view gives us a clean, aggregated look at credit usage per warehouse over time.

Here is the query I use. It groups credits by warehouse name for the last 7 days and rounds the result for readability.

SELECT
    WAREHOUSE_NAME,
    SUM(CREDITS_USED) AS TOTAL_CREDITS_USED
FROM
    SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE
    START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY
    WAREHOUSE_NAME
ORDER BY
    TOTAL_CREDITS_USED DESC;

Pro Tip: The ACCOUNT_USAGE schema can have a latency of up to a few hours. This is perfectly fine for a weekly report but isn’t suitable for real-time monitoring. Also, be mindful of the time zone your Snowflake account operates in, as CURRENT_TIMESTAMP() will respect that setting.

Step 2: The Python Script to Bring It All Together

Now, let’s write the Python script that runs the query, formats the data, and emails the report. I’ll skip the standard virtualenv setup since you likely have your own workflow for that. Let’s jump straight to the logic.

First, you’ll need to install a few libraries. In your terminal, run a command like this to get the dependencies: pip install snowflake-connector-python pandas python-dotenv.

A. Securely Store Your Credentials

We should never hardcode credentials. I use a config.env file to store sensitive information. Create a file with that name in the same directory as your script and add your details:

# Snowflake Credentials
SF_USER="your_snowflake_user"
SF_PASSWORD="your_snowflake_password"
SF_ACCOUNT="your_account_identifier"
SF_WAREHOUSE="your_reporting_warehouse"
SF_DATABASE="SNOWFLAKE"
SF_SCHEMA="ACCOUNT_USAGE"
SF_ROLE="your_snowflake_role"

# Email Configuration
SMTP_SERVER="smtp.example.com"
SMTP_PORT=587
EMAIL_SENDER="sender@example.com"
EMAIL_PASSWORD="your_app_password"
EMAIL_RECIPIENT="recipient@example.com"

B. The Python Code

Here is the full script. I’ve added comments to explain what each part does. It connects to Snowflake, runs our query, uses the excellent Pandas library to create a clean HTML table from the results, and then emails it using Python’s built-in `smtplib`.

import os
import smtplib
import ssl
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import date

import pandas as pd
from dotenv import load_dotenv
from snowflake.connector import connect

def fetch_snowflake_data(conn):
    """Fetches warehouse credit usage from Snowflake."""
    query = """
    SELECT
        WAREHOUSE_NAME,
        ROUND(SUM(CREDITS_USED), 2) AS TOTAL_CREDITS_USED
    FROM
        SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE
        START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
    GROUP BY
        WAREHOUSE_NAME
    HAVING
        SUM(CREDITS_USED) > 0
    ORDER BY
        TOTAL_CREDITS_USED DESC;
    """
    cursor = conn.cursor()
    cursor.execute(query)
    df = cursor.fetch_pandas_all()
    cursor.close()
    return df

def send_email(html_body):
    """Sends an email with the given HTML body."""
    sender_email = os.getenv("EMAIL_SENDER")
    receiver_email = os.getenv("EMAIL_RECIPIENT")
    password = os.getenv("EMAIL_PASSWORD")
    smtp_server = os.getenv("SMTP_SERVER")
    smtp_port = int(os.getenv("SMTP_PORT", 587))

    message = MIMEMultipart("alternative")
    today = date.today().strftime("%Y-%m-%d")
    message["Subject"] = f"Snowflake Weekly Credit Report: {today}"
    message["From"] = sender_email
    message["To"] = receiver_email

    message.attach(MIMEText(html_body, "html"))

    context = ssl.create_default_context()
    try:
        with smtplib.SMTP(smtp_server, smtp_port) as server:
            server.starttls(context=context)
            server.login(sender_email, password)
            server.sendmail(sender_email, receiver_email, message.as_string())
        print("Email sent successfully!")
    except Exception as e:
        print(f"Error sending email: {e}")

def main():
    """Main function to run the reporting process."""
    load_dotenv('config.env')

    try:
        conn = connect(
            user=os.getenv("SF_USER"),
            password=os.getenv("SF_PASSWORD"),
            account=os.getenv("SF_ACCOUNT"),
            warehouse=os.getenv("SF_WAREHOUSE"),
            database=os.getenv("SF_DATABASE"),
            schema=os.getenv("SF_SCHEMA"),
            role=os.getenv("SF_ROLE"),
        )
        print("Successfully connected to Snowflake.")
    except Exception as e:
        print(f"Error connecting to Snowflake: {e}")
        return

    usage_df = fetch_snowflake_data(conn)
    conn.close()

    if usage_df.empty:
        print("No credit usage data found for the last 7 days.")
        # You could optionally send an email saying no data was found
        return

    # Convert DataFrame to a styled HTML table
    html_table = usage_df.to_html(index=False, border=0, classes='dataframe')
    
    # Add some basic styling for the email
    html_content = f"""
    <html>
    <head>
    <style>
        body {{ font-family: sans-serif; }}
        .dataframe {{
            border-collapse: collapse;
            margin: 20px 0;
            font-size: 0.9em;
            width: auto;
        }}
        .dataframe th, .dataframe td {{
            padding: 12px 15px;
            border: 1px solid #dddddd;
        }}
        .dataframe th {{
            background-color: #009879;
            color: #ffffff;
            text-align: left;
        }}
        .dataframe tbody tr:nth-of-type(even) {{
            background-color: #f3f3f3;
        }}
    </style>
    </head>
    <body>
        <h2>Snowflake Weekly Credit Usage Report</h2>
        <p>Below is the credit consumption per warehouse for the past 7 days.</p>
        {html_table}
        <p>This is an automated report.</p>
    </body>
    </html>
    """
    
    send_email(html_content)

if __name__ == "__main__":
    main()

Step 3: Schedule the Script

The final step is automation. On a Linux-based server, I use a simple cron job. On Windows, Task Scheduler does the same thing. To run this script every Monday at 2 AM, your cron entry would look like this.

0 2 * * 1 python3 script.py

Pro Tip: Make sure you run the cron job from the directory containing your script and the config.env file, or provide an absolute path to your script and handle the path to the config file within the script itself. Simplicity is key, so I usually keep them together.

Common Pitfalls (Where I Usually Mess Up)

• Permissions: The most common issue is the Snowflake role lacking permissions. Your role needs USAGE permission on the SNOWFLAKE database. If the script fails with an access error, check your role’s grants first.
• SMTP Authentication: Email servers, especially services like Gmail or Outlook 365, are picky. You almost always need to generate an “App Password” instead of using your regular account password. Also, corporate firewalls can block SMTP ports, so if you get a connection timeout, check with your network team.
• Empty Results: My script includes a check for an empty DataFrame. It’s possible for a reporting period to have zero usage. You might want to modify the script to send a confirmation email saying “No usage detected” so you know the script ran successfully.

Conclusion

And there you have it. A straightforward, automated system for monitoring Snowflake credit usage that takes about 30 minutes to set up. In my production setups, this has been invaluable for cost management and capacity planning. It keeps the engineering team aware of their footprint and helps us spot anomalies before they become major incidents.

You can easily extend this foundation—add alerting for when a warehouse exceeds a certain threshold, log the results to a database for long-term trend analysis, or even generate charts. But for getting started, this email report provides 80% of the value with 20% of the effort. Happy building!

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions