Skip to main content

Core Components Creating Warehouses

🏭 Core Components & Sample Code: Creating Warehouses

Core Components of a Warehouse​

  1. Warehouse Size
    Defines compute capacity (e.g., XSMALL, SMALL, MEDIUM, LARGE). Bigger sizes offer more CPU and memory but cost more.

  2. Auto-Suspend & Auto-Resume

  • Auto-suspend pauses warehouse after idle time to save costs.
  • Auto-resume restarts warehouse when a query arrives.
  1. Scaling Policy
    Controls scaling behavior:
  • CONCURRENCY (multi-cluster scaling) or vertical scaling.
  • Policies like ECONOMY or STANDARD affect cost/performance tradeoffs.
  1. Resource Monitoring & Quotas
    Set limits to manage cost and concurrency.

  2. Access Control
    Grant roles/users permission to create, manage, or use warehouses.

  3. 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.