Snowflake Monitoring with Account Usage

Running Snowflake in production means you need visibility into what is happening: which queries run the longest, which warehouses consume the most credits, which users access sensitive data, and whether your pipelines ran successfully. Snowflake provides a powerful built-in monitoring system through the ACCOUNT_USAGE schema and the INFORMATION_SCHEMA. This page shows you how to use both to monitor performance, track costs, and detect problems.

Two Monitoring Systems in Snowflake

Diagram: The Two Logbooks

Imagine a ship's captain who keeps two logbooks. The first logbook is the deck log — updated every few minutes with current conditions. The second is the voyage record — a complete historical archive of every trip the ship has ever made, available for inspection anytime. Snowflake's monitoring works the same way:

• INFORMATION_SCHEMA: The deck log. Shows recent data (last 7 days to 6 months depending on the view). Updated in near real-time. Scoped to the current database.
• ACCOUNT_USAGE schema (in the SNOWFLAKE database): The voyage record. Shows up to 12 months of history. Data may be 45 minutes to 3 hours behind current. Covers the entire account across all databases.

Accessing the SNOWFLAKE Database

The SNOWFLAKE database is a special system database that every Snowflake account has. It contains the ACCOUNT_USAGE schema with detailed views about your account's activity.

-- Switch to the SNOWFLAKE database
USE DATABASE SNOWFLAKE;
USE SCHEMA ACCOUNT_USAGE;

-- See what views are available
SHOW VIEWS IN SNOWFLAKE.ACCOUNT_USAGE;

To access ACCOUNT_USAGE, your role needs the SNOWFLAKE database privilege or the ACCOUNTADMIN role. Grant access like this:

-- Grant access to a role (run as ACCOUNTADMIN)
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE data_analyst;

Monitoring Query History

The most frequently used monitoring view is QUERY_HISTORY. It records every query executed in your account — who ran it, when, how long it took, how many bytes it scanned, and whether it succeeded.

Find the Slowest Queries in the Past 24 Hours

SELECT 
    query_id,
    query_text,
    user_name,
    warehouse_name,
    execution_status,
    ROUND(total_elapsed_time / 1000, 1) AS duration_seconds,
    ROUND(bytes_scanned / 1024 / 1024 / 1024, 2) AS gb_scanned,
    ROUND(percentage_scanned_from_cache, 1) AS cache_hit_pct,
    start_time
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(hour, -24, CURRENT_TIMESTAMP)
  AND execution_status = 'SUCCESS'
ORDER BY total_elapsed_time DESC
LIMIT 20;

Find Failed Queries

SELECT 
    query_id,
    query_text,
    user_name,
    warehouse_name,
    error_code,
    error_message,
    start_time
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
  AND execution_status = 'FAIL'
ORDER BY start_time DESC
LIMIT 50;

Identify Queries Scanning Too Much Data

-- Find queries that scanned more than 100GB of data
SELECT 
    query_id,
    SUBSTR(query_text, 1, 100) AS query_preview,
    user_name,
    warehouse_name,
    ROUND(bytes_scanned / 1024 / 1024 / 1024, 1) AS gb_scanned,
    ROUND(total_elapsed_time / 1000, 1) AS seconds,
    start_time
FROM snowflake.account_usage.query_history
WHERE bytes_scanned > 100 * 1024 * 1024 * 1024  -- 100 GB
  AND start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP)
ORDER BY bytes_scanned DESC
LIMIT 30;

Diagram: The Query X-Ray

A doctor uses X-rays to see inside a patient without opening them up. The QUERY_HISTORY view acts as your X-ray for Snowflake queries. You see everything happening inside: duration, data scanned, cache usage, and errors — without intercepting or interrupting the queries themselves.

Monitoring Warehouse Usage

Credit Consumption by Warehouse

SELECT 
    warehouse_name,
    TO_DATE(start_time) AS usage_date,
    ROUND(SUM(credits_used), 3) AS compute_credits,
    ROUND(SUM(credits_used_cloud_services), 3) AS cloud_credits,
    COUNT(*) AS sessions
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY warehouse_name, usage_date
ORDER BY usage_date DESC, compute_credits DESC;

Find Warehouses Running Too Long Without Jobs

-- Identify warehouses that stayed active (not suspended) for extended periods
SELECT 
    warehouse_name,
    start_time,
    end_time,
    ROUND(DATEDIFF('minute', start_time, end_time), 0) AS active_minutes,
    credits_used
FROM snowflake.account_usage.warehouse_metering_history
WHERE DATEDIFF('hour', start_time, end_time) > 2  -- Active for more than 2 hours
  AND credits_used < 0.1  -- But consumed very few credits (idle)
  AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
ORDER BY active_minutes DESC;

Long active periods with low credit usage indicate warehouses that stayed running without actually doing work. These warehouses need a shorter AUTO_SUSPEND setting.

User Activity Monitoring

Most Active Users by Query Count

SELECT 
    user_name,
    COUNT(*) AS query_count,
    ROUND(SUM(total_elapsed_time) / 1000 / 3600, 1) AS total_compute_hours,
    ROUND(SUM(bytes_scanned) / 1024 / 1024 / 1024, 1) AS total_gb_scanned,
    MAX(start_time) AS last_query_time
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY user_name
ORDER BY query_count DESC
LIMIT 20;

Login History

-- See who logged in and from where
SELECT 
    user_name,
    event_timestamp,
    client_ip,
    reported_client_type,
    is_success,
    error_message
FROM snowflake.account_usage.login_history
WHERE event_timestamp >= DATEADD(day, -7, CURRENT_TIMESTAMP)
ORDER BY event_timestamp DESC;

-- Failed login attempts (potential security concern)
SELECT 
    user_name,
    client_ip,
    COUNT(*) AS failed_attempts
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(*) > 3
ORDER BY failed_attempts DESC;

Storage Monitoring

-- Database storage growth over time
SELECT 
    usage_date,
    database_name,
    ROUND(average_database_bytes / 1024 / 1024 / 1024, 2) AS avg_storage_gb,
    ROUND(average_failsafe_bytes / 1024 / 1024 / 1024, 2) AS failsafe_gb
FROM snowflake.account_usage.database_storage_usage_history
WHERE usage_date >= DATEADD(day, -30, CURRENT_DATE)
ORDER BY usage_date DESC, avg_storage_gb DESC;

-- Stage storage usage
SELECT 
    usage_date,
    stage_name,
    ROUND(average_stage_bytes / 1024 / 1024 / 1024, 2) AS stage_gb
FROM snowflake.account_usage.stage_storage_usage_history
WHERE usage_date >= DATEADD(day, -7, CURRENT_DATE)
ORDER BY usage_date DESC, stage_gb DESC;

Using INFORMATION_SCHEMA for Real-Time Monitoring

While ACCOUNT_USAGE covers historical data, INFORMATION_SCHEMA gives you near-real-time data about the current database. It is scoped to one database at a time.

-- Switch to your working database first
USE DATABASE ANALYTICS_DB;

-- Recent queries in the current database (last 7 days)
SELECT 
    query_id,
    query_text,
    user_name,
    execution_status,
    ROUND(total_elapsed_time / 1000, 1) AS seconds,
    start_time
FROM TABLE(information_schema.query_history(
    DATE_RANGE_START => DATEADD(hour, -1, CURRENT_TIMESTAMP),
    DATE_RANGE_END => CURRENT_TIMESTAMP,
    RESULT_LIMIT => 100
))
ORDER BY start_time DESC;

Current Running Queries

-- See what is running RIGHT NOW
SELECT 
    query_id,
    query_text,
    user_name,
    warehouse_name,
    execution_status,
    ROUND(total_elapsed_time / 1000, 0) AS seconds_running
FROM TABLE(information_schema.query_history())
WHERE execution_status = 'RUNNING'
ORDER BY total_elapsed_time DESC;

Access History: Who Touched What Data

The ACCESS_HISTORY view tracks which objects (tables, views, columns) each query read or modified. This is essential for data governance, compliance, and auditing.

-- Find all queries that accessed a sensitive table
SELECT 
    qh.user_name,
    qh.query_text,
    qh.start_time,
    ah.query_start_time
FROM snowflake.account_usage.access_history ah
JOIN snowflake.account_usage.query_history qh 
    ON ah.query_id = qh.query_id
WHERE EXISTS (
    SELECT 1
    FROM LATERAL FLATTEN(ah.base_objects_accessed) obj
    WHERE obj.value:objectName::STRING = 'CUSTOMER_PII'
)
AND ah.query_start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP)
ORDER BY ah.query_start_time DESC;

Column-Level Access Audit

-- Which users accessed sensitive columns (e.g., social_security_number)
SELECT DISTINCT
    qh.user_name,
    col.value:columnName::STRING AS column_accessed,
    obj.value:objectName::STRING AS table_accessed,
    COUNT(*) AS access_count
FROM snowflake.account_usage.access_history ah
JOIN snowflake.account_usage.query_history qh ON ah.query_id = qh.query_id,
LATERAL FLATTEN(ah.base_objects_accessed) obj,
LATERAL FLATTEN(obj.value:columns) col
WHERE col.value:columnName::STRING IN ('SOCIAL_SECURITY_NUMBER', 'CREDIT_CARD_NUMBER', 'DATE_OF_BIRTH')
  AND ah.query_start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY 1, 2, 3
ORDER BY access_count DESC;

Building a Monitoring Dashboard with SQL

Combine multiple monitoring queries into a daily health check report. Schedule this in a Snowflake Task to run automatically.

-- Create a summary monitoring table
CREATE OR REPLACE TABLE monitoring.daily_health_report AS
SELECT 
    CURRENT_DATE AS report_date,
    
    -- Query metrics
    (SELECT COUNT(*) FROM snowflake.account_usage.query_history 
     WHERE TO_DATE(start_time) = CURRENT_DATE - 1) AS queries_yesterday,
    
    (SELECT COUNT(*) FROM snowflake.account_usage.query_history 
     WHERE TO_DATE(start_time) = CURRENT_DATE - 1 
     AND execution_status = 'FAIL') AS failed_queries,
    
    -- Cost metrics
    (SELECT ROUND(SUM(credits_used), 2) 
     FROM snowflake.account_usage.warehouse_metering_history 
     WHERE TO_DATE(start_time) = CURRENT_DATE - 1) AS credits_yesterday,
    
    -- Storage
    (SELECT ROUND(SUM(average_database_bytes) / 1024 / 1024 / 1024, 1)
     FROM snowflake.account_usage.database_storage_usage_history
     WHERE usage_date = CURRENT_DATE - 1) AS total_storage_gb;

Automating Monitoring with Snowflake Tasks

-- Create a task that runs a monitoring query every day at 7 AM
CREATE OR REPLACE TASK daily_monitoring_task
  WAREHOUSE = MONITORING_WH
  SCHEDULE = 'USING CRON 0 7 * * * UTC'
AS
INSERT INTO monitoring.slow_query_alerts (alert_date, query_id, duration_seconds, user_name, query_text)
SELECT 
    CURRENT_DATE,
    query_id,
    ROUND(total_elapsed_time / 1000, 0),
    user_name,
    SUBSTR(query_text, 1, 500)
FROM snowflake.account_usage.query_history
WHERE TO_DATE(start_time) = CURRENT_DATE - 1
  AND total_elapsed_time > 300000  -- Queries longer than 5 minutes
  AND execution_status = 'SUCCESS';

-- Enable the task
ALTER TASK daily_monitoring_task RESUME;

Key ACCOUNT_USAGE Views Reference

Key Points Summary

• ACCOUNT_USAGE schema (in the SNOWFLAKE database) holds up to 365 days of account-wide history. Data lags by 45 minutes to 3 hours.
• INFORMATION_SCHEMA provides near-real-time data for the current database only, with shorter retention windows.
• QUERY_HISTORY is the most valuable monitoring view — track slow queries, failed queries, and excessive data scans.
• WAREHOUSE_METERING_HISTORY shows credit consumption per warehouse, helping you identify cost hotspots.
• LOGIN_HISTORY tracks authentication events including failed login attempts, important for security monitoring.
• ACCESS_HISTORY provides column-level audit trails showing exactly which data each query touched — essential for compliance.
• Use Snowflake Tasks to schedule monitoring queries and write alerts to dedicated monitoring tables automatically.
• Grant IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE to non-admin roles that need access to ACCOUNT_USAGE views.