Why your Snowflake queries running slow – even when the warehouse is Large

27 July | 6 min read

Slow Snowflake queries can be more frustrating, especially at the time when you’ve already expanded to a larger warehouse. So, why is the delay in the execution of the query? If this sounds familiar, you are not alone.

One of the biggest misconceptions after migrating to Snowflake is that a larger warehouse will solve every performance issue. While it adds more computing power, slow queries often have other underlying problems. If performance lags, their first response is to upgrade it. But again, when you run the same query after increasing the warehouse size, and it’s still the same? After all, we were told that a larger warehouse has more computer power and should process queries faster.

But it’s not always. A larger warehouse can process more work, but it cannot fix unnecessary data scans, workload bottlenecks, poor table design, or inefficient SQL. The question hits: why are your Snowflake queries still slow after upgrading your warehouse? In this article, we will delve into the most common reasons behind the problem and the best ways to solve it.

• 1. The Problem with Concurrency and Query Queues:

  When you upgrade the warehouse, you are basically making it bigger and stronger. A larger warehouse gives your query more resources, so it can process different parts of the workload at the same time.
  If your website has hundreds of user queries running at the same time, Snowflake can get overloaded. When all execution slots are full, all the new queries are placed in a queue. Spending 45 seconds in the queue and only 5 seconds processing means the delay happens before the query even begins.

• 2. The hidden cost of Data Spilling:

  Snowflake warehouse has a limited amount of local memory known as RAM. Large queries are processed in memory whenever possible, but if they need more space, Snowflake starts using the local disk. If the data grows beyond that, it’s moved to remote storage, which is much slower to access. That’s why some queries still take a long time, even on a large warehouse.

• 3. How poor data clustering slows down queries:

  Snowflake stores data in small, compressed pieces called micro-partitions. When a user applies a filter in a query, the platform skips unnecessary data and scans only the information it needs. This process, called partition pruning, which helps queries run faster and efficiently. If Snowflake can’t narrow down the data, it ends up reading much more than it should. As a result, Snowflake must scan the whole table, increasing query execution time.

• 4. Unexpected Row Growth in Queries:

  It doesn’t matter if you have a large warehouse or not. A large warehouse alone isn’t enough. If JOINs aren’t optimized, queries can still run slowly. This frequently happens when two big tables are joined on columns that are not unique, or when the join condition is missing or incorrect. The warehouse ends up spending more time transferring data than processing it, causing overall performance to drop.

Let’s look at this with a real-time query. The following Snowflake warehouse usage query identifies the top 10 worst-performing queries over the last 15 days. It focuses on queries that were delayed because of data spilling.

SQL

SELECT

    query_id,

    user_name,

    warehouse_name,

    warehouse_size,

    total_elapsed_time / 1000 AS total_elapsed_seconds,

    compilation_time / 1000 AS compilation_seconds,

    execution_time / 1000 AS execution_seconds,

    queued_overload_time / 1000 AS queued_by_overload_seconds,

    — Check for data spilling to disk

    ROUND(bytes_spilled_to_local_storage / POWER(1024, 3), 2) AS local_spill_gb,

    ROUND(bytes_spilled_to_remote_storage / POWER(1024, 3), 2) AS remote_spill_gb,

    SUBSTR(query_text, 1, 100) AS partial_query_text

FROM snowflake.account_usage.query_history

WHERE start_time >= DATEADD(day, -14, CURRENT_TIMESTAMP())

  AND execution_status = ‘SUCCESS’

  AND total_elapsed_time > 10000 — Focus on queries taking longer than 10 seconds

ORDER BY total_elapsed_time DESC

LIMIT 10;

Snowflake Troubleshooting Playbook

Once you run the query, review the results to decide what needs to be fixed immediately.

Analyzing the Query Output:

1. Imagine walking into a busy store with customers waiting at the checkout. If too many queries arrive at once, your warehouse can’t process them all immediately, so some are placed in a queue and must wait. To fix this, you can separate different types of workloads into different warehouses.
2. If remote spill GB is greater than 0, it means the query is using more memory than expected. This can slow down performance. To speed up the query, split it into smaller steps, remove unnecessary ORDER BY clauses, and check your JOINs for extra rows.

Before you upgrade: Try these fixes first.

Before upgrading your warehouse size, it’s important to check a few fundamental optimizations, without adding any extra cost.

• 1. Avoid using SELECT: Retrieve only the columns you need. This reduces the amount of data scanned and saves memory.
• 2. Use simple filters that let Snowflake scan only the data it needs. For example:

WHERE created_at >= ‘2026-01-01’

instead of

WHERE YEAR(created_at) = 2026

• 1) Take advantage of the result cache. Snowflake can reuse query results for up to 24 hours. To benefit from this, keep your queries deterministic. If you want Snowflake to reuse cached results, avoid using functions like CURRENT_TIMESTAMP() because they return a different value each time the query runs.

Best Practices for Faster Snowflake Queries

If you want consistently fast performance, keep these best practices in mind:

• Select only the columns that you need.
• Filter data as early as possible.
• Use filters to read only the data you need instead of scanning the entire table.
• Reduce data before joining tables.
• Avoid unnecessary sorting unless it’s required.
• Review the Query Profile regularly.
• Use separate warehouses for different workloads.
• Take advantage of query cache and materialized views.
• Optimize your SQL by tuning and rewriting before increasing warehouse size.
• Review your Snowflake warehouse usage regularly to understand how compute resources are being used.

Well, the best place to start is always the query itself. Look at what data it reads, how it filters, and whether it’s doing more work than required. After that, you can decide if upgrading the warehouse is actually needed.
Always remember, the fastest Snowflake query is not always the one running on the largest warehouse. It’s the one that was written to perform the work optimally matters.