cost-report

# Cost Report ## Requirements **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. ## Workflow 1. **Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY** for the top 20 most expensive queries by credits used: ```sql 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. 2. **Group and summarize** the results by: - **User**: Which users are driving the most cost? - **Warehouse**: Which warehouses consume the most credits? - **Query type**: SELECT vs INSERT vs CREATE TABLE AS SELECT vs MERGE, etc. Present each grouping as a markdown table. 3. **Analyze the top offenders** - For each of the top 10 most expensive queries: - Run `sql_analyze` on the query text to detect anti-patterns (SELECT *, missing LIMIT, cartesian products, correlated subqueries, etc.) - Summarize anti-patterns found and their severity 4. **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 | 5. **Warehouse analysis** - Run `finops_warehouse_advice` to check if warehouses used by the top offenders are right-sized. 6. **Unused resource detection** - Run `finops_unused_resources` to find: - **Stale tables**: Tables not accessed in the last 30+ days (candidates for archival/drop) - **Idle warehouses**: Warehouses with no query activity (candidates for suspension/removal) Include findings in the report under a "Waste Detection" section. 7. **Query history enrichment** - Run `finops_query_history` to fetch recent execution patterns: - Identify frequently-run expensive queries (high frequency × high cost = top optimization target) - Find queries that could benefit from result caching or materialization 8. **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 ``` ## Usage 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`.