Email the data steward when a Snowflake data quality check fails
The short answer
Use a Snowflake alert. Every 60 minutes it checks whether any data quality check has failed. If one has, it looks up the table's steward in a small mapping table and asks Snowflake to send them an email. If nothing has failed, nothing is sent. It uses only Snowflake features, with no outside tool.
What you will have at the end
One SQL file, email_steward_on_dq_failure.sql, with four parts: data quality checks attached to a table, a table that says who owns it, a stored procedure that sends the email, and an alert that runs the procedure when a check fails. It also plants errors and shows the alert firing, then shows a clean run where it stays quiet.
The checks are data metric functions with expectations (read 2026-10-30). A data metric function counts something about a column; an expectation says what the count must be. Data quality monitoring requires Enterprise Edition (Snowflake docs, read 2026-10-30).
Step 1. Attach the checks to the table
Two checks are built-in functions: NULL_COUNT and DUPLICATE_COUNT. The third is a custom function that counts non-positive prices. Each one counts values that break the rule, so an expectation of VALUE = 0 means pass. DUPLICATE_COUNT returns the count of column values that have duplicates, including NULL values (read 2026-10-30).
CREATE OR REPLACE DATA METRIC FUNCTION RULE_PRICE_POSITIVE(arg_t TABLE(arg_c NUMBER))
RETURNS NUMBER AS
$$ SELECT COUNT(*) FROM arg_t WHERE arg_c IS NULL OR arg_c <= 0 $$;
ALTER TABLE ORDERS SET DATA_METRIC_SCHEDULE = '60 MINUTE';
ALTER TABLE ORDERS ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (O_CUSTKEY)
EXPECTATION no_null_customer (VALUE = 0);
ALTER TABLE ORDERS ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (O_ORDERKEY)
EXPECTATION no_duplicate_order (VALUE = 0);
ALTER TABLE ORDERS ADD DATA METRIC FUNCTION RULE_PRICE_POSITIVE ON (O_TOTALPRICE)
EXPECTATION price_positive (VALUE = 0);All data metric functions on a table share one schedule, one hour by default (Snowflake docs, read 2026-10-30). The alert in step 3 does not wait for that schedule: it evaluates the expectations itself each time it runs.
Step 2. Say who owns the table, then write the email procedure
The steward lives in a plain table: one row per table and steward, holding the table's fully qualified name and an email address. Two stewards for one table are two rows.
SQL: the steward table-- 4. Who owns which table. One row per table and steward: the fully qualified table name and
-- the steward's email address. Several stewards for one table are several rows.
CREATE TABLE IF NOT EXISTS DQ_STEWARDS (TABLE_NAME VARCHAR, STEWARD_EMAIL VARCHAR);
-- Register your own with a statement like this (one per table and steward):
-- INSERT INTO DQ_STEWARDS VALUES ('MY_DB.MY_SCHEMA.MY_TABLE', 'steward@example.com');Snowflake sends email through a notification integration. Its recipients must be verified users of the same Snowflake account, and if the integration sets ALLOWED_RECIPIENTS, one address that is not on the list blocks the whole send (Snowflake docs, Snowflake docs, read 2026-10-30). The procedure below calls SYSTEM$SEND_EMAIL, which takes the integration name, the recipients as a comma-separated list, a subject and a body (read 2026-10-30).
The procedure first asks SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS which expectations are violated right now. Snowflake says that function runs the data metric functions to decide whether each expectation is currently violated (read 2026-10-30). It then looks up the steward. If it finds none it raises an error, so a table with no steward shows up as a failed alert run instead of a silent no-email. The email names the failed checks and their counts, not the offending rows.
SQL: the procedureCREATE OR REPLACE PROCEDURE NOTIFY_STEWARD(TABLE_FQN VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
failed VARCHAR;
recipients VARCHAR;
no_steward EXCEPTION (-20001, 'No steward registered in DQ_STEWARDS for this table');
BEGIN
SELECT LISTAGG(EXPECTATION_NAME || ' = ' || VALUE, ', ') INTO :failed
FROM TABLE(SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS(REF_ENTITY_NAME => :TABLE_FQN))
WHERE EXPECTATION_VIOLATED = 'TRUE';
SELECT LISTAGG(STEWARD_EMAIL, ',') INTO :recipients
FROM DQ_STEWARDS WHERE UPPER(TABLE_NAME) = UPPER(:TABLE_FQN);
IF (COALESCE(:recipients, '') = '') THEN
RAISE no_steward;
END IF;
CALL SYSTEM$SEND_EMAIL('CONTENT_LOOP_EMAIL', :recipients,
'Data quality check failed on ' || :TABLE_FQN,
'These checks failed (value = number of rows or values that break the rule): '
|| COALESCE(:failed, 'none by the time this ran') || '. Table: ' || :TABLE_FQN);
RETURN 'emailed steward: ' || COALESCE(:failed, 'none');
END;
$$;Step 3. Create the alert
An alert is a schema-level object with a condition, an action and a schedule. A new alert starts suspended and has to be resumed, and the role that runs it needs the account-level EXECUTE ALERT privilege, which only ACCOUNTADMIN can grant (Snowflake docs, read 2026-10-30). This one uses a warehouse; Snowflake also offers serverless alerts on the same page.
The role in our test held USAGE on the email integration, EXECUTE ALERT and EXECUTE DATA METRIC FUNCTION on the account, and the SNOWFLAKE.DATA_METRIC_USER database role. The SQL file lists them at the top. Your role needs equivalent rights.
CREATE OR REPLACE ALERT ORDERS_DQ_EMAIL_ALERT
WAREHOUSE = CONTENT_LOOP_WH
SCHEDULE = '60 MINUTE'
IF (EXISTS (
SELECT 1
FROM TABLE(SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS(
REF_ENTITY_NAME => 'CONTENT_LOOP_SANDBOX.SNOWFLAKE_DATA_QUALITY_ALERT_EMAIL.ORDERS'))
WHERE EXPECTATION_VIOLATED = 'TRUE'))
THEN CALL NOTIFY_STEWARD('CONTENT_LOOP_SANDBOX.SNOWFLAKE_DATA_QUALITY_ALERT_EMAIL.ORDERS');
ALTER ALERT ORDERS_DQ_EMAIL_ALERT RESUME;Step 4. Run it against planted errors, then against clean data
The test table is 1,000 orders copied from the TPC-H sample data. We planted four bad rows: one with a NULL customer, one with a negative price, and two copies of the same order key. DUPLICATE_COUNT reports 1 for that, because one key value is duplicated. Then we ran the alert at once with EXECUTE ALERT, which triggers a single run outside its schedule (Snowflake docs, read 2026-10-30), waited 45 seconds, and read the run from ALERT_HISTORY.
EXPECTATION_NAME | VALUE | EXPECTATION_VIOLATED -------------------+-------+--------------------- NO_DUPLICATE_ORDER | 1 | TRUE NO_NULL_CUSTOMER | 1 | TRUE PRICE_POSITIVE | 1 | TRUE
In ALERT_HISTORY, TRIGGERED means the condition was evaluated successfully and the action ran successfully (read 2026-10-30):
NAME | STATE | SQL_ERROR_MESSAGE ----------------------+-----------+------------------ ORDERS_DQ_EMAIL_ALERT | TRIGGERED | NULL
Then we deleted the four planted rows and ran the same alert again.
Output from our run 2, clean data: nothing violatedEXPECTATION_NAME | VALUE | EXPECTATION_VIOLATED -------------------+-------+--------------------- NO_DUPLICATE_ORDER | 0 | FALSE NO_NULL_CUSTOMER | 0 | FALSE PRICE_POSITIVE | 0 | FALSE
For the clean run, CONDITION_FALSE means the condition returned no data, so the action did not run and no email was attempted (Snowflake docs, read 2026-10-30).
NAME | STATE | SQL_ERROR_MESSAGE ----------------------+-----------------+------------------ ORDERS_DQ_EMAIL_ALERT | CONDITION_FALSE | NULL
What this proves, and what it does not: TRIGGERED is Snowflake's report that the action ran successfully, so the procedure, including its email call, raised no error. We did not read the email or check an inbox. In our run the one registered steward was the only address the sandbox's integration allowed.
Tested on
Tested on
- Platform
- Snowflake
- Edition
- Enterprise Edition
- Date
- 2026-10-30
- Dataset
- SNOWFLAKE_SAMPLE_DATA, TPCH_SF1 (
ORDERS, limited to 1,000 rows) - Code
- email_steward_on_dq_failure.sql
Run it on your own table
The step numbers in this section are the numbers in the SQL file's comments, not the headings on this page.
To run this on your own table, delete every step the file marks TEST ONLY (numbered 0, 2, 7, 8, 9 and 10). Keep step 1 and the USE DATABASE line, and change them to the database and schema where your table lives. In step 3, replace the three attach statements with your own table, columns and rules, each with an expectation (VALUE = 0), and rename or rewrite RULE_PRICE_POSITIVE to your own rule. If your table already has a data metric schedule, delete the SET DATA_METRIC_SCHEDULE line: a table has one schedule for all its data metric functions, so that line would change when the checks already on it run, and the alert does not need it. In step 4, run the INSERT shown in the comment once for each table and steward, with the table's fully qualified name and the steward's email address; that address must be in the ALLOWED_RECIPIENTS of your email integration. In steps 5 and 6, replace CONTENT_LOOP_EMAIL with your integration's name, CONTENT_LOOP_WH with your warehouse, and the fully qualified table name with your own.
The alert in step 6 watches one table. For each further table, attach its checks as in step 3, register its steward as in step 4, then run step 6 again with a new alert name in both the CREATE ALERT and ALTER ALERT ... RESUME lines, and that table's fully qualified name in both the condition and the CALL. Keeping the name ORDERS_DQ_EMAIL_ALERT would replace the first table's alert without a warning, and its steward would stop getting email.
We ran these instructions on a different schema in the same sandbox, with a different table and different column names, and one planted error per check. We registered the steward's table name in lower case (the lookup ignores case) and read the address from the integration instead of typing it, because the sandbox's integration allows only one recipient. The alert found all three planted errors and reached TRIGGERED. We also ran a second table that had a failing check but only a NULL steward address through the procedure: it raised the "No steward registered" error and sent nothing. That run found a defect in our first draft: with no matching steward, the lookup returned an empty string rather than NULL, so the guard never fired. The file now checks for both.
What it costs to run
Two things run on a schedule. Snowflake says data metric functions use serverless compute that is billed when a scheduled function runs (Snowflake docs, read 2026-10-30). A warehouse alert uses the warehouse you name (Snowflake docs, read 2026-10-30). Each alert run also evaluates the checks itself, once in the condition and once more in the procedure when it fires. We did not measure either cost, so check them on your own account before pointing this at large tables.
When this is not enough
- It repeats. The alert runs every schedule period, so while a check keeps failing the steward gets an email each time. Our file does not suppress repeats; add a table of what you already sent if that matters.
- The steward must be a Snowflake user. Email goes only to verified users in the same account (Snowflake docs, read 2026-10-30). A business owner without a login needs another route.
- It says which check failed, not which rows. The email carries counts. The steward still has to look for the rows.
- It only covers checks written in Snowflake. Rules that live in an ETL tool or a BI tool are not seen by this alert.
- The email is a message, not a ticket. Nothing here assigns, tracks or closes the work.
- Email content passes through Snowflake's email service. Snowflake says it may keep message content for up to thirty days (Snowflake docs, read 2026-10-30), so keep sensitive values out of the body. Ours carries only check names and counts.
Want these checks written and running in your Snowflake account?
This guide covers the alert. Native Data Quality runs your approved data quality rules inside Snowflake or Databricks for a fixed fee, and notifies the steward who owns a rule when it fails.