Capstone Project: Pipeline Monitoring & Alerting Setup
Welcome to the DataOps Monitoring Capstone!
In this project, you will step into the role of a Site Reliability Engineer (SRE) for a fintech startup in Bengaluru. Your data engineering team has built several complex pipelines using Tasks, Snowpipe, and Dynamic Tables. However, they frequently break silently.
Your objective is to build a robust, automated monitoring and alerting system using Snowflake's native capabilities to catch failures before the business users notice them.
Project Scope
- Setting up the Monitoring Database
- Task History Failure Alerts
- Snowpipe Stale Data Checks
- Data Freshness Monitors
- Email Integration Setup
Step 1: Database and Schema Setup
First, we need a dedicated database to store our monitoring logs and alerts.
Run the following code in your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_DATAOPS_DB;
USE DATABASE BEEMS_DATAOPS_DB;
CREATE SCHEMA IF NOT EXISTS MONITORING;
USE SCHEMA MONITORING;
-- Create some dummy target tables to monitor
CREATE OR REPLACE TABLE RAW_PAYMENTS (PAYMENT_ID INT, AMOUNT NUMBER, INGESTED_AT TIMESTAMP);
CREATE OR REPLACE TABLE PROCESSED_PAYMENTS (PAYMENT_ID INT, STATUS STRING, PROCESSED_AT TIMESTAMP);
Step 2: Creating an Email Notification Integration
Before we can send alerts, Snowflake must be allowed to send emails to your team.
(This step requires ACCOUNTADMIN privileges. To check your current role, run SELECT CURRENT_ROLE();)
USE ROLE ACCOUNTADMIN;
-- Create the integration to send emails to the DataOps team in Bengaluru
CREATE OR REPLACE NOTIFICATION INTEGRATION BLR_DATAOPS_EMAIL_INT
TYPE = EMAIL
ENABLED = TRUE
ALLOWED_RECIPIENTS = ('dataops.alert@yourcompany.com', 'rohan.sre@yourcompany.com');
-- Grant usage to SYSADMIN so they can create alerts using this integration
GRANT USAGE ON INTEGRATION BLR_DATAOPS_EMAIL_INT TO ROLE SYSADMIN;
Step 3: Task History Monitoring Alert
We want to receive an email immediately if any Snowflake Task in our account fails.
USE ROLE SYSADMIN;
-- Create an alert that runs every 30 minutes
CREATE OR REPLACE ALERT TASK_FAILURE_ALERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = '30 MINUTE'
-- Condition: Are there any FAILED tasks in the last 30 minutes?
IF (EXISTS (
SELECT 1
FROM table(information_schema.task_history(
scheduled_time_range_start=>dateadd('minute', -30, current_timestamp())
))
WHERE STATE = 'FAILED'
))
-- Action: Send an email using the integration
THEN CALL SYSTEM$SEND_EMAIL(
'BLR_DATAOPS_EMAIL_INT',
'dataops.alert@yourcompany.com',
'CRITICAL: Snowflake Task Failure Detected',
'One or more Snowflake tasks have failed in the last 30 minutes. Please check TASK_HISTORY.'
);
-- Don't forget to resume the alert!
ALTER ALERT TASK_FAILURE_ALERT RESUME;
Step 4: Snowpipe Stale Data Check
Snowpipe can stop silently if the AWS SQS queue is deleted or IAM roles change. We will monitor the RAW_PAYMENTS table. If no new records are ingested for more than 2 hours, we fire an alert.
-- Create an alert to check for stale Snowpipe ingestion
CREATE OR REPLACE ALERT STALE_PAYMENTS_ALERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = '60 MINUTE'
-- Condition: Is the maximum timestamp older than 2 hours?
IF (EXISTS (
SELECT 1
FROM BEEMS_DATAOPS_DB.MONITORING.RAW_PAYMENTS
HAVING MAX(INGESTED_AT) < DATEADD('hour', -2, CURRENT_TIMESTAMP())
OR COUNT(*) = 0 -- Also alert if the table is completely empty
))
THEN CALL SYSTEM$SEND_EMAIL(
'BLR_DATAOPS_EMAIL_INT',
'dataops.alert@yourcompany.com',
'WARNING: Snowpipe is Stale',
'No new payments have been ingested into RAW_PAYMENTS in the last 2 hours. Check the Pipe status.'
);
ALTER ALERT STALE_PAYMENTS_ALERT RESUME;
Step 5: Dynamic Table Freshness Monitor
If your team uses Dynamic Tables for the Gold layer, you need to ensure the automated background refresh isn't lagging behind the Target Lag.
-- Alert if any Dynamic Table is currently lagging
CREATE OR REPLACE ALERT DYNAMIC_TABLE_LAG_ALERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = '15 MINUTE'
-- Condition: Check the refresh history for lagging state
IF (EXISTS (
SELECT 1
FROM table(information_schema.dynamic_table_refresh_history())
WHERE STATE = 'LAGGING'
AND REFRESH_END_TIME >= DATEADD('minute', -15, CURRENT_TIMESTAMP())
))
THEN CALL SYSTEM$SEND_EMAIL(
'BLR_DATAOPS_EMAIL_INT',
'dataops.alert@yourcompany.com',
'ALERT: Dynamic Table Lagging',
'A Dynamic Table is failing to meet its Target Lag. Consider scaling up the warehouse.'
);
ALTER ALERT DYNAMIC_TABLE_LAG_ALERT RESUME;
Step 6: Building a Pipeline Health Dashboard (SQL)
Alerts are proactive, but sometimes you just want a single dashboard to view the health of everything. You can create a materialized view or a standard view that acts as the source for your BI tool (like Tableau or Superset).
CREATE OR REPLACE VIEW PIPELINE_HEALTH_DASHBOARD AS
SELECT
'TASK' AS PIPELINE_TYPE,
NAME AS OBJECT_NAME,
STATE AS CURRENT_STATUS,
ERROR_MESSAGE AS REASON,
COMPLETED_TIME AS LAST_ACTIVITY
FROM table(information_schema.task_history(scheduled_time_range_start=>dateadd('day', -1, current_timestamp())))
WHERE STATE = 'FAILED'
UNION ALL
SELECT
'DYNAMIC_TABLE' AS PIPELINE_TYPE,
NAME AS OBJECT_NAME,
STATE AS CURRENT_STATUS,
'Exceeded Target Lag' AS REASON,
DATA_TIMESTAMP AS LAST_ACTIVITY
FROM table(information_schema.dynamic_table_refresh_history())
WHERE STATE = 'LAGGING';
-- Query the dashboard
SELECT * FROM PIPELINE_HEALTH_DASHBOARD;
Summary
Congratulations! You have built a comprehensive DataOps monitoring system. You are now proactively tracking:
- Hard task failures using
TASK_HISTORY. - Silent data ingestion failures by checking table timestamps.
- Compute bottlenecks by monitoring Dynamic Table lag states.
- Sending automated emails via
NOTIFICATION INTEGRATION.