Performance Monitoring and Tuning · Topic 10 of 16

Analyzing Event Tables - Health, Security Monitoring, Alerts and Trace Analysis

Query event tables for system health, monitor user activity and access with log data, create alerts, and use trace data for root cause analysis and tuning.

  • 4 min
In a nutshell
  • Filter event tables by RECORD_TYPE: LOG, METRIC, SPAN, SPAN_EVENT, EVENT.
  • Use LOGIN_HISTORY, ACCESS_HISTORY and QUERY_HISTORY for user activity and threat detection.
  • Alerts run a condition on a schedule, start suspended, and can send email or call a procedure.

What it is

Once telemetry lands in an event table, you query it like any table. The administrator uses it to check the health of procedures and functions, to watch for suspicious activity, to raise alerts when a condition appears, and to find where time goes inside code using trace spans.

Event table logs cover what runs inside your handlers. For activity at the account level, such as who logged in or which data was read, the ACCOUNT_USAGE views complete the picture.

Event tablelogs, metrics, spansqueried bySQLfilter by RECORD_TYPEdrivesAlertcondition and actionnotifiesAdminemail or procedure

How it works

Event table columns worth knowing

ColumnContent
TIMESTAMPWhen the record was created
START_TIMESTAMPStart of a span, for traces
RECORD_TYPELOG, METRIC, SPAN, SPAN_EVENT or EVENT
RECORDDetails such as severity or span name
VALUEThe message or metric value
TRACETrace ID and span ID
RESOURCE_ATTRIBUTESOrigin of the record, such as database, schema, function or user
SCOPELogger or scope that emitted it

Analyze for system health

Count errors by object over time to see which procedures or functions are unhealthy.

SELECT RESOURCE_ATTRIBUTES:"snow.executable.name"::STRING AS object_name,
       COUNT(*) AS error_count
FROM my_db.logs.app_events
WHERE RECORD_TYPE = 'LOG'
  AND RECORD:severity_text IN ('ERROR', 'FATAL')
  AND TIMESTAMP > DATEADD('hour', -24, CURRENT_TIMESTAMP())
GROUP BY object_name
ORDER BY error_count DESC;

A rising count of errors, or a metric such as memory use approaching a limit, is a health indicator. Check the log level first: a quiet table may only mean low levels.

Log data for user activity, threat detection and access control

QuestionSource
Who logged in, from where, and who failedACCOUNT_USAGE.LOGIN_HISTORY
Which queries a user ranACCOUNT_USAGE.QUERY_HISTORY
Which objects and columns were readACCOUNT_USAGE.ACCESS_HISTORY
Who holds which roles and privilegesGRANTS_TO_USERS, GRANTS_TO_ROLES
What custom code logged about user actionsEvent table logs

Patterns that suggest a threat are repeated failed logins from one client IP, logins from unexpected locations, sudden large reads from a sensitive table, and new grants of powerful roles. Trust Center scanners also report on security posture.

SELECT user_name, client_ip, COUNT(*) AS failures
FROM snowflake.account_usage.login_history
WHERE is_success = 'NO'
  AND event_timestamp > DATEADD('day', -1, CURRENT_TIMESTAMP())
GROUP BY user_name, client_ip
HAVING COUNT(*) > 5;

Create alerts

An alert runs a condition on a schedule and performs an action when the condition returns rows. It fits log monitoring well.

CREATE ALERT error_spike
  WAREHOUSE = admin_wh
  SCHEDULE = '15 MINUTE'
  IF (EXISTS (
    SELECT 1 FROM my_db.logs.app_events
    WHERE RECORD_TYPE = 'LOG'
      AND RECORD:severity_text = 'ERROR'
      AND TIMESTAMP > SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME()))
  THEN CALL SYSTEM$SEND_EMAIL('my_email_int', '[email protected]',
                              'Errors logged', 'New errors in app_events');
ALTER ALERT error_spike RESUME;

Key points:

  • A new alert is created suspended, so run ALTER ALERT ... RESUME.
  • The role needs the EXECUTE ALERT privilege, and the owner needs privileges on the objects the condition reads.
  • The action can send email through a notification integration, call a stored procedure or insert into a table.
  • Using LAST_SUCCESSFUL_SCHEDULED_TIME() in the condition avoids counting the same rows again on each run.
Exam tip

Alerts check on a schedule, so they are not instant. Choose the interval to match how quickly you must react.

Trace data for root cause analysis

A trace is a tree of spans, one per unit of work, linked by trace ID and span ID. Spans have a start and an end, so duration is the difference between TIMESTAMP and START_TIMESTAMP. Capture needs TRACE_LEVEL set to ALWAYS or ON_EVENT.

For a failing or slow request, filter by its trace ID and read the spans in order. The span with an error event, or the first span that stops, shows where it broke.

SELECT RECORD:name::STRING AS span_name,
       DATEDIFF('millisecond', START_TIMESTAMP, TIMESTAMP) AS duration_ms
FROM my_db.logs.app_events
WHERE RECORD_TYPE = 'SPAN'
  AND TRACE:trace_id::STRING = '<trace_id>'
ORDER BY START_TIMESTAMP;

Trace data to optimize performance

Aggregate span durations across many traces to find the slowest steps.

  • Group spans by name and compare average and maximum duration.
  • Spot calls that repeat many times in one trace, such as a query in a loop.
  • Compare before and after a code change to confirm the gain.
Slow requesttrace IDfilterSpansname, start, durationrankSlowest spanroot causechange codeRe checkcompare durations

Common mistakes

Expecting event tables to hold login history. Account level activity lives in ACCOUNT_USAGE views.

Forgetting to resume an alert. It stays suspended after creation.

Analyzing traces without enabling them. TRACE_LEVEL defaults to OFF.

Alerting on all rows each run. Use the last successful scheduled time to look only at new data.

What to remember for the exam

  • Filter event tables by RECORD_TYPE: LOG, METRIC, SPAN, SPAN_EVENT, EVENT.
  • Use LOGIN_HISTORY, ACCESS_HISTORY and QUERY_HISTORY for user activity and threat detection.
  • Alerts run a condition on a schedule, start suspended, and can send email or call a procedure.
  • Span duration is TIMESTAMP minus START_TIMESTAMP; trace ID ties spans together.
  • Traces find the slow or failing step inside code.

Check yourself

  • Which RECORD_TYPE holds trace data?

    SPAN, with SPAN_EVENT for events inside a span.

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

    It is created suspended, so it needs ALTER ALERT ... RESUME.

  • Which view shows repeated failed logins by client IP?

    SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY.

Topic 10 of 16 in Performance Monitoring and Tuning

Test yourself on Performance Monitoring and Tuning

The full Snowflake ADA-C02 question bank has 814 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.