How to Use Query Profile in Snowflake for Query Analysis
Use the
Query Profile in Snowflake by running a query and then clicking on the query ID in the History tab to open its profile. This visual tool shows detailed execution steps, resource usage, and helps identify bottlenecks in your query.Syntax
The Query Profile is accessed through the Snowflake web interface or via SQL commands to find query IDs. It does not have a direct SQL syntax but is used by selecting a query from the history.
Key parts:
- Query ID: Unique identifier for each executed query.
- History tab: Where you find past queries and their IDs.
- Query Profile button: Opens the detailed execution plan and metrics.
sql
SELECT query_id, query_text, start_time FROM table(information_schema.query_history()) WHERE user_name = CURRENT_USER() ORDER BY start_time DESC LIMIT 5;
Example
This example shows how to find recent queries and open their Query Profile in the Snowflake UI to analyze performance.
sql
SELECT query_id, query_text, start_time FROM table(information_schema.query_history()) WHERE user_name = CURRENT_USER() ORDER BY start_time DESC LIMIT 1;
Output
QUERY_ID | QUERY_TEXT | START_TIME
-----------------------------------|----------------------------|-------------------------
01a2b3c4-5678-90de-f123-4567890abcde | SELECT * FROM sales LIMIT 10 | 2024-06-01 10:15:00.000
Common Pitfalls
Common mistakes when using Query Profile include:
- Trying to access Query Profile for queries older than the retention period (default 7 days).
- Not having sufficient privileges to view query history or profiles.
- Misinterpreting the visual steps without understanding the execution flow.
Always ensure you select the correct query ID and check permissions.
sql
/* Wrong: Trying to open profile for a query ID that does not exist or expired */ -- No direct SQL to open profile, must use UI /* Right: Use recent query IDs from query_history() and open profile in UI */ SELECT query_id FROM table(information_schema.query_history()) ORDER BY start_time DESC LIMIT 1;
Quick Reference
| Feature | Description |
|---|---|
| Query ID | Unique identifier for each executed query |
| History Tab | Location in Snowflake UI to find past queries |
| Query Profile | Visual tool showing query execution details |
| Execution Steps | Breakdown of query operations and time spent |
| Resource Usage | CPU, memory, and I/O metrics during query execution |
Key Takeaways
Access Query Profile by selecting a query ID from the History tab in Snowflake UI.
Use Query Profile to see detailed execution steps and resource usage for performance tuning.
Query Profile is only available for recent queries within the retention period.
Ensure you have proper permissions to view query history and profiles.
Use the information_schema.query_history() view to find query IDs for analysis.
