Monitoring: Account Usage, Query History & Observability

In the world of cloud data warehousing, Snowflake has emerged as a powerhouse, offering near-infinite scalability and a unique architecture that separates storage and compute. But with great power comes great responsibility—and cost. Without proper monitoring, your Snowflake account can quickly become a black hole for credits, with runaway queries and idle warehouses burning through your budget. This is where Snowflake's built-in observability features come to the rescue.

Snowflake provides a rich set of views and functions under the SNOWFLAKE.ACCOUNT_USAGE schema and INFORMATION_SCHEMA that give you unprecedented visibility into your account's health, performance, and usage patterns. Whether you're a data engineer trying to optimize a slow ETL pipeline, a platform admin tracking cost allocation across teams, or a developer debugging a sudden spike in query latency, these tools are indispensable.

In this tutorial, you'll learn how to query account usage views to monitor storage, credits, and login history. You'll dive deep into query history to identify slow or expensive queries, understand warehouse utilization, and set up proactive alerts. We'll also cover real-world debugging scenarios, including a production incident where a missing warehouse led to a major outage. By the end, you'll have a production-ready monitoring toolkit to keep your Snowflake environment running smoothly and cost-effectively.

Introduction to Snowflake Monitoring Views

Snowflake exposes several system-defined schemas that provide comprehensive monitoring data. The two most important are:

• SNOWFLAKE.ACCOUNT_USAGE: Contains views with aggregated historical data (up to 365 days) for storage, credits, queries, logins, and more. Data is typically delayed by up to 2 hours.
• INFORMATION_SCHEMA: Contains views like QUERY_HISTORY that provide near real-time data (usually within minutes) for the current session or account.

To access these views, you need the appropriate privileges (e.g., MONITOR USAGE on the account). Let's start by exploring the account usage schema.

-- List all views in SNOWFLAKE.ACCOUNT_USAGE
SELECT view_name FROM SNOWFLAKE.ACCOUNT_USAGE.VIEWS;

-- Check the definition of a specific view
DESCRIBE VIEW SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY;

Querying Query History for Performance Analysis

The QUERY_HISTORY view is your go-to for understanding what queries are running, how long they take, and how many resources they consume. You can filter by time range, warehouse, user, and more. Key columns include:

• QUERY_ID: Unique identifier.
• QUERY_TEXT: The SQL executed.
• EXECUTION_TIME: Time in milliseconds.
• BYTES_SCANNED: Amount of data read.
• CREDITS_USED: Credits consumed (for serverless features).
• WAREHOUSE_NAME: Warehouse used.
• START_TIME, END_TIME.

Let's find the top 10 slowest queries in the last 24 hours.

-- Top 10 slowest queries in the last 24 hours
SELECT
  query_id,
  query_text,
  execution_time / 1000 AS execution_seconds,
  bytes_scanned,
  warehouse_name,
  start_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
ORDER BY execution_time DESC
LIMIT 10;

Monitoring Warehouse Credit Consumption

Warehouses consume credits based on their size and runtime. The WAREHOUSE_METERING_HISTORY view provides hourly credit usage per warehouse. You can use this to track costs and identify idle warehouses. Let's see which warehouses consumed the most credits yesterday.

-- Credit consumption by warehouse for yesterday
SELECT
  warehouse_name,
  SUM(credits_used) AS total_credits,
  COUNT(*) AS hours_active
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND start_time < DATEADD('day', 0, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;

Tracking Storage Usage and Growth

Storage costs can sneak up on you. The STORAGE_USAGE view shows the average daily storage (in bytes) for each database, including compressed data, fail-safe, and time travel. Use DATABASE_STORAGE_USAGE_HISTORY for historical trends. Let's find the top 5 databases by storage.

-- Top 5 databases by storage today
SELECT
  database_name,
  average_storage_bytes / 1099511627776 AS storage_tb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
ORDER BY average_storage_bytes DESC
LIMIT 5;

Analyzing Login and Session History

Security and access monitoring are critical. LOGIN_HISTORY records all login attempts (success/failure), while SESSIONS shows currently active sessions. Use these to detect unauthorized access or troubleshoot connection issues. Let's find failed login attempts in the last week.

-- Failed login attempts in the last 7 days
SELECT
  user_name,
  client_ip,
  first_authentication_method,
  error_message,
  event_timestamp
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE is_success = 'NO'
  AND event_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC;

Building a Monitoring Dashboard with Snowflake Views

To operationalize monitoring, you can create a dashboard using Snowsight (Snowflake's web interface) or export data to a BI tool. Here's a sample query that combines multiple views into a single health check report.

-- Account health summary for the last hour
SELECT
  (SELECT COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE start_time >= DATEADD('hour', -1, CURRENT_TIMESTAMP())) AS queries_last_hour,
  (SELECT SUM(credits_used) FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WHERE start_time >= DATEADD('hour', -1, CURRENT_TIMESTAMP())) AS credits_last_hour,
  (SELECT COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY WHERE is_success = 'NO' AND event_timestamp >= DATEADD('hour', -1, CURRENT_TIMESTAMP())) AS failed_logins_last_hour,
  (SELECT COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.SESSIONS) AS active_sessions;

Using INFORMATION_SCHEMA for Near Real-Time Monitoring

When you need up-to-the-minute data, INFORMATION_SCHEMA views like QUERY_HISTORY_BY_SESSION are your friend. They reflect data within minutes. For example, to see queries currently running or recently completed for your session.

-- Last 10 queries for the current session
SELECT
  query_id,
  query_text,
  execution_time,
  start_time,
  end_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION(RESULT_LIMIT => 10))
ORDER BY start_time DESC;