How do I troubleshoot slow queries in Snowflake?

Here’s a practical way to troubleshoot slow queries in Snowflake, from fastest checks to deeper diagnosis.

1) Find the slow query and its profile

Start with Snowsight or query history.

• Snowsight → Query History
• Open the query and inspect the Query Profile

Look for:

• Total execution time
• Compilation time
• Queued time
• Bytes scanned
• Partitions scanned
• Spills to remote/local storage
• Join strategy
• Skew or very large intermediate results

If compilation or queue time is high, the problem may not be SQL logic at all.

---

2) Determine where time is being spent

Slow queries usually fall into one of these buckets:

A. Queueing / warehouse contention

Symptoms:

• High queued provisioning or queued overload
• Many concurrent queries on the same warehouse

What to do:

• Scale the warehouse up or out
• Use a larger warehouse
• Use multi-cluster warehouses
• Reduce concurrency or split workloads across warehouses

---

B. Data scanning too much

Symptoms:

• High bytes scanned
• Many partitions scanned
• No pruning in the profile

What to do:

• Add more selective filters
• Avoid wrapping filter columns in functions
• Make predicates sargable
• Reduce SELECT *
• Use clustering if pruning is poor on large tables with common filter patterns

Example bad pattern:

WHERE DATE(order_ts) = '2026-01-01'

Better:

WHERE order_ts >= '2026-01-01'
  AND order_ts <  '2026-01-02'

---

C. Expensive joins

Symptoms:

• Large join intermediates
• Broadcast/hash join on huge inputs
• Join reordering issues
• Duplicate amplification from many-to-many joins

What to do:

• Filter early before joins
• Join on smaller, pre-aggregated datasets
• Verify join keys are correct and selective
• Avoid joining unnecessarily wide tables
• Check for accidental Cartesian joins

---

D. Sorting / aggregation bottlenecks

Symptoms:

• Large ORDER BY, DISTINCT, GROUP BY, window functions
• Spill to remote storage

What to do:

• Reduce the dataset before sorting
• Aggregate earlier
• Remove unnecessary DISTINCT
• Limit rows before expensive final sorts if possible

---

E. UDFs / complex expressions

Symptoms:

• Heavy CPU usage
• Little improvement from larger warehouse

What to do:

• Replace row-by-row logic with set-based SQL
• Simplify regex, JSON parsing, or nested expressions
• Avoid JavaScript UDFs when possible for large datasets

---

3) Check the actual execution plan

Use:

EXPLAIN USING TEXT <your_query>;

And in Snowsight, inspect:

• table scans
• join order
• filters applied early or late
• sort/aggregate nodes

You want to see:

• filters pushed down early
• small inputs to joins
• minimal spill
• pruning on scans

---

4) Look for warehouse sizing issues

If a query is CPU-bound or spilling:

• Try a larger warehouse
• Compare runtime vs. cost
• If runtime drops a lot, the query is likely compute-bound or memory-bound

If the query is mostly waiting:

• A larger warehouse may not help much
• Concurrency or locking/contention is more likely

---

5) Validate data distribution and pruning

For large tables:

• Check whether clustering helps
• Look at filter patterns used most often
• Use clustering on columns frequently used in selective predicates and joins

If your table is naturally well-partitioned, clustering may not be necessary.

---

6) Check query design anti-patterns

Common slow-query patterns in Snowflake:

• SELECT *
• Non-sargable predicates
• Joining before filtering
• Excessive DISTINCT
• Unnecessary ORDER BY
• Cross joins or missing join conditions
• Repeated subqueries that could be materialized
• Window functions over huge unfiltered datasets
• Parsing semi-structured data repeatedly

---

7) Use Query Profile to spot spills

Spills are a strong sign of memory pressure.

If you see:

• local spill
• remote spill

Try:

• larger warehouse
• reduce intermediate row width
• filter earlier
• aggregate before joins
• simplify the query

---

8) Use result caching and warehouse caching wisely

Snowflake can be fast if:

• the result cache can be used
• the warehouse cache contains frequently accessed data

But if every run changes slightly:

• cache benefits may be limited

Check whether the query text or session context changes in ways that invalidate caching.

---

9) Compare query runs

Run the same query under controlled conditions:

• same warehouse size
• no competing workloads
• same parameters
• same time window if possible

Then compare:

• runtime
• scanned bytes
• queue time
• spill

This helps isolate whether the issue is the SQL or the environment.

---

10) Practical debugging checklist

When a query is slow, ask:

1. Is it queued or actually executing?
2. How many bytes/partitions were scanned?
3. Are filters being pushed down?
4. Are joins on the right keys?
5. Is there spill?
6. Is the warehouse too small?
7. Is there too much concurrency?
8. Can I reduce rows earlier?
9. Can I remove DISTINCT, ORDER BY, or SELECT *?
10. Would clustering or redesign help?

---

11) Useful Snowflake tools/views

• Snowsight Query Profile
• QUERY_HISTORY
• WAREHOUSE_LOAD_HISTORY
• QUERY_HISTORY_BY_* views
• EXPLAIN
• Account usage views for historical analysis

---

12) If you want a quick workflow

A good sequence is:

1. Open Query Profile
2. Check queued time, bytes scanned, spill
3. Run EXPLAIN
4. Simplify filters and joins
5. Try a larger warehouse
6. Compare before/after

---

If you want, I can also give you:

• a step-by-step Snowflake troubleshooting checklist
• a SQL script to inspect slow queries
• or help analyze a specific query plan if you paste it.