snowflake-known-pitfalls

name	snowflake-known-pitfalls
description	Identify and avoid Snowflake anti-patterns and common mistakes in SQL, warehouse management, data loading, and access control. Use when reviewing Snowflake configurations, onboarding new users, or auditing existing Snowflake deployments for best practices. Trigger with phrases like "snowflake mistakes", "snowflake anti-patterns", "snowflake pitfalls", "snowflake what not to do", "snowflake code review".
allowed-tools	Read, Grep
version	1.5.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","data-warehouse","analytics","snowflake"]
compatibility	Designed for Claude Code

Snowflake Known Pitfalls

Overview

Common mistakes and anti-patterns when using Snowflake, with real SQL examples and fixes.

Pitfall #1: Leaving Warehouses Running (Cost Killer)

Anti-Pattern:

-- Warehouse with auto_suspend = 0 (never suspends)
CREATE WAREHOUSE ALWAYS_ON_WH
  WAREHOUSE_SIZE = 'XLARGE'
  AUTO_SUSPEND = 0;
-- 16 credits/hour = ~$1,152/day at $3/credit

Fix:

ALTER WAREHOUSE ALWAYS_ON_WH SET
  AUTO_SUSPEND = 120,    -- Suspend after 2 min idle
  AUTO_RESUME = TRUE;    -- Resume on next query

-- Audit all warehouses for high auto_suspend
SELECT name, size, auto_suspend, state
FROM INFORMATION_SCHEMA.WAREHOUSES
WHERE auto_suspend > 600 OR auto_suspend = 0;

---

Pitfall #2: Using ACCOUNTADMIN for Everything

Anti-Pattern:

-- Human users with ACCOUNTADMIN default role
ALTER USER analyst SET DEFAULT_ROLE = 'ACCOUNTADMIN';
-- One bad query can drop production databases

Fix:

-- Use least-privilege roles
ALTER USER analyst SET DEFAULT_ROLE = ;


 grantee_name, role
 SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
 role    deleted_on  ;