Snowflake Best Practices

This comprehensive guide covers proven patterns for optimizing performance, managing costs, ensuring security, and operating Snowflake at scale.

---

Table of Contents

• Performance Optimization
• Cost Management
• Security & Governance
• Data Organization
• Query Optimization
• Warehouse Management
• Monitoring & Observability
• Common Anti-Patterns

---

Performance Optimization

1. Clustering for Large Tables

When to Cluster:

• Table > 1TB in size
• Queries frequently filter on specific columns
• Query performance degraded over time

Clustering Best Practices:

• ✅ Cluster on columns used in WHERE clauses
• ✅ Use 2-4 columns maximum
• ✅ Put high-cardinality columns first
• ❌ Don't cluster small tables (<1TB)
• ❌ Don't cluster on columns with very low cardinality

Example:

---

2. Materialized Views

Use When:

• Expensive aggregations run repeatedly
• Complex joins executed frequently
• Pre-computed results acceptable

Materialized View Best Practices:

• ✅ Use for expensive aggregations
• ✅ Monitor refresh costs
• ✅ Create on stable, frequently-queried data
• ❌ Don't use for rapidly changing data
• ❌ Don't create too many (storage costs add up)

Cost Monitoring:

---

3. Search Optimization Service

Enable for:

• Point lookups on large tables
• Selective filters not in clustering key
• Equality and IN predicates

When to Use:

• ✅ Large fact tables with point lookups
• ✅ Columns not in clustering key
• ✅ Queries with selective filters
• ❌ Small tables
• ❌ Scan-heavy queries
• ❌ Very low selectivity

---

Cost Management

1. Warehouse Sizing Strategy

Right-Sizing Framework:

Workload Type	Warehouse Size	Auto-Suspend	Reasoning
ETL/ELT	Large/X-Large	60-300 sec	Large batches, infrequent
BI Dashboards	Small/Medium	300-600 sec	Concurrent queries, frequent
Ad-hoc Analysis	X-Small/Small	60-180 sec	Sporadic, user-driven
Data Science	Large/X-Large	60 sec	Intensive compute, sporadic
Reporting	Medium	600 sec	Scheduled, predictable

---

2. Resource Monitors (Budget Controls)

---

3. Cost Monitoring Queries

---

4. Cost Optimization Quick Wins

Immediate Actions:

1. Reduce Time Travel (if not needed)

2. Aggressive Auto-Suspend

3. Drop Unused Objects

4. Use TRANSIENT Tables for Temp Data

5. Result Caching

---

Security & Governance

1. Role-Based Access Control (RBAC)

Hierarchy Design:

Implementation:

Best Practices:

• ✅ Use custom roles (don't assign ACCOUNTADMIN directly)
• ✅ Grant on FUTURE objects to avoid manual updates
• ✅ Principle of least privilege
• ❌ Don't use ACCOUNTADMIN for daily work
• ❌ Don't grant ALL PRIVILEGES unnecessarily

---

2. Data Masking

---

3. Row Access Policies

---

4. Network Policies

---

Data Organization

1. Database & Schema Structure

Recommended Pattern:

Implementation:

---

2. Naming Conventions

Tables:

• raw_<source>_<table> - Raw ingested data
• stg_<source>_<table> - Staging tables
• int_<description> - Intermediate tables
• fct_<event> - Fact tables
• dim_<entity> - Dimension tables

Views:

• vw_<description> - Standard views
• sv_<description> - Secure views
• mv_<description> - Materialized views

Examples:

---

Query Optimization

1. Use Query Profile

Access Query Profile:

1. Run query in Snowflake UI
2. Click on query in History
3. Click "Query Profile" tab
4. Analyze:
   • Execution time breakdown
   • Data scanned
   • Spilling to disk (bad!)
   • Partitions pruned

2. Common Query Optimizations

Filter Early (Predicate Pushdown)

**Avoid SELECT ***

Use EXISTS Instead of IN for Large Lists

Leverage Query Result Caching

---

3. Optimize GROUP BY

---

Warehouse Management

1. Multi-Cluster Warehouses

When to Use:

• High concurrency (many users)
• Unpredictable query arrival patterns
• Need to prevent queueing

Monitoring:

---

2. Statement Queuing

---

Monitoring & Observability

1. Essential Monitoring Queries

Query Performance Dashboard

Failed Queries

Data Loading Monitoring

---

2. Alerting with Tasks

---

Common Anti-Patterns

❌ Anti-Pattern 1: Always-On Large Warehouses

✅ Fix:

---

❌ Anti-Pattern 2: Not Using Zero-Copy Clone

---

❌ Anti-Pattern 3: Row-by-Row Processing

---

❌ Anti-Pattern 4: Not Monitoring Costs

✅ Fix: Set up cost monitoring dashboard

---

Quick Wins Checklist

Performance:

• Cluster large tables (>1TB) on filter columns
• Create materialized views for expensive aggregations
• Enable result caching on all warehouses
• Use TRANSIENT tables for temporary/staging data

Cost:

• Set aggressive auto-suspend (60-300 seconds)
• Right-size warehouses (start small, scale up if needed)
• Set up resource monitors with budget alerts
• Use zero-copy cloning for dev/test
• Reduce Time Travel retention where possible

Security:

• Implement role-based access control
• Apply data masking to PII columns
• Use row access policies for multi-tenant data
• Enable network policies for sensitive users
• Audit ACCOUNTADMIN usage

Operations:

• Monitor query performance weekly
• Review warehouse utilization monthly
• Set up alerting for failures
• Document data lineage and dependencies
• Train users on best practices

---

Need Expert Help?

These best practices come from hundreds of real-world Snowflake implementations. Need assistance with:

• Performance Tuning: Optimize slow queries and improve response times
• Cost Optimization: Reduce spend by 30-70% through right-sizing and efficiency
• Architecture Review: Get expert feedback on your Snowflake design
• Migration Planning: Move to Snowflake with minimal risk
• Team Training: Custom workshops on Snowflake best practices

Contact for consulting services

---

← Back to Snowflake Overview | View Tutorials | See Use Cases