Warehouses SQL vs Snowsight

0.1Check your role, edition and region

• Creating warehouses: SYSADMIN or a higher role has CREATE WAREHOUSE by default. This guide uses SYSADMIN. To let a custom role create warehouses, grant it CREATE WAREHOUSE ON ACCOUNT.
• Managing all warehouses: MANAGE WAREHOUSES ON ACCOUNT gives a role MODIFY, MONITOR and OPERATE on every warehouse, without owning them.
• Resource monitors: only ACCOUNTADMIN can create them, and only account administrators see them in Snowsight.
• Edition: multi-cluster warehouses and Adaptive Warehouses need Enterprise Edition or higher.
• Region: Gen2 is available in almost every region. Adaptive Warehouses are generally available in select regions, including AWS Asia Pacific (Jakarta), AWS Asia Pacific (Singapore) and Azure Southeast Asia (Singapore).

0.2Create the role that runs queries

The warehouse is owned by SYSADMIN. ANALYST_ROLE is the role your users and tools will query with. Granting it to SYSADMIN keeps the role hierarchy tidy.

USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS ANALYST_ROLE;
GRANT ROLE ANALYST_ROLE TO USER <user_name>;
GRANT ROLE ANALYST_ROLE TO ROLE SYSADMIN;

In Snowsight: Governance & security›Users & roles, open the Roles tab, click + Role, enter the name and click Create Role. Then open the role and use Grant to User.

A1Create a standard warehouse

USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS ANALYTICS_WH
  WAREHOUSE_SIZE = XSMALL
  GENERATION = '2'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Analytics and BI queries';

WAREHOUSE_SIZE

Default XSMALL. Each size up doubles the compute and the credits per hour. Start small and size up only when single queries are too slow.

GENERATION

'2' is the default where Gen2 is available, so this line only makes it explicit. Gen2 goes up to X4LARGE.

AUTO_SUSPEND

Seconds of inactivity before it suspends. The default is 600 (10 minutes). Use about 60 for batch and ad hoc work, and about 600 for BI warehouses so the cache stays warm. Avoid 0 and NULL.

AUTO_RESUME

Default TRUE: the next query wakes the warehouse up.

INITIALLY_SUSPENDED

Default FALSE, which starts the warehouse, and its billing, as soon as it is created. TRUE waits for the first query.

New Gen2 warehouses get the Query Acceleration Service turned on, with a scale factor of 2. To leave it off, add ENABLE_QUERY_ACCELERATION = FALSE. Changing an existing Gen1 warehouse to Gen2 does not change its QAS setting.

Check the result

SHOW WAREHOUSES LIKE 'ANALYTICS_WH'
  ->> SELECT "name", "state", "type", "size", "resource_constraint", "max_cluster_count"
      FROM $1;

resource_constraint shows STANDARD_GEN_2 for Gen2. To switch generation later, run ALTER WAREHOUSE ... SET GENERATION = '1' or '2', running or suspended.

A2Use, resize, suspend and resume

USE ROLE SYSADMIN;
USE WAREHOUSE ANALYTICS_WH;

-- Resize at any time. Running queries finish on the current size.
ALTER WAREHOUSE ANALYTICS_WH SET WAREHOUSE_SIZE = SMALL WAIT_FOR_COMPLETION = TRUE;

-- Stop and start it by hand
ALTER WAREHOUSE ANALYTICS_WH SUSPEND;
ALTER WAREHOUSE ANALYTICS_WH RESUME IF SUSPENDED;

• Resizing a running warehouse: new compute serves queued and new queries once it is ready. Queries already running finish on the old size. WAIT_FOR_COMPLETION = TRUE returns only when the new size is ready.
• Resizing a suspended warehouse: nothing starts. The new size applies at the next resume.
• Billing: per second while running, with a 1-minute minimum each time the warehouse resumes.

A3Scale out for users, accelerate big scans

Scale up (a bigger size) makes large, complex queries faster. Scale out (more clusters of the same size) serves more users and queries at once.

-- Multi-cluster (Enterprise Edition): add clusters when queries queue
ALTER WAREHOUSE ANALYTICS_WH SET
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD';

-- Query Acceleration Service: offload parts of large scans
ALTER WAREHOUSE ANALYTICS_WH SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

Auto-scale

MIN_CLUSTER_COUNT lower than MAX_CLUSTER_COUNT. Clusters start and stop with the load. Start here.

Maximized

Min and max are equal, so every cluster runs whenever the warehouse runs.

SCALING_POLICY

'STANDARD' (default) starts a cluster as soon as queries queue. 'ECONOMY' saves credits: it starts a cluster only when there is enough work to keep it busy for about 6 minutes, so queries can queue longer.

QUERY_ACCELERATION_MAX_SCALE_FACTOR

0 to 100, default 8. Caps the extra serverless compute that QAS may add, as a multiple of the warehouse size. New multi-cluster warehouses also get QAS turned on with a scale factor of 8.

In SQL, the highest MAX_CLUSTER_COUNT depends on the warehouse size. Snowsight lets you choose up to 10.

A4Give roles access

USE ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE ANALYTICS_WH TO ROLE ANALYST_ROLE;

-- Optional: let the role suspend, resume and resize it
GRANT OPERATE, MODIFY ON WAREHOUSE ANALYTICS_WH TO ROLE ANALYST_ROLE;

-- Default warehouse at sign-in. Run as the role that owns the user.
USE ROLE USERADMIN;
ALTER USER <user_name> SET DEFAULT_WAREHOUSE = ANALYTICS_WH;

USAGE

Run queries on the warehouse. With auto-resume on, a query also wakes it up.

OPERATE

Suspend and resume it, and stop its running queries.

MODIFY

Change its properties, such as size and clusters.

MONITOR

See its queries and usage.

A session uses the default warehouse of the user first. Tools can override it in their connection settings, and USE WAREHOUSE changes it at any time.

A5Create or convert an Adaptive Warehouse

An Adaptive Warehouse has no size, clusters, QAS or suspend settings. Snowflake decides the compute for every query, and you set two limits.

USE ROLE SYSADMIN;

-- A new Adaptive Warehouse, shown with the default values
CREATE ADAPTIVE WAREHOUSE IF NOT EXISTS ANALYTICS_ADAPTIVE_WH
  WITH MAX_QUERY_PERFORMANCE_LEVEL = XLARGE
       QUERY_THROUGHPUT_MULTIPLIER = 2;

GRANT USAGE ON WAREHOUSE ANALYTICS_ADAPTIVE_WH TO ROLE ANALYST_ROLE;
SHOW WAREHOUSES LIKE 'ANALYTICS_ADAPTIVE_WH';

MAX_QUERY_PERFORMANCE_LEVEL

XSMALL to X4LARGE, default XLARGE. The upper bound of performance for any single query, in t-shirt sizes.

QUERY_THROUGHPUT_MULTIPLIER

Default 2, the lowest finite value. 0 means unlimited, and 1 is not allowed. Raise it if queries queue; lower it to limit sudden spikes in spend.

Convert, tune, enable or disable

-- Convert a standard warehouse. Online: no downtime.
ALTER WAREHOUSE ANALYTICS_WH SET WAREHOUSE_TYPE = 'ADAPTIVE';

-- Tune it later, or go back to standard
ALTER WAREHOUSE ANALYTICS_WH SET MAX_QUERY_PERFORMANCE_LEVEL = LARGE QUERY_THROUGHPUT_MULTIPLIER = 4;
ALTER WAREHOUSE ANALYTICS_WH SET WAREHOUSE_TYPE = 'STANDARD';

-- Stop an Adaptive Warehouse from taking new queries, then allow them again
ALTER WAREHOUSE ANALYTICS_ADAPTIVE_WH DISABLE;
ALTER WAREHOUSE ANALYTICS_ADAPTIVE_WH ENABLE;

• Conversion: Snowflake derives both limits from the old size, cluster count, QAS scale factor and generation. Running queries finish on the old compute while new ones use the new type, and you pay for both until they finish.
• Billing: per query, reported as warehouse credits. Creating one is free; charges start with the first query.
• Not supported yet: converting X5LARGE or X6LARGE warehouses, and Snowpark-optimized or interactive warehouses.
• When to stay on Gen2: mostly HTAP workloads, or when you need direct control over size and scaling policy.

A6Cap spend with a resource monitor

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE RESOURCE MONITOR ANALYTICS_RM
  WITH CREDIT_QUOTA = 100
  TRIGGERS ON 75 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND
           ON 110 PERCENT DO SUSPEND_IMMEDIATE;

-- A monitor tracks nothing until you assign it
ALTER WAREHOUSE ANALYTICS_WH SET RESOURCE_MONITOR = ANALYTICS_RM;
ALTER WAREHOUSE ANALYTICS_ADAPTIVE_WH SET RESOURCE_MONITOR = ANALYTICS_RM;

• Schedule: without a schedule, the monitor starts now and resets at the start of each month.
• Actions: SUSPEND lets running queries finish. SUSPEND_IMMEDIATE cancels them. On an Adaptive Warehouse, both disable it, and it is enabled again when the monitor conditions clear.
• Whole account: ALTER ACCOUNT SET RESOURCE_MONITOR = ... watches every warehouse you created, but not serverless features.
• Notifications: each account administrator turns them on in Snowsight, under the user menu, Profile, Notifications, with a verified email address.

A7Watch load and credits

USE ROLE ACCOUNTADMIN;

-- Credits per warehouse over the last 7 days. ACCOUNT_USAGE data can lag by a few hours.
SELECT warehouse_name, SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY credits DESC;

For Adaptive Warehouses, QUERY_METERING_HISTORY shows the credits of each query. Budgets also work on both warehouse types.

B1Open Warehouses

1. Sign in to Snowsight. Open your user menu at the bottom left, select Switch role and choose SYSADMIN.
2. In the left navigation, select Compute›Warehouses.
3. The table lists each warehouse with its status, size, clusters, running and queued queries, owner and last resume. Search, or filter by status or size.

Add more columns to the table, such as QAS (Scale Factor), Scaling Policy, Auto Resume, Auto Suspend and Created.

B2Create a warehouse

1. Click + Warehouse. The dialog header shows the role that will own the warehouse, for example Creating as SYSADMIN.
2. Enter the name ANALYTICS_WH. Optionally click + Add Comment.
3. Open Type and choose Standard (Gen2). For Adaptive, see B6.
4. Open Size and choose X-Small.
5. Expand Advanced Options. Keep Auto resume and Auto suspend ticked, and set Suspend After to 1 minute. Leave multi-cluster and Query Acceleration for B4.
6. Click Create Warehouse.

If Type shows only Standard, check the generation afterwards with SHOW WAREHOUSES A1 and set GENERATION with SQL if needed. Also check whether Query Acceleration is ticked before you create the warehouse.

B3Resize, suspend or resume

1. In Compute›Warehouses, click the warehouse name to open it.
2. Open the More menu (...) and choose Suspend or Resume.
3. To resize, choose Edit in the same menu, pick a new Size and click Save Warehouse.

B4Scale out and accelerate

1. Open Edit for the warehouse, or expand Advanced Options while you create it.
2. Turn on Multi-cluster Warehouse. It needs Enterprise Edition.
3. Choose a Mode: Auto-scale starts and stops clusters with the load; Maximized runs them all.
4. Set Min Clusters and Max Clusters. Snowsight goes up to 10.
5. Choose a Scaling Policy: Standard to avoid queuing, or Economy to save credits. It only appears when Max Clusters is higher than Min Clusters.
6. Turn on Query Acceleration and drag Scale Factor if some large scans are slow.
7. Click Save Warehouse.

B5Give roles access

1. Open the warehouse in Compute›Warehouses.
2. In the Privileges section, click + Privilege.
3. Select the role ANALYST_ROLE and the privilege USAGE. Add OPERATE or MODIFY if the role should suspend, resume or resize it.
4. Click Grant Privileges.

The same section shows existing grants and lets you revoke them. To set a default warehouse for a user, edit the user in Governance & security›Users & roles, or use ALTER USER A4.

B6Create or convert an Adaptive Warehouse

Create one

1. In Compute›Warehouses, click + Warehouse.
2. Enter the name ANALYTICS_ADAPTIVE_WH and set Type to Adaptive. The dialog no longer asks for a size.
3. Optionally expand Advanced Options and set Max query performance level (default XLarge) and Query throughput multiplier (default 2, or 0 for unlimited).
4. Click Create Warehouse, then grant USAGE as in B5.

Convert a standard warehouse

1. Open the warehouse in Compute›Warehouses.
2. Open the More menu (...), choose Convert to Adaptive Warehouse and confirm. Running queries finish on the old compute; new queries use Adaptive.

Enabling or disabling an Adaptive Warehouse is done with SQL A5.

B7Cap spend with a resource monitor

1. Switch role to ACCOUNTADMIN.
2. Select Admin›Cost management, open Resource Monitors and click + Resource Monitor.
3. Enter the Name ANALYTICS_RM and the Credit Quota 100.
4. Set Monitor Type to Warehouse and select ANALYTICS_WH and ANALYTICS_ADAPTIVE_WH. Snowsight asks for the warehouses now; choose Account to watch the whole account instead.
5. Keep the default Schedule (starts now, resets monthly), or click Customize.
6. Under Actions, enter a percentage next to each action you want, for example 75 to notify, 100 to suspend and 110 to suspend immediately. Click Add for more notifications, up to five.
7. Click Create Resource Monitor.

To receive alerts, select your user name, then Profile, and under Notifications select Enable notifications from resource monitors. Your email address must be verified first.

B8Watch load

1. Open the warehouse in Compute›Warehouses.
2. Warehouse Activity charts running, queued, provisioning and locked load. Switch between Hour, Day, Week and 2 Weeks.
3. Read it: queued load that keeps appearing calls for more clusters B4. Single slow queries call for a bigger size B3. Load near zero most of the day means you can size down or share the warehouse.
4. The Details section shows status, size, clusters, scaling policy, auto-suspend and auto-resume.