Snowflake Best Practices

Analogy categoryAnalogies

Snowflake's separation of storage and compute means most of the classic data-warehouse tuning tricks you learned elsewhere (index maintenance, manual partitioning, vacuuming) do not apply. Instead, best practice revolves around right-sizing virtual warehouses, controlling spend with resource monitors, choosing clustering keys deliberately, and locking down access with role-based access control (RBAC) from day one.

🏏

Cricket analogy: Just as a captain sets a fielding plan before the first ball rather than reacting after runs are conceded, Snowflake teams should configure warehouses and RBAC before the first production query runs.

Right-Sizing and Scaling Virtual Warehouses

Because compute cost scales linearly with warehouse size (an X-Small credits at 1x per hour while a Large credits at 8x), the biggest lever for cost control is matching warehouse size to workload rather than defaulting everything to Large. Multi-cluster warehouses should be reserved for high-concurrency BI dashboards, using auto-scale policies with a defined min/max cluster count so Snowflake spins clusters up only under queuing pressure.

🏏

Cricket analogy: Like choosing between a defensive block and a lofted six depending on the required run rate, picking an X-Small for a quick lookup versus a Large for a heavy ETL join matches effort to the task.

Cost Control with Resource Monitors and Auto-Suspend

Every warehouse should have auto-suspend set aggressively (60-300 seconds is common) so idle compute stops billing immediately, and auto-resume enabled so the next query simply wakes it back up transparently. Resource monitors should be attached at the account or warehouse level with credit quotas and notification/suspend actions at 75%, 90%, and 100% thresholds to prevent runaway spend from a misbehaving job or forgotten dashboard refresh.

🏏

Cricket analogy: Like a bowler being immediately taken off after two expensive overs rather than finishing the spell, auto-suspend pulls warehouse compute the moment it stops delivering value.

Query Performance: Clustering and Result Caching

For tables beyond a few hundred GB with predictable filter patterns, defining an explicit clustering key (typically a date or a low-cardinality dimension often filtered on) keeps micro-partitions well-pruned as data grows, reducing partitions scanned. Snowflake also automatically caches query results for 24 hours, so identical repeated queries against unchanged data return instantly from the result cache without consuming any warehouse credits at all.

🏏

Cricket analogy: Like a fielding captain setting a slip cordon specifically for a known edge-prone batsman rather than spreading fielders randomly, clustering keys target the exact filter patterns queries actually use.

sql

-- Right-size and auto-manage a warehouse
CREATE OR REPLACE WAREHOUSE etl_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD';

-- Attach a resource monitor with staged actions
CREATE RESOURCE MONITOR monthly_etl_budget
  WITH CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 75 PERCENT DO NOTIFY
           ON 90 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = monthly_etl_budget;

-- Add a clustering key for a large, date-filtered fact table
ALTER TABLE sales_fact CLUSTER BY (sale_date);

Do not cluster small tables (under roughly 1 TB) or tables without a stable, frequently-filtered column — reclustering itself consumes credits, and on a small table the pruning benefit rarely outweighs that ongoing maintenance cost.

Match warehouse size (X-Small to 6X-Large) to actual query weight instead of defaulting to Large for everything.
Use multi-cluster warehouses with auto-scale for unpredictable, high-concurrency BI workloads.
Set aggressive AUTO_SUSPEND (60-300s) and AUTO_RESUME = TRUE on every warehouse.
Attach resource monitors with staged 75/90/100% triggers to prevent runaway credit spend.
Only cluster large, frequently-filtered tables; reclustering has its own ongoing cost.
Rely on the 24-hour result cache for repeated identical queries — it costs zero credits.
Bake RBAC and naming/tagging conventions into the environment before onboarding real workloads.