Analyze Snowflake query costs and identify optimization opportunities
Agent: any (read-only analysis) Tools used: sql_execute, sql_analyze, finops_analyze_credits, finops_expensive_queries, finops_warehouse_advice, finops_unused_resources, finops_query_history
Analyze Snowflake warehouse query costs, identify the most expensive queries, detect anti-patterns, and recommend optimizations.
Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for the top 20 most expensive queries by credits used:
SELECT
query_id,
query_text,
user_name,
warehouse_name,
query_type,
credits_used_cloud_services,
bytes_scanned,
rows_produced,
total_elapsed_time,
execution_status,
start_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
AND credits_used_cloud_services > 0
ORDER BY credits_used_cloud_services DESC
LIMIT 20;
Use sql_execute to run this query against the connected Snowflake warehouse.
Group and summarize the results by:
Present each grouping as a markdown table.
Analyze the top offenders - For each of the top 10 most expensive queries:
sql_analyze on the query text to detect anti-patterns (SELECT *, missing LIMIT, cartesian products, correlated subqueries, etc.)Classify each query into a cost tier:
| Tier | Credits | Label | Action |
|---|---|---|---|
| 1 | < $0.01 | Cheap | No action needed |
| 2 | $0.01 - $1.00 | Moderate | Review if frequent |
| 3 | $1.00 - $100.00 | Expensive | Optimize or review warehouse sizing |
| 4 | > $100.00 | Dangerous | Immediate review required |
Warehouse analysis - Run finops_warehouse_advice to check if warehouses used by the top offenders are right-sized.
Unused resource detection - Run finops_unused_resources to find:
Include findings in the report under a "Waste Detection" section.
Query history enrichment - Run finops_query_history to fetch recent execution patterns:
Output the final report as a structured markdown document:
# Snowflake Cost Report (Last 30 Days)
## Summary
- Total credits consumed: X
- Number of unique queries: Y
- Most expensive query: Z credits
## Cost by User
| User | Total Credits | Query Count | Avg Credits/Query |
|------|--------------|-------------|-------------------|
## Cost by Warehouse
| Warehouse | Total Credits | Query Count | Avg Credits/Query |
|-----------|--------------|-------------|-------------------|
## Cost by Query Type
| Query Type | Total Credits | Query Count | Avg Credits/Query |
|------------|--------------|-------------|-------------------|
## Top 10 Expensive Queries (Detailed Analysis)
### Query 1 (X credits) - DANGEROUS
**User:** user_name | **Warehouse:** wh_name | **Type:** SELECT
**Anti-patterns found:**
- SELECT_STAR (warning): Query uses SELECT * ...
- MISSING_LIMIT (info): ...
**Optimization suggestions:**
1. Select only needed columns
2. Add LIMIT clause
3. Consider partitioning strategy
**Cost tier:** Tier 1 (based on credits used)
...
## Waste Detection
### Unused Tables
| Table | Last Accessed | Size | Recommendation |
|-------|--------------|------|----------------|
### Idle Warehouses
| Warehouse | Last Query | Size | Recommendation |
|-----------|-----------|------|----------------|
## Recommendations
1. Top priority optimizations
2. Warehouse sizing suggestions
3. Unused resource cleanup
4. Scheduling recommendations
The user invokes this skill with:
/cost-report -- Analyze the last 30 days/cost-report 7 -- Analyze the last 7 days (adjust the DATEADD interval)Use the tools: sql_execute, sql_analyze, finops_analyze_credits, finops_expensive_queries, finops_warehouse_advice, finops_unused_resources, finops_query_history.