Performance Tips

Optimize your Snowflake data warehouse performance and reduce costs with these best practices.

Best Practices

1. Indexing

Create indexes on frequently queried columns

While Snowflake doesn't use traditional indexes, you can optimize query performance by:

Clustering tables on frequently filtered columns
Using search optimization service for text-based searches
Creating materialized views for complex, frequently-run queries

2. Warehouse Scaling

Adjust Snowflake warehouse size during peak loads

Snowflake allows dynamic scaling of compute resources:

Scale up during high-demand periods (e.g., month-end reporting)
Scale down during low-usage periods to save costs
Use multi-cluster warehouses for concurrent workloads

-- Scale up a warehouse
ALTER WAREHOUSE ANALYTICS_WH SET WAREHOUSE_SIZE = 'LARGE';

-- Scale down a warehouse
ALTER WAREHOUSE ANALYTICS_WH SET WAREHOUSE_SIZE = 'SMALL';

-- Configure multi-cluster settings
ALTER WAREHOUSE ANALYTICS_WH SET 
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 5
  SCALING_POLICY = 'STANDARD';

3. Query Caching

Leverage Snowflake's result caching for repeated queries

Snowflake automatically caches query results for 24 hours. To maximize cache hits:

Use consistent query patterns
Avoid unnecessary ORDER BY clauses
Be aware that DDL operations invalidate cache

4. Incremental Syncs

Set for large datasets (e.g., sync only new Salesforce leads)

Configure incremental syncs to reduce data transfer and processing time:

{
  "sync_mode": "incremental",
  "cursor_field": "last_modified_date",
  "cursor_granularity": "seconds"
}

5. Auto-Suspend

Use in Snowflake to save costs during idle periods

Configure warehouses to automatically suspend when idle:

ALTER WAREHOUSE FIVETRAN_WH 
SET AUTO_SUSPEND = 300; -- Suspend after 5 minutes of inactivity

Additional Optimization Techniques

Query Optimization

Use EXPLAIN to analyze query execution plans
Filter data early in the query to reduce processing
Use appropriate JOIN types (INNER vs. LEFT vs. RIGHT)
Avoid SELECT * and only retrieve needed columns

Data Partitioning

Partition large tables by date or other high-cardinality columns
Use clustering keys for frequently filtered columns
Consider micro-partitioning for very large tables

Resource Monitoring

Set up credit usage alerts to prevent unexpected costs
Monitor query history to identify slow or expensive queries
Track warehouse utilization to optimize sizing

Next Steps

Snowflake Integration

Learn how to connect and configure Snowflake

Schema Management

Organize and manage your data schemas and tables