Core Components Creating Warehouses
🏭 Core Components & Sample Code: Creating Warehouses
Core Components of a Warehouse
-
Warehouse Size
Defines compute capacity (e.g., XSMALL, SMALL, MEDIUM, LARGE). Bigger sizes offer more CPU and memory but cost more. -
Auto-Suspend & Auto-Resume
- Auto-suspend pauses warehouse after idle time to save costs.
- Auto-resume restarts warehouse when a query arrives.
- Scaling Policy
Controls scaling behavior:
- CONCURRENCY (multi-cluster scaling) or vertical scaling.
- Policies like ECONOMY or STANDARD affect cost/performance tradeoffs.
-
Resource Monitoring & Quotas
Set limits to manage cost and concurrency. -
Access Control
Grant roles/users permission to create, manage, or use warehouses. -
Integration with Schedulers
Link warehouses with job schedulers or orchestrators for automated workloads.
Sample SQL to Create a Warehouse
CREATE WAREHOUSE my_warehouse
WITH
WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 3
SCALING_POLICY = 'ECONOMY'
COMMENT = 'Warehouse for ETL processing and analytics';
-- Grant usage permission
GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE analyst_role;
-- Alter warehouse size
ALTER WAREHOUSE my_warehouse SET WAREHOUSE_SIZE = 'LARGE';
---
Summary:
This configuration balances cost and performance by auto-suspending idle warehouses and enabling auto-resume. Multi-cluster scaling with min/max cluster counts supports concurrency spikes. Permissions ensure secure access control.