Skip to main content

Account Usage Views (ACCOUNT_USAGE)

Snowflake maintains a shared, read-only database named SNOWFLAKE. Within this database is the ACCOUNT_USAGE schema.

This schema is a goldmine for Snowflake Administrators. It contains dozens of views that track the historical metadata of your account—every query ever run, every user login, every credit consumed by virtual warehouses, and the exact storage footprint of every table.

Topics Covered​

  • ACCOUNT_USAGE vs INFORMATION_SCHEMA
  • Key Views for Cost and Auditing
  • Building Admin Dashboards

ACCOUNT_USAGE vs INFORMATION_SCHEMA​

It is critical to understand the difference between the ACCOUNT_USAGE schema and the INFORMATION_SCHEMA (which exists inside every database).

FeatureINFORMATION_SCHEMAACCOUNT_USAGE
LatencyReal-time (No delay)Latency of 45 mins to 2 hours
Retention7 days to 6 months (varies by view)1 Year (365 days)
ScopeCurrent DatabaseEntire Account
Use CaseTactical troubleshooting (e.g., "Why did my query fail just now?")Strategic reporting (e.g., "Who spent the most credits last month?")

(Note: The ACCOUNT_USAGE views are restricted to the ACCOUNTADMIN role by default, though access can be delegated. To verify your active role, run SELECT CURRENT_ROLE();)

Hands-On Demonstration: Admin Reporting & Auditing​

Let's simulate how a Snowflake Administrator (e.g., Anjali from the platform engineering team in Bangalore) uses these views to audit security and manage costs.

1. Database Setup for the Lab​

We will create an admin database to store our materialized reporting tables.

Copy and paste the following code into your Snowflake worksheet.

USE ROLE ACCOUNTADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_AUDIT_DB;
USE DATABASE BEEMS_AUDIT_DB;

CREATE SCHEMA IF NOT EXISTS ADMIN_REPORTS;
USE SCHEMA ADMIN_REPORTS;

2. Auditing Security (LOGIN_HISTORY & ACCESS_HISTORY)​

If your Chief Information Security Officer (CISO) asks for a report of all failed login attempts over the last 90 days, you query the LOGIN_HISTORY view.

-- Identify IP addresses with repeated failed login attempts
SELECT
CLIENT_IP,
USER_NAME,
COUNT(*) AS FAILED_ATTEMPTS,
MAX(EVENT_TIMESTAMP) AS LAST_ATTEMPT
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE EVENT_TIMESTAMP >= DATEADD(day, -90, CURRENT_DATE())
AND IS_SUCCESS = 'NO'
GROUP BY CLIENT_IP, USER_NAME
ORDER BY FAILED_ATTEMPTS DESC;

3. Monitoring Compute Costs (WAREHOUSE_METERING_HISTORY)​

Snowflake compute is billed in Credits. The WAREHOUSE_METERING_HISTORY view tracks exactly how many credits each warehouse burned per hour.

-- Find the most expensive virtual warehouses over the last 30 days
SELECT
WAREHOUSE_NAME,
SUM(CREDITS_USED_COMPUTE) AS TOTAL_COMPUTE_CREDITS,
SUM(CREDITS_USED_CLOUD_SERVICES) AS TOTAL_CS_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY WAREHOUSE_NAME
ORDER BY TOTAL_COMPUTE_CREDITS DESC;

4. Query Performance Profiling (QUERY_HISTORY)​

If users complain that "Snowflake is slow", administrators use the QUERY_HISTORY view to find the slowest queries across the entire account.

-- Find the Top 10 longest running queries from the past week
SELECT
QUERY_ID,
USER_NAME,
WAREHOUSE_NAME,
EXECUTION_TIME / 1000 AS EXECUTION_TIME_SECONDS,
QUERY_TEXT
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE())
AND EXECUTION_STATUS = 'SUCCESS'
ORDER BY EXECUTION_TIME DESC
LIMIT 10;

5. Tracking Storage Footprint (STORAGE_USAGE & TABLE_STORAGE_METRICS)​

Storage is cheap, but it still costs money. The TABLE_STORAGE_METRICS view shows the exact byte-size of every table, including the cost of Time Travel (Fail-safe) storage.

Let's simulate storing this data into a historical tracking table so Anjali can visualize storage growth in a BI tool.

-- Create a tracking table
CREATE OR REPLACE TABLE ADMIN_REPORTS.DAILY_STORAGE_METRICS AS
SELECT
TABLE_CATALOG AS DATABASE_NAME,
TABLE_SCHEMA AS SCHEMA_NAME,
TABLE_NAME,
ACTIVE_BYTES / (1024*1024*1024) AS ACTIVE_STORAGE_GB,
TIME_TRAVEL_BYTES / (1024*1024*1024) AS TIME_TRAVEL_STORAGE_GB
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE DELETED = FALSE;

-- The BI Dashboard queries this table
SELECT * FROM ADMIN_REPORTS.DAILY_STORAGE_METRICS ORDER BY ACTIVE_STORAGE_GB DESC LIMIT 5;

Building Cost and Usage Dashboards​

The best practice for managing a Snowflake account is to connect a BI tool (like Tableau, PowerBI, or Superset) directly to the ACCOUNT_USAGE schema.

Instead of writing these queries manually, you build visual dashboards showing:

  1. Credit Burn Rate: A line chart of WAREHOUSE_METERING_HISTORY.
  2. User Activity: A bar chart of QUERY_HISTORY showing which users execute the most queries.
  3. Data Growth: A stacked area chart of STORAGE_USAGE.

(To verify which user you are logged in as to ensure you have the rights to build these dashboards, run SELECT CURRENT_USER();)

Summary​

  • ACCOUNT_USAGE is the master ledger of your Snowflake account's history.
  • It has up to a 2-hour latency but retains data for 365 days.
  • Use WAREHOUSE_METERING_HISTORY for billing/FinOps.
  • Use LOGIN_HISTORY and QUERY_HISTORY for security auditing and performance tuning.