snowflake-advanced-troubleshooting

# Snowflake Advanced Troubleshooting ## Overview Deep debugging techniques for complex Snowflake issues: query profile analysis, spill detection, lock contention, transaction conflicts, and metadata operation bottlenecks. ## Instructions ### Step 1: Query Profile Deep Dive ```sql -- Get detailed execution stats for a specific query SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>')); -- Key operators to look for: -- TableScan: Check partitions_scanned vs partitions_total -- Sort: Check spilling_to_local_storage, spilling_to_remote_storage -- Join: Check type (broadcast vs hash), probe/build side sizes -- Aggregate: Check if grouping cardinality causes spill -- Identify queries with excessive spilling SELECT query_id, query_text, bytes_spilled_to_local_storage / 1e9 AS local_spill_gb, bytes_spilled_to_remote_storage / 1e9 AS remote_spill_gb, total_elapsed_time / 1000 AS seconds, warehouse_size FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE (bytes_spilled_to_local_storage > 0 OR bytes_spilled_to_remote_storage > 0) AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP()) ORDER BY bytes_spilled_to_remote_storage DESC LIMIT 20; -- Remote spill = warehouse too small for the query -- Fix: Scale up warehouse or optimize query to reduce intermediate data ``` ### Step 2: Lock and Transaction Contention ```sql -- Find blocked/waiting queries SELECT query_id, query_text, blocked_query_id, DATEDIFF('second', start_time, CURRENT_T...