snowflake-performance-tuning

name	snowflake-performance-tuning
description	Optimize Snowflake query performance with clustering, materialized views, caching, and query profiling. Use when queries are slow, analyzing QUERY_HISTORY for bottlenecks, or optimizing warehouse utilization and data scanning. Trigger with phrases like "snowflake performance", "optimize snowflake", "snowflake slow query", "snowflake clustering", "snowflake query profile".
allowed-tools	Read, Write, Edit
version	1.5.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","data-warehouse","analytics","snowflake"]
compatibility	Designed for Claude Code

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

-- 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  
   partitions_total  
   start_time  DATEADD(hours, , ())
  partitions_scanned 
LIMIT ;