Performance Optimization · Topic 7 of 7
Dynamic Table Monitoring, Alerts, Notifications and Data Quality
Monitor dynamic table refreshes, build Snowflake alerts, send email and webhook notifications, and track data quality with data metric functions.
- 5 min
- Dynamic table health:
SHOW DYNAMIC TABLES, refresh history (7 days in Information Schema, 365 inACCOUNT_USAGE), statesSUCCEEDED,FAILED,UPSTREAM_FAILED. - Alerts:
SCHEDULE,IF (EXISTS ...),THENaction, created suspended, serverless or warehouse compute,EXECUTE ALERTprivilege. - Notifications:
EMAILandWEBHOOKintegrations; queue integrations for task and pipe errors.
What it is
Once a pipeline runs, you need it to tell you when something is wrong. Snowflake provides monitoring surfaces for dynamic tables, alerts that run a condition on a schedule, notification integrations that deliver messages, and data metric functions (DMFs) that measure data quality.
How it works
Monitoring dynamic tables
| Surface | Shows | Retention |
|---|---|---|
SHOW DYNAMIC TABLES | Current refresh mode, warehouse, scheduling state | Snapshot |
INFORMATION_SCHEMA.DYNAMIC_TABLES() | Scheduling state, last refresh, target lag, lag metrics | 7 days |
INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY() | One row per refresh: state, error, refresh action, timestamps | 7 days |
ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY | Same history for trends | 365 days |
INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY() | Dependency graph and property changes | 7 days |
| Snowsight (Transformation, then Dynamic tables) | Lineage graph, status, lag, refresh history |
Key fields:
- Scheduling state:
RUNNINGorSUSPENDED. - Refresh state:
SUCCEEDED,FAILEDorUPSTREAM_FAILED(a dependency failed, so this table was skipped). - Refresh action: for example
INCREMENTAL,FULLorNO_DATA(nothing changed upstream). - Lag:
TARGET_LAG_SEC,MAXIMUM_LAG_SECandTIME_WITHIN_TARGET_LAG_RATIO. A ratio well below 1 means the table often misses its freshness target.
SELECT name, state, state_message, refresh_action, data_timestamp
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(ERROR_ONLY => TRUE))
ORDER BY data_timestamp DESC;
If lag is missed, look at the refresh history for FULL refreshes that should be incremental, long refresh durations (size the dynamic table's warehouse up), or an upstream failure. To get events and alerts for dynamic tables, set LOG_EVENT_LEVEL at the account, database, schema or table level; without it no events are recorded, and event logging itself incurs cost.
Alerts
An alert checks a condition and runs an action when the condition returns rows.
CREATE ALERT late_orders_alert
WAREHOUSE = alert_wh
SCHEDULE = '15 MINUTE'
IF (EXISTS (
SELECT COUNT(*) AS n FROM orders_dt
WHERE order_date = CURRENT_DATE() HAVING COUNT(*) = 0))
THEN CALL SYSTEM$SEND_EMAIL(
'my_email_int', '[email protected]',
'No orders today', 'The orders dynamic table has no rows for today.');
ALTER ALERT late_orders_alert RESUME;
- Schedule: a fixed interval or a cron expression. Alerts on new data have no schedule and run when new rows arrive, with limits such as one source table and change tracking enabled.
- Condition: normally
IF (EXISTS (SELECT ...)); the alert fires when the query returns at least one row. - Action: one SQL statement or a
BEGIN ... ENDblock, such as an INSERT into a log table or aCALLthat sends a message. - Compute: name a
WAREHOUSE, or omit it to use serverless compute. - New alerts are suspended. Run
ALTER ALERT ... RESUME. - Privileges:
EXECUTE ALERTon the account (granted byACCOUNTADMIN), plusEXECUTE MANAGED ALERTfor serverless alerts, andCREATE ALERTon the schema. - History:
INFORMATION_SCHEMA.ALERT_HISTORY()andACCOUNT_USAGE.ALERT_HISTORY. Serverless alert credits appear inSERVERLESS_ALERT_HISTORY.
A task runs work on a schedule. An alert only checks a condition and acts when it is true. For "tell me if something is wrong", use an alert.
Notifications
Snowflake has several notification paths.
| Mechanism | Used for |
|---|---|
Notification integration TYPE = EMAIL with SYSTEM$SEND_EMAIL | Email to verified recipients on the allowed list |
Notification integration TYPE = WEBHOOK with SYSTEM$SEND_SNOWFLAKE_NOTIFICATION | Messages to Slack, Microsoft Teams, PagerDuty and similar |
| Queue integration (Amazon SNS, Azure Event Grid, Google Pub/Sub) | Task and pipe error notifications via ERROR_INTEGRATION |
| Resource monitor | Email at credit thresholds |
CREATE NOTIFICATION INTEGRATION my_email_int
TYPE = EMAIL
ENABLED = TRUE
ALLOWED_RECIPIENTS = ('[email protected]');
- Email recipients must be on the integration's
ALLOWED_RECIPIENTSlist and be users with a verified email address in the account. - Queue based notifications for tasks use the cloud service of the platform hosting your account, and delivery is at least once, so duplicates are possible.
NOTIFICATION_HISTORYis a table function that returns notification history.
Data quality and data metric functions
A data metric function measures a property of a table or view, such as null counts or duplicates. Snowflake provides system DMFs in the SNOWFLAKE.CORE schema, including NULL_COUNT, ROW_COUNT, DUPLICATE_COUNT, BLANK_COUNT, ACCEPTED_VALUES, UNIQUE_COUNT and FRESHNESS. You can also write custom DMFs in SQL. Data quality monitoring needs Enterprise Edition.
ALTER TABLE customers SET DATA_METRIC_SCHEDULE = '60 MINUTE';
ALTER TABLE customers
ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (email);
- Schedule: set per table with
DATA_METRIC_SCHEDULE: an interval such as'5 MINUTE', a cron expression, or'TRIGGER_ON_CHANGES'to run on data changes. The default is once an hour. - Results: stored and queried through the
DATA_QUALITY_MONITORING_RESULTSview and shown in Snowsight. - Expectations: attach a pass condition to a DMF, for example
EXPECTATION no_null_email (VALUE = 0). Results that break it are reported as expectation violations. - Compute: DMFs run on serverless compute and are billed only when a scheduled DMF runs. Usage appears in
DATA_QUALITY_MONITORING_USAGE_HISTORY. - Limits: DMFs cannot be set on streams, hybrid tables or shared tables and views.
- Anomaly detection can flag unusual volume or freshness values from history.
A DMF only measures. To be told about bad data, combine it with an alert or review the results, because nothing is sent automatically.
Common mistakes
Expecting a new alert to run. Alerts are created suspended; resume them.
Reading UPSTREAM_FAILED as a bug in that table. The failure is in a dependency; fix the upstream table.
Sending alert email to any address. Recipients must be allowed by the integration and verified in the account.
Forgetting event logging. Without LOG_EVENT_LEVEL, no events are recorded for dynamic tables or tasks.
What to remember for the exam
- Dynamic table health:
SHOW DYNAMIC TABLES, refresh history (7 days in Information Schema, 365 inACCOUNT_USAGE), statesSUCCEEDED,FAILED,UPSTREAM_FAILED. - Alerts:
SCHEDULE,IF (EXISTS ...),THENaction, created suspended, serverless or warehouse compute,EXECUTE ALERTprivilege. - Notifications:
EMAILandWEBHOOKintegrations; queue integrations for task and pipe errors. - DMFs:
SNOWFLAKE.COREsystem functions,DATA_METRIC_SCHEDULE(default hourly), expectations, serverless billing, Enterprise.
Check yourself
What does the UPSTREAM_FAILED refresh state mean?
A table the dynamic table depends on failed, so this refresh was skipped.
What must you do after creating an alert before it runs?
Resume it with ALTER ALERT ... RESUME, because alerts are created suspended.
How can a data metric function check a column for nulls and report a violation?
Attach SNOWFLAKE.CORE.NULL_COUNT to the column on a schedule and add an expectation such as VALUE = 0.
Topic 7 of 7 in Performance Optimization
Test yourself on Performance Optimization
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.