The Overlooked Cost of Snowflake Query Compilation Time

When analytics engineers troubleshoot slow Snowflake queries, the conversation usually centers around execution time.

We look at bytes scanned, partition pruning, local/remote spilling, and whether a larger warehouse would help. Those are all important factors, but they don’t tell the whole story.

Snowflake also spends time compiling a query before execution begins. For simple queries, compilation time is usually small enough to ignore. For more complex analytics workloads, compilation can become a major percentage of total query time.

This becomes especially noticeable in modern analytics engineering environments where:

• dbt models generate large SQL statements
• Dashboards query deeply layered views
• Models contain many joins and derived fields
• Complex business logic is repeated across transformations
• Wide reporting tables carry several dozens of columns

In some cases, the warehouse finishes execution quickly, but users still experience slow query performance because Snowflake spends significant time compiling the query plan before execution starts.

Once we started looking at compilation time directly, it changed how we thought about model design, view layering, and performance optimization.

On a recent client project, a data team was using Snowflake behind a semantic layer to power a high-traffic, user-facing web application. When we reviewed their most frequent and longest-running queries, the main bottleneck was compilation time rather than execution time. By implementing the recommendations outlined here, we were able to downsize their multi-cluster warehouse and reduce warehouse spend by roughly 40%.

What Snowflake Is Doing During Query Compilation

Before a query is executed, Snowflake turns SQL text into an executable plan.

During compilation, Snowflake:

• Parses and validates the SQL
• Resolves referenced objects, columns, and functions
• Applies permissions and metadata rules
• Expands views, CTEs, and nested subqueries
• Evaluates optimization strategies
• Determines join order and execution strategies
• Builds the query plan shown in the Query Profile

Snowflake exposes the time spent doing this work directly through the compilation_time field in the ACCOUNT_USAGE.QUERY_HISTORY view as well as in the Monitoring > Query History page within their web UI.

That metric is important because it lets us separate two very different performance problems:

This distinction matters because the fixes are different. If execution time is slow, increasing warehouse size may help. If compilation time is slow, warehouse resizing usually does nothing. This is because compilation happens in Snowflake’s cloud services layer, not inside the virtual warehouse compute layer.

Why Compilation Time Gets Expensive and Best Practices for Reducing It

Compilation time tends to grow when Snowflake has to reason about larger and more complex SQL.

There are a few common patterns that drive this. Here are some best practices for addressing them:

High Query Plan Complexity

Every join, aggregation, union, window function, subquery, and view expansion adds more nodes to the query plan.

This means the optimizer has more possible execution paths to evaluate. As plans grow, compilation time grows with them.

Reduce Query Plan Complexity

Good candidates for query complexity simplification include:

• Heavily reused dashboard views
• Models with many joins
• Semantic-layer queries
• dbt models with long ephemeral chains
• Repeated lookup joins

If the same join logic gets reused frequently, consider materializing it upstream into a table. That moves complexity out of repeated end-user queries.

Querying Deeply Layered Views

A dashboard query might hit a view that references another view, which references another view, which joins several staging tables.

To the analyst, the query looks simple. To Snowflake, the query may expand into a massive execution graph with lengthy underlying SQL that Snowflake must compile.

Materialize Frequently Queried Complex Views

For heavily queried models, materializing the logic into a table can significantly reduce repeated compilation work.

select * Against Wide Tables

Wide reporting tables often carry dozens or even hundreds of columns.

Using select* forces Snowflake to expand and resolve all column names and data types in addition to parsing the query to understand whether each column is used in it.

This increases both plan complexity and execution overhead.

Select Only the Columns You Need

To combat this, avoid select* in production models, dashboards, and reusable views.

Explicitly selecting columns in your queries reduces the amount of metadata Snowflake has to resolve and reduces the amount of time the optimizer spends creating the query plan.

Instead of:

select * from analytics.orders
Prefer:
select
      customer_id
      , order_date
      , total_revenue
from analytics.orders

This becomes increasingly important for wide reporting tables and semantic-layer views.

Large Repeated Business Logic

Huge case when blocks, repeated regex logic, attribution rules, and derived calculations all increase the amount of SQL Snowflake has to parse and optimize.

This is especially common in dbt projects when business logic gets copied between models instead of centralized.

Move Complex Business Logic Upstream

If the logic is reused frequently, calculate it once upstream and expose it as a named field.

Good candidates include:

• Channel grouping logic
• Product categorization mappings
• Large conditional financial or attribution rules

This improves maintainability while reducing repeated compilation overhead.

Long dbt Compilation Chains

dbt makes it easy to compose transformations through references, ephemeral models, and reusable logic.

That flexibility is valuable, but it can also generate extremely large compiled SQL.

A model that looks manageable in dbt may compile into thousands of lines of SQL once all references and ephemeral dependencies expand.

Be Intentional with dbt Ephemeral Models

A few ephemeral models are usually fine. However, large chains of nested ephemeral models can generate enormous compiled SQL statements that increase compilation time significantly.

For heavily reused transformations, materialized intermediate models are often a better tradeoff.

Measure Compilation Time Directly

One of the easiest ways to identify compilation-heavy queries is through the QUERY_HISTORY view.

select
      query_id
      , start_time
      , warehouse_name
      , warehouse_size
      , compilation_time / 1000 as compilation_seconds
      , execution_time / 1000 as execution_seconds
      , total_elapsed_time / 1000 as total_seconds
      , round(compilation_time / nullif(total_elapsed_time, 0) * 100, 
2) as compilation_pct
from snowflake.account_usage.query_history
where start_time >= dateadd(hour, -24, current_timestamp())
    and execution_status = 'SUCCESS'
    and total_elapsed_time > 0
order by compilation_pct desc;

Queries where compilation makes up a large percentage of total elapsed time are strong candidates for SQL simplification.

Warehouse Resizing Is Not a Compilation Fix

One of the most important takeaways is that warehouse size primarily affects execution performance.

Compilation happens in Snowflake cloud services.

If a query spends most of its time compiling, moving from a Medium warehouse to a Large warehouse most likely will not improve performance in a meaningful way.

In those situations, the better optimization path is usually following the recommendations detailed above.

The Analytics Engineering Tradeoff

None of this means complex SQL is inherently bad. Analytics engineering often requires complicated transformations.

The real goal is not “smallest possible SQL.” The goal is:

• Maintainable models
• Reusable business logic
• Predictable performance
• Simpler execution plans where possible

Sometimes the right solution is still a complex transformation. The important thing is understanding when architectural convenience creates unnecessary query complexity.

Final Thoughts

Compilation time is easy to overlook because it happens before execution begins.

For simple workloads, that is usually fine.

But modern analytics engineering workflows often generate significantly more SQL complexity than teams realize.

As dbt projects grow, semantic layers expand, and dashboards rely on increasingly reusable models, compilation time becomes a meaningful part of overall query performance.

Understanding how Snowflake compiles queries changes how you think about model design.

Performance optimization stops being only about warehouse size and execution speed. It also becomes a question of how much complexity Snowflake must evaluate before execution can even begin.

That shift in perspective leads to simpler models, more maintainable transformations, and faster user experiences overall.