Operator Module · Tier 2
ACCOUNT_USAGE FinOps Queries
Five Queries Every Operator Should Run
The diagnostic SQL that finds idle warehouses, oversized compute, runaway queries, and stale storage in your Snowflake account — and quantifies the avoidable monthly spend. This is the Triad FinOps Agent in source form.
Learning objectives
01
Understand the SNOWFLAKE.ACCOUNT_USAGE schema and the latency characteristics that affect FinOps analysis.
02
Execute the five core FinOps queries — warehouse utilization, idle compute, query waste, storage waste, and credit attribution.
03
Quantify avoidable spend in real dollars using your account's contract credit price.
04
Diagnose the six common false-positive patterns that produce misleading FinOps signals.
05
Schedule the queries as a recurring FinOps health check rather than one-time exercise.
06
Build a remediation roadmap from query findings to prioritized fix list.
Duration
60 minutes
plus lab
Prerequisites
SQL fluency
ACCOUNTADMIN read
Why this matters
Most mid-market Snowflake customers are overspending by 30–50%. They don't know it because nobody runs the queries that would surface it. The five queries in this module take under 60 seconds to execute and typically find $3,000–$15,000 in avoidable monthly spend on a first-time run. This is the highest-ROI hour you'll spend on Snowflake all month.
00
Module Contents
What this module covers
This module is the operating manual for FinOps analysis on Snowflake. Five queries, the patterns they surface, the dollar quantification math, and the remediation roadmap. By the end you will have a recurring FinOps health check running against your account and a backlog of optimization opportunities ranked by dollar impact.
-
01
The ACCOUNT_USAGE schema
Where credit consumption, query history, and storage metrics live. Latency, retention, and access patterns.
p.03
-
02
Query 01 — Warehouse utilization
Find under-utilized warehouses and the dollar savings from right-sizing.
p.04
-
03
Query 02 — Top query consumption
Identify the top 25 queries by credit consumption and the optimization candidates among them.
p.05
-
04
Query 03 — Storage waste
Abandoned tables, Time Travel overhead, and clone storage that produces no business value.
p.06
-
05
Query 04 — Credit attribution by user
Which users and service accounts consume the most credits. Foundation for governance conversations.
p.07
-
06
Query 05 — Trend and anomaly detection
Week-over-week credit consumption trends. Surfaces spikes before they become budget problems.
p.08
-
07
Hands-on lab worksheet
Run all five queries against your account; build a prioritized remediation roadmap.
p.09
-
08
Knowledge check
Five questions covering schema knowledge, query mechanics, dollar quantification, and false positives.
p.10
-
09
Glossary and reference
Terminology, query templates, and a recurring FinOps health-check checklist.
p.11
-
10
Module completion and next steps
Module summary, certificate, and the next module in the series.
p.12
01
Chapter 01
The ACCOUNT_USAGE schema
SNOWFLAKE.ACCOUNT_USAGE is the metadata schema Snowflake exposes for every account. It contains the historical record of warehouse activity, query execution, storage growth, role usage, and credit consumption — the raw material for every FinOps analysis. Knowing its characteristics is the difference between insights and confused investigations.
Three characteristics that matter
Characteristic 01 — Latency. Most ACCOUNT_USAGE views have a 45-minute to 3-hour latency. Yesterday's queries are visible; the last hour's may not be. For real-time monitoring, use INFORMATION_SCHEMA instead (lower retention but no latency).
Characteristic 02 — Retention. 365 days of history for most views. WAREHOUSE_METERING_HISTORY retains 365 days; QUERY_HISTORY retains 365 days; STORAGE_DAILY_HISTORY retains 365 days. Long enough for year-over-year analysis, short enough that monthly snapshots are worth persisting for compliance.
Characteristic 03 — Access requires ACCOUNTADMIN or explicit grant. The schema is locked down by default. Either run FinOps queries as ACCOUNTADMIN, or grant the IMPORTED PRIVILEGES on the SNOWFLAKE database to a dedicated FINOPS_ROLE.
The five views that matter most
| View | Contains | Used in |
|---|
| WAREHOUSE_METERING_HISTORY | Hourly warehouse credit consumption | Q1, Q5 |
| QUERY_HISTORY | Every query, who ran it, credits consumed | Q2, Q4 |
| STORAGE_DAILY_HISTORY | Daily storage costs by database | Q3 |
| TABLE_STORAGE_METRICS | Storage details per table | Q3 |
| METERING_DAILY_HISTORY | Daily credits by service type | Q5 |
Granting FinOps role access
# Dedicated FinOps role pattern
CREATE ROLE FINOPS_ROLE;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE FINOPS_ROLE;
GRANT USAGE ON WAREHOUSE FINOPS_WH TO ROLE FINOPS_ROLE;
GRANT ROLE FINOPS_ROLE TO USER finops_analyst;
Operator Tip
Always run FinOps queries against a dedicated small warehouse (XS). The queries are metadata-only and don't need a Medium or Large warehouse. Running them on a Large warehouse for an hour is itself an avoidable FinOps cost.
02
Chapter 02
Query 01 — Warehouse utilization
The first and highest-impact FinOps query. Identifies warehouses where actual query execution time is a small fraction of credit-billing time. The gap is idle time — warehouses sitting on standby, billing credits, doing no useful work. Reducing this gap is the easiest FinOps win available.
# Warehouse utilization over the last 30 days
WITH warehouse_credits AS (
SELECT
warehouse_name,
SUM(credits_used) AS total_credits,
SUM(credits_used) * 24 * 30 AS annualized_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
),
query_time AS (
SELECT
warehouse_name,
SUM(execution_time) / 1000.0 / 3600.0 AS total_execution_hours
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
AND warehouse_name IS NOT NULL
GROUP BY warehouse_name
)
SELECT
c.warehouse_name,
c.total_credits,
q.total_execution_hours,
c.total_credits * 1.0 / NULLIF(q.total_execution_hours, 0) AS credits_per_active_hour,
ROUND(c.total_credits * 4.00, 2) AS dollar_cost_30d -- replace 4.00 with your credit price
FROM warehouse_credits c
LEFT JOIN query_time q ON c.warehouse_name = q.warehouse_name
ORDER BY c.total_credits DESC;
Interpreting the output
Warehouses with high credits_per_active_hour are good candidates for size reduction or auto-suspend tuning. A warehouse consuming 12 credits per active hour is much more expensive than one consuming 4. If the workload doesn't justify the size, you're paying for performance you don't use.
Common findings: a Large warehouse running queries that complete in seconds (downgrade to Medium or Small); a warehouse with 600 credits/month and only 8 hours of execution (increase auto-suspend aggressiveness); two near-identical warehouses with overlapping use cases (consolidate).
Dollar quantification
Replace the 4.00 in the query with your actual contract credit price (typical range $2.00–$4.50 per credit depending on edition and commitment). The dollar_cost_30d column is the current monthly spend per warehouse. Identifying right-size candidates and applying a 40% reduction estimate gives you the dollar savings opportunity for the next remediation conversation.
Operator Tip
Never recommend a warehouse downsize without first proposing a parallel-run period. The downsize might break performance for an edge-case workload. Recommend "Run Medium and Small in parallel for one week, compare query performance, downsize permanently if no regressions." Conservative wins trust.
03
Chapter 03
Query 02 — Top query consumption
The second highest-impact query. In most Snowflake accounts, the top 10 queries by credit consumption account for 50–70 percent of total credits. Often these are a small set of repeated queries — broken dashboards, unoptimized pipeline jobs, or runaway BI tool queries. Fixing one or two of them produces dramatic savings.
# Top 25 query patterns by credit consumption over 30 days
SELECT
LEFT(query_text, 100) AS query_pattern,
COUNT(*) AS execution_count,
SUM(credits_used_cloud_services) AS total_credits,
AVG(execution_time)/1000 AS avg_execution_seconds,
SUM(bytes_scanned)/1024/1024/1024 AS total_gb_scanned,
SUM(partitions_scanned) AS total_partitions_scanned,
SUM(partitions_total) AS total_partitions_available,
ROUND(
SUM(partitions_scanned) * 100.0 / NULLIF(SUM(partitions_total), 0),
1
) AS partition_scan_pct,
ROUND(SUM(credits_used_cloud_services) * 4.00, 2) AS dollar_cost_30d
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
AND warehouse_size IS NOT NULL -- exclude metadata-only queries
GROUP BY query_pattern
ORDER BY total_credits DESC
LIMIT 25;
The three signals to look for
Signal 01 — High partition_scan_pct. A query scanning 90%+ of available partitions on every run is a clustering optimization candidate. Adding a clustering key or restructuring the WHERE clause can cut credits by 60–80%.
Signal 02 — High execution_count with significant per-execution cost. A query running 1,000 times daily at 2 credits per run is consuming 60 credits per day — $7,200/month at typical pricing. These are dashboard refresh queries or pipeline jobs that often have simple optimization opportunities.
Signal 03 — Long avg_execution_seconds on a Large warehouse. A query that runs for minutes on a Large warehouse may be CPU-bound rather than IO-bound. Sometimes a query rewrite or materialization saves more than warehouse sizing alone.
The 80/20 pattern
In FinOps work, the 80/20 rule consistently understates the concentration. It's usually 90/10 — the top 10% of queries cause 90% of spend. Fix the top three queries and you've often captured most of the available savings. Don't try to optimize everything; find the heavy hitters and fix those.
04
Chapter 04
Query 03 — Storage waste
Storage is the quiet cost driver. Compute waste shows up immediately on the bill; storage waste accumulates silently. Tables created for a one-off analysis three years ago. Clones nobody remembers making. Time Travel retention configured for 90 days on tables that need 1 day. Each individual finding looks small; the aggregate is meaningful.
# Storage waste — tables not queried in 90+ days
SELECT
t.table_catalog,
t.table_schema,
t.table_name,
t.active_bytes/1024/1024/1024 AS active_gb,
t.time_travel_bytes/1024/1024/1024 AS time_travel_gb,
t.failsafe_bytes/1024/1024/1024 AS failsafe_gb,
(t.active_bytes + t.time_travel_bytes + t.failsafe_bytes)/1024/1024/1024 AS total_gb,
MAX(q.start_time) AS last_query_time,
DATEDIFF(day, MAX(q.start_time), CURRENT_TIMESTAMP()) AS days_since_last_query,
ROUND(
(t.active_bytes + t.time_travel_bytes + t.failsafe_bytes)/1024/1024/1024/1024 * 23.00,
2
) AS monthly_storage_cost_usd -- $23/TB/month typical
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS t
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah
ON ah.objects_accessed[0]:objectName::VARCHAR =
t.table_catalog || '.' || t.table_schema || '.' || t.table_name
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
ON q.query_id = ah.query_id
WHERE t.deleted = FALSE
GROUP BY 1, 2, 3, 4, 5, 6, 7
HAVING (
last_query_time IS NULL
OR last_query_time < DATEADD(day, -90, CURRENT_TIMESTAMP())
)
AND total_gb > 1
ORDER BY total_gb DESC;
Three storage waste patterns
Pattern 01 — Abandoned tables. Tables not queried in 90+ days that consume significant storage. Investigate ownership; many turn out to be orphaned from completed projects.
Pattern 02 — Excessive Time Travel. Tables with 90 days of Time Travel where business need is 1 day. The DATA_RETENTION_TIME_IN_DAYS parameter can be reduced retroactively.
Pattern 03 — Stale clones. Zero-copy clones consume storage as they diverge from the source. Clones older than 30 days that aren't actively used should be evaluated for deletion.
Operator Tip
Never drop a table immediately based on this query. Always confirm ownership first, send a heads-up to the owner, and wait 14 days. The query identifies candidates for removal; humans decide which candidates actually get removed. Surprises here damage trust with data teams.
05
Chapter 05
Query 04 — Credit attribution by user
When the credit bill arrives higher than expected, the first question is always "who consumed it?" The credit attribution query answers that. Group by user, group by role, group by service account. The output is the foundation of every accountability conversation.
# Credit consumption by user — last 30 days
SELECT
user_name,
role_name,
COUNT(*) AS query_count,
SUM(credits_used_cloud_services) AS credits_consumed,
ROUND(SUM(credits_used_cloud_services) * 4.00, 2) AS dollar_cost,
ROUND(
SUM(credits_used_cloud_services) * 100.0 /
SUM(SUM(credits_used_cloud_services)) OVER (),
2
) AS pct_of_total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
AND credits_used_cloud_services > 0
GROUP BY user_name, role_name
ORDER BY credits_consumed DESC
LIMIT 25;
Three patterns to investigate
Pattern 01 — Service account consuming 40%+ of total credits. Usually indicates a scheduled pipeline or BI tool consuming disproportionate credits. Investigate the workflow under that service account; optimize or scope.
Pattern 02 — Single human user consuming top spot. Often legitimate — a senior data engineer doing heavy analytics work. Sometimes a sign of accidental cost behavior — a long-running notebook left open with auto-refresh.
Pattern 03 — ACCOUNTADMIN role appearing in top consumers. Red flag for governance. ACCOUNTADMIN shouldn't be doing routine query work; it's a privilege escalation role. Heavy ACCOUNTADMIN consumption signals role design problems.
The accountability shift
Without attribution, "Snowflake is expensive" is a vague complaint that lands nowhere. With attribution, the conversation becomes "Marketing's data team consumed 32% of credits last month." That's actionable. It opens the door to charge-back conversations, optimization investments, and budget right-sizing.
06
Chapter 06
Query 05 — Trend and anomaly detection
The first four queries are snapshots. The fifth is a trend — week-over-week credit consumption. Snapshot queries find existing problems; trend queries catch new problems before they become old problems. Most FinOps disasters started as a small Tuesday spike that nobody noticed for three weeks.
# Week-over-week credit consumption trend
WITH daily_credits AS (
SELECT
DATE(start_time) AS usage_date,
service_type,
SUM(credits_used_compute) AS compute_credits,
SUM(credits_used_cloud_services) AS services_credits,
SUM(credits_used_compute + credits_used_cloud_services) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY
WHERE start_time >= DATEADD(day, -90, CURRENT_TIMESTAMP())
GROUP BY 1, 2
),
weekly_summary AS (
SELECT
DATE_TRUNC(week, usage_date) AS week_start,
service_type,
SUM(total_credits) AS week_credits,
LAG(SUM(total_credits)) OVER (
PARTITION BY service_type
ORDER BY DATE_TRUNC(week, usage_date)
) AS prev_week_credits
FROM daily_credits
GROUP BY 1, 2
)
SELECT
week_start,
service_type,
week_credits,
prev_week_credits,
ROUND(
(week_credits - prev_week_credits) * 100.0 / NULLIF(prev_week_credits, 0),
1
) AS wow_change_pct,
CASE
WHEN week_credits > prev_week_credits * 1.25 THEN '⚠ SPIKE'
WHEN week_credits > prev_week_credits * 1.10 THEN 'WARNING'
ELSE 'NORMAL'
END AS alert_level
FROM weekly_summary
WHERE prev_week_credits IS NOT NULL
ORDER BY week_start DESC, week_credits DESC;
What the alert levels mean
NORMAL — week-over-week change within 10%. Probably noise. No investigation needed.
WARNING — 10-25% increase. Could be legitimate (adoption, new features) or could be early signal of a problem. Investigate the service_type with the largest change.
SPIKE — >25% increase. Investigate immediately. Most likely causes: new scheduled job, retry loop, broken auto-suspend, or a single workflow that exploded in cost.
Operator Tip
Schedule this query as a Monday morning task. Five minutes of review every week catches issues before they become emergencies. The query that takes 30 seconds to run on Monday saves the 8-hour investigation on Thursday when someone notices the monthly bill is double expected.
07
Chapter 07
Hands-on lab worksheet
Five tasks to run the FinOps queries against your own Snowflake account and build a remediation roadmap. Estimated time: 60–75 minutes.
Task 01
Set up the FINOPS_ROLE
10 min
Create FINOPS_ROLE with IMPORTED PRIVILEGES on SNOWFLAKE database. Create a small FINOPS_WH (XS, auto-suspend 60s). Grant role to your user. Switch into the role and confirm ACCOUNT_USAGE schema is accessible.
Task 02
Run Query 01 and identify top right-size candidates
15 min
Execute the warehouse utilization query. Identify the two warehouses with highest credit consumption and worst utilization. Calculate the dollar savings opportunity at 40% reduction.
Task 03
Run Query 02 and pick the top three optimization candidates
15 min
Execute the top-query consumption query. Identify three queries with high partition_scan_pct or high execution_count. Estimate the dollar impact of optimization.
Task 04
Run Query 03 and quantify storage waste
10 min
Execute the storage waste query. Sum the storage of tables not queried in 90+ days. Calculate the monthly storage cost of that aggregate.
Task 05
Build the remediation roadmap
15 min
Combine findings from Tasks 02, 03, 04. Sum the total dollar savings opportunity. Rank items by impact and ease. The top 3 items become your Quick-Win Sprint scope.
08
Chapter 08
Knowledge check
Question 01
What is the typical latency of SNOWFLAKE.ACCOUNT_USAGE views?
A
Real-time (sub-second)
B
45 minutes to 3 hours
C
24-48 hours
D
One full week
Question 02
In the warehouse utilization query, a warehouse with high credits_per_active_hour is most likely:
Question 03
A query with 95% partition_scan_pct is a candidate for:
A
Deletion — it's broken
B
Clustering optimization or WHERE clause restructuring
C
Running on a larger warehouse
D
No action — this is normal
Question 04
Storage waste analysis should be acted on:
A
Immediately — drop all flagged tables
B
After confirming ownership and giving owners 14 days to respond
C
Never — storage is too cheap to bother with
D
Only during major migrations
Question 05
The week-over-week trend query is best scheduled as:
A
A one-time ad-hoc investigation
B
A weekly Monday review — 5 minutes catches issues before they become emergencies
C
Only when the bill arrives high
D
Quarterly
09
Chapter 09
Glossary and reference
Key terminology
ACCOUNT_USAGE
The Snowflake-provided metadata schema with 365 days of historical activity data. Requires ACCOUNTADMIN or imported privileges to query.
FINOPS_ROLE
Dedicated role pattern for FinOps analysis. Has IMPORTED PRIVILEGES on the SNOWFLAKE database; can read ACCOUNT_USAGE without ACCOUNTADMIN escalation.
Credit price
Your contract-specific dollar price per credit. Standard ranges $2.00–$4.50. Replace literal in queries with your actual rate.
Partition scan percentage
Ratio of partitions scanned to partitions available for a query. High values (>80%) indicate clustering or filter optimization opportunity.
Time Travel
Snowflake's point-in-time recovery feature. Default retention is 1 day; configurable up to 90 days. Excess retention is silent storage cost.
Fail-safe
Snowflake's 7-day disaster recovery storage. Not configurable. Counted in TABLE_STORAGE_METRICS as failsafe_bytes.
Service type
METERING_DAILY_HISTORY column. WAREHOUSE_METERING, CLOUD_SERVICES, AUTO_CLUSTERING, MATERIALIZED_VIEW, CORTEX_FUNCTIONS, and others.
Recurring health-check checklist
Monday: Run Query 05 (trend). Investigate any SPIKE alert.
First Monday of month: Run Queries 01, 02, 03 (utilization, queries, storage). Update remediation backlog.
Quarterly: Run Query 04 (attribution). Review with finance and department leaders.
Annually: Re-baseline credit price assumption in queries.
After major architecture changes: Run all five queries; compare to baseline.
10
Chapter 10
Module completion
What you learned
The schema
ACCOUNT_USAGE characteristics, the five views that matter most, and the FINOPS_ROLE pattern for non-ACCOUNTADMIN access.
The five queries
Warehouse utilization, top queries, storage waste, credit attribution, and trend detection. The complete FinOps diagnostic toolkit.
Dollar quantification
How to convert credits into dollars using your contract price. The math that turns FinOps findings into business cases.
Recurring rhythm
Weekly trend review, monthly snapshot analysis, quarterly attribution. The cadence that catches problems early.
Cloud On Demand · Operator Module Completion
ACCOUNT_USAGE
FinOps Queries
Awarded to the practitioner who completed module OPR-003
Recipient signature
Issued by Cloud On Demand · OPR-003 · v1.0
Next in series
OPR-004 · Cortex Analyst Semantic Models
→