clustering keys<\/a> to these large tables, however performance improvement was not noticeable and all table partitions were still scanned.<\/p>\n\n\n\nThe fix was even simpler, we implemented materialized tables per destination dataset, keeping only the data of the accounts subscribed to that dataset.<\/p>\n\n\n\n
Before (View on top of full table):<\/strong><\/p>\n\n\n\ncreate view data_destination_invoices as \nselect * from invoices;\n\n-- data destinations query\nselect * from data_destination_invoices where account_id = X;\n<\/code><\/pre>\n\n\n\nAfter (Materialized table):<\/strong><\/p>\n\n\n\ncreate table data_destination_invoices as \nselect * from invoices \nwhere account_id in (\n\tselect account_id from data_destinations where dataset == 'invoices'\n);\n\n-- data destinations query\nselect * from data_destination_invoices where account_id = X;\n<\/code><\/pre>\n\n\n\nInstead 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<\/code> latency to <1 minute!<\/p>\n\n\n\nReducing the number of columns materialized<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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%.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Replacing joins with IN<\/code> filters when columns are unused<\/h3>\n\n\n\nIt 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.<\/p>\n\n\n\n
In such cases, replacing these joins with a simple WHERE ... IN (subquery)<\/code> filter achieves the same functional result with less computational effort.<\/p>\n\n\n\nBefore (Inefficient join):<\/strong><\/p>\n\n\n\nselect m.id, m.customer_id, m.date\nfrom mrr_movements m\ninner join active_customers c on m.customer_id = c.id\n<\/code><\/pre>\n\n\n\nAfter (Optimized filter using IN<\/code>):<\/strong><\/p>\n\n\n\nselect m.id, m.customer_id, m.date\nfrom mrr_movements m\nwhere m.customer_id in (select id from active_customers)\n<\/code><\/pre>\n\n\n\nBy eliminating the join, we reduced data shuffling and allowed Snowflake to leverage more efficient filter pushdowns and optimize certain queries\u2019 execution times by 30%.<\/p>\n\n\n\n
Removing unnecessary ordering from dbt models<\/h3>\n\n\n\n
Ordering data is an expensive operation, especially on large datasets. While dbt models sometimes include ORDER BY<\/code>clauses 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<\/code> clauses from dbt models can result in substantial compute savings, particularly in large transformations.<\/p>\n\n\n\nBy doing so, we eliminated a costly sort step from our model builds without affecting end-user functionality.<\/p>\n\n\n\n
Replacing dynamic range generation with a materialized table<\/h3>\n\n\n\n
Another optimization involved replacing\u00a0on-the-fly date range generation\u00a0(commonly used in time-series reports and metrics tables) with a\u00a0pre-materialized calendar table. Generating date ranges dynamically within queries not only adds compute overhead but also increases the number of processed rows unnecessarily.<\/p>\n\n\n\n
By maintaining a materialized dates table and joining to it as needed, we reduced computation and simplified query logic.<\/p>\n\n\n\n
Before (Dynamic date generation in query):<\/strong><\/p>\n\n\n\nwith dates as (\n\tselect dateadd(day, seq4(), '2010-01-01') as date\n\tfrom table(generator(rowcount => 10000))\n)\n\nselect d.date, m.mrr\nfrom dates as d\nleft join movements as m\non d.date = m.date\n<\/code><\/pre>\n\n\n\nAfter (Joining pre-materialized dates table):<\/strong><\/p>\n\n\n\ncreate table materialized_dates as \nselect dateadd(day, seq4(), '2010-01-01') as date\nfrom table(generator(rowcount => 10000))\n\nselect d.date, m.mrr\nfrom materialized_dates as d\nleft join movements as m\non d.date = m.date\n<\/code><\/pre>\n\n\n\nBeyond 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\u2019t put large values, e.g. 1_000_000 that you don\u2019t need, assuming that Snowflake will somehow skip them in later steps of the query.<\/p>\n\n\n\n
Additional small improvements<\/h3>\n\n\n\n
Beyond larger optimizations, small SQL improvements had a noticeable impact on Snowflake performance and costs. These quick wins helped prevent unnecessary compute consumption:<\/p>\n\n\n\n
\n- 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<\/code> on every CTE before joining.<\/li>\n\n\n\n- 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.<\/li>\n\n\n\n
- Fix incorrect joins: Query profiles revealed exponential row growth caused by missing join conditions. Ensuring complete and correct join logic prevented accidental Cartesian products.<\/li>\n\n\n\n
- Minimize
SELECT *<\/code>: Explicitly selecting only needed columns reduced I\/O and materialization overhead, especially for wide tables.<\/li>\n\n\n\n- 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.<\/li>\n<\/ul>\n\n\n\n
These targeted SQL adjustments, while simple, collectively contributed to reducing query runtimes and Snowflake credit usage.<\/p>\n\n\n\n
A dbt checklist for Snowflake cost optimization<\/h1>\n\n\n\n
Given that dbt was a major part of our data transformation layer, optimizing our dbt workflows was crucial for sustained cost efficiency. Here\u2019s what we did:<\/p>\n\n\n\n
Pruning unused & legacy models<\/h3>\n\n\n\n
An audit revealed several dbt models that:<\/p>\n\n\n\n
\n- Were no longer used downstream.<\/li>\n\n\n\n
- Were built for one-off analyses.<\/li>\n\n\n\n
- Introduced redundant models into the Directed Acyclic Graph (DAG).<\/li>\n<\/ul>\n\n\n\n
We tagged these models as deprecated or completely removed them, reducing both runtime and complexity.<\/p>\n\n\n\n
Incremental materializations<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
{{ config(materialized='incremental') }}\n\nselect ...\nfrom source_table\n{% if is_incremental() %}\nwhere updated_at > (select max(updated_at) from {{ this }})\n{% endif %}\n<\/code><\/pre>\n\n\n\nThis simple pattern cut down our warehouse compute time dramatically.<\/p>\n\n\n\n
Incremental strategy<\/h3>\n\n\n\n
As part of our dbt optimizations, we revisited the choice of incremental strategies used in table builds. While dbt defaults to using merge<\/code> for incremental models, we observed that this approach became inefficient for wide tables with many columns.<\/p>\n\n\n\nThe reason is straightforward: merge<\/code> 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.<\/p>\n\n\n\nIn cases where updates could be isolated to specific partitions, we achieved better performance by switching to a delete + insert<\/code> strategy, which first, deletes existing rows for the affected key(s) and then, inserts the refreshed data.<\/p>\n\n\n\nBefore (MERGE strategy, implicit in dbt incremental models):<\/strong><\/p>\n\n\n\n{{ config(\n\tmaterialized='incremental', \n\tunique_key='id') \n}}\nselect * from source_table \nwhere updated_at >= (select max(updated_at) from {{ this }})\n<\/code><\/pre>\n\n\n\nThis would generate a MERGE<\/code> statement under the hood.<\/p>\n\n\n\nAfter (DELETE + INSERT strategy):<\/strong><\/p>\n\n\n\n{{ config(\n\tmaterialized='incremental', \n\tunique_key='id', \n\tincremental_strategy='delete+insert') \n}}\nselect * from source_table \nwhere updated_at >= (select max(updated_at) from {{ this }})\n<\/code><\/pre>\n\n\n\nWith this small change, dbt will issue a\u00a0DELETE<\/code>\u00a0for affected keys followed by a bulk\u00a0INSERT<\/code>, avoiding the costly merge logic. In our case, we reduced model execution times by\u00a020%.<\/p>\n\n\n\nMaterialization strategy review<\/h3>\n\n\n\n
We assessed each model based on its purpose and usage pattern, applying a more deliberate approach:<\/p>\n\n\n\n
\n- Tables were used for:\n
\n- Large, compute-intensive transformations.<\/li>\n\n\n\n
- Models reused in multiple downstream models.<\/li>\n\n\n\n
- Datasets supporting frequently accessed BI dashboards where query performance was critical.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Incremental tables were adopted wherever feasible, especially for large datasets with append-only or easily isolated update patterns, to reduce unnecessary recomputation.<\/li>\n\n\n\n
- Views were preferred for:\n