snowflake-performance-tuning

# Snowflake Performance Tuning ## Overview Optimize Snowflake query performance using clustering keys, materialized views, result caching, query profiling, and warehouse tuning. ## Prerequisites - Access to `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` - Understanding of micro-partitions and pruning - Role with `MONITOR` privilege on warehouses ## Instructions ### Step 1: Identify Slow Queries ```sql -- Top 20 slowest queries in last 24 hours SELECT query_id, query_text, total_elapsed_time / 1000 AS seconds, bytes_scanned / 1e9 AS gb_scanned, partitions_scanned, partitions_total, ROUND(partitions_scanned / NULLIF(partitions_total, 0) * 100, 1) AS pct_scanned, warehouse_name, warehouse_size FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE execution_status = 'SUCCESS' AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP()) AND query_type = 'SELECT' ORDER BY total_elapsed_time DESC LIMIT 20; -- Queries scanning too many partitions (poor pruning) SELECT query_id, query_text, partitions_scanned, partitions_total, bytes_scanned / 1e9 AS gb_scanned FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE partitions_scanned > partitions_total * 0.5 AND partitions_total > 100 AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP()) ORDER BY partitions_scanned DESC LIMIT 10; ``` ### Step 2: Add Clustering Keys ```sql -- Clustering improves pruning for large tables (> 1TB) -- Choose columns used in WHERE and JOIN clauses -- Cluster by ...