Diagnosing a slow query from its telemetry down to the root cause

Troubleshooting slow queries in Snowflake starts with Query Profile, which visualizes execution steps and highlights the most time- and resource-consuming operators. Combine this with warehouse sizing, spillage, and pruning metrics to isolate whether the bottleneck is compute, data volume, or query design. History views (QUERY_HISTORY, ACCESS_HISTORY) and system functions help correlate performance issues with warehouse load and concurrency.

1 · Learn the must-know

• 1Query Profile shows execution time per node, bytes scanned, partitions scanned vs. total, and row counts, making it the first stop for diagnosing slow queries.
• 2High 'bytes spilled to local/remote storage' in Query Profile indicates the warehouse is undersized for the operation (e.g., large sorts/joins), so resizing (scaling up) or optimizing the query can help.
• 3Poor partition pruning (scanned partitions close to total partitions) signals ineffective clustering or filter predicates that don't align with the table's natural or defined clustering key.
• 4Queuing time (visible in QUERY_HISTORY as QUEUED_PROVISIONING_TIME or QUEUED_OVERLOAD_TIME) reflects warehouse contention/concurrency issues, not query inefficiency, and may require a larger warehouse or separate warehouse for the workload.
• 5Exploding joins (cartesian products) or repeated small operations show up in Query Profile as operators with row counts far exceeding input row counts, a common root cause of runaway query times.
• 6The RESULT_SCAN and caching behavior mean re-running an identical query may appear fast due to result cache, so always check 'Persisted Query Results' reuse before concluding a query was optimized.

3 · Keep going

These are independent practice questions, written against this certification's published exam guide. They are not the certification vendor's own questions, and not the real exam.