10 SQL Patterns to Drastically Reduce Your Snowflake Costs

18 minutes to read

August 14, 2026

Managing a modern data stack effectively means keeping your cloud bill predictable and aligned with your data strategy. Data Engineers and Analytics Engineers can streamline their workloads by addressing queries that consume excess compute credits and drive up monthly costs. Improving performance requires looking beyond simply scaling up the warehouse size. Refining your SQL architecture directly addresses the core performance issues.

At Stellans, we believe in acting as an empowering partner. We work with you to unlock data potential by treating your pipelines like a well-oiled data machine. We fix the SQL first. By refactoring inefficient queries directly at the source, you gain predictable cloud spend and a significantly faster reporting layer. In this guide, we will break down exactly how to audit your queries and share ten proven SQL rewrite patterns that our consultants use in the trenches to save clients real money.

Why SQL Patterns Matter More Than Warehouse Size

Snowflake separates compute from storage, meaning you pay for the virtual warehouse compute time required to process your SQL. When queries are poorly structured, they scan unnecessary data, forcing the warehouse to stay awake longer. A larger warehouse might process that inefficient scan faster, but it burns through credits at double or quadruple the rate, removing cost predictability.

To understand why SQL structure matters, you have to look under the hood at how Snowflake stores your data.

Micro-Partitions and Partition Pruning

Snowflake automatically divides your tables into contiguous units called micro-partitions. Each micro-partition contains metadata about the minimum and maximum values of the columns within it. When you write a SARGable query (Search Argument Able), Snowflake reads this metadata and skips the micro-partitions that do not match your filters. This process is called micro-partition pruning. Optimized SQL maximizes pruning, meaning less data is scanned, leading to faster execution and lower costs.

Understanding Snowflake Query Profile

Before optimizing, it is crucial to clearly identify the specific areas that need improvement. The Snowflake query profile is the ultimate source of truth for identifying bottlenecks in your ELT transformations and ad-hoc queries. It shows you exactly where your compute time is going by breaking down the query execution plan visually.

How to Use Snowflake Query Profile to Find Bottlenecks

Relying on objective metrics enables you to optimize accurately and effectively. Here is how we guide teams to pinpoint the exact step causing slow queries and excessive compute usage.

Step 1: Locate the Query in History Log into the Snowsight interface and navigate to the Activity tab. Click on Query History and select the query ID of the specific statement that took too long or consumed too many credits.

Step 2: Open the Query Profile Once you click on the query, navigate to the “Query Profile” tab. This presents a graphical representation of the query execution plan. Look at the operator tree to find the node consuming the highest percentage of execution time.

Step 3: Analyze Key Performance Indicators Focus intensely on three specific metrics within the profile statistics:

Scan: Look at “Partitions scanned” versus “Partitions total”. If you are scanning 90% of the partitions but only returning 1% of the rows, your micro-partition pruning is failing.
Local/Remote Disk I/O: High remote disk I/O means you are pulling massive amounts of data from cloud storage rather than relying on cached data.
Spillage: Look for “Bytes spilled to local storage” or “Bytes spilled to remote storage”. Spillage happens when your query requires more memory than the warehouse provides, prompting Snowflake to temporarily write data to disk. Resolving this fundamentally accelerates performance.

10 SQL Patterns to Drastically Reduce Your Snowflake Costs

In our recent client implementations, optimizing just the top three incremental MERGE statements reduced overnight warehouse compute costs by 40%. Below are ten real-world SQL patterns we see repeatedly, alongside the refactored code that drives high performance.

1. Predicate Pushdown Pattern

Anti-Pattern: Filtering late in the query execution. Many developers build complex Common Table Expressions (CTEs) that process millions of rows, only to filter down to a handful of records at the very end of the query.

-- Anti-Pattern
WITH raw_sales AS (
    SELECT order_id, customer_id, amount, region
    FROM enterprise_sales_data
)
SELECT * 
FROM raw_sales 
WHERE region = 'EMEA';

Optimized Pattern: Push your predicates (filters) as close to the foundational tables as possible.

-- Optimized Pattern
WITH raw_sales AS (
    SELECT order_id, customer_id, amount, region
    FROM enterprise_sales_data
    WHERE region = 'EMEA' -- Filter applied early
)
SELECT * 
FROM raw_sales;

Impact: Filtering early forces immediate micro-partition pruning. You process only a fraction of the data through subsequent joins and aggregations, avoiding massive memory spillage and reducing runtime by up to 80%.

2. No Function on Filter Columns Pattern

Anti-Pattern: Wrapping a date or timestamp column in a function within your WHERE clause. Applying functions directly on columns is a common performance bottleneck because Snowflake must compute the function on every single row before it can evaluate the filter, disabling micro-partition pruning.

-- Anti-Pattern
SELECT customer_id, total_spent
FROM transactions
WHERE EXTRACT(YEAR FROM transaction_date) = 2023;

Optimized Pattern: Keep the column bare and adjust the logic on the other side of the operator.

-- Optimized Pattern
SELECT customer_id, total_spent
FROM transactions
WHERE transaction_date >= '2023-01-01'::DATE
  AND transaction_date < '2024-01-01'::DATE;

Impact: The bare column allows Snowflake to look at the partition metadata and instantly skip data from 2022 or 2024. Clients report 40% faster insights post-implementation of bare-column filtering alone.

3. Targeted Column Selection Pattern

Anti-Pattern: Relying on the SELECT * habit in production queries. Snowflake stores data in a columnar format. When you select every column, you force the warehouse to retrieve unnecessary data blocks over the network.

-- Anti-Pattern
SELECT * 
FROM customer_behavior_events
WHERE event_type = 'checkout';

Optimized Pattern: Explicitly name only the columns absolutely necessary for your downstream pipeline or dashboard.

-- Optimized Pattern
SELECT session_id, user_id, cart_value
FROM customer_behavior_events
WHERE event_type = 'checkout';

Impact: Drastically reduces Remote Disk I/O. By fetching 3 columns instead of 50, you reduce network transfer times and lower the required compute overhead by at least 50% in wide tables.

4. Incremental MERGE Pattern

Anti-Pattern: Running MERGE statements without a filter on the target table. When creating ELT transformations, developers often try to upsert data by scanning the entire historical table.

-- Anti-Pattern
MERGE INTO target_orders t 
USING source_orders s 
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.status = s.status
WHEN NOT MATCHED THEN INSERT (order_id, status) VALUES (s.order_id, s.status);

Optimized Pattern: Gate your MERGE with a high-watermark or date bounding filter on both the source and the target to restrict the scan surface.

Optimized Pattern
MERGE INTO target_orders t 
USING source_orders s 
ON t.order_id = s.order_id
   -- Prune target table scanning
   AND t.order_date >= (SELECT MIN(order_date) FROM source_orders)
WHEN MATCHED THEN UPDATE SET t.status = s.status
WHEN NOT MATCHED THEN INSERT (order_id, status, order_date) 
VALUES (s.order_id, s.status, s.order_date);

Impact: Restricting the partition space on the target_orders table allows you to avoid performing a full table scan on billions of rows just to update the few thousand records that changed today. This specific fix routinely drops daily warehouse costs by 30%.

5. Pre-Aggregation Pattern for BI Queries

Anti-Pattern: Hitting raw, granular truth tables directly from business intelligence dashboards like Looker or Tableau.

-- Anti-Pattern: Executed on every dashboard load
SELECT store_id, DATE_TRUNC('month', sale_timestamp) as sale_month, SUM(revenue) 
FROM raw_point_of_sale_data
GROUP BY 1, 2;

Optimized Pattern: Create materialized views or clustered aggregate tables overnight. Let the BI tool query the pre-calculated summary.

-- Optimized Pattern: Scheduled via ELT
CREATE OR REPLACE TABLE monthly_store_sales AS (
    SELECT store_id, DATE_TRUNC('month', sale_timestamp) as sale_month, SUM(revenue) as total_revenue
    FROM raw_point_of_sale_data
    GROUP BY 1, 2
);

-- BI Tool queries the lightweight table
SELECT store_id, sale_month, total_revenue 
FROM monthly_store_sales;

Impact: Shifting heavy calculations to scheduled batch processes instead of reactive dashboard clicks prevents Snowflake auto-resume concurrency spikes. This turns unpredictable BI costs into a fixed, managed overhead.

6. Flatten-Once Pattern for Variant/JSON Data

Anti-Pattern: Using multiple LATERAL FLATTEN functions repetitively inside nested subqueries when extracting arrays from semi-structured JSON payloads.

-- Anti-Pattern
SELECT a.value:id::string, b.value:name::string
FROM raw_json_table,
LATERAL FLATTEN(input => raw_json_table.data:users) a,
LATERAL FLATTEN(input => raw_json_table.data:users) b
WHERE a.value:id = b.value:id;

Optimized Pattern: Flatten the JSON data once in an initial CTE, extract all necessary primitive fields, and then process the relational structure downstream.

-- Optimized Pattern
WITH parsed_users AS (
    SELECT 
        user_element.value:id::string AS user_id,
        user_element.value:name::string AS user_name
    FROM raw_json_table,
    LATERAL FLATTEN(input => raw_json_table.data:users) user_element
)
SELECT user_id, user_name 
FROM parsed_users;

Impact: Parsing JSON requires heavy CPU cycles. Flattening the raw variant data once simplifies the query execution plan, drops CPU utilization, and eliminates redundant memory processing.

7. Limited Cross-Join Pattern

Anti-Pattern: Accidentally creating a Cartesian product by joining large tables without appropriate constraints. Joining large tables unconstrained acts as the top cause of massive disk spillage.

-- Anti-Pattern
SELECT t1.id, t2.category 
FROM massive_table_one t1 
JOIN massive_table_two t2 
  ON t1.status = t2.status; -- If 'status' has low cardinality, this explodes.

Optimized Pattern: Always ensure joins rely on unique keys or highly selective composite keys.

-- Optimized Pattern
SELECT t1.id, t2.category 
FROM massive_table_one t1 
JOIN massive_table_two t2 
  ON t1.status = t2.status 
  AND t1.id = t2.reference_id; -- High cardinality constraint added.

Impact: Safeguards against explosive row multiplication. You eliminate the “byte spillage to remote storage” warning in your query profile and drastically enhance query speed.

8. CTE Reuse Pattern

Anti-Pattern: Recalculating the same logic multiple times in disparate parts of a query or in separate tables rather than centralizing it.

-- Anti-Pattern
WITH active_users AS (
    SELECT user_id FROM user_logins WHERE status = 'active'
),
premium_users AS (
    SELECT user_id FROM user_logins WHERE status = 'active' AND tier = 'premium'
)
...

Optimized Pattern: Define the base transformation logically, and then branch off it to prevent Snowflake from executing two separate physical reads.

-- Optimized Pattern
WITH active_users AS (
    SELECT user_id, tier 
    FROM user_logins 
    WHERE status = 'active'
),
premium_users AS (
    SELECT user_id 
    FROM active_users 
    WHERE tier = 'premium'
)
...

Impact: Reduces disk reads by 50% in that specific operation, keeping your data footprint small during the execution lifecycle.

9. Warehouse Right-Sizing & Concurrency Pattern

Anti-Pattern: Scaling warehouses indiscriminately. Over-sizing a warehouse (choosing sizes like Large to X-Large) for simple queries merely burns credits needlessly.

Optimized Pattern: Define warehouse scaling based on the specific bottleneck. Use this assessment matrix:

Workload Issue	Scaling Strategy	Snowflake Action
High Queue Times (too many users)	Scale OUT (Concurrency)	Increase Multi-Cluster Max Clusters
High Disk Spillage (complex queries)	Scale UP (Compute Size)	Change Size (e.g., M to L)
Intermittent usage	Auto-Suspend tuning	Set Suspend to 60 seconds

Impact: You guarantee paying only for what you strictly require. Multi-cluster warehouses guarantee your BI analysts enjoy immediate availability, while aggressive auto-suspend timers ensure your idle compute bill stays fully optimized.

10. Caching-Friendly Query Pattern

Anti-Pattern: Introducing volatile functions into analytical queries that prevent Snowflake from using Result Caching. Whenever a query uses CURRENT_TIMESTAMP() or RANDOM(), Snowflake is forced to bypass the cache and compute fresh logic.

-- Anti-Pattern
SELECT region, SUM(sales)
FROM historical_sales
WHERE calculation_date <= CURRENT_TIMESTAMP();

Optimized Pattern: Utilize fixed bindings or static inputs for analytical queries to maximize your use of Result Caching and return lightning-fast results.

-- Optimized Pattern
-- Pass via your orchestration tool (like dbt or Airflow) as a static string
SELECT region, SUM(sales)
FROM historical_sales
WHERE calculation_date <= '2023-10-31 00:00:00';

Impact: Result cache provides a 100% cost saving and returns results in milliseconds. The query does not consume active virtual warehouse compute credits if the exact result is retrieved from the global state cache.

Micro-Partition Pruning in Practice: Clustering and Search Optimization

The cleanest SQL performs best when the underlying data is highly organized. Snowflake typically handles micro-partitioning automatically based on natural ingestion order. For most date-driven tables, this natural clustering works perfectly.

Configuring explicit Clustering Keys forces the engine to co-locate similar data when queries filter on non-date metrics (like a specific customer_id or device_uuid). Alternatively, you can enable the Search Optimization Service (SOS). SOS builds specialized background maintenance paths for lightning-fast point lookups. We advise a thorough cost-benefit analysis before enabling these features to maximize efficiency, as background clustering consumes its own subset of compute credits.

Beyond Queries: Governance, Cost Attribution, and Culture

Technical optimizations succeed long-term when backed by strong FinOps principles. Implementing snowflake performance tuning relies upon a cultural shift where developers understand the financial weight of their code. We guide teams through this transformation by connecting engineering practices directly to business goals.

Ensure cost control by setting up Resource Monitors to automatically pause warehouses when daily credit limits are reached. Implement strict tag-based cost attribution so every department owns their specific cloud spend. Cultivate a proactive data culture by training your BI users and Analytics Engineers to default to optimized practices from day one. You can read more about broad financial optimization structures in the industry-standard FinOps Foundation framework. See how our internal standards map directly to our modern data engineering services.

How Stellans Helps Teams Apply These Patterns at Scale

Implementing optimization patterns across tens of thousands of legacy SQL scripts is a powerful step forward. That is where we step in. Our goal is your growth. We audit complicated Snowflake environments, identify the most expensive workloads, and refactor pipelines to embed performance directly into your data framework.

Through our data architecture evaluations and hands-on real-world projects, we serve as your technical translators. We simplify complex pipeline bottlenecks, overhaul legacy scripts, and ensure your analytics foundation is prepared to scale efficiently and cost-effectively.

Conclusion

Optimizing your Snowflake environment requires addressing underlying structural workflows. Maintaining an optimized cloud bill begins with your code. By adopting micro-partition pruning strategies, auditing your Query Profiles for spillage, and standardizing your rewrite patterns, you build a robust and highly predictable data infrastructure.

Protect your technology budget by optimizing your queries today. We invite you to audit your top 5 most expensive queries. If you are ready for a systematic approach to cost optimization, schedule a Snowflake cost and performance review with the Stellans team.

Frequently Asked Questions

How can I identify bottlenecks using Snowflake Query Profile? You can identify bottlenecks by locating the specific query in your History tab and accessing its Query Profile. Look at the operator tree to find the node consuming the most execution time, and check the statistics panel for high local/remote disk spillage or poor partition pruning metrics.

What are common Snowflake SQL anti-patterns that increase cost? Common anti-patterns include failing to filter early (lack of predicate pushdown), wrapping filter columns in date/time functions which disables pruning, using SELECT * on wide tables, and executing MERGE statements without bounding dates on the target table.

How does micro-partition pruning improve Snowflake query performance? Snowflake groups table data into micro-partitions and tracks the minimum and maximum values inside them. By using clean filters in your queries (SARGable queries), Snowflake skips reading partitions that do not contain relevant data, drastically lowering I/O consumption and query runtime.

References

Article By:

https://stellans.io/wp-content/uploads/2026/01/leadership-1-1.png

David Ashirov

Co-founder & CTO

Get free consultation

10 SQL Patterns to Drastically Reduce Your Snowflake Costs

Why SQL Patterns Matter More Than Warehouse Size

How to Use Snowflake Query Profile to Find Bottlenecks

10 SQL Patterns to Drastically Reduce Your Snowflake Costs

Optimized Pattern: Push your predicates (filters) as close to the foundational tables as possible.

Micro-Partition Pruning in Practice: Clustering and Search Optimization

Beyond Queries: Governance, Cost Attribution, and Culture

How Stellans Helps Teams Apply These Patterns at Scale

Conclusion

Frequently Asked Questions

References

Article By:

David Ashirov

Related Posts

Let’s
Talk

Get a Free Data Audit

10 SQL Patterns to Drastically Reduce Your Snowflake Costs

Why SQL Patterns Matter More Than Warehouse Size

How to Use Snowflake Query Profile to Find Bottlenecks

10 SQL Patterns to Drastically Reduce Your Snowflake Costs

Optimized Pattern: Push your predicates (filters) as close to the foundational tables as possible.

Micro-Partition Pruning in Practice: Clustering and Search Optimization

Beyond Queries: Governance, Cost Attribution, and Culture

How Stellans Helps Teams Apply These Patterns at Scale

Conclusion

Frequently Asked Questions

References

Article By:

David Ashirov

Related Posts

Let’s Talk

Get a Free Data Audit

Get a Free Consultation

Let's talk about your project

Select an available slot to get in touch with Stellans so that one of our representatives can contact you and start a discussion.

David Ashirov

Co-founder, CTO

30 minutes

Contact us

Select an available slot to get in touch with Stellans so that one of our representatives can contact you and start a discussion.

Anton Malyshev

Co-founder, COO

30 minutes

Contact us

Select an available slot to get in touch with Stellans so that one of our representatives can contact you and start a discussion.

Vitaly Lilich

Co-founder, CEO

30 minutes

Contact us

Thank You

Thank You

Thank You

Let’s
Talk

Let's talk about
your project

Select an available slot to
get in touch with Stellans
so that one of our representatives can contact you and start a discussion.

Select an available slot to
get in touch with Stellans
so that one of our representatives can contact you and start a discussion.

Select an available slot to
get in touch with Stellans
so that one of our representatives can contact you and start a discussion.

Thank
You

Thank
You

Thank
You