Snowflake Cost & Usage Analyzer

Replace {{PLACEHOLDERS}} with your environment values, then deploy to your stack.

-- =============================================================================
-- SNOWFLAKE COST & USAGE ANALYZER
-- Replace {{WAREHOUSE_NAME}} / {{ROLE_NAME}} / date filters, run weekly
-- =============================================================================

-- ── CONFIGURATION ────────────────────────────────────────────────────────────
SET start_date = '{{START_DATE}}'::DATE;   -- e.g. '2026-01-01'
SET end_date   = '{{END_DATE}}'::DATE;     -- e.g. '2026-01-31'
SET target_wh  = '{{WAREHOUSE_NAME}}';     -- e.g. 'ETL_WH' or '%' for all

-- ── 1. Top expensive queries (last 30 days) ──────────────────────────────────
SELECT
    query_id,
    user_name,
    warehouse_name,
    total_elapsed_time / 1000 / 60          AS elapsed_minutes,
    bytes_scanned / POWER(1024, 3)          AS gb_scanned,
    credits_used_cloud_services,
    LEFT(query_text, 200)                 AS query_preview,
    start_time
FROM snowflake.account_usage.query_history
WHERE start_time BETWEEN $start_date AND $end_date
  AND warehouse_name ILIKE $target_wh
  AND execution_status = 'SUCCESS'
ORDER BY total_elapsed_time DESC
LIMIT 25;

-- ── 2. Warehouse utilization & idle time ───────────────────────────────────
SELECT
    warehouse_name,
    SUM(credits_used)                       AS total_credits,
    AVG(avg_running)                        AS avg_concurrency,
    SUM(credits_used_compute)               AS compute_credits,
    SUM(credits_used_cloud_services)        AS cloud_services_credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN $start_date AND $end_date
  AND warehouse_name ILIKE $target_wh
GROUP BY 1
ORDER BY total_credits DESC;

-- ── 3. Tables with highest storage cost drivers ──────────────────────────────
SELECT
    table_catalog   AS database_name,
    table_schema    AS schema_name,
    table_name,
    active_bytes / POWER(1024, 3)           AS active_gb,
    time_travel_bytes / POWER(1024, 3)      AS time_travel_gb,
    failsafe_bytes / POWER(1024, 3)         AS failsafe_gb,
    (active_bytes + time_travel_bytes + failsafe_bytes) / POWER(1024, 3) AS total_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE deleted = FALSE
ORDER BY total_gb DESC
LIMIT 20;

-- ── 4. Recommended actions (manual review) ───────────────────────────────────
-- • Resize warehouses with avg_running < 0.5 and high credits
-- • Set AUTO_SUSPEND = 60 for dev/test warehouses
-- • Add clustering keys on tables with frequent full scans
-- • Archive tables with time_travel_gb > active_gb * 2