--- title: Snowflake Performance Tips and Tricks — Part 5 | daily.dev description: Four Snowflake query performance anti-patterns are covered with concrete benchmarks and fixes. First, warehouse sizing is not always a cost trade-off — a... canonical: https://daily.dev/posts/snowflake-performance-tips-and-tricks-part-5-mounpgvlh twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Snowflake Performance Tips and Tricks — Part 5 | daily.dev og:description: Four Snowflake query performance anti-patterns are covered with concrete benchmarks and fixes. First, warehouse sizing is not always a cost trade-off — a... og:url: https://daily.dev/posts/snowflake-performance-tips-and-tricks-part-5-mounpgvlh og:image: https://api.daily.dev/og/posts/mouNpGvlh.png og:image:alt: Snowflake Performance Tips and Tricks — Part 5 og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Snowflake Performance Tips and Tricks — Part 5 **[Snowflake Community](https://daily.dev/sources/snowflake_comm)** · 10 min read · 0 upvotes · 0 comments ## Summary Four Snowflake query performance anti-patterns are covered with concrete benchmarks and fixes. First, warehouse sizing is not always a cost trade-off — a Medium warehouse can be 5x faster than X-Small while consuming fewer credits due to linear scaling. Second, correlated subqueries in the SELECT list cause repeated table scans; replacing them with a JOIN+aggregation or a CTE reduces execution time by ~40%. Third, wrapping filter columns in functions like DATEDIFF disables partition pruning — keeping columns bare and transforming constants instead reduced scanned partitions from 7 to 2 (71% reduction). Fourth, concurrent query stampedes occur when many heavy queries hit the same warehouse simultaneously, causing memory spilling (2–50x slowdowns); solutions include staggering schedules, separating workloads, and using multi-cluster warehouses. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### How do I avoid full table scans in Snowflake when filtering by date? Keep the filter column bare and apply any function to the constant side of the comparison. For example, instead of WHERE DATEDIFF(d, CURRENT_DATE(), created_at) BETWEEN -30 AND 30, use WHERE created_at BETWEEN DATEADD(d, -30, CURRENT_DATE()) AND DATEADD(d, 30, CURRENT_DATE()). The latter lets Snowflake evaluate the bounds at compile time and use partition metadata for pruning — reducing scanned partitions from 7 to 2 in a tested 5-million-row table. _Engineers tuning Snowflake date filters track partition pruning patterns like this on daily.dev._ ### How do I fix correlated subqueries in Snowflake SELECT list that are causing slow queries? Replace each correlated subquery with a single JOIN combined with aggregation, or pre-aggregate in a CTE and then JOIN. A query using three correlated subqueries against the TPC-H LINEITEM table took 36 seconds because LINEITEM was scanned three times. Rewriting with a JOIN+GROUP BY brought it to 21 seconds (40% faster); a CTE approach ran in ~23 seconds with a cleaner query plan and a single table scan. _Snowflake developers rewriting slow queries find patterns like this alongside other optimization techniques on daily.dev._ ### What causes concurrent query stampede in Snowflake and how do I prevent it? A concurrent query stampede happens when many heavy queries arrive simultaneously on the same warehouse, competing for shared memory and causing spilling to local SSD (2–5x slower) or remote S3 (5–50x slower). Common triggers include Snowflake Tasks scheduled at identical cron times, BI tool auto-refreshes, and ETL orchestrators launching all stages at once. Fixes include staggering schedules by even 1 minute, separating ETL and BI onto dedicated warehouses, and using multi-cluster warehouses for unpredictable bursts. _Teams managing Snowflake warehouse contention follow operational patterns like these on daily.dev._ ## Similar posts on daily.dev - [Cost-effective performance with Snowflake Interactive Warehouses](https://daily.dev/posts/cost-effective-performance-with-snowflake-interactive-warehouses-34fgelchz) · Snowflake Community · 0 upvotes · 0 comments - [Sub-Second Analytics with Snowflake Interactive](https://daily.dev/posts/sub-second-analytics-with-snowflake-interactive-voj5bnydc) · Snowflake Community · 0 upvotes · 0 comments - [Benchmaxxing](https://daily.dev/posts/benchmaxxing-mvpiuwwl0) · iamluminousmen · 0 upvotes · 0 comments - [ClickHouse vs Snowflake: A Practical Comparison for Analytics Engineers](https://daily.dev/posts/clickhouse-vs-snowflake-a-practical-comparison-for-analytics-engineers-jdaf7whpc) · BigData Boutique blog · 1 upvotes · 0 comments - [Top 10 best practices tips for ClickHouse](https://daily.dev/posts/top-10-best-practices-tips-for-clickhouse-a5ciehuus) · ClickHouse · 1 upvotes · 0 comments --- Tags: [#sql](https://daily.dev/tags/sql), [#snowflake](https://daily.dev/tags/snowflake), [#data-warehouse](https://daily.dev/tags/data-warehouse) [View this post on daily.dev](https://daily.dev/posts/snowflake-performance-tips-and-tricks-part-5-mounpgvlh) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Snowflake Performance Tips and Tricks — Part 5","url":"https://daily.dev/posts/snowflake-performance-tips-and-tricks-part-5-mounpgvlh","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/snowflake-performance-tips-and-tricks-part-5-mounpgvlh"},"datePublished":"2026-08-11T14:39:18.443Z","dateModified":"2026-08-11T14:59:53.488Z","description":"Four Snowflake query performance anti-patterns are covered with concrete benchmarks and fixes. First, warehouse sizing is not always a cost trade-off — a...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/ebe9f4a5d30fe4977c0eff797ec1773f?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/ebe9f4a5d30fe4977c0eff797ec1773f?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"Snowflake Community","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Snowflake Community","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/3eb5260f70bb4653be0f9581c0d937c8","url":"https://daily.dev/sources/snowflake_comm"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/snowflake-performance-tips-and-tricks-part-5-mounpgvlh","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"sql,snowflake,data-warehouse","timeRequired":"PT10M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Snowflake Community","item":"https://daily.dev/sources/snowflake_comm"},{"@type":"ListItem","position":3,"name":"Snowflake Performance Tips and Tricks — Part 5"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/snowflake-performance-tips-and-tricks-part-5-mounpgvlh#faq","mainEntity":[{"@type":"Question","name":"How do I avoid full table scans in Snowflake when filtering by date?","acceptedAnswer":{"@type":"Answer","text":"Keep the filter column bare and apply any function to the constant side of the comparison. For example, instead of WHERE DATEDIFF(d, CURRENT_DATE(), created_at) BETWEEN -30 AND 30, use WHERE created_at BETWEEN DATEADD(d, -30, CURRENT_DATE()) AND DATEADD(d, 30, CURRENT_DATE()). The latter lets Snowflake evaluate the bounds at compile time and use partition metadata for pruning — reducing scanned partitions from 7 to 2 in a tested 5-million-row table. Engineers tuning Snowflake date filters track partition pruning patterns like this on daily.dev."}},{"@type":"Question","name":"How do I fix correlated subqueries in Snowflake SELECT list that are causing slow queries?","acceptedAnswer":{"@type":"Answer","text":"Replace each correlated subquery with a single JOIN combined with aggregation, or pre-aggregate in a CTE and then JOIN. A query using three correlated subqueries against the TPC-H LINEITEM table took 36 seconds because LINEITEM was scanned three times. Rewriting with a JOIN+GROUP BY brought it to 21 seconds (40% faster); a CTE approach ran in ~23 seconds with a cleaner query plan and a single table scan. Snowflake developers rewriting slow queries find patterns like this alongside other optimization techniques on daily.dev."}},{"@type":"Question","name":"What causes concurrent query stampede in Snowflake and how do I prevent it?","acceptedAnswer":{"@type":"Answer","text":"A concurrent query stampede happens when many heavy queries arrive simultaneously on the same warehouse, competing for shared memory and causing spilling to local SSD (2–5x slower) or remote S3 (5–50x slower). Common triggers include Snowflake Tasks scheduled at identical cron times, BI tool auto-refreshes, and ETL orchestrators launching all stages at once. Fixes include staggering schedules by even 1 minute, separating ETL and BI onto dedicated warehouses, and using multi-cluster warehouses for unpredictable bursts. Teams managing Snowflake warehouse contention follow operational patterns like these on daily.dev."}}]} ```