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
In a nutshell
  • Dynamic table health: SHOW DYNAMIC TABLES, refresh history (7 days in Information Schema, 365 in ACCOUNT_USAGE), states SUCCEEDED, FAILED, UPSTREAM_FAILED.
  • Alerts: SCHEDULE, IF (EXISTS ...), THEN action, created suspended, serverless or warehouse compute, EXECUTE ALERT privilege.
  • Notifications: EMAIL and WEBHOOK integrations; 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.

Dynamic table refresh historyfeedsAlertcondition queryfiresNotificationemail or webhookreachesEngineerTablescheduled DMFQuality resultschecked byAlert

How it works

Monitoring dynamic tables

SurfaceShowsRetention
SHOW DYNAMIC TABLESCurrent refresh mode, warehouse, scheduling stateSnapshot
INFORMATION_SCHEMA.DYNAMIC_TABLES()Scheduling state, last refresh, target lag, lag metrics7 days
INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY()One row per refresh: state, error, refresh action, timestamps7 days
ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORYSame history for trends365 days
INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY()Dependency graph and property changes7 days
Snowsight (Transformation, then Dynamic tables)Lineage graph, status, lag, refresh history

Key fields:

  • Scheduling state: RUNNING or SUSPENDED.
  • Refresh state: SUCCEEDED, FAILED or UPSTREAM_FAILED (a dependency failed, so this table was skipped).
  • Refresh action: for example INCREMENTAL, FULL or NO_DATA (nothing changed upstream).
  • Lag: TARGET_LAG_SEC, MAXIMUM_LAG_SEC and TIME_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 ... END block, such as an INSERT into a log table or a CALL that sends a message.
  • Compute: name a WAREHOUSE, or omit it to use serverless compute.
  • New alerts are suspended. Run ALTER ALERT ... RESUME.
  • Privileges: EXECUTE ALERT on the account (granted by ACCOUNTADMIN), plus EXECUTE MANAGED ALERT for serverless alerts, and CREATE ALERT on the schema.
  • History: INFORMATION_SCHEMA.ALERT_HISTORY() and ACCOUNT_USAGE.ALERT_HISTORY. Serverless alert credits appear in SERVERLESS_ALERT_HISTORY.
Exam tip

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.

MechanismUsed for
Notification integration TYPE = EMAIL with SYSTEM$SEND_EMAILEmail to verified recipients on the allowed list
Notification integration TYPE = WEBHOOK with SYSTEM$SEND_SNOWFLAKE_NOTIFICATIONMessages 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 monitorEmail 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_RECIPIENTS list 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_HISTORY is 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_RESULTS view 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.
Tablewith DMF attachedscheduledDMF runserverlesswritesResults viewexpectation failsAlertsendsNotification
Watch out

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 in ACCOUNT_USAGE), states SUCCEEDED, FAILED, UPSTREAM_FAILED.
  • Alerts: SCHEDULE, IF (EXISTS ...), THEN action, created suspended, serverless or warehouse compute, EXECUTE ALERT privilege.
  • Notifications: EMAIL and WEBHOOK integrations; queue integrations for task and pipe errors.
  • DMFs: SNOWFLAKE.CORE system 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.