Skip to main content

Using Snowflake Query Profiler for Monitoring


1️⃣ What is the Query Profiler?​

The Query Profiler is a powerful tool in Snowflake’s Web UI that provides a visual representation of query execution.
It breaks down a query into stages and shows execution metrics (e.g., time, rows processed, data spillage).


🔹 Key Features​

  • ✅ Graphical execution plan: Visualizes how the query was processed.
  • ✅ Detailed metrics: Time spent per stage, rows processed, partition info.
  • ✅ Insights for optimization: Pinpoints expensive steps (e.g., joins, scans).

2️⃣ Why is it required?​

  • ✅ To identify bottlenecks in queries (e.g., joins, filters, table scans).
  • ✅ To understand execution strategies used by Snowflake’s optimizer.
  • ✅ To optimize and rewrite queries for better performance.
  • ✅ To monitor query health and proactively troubleshoot slow performance.

3️⃣ Accessing the Query Profiler​

The Query Profiler is available in the History tab of the Snowflake Web UI:

1️⃣ Go to the History page.
2️⃣ Click on a query’s Query ID.
3️⃣ Open the Query Profile tab.


🔹 Associated SQL​

There’s no direct SQL command to generate a Query Profiler; it’s UI-based.
However, you can fetch Query IDs from QUERY_HISTORY to locate slow queries:

SELECT QUERY_ID, USER_NAME, EXECUTION_STATUS, TOTAL_ELAPSED_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD('DAY', -1, CURRENT_TIMESTAMP())
ORDER BY TOTAL_ELAPSED_TIME DESC;

Use the Query ID from this output to locate and open the profiler in the Web UI.


4️⃣ Use Case​

A data engineer notices a dashboard query is running slowly. They open the Query Profiler to identify:

  • If a full table scan is occurring.
  • If a join is causing excessive data shuffling.
  • If intermediate results are spilling to local storage (common cause of slowness).

Using this insight, they rewrite the query to use better joins, filters, or clustering.


5️⃣ Best Practices​

  • ✅ Regularly profile expensive queries to spot optimization opportunities.
  • ✅ Use partitioning or clustering to avoid full scans.
  • ✅ Ensure joins use indexed/clustered columns to reduce data shuffle.
  • ✅ Combine profiler insights with Query History data for trends.

6️⃣ Limitations​

❌ Only available in the Web UI (not exposed via SQL or API). ❌ Does not directly show table-level metadata (like clustering keys). ❌ Requires manual analysis—no automatic suggestions.


7️⃣ Advantages​

  • ✅ Intuitive visual insights—easy to spot issues.
  • ✅ Helps fine-tune queries to reduce compute costs.
  • ✅ Complements other monitoring tools (like Resource Monitors).

8️⃣ Disadvantages​

❌ Not suitable for bulk analysis across many queries—one at a time. ❌ May not show low-level execution details (e.g., hardware-level tuning).


9️⃣ Required Roles​

  • MONITOR privileges or higher.
  • Typically used by data engineers or DBAs.

🔟 Impact on Performance Tuning​

  • ✅ Helps identify stages causing delays (e.g., large joins, disk spillage).
  • ✅ Supports right-sizing of warehouses by showing resource usage.
  • ✅ Informs indexing/clustering strategies to improve performance.
  • ✅ Promotes better query design (e.g., avoid cartesian joins).
  • ✅ Facilitates data pruning by visualizing scans vs. pruned partitions.

🚀 Summary Table​

AspectDetails
📌 ToolQuery Profiler (UI)
📌 RoleMONITOR, ACCOUNTADMIN, or DBAs
📌 PurposeVisualize and analyze query execution to tune performance
📌 LimitationOnly in UI; requires manual step-by-step review
📌 BenefitPinpoints costly query stages, helping you reduce costs & improve speed