Monitoring with ACCOUNT_USAGE

Snowflake Management, Governance & Collaboration

Emily Melhuish

Technical Curriculum Developer, Snowflake

The Monitoring Problem

Which warehouses cost the most last month?
Were there runaway queries that should have been caught?
Is storage within budget?
Did any unusual login patterns occur?

ACCOUNT_USAGE can answer these questions

Cost control framework.png

What is ACCOUNT_USAGE?

A schema inside the SNOWFLAKE system database
Contains views exposing historical account data
Latency of up to 3 hours
Most views retain data for 365 days

Account Overview.png

ACCOUNT_USAGE vs INFORMATION_SCHEMA

Feature	ACCOUNT_USAGE	INFORMATION_SCHEMA
Scope	History of entire account, all databases	Current state of your database
Data latency	Up to 3 hours	Low latency
Retention	Up to 365 days	Up to 6 months (varies by view)
Dropped objects	Included	Not included
Primary use case	Historical audit and cost analysis	Current state and metadata

WAREHOUSE_METERING_HISTORY

Answers which warehouses consumed the most credit

SELECT warehouse_name,
       SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;

QUERY_HISTORY

Records every query in the account

SELECT query_text,
     warehouse_name,
     execution_time,
     bytes_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY execution_time DESC
LIMIT 10;

STORAGE_USAGE

Tracks how much storage the account is consuming

both active and Fail-safe storage

SELECT usage_date,
   ROUND(storage_bytes / POWER(1024, 3), 2) AS storage_gb,
   ROUND(failsafe_bytes / POWER(1024, 3), 2) AS failsafe_gb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
ORDER BY usage_date DESC
LIMIT 30;

LOGIN_HISTORY

Records every login attempt against the account
- Successful or not

SELECT user_name,
       event_timestamp,
       reported_client_type,
       error_code,
       error_message
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE error_code IS NOT NULL
  AND event_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC;

Let's practice!

Snowflake Management, Governance & Collaboration