Snowflake Data Quality Alerts: Monitoring and Notifying on New Data Issues

※This is an English translation of my original Japanese article Hello, this is Sagara. In this article, I'll share how to create alerts that perform quality tests when new data is added to a table and notify you if anomalies are detected. Background: Alerts on New Data Feature This is a new alert feature released in public preview in March 2025. https://docs.snowflake.com/en/release-notes/2025/other/2025-03-19-alerts-on-new-data https://docs.snowflake.com/en/user-guide/alerts#creating-an-alert-on-new-data As a practical example of this feature, I recently wrote a blog about using it for error notifications with Tasks and Dynamic Tables. https://dev.to/sagara/snowflakes-new-feature-alert-on-new-data-and-try-with-monitoring-tasks-and-dynamic-tables-248o Prerequisites Creating an Email Notification Integration Since we want to send email notifications when our alert conditions are met, let's create an Email Notification Integration with the following query: -- Create Email Notification Integration use role accountadmin; create or replace notification integration email_int type = email enabled = true; grant usage on integration email_int to role sysadmin; Granting Required Permissions We'll execute all queries with the SYSADMIN role, so let's grant the necessary permissions in advance: use role accountadmin; -- Serverless Alert permissions grant execute managed alert on account to role sysadmin; grant execute alert on account to role sysadmin; Data Preparation Execute the following query to prepare the base data: -- Set role use role sysadmin; -- Create database and schema create database if not exists dq_tutorial_db; create schema if not exists dq_tutorial_db.sch; -- Create employees table create or replace table dq_tutorial_db.sch.employeestable ( id number, name varchar, last_name varchar, email varchar, zip_code number ); -- Insert valid data insert into dq_tutorial_db.sch.employeestable (id, name, last_name, email, zip_code) values (1, 'john', 'doe', 'johndoe@example.com', 12345), (2, 'jane', 'smith', 'janesmith@example.com', 23456), (3, 'robert', 'johnson', 'rjohnson@example.com', 34567); -- Verify data select * from dq_tutorial_db.sch.employeestable; Creating and Enabling the Alert Execute the following query to create the alert: -- Create alert use role sysadmin; create or replace alert dq_tutorial_db.sch.data_quality_alert if (exists( select 1 from dq_tutorial_db.sch.employeestable where name = '' or email is null )) then begin -- Get error details let error_details varchar; -- Get rows with blank values select 'blank name values found in the following rows:' || chr(10) || array_to_string(array_agg('id: ' || id || ', last name: ' || last_name), chr(10)) into :error_details from dq_tutorial_db.sch.employeestable where name = ''; -- Add null email addresses let null_email_details varchar; select 'null email values found in the following rows:' || chr(10) || array_to_string(array_agg('id: ' || id || ', name: ' || name || ', last name: ' || last_name), chr(10)) into :null_email_details from dq_tutorial_db.sch.employeestable where email is null; -- Combine error information let full_error_details varchar := :error_details || chr(10) || chr(10) || :null_email_details; -- Send email call system$send_email( 'email_int', 'sagara.satoshi@classmethod.jp', 'data quality alert: issues detected in employee table', 'the following data quality issues were detected:' || chr(10) || chr(10) || :full_error_details || chr(10) || chr(10) || 'please review and fix these issues as soon as possible.' ); end; Then, enable the alert with the following query: -- Enable alert use role sysadmin; alter alert dq_tutorial_db.sch.data_quality_alert resume; Testing the Alert Execute the following query to insert records that will trigger the alert: -- Insert problematic data use role sysadmin; insert into dq_tutorial_db.sch.employeestable (id, name, last_name, email, zip_code) values (4, '', 'williams', 'williams@example.com', 45678), -- Empty name (5, 'michael', 'brown', null, 56789); -- Null email -- Verify data select * from dq_tutorial_db.sch.employeestable; Within a minute, I received the following email. This confirms that our data quality check is working correctly! Considerations for Production Use While we used a simple configuration for this demonstration, here are some important considerations for production use: When defining alerts, it's strongly recommended to limit the detection target to recent records (e.g., last hour or day) For use cases like Snowpipe ingesting data every minute, without time filtering, you migh

Apr 1, 2025 - 04:24

Snowflake Data Quality Alerts: Monitoring and Notifying on New Data Issues

※This is an English translation of my original Japanese article

Hello, this is Sagara.

In this article, I'll share how to create alerts that perform quality tests when new data is added to a table and notify you if anomalies are detected.

Background: Alerts on New Data Feature

This is a new alert feature released in public preview in March 2025.

https://docs.snowflake.com/en/release-notes/2025/other/2025-03-19-alerts-on-new-data

https://docs.snowflake.com/en/user-guide/alerts#creating-an-alert-on-new-data

As a practical example of this feature, I recently wrote a blog about using it for error notifications with Tasks and Dynamic Tables.

https://dev.to/sagara/snowflakes-new-feature-alert-on-new-data-and-try-with-monitoring-tasks-and-dynamic-tables-248o

Prerequisites

Creating an Email Notification Integration

Since we want to send email notifications when our alert conditions are met, let's create an Email Notification Integration with the following query:

-- Create Email Notification Integration
use role accountadmin;
create or replace notification integration email_int
  type = email
  enabled = true;
grant usage on integration email_int to role sysadmin;

Granting Required Permissions

We'll execute all queries with the SYSADMIN role, so let's grant the necessary permissions in advance:

use role accountadmin;

-- Serverless Alert permissions
grant execute managed alert on account to role sysadmin;
grant execute alert on account to role sysadmin;

Data Preparation

Execute the following query to prepare the base data:

-- Set role
use role sysadmin;

-- Create database and schema
create database if not exists dq_tutorial_db;
create schema if not exists dq_tutorial_db.sch;

-- Create employees table
create or replace table dq_tutorial_db.sch.employeestable (
  id number,
  name varchar,
  last_name varchar,
  email varchar,
  zip_code number
);

-- Insert valid data
insert into dq_tutorial_db.sch.employeestable (id, name, last_name, email, zip_code)
values
  (1, 'john', 'doe', 'johndoe@example.com', 12345),
  (2, 'jane', 'smith', 'janesmith@example.com', 23456),
  (3, 'robert', 'johnson', 'rjohnson@example.com', 34567);

-- Verify data
select * from dq_tutorial_db.sch.employeestable;

Creating and Enabling the Alert

Execute the following query to create the alert:

-- Create alert
use role sysadmin;
create or replace alert dq_tutorial_db.sch.data_quality_alert
  if (exists(
    select 1 
    from dq_tutorial_db.sch.employeestable
    where name = '' or email is null
  ))
  then
    begin
      -- Get error details
      let error_details varchar;

      -- Get rows with blank values
      select 'blank name values found in the following rows:' || chr(10) || 
             array_to_string(array_agg('id: ' || id || ', last name: ' || last_name), chr(10))
      into :error_details
      from dq_tutorial_db.sch.employeestable
      where name = '';

      -- Add null email addresses
      let null_email_details varchar;
      select 'null email values found in the following rows:' || chr(10) || 
             array_to_string(array_agg('id: ' || id || ', name: ' || name || ', last name: ' || last_name), chr(10))
      into :null_email_details
      from dq_tutorial_db.sch.employeestable
      where email is null;

      -- Combine error information
      let full_error_details varchar := :error_details || chr(10) || chr(10) || :null_email_details;

      -- Send email
      call system$send_email(
        'email_int',
        'sagara.satoshi@classmethod.jp',
        'data quality alert: issues detected in employee table',
        'the following data quality issues were detected:' || chr(10) || chr(10) || :full_error_details || 
        chr(10) || chr(10) || 'please review and fix these issues as soon as possible.'
      );
    end;

Then, enable the alert with the following query:

-- Enable alert
use role sysadmin;
alter alert dq_tutorial_db.sch.data_quality_alert resume;

Testing the Alert

Execute the following query to insert records that will trigger the alert:

-- Insert problematic data
use role sysadmin;
insert into dq_tutorial_db.sch.employeestable (id, name, last_name, email, zip_code)
values
  (4, '', 'williams', 'williams@example.com', 45678),   -- Empty name
  (5, 'michael', 'brown', null, 56789);                -- Null email

-- Verify data
select * from dq_tutorial_db.sch.employeestable;

Within a minute, I received the following email. This confirms that our data quality check is working correctly!

Considerations for Production Use

While we used a simple configuration for this demonstration, here are some important considerations for production use:

When defining alerts, it's strongly recommended to limit the detection target to recent records (e.g., last hour or day)
- For use cases like Snowpipe ingesting data every minute, without time filtering, you might receive alert notifications every minute once an error is detected.
Whether using serverless alerts or warehouse-specified alerts, the condition query in this example runs every time data is added to the target table, so be mindful of costs
- Alerts can also be scheduled to run at specified frequencies, which may be recommended depending on your data update frequency and required quality assurance level.

Side Note

I initially planned to create an alert using Data Metric Functions, but as of April 1, 2025, stored procedures are not supported for alerts on new data, and DMFs fall into this category, resulting in an error...

https://docs.snowflake.com/en/user-guide/alerts#alerts-on-new-data

Documentation note as of April 1, 2025:

Actual query error: