Run incremental in different warehouse than full refresh

Question

I would like to build an incremental model that utilizes two different warehouse specs depending on whether the incremental or the full-refresh version of the run is occurring.

I was thinking that something like the following might be possible but have not yet been able to make it work:

{% if is_incremental() %}
    {{ config(snowflake_warehouse=var("m_warehouse")) }}
{% else %}
    {{ config(snowflake_warehouse=var("xl_warehouse")) }} 
{% endif %}

{{
    config(
        materialized="incremental", 
        unique_key="pk_number", 
        on_schema_change="fail"
    )
}}

-- my model logic
select 1;

The answer to this should be a config block at the top of a model file.

Answer 1

In your case, you can inline the Jinja inside the config block:

{{
  config({
    "materialized": "incremental", 
    "unique_key": "pk_number", 
    "on_schema_change": "fail",
    "snowflake_warehouse": var('m_warehouse') if is_incremental() else var('xl_warehouse')
  })
}}

Note - More complicated Jinja logic won't work here. There is a PR to allow for overriding the default macro, but it is not yet released: https://github.com/dbt-labs/dbt-snowflake/pull/503