-- Create a multi-cluster warehouse with STANDARD policy
CREATE OR REPLACE WAREHOUSE analytics_mcw
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTERS = 1
  MAX_CLUSTERS = 5
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = FALSE
  COMMENT = 'Multi-cluster warehouse for analytics workloads';

-- Create an ECONOMY policy warehouse for batch jobs
CREATE OR REPLACE WAREHOUSE etl_mcw
  WAREHOUSE_SIZE = 'LARGE'
  MIN_CLUSTERS = 1
  MAX_CLUSTERS = 8
  SCALING_POLICY = 'ECONOMY'
  AUTO_SUSPEND = 600
  AUTO_RESUME = TRUE
  COMMENT = 'Economy multi-cluster for ETL processing';

-- Create a multi-cluster for high-concurrency dashboards
CREATE OR REPLACE WAREHOUSE dashboard_mcw
  WAREHOUSE_SIZE = 'SMALL'
  MIN_CLUSTERS = 2
  MAX_CLUSTERS = 10
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  COMMENT = 'High-concurrency warehouse for BI dashboards';

-- Alter an existing warehouse to add multi-cluster
ALTER WAREHOUSE existing_wh
  SET
    MIN_CLUSTERS = 2
    MAX_CLUSTERS = 6
    SCALING_POLICY = 'ECONOMY';

-- Suspend a specific cluster
ALTER WAREHOUSE analytics_mcw SUSPEND CLUSTER 2;

-- Resume a specific cluster
ALTER WAREHOUSE analytics_mcw RESUME CLUSTER 2;

-- Check active clusters for a warehouse
SELECT
  WAREHOUSE_NAME,
  CLUSTER_NUMBER,
  CLUSTER_STATE,
  ACTIVE_QUERY_COUNT,
  QUEUED_OVERLOAD_QUERIES,
  QUERY_AVERGAGE_EXECUTION_TIME_MS
FROM TABLE(WAREHOUSE_LOAD_HISTORY(
  DATE_RANGE_START => DATEADD('hour', -1, CURRENT_DATE()),
  DATE_RANGE_END => CURRENT_DATE()
))
WHERE WAREHOUSE_NAME = 'ANALYTICS_MCW'
ORDER BY CLUSTER_NUMBER;

-- Monitor cluster scaling events
SELECT
  WAREHOUSE_NAME,
  EVENT_NAME,
  EVENT_TIMESTAMP,
  CLUSTER_NUMBER,
  NEW_CLUSTER_COUNT
FROM TABLE(WAREHOUSE_EVENT_HISTORY(
  DATE_RANGE_START => DATEADD('day', -7, CURRENT_DATE())
))
WHERE WAREHOUSE_NAME = 'ANALYTICS_MCW'
  AND EVENT_NAME IN ('MULTI_CLUSTER_UPSCALE', 'MULTI_CLUSTER_DOWNSCALE')
ORDER BY EVENT_TIMESTAMP DESC;

-- Analyze query distribution across clusters
SELECT
  CLUSTER_NUMBER,
  COUNT(*) AS query_count,
  AVG(EXECUTION_TIME_MS) / 1000 AS avg_exec_seconds,
  P50_EXECUTION_TIME_MS / 1000 AS median_exec_seconds
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  DATE_RANGE_START => DATEADD('day', -1, CURRENT_DATE())
))
WHERE WAREHOUSE_NAME = 'ANALYTICS_MCW'
GROUP BY 1
ORDER BY 1;