菜单
Analyze Snowflake query costs and identify optimization opportunities
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
基于 SOC 职业分类
Analyze and optimize SQL queries for better performance
Cloudflare-style AI code review for dbt/SQL pull requests. Produces a signed APPROVE/COMMENT/REQUEST_CHANGES verdict where every blocking finding is backed by a deterministic engine call — column-lineage blast radius, query equivalence, PII classification, and A–F grade. Use to review a dbt PR or the working-tree changes before merge.
REQUIRED before writing or modifying ANY dbt model. Invoke this skill FIRST whenever a task says "create", "build", "add", "modify", "update", "fix", or "refactor" a dbt model, staging file, mart, incremental, or snapshot. Skipping this skill is the leading cause of silent-correctness bugs — models that compile and `dbt build` cleanly but produce wrong values. It contains the patterns that prevent the most common such bugs encountered in real dbt projects: • Incremental high-water marks (`>=` vs `>` ties → silent row dropout) • Snapshot strategy selection (timestamp vs check, `unique_key` choice) • `LEFT JOIN + COUNT(*)` phantom rows from unmatched parents • Type harmonization in `COALESCE` / `CASE` / `UNION` legs • Date-spine completeness (every period present, even empty ones) • Off-by-one window boundaries (`BETWEEN d - (N-1) AND d` for N-wide) • Uniqueness enforcement when schema implies a key • Window-function `LIMIT` with deterministic tiebreaker • Verifying transformation correctness with dbt unit te
REQUIRED after building or modifying ANY dbt model that has columns declared in `schema.yml` / `_models.yml`. Run `altimate-dbt schema-verify --model <name>` to diff actual columns against the spec, and treat any `mismatch` verdict as "not done." The most common reason "the build is green but the tests still fail" is that the model produces the right *data values* in the wrong *column shape* — extra columns, missing columns, wrong order, wrong types. Many dbt equality tests grade the column tuple `(name, type, position)` exactly, and the agent's prior bias is to add "helpful" extras (`p1`/`p2`/`p3` rank breakdowns, name-resolved variants, lineage metadata) or reorder columns "more logically." Both break the contract. This skill enforces the mechanical check that catches those bugs before declaring done. Use it before declaring any model task complete.
Generate dbt unit tests automatically for any model. Analyzes SQL logic (CASE/WHEN, JOINs, window functions, NULLs), creates type-correct mock inputs from manifest schema, and assembles complete YAML. Use when a user says "generate tests", "add unit tests", "test this model", or "test coverage" for dbt models.
Validate that two tables or query results are identical — or diagnose exactly how they differ. Discover schema, identify keys, profile cheaply, then diff. Use for migration validation, ETL regression, and query refactor verification.
| name | cost-report |
| description | 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.