Performance Optimization · Topic 9 of 9

Alerts, Notifications and Event Tables

Automate monitoring with alerts and notification integrations, send email with SYSTEM$SEND_EMAIL, and capture logs and traces in event tables.

  • 5 min
In a nutshell
  • Alert: SCHEDULE, IF condition, THEN action; created suspended; RESUME to start; history in ALERT_HISTORY.
  • SYSTEM$SEND_EMAIL needs an email notification integration and allowed, verified recipients.
  • One active event table per account (default SNOWFLAKE.TELEMETRY.EVENTS), set with ALTER ACCOUNT SET EVENT_TABLE; a database level event table takes precedence for that database.

What it is

An alert is a schedulable object that checks a condition with SQL and runs an action when it is true, such as sending an email. Notifications are how that message leaves Snowflake: email, a webhook or a cloud queue, through a notification integration. An event table is a special table that collects the logs, traces and metrics emitted by your code, so you can find errors inside functions, procedures and apps.

Scheduleevery few minutesrunsAlert conditionSQL returns rows?trueActionsend email or call proceduredelivered viaNotification integration

How it works

Alerts

CREATE OR REPLACE ALERT slow_query_alert
  WAREHOUSE = monitor_wh
  SCHEDULE = '15 MINUTE'
  IF (EXISTS (
    SELECT 1
    FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
      END_TIME_RANGE_START => DATEADD('minute', -15, CURRENT_TIMESTAMP())))
    WHERE total_elapsed_time > 600000))
  THEN CALL SYSTEM$SEND_EMAIL(
    'ops_email_int', '[email protected]',
    'Slow queries', 'A query ran over 10 minutes.');

ALTER ALERT slow_query_alert RESUME;
  • The condition is a SELECT, SHOW or CALL; the alert fires when it returns rows (with EXISTS, when it is true).
  • The action is one SQL statement, such as CALL SYSTEM$SEND_EMAIL(...), an INSERT into a log table, or a call to a stored procedure.
  • SCHEDULE is an interval in minutes or a CRON expression with a time zone.
  • Alerts are created suspended. ALTER ALERT ... RESUME starts them.
  • Compute comes from a named warehouse, or from serverless compute if you omit WAREHOUSE.
  • Write conditions to look only at the period since the last run, or the same event can fire repeatedly.
  • The history of runs is in ALERT_HISTORY, and EXECUTE ALERT runs one on demand.
  • The owner role needs EXECUTE ALERT on the account and the privileges for the condition and action.

Notification integrations and sending email

CREATE NOTIFICATION INTEGRATION ops_email_int
  TYPE = EMAIL
  ENABLED = TRUE
  ALLOWED_RECIPIENTS = ('[email protected]');
ItemRule
SYSTEM$SEND_EMAILSends a message through an email notification integration
RecipientsMust be in ALLOWED_RECIPIENTS and be verified email addresses of Snowflake users
Other channelsNotification integrations also support cloud queues and webhooks
Task failuresSet ERROR_INTEGRATION on a task to send failure messages
PipesSnowpipe error notifications go through a notification integration to a cloud queue
Resource monitorsNOTIFY triggers email account administrators who enabled notifications
Exam tip

Think of three alert routes. Credit thresholds use resource monitors, task and pipe failures use error integrations, and anything else you can query uses an alert.

Event tables

An event table has a fixed structure and stores telemetry from your code. One event table is active for the account, and a database can have its own event table, which takes precedence over the account's for that database.

CREATE EVENT TABLE my_db.telemetry.events;
ALTER ACCOUNT SET EVENT_TABLE = my_db.telemetry.events;
ALTER SESSION SET LOG_LEVEL = INFO;

Snowflake provides a default event table, SNOWFLAKE.TELEMETRY.EVENTS, which is active until you set your own.

ColumnContent
TIMESTAMPWhen the event happened
RECORD_TYPELOG, SPAN, SPAN_EVENT, EVENT or METRIC
RECORDAttributes of the record, such as severity
VALUEThe log message or metric value
RESOURCE_ATTRIBUTESWhich object produced it: database, schema, function, warehouse, query ID
SCOPEScope such as a logger name

Logging, tracing and metrics.

SignalHow it is producedControl
LogsHandler code calls a logging library, such as Python logging, or Snowflake Scripting SYSTEM$LOGLOG_LEVEL
TracesSpans created by the trace API, and automatically for some eventsTRACE_LEVEL
MetricsCollected for functions and proceduresMETRIC_LEVEL

Levels can be set on the account, database, schema or the individual function or procedure, and the most specific object setting wins; when both the session and the object set a level, the more verbose one is used. Logging stays off until a level is set.

Handler codeUDF, procedure, appemitsTelemetrylogs, spans, metricsstored inEvent tableaccount or databasequeried bySQL and alerts

Query the table with normal SQL, with filters on RECORD_TYPE and RESOURCE_ATTRIBUTES. Because event tables are often large, set a retention policy by deleting old rows, filter by time first, and consider an alert that watches for ERROR level records.

Choosing the right observability tool

NeedTool
Errors inside a UDF, procedure or appEvent table with logging
Slow query across the accountQUERY_HISTORY
Credit capResource monitor
Notification when a SQL condition is trueAlert
Failed taskTask ERROR_INTEGRATION

Common mistakes

Forgetting to resume an alert. Alerts are created suspended, like tasks.

Emailing an address that is not allowed or verified. SYSTEM$SEND_EMAIL fails.

Expecting logs without a log level. Logging is off until a level is set.

Treating event tables as a metadata view. They hold code telemetry, not query or usage history.

What to remember for the exam

  • Alert: SCHEDULE, IF condition, THEN action; created suspended; RESUME to start; history in ALERT_HISTORY.
  • SYSTEM$SEND_EMAIL needs an email notification integration and allowed, verified recipients.
  • One active event table per account (default SNOWFLAKE.TELEMETRY.EVENTS), set with ALTER ACCOUNT SET EVENT_TABLE; a database level event table takes precedence for that database.
  • Control telemetry with LOG_LEVEL, TRACE_LEVEL and METRIC_LEVEL.
  • Event tables capture logs, traces and metrics from handler code, not account usage.

Check yourself

  • You need an email when a query runs over 10 minutes. What do you create?

    An alert with a condition on query history and a SYSTEM$SEND_EMAIL action through an email notification integration.

  • A new alert never fires. What did you likely miss?

    Resuming it with ALTER ALERT ... RESUME, since alerts are created suspended.

  • Where do you find error messages logged by a Python UDF?

    In the account's active event table, with the log level set so the messages are recorded.

Topic 9 of 9 in Performance Optimization

Test yourself on Performance Optimization

The full Snowflake ARA-C01 question bank has 687 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.