Snowflake ACCOUNT_USAGE ops cheat sheet: failed queries, long runners, failed logins, locks and grant changes

#Before you start

Most of the queries below read the shared SNOWFLAKE database. Give your on-call role access once:

USE ROLE ACCOUNTADMIN;
-- full access to ACCOUNT_USAGE
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE ops_monitor;
-- or narrower, using the built-in SNOWFLAKE database roles
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER      TO ROLE ops_monitor;
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE ops_monitor;
GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER   TO ROLE ops_monitor;

Pick the right source for the question:

Tip

If an incident started less than an hour ago and ACCOUNT_USAGE shows nothing, the data probably hasn't arrived yet. Use the INFORMATION_SCHEMA versions. The latency figures are maximums; check the current values in the Account Usage docs.

#Failed queries

#Errors in the last 30 minutes (real time)

SELECT query_id, user_name, warehouse_name, error_code, error_message, start_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
       END_TIME_RANGE_START => DATEADD('minute', -30, CURRENT_TIMESTAMP()),
       RESULT_LIMIT => 10000))
WHERE execution_status = 'FAILED_WITH_ERROR'
ORDER BY start_time DESC;

The table function only returns queries that your current role can see, and it uses different status values from the view (for example FAILED_WITH_ERROR instead of FAIL; names vary, so check your output). For one user, use QUERY_HISTORY_BY_USER(USER_NAME => 'SVC_ETL'), and for one warehouse, QUERY_HISTORY_BY_WAREHOUSE(WAREHOUSE_NAME => 'ETL_WH').

#Top error messages today (account-wide)

SELECT error_code, LEFT(error_message, 120) AS error, user_name, COUNT(*) AS failures
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('hour', -24, CURRENT_TIMESTAMP())
  AND execution_status = 'FAIL'
GROUP BY 1, 2, 3
ORDER BY failures DESC
LIMIT 25;

A sudden spike from one service user usually means a deploy, an expired credential or a dropped object. Common codes: 002003 (object does not exist or not authorized), 000630 (statement timeout), 000604 (query cancelled).

#Find a query by its tag or text

SELECT query_id, user_name, execution_status, total_elapsed_time/1000 AS secs, query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -2, CURRENT_TIMESTAMP())
  AND (query_tag ILIKE '%dbt%orders%' OR query_text ILIKE '%fct_orders%')
ORDER BY start_time DESC
LIMIT 50;

#Slow and long-running queries

#What is running now and for how long

SELECT query_id, user_name, warehouse_name, execution_status,
       DATEDIFF('second', start_time, CURRENT_TIMESTAMP()) AS running_secs,
       LEFT(query_text, 100) AS query
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT => 10000))
WHERE execution_status IN ('RUNNING', 'QUEUED', 'BLOCKED')
ORDER BY running_secs DESC;

SHOW WAREHOUSES;   -- "running" and "queued" columns per warehouse

#Slowest queries yesterday, with the reason

SELECT query_id, warehouse_name, warehouse_size,
       total_elapsed_time/1000              AS total_s,
       queued_overload_time/1000            AS queued_s,
       transaction_blocked_time/1000        AS blocked_s,
       bytes_spilled_to_remote_storage/1e9  AS remote_spill_gb,
       partitions_scanned, partitions_total
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
ORDER BY total_elapsed_time DESC
LIMIT 20;

#Stop a runaway query

SELECT SYSTEM$CANCEL_QUERY('01b2c3d4-0000-1234-0000-000123456789');
ALTER WAREHOUSE adhoc_wh ABORT ALL QUERIES;   -- every running and queued query on that warehouse

Warning

ABORT ALL QUERIES also kills ETL and dashboard queries on that warehouse. Use it only on a warehouse that serves a single workload, and tell the owners first.

#Failed logins

#Who is failing to log in, and why

SELECT user_name, client_ip, reported_client_type, first_authentication_factor,
       error_code, error_message, COUNT(*) AS attempts, MAX(event_timestamp) AS last_seen
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE event_timestamp >= DATEADD('hour', -24, CURRENT_TIMESTAMP())
  AND is_success = 'NO'
GROUP BY ALL
ORDER BY attempts DESC;

For the last few minutes, use the table function:

SELECT * FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY_BY_USER(
  USER_NAME => 'SVC_ETL', RESULT_LIMIT => 100))
ORDER BY event_timestamp DESC;

Quick reading:

• INCORRECT_USERNAME_PASSWORD from one service user after a rotation: a client still has the old secret.
• JWT_TOKEN_INVALID* errors: key-pair mismatch or clock skew on the client.
• Many failures from one unfamiliar IP: treat it as a security event, and check the network policy.
• USER_LOCKED_TEMP: too many failures. To unlock early, run ALTER USER svc_etl SET MINS_TO_UNLOCK = 0;.

#Locks and blocked transactions

#Live view

SHOW LOCKS IN ACCOUNT;          -- resource, transaction, status (HOLDING / WAITING)
SHOW TRANSACTIONS IN ACCOUNT;   -- id, user, session, started_on, state

Kill the blocker after you confirm with its owner:

SELECT SYSTEM$ABORT_TRANSACTION(1721234567890000000);   -- transaction id from SHOW TRANSACTIONS

Caution

Aborting a transaction rolls back all of its work. For a long MERGE or a multi-statement load, the job must be rerun, and it may need cleanup if it isn't idempotent.

#Who blocked whom earlier

SELECT object_name, lock_type, requested_at, acquired_at,
       DATEDIFF('second', requested_at, acquired_at) AS waited_s,
       query_id, blocker_queries
FROM SNOWFLAKE.ACCOUNT_USAGE.LOCK_WAIT_HISTORY
WHERE requested_at >= DATEADD('day', -1, CURRENT_TIMESTAMP())
ORDER BY waited_s DESC NULLS FIRST
LIMIT 20;

The usual pattern is two pipelines running MERGE/UPDATE/DELETE on the same table at the same time. Schedule them in sequence, or set LOCK_TIMEOUT on the session so the waiting query fails fast instead of hanging.

#Who changed access

#Grants and revokes in the last week

SELECT start_time, user_name, role_name, query_type, query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND query_type IN ('GRANT', 'REVOKE', 'ALTER_USER', 'CREATE_USER', 'DROP_USER')
  AND execution_status = 'SUCCESS'
ORDER BY start_time DESC;

#Current and revoked privileges on one table

SELECT privilege, grantee_name, granted_by, created_on, deleted_on
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE table_catalog = 'SALES' AND name = 'ORDERS' AND granted_on = 'TABLE'
ORDER BY COALESCE(deleted_on, created_on) DESC;

A non-null deleted_on means the grant was revoked. It's often the cause when a job starts failing with error 002003.

#Who read a sensitive table (Enterprise Edition)

SELECT ah.query_start_time, ah.user_name, obj.value:"objectName"::string AS object_name
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
     LATERAL FLATTEN(input => ah.base_objects_accessed) obj
WHERE ah.query_start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND obj.value:"objectName"::string = 'SALES.PUBLIC.CUSTOMERS'
ORDER BY ah.query_start_time DESC;

#Prevent it next time

• Save these queries as a Snowsight dashboard or a worksheet folder that every on-call engineer can open with ops_monitor.
• Make every pipeline set QUERY_TAG (ALTER SESSION SET QUERY_TAG = 'dbt:orders'), so you can find its queries in seconds.
• Set STATEMENT_TIMEOUT_IN_SECONDS on ad-hoc and BI warehouses, so a runaway query stops on its own.
• Alert on failed-query and failed-login counts per service user. A Snowflake alert (CREATE ALERT) running the "top error messages" query on a schedule is enough to start.
• Manage grants as code, so changes go through review and GRANTS_TO_ROLES holds no surprises.