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
- Filter event tables by
RECORD_TYPE:LOG,METRIC,SPAN,SPAN_EVENT,EVENT. - Use
LOGIN_HISTORY,ACCESS_HISTORYandQUERY_HISTORYfor 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.
How it works
Event table columns worth knowing
| Column | Content |
|---|---|
TIMESTAMP | When the record was created |
START_TIMESTAMP | Start of a span, for traces |
RECORD_TYPE | LOG, METRIC, SPAN, SPAN_EVENT or EVENT |
RECORD | Details such as severity or span name |
VALUE | The message or metric value |
TRACE | Trace ID and span ID |
RESOURCE_ATTRIBUTES | Origin of the record, such as database, schema, function or user |
SCOPE | Logger 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
| Question | Source |
|---|---|
| Who logged in, from where, and who failed | ACCOUNT_USAGE.LOGIN_HISTORY |
| Which queries a user ran | ACCOUNT_USAGE.QUERY_HISTORY |
| Which objects and columns were read | ACCOUNT_USAGE.ACCESS_HISTORY |
| Who holds which roles and privileges | GRANTS_TO_USERS, GRANTS_TO_ROLES |
| What custom code logged about user actions | Event 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 ALERTprivilege, 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.
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.
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_HISTORYandQUERY_HISTORYfor 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
TIMESTAMPminusSTART_TIMESTAMP; trace ID ties spans together. - Traces find the slow or failing step inside code.
Check yourself
Which
RECORD_TYPEholds trace data?SPAN, withSPAN_EVENTfor 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.