ACCOUNT_USAGE Views Deep Dive
As a Snowflake Administrator, your most powerful tool is the SNOWFLAKE database. Specifically, the ACCOUNT_USAGE schema inside that database contains a massive wealth of metadata about everything happening in your account: costs, queries, security, and objects.
Unlike INFORMATION_SCHEMA (which only holds 7 to 14 days of data), the ACCOUNT_USAGE views retain data for 365 days (1 year), making them essential for compliance audits and historical cost analysis.
Topics Covered
- High-Value Admin Views
- Latency Considerations
ACCOUNT_USAGEvsINFORMATION_SCHEMA- Practical Admin Queries
1. High-Value Admin Views
The ACCOUNT_USAGE schema contains dozens of views. Here are the most critical ones for administration:
QUERY_HISTORY: The holy grail of performance monitoring. Contains the SQL text, execution time, bytes scanned, and warehouse used for every query run in the last year.LOGIN_HISTORY: Security auditing. Shows IP addresses, usernames, and authentication methods for all logins.GRANTS_TO_USERS/GRANTS_TO_ROLES: Used for auditing Role-Based Access Control (RBAC).ACCESS_HISTORY: Critical for compliance. Shows exactly which columns were read by which queries (who accessed PII).METERING_HISTORY: Hourly credit consumption per warehouse.
2. Latency Considerations
The biggest gotcha with ACCOUNT_USAGE is Latency.
Because Snowflake must extract this massive amount of metadata from its internal cloud services layer and push it into these views, the data is not real-time.
LOGIN_HISTORY: ~10 minutes delayed.QUERY_HISTORY: ~45 minutes delayed.ACCESS_HISTORY: Up to 3 hours delayed.
If you are trying to debug a query that failed 5 seconds ago, do not use ACCOUNT_USAGE. You must use INFORMATION_SCHEMA.
3. ACCOUNT_USAGE vs INFORMATION_SCHEMA
| Feature | INFORMATION_SCHEMA | ACCOUNT_USAGE |
|---|---|---|
| Location | Present in every database | Only in the SNOWFLAKE shared database |
| Data Retention | 7 days (usually) | 365 days (1 year) |
| Latency | Real-time (Instant) | Delayed (10 mins to 3 hours) |
| Dropped Objects | Disappear immediately | Retained in views with a DELETED timestamp |
| Use Case | Real-time troubleshooting | Historical trending, monthly billing, compliance audits |
4. Hands-On: Practical Admin Queries
Let's look at some real-world administrative queries using the ACCOUNT_USAGE schema.
(To query the SNOWFLAKE database, you usually need the ACCOUNTADMIN role, or a role explicitly granted the IMPORTED PRIVILEGES. Check your role: SELECT CURRENT_ROLE();)
Query 1: Finding Stale Objects (Tables never queried)
Assume a Data Engineering team in Hyderabad built hundreds of staging tables last year. You want to know which tables haven't been queried in the last 90 days so you can drop them and save storage costs.
USE ROLE ACCOUNTADMIN;
SELECT
TABLE_CATALOG AS DATABASE_NAME,
TABLE_SCHEMA AS SCHEMA_NAME,
TABLE_NAME,
CREATED
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE DELETED IS NULL
-- Find tables created more than 90 days ago
AND CREATED < DATEADD('day', -90, CURRENT_TIMESTAMP())
-- Ensure the table name doesn't appear in the query history for the last 90 days
AND TABLE_NAME NOT IN (
SELECT DISTINCT PARSE_JSON(OBJECTS_MODIFIED)[0].objectName::STRING
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE QUERY_START_TIME >= DATEADD('day', -90, CURRENT_TIMESTAMP())
)
ORDER BY CREATED ASC;
Query 2: Finding Unused Roles
Over-permissioning is a massive security risk. If an analyst (e.g., Rohan) was granted FINANCE_ADMIN_ROLE but hasn't used it in 6 months, that role should be revoked.
SELECT
GRANTEE_NAME AS USER_NAME,
ROLE AS GRANTED_ROLE,
CREATED_ON AS ROLE_GRANTED_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
WHERE DELETED_ON IS NULL
-- Exclude roles that have been actively used in the query history
AND ROLE NOT IN (
SELECT DISTINCT ROLE_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('day', -180, CURRENT_TIMESTAMP())
);
Query 3: Identifying the Most Expensive Queries
If your monthly Snowflake bill suddenly spikes, you need to find the specific SQL queries causing the spike.
We can estimate the cost of a query by calculating its execution time as a percentage of the warehouse's total uptime for that hour.
SELECT
USER_NAME,
WAREHOUSE_NAME,
QUERY_TEXT,
EXECUTION_TIME / 1000 / 60 AS EXECUTION_MINUTES,
-- A rough heuristic: Longest running queries on large warehouses cost the most
(EXECUTION_TIME / 1000) * WAREHOUSE_SIZE_MULTIPLIER AS COST_HEURISTIC
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND WAREHOUSE_NAME IS NOT NULL
ORDER BY EXECUTION_TIME DESC
LIMIT 10;
Summary
- The
SNOWFLAKE.ACCOUNT_USAGEschema is the authoritative source for all historical metadata, security auditing, and cost tracking. - It retains data for 365 days, but suffers from latency (up to a few hours).
- Use
INFORMATION_SCHEMAfor real-time debugging. - Administrators should regularly query these views to find unused roles, stale tables, and expensive queries.