Snowflake ACCOUNT_USAGE Complete Guide

SNOWFLAKE.ACCOUNT_USAGE is a schema inside the Snowflake-provided shared SNOWFLAKE database that retains every kind of metadata, usage history, and audit log within your account for up to 365 days. It centralizes information that is essential for operational monitoring and governance — query history, login history, storage usage, credit consumption, access auditing, and more.

Key Views at a Glance

ACCOUNT_USAGE vs INFORMATION_SCHEMA Comparison

Granting Access

-- ACCOUNTADMINで監視用ロールにアクセスを許可
USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS monitoring_role;

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE
  TO ROLE monitoring_role;

-- 監視担当ユーザーにロールを付与
GRANT ROLE monitoring_role TO USER monitoring_user;

QUERY_HISTORY Examples

-- 過去7日間で最もコストの高いクエリ TOP 20
SELECT
  query_id,
  user_name,
  warehouse_name,
  warehouse_size,
  execution_time / 1000 AS exec_sec,
  bytes_scanned / POWER(1024, 3) AS gb_scanned,
  partitions_scanned,
  partitions_total,
  ROUND(partitions_scanned / NULLIF(partitions_total, 0) * 100, 1) AS scan_pct,
  query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
  AND warehouse_name IS NOT NULL
ORDER BY execution_time DESC
LIMIT 20;

-- ユーザー別クエリ実行統計
SELECT
  user_name,
  COUNT(*) AS total_queries,
  AVG(execution_time) / 1000 AS avg_exec_sec,
  SUM(bytes_scanned) / POWER(1024, 4) AS tb_scanned,
  SUM(credits_used_cloud_services) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
GROUP BY user_name
ORDER BY total_queries DESC;

LOGIN_HISTORY Examples

-- ログイン失敗の検出（不正アクセスの兆候）
SELECT
  user_name,
  client_ip,
  reported_client_type,
  error_code,
  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;

-- ユーザー別ログインパターン分析
SELECT
  user_name,
  COUNT_IF(is_success = 'YES') AS success_count,
  COUNT_IF(is_success = 'NO') AS fail_count,
  COUNT(DISTINCT client_ip) AS distinct_ips,
  LISTAGG(DISTINCT reported_client_type, ', ') AS client_types
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE event_timestamp >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
GROUP BY user_name
HAVING fail_count > 5
ORDER BY fail_count DESC;

STORAGE_USAGE Examples

-- ストレージ使用量の推移（日次）
SELECT
  usage_date,
  ROUND(storage_bytes / POWER(1024, 4), 2) AS active_tb,
  ROUND(failsafe_bytes / POWER(1024, 4), 2) AS failsafe_tb,
  ROUND(stage_bytes / POWER(1024, 4), 2) AS stage_tb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
WHERE usage_date >= DATEADD(MONTH, -3, CURRENT_DATE())
ORDER BY usage_date;

WAREHOUSE_METERING_HISTORY Examples

-- ウェアハウス別の日次クレジット消費
SELECT
  start_time::DATE AS usage_date,
  warehouse_name,
  SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY total_credits DESC;

Privilege Auditing

-- 過去30日間の権限変更履歴
SELECT
  created_on,
  privilege,
  granted_on,
  name AS object_name,
  granted_to,
  grantee_name,
  granted_by
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE created_on >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
  AND deleted_on IS NULL
ORDER BY created_on DESC;

Operational Best Practices

• Create a dedicated monitoring role and grant IMPORTED PRIVILEGES: avoid overusing ACCOUNTADMIN and stay aligned with the principle of least privilege.
• Use INFORMATION_SCHEMA for real-time, ACCOUNT_USAGE for long-term analysis: reach for INFORMATION_SCHEMA table functions when latency is unacceptable (e.g. alerting), and lean on ACCOUNT_USAGE's 365-day window for trend analysis.
• Automate scheduled reports with Dynamic Tables or Tasks: declare aggregations of QUERY_HISTORY and WAREHOUSE_METERING_HISTORY as Dynamic Tables, then surface them in Snowsight dashboards.
• Manage multi-account cost with ORGANIZATION_USAGE: organizations running multiple accounts should consolidate credit consumption through ORGANIZATION_USAGE and generate chargeback reports from it.

Check Your Understanding

Frequently Asked Questions