Query Profile, History, and Insights

1. Query Profile, History, and Insights

In this video you'll learn how Snowflake's Query Profile, QUERY_HISTORY, and Query Insights work together to find out why a query is slow and what to do about it.

2. Why Query Profile

Harbr's operations dashboard used to run in seconds. It's now taking four minutes, and adding more warehouse compute hasn't helped. That's because the problem isn't compute - it's that Snowflake is scanning data it shouldn't touch at all. Query Profile shows you exactly where the time is going, so you can fix the right thing.

3. Opening the Query Profile in Snowsight

To open the Query Profile in Snowsight, go to Monitoring, then Query History. Click the query you want to inspect, then open the Query Profile tab. You'll see the execution plan as an operator tree, with timing data alongside it.

4. Reading the Operator Tree

The operator tree shows every step Snowflake took to run your query. Each node shows the operator type and the percentage of total execution time it consumed. The Most Expensive Nodes pane on the right ranks them for you - the top entry is your bottleneck. The Statistics pane shows what each node actually did, including bytes scanned, partitions pruned, and any data spilled to disk. That's where you start any investigation on your query performance.

5. Four Common Problems Query Profile reveals

There are four common problems you can spot with Query Profile. Firstly, exploding joins produce far more rows than they consume - usually a missing or wrong join condition. UNION without ALL adds an Aggregate above your Union All to remove duplicates, costing extra time. Spill to disk happens when memory-intensive operators like Aggregates, Sorts, or large Joins run out of warehouse memory. And inefficient pruning shows up when partitions scanned is close to partitions total, that means your filters aren't doing their job.

6. Spill to Disk: Local vs Remote

Spill happens in two ways, first where Local spill writes overflow to the warehouse's local SSD which is slower than memory but manageable. Remote spill writes to cloud storage - much slower, and a clear signal something needs to change. The columns to check in QUERY_HISTORY are bytes spilled to local storage and bytes spilled to remote storage. The fix is either a larger warehouse with more memory, or rewriting the query to process less data.

7. QUERY_HISTORY: Account-wide Visibility

Query Profile tells you why one query is slow. QUERY_HISTORY tells you which queries across your whole account are the worst offenders. The view lives in the ACCOUNT_USAGE schema of the SNOWFLAKE database, and it retains a year of history. Don't confuse it with the INFORMATION_SCHEMA QUERY_HISTORY table function - same name, different object, but with much shorter retention. The view is a plain table you SELECT from; the table function needs to be wrapped in TABLE with brackets.

8. Query Insights: Automated Recommendations

Snowflake also surfaces Query Insights automatically. Open the Query Profile and the Query Insights pane on the right shows specific conditions that affected this query - like remote spillage, an exploding join, or an unnecessary UNION distinct clause. Each insight comes with a recommended next step. Query Insights don't appear for cached results, hybrid tables, or multi-step plans - so run a fresh query to see them.

9. Let's practice!

You've covered how to read the Query Profile to identify bottlenecks, what spill to disk signals, and how to use QUERY_HISTORY to find the worst offenders across your account. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.