How to Debug Queries in Snowflake

How can I debug slow queries in Snowflake?

Use EXPLAIN, PROFILE, and QUERY_HISTORY views to inspect plans, runtime metrics, and bottlenecks in Snowflake SQL statements.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I quickly spot slow Snowflake queries?

List recent statements with SELECT query_id, total_elapsed_time, query_text FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) ORDER BY total_elapsed_time DESC LIMIT 10;. Copy the query_id of the slowest query for deeper inspection.

Which system views expose runtime details?

QUERY_HISTORY shows elapsed times, bytes scanned, and cluster queuing. Join with QUERY_ACCELERATED_STATS or QUERY_STAGE_HISTORY to identify partitions skipped, spilled bytes, and skewed joins.

How can EXPLAIN reveal execution plans?

EXPLAIN returns Snowflake’s plan without running the query. Inspect nodes for JOIN ORDER, PARTITION PRUNING, and FILTER placement to ensure predicates limit scanned data.

Syntax

EXPLAIN [USING {TABULAR | JSON | TEXT}] <sql_statement>;

When should I run PROFILE?

PROFILE executes the statement and captures stage-level metrics—partition scans, bytes spilled, and compilation time. Use it after studying EXPLAIN so you intentionally pay the extra run cost.

Syntax

PROFILE <sql_statement>;

How do I identify plan stages that waste time?

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_PROFILE('your_query_id')); lists each stage’s execution and wait times. Target stages with a high percentage of total_elapsed_time for optimization.

Can query tags help organize debugging?

Run ALTER SESSION SET QUERY_TAG='checkout_report_v3'; before execution. Later, filter QUERY_HISTORY WHERE query_tag='checkout_report_v3' to isolate related runs.

Best practices for debugging Snowflake queries

Tag analytical queries, test complex CTEs individually, cluster large fact tables on high-cardinality columns, and save EXPLAIN outputs in version control for future comparison.

Why How to Debug Queries in Snowflake is important

How to Debug Queries in Snowflake Example Usage


-- Find slow query, study plan, and profile
WITH slowest AS (
  SELECT query_id
  FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
  WHERE user_name = CURRENT_USER()
  ORDER BY total_elapsed_time DESC
  LIMIT 1
)
SELECT * FROM slowest; -- grab the query_id

-- Explain the problematic revenue report
EXPLAIN USING JSON
SELECT c.name, SUM(oi.quantity*p.price) AS lifetime_value
FROM Customers c
JOIN Orders      o  ON o.customer_id = c.id
JOIN OrderItems  oi ON oi.order_id   = o.id
JOIN Products    p  ON p.id          = oi.product_id
GROUP BY c.name;

-- Profile it to capture real metrics
PROFILE
SELECT c.name, SUM(oi.quantity*p.price) AS lifetime_value
FROM Customers c
JOIN Orders      o  ON o.customer_id = c.id
JOIN OrderItems  oi ON oi.order_id   = o.id
JOIN Products    p  ON p.id          = oi.product_id
GROUP BY c.name;

How to Debug Queries in Snowflake Syntax


-- 1. Review recent slow statements
SELECT query_id, total_elapsed_time, query_text
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY total_elapsed_time DESC LIMIT 10;

-- 2. Inspect logical & physical plan
EXPLAIN USING TABULAR
SELECT o.id, o.order_date, SUM(oi.quantity*p.price) AS revenue
FROM Orders o
JOIN OrderItems oi   ON oi.order_id = o.id
JOIN Products   p    ON p.id        = oi.product_id
GROUP BY o.id, o.order_date;

-- 3. Capture runtime metrics
PROFILE
SELECT o.id, o.order_date, SUM(oi.quantity*p.price) AS revenue
FROM Orders o
JOIN OrderItems oi   ON oi.order_id = o.id
JOIN Products   p    ON p.id        = oi.product_id
GROUP BY o.id, o.order_date;

-- 4. Drill into stage-level stats
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_STAGE_HISTORY(' &lt;query_id&gt;'));

-- 5. Filter history by tag
ALTER SESSION SET QUERY_TAG='revenue_report_debug';
SELECT *
FROM Orders o
WHERE o.order_date >= DATEADD(month,-1,CURRENT_DATE);

Common Mistakes

Relying only on EXPLAIN. The plan shows intent, not reality. Always follow with PROFILE or QUERY_HISTORY to confirm which stage actually consumed time.
Ignoring query tagging. Without tags, isolating a specific run in busy accounts is painful. Set QUERY_TAG before execution and filter QUERY_HISTORY on it.

Frequently Asked Questions (FAQs)

Does PROFILE consume credits?

Yes. PROFILE fully executes the query, so it uses compute resources and credits. Run it only after cheaper methods like EXPLAIN and plan inspection.

Can I debug queries run by another user?

Yes, if you have the MONITOR privilege on their warehouse or the ACCOUNTADMIN role. Query ACCOUNT_USAGE views with the other user’s query_id.

How do I see partition pruning effectiveness?

In the EXPLAIN output, check for Pruning=true flags and compare scan bytes in QUERY_STAGE_HISTORY. High unpruned bytes indicate missing filters or clustering.