Skip to main content

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:

  1. Hard task failures using TASK_HISTORY.
  2. Silent data ingestion failures by checking table timestamps.
  3. Compute bottlenecks by monitoring Dynamic Table lag states.
  4. Sending automated emails via NOTIFICATION INTEGRATION.