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".
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Instruções da origem · Visualização somente leitura
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 <jeremy@intentsolutions.io>
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.
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_suspendSELECT name, size, auto_suspend, state
FROM INFORMATION_SCHEMA.WAREHOUSES
WHERE auto_suspend >600OR auto_suspend =0;
Pitfall #2: Using ACCOUNTADMIN for Everything
Anti-Pattern:
-- Human users with ACCOUNTADMIN default roleALTERUSER analyst SET DEFAULT_ROLE ='ACCOUNTADMIN';
-- One bad query can drop production databases
Fix:
-- Use least-privilege rolesALTERUSER analyst SET DEFAULT_ROLE = ;
grantee_name, role
SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
role deleted_on ;
'DATA_ANALYST'
-- Audit ACCOUNTADMIN usage
SELECT
FROM
WHERE
=
'ACCOUNTADMIN'
AND
IS
NULL
-- Should be < 3 users, all named admins
Pitfall #3: SELECT * on Wide Tables
Anti-Pattern:
-- Scans ALL columns (Snowflake stores columnar — unused cols waste I/O)SELECT*FROM events; -- 200 columns, only need 3
-- Clustering key on a 10,000 row tableALTER TABLE config_settings CLUSTER BY (category);
-- Costs credits for reclustering with zero performance benefit
Fix:
-- Only cluster tables > 1TB with frequent filter queries-- Check table size before clusteringSELECT table_name, row_count, bytes /1e9AS gb
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name ='CONFIG_SETTINGS';
-- If < 1 GB, clustering is waste-- Remove unnecessary clusteringALTER TABLE config_settings DROP CLUSTERING KEY;
Pitfall #5: Not Using MERGE for Idempotent Loads
Anti-Pattern:
-- INSERT creates duplicates on retryINSERT INTO dim_orders SELECT*FROM staging_orders;
-- Network blip → retry → duplicate rows
Fix:
-- MERGE is idempotent — safe to retryMERGEINTO dim_orders AS target
USING staging_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THENUPDATESET
target.amount = source.amount,
target.updated_at =CURRENT_TIMESTAMP()
WHENNOT MATCHED THENINSERT
(order_id, amount, created_at)
VALUES (source.order_id, source.amount, CURRENT_TIMESTAMP());
Pitfall #6: Ignoring Stale Streams
Anti-Pattern:
-- Stream goes stale when retention period is exceeded-- (source table changes exceed DATA_RETENTION_TIME_IN_DAYS)-- Result: DATA LOSS — changes between old and new offset are gone
Fix:
-- Monitor stream stalenessSELECT stream_name, stale
FROM INFORMATION_SCHEMA.STREAMS
WHERE stale =TRUE;
-- Increase retention on source tablesALTER TABLE raw_orders SET DATA_RETENTION_TIME_IN_DAYS =14;
-- Set up alert for stale streamsCREATE ALERT stale_stream_alert
WAREHOUSE = ADMIN_WH
SCHEDULE ='30 MINUTE'
IF (EXISTS (SELECT1FROM INFORMATION_SCHEMA.STREAMS WHERE stale =TRUE))
THENCALLSYSTEM$SEND_EMAIL(...);
Pitfall #7: Loading Many Small Files
Anti-Pattern:
# 100,000 small files (< 100KB each) in stage# Each file = separate micro-partition = metadata overhead
Fix:
-- Combine small files before loading-- Or use Snowpipe with recommended file sizes (100-250 MB)-- Check COPY history for file size issuesSELECT file_name, file_size, row_count
FROMTABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME =>'MY_TABLE',
START_TIME => DATEADD(hours, -24, CURRENT_TIMESTAMP())
))
WHERE file_size <100000-- Files under 100KBORDERBY file_size;
Pitfall #8: No Resource Monitors
Anti-Pattern:
-- No resource monitors = unlimited credit consumption-- A runaway query or always-on warehouse can burn thousands of credits
Fix:
CREATE RESOURCE MONITOR monthly_budget
WITH CREDIT_QUOTA =2000
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON75PERCENT DO NOTIFY
ON100PERCENT DO SUSPEND
ON110PERCENT DO SUSPEND_IMMEDIATE;
ALTER ACCOUNT SET RESOURCE_MONITOR = monthly_budget;
Pitfall #9: Using Transient Tables for Important Data
Anti-Pattern:
-- Transient tables have NO Fail-safe (7 days of extra recovery)-- and max 1 day of Time TravelCREATE TRANSIENT TABLE critical_orders (...);
-- Data loss risk if table is accidentally dropped after 1 day
Fix:
-- Use permanent tables for important dataCREATE TABLE critical_orders (...);
ALTER TABLE critical_orders SET DATA_RETENTION_TIME_IN_DAYS =14;
-- Use transient only for truly temporary dataCREATE TRANSIENT TABLE temp_staging_batch (...);
Pitfall #10: Wrong Account Identifier Format
Anti-Pattern:
// Using the full URL instead of account identifierconst conn = snowflake.createConnection({
account: 'myaccount.us-east-1.snowflakecomputing.com', // WRONG
});
// Results in: "Could not connect to Snowflake backend"
Fix:
const conn = snowflake.createConnection({
account: 'myorg-myaccount', // Correct: orgname-accountname format
});
// For legacy locator format: 'xy12345.us-east-1' (include region)
Quick Audit Script
-- Run this monthly to catch common pitfallsSELECT'Always-on warehouses'AScheck,
COUNT(*) AS issues
FROM INFORMATION_SCHEMA.WAREHOUSES
WHERE auto_suspend =0OR auto_suspend >3600UNIONALLSELECT'ACCOUNTADMIN default role',
COUNT(*)
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
WHERE default_role ='ACCOUNTADMIN'AND disabled ='false'UNIONALLSELECT'Stale streams',
COUNT(*)
FROM INFORMATION_SCHEMA.STREAMS
WHERE stale =TRUEUNIONALLSELECT'No resource monitor',
CASEWHENCOUNT(*) =0THEN1ELSE0ENDFROM INFORMATION_SCHEMA.RESOURCE_MONITORS
UNIONALLSELECT'Tables without clustering (>1TB)',
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE bytes >1e12AND auto_clustering_on ='NO';
