-- Example 1: Create warehouse with comprehensive settings
CREATE WAREHOUSE analytics_wh
    WAREHOUSE_SIZE = 'xlarge'
    MIN_CLUSTER_COUNT = 2
    MAX_CLUSTER_COUNT = 8
    SCALING_POLICY = 'STANDARD'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = FALSE
    STATEMENT_TIMEOUT_IN_SECONDS = 3600
    STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600
    RESOURCE_MONITOR = 'analytics_monitor'
    COMMENT = 'Multi-cluster warehouse for analytics workloads';

-- Example 2: Create resource monitor
CREATE RESOURCE MONITOR account_monitor
    WITH
    CREDIT_QUOTA = 10000
    FREQUENCY = MONTHLY
    START_TIMESTAMP = IMMEDIATELY
    TRIGGERS
        ON 80% DO NOTIFY
        ON 90% DO SUSPEND
        ON 95% DO SUSPEND_IMMEDIATELY;

-- Example 3: Create warehouse-specific monitor
CREATE RESOURCE MONITOR etl_monitor
    WITH
    CREDIT_QUOTA = 5000
    FREQUENCY = MONTHLY
    TRIGGERS
        ON 75% DO NOTIFY
        ON 90% DO SUSPEND;

-- Assign monitor to warehouse
ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = etl_monitor;

-- Example 4: Monitor warehouse performance
SELECT 
    warehouse_name,
    warehouse_size,
    cluster_number,
    AVG(queries_completed) as avg_queries,
    AVG(queued_overload_queries) as avg_queued,
    AVG(execution_time_ms) / 1000 as avg_exec_seconds,
    SUM(credits_used) as total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY total_credits DESC;

-- Example 5: Analyze auto-scaling events
SELECT 
    warehouse_name,
    event_name,
    event_timestamp,
    event_reason,
    cluster_number
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
    START_TIME => DATEADD(hour, -24, CURRENT_TIMESTAMP())
))
WHERE event_name IN ('RESUME', 'SUSPEND', 'ADD_CLUSTER', 'REMOVE_CLUSTER')
ORDER BY event_timestamp DESC;

-- Example 6: Monitor current warehouse status
SELECT 
    warehouse_name,
    state,
    num_queries,
    num_queued_queries,
    time_active,
    time_suspended
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
    START_TIME => DATEADD(hour, -1, CURRENT_TIMESTAMP())
))
ORDER BY time_active DESC;

-- Example 7: Dynamic warehouse scaling
-- Scale up warehouse for batch processing
ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = '2xlarge';

-- Scale out warehouse for peak load
ALTER WAREHOUSE analytics_wh SET MAX_CLUSTER_COUNT = 10;

-- Scale in warehouse during off-hours
ALTER WAREHOUSE analytics_wh SET MAX_CLUSTER_COUNT = 2;

-- Example 8: Warehouse usage analysis
SELECT 
    warehouse_name,
    DATE_TRUNC('day', start_time) as usage_date,
    SUM(credits_used) as daily_credits,
    SUM(credits_used) * 3 as daily_cost_usd,  -- ~$3/credit
    AVG(queries_completed) as avg_queries_per_hour
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -90, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY usage_date DESC, daily_credits DESC;

-- Example 9: Optimize warehouse for specific workload
-- Create warehouse optimized for large scans
CREATE WAREHOUSE scan_wh
    WAREHOUSE_SIZE = '3xlarge'
    AUTO_SUSPEND = 600
    STATEMENT_TIMEOUT_IN_SECONDS = 7200
    COMMENT = 'Optimized for large table scans';

-- Create warehouse for interactive queries
CREATE WAREHOUSE interactive_wh
    WAREHOUSE_SIZE = 'medium'
    AUTO_SUSPEND = 60
    STATEMENT_TIMEOUT_IN_SECONDS = 300
    STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 60
    COMMENT = 'Optimized for interactive queries';

-- Example 10: Monitor query performance by warehouse
SELECT 
    warehouse_name,
    AVG(execution_time_ms) / 1000 as avg_exec_seconds,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY execution_time_ms) / 1000 as median_exec_seconds,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms) / 1000 as p95_exec_seconds,
    COUNT(*) as total_queries,
    SUM(CASE WHEN execution_time_ms > 60000 THEN 1 ELSE 0 END) as slow_queries
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
GROUP BY 1
ORDER BY avg_exec_seconds DESC;