Set snowflake_warehouse in Model using env_vars

Help
, ,
1

The problem I’m having

We are running dbt Core version 1.7.2 against Snowflake.

I have named Snowflake Warehouses which include the environment. The format is _WH. This gives me Warehouses named like the following:

  • CARELOGIC_ELT_DEV_WH
  • CARELOGIC_ELT_QA_WH
  • CARELOGIC_ELT_PROD_WH

We use variables in our profiles.yml to set the User, Role, WH, Database, etc. as env_var variables as follows:

qsi_snowflake:
    target: snowflake-db
    outputs:
      snowflake-db:
        type: snowflake
        account: "{{ env_var('DBT_SF_ACCOUNT') }}"
        user: "{{ env_var('DBT_SF_USER') }}"
        private_key_path: "{{ env_var('DBT_SF_KEY_PATH') }}"
        role: "{{ env_var('DBT_SF_ROLE') }}"
        database: "{{ env_var('DBT_SF_DATABASE') }}"
        warehouse: "{{ env_var('DBT_SF_WAREHOUSE') }}"
        schema: "{{ env_var('DBT_SF_SCHEMA') }}"
        threads: 32
        client_session_keep_alive: False

I’m able to run the Models using the “database” value established in my profiles.yml, but I need to override the WH in some Models that require a different (larger) named WH, dynamically switching based on the values from profiles.yml.

The context of why I’m trying to do this

I created some special WHs for outlying use cases which will require a larger Warehouse and I can’t seem to figure out the logic to place in the affected Models. I have a Model, for example, where I want to use a Warehouse with a differently-patterned name, CARELOGIC_ELT_<‘env’>_XL_WH.

So when I am operating in DEV, I want to use CARELOGIC_ELT_DEV_XL_WH for this Model; when I am operating in QA, I want to use CARELOGIC_ELT_QA_XL_WH for this Model.

What I’ve already tried

I tried to use some Jinja template in the Model (using a different naming for the WH if this would work), but received errors:

{{config(
    materialized = 'incremental',
    enabled = true,
    unique_key='unique_key',
    tags=["dbt_run:hourly"],
    cluster_by=['customer_schema'],
    snowflake_warehouse={{{{ env_var('DBT_SF_DATABASE') }}}} || '_ELT_XL_WH'
)
}}

This operation failed both with double-{{ and with quadruple-{{{{ curly braces.
15:49:32 Encountered an error:
Compilation Error in model CF_FINAL_UNMERGED (models/stage/carelogic_static/CF_FINAL_UNMERGED.sql)
expected token ‘:’, got ‘}’
line 7
snowflake_warehouse={{ env_var(‘dbt_SF_DATABASE’) }} || ‘_ELT_XL_WH’

I have tried using a macro, but I got errors in this as well. Here is the sample macro:

{% macro set_wh_size_env() %}
    {% if target.database in ('CARELOGIC_PROD') %}
        {% return('CARELOGIC_PROD_ELT_XL_WH') %}
    {% elif target.database in ('CARELOGIC_QA') %}
        {% return('CARELOGIC_QA_ELT_XL_WH') %}
    {% elif target.database in ('CARELOGIC_DEV') %}
        {% return('CARELOGIC_DEV_ELT_XL_WH') %}
    {% endif %}
{% endmacro %}

The Model config block was set as follows:

{{config(
    materialized = 'incremental',
    enabled = true,
    unique_key='unique_key',
    tags=["dbt_run:hourly"],
    cluster_by=['customer_schema'],
    snowflake_warehouse=set_wh_size_env()
)
}}

The error from this attempt:
15:13:55 Encountered an error:
Compilation Error
Encountered unknown tag ‘return’. Jinja was looking for the following tags: ‘elif’ or ‘else’ or ‘endif’. The innermost block that needs to be closed is ‘if’.
line 3
{% return(‘CARELOGIC_PROD_ELT_XL_WH’) %}

I’m open to anything, whether Macro or directly in the Model(s), but I can’t figure out why I am getting errors in these attempts to point the Model to the correct environment’s XL WH.

2

Self-solved!

Here is how I accomplished dynamically setting the WH for these use cases:

My macro code:

{% macro set_warehouse() %}
    {% if target.database in ('CARELOGIC_PROD') %}
        {% do return('CARELOGIC_ELT_PROD_XL_WH') %}
    {% elif target.database in ('CARELOGIC_QA') %}
        {% do return('CARELOGIC_ELT_QA_XL_WH') %}
    {% elif target.database in ('CARELOGIC_DEV') %}
        {% do return('CARELOGIC_ELT_DEV_XL_WH') %}
    {% endif %}
{% endmacro %}

My Model config block:

{{config(
    materialized = 'incremental',
    enabled = true,
    unique_key='unique_key',
    tags=["dbt_run:hourly"],
    cluster_by=['customer_schema'],
    snowflake_warehouse=set_warehouse()
)
}}

When I executed dbt run -s +stage.carelogic_dynamic.CF_FINAL in DEV, I see that all of the Models run on the default env_var-driven CARELOGIC_ELT_DEV_WH except the specific Model with the snowflake_warehouse override which ran on CARELOGIC_ELT_DEV_XL_WH.

One piece that I believe I was previously missing was to GRANT USAGE on the new WHs to my dbt Role. With this in place, my macro is up and running. Thanks!