Snowflake Best Practices cheat sheet
Warehouse, cost, security, and modeling practices that survive production — plus the defaults you should always change.
Warehouses
auto_suspend = 60- Change this on day one. The 600-second default means every ad-hoc query buys ten minutes of idle compute.
One warehouse per workload, not per team- Separate ETL, BI, and ad-hoc so a dashboard storm cannot starve a load. Tag each one for cost attribution.
Test one size smaller before sizing up- Bigger is sometimes cheaper because queries finish faster, but only measure that — do not assume it either way.
Multi-cluster for concurrency, larger size for single-query speed- Confusing these is the most common sizing mistake. Queued queries need clusters; slow queries need size or better SQL.
statement_timeout_in_seconds on every warehouse- The guardrail against a runaway query consuming a weekend of credits unnoticed.
Cost
Resource monitors at account and workload level- Notify at 75 percent, suspend at 100. A monitor that only notifies is a monitor that gets ignored.
QUERY_TAG set by the orchestrator- Without tags, cost attribution by team or model is impossible. Set it in dbt and Airflow, never by hand.
Review WAREHOUSE_METERING_HISTORY weekly- Fifteen minutes a week catches the new dashboard scanning an unclustered fact table before month-end.
Lower data_retention_time_in_days on high-churn tables- Time Travel storage on a frequently rewritten table can exceed the table itself. Set retention per table, not globally.
Drop unused tables and clones- Abandoned dev clones diverge from their source over time and start billing real storage.
Performance
Read the Query Profile before changing anything- Find the node consuming the time. Resizing a warehouse to fix a missing filter wastes money and fixes nothing.
Filter on the clustering key, not an expression over it- where date(ordered_at) = ... defeats pruning. Compare the column directly to a range.
Cluster only large, selectively filtered tables- Below roughly a terabyte, natural micro-partition ordering is usually enough. Reclustering is a real ongoing cost.
Search Optimization for point lookups- Correct tool for selective equality on high-cardinality columns, where clustering cannot help.
Watch for spilling to remote storage- Remote spill in the profile means the warehouse is too small for the query's memory needs. That is a real size signal.
Security and governance
Functional roles for people, access roles for objects- Two layers. People get functional roles; access roles hold privileges. This is what makes an audit answerable.
Never grant to PUBLIC or use ACCOUNTADMIN for daily work- ACCOUNTADMIN should have almost no query history. Both are standard audit findings.
Tag-based masking policies- Tag a column once and the policy follows it into clones and downstream models. Per-column policies do not scale.
Key-pair auth for service accounts- Password auth for automation is an audit finding. Rotate keys on a schedule you actually keep.
Future grants on schemas- New models become readable without a manual grant after every deploy, which is how permissions drift.
Modeling and pipelines
Declare the grain of every table- One sentence in the model description. Most double-counting incidents are an undeclared grain.
Dedup before MERGE, always- Set ERROR_ON_NONDETERMINISTIC_MERGE and dedup the source. A silent wrong merge is worse than a failed load.
Idempotent loads: delete-insert by partition or MERGE by key- A rerun must produce the same result. Without this, every retry is a data quality risk.
Dynamic Tables for lag contracts, Streams plus Tasks for MERGE control- Choose by what the pipeline needs to express, not by which feature is newer.
Keep tests and version control even with warehouse-native pipelines- A successful refresh means SQL ran. It does not mean the primary key is unique.
Operations
COMMENT ON every warehouse, role, and shared table- Owner and purpose in the comment. Six months later this is the only documentation that still exists.
Zero-copy clone for testing migrations- Clone production, run the change, compare. Cheaper and safer than any staging refresh process.
Test the DR plan, do not just configure it- An untested failover group is a hope, not a plan. Run an actual failover in a drill.
ACCOUNT_USAGE lags up to three hours- Use INFORMATION_SCHEMA table functions during a live incident, ACCOUNT_USAGE for trend analysis.
From DataLane — tutorials at/blog, practice SQL live in theplayground.