finops_and_monitoring.sql

-- -- View warehouse credit consumption
SELECT 
    warehouse_name,
    DATE_TRUNC('DAY', start_time) as usage_date,
    SUM(credits_used) as total_credits,
    ROUND(SUM(credits_used) * 2, 2) as estimated_cost_usd
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_DATE())
GROUP BY warehouse_name, DATE_TRUNC('DAY', start_time)
ORDER BY usage_date DESC, warehouse_name;

-- -- Top 10 most expensive queries
SELECT 
    query_id,
    user_name,
    LEFT(query_text, 60) as query_preview,
    ROUND(total_elapsed_time / 1000, 2) as execution_seconds,
    ROUND((total_elapsed_time / 1000 / 3600) * CASE warehouse_size
            WHEN 'XSMALL' THEN 1
            WHEN 'SMALL' THEN 2
            WHEN 'MEDIUM' THEN 4
            WHEN 'LARGE' THEN 8
        END, 6) as estimated_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_DATE())
    AND execution_status = 'SUCCESS'
    AND warehouse_name IS NOT NULL
ORDER BY estimated_credits DESC
LIMIT 10;


-- Monthly budget tracking dashboard
WITH monthly_usage AS (
    SELECT 
        warehouse_name,
        SUM(credits_used) as credits_used
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE start_time >= DATE_TRUNC('MONTH', CURRENT_DATE())
    GROUP BY warehouse_name
)
SELECT 
    warehouse_name,
    ROUND(credits_used, 4) as credits_used,
    50 as monthly_budget,
    ROUND((credits_used / 50) * 100, 1) as pct_consumed,
    CASE 
        WHEN credits_used < 25 THEN 'On Track'
        WHEN credits_used < 40 THEN 'Monitor Closely'
        WHEN credits_used < 50 THEN 'Near Limit'
        ELSE 'Over Budget'
    END as status,
    ROUND(50 - credits_used, 2) as credits_remaining
FROM monthly_usage
ORDER BY pct_consumed DESC;