Resource Monitors and Cost Management¶

-- Comprehensive resource monitor
CREATE RESOURCE MONITOR production_monthly
  WITH CREDIT_QUOTA = 5000
  FREQUENCY = MONTHLY
  START_TIMESTAMP = '2024-01-01 00:00 UTC'
  END_TIMESTAMP = NULL  -- No end date
  TRIGGERS
    ON 25 PERCENT DO NOTIFY
    ON 50 PERCENT DO NOTIFY
    ON 75 PERCENT DO NOTIFY
    ON 90 PERCENT DO SUSPEND
    ON 100 PERCENT DO SUSPEND_IMMEDIATE;

-- Apply to account
ALTER ACCOUNT SET RESOURCE_MONITOR = production_monthly;

-- Apply to specific warehouse
ALTER WAREHOUSE analytics_wh SET RESOURCE_MONITOR = production_monthly;

-- Change quota
ALTER RESOURCE MONITOR production_monthly SET CREDIT_QUOTA = 7000;

-- Add trigger
ALTER RESOURCE MONITOR production_monthly SET
  TRIGGERS ON 60 PERCENT DO NOTIFY;

-- View monitors
SHOW RESOURCE MONITORS;

-- View monitor details
DESCRIBE RESOURCE MONITOR production_monthly;

-- Credit usage by warehouse (last 30 days)
SELECT warehouse_name,
       SUM(credits_used) AS total_credits,
       SUM(credits_used_compute) AS compute_credits,
       SUM(credits_used_cloud_services) AS cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY total_credits DESC;

-- Hourly credit pattern
SELECT warehouse_name,
       HOUR(start_time) AS hour_of_day,
       DAYNAME(start_time) AS day_of_week,
       AVG(credits_used) AS avg_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY 1, 2;

-- Serverless task credits
SELECT task_name, SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.SERVERLESS_TASK_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY task_name
ORDER BY total_credits DESC;

-- Snowpipe credits
SELECT pipe_name, SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY pipe_name
ORDER BY total_credits DESC;

-- Auto-clustering credits
SELECT table_name, SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY total_credits DESC;

-- All metering (combined)
SELECT service_type, SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY
WHERE start_time >= DATEADD('month', -1, CURRENT_TIMESTAMP())
GROUP BY service_type
ORDER BY total_credits DESC;

-- Cloud services overage analysis
SELECT DATE_TRUNC('day', start_time) AS day,
       SUM(CASE WHEN service_type = 'WAREHOUSE_METERING' THEN credits_used ELSE 0 END) AS warehouse_credits,
       SUM(CASE WHEN service_type = 'CLOUD_SERVICES' THEN credits_used ELSE 0 END) AS cloud_credits,
       SUM(CASE WHEN service_type = 'WAREHOUSE_METERING' THEN credits_used ELSE 0 END) * 0.1 AS free_cloud_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1
HAVING cloud_credits > free_cloud_credits
ORDER BY 1;

-- Business hours warehouse management
CREATE TASK scale_up_morning
  SCHEDULE = 'USING CRON 0 8 * * 1-5 America/New_York'
AS
  ALTER WAREHOUSE analytics_wh SET
    WAREHOUSE_SIZE = 'LARGE'
    MIN_CLUSTER_COUNT = 2;

CREATE TASK scale_down_evening
  SCHEDULE = 'USING CRON 0 19 * * 1-5 America/New_York'
AS
  ALTER WAREHOUSE analytics_wh SET
    WAREHOUSE_SIZE = 'SMALL'
    MIN_CLUSTER_COUNT = 1;

CREATE TASK weekend_suspend
  SCHEDULE = 'USING CRON 0 20 * * 5 America/New_York'
AS
  ALTER WAREHOUSE analytics_wh SUSPEND;

-- Table storage analysis
SELECT table_catalog, table_schema, table_name,
       active_bytes/1024/1024/1024 AS active_gb,
       time_travel_bytes/1024/1024/1024 AS time_travel_gb,
       failsafe_bytes/1024/1024/1024 AS failsafe_gb,
       (active_bytes + time_travel_bytes + failsafe_bytes)/1024/1024/1024 AS total_gb
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE active_bytes > 0
ORDER BY total_gb DESC
LIMIT 20;

-- Stage storage
SELECT stage_name, SUM(stage_bytes)/1024/1024/1024 AS stage_gb
FROM SNOWFLAKE.ACCOUNT_USAGE.STAGE_STORAGE_USAGE_HISTORY
WHERE usage_date = CURRENT_DATE()
GROUP BY stage_name
ORDER BY stage_gb DESC;

-- Create transient table (no Fail-safe)
CREATE TRANSIENT TABLE staging_data (
  id INTEGER,
  data VARIANT,
  loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Set Time Travel to 0 for transient
ALTER TABLE staging_data SET DATA_RETENTION_TIME_IN_DAYS = 0;

-- Reduce Time Travel for non-critical tables
ALTER TABLE logs SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Clean up old stages
REMOVE @my_stage/old_data/;

-- Drop unused clones
DROP TABLE IF EXISTS dev_copy_customers;

-- Monitor storage trend
SELECT DATE_TRUNC('month', usage_date) AS month,
       AVG(storage_bytes + stage_bytes + failsafe_bytes)/1024/1024/1024/1024 AS avg_total_tb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
WHERE usage_date >= DATEADD('month', -12, CURRENT_DATE())
GROUP BY 1
ORDER BY 1;

-- Create cost center tag
CREATE TAG cost_center ALLOWED_VALUES 'engineering', 'analytics', 'finance', 'marketing';

-- Tag warehouses
ALTER WAREHOUSE eng_wh SET TAG cost_center = 'engineering';
ALTER WAREHOUSE analytics_wh SET TAG cost_center = 'analytics';

-- Query costs by tag
SELECT tag_value AS cost_center,
       SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wmh
JOIN SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES tr
  ON wmh.warehouse_name = tr.object_name
  AND tr.tag_name = 'COST_CENTER'
WHERE wmh.start_time >= DATE_TRUNC('month', CURRENT_TIMESTAMP())
GROUP BY tag_value
ORDER BY total_credits DESC;