cost-management

name	cost-management
description	Snowflake billing and credit analysis - generates SQL queries against ACCOUNT_USAGE billing/metering views to analyze Snowflake account charges and credit consumption (compute, storage, data transfer costs). Use for: virtual warehouse costs, credit consumption, storage costs, query compute costs, serverless compute costs, cost attribution by team/department, budget tracking, cost anomaly detection, spend optimization. Triggers: costs, spending, credits, bills, billing, charges, expensive, budget, warehouse costs, storage costs, how much did it cost, cost breakdown, top spenders, cost allocation.
location	system

Cost Management Instructions

Generate SQL queries against Snowflake ACCOUNT_USAGE cost and metering data using the embedded semantic model.

When to Use

Warehouse & Compute: Warehouse costs, compute pools, Snowpark containers, resize events, utilization
Storage: Database/stage storage usage, growth trends, optimization opportunities
Query Costs: Expensive queries, parameterized patterns, query acceleration, user attribution
Attribution: Team/department cost breakdown via tags, untagged resources, budget tracking
Anomalies: Automated detection, variance analysis, week/month-over-week comparisons
Services: Serverless tasks, Cortex AI (Analyst/Functions), clustering, data transfer

Workflow

Step 1: Parse Question → Identify Domain

• Warehouse costs → WAREHOUSE_METERING, METERING
• Query costs → QUERY_ATTRIBUTION, QUERY_HISTORY
• Storage → DATABASE_STORAGE, STAGE_STORAGE
• Team attribution → TAG_REFERENCES + cost tables
• Anomalies → ANOMALIES_DAILY
• Service-specific → CORTEX_*, SERVERLESS_TASK_HISTORY, SNOWPARK_CONTAINER_SERVICES

Step 2: Check Verified Queries FIRST

Search the embedded semantic model's verified_queries section (90+ queries) for matching patterns:

• Spending/costs → "Where is my money going", "Top warehouses/users"
• Time comparisons → "Week-over-week", "Month-over-month", "Why has my cost increased"
• Anomalies → "Cost anomaly analysis", "Anomaly trends"
• Teams/attribution → "Team cost breakdown", "Team attribution"
• Services → Cortex, serverless tasks, clustering, data transfer queries

If match found: Adapt the verified query SQL (adjust time filters, object names, limits). Replace __table_name with SNOWFLAKE.ACCOUNT_USAGE.TABLE_NAME from base_table definition.

If no match: Generate SQL using semantic model table definitions and similar verified queries as structural reference.

Step 3: SQL Construction Guidelines

• All tables in SNOWFLAKE.ACCOUNT_USAGE schema
• Use ROUND() for credit amounts (2 decimal places)
• For anomaly queries, ALWAYS filter on IS_ANOMALY = TRUE (not actual > forecasted)
• JOIN TAG_REFERENCES for cost attribution
• Apply time filters (common: last 7/30 days, month-to-date)
• Order by cost/credits DESC, use LIMIT for top-N queries

Step 4: Execute Query

Return generated SQL and results.

Key Notes

• Round credits to 2 decimals
• Anomaly detection requires IS_ANOMALY = TRUE
• TAG_REFERENCES enables team/department attribution
• METERING = cross-service view; specific tables = detail
• Apply time filters to avoid excessive data scanning
• 90+ verified queries embedded below with proven patterns

---

Semantic Model

Semantic model embedded below:

name: cost_management description: Semantic view covering account level cost related views tables:

• name: ANOMALIES_DAILY synonyms:
  • cost anomalies
  • spending anomalies
  • usage anomalies
  • anomaly detection description: Daily cost anomaly detection results with consumption patterns and anomaly indicators base_table: database: SNOWFLAKE schema: ACCOUNT_USAGE table: ANOMALIES_DAILY primary_key: columns:
    • DATE
    • ANOMALY_ID dimensions:
  • name: ANOMALY_ID synonyms:
    • anomaly identifier description: System-generated identifier for the anomaly detection record expr: anomaly_id data_type: VARCHAR(16777216)
  • name: DATE synonyms:
    • anomaly date
    • detection date description: Day in UTC when the consumption occurred expr: date data_type: DATE
  • name: IS_ANOMALY synonyms:
    • anomaly flag
    • is cost anomaly
    • anomaly detected
    • true anomaly
    • actual anomaly description: "CRITICAL: This boolean field is the authoritative indicator of anomalies. Only rows where is_anomaly = TRUE represent actual cost anomalies. Do not assume actual_value > forecasted_value means anomaly - use this field instead." expr: is_anomaly data_type: BOOLEAN facts:
  • name: ACTUAL_VALUE description: Amount of consumption measured in credits expr: actual_value data_type: NUMBER(38,2)
  • name: FORECASTED_VALUE description: Predicted consumption based on the anomaly-detecting algorithm, measured in credits expr: forecasted_value data_type: NUMBER(38,2)
  • name: LOWER_BOUND description: Predicted lowest level of consumption based on the anomaly-detecting algorithm, measured in credits expr: lower_bound data_type: NUMBER(38,2)
  • name: UPPER_BOUND description: Predicted highest level of consumption based on the anomaly-detecting algorithm, measured in credits expr: upper_bound data_type: NUMBER(38,2) metrics:
  • name: ANOMALY_PERCENTAGE description: Percentage of days that were identified as cost anomalies expr: (SUM(CASE WHEN anomalies_daily.is_anomaly THEN 1 ELSE 0 END) * 100.0 / COUNT(anomalies_daily.date))
  • name: AVERAGE_VARIANCE_FROM_FORECAST description: Average absolute variance between actual and forecasted consumption in credits expr: AVG(ABS(anomalies_daily.actual_value - anomalies_daily.forecasted_value))
  • name: TOTAL_ANOMALOUS_CONSUMPTION description: Total consumption in credits during anomalous periods expr: SUM(CASE WHEN anomalies_daily.is_anomaly THEN anomalies_daily.actual_value ELSE 0 END)
• name: AUTOMATIC_CLUSTERING_HISTORY synonyms:
  • clustering history
  • automatic clustering
  • clustering usage
  • clustering costs
  • table clustering description: Historical record of automatic clustering operations including credits consumed, bytes and rows reclustered for each table clustering event base_table: database: SNOWFLAKE schema: ACCOUNT_USAGE table: AUTOMATIC_CLUSTERING_HISTORY primary_key: columns: