Working with Warehouses

243 viewsConnecting to SnowflakeVirtualwarehouses
0

Working with Warehouses

Alejandro Penzini Answered question December 19, 2023
0

Managing Warehouses:

Snowflake offers two options for managing your warehouses:

1. Web Interface:

Access the Admin > Warehouses > Warehouse panel in Snowsight.

In the Classic Console, navigate to Warehouses > Create.

2. DDL Commands:

Utilize the CREATE WAREHOUSE command.

Warehouse Creation:

Specify the initial state of the warehouse during creation:

Started: Resources provisioned immediately, incurring credit consumption.
Suspended: Delayed resource provisioning, minimizing initial costs.

Starting and Resuming Warehouses:

Initial Creation: Warehouses can be started during initial creation or at any later point.
Resuming: Once created, resuming a warehouse is identical to starting.

Suspended Warehouses:

Use the following methods to resume a suspended warehouse:

Web Interface: Navigate to Admin > Warehouses > > Resume in Snowsight, or Warehouses > > Resume in the Classic Console.
SQL: Execute an ALTER WAREHOUSE RESUME command.
Startup and Credit Consumption:

Resource Provisioning: Starting typically takes seconds, but may occasionally be longer during resource provisioning.
Credit Billing: Warehouses begin consuming credits when all resources are provisioned, except in rare cases of partial provisioning where billing reflects only provisioned resources. Once remaining resources are provisioned, full billing resumes.
Billing Minimum: Resumes incur a 1-minute minimum billing, though usage is reported in hourly increments.

SQL Processing:

Snowflake delays executing submitted statements until all resources are provisioned, except during rare provisioning failures.
Partial Provisioning: In such cases, processing begins once 50% or more of resources are available.

Suspending Warehouses:

Running warehouses can be suspended at any time, even during active SQL execution. This action halts credit consumption upon resource shutdown.

Suspension Methods:

Web Interface: Navigate to Admin > Warehouses > > Suspend in Snowsight or Warehouses > > Suspend in the Classic Console.
SQL: Execute an ALTER WAREHOUSE SUSPEND command.

Suspension Process:

Idle Resource Shutdown: Snowflake immediately closes all idle compute resources.
Active Statement Completion: Resources actively executing statements are allowed to finish.
Resource Shutdown & Suspension: Upon statement completion, remaining resources shut down, transitioning the warehouse to "Suspended" status.

Note: Resources awaiting shutdown are in "quiesce" mode.

Resizing Warehouses for Dynamic Resource Allocation:

Snowflake empowers you to adjust warehouse size (up or down) at any time, even during active query processing.

Resizing Methods:

Web Interface:
Snowsight: Navigate to Admin > Warehouses > > Edit.
Classic Console: Access Warehouses > > Configure.
SQL: Execute ALTER WAREHOUSE SET WAREHOUSE_SIZE = .

Benefits of Resizing Up:

Consider increasing warehouse size to enhance performance in scenarios like:

Executing large, complex queries against extensive datasets.
Loading or unloading significant data volumes.

Alejandro Penzini Answered question December 19, 2023
You are viewing 1 out of 1 answers, click here to view all answers.
Feedback on Q&A