Dedicated Warehouses: Isolate Workloads to Control Costs

Dedicated Warehouses: Isolate Workloads to Control Costs

Using separate warehouses for ETL, BI, and ad-hoc queries prevents expensive queries from blocking cheaper ones. Learn the optimal warehouse architecture.

The Problem

When all queries run on the same warehouse: expensive ad-hoc queries slow down quick dashboards, ETL processes compete with user queries, small queries get queued behind large ones, difficult to attribute costs to teams, and one team's inefficient queries impact everyone.

Warehouse Architecture Strategy

• ETL_WH → Batch loading
• ETL_TRANSFORM_WH → Data transformation
• BI_WH → Dashboards & reports
• ANALYST_WH → Ad-hoc analysis
• DATA_SCIENCE_WH → ML/complex analytics
• DEV_WH → Development & testing

ETL Warehouse

CREATE WAREHOUSE etl_wh WITH
WAREHOUSE_SIZE = 'LARGE'
AUTO_SUSPEND = 60
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1;

BI/Dashboard Warehouse

CREATE WAREHOUSE bi_wh WITH
WAREHOUSE_SIZE = 'SMALL'
AUTO_SUSPEND = 120
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 5;

Real-World Example

A company was running all workloads on a single X-Large warehouse: 50 concurrent BI users, nightly ETL (4-6 hours), ad-hoc analyst queries throughout day, average 20 hours/day running, monthly cost: $43,200.

After implementing dedicated warehouses: BI_WH (Small, multi-cluster) 12 hours/day = $10,800, ETL_WH (Large, single-cluster) 5 hours/day = $7,500, ANALYST_WH (Medium) 6 hours/day = $5,400, total: $23,700/month. Savings: $19,500/month (45% reduction).

Cost Attribution Benefits

-- Chargeback reporting
SELECT
WAREHOUSE_NAME,
SUM(CREDITS_USED) as TOTAL_CREDITS,
SUM(CREDITS_USED) * 3 as APPROXIMATE_COST
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATE_TRUNC('month', CURRENT_DATE())
GROUP BY 1
ORDER BY 2 DESC;

Key Takeaways

• Separate warehouses for ETL, BI, and ad-hoc workloads
• Right-size each warehouse for its specific workload
• Enable clear cost attribution by team/department
• Set appropriate auto-suspend times for each workload type
• Monitor usage and adjust configuration regularly