How we reduced Snowflake costs by 70%
Snowflake is a powerful data platform known for its scalability and ease of use, but its consumption-based pricing requires careful cost management. Without active monitoring and optimization, usage can grow unpredictably and lead to substantial expenses.
In a previous post, we shared how our modern data stack enables analysts to build visualizations, deliver customer insights, and support SaaS reporting. We are also using Snowflake to power certain ChartMogul platform features such as Destinations and Benchmarks. However, as usage grew, so did our Snowflake spend. This is the story of how we systematically analyzed, debugged, and optimized our Snowflake workloads, ultimately reducing our weekly costs by 70%, while maintaining both performance and data freshness.
In this guide, we’ll walk through how we identified cost drivers, the practical optimization techniques we implemented, and the dbt-specific strategies that had the greatest impact.
Before optimizing, we had to understand where our credits were actually going.
Sorting by warehouse & consumed credits
The first step was to check credit consumption by warehouse to see which workloads were the heaviest hitters. You should always try to identify:
- Warehouses that are over-provisioned (too large for their actual workload).
- ETL pipelines running on the same warehouse as BI queries, causing contention.
- Warehouses with long idle times but no auto-suspend configured.
None of these applied to our case as we have already done some heavy work in the past to separate warehouses per usage type, as well as properly size, scale and configure them. However, this step will also give you some valuable insights on the usage type which will allow you to take appropriate measures later on. In our case:
- 80% of credits were consumed for transformation purposes, hence this is where we had to focus in order to reduce our spend.
- a tiny percentage of credits was consumed for live queries and we also checked that specific dashboards were queried more than others, hence allowed us to apply proper materialization to certain models exposed to Superset, Jupyter Notebooks, HEX and other tools in our RevOps stack.
Sorting queries by duration & execution time
The next step was to dive into the Query History in Snowflake and sort daily queries by total execution time. You should focus on the following:
- Identify the long-running ones:
- if the majority of their duration is spent on execution, mark them as candidates for optimization.
- if you notice lots of queueing then you might need to see whether they execute at the same time as other heavy queries, hence you need to try to optimize those. Otherwise, you might need to increase the warehouse size to run all of them or even adjust their scaling strategy.
- Identify the type of long-running queries. Is it mostly INSERT, MERGE, DELETE, SELECT, COPY or CREATE table queries? If it’s mostly any of the first 3 then it might be tough to optimize anything within Snowflake, but you could investigate improvements on ingestion side. Otherwise there is always room for improvements in SELECT, COPY or CREATE queries using some of the tips below.
Analyzing Query Profiles
For the top offenders, we analyzed the Query Profiles in detail. This helped us pinpoint:
- Queries where >70% of the execution time was spent just scanning data.
- Expensive and even incorrect joins with data skew and network shuffling.
- Expensive table creations with tens or hundred of columns being read and materialized.
- Unnecessary deduplications and dataset ordering.
This level of profiling gave us a prioritized list of where optimization efforts would have the highest return.
Given the sorted list of inefficient queries, we audited all SQL queries and found various inefficiencies.
Minimizing table scans with targeted materializations
One of our most impactful changes was addressing inefficient table scans for a few COPY queries. ChartMogul’s Destinations feature is currently built on top of Snowflake. Initially we only implemented views for certain destination datasets, assuming that Snowflake would be “smart” enough to scan only the needed micro-partitions for every account subscribed per dataset. However that was not the case. Snowflake was scanning the whole underlying table on every query leading to COPY duration times of 30-50 minutes for certain datasets of our largest accounts.
We initially tested clustering keys to these large tables, however performance improvement was not noticeable and all table partitions were still scanned.
The fix was even simpler, we implemented materialized tables per destination dataset, keeping only the data of the accounts subscribed to that dataset.
Before (View on top of full table):
create view data_destination_invoices as
select * from invoices;
— data destinations query
select * from data_destination_invoices where account_id = X;
After (Materialized table):
create table data_destination_invoices as
select * from invoices
where account_id in (
select account_id from data_destinations where dataset == ‘invoices’
);
— data destinations query
select * from data_destination_invoices where account_id = X;
Instead of scanning 1 billion row tables daily, we would query tables of a few tens of million records. This dramatically reduced the scan footprint and improved COPY latency to <1 minute!
Reducing the number of columns materialized
An important optimization that significantly contributed to reducing our Snowflake costs was minimizing the number of columns materialized in our tables. In several cases, we identified dbt models that joined multiple upstream tables and materialized all available columns into the final dataset. This practice resulted in tables containing 70 to 100 columns, many of which had little to no downstream usage.
Through analysis of Query Profiles, we observed that a substantial portion of query execution time was spent on materializing these excessive columns. This was more prominent on models containing large text columns or custom attributes.
To address this, we assessed how each table was consumed by BI tools, analytics workflows, and downstream models. Columns, and even custom attributes, that were not actively used were removed from materialization. By reducing the number of columns, execution times for affected queries were reduced by 30-50%.
This optimization highlighted the importance of intentional data modeling. Simply joining tables and materializing all available columns may seem convenient initially, but it can become a hidden driver of escalating Snowflake costs over time.
Replacing joins with IN filters when columns are unused
It is common to see joins in queries that do not actually use any columns from the joined table. These joins often exist purely for filtering purposes, but they come with additional overhead such as join condition evaluations, data shuffling, and scan costs on both tables.
In such cases, replacing these joins with a simple WHERE … IN (subquery) filter achieves the same functional result with less computational effort.
Before (Inefficient join):
select m.id, m.customer_id, m.date
from mrr_movements m
inner join active_customers c on m.customer_id = c.id
After (Optimized filter using IN):
select m.id, m.customer_id, m.date
from mrr_movements m
where m.customer_id in (select id from active_customers)
By eliminating the join, we reduced data shuffling and allowed Snowflake to leverage more efficient filter pushdowns and optimize certain queries’ execution times by 30%.
Removing unnecessary ordering from dbt models
Ordering data is an expensive operation, especially on large datasets. While dbt models sometimes include ORDER BYclauses for perceived neatness or convenience, this is rarely necessary at the data warehouse level. Most downstream BI tools can apply ordering dynamically as needed. Removing these ORDER BY clauses from dbt models can result in substantial compute savings, particularly in large transformations.
By doing so, we eliminated a costly sort step from our model builds without affecting end-user functionality.
Replacing dynamic range generation with a materialized table
Another optimization involved replacing on-the-fly date range generation (commonly used in time-series reports and metrics tables) with a pre-materialized calendar table. Generating date ranges dynamically within queries not only adds compute overhead but also increases the number of processed rows unnecessarily.
By maintaining a materialized dates table and joining to it as needed, we reduced computation and simplified query logic.
Before (Dynamic date generation in query):
with dates as (
select dateadd(day, seq4(), ‘2010-01-01’) as date
from table(generator(rowcount => 10000))
)
select d.date, m.mrr
from dates as d
left join movements as m
on d.date = m.date
After (Joining pre-materialized dates table):
create table materialized_dates as
select dateadd(day, seq4(), ‘2010-01-01’) as date
from table(generator(rowcount => 10000))
select d.date, m.mrr
from materialized_dates as d
left join movements as m
on d.date = m.date
Beyond performance improvements, this approach also ensured consistency in date-related calculations across models and reports. We applied the same technique for CTEs using generators, and we also observed significant performance gains tuning the generator size. Don’t put large values, e.g. 1_000_000 that you don’t need, assuming that Snowflake will somehow skip them in later steps of the query.
Additional small improvements
Beyond larger optimizations, small SQL improvements had a noticeable impact on Snowflake performance and costs. These quick wins helped prevent unnecessary compute consumption:
- Filter datasets early: There were a few models which were joining multiple tables only for our account data, however Snowflake query optimizer was pushing the filtering condition on the last step. Execution time reduced dramatically by just adding tenant_id = X on every CTE before joining.
- Remove unnecessary window functions: We often saw window functions used for deduplication where upstream models could have provided unique records. By deduplicating earlier, we avoided costly per-row operations.
- Fix incorrect joins: Query profiles revealed exponential row growth caused by missing join conditions. Ensuring complete and correct join logic prevented accidental Cartesian products.
- Minimize SELECT *: Explicitly selecting only needed columns reduced I/O and materialization overhead, especially for wide tables.
- Use clustering only when justified: Clustering improved performance for large, frequently filtered tables but added overhead elsewhere. We applied it selectively based on query patterns and table size.
These targeted SQL adjustments, while simple, collectively contributed to reducing query runtimes and Snowflake credit usage.
Given that dbt was a major part of our data transformation layer, optimizing our dbt workflows was crucial for sustained cost efficiency. Here’s what we did:
Pruning unused & legacy models
An audit revealed several dbt models that:
- Were no longer used downstream.
- Were built for one-off analyses.
- Introduced redundant models into the Directed Acyclic Graph (DAG).
We tagged these models as deprecated or completely removed them, reducing both runtime and complexity.
Incremental materializations
The most effective change was applying incremental materializations for any model dealing with large, append-only, or slowly-changing datasets. By using dbt’s incremental configuration, we ensured only new or modified data was processed.
{{ config(materialized=’incremental’) }}
select …
from source_table
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
This simple pattern cut down our warehouse compute time dramatically.
Incremental strategy
As part of our dbt optimizations, we revisited the choice of incremental strategies used in table builds. While dbt defaults to using merge for incremental models, we observed that this approach became inefficient for wide tables with many columns.
The reason is straightforward: merge performs row-by-row comparisons, matching keys and updating all columns involved. For tables with dozens of columns, this added significant computational overhead, slowing down builds and increasing warehouse credits consumed.
In cases where updates could be isolated to specific partitions, we achieved better performance by switching to a delete + insert strategy, which first, deletes existing rows for the affected key(s) and then, inserts the refreshed data.
Before (MERGE strategy, implicit in dbt incremental models):
{{ config(
materialized=’incremental’,
unique_key=’id’)
}}
select * from source_table
where updated_at >= (select max(updated_at) from {{ this }})
This would generate a MERGE statement under the hood.
After (DELETE + INSERT strategy):
{{ config(
materialized=’incremental’,
unique_key=’id’,
incremental_strategy=’delete+insert’)
}}
select * from source_table
where updated_at >= (select max(updated_at) from {{ this }})
With this small change, dbt will issue a DELETE for affected keys followed by a bulk INSERT, avoiding the costly merge logic. In our case, we reduced model execution times by 20%.
Materialization strategy review
We assessed each model based on its purpose and usage pattern, applying a more deliberate approach:
- Tables were used for:
- Large, compute-intensive transformations.
- Models reused in multiple downstream models.
- Datasets supporting frequently accessed BI dashboards where query performance was critical.
- Incremental tables were adopted wherever feasible, especially for large datasets with append-only or easily isolated update patterns, to reduce unnecessary recomputation.
- Views were preferred for:
- Lightweight data abstractions.
- Datasets serving infrequently accessed reports or exploratory analysis, where real-time freshness outweighed the need for materialization.
Frequency & scheduling optimization
In parallel, we re-evaluated the run frequency of each model, introducing dbt tags and orchestrating schedules based on actual business needs:
- Daily schedules for critical, operational data models.
- Weekly or monthly refreshes for slower-moving datasets, non-critical metrics and datasets that are only needed the 1st day of each month. One such example is our Benchmarks datasets which are powered by Snowflake and are updated only the 1st day of each month.
This allowed us to prioritize warehouse resources for high-impact workloads while avoiding unnecessary daily recomputation of static or slow-changing data.
By combining Snowflake query profiling, targeted table materializations, incremental dbt models, and warehouse optimizations, we reduced our Snowflake spend by 70%.
The key principles behind this improvement were:
