1. Warehouses: Essential Processing Units
Snowflake virtual warehouses serve as the core computational units responsible for running all SQL queries and data manipulation language (DML) operations, including loading, unloading, and updating data within tables.
2. Warehouse Properties and Functionality
Each warehouse is characterized by two key attributes:
Type: Categorized as either Standard or Snowpark-optimized, catering to different workload requirements.
Size: Defines the available compute resources, measured in virtual CPUs and memory.
Additionally, various configurable properties enable granular control and automation of warehouse activity.
3. Dynamic Resource Management
Snowflake offers unparalleled flexibility in managing warehouses:
Start and Stop on Demand: Warehouses can be activated and deactivated instantly as needed.
Responsive Scaling: Resizing is possible at any time, even during active operation, to adjust compute resources according to the intensity and type of warehouse tasks.
Warehouse Size:
Determines available compute resources per cluster.
Sizes: X-Small to 6X-Large, with increasing credits per hour and second.
Notes:
X-Small and Small are defaults for Snowsight and CREATE WAREHOUSE.
X-Large is default for Classic Console.
5X-Large and 6X-Large are in preview for US Government and Azure regions.
Impact on Credit Usage and Billing:
Billing: Per-second, with a 60-second minimum on startup.
Impact: Doubles with each size upgrade per full hour.
Example: Credits consumed for different sizes with varying running times.
Impact on Data Loading:
Increasing size doesn't always improve loading performance.
Tip: Smaller warehouses (Small-Large) suffice for most scenarios.
Impact on Query Processing:
Larger size can improve execution time, especially for complex queries.
Resizing: Provides more resources for running and queued queries.
Tip: Large size not necessarily faster for small, basic queries.
Auto-suspension and Auto-resumption:
Auto-suspend: Deactivates warehouse after inactivity (default enabled).
Auto-resume: Activates warehouse on incoming query (default enabled).
Benefit: Simplifies monitoring and usage, avoiding unnecessary credit consumption.
Query Processing and Concurrency:
Warehouse size and query complexity determine concurrent queries.
Queued queries: Wait for resources as others complete.
Control mechanisms: STATEMENT_QUEUED_TIMEOUT_IN_ SECONDS and STATEMENT_TIMEOUT_IN_SECONDS.
Alternatives: Create new or resize existing warehouse, or leverage multi-cluster warehouses (Enterprise Edition).
Warehouse Usage in Sessions:
Sessions initially lack associated warehouses, preventing queries.
Default warehouses:
User-specific: Set during creation/modification.
Client utilities/drivers/connectors: Specified in configuration files or connection parameters.
Precedence: User > Client defaults > Command line/driver parameters.
Change: USE WAREHOUSE command within a session.
