Snowflake Task Monitor & Email Alerting

What This POC Does

This proof-of-concept implements a fully automated monitoring system for Snowflake tasks. It uses Snowpark for Python to query task execution history, generates an HTML failure report, and sends it via email — all scheduled to run automatically inside Snowflake.

Repository Structure

snowflake-task-monitor/
├── create_sp_and_task.py          # Deploys the SP and schedules the task
├── deploy_procedures.py           # CI/CD-friendly deployment script
├── html_report.py                 # HTML email body generator
├── mailer.py                      # SMTP email sender
├── requirements.txt
└── stored_procedures/
    └── sp_task_failure_report.py  # Core Snowpark logic

Setup

Clone the repository and install dependencies:

git clone https://github.com/flowbydata/snowflake-task-monitor
cd snowflake-task-monitor
pip install -r requirements.txt

Configure your Snowflake credentials in a .env file:

SNOWFLAKE_ACCOUNT=your_account
SNOWFLAKE_USER=your_user
SNOWFLAKE_PASSWORD=your_password
SNOWFLAKE_WAREHOUSE=COMPUTE_WH
SNOWFLAKE_DATABASE=MONITORING_DB
SNOWFLAKE_SCHEMA=PUBLIC
SMTP_HOST=smtp.gmail.com
SMTP_PORT=587
ALERT_EMAIL=team@yourcompany.com

Deploy

python deploy_procedures.py
python create_sp_and_task.py

This creates the stored procedure and schedules a Snowflake Task to run every day at 8 AM UTC. Any task failures in the past 24 hours will trigger an email report.

Core Stored Procedure

def sp_task_failure_report(session: Session, lookback_hours: int) -> str:
    df = session.sql(f"""
        SELECT
          NAME, DATABASE_NAME, SCHEMA_NAME,
          STATE, ERROR_MESSAGE,
          SCHEDULED_TIME, COMPLETED_TIME
        FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY
        WHERE STATE = 'FAILED'
          AND SCHEDULED_TIME >= DATEADD('hour', -{lookback_hours},
                                        CURRENT_TIMESTAMP())
        ORDER BY SCHEDULED_TIME DESC
    """)
    failures = df.to_pandas()

    if failures.empty:
        return "No failures found."

    html = build_html_report(failures)
    send_email(html)
    return f"Report sent for {len(failures)} failures."

Sample Output

The HTML email includes a colour-coded table with task name, database, schema, error message, scheduled time, and completion time for each failed execution.

The monitoring procedure is itself monitored by Snowflake's native task retry mechanism — so you won't miss failures even if the monitor task has a transient issue.