-- ============================================================================
-- warehouse-cost-diagnostics.sql
-- Find out how much of your Snowflake bill is warehouse sizing/idle time,
-- not query complexity.
-- Source: Sqlism (sqlism.com) -- "Your Snowflake SQL Isn't Expensive --
-- Your Warehouse Is"
--
-- Requires: ACCOUNTADMIN, or a role granted IMPORTED PRIVILEGES on the
-- SNOWFLAKE database (needed to query ACCOUNT_USAGE views).
-- ============================================================================

-- 1) Credits used per warehouse over the last 30 days -- start here.
SELECT
    warehouse_name,
    ROUND(SUM(credits_used), 2)                AS total_credits_used,
    ROUND(SUM(credits_used_compute), 2)        AS credits_used_compute,
    ROUND(SUM(credits_used_cloud_services), 2) AS credits_used_cloud_services
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits_used DESC;

-- 2) How much of that is actually tied to running a query, versus a
--    warehouse just sitting there (started but idle, waiting to
--    auto-suspend)? CREDITS_ATTRIBUTED_COMPUTE_QUERIES only counts credits
--    tied to an actual query execution -- the gap between it and
--    CREDITS_USED_COMPUTE is compute time nobody's SQL asked for.
SELECT
    warehouse_name,
    ROUND(SUM(credits_used_compute), 2)                                   AS credits_used_compute,
    ROUND(SUM(credits_attributed_compute_queries), 2)                     AS credits_from_actual_queries,
    ROUND(SUM(credits_used_compute) - SUM(credits_attributed_compute_queries), 2) AS idle_overhead_credits,
    ROUND(
        100.0 * (SUM(credits_used_compute) - SUM(credits_attributed_compute_queries))
        / NULLIF(SUM(credits_used_compute), 0), 1
    ) AS idle_overhead_pct
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY idle_overhead_credits DESC;

-- 3) Is the warehouse oversized for what it actually scans? Cross-reference
--    warehouse_size against typical bytes_scanned per query -- a warehouse
--    running Large or bigger for queries that scan a few hundred MB is
--    usually a sizing decision, not a workload requirement.
SELECT
    warehouse_name,
    warehouse_size,
    COUNT(*)                                   AS query_count,
    ROUND(AVG(bytes_scanned) / POWER(1024, 3), 2) AS avg_gb_scanned,
    ROUND(AVG(total_elapsed_time) / 1000.0, 1)    AS avg_elapsed_seconds
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND warehouse_size IS NOT NULL
GROUP BY warehouse_name, warehouse_size
ORDER BY avg_gb_scanned ASC;