-- Check current storage usage by database
SELECT
  DATABASE_NAME,
  SUM(ACTIVE_BYTES) / 1024 / 1024 / 1024 / 1024 AS ACTIVE_TB,
  SUM(TIME_TRAVEL_BYTES) / 1024 / 1024 / 1024 / 1024 AS TIME_TRAVEL_TB,
  SUM(FAILSAFE_BYTES) / 1024 / 1024 / 1024 / 1024 AS FAILSAFE_TB,
  (SUM(ACTIVE_BYTES) + SUM(TIME_TRAVEL_BYTES)) / 1024 / 1024 / 1024 / 1024 AS BILLABLE_TB
FROM DATABASE_STORAGE_USAGE_HISTORY
WHERE USAGE_DATE = CURRENT_DATE()
GROUP BY 1
ORDER BY BILLABLE_TB DESC;

-- Create a resource monitor with credit quota
CREATE OR REPLACE RESOURCE MONITOR monthly_budget
  WITH
    CREDIT_QUOTA = 5000
    FREQUENCY = MONTHLY
    START_TIMESTAMP = IMMEDIATELY
    TRIGGERS
      ON 50% DO NOTIFY
      ON 75% DO NOTIFY
      ON 90% DO SUSPEND
      ON 100% DO SUSPEND_IMMEDIATELY;

-- Assign monitor to warehouse
ALTER WAREHOUSE analytics_wh
  SET RESOURCE_MONITOR = monthly_budget;

-- Create a monitor for a specific department
CREATE OR REPLACE RESOURCE MONITOR dept_marketing
  WITH
    CREDIT_QUOTA = 1000
    FREQUENCY = MONTHLY
    TRIGGERS
      ON 80% DO SUSPEND
      ON 100% DO SUSPEND_IMMEDIATELY;

-- Check current credit usage
SELECT
  WAREHOUSE_NAME,
  SUM(CREDITS_USED) AS TOTAL_CREDITS,
  SUM(CREDITS_USED_COMPUTE) AS COMPUTE_CREDITS,
  SUM(CREDITS_USED_CLOUD_SERVICES) AS CLOUD_SERVICES_CREDITS
FROM ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATE_TRUNC('month', CURRENT_DATE())
GROUP BY 1
ORDER BY TOTAL_CREDITS DESC;

-- Analyze query performance vs warehouse size
SELECT
  w.WAREHOUSE_NAME,
  w.WAREHOUSE_SIZE,
  COUNT(q.QUERY_ID) AS query_count,
  AVG(q.EXECUTION_TIME_MS) / 1000 AS avg_exec_seconds,
  SUM(q.CREDITS_USED) AS total_credits,
  SUM(q.CREDITS_USED) / COUNT(q.QUERY_ID) AS credits_per_query
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
JOIN SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY w
  ON q.WAREHOUSE_NAME = w.WAREHOUSE_NAME
WHERE q.START_TIME >= CURRENT_DATE() - 30
GROUP BY 1, 2
ORDER BY total_credits DESC;

-- Find warehouses with low utilization
SELECT
  WAREHOUSE_NAME,
  WAREHOUSE_SIZE,
  SUM(CREDITS_USED) AS credits_used,
  SUM(AVG_RUNNING) / 60 AS avg_running_minutes,
  CASE
    WHEN SUM(AVG_RUNNING) < 300 THEN 'LOW - Consider smaller size'
    WHEN SUM(AVG_RUNNING) < 1800 THEN 'MEDIUM'
    ELSE 'HIGH - Good utilization'
  END AS utilization_assessment
FROM TABLE(WAREHOUSE_LOAD_HISTORY(
  DATE_RANGE_START => DATEADD('day', -7, CURRENT_DATE()),
  DATE_RANGE_END => CURRENT_DATE()
))
GROUP BY 1, 2
ORDER BY credits_used DESC;