Change Snowflake warehouse when full-refresh or incremental run

Help
, , ,
1

Hi,

We’re trying to set up our dbt Core project so that an individual incremental model can be configured to run with different Snowflake Warehouses depending on whether it’s running in a full-refresh, or an incremental mode but haven’t had luck yet.

We’ve tried combining a few flags & functions into a given macro, including:

We found the most sensible way to be as such:

{%- macro needs_full_refresh() -%}
    -- This uses dbt's internal "adaptor" object to detect if the model to
    -- materialize already exists.
    {%-
        set relation = adapter.get_relation(
            database=this.database,
            schema=this.schema,
            identifier=this.identifier
        )
    -%}
    {%- set relation_exists = (relation != None) -%}
    {%- set needs_full_refresh = (not relation_exists or flags.FULL_REFRESH) -%}
	{{ log("Relation found     -> " ~ relation) }}
	{{ log("Relation exists    -> " ~ relation_exists) }}
	{{ log("Full-refresh flag  -> " ~ flags.FULL_REFRESH) }}
	{{ log("Needs full-refresh -> " ~ needs_full_refresh) }}
    -- If the model does not exist OR the full-refresh flag was passed we
    -- consider the model needs a full-refresh.
    {{ return(needs_full_refresh) }}
{%- endmacro -%}

To be used in a model as so:

{{
    config(
        materialized="incremental",
        snowflake_warehouse="LARGE" if needs_full_refresh() else "SMALL"
    )
}}

...

Running dbt with --debug we see some interesting logs: it looks as if the macro is run as expected (during some sort of runtime?) but is simply ignored:

dbt run --debug --log-format=debug -m <model>
# 16:29:11.779397 [debug] [Thread-3 (]: Relation found     -> "DB"."SCHEMA"."MY_RELATION"
# 16:29:11.780001 [debug] [Thread-3 (]: Relation exists    -> True
# 16:29:11.780437 [debug] [Thread-3 (]: Full-refresh flag  -> False
# 16:29:11.780842 [debug] [Thread-3 (]: Needs full-refresh -> False
# ...
# 16:29:12.064190 [debug] [Thread-3 (]: On model.moo.common__users: use warehouse LARGE

Moreover, with flags.FULL_REFRESH only

We also observe a very interesting behaviour when making this simpler by using the flags.FULL_REFRESH variable ONLY. We see that the DEBUG logs ALWAYS show the “SMALL” warehouse being picked, however we see from Snowflake’s QUERY logs that the “LARGE” warehouse is actually picked when passing the --full-refresh flag.

{{
    config(
        materialized="incremental",
        snowflake_warehouse="LARGE" if flags.FULL_REFRESH else "SMALL"
    )
}}
  • debug logs
10:42:34.016994 [debug] [Thread-3 (]: Using snowflake connection "model.<my_model>"
10:42:34.018540 [debug] [Thread-3 (]: On model.<my_model>: use warehouse SMALL
  • Snowflake’s query logs
***************************[ 13 ]***************************
START_TIME       | 2025-11-20 16:42:13.589 +0000
WAREHOUSE_NAME   | LARGE
QUERY_TEXT_TRUNC | select * from (
                         ...

***************************[ 14 ]***************************
START_TIME       | 2025-11-20 16:42:13.404 +0000
WAREHOUSE_NAME   | MEDIUM
QUERY_TEXT_TRUNC | use warehouse LARGE

So, we’re a little confused. Our understanding so far is:

  • That a model’s warehouse is set at “compile time” or “parse time”
  • It also looks like when calling the adaptor’s method, the execution of our macro is deferred to “run time”, even though the warehouse has ALREADY been set for a model
  • It finally looks as if that when using flags.FULL_REFRESH only, then the if ... else snowflake_warehouse assignment in the configuration block is actually picked up at “compile time”, even though the debug logs show otherwise (bug?).

We’re a little behind in our versions, but not too much.

  • dbt-core → 1.9.0
  • dbt-snowflake → 1.9.0

Has anyone managed to achieve this? Can anyone spot anything we might be doing wrong, or is that simply an impossible thing to achieve in the current state of the dbt-core and dbt-snowflake projects?

2

you could probably use https://hub.getdbt.com/Montreal-Analytics/snowflake_utils/latest/
or incorporate the logic based on your preferences by creating custom macro that’s based on this:
https://github.com/Montreal-Analytics/dbt-snowflake-utils/blob/master/macros/warehouse_size.sql

Note: @navedgaras originally posted this reply in Slack. It might not have transferred perfectly.

3 
    {%- set warehouse = 'DW_DBT_CLOUD_FULL_PRODUCTION' -%}
  {%- endif -%}```

<sub>Note: `@Daniel Wiszowaty` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1764014430674879?thread_ts=1763723208.461049&cid=CBSQTAPLG). It might not have transferred perfectly.</sub>
4 
    {%- set warehouse = 'DW_DBT_CLOUD_FULL_PRODUCTION' -%}
  {%- endif -%}```
`flags.FULL_REFRESH` does not work in macros

<sub>Note: `@Daniel Wiszowaty` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1764014564941689?thread_ts=1763723208.461049&cid=CBSQTAPLG). It might not have transferred perfectly.</sub>