1-- Full CREATE WAREHOUSE with all key parameters
2CREATE WAREHOUSE etl_warehouse
3WAREHOUSE_SIZE         = 'LARGE'          -- compute size
4WAREHOUSE_TYPE         = 'STANDARD'       -- STANDARD or SNOWPARK-OPTIMIZED
5AUTO_SUSPEND           = 300              -- suspend after 5 minutes idle
6AUTO_RESUME            = TRUE             -- auto-resume on query submission
7INITIALLY_SUSPENDED    = TRUE             -- start in suspended state
8MAX_CONCURRENCY_LEVEL  = 8               -- max concurrent queries before queuing
9STATEMENT_TIMEOUT_IN_SECONDS = 3600      -- kill queries running > 1 hour
10STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600 -- timeout queued queries after 10 min
11COMMENT = 'Nightly ETL warehouse — Large, auto-suspended';
12
13-- Create a minimal warehouse with sensible defaults
14CREATE WAREHOUSE reporting_wh
15WAREHOUSE_SIZE = 'SMALL'
16AUTO_SUSPEND   = 120
17AUTO_RESUME    = TRUE
18INITIALLY_SUSPENDED = TRUE;
19
20-- Verify warehouse was created
21SHOW WAREHOUSES LIKE 'etl_warehouse';

1-- Set the active warehouse for the current session
2USE WAREHOUSE reporting_wh;
3
4-- Resize a warehouse (takes effect near-instantly)
5ALTER WAREHOUSE etl_warehouse
6SET WAREHOUSE_SIZE = 'X-LARGE';
7
8-- Change auto-suspend interval
9ALTER WAREHOUSE reporting_wh
10SET AUTO_SUSPEND = 60;
11
12-- Disable auto-suspend (warehouse runs until manually stopped)
13ALTER WAREHOUSE etl_warehouse
14SET AUTO_SUSPEND = NULL;
15
16-- Manually suspend a warehouse immediately
17ALTER WAREHOUSE reporting_wh SUSPEND;
18
19-- Manually resume a suspended warehouse
20ALTER WAREHOUSE reporting_wh RESUME;
21
22-- Resize and change multiple settings in one ALTER
23ALTER WAREHOUSE etl_warehouse SET
24WAREHOUSE_SIZE        = 'MEDIUM'
25AUTO_SUSPEND          = 300
26MAX_CONCURRENCY_LEVEL = 4;
27
28-- Change the statement timeout
29ALTER WAREHOUSE reporting_wh
30SET STATEMENT_TIMEOUT_IN_SECONDS = 1800;  -- 30 minutes
31
32-- Show current warehouse status
33SHOW WAREHOUSES;

1-- Multi-cluster warehouse in Auto-scale mode (Enterprise edition required)
2CREATE WAREHOUSE bi_warehouse
3WAREHOUSE_SIZE      = 'MEDIUM'
4MIN_CLUSTER_COUNT   = 1           -- minimum clusters always running
5MAX_CLUSTER_COUNT   = 4           -- maximum clusters allowed
6SCALING_POLICY      = 'STANDARD'  -- STANDARD or ECONOMY
7AUTO_SUSPEND        = 300
8AUTO_RESUME         = TRUE
9INITIALLY_SUSPENDED = FALSE
10COMMENT = 'BI warehouse — scales from 1 to 4 clusters automatically';
11
12-- Maximized mode: all clusters always running (fixed concurrency)
13-- Use when you have predictably high, sustained concurrency
14CREATE WAREHOUSE high_concurrency_wh
15WAREHOUSE_SIZE    = 'SMALL'
16MIN_CLUSTER_COUNT = 3           -- always run exactly 3 clusters
17MAX_CLUSTER_COUNT = 3           -- same as min = Maximized mode
18SCALING_POLICY    = 'STANDARD'
19AUTO_SUSPEND      = 600
20AUTO_RESUME       = TRUE;
21
22-- Convert existing single-cluster to multi-cluster
23ALTER WAREHOUSE reporting_wh SET
24MIN_CLUSTER_COUNT = 1
25MAX_CLUSTER_COUNT = 3
26SCALING_POLICY    = 'ECONOMY';
27
28-- Check multi-cluster warehouse status
29SHOW WAREHOUSES LIKE 'bi_warehouse';

1-- Set the maximum concurrent queries before queuing begins
2ALTER WAREHOUSE reporting_wh
3SET MAX_CONCURRENCY_LEVEL = 8;
4
5-- Set timeout for queued queries (seconds)
6-- If a query waits longer than this in the queue, it fails with an error
7ALTER WAREHOUSE reporting_wh
8SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;  -- 5 minutes
9
10-- Set timeout for running statements
11-- Queries running longer than this are automatically killed
12ALTER WAREHOUSE reporting_wh
13SET STATEMENT_TIMEOUT_IN_SECONDS = 7200;  -- 2 hours
14
15-- Check current queue depth and active queries
16SELECT *
17FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
18DATE_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP()),
19DATE_RANGE_END   => CURRENT_TIMESTAMP(),
20WAREHOUSE_NAME   => 'REPORTING_WH'
21));
22
23-- View current and recently queued queries
24SELECT
25query_id,
26user_name,
27warehouse_name,
28execution_status,
29queued_provisioning_time / 1000  AS queued_seconds,
30execution_time / 1000            AS execution_seconds,
31LEFT(query_text, 100)            AS query_preview
32FROM snowflake.account_usage.query_history
33WHERE warehouse_name = 'REPORTING_WH'
34AND start_time >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
35ORDER BY start_time DESC;

1-- 1. Credit consumption by warehouse over the last 7 days (ACCOUNT_USAGE)
2SELECT
3warehouse_name,
4SUM(credits_used)           AS total_credits,
5SUM(credits_used_compute)   AS compute_credits,
6SUM(credits_used_cloud_services) AS cloud_service_credits,
7COUNT(*)                    AS billing_periods
8FROM snowflake.account_usage.warehouse_metering_history
9WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
10GROUP BY warehouse_name
11ORDER BY total_credits DESC;
12
13-- 2. Warehouse load over time (INFORMATION_SCHEMA — current DB, near real-time)
14SELECT
15start_time,
16end_time,
17warehouse_name,
18avg_running,    -- average concurrent queries running
19avg_queued_load -- average queries in the queue
20FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
21DATE_RANGE_START => DATEADD('hour', -4, CURRENT_TIMESTAMP()),
22WAREHOUSE_NAME   => 'REPORTING_WH'
23))
24ORDER BY start_time DESC;
25
26-- 3. Recent query performance on a specific warehouse
27SELECT
28query_id,
29user_name,
30query_type,
31execution_status,
32ROUND(execution_time / 1000, 2)        AS exec_seconds,
33ROUND(bytes_scanned / POWER(1024,3), 4) AS gb_scanned,
34partitions_scanned,
35partitions_total,
36ROUND(partitions_scanned / NULLIF(partitions_total, 0) * 100, 1) AS pct_scanned,
37LEFT(query_text, 200)                   AS query_preview
38FROM snowflake.account_usage.query_history
39WHERE warehouse_name = 'ETL_WAREHOUSE'
40AND start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
41AND execution_status = 'SUCCESS'
42ORDER BY execution_time DESC
43LIMIT 50;
44
45-- 4. Identify warehouses with high queuing
46SELECT
47warehouse_name,
48AVG(avg_queued_load) AS avg_queue_depth,
49MAX(avg_queued_load) AS max_queue_depth
50FROM snowflake.account_usage.warehouse_load_history
51WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
52GROUP BY warehouse_name
53HAVING avg_queue_depth > 0.5
54ORDER BY avg_queue_depth DESC;

1-- Create an account-level resource monitor
2-- Monitors all credit usage in the account per month
3CREATE RESOURCE MONITOR account_monthly_monitor
4WITH CREDIT_QUOTA = 5000                -- 5,000 credits per period
5FREQUENCY        = MONTHLY              -- reset at start of each month
6START_TIMESTAMP  = IMMEDIATELY
7TRIGGERS
8  ON 75 PERCENT DO NOTIFY              -- email alert at 75%
9  ON 90 PERCENT DO NOTIFY              -- email alert at 90%
10  ON 100 PERCENT DO SUSPEND            -- suspend warehouses gracefully at 100%
11  ON 110 PERCENT DO SUSPEND_IMMEDIATE; -- force-kill at 110% (overage protection)
12
13-- Apply the resource monitor at the account level
14ALTER ACCOUNT SET RESOURCE_MONITOR = account_monthly_monitor;
15
16-- Create a warehouse-specific resource monitor
17CREATE RESOURCE MONITOR etl_weekly_monitor
18WITH CREDIT_QUOTA = 200              -- 200 credits per week
19FREQUENCY        = WEEKLY
20START_TIMESTAMP  = IMMEDIATELY
21TRIGGERS
22  ON 80 PERCENT DO NOTIFY
23  ON 100 PERCENT DO SUSPEND_IMMEDIATE;
24
25-- Assign resource monitor to a specific warehouse
26ALTER WAREHOUSE etl_warehouse
27SET RESOURCE_MONITOR = etl_weekly_monitor;
28
29-- View all resource monitors and their current usage
30SHOW RESOURCE MONITORS;
31
32-- View credit usage for resource monitors
33SELECT *
34FROM snowflake.account_usage.resource_monitors
35ORDER BY created_on DESC;

1-- Create a Snowpark-Optimized warehouse for ML workloads
2-- These warehouses have ~16x more memory per node than STANDARD warehouses
3CREATE WAREHOUSE ml_training_wh
4WAREHOUSE_SIZE = 'MEDIUM'
5WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
6AUTO_SUSPEND   = 600
7AUTO_RESUME    = TRUE
8INITIALLY_SUSPENDED = TRUE
9COMMENT = 'Snowpark ML training — high memory per node';
10
11-- Standard warehouses for regular SQL and Python UDFs
12CREATE WAREHOUSE analytics_wh
13WAREHOUSE_SIZE = 'SMALL'
14WAREHOUSE_TYPE = 'STANDARD'   -- default type
15AUTO_SUSPEND   = 180
16AUTO_RESUME    = TRUE;