snowflake-known-pitfalls

# 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:** ```sql -- 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:** ```sql 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:** ```sql -- Human users with ACCOUNTADMIN default role ALTER USER analyst SET DEFAULT_ROLE = 'ACCOUNTADMIN'; -- One bad query can drop production databases ``` **Fix:** ```sql -- Use least-privilege roles ALTER USER analyst SET DEFAULT_ROLE = 'DATA_ANALYST'; -- Audit ACCOUNTADMIN usage SELECT grantee_name, role FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS WHERE role = 'ACCOUNTADMIN' AND deleted_on IS NULL; -- Should be < 3 users, all named admins ``` --- ## Pitfall #3: SELECT * on Wide Tables **Anti-Pattern:** ```sql -- Scans ALL columns (Snowflake stores columnar — unused cols waste I/O) SELECT * FROM events; -- 200 columns, only need 3 ``` **Fix:** ```sql -- Select only needed columns — dramat...