Making a macro use the same Snowflake warehouse as the model that's calling the macro #dbt

README.md

Making a macro use the same Snowflake warehouse as the model that's calling the macro

For the purposes of this exercise, assume we have a connection to Snowflake that uses the default Snowflake warehouse of transforming.

Project setup

# dbt_project.yml
name: "my_dbt_project"
version: "1.0.0"
config-version: 2
profile: "snowflake"

models:
  my_dbt_project:
    +materialized: table
    +snowflake_warehouse: analytics

-- models/foo.sql

{{ run_something() }}

select 1 as id

-- macros/run_something.sql

{% macro run_something() %}
    {% set query %}
       select current_timestamp as ts_in_run_something_macro;
    {% endset %}
    {% do run_query(query) %}
{% endmacro %}

We have a model foo that we want to build using an alternate warehouse analytics instead of the default transforming. On top of that there is a macro in the model run_something() with some arbitrary SQL that we want to execute.

Now, let's do a dbt run and see what happens in Snowflake:

Notice that our macro execution didn't seem to use the analytics warehouse but instead uses the default transforming warehouse. This is because the snowflake_warehouse config ONLY¹ applies to the building of the model alone and nothing else, i.e. it does not apply to the macro run_something() that's being called within it.

So how can we also make the macro use the snowflake_warehouse? Let's modify our macro:

-- macros/run_something.sql

{% macro run_something() %}
    {% set wh = config.get("snowflake_warehouse") %}
    {% set query %}
       use warehouse {{ wh }}; select current_timestamp as ts_in_run_something_macro;
    {% endset %}
    {% do run_query(query) %}
{% endmacro %}

Basically model's have config variables that we can use the get method on to retrieve model configurations - one of the configuration is of course snowflake_warehouse here. Read more about this here.

So we retrieve the calling model's snowflake_warehouse config, and then we simply make sure that we're issuing the use warehouse ... statement right before the SQl that we want to execute. Let's see what happens with a dbt run now:

Perfect - our macro that runs arbitrary SQL now uses the same warehouse as our model.

Note that if we had instead used hooks to run our macro:

-- models/foo.sql

{{ config(pre_hook = "{{ run_something() }}") }}

select 1 as id

Then we could have left our macro alone as it was previously:

-- macros/run_something.sql

{% macro run_something() %}
    {% set query %}
       select current_timestamp as ts_in_run_something_macro;
    {% endset %}
    {% do run_query(query) %}
{% endmacro %}

And because hooks kind of have run_query built into them, we can further simply the macro to be:

-- macros/run_something.sql

{% macro run_something() %}
    select current_timestamp as ts_in_run_something_macro;
{% endmacro %}

^ That's right - nothing but a simple string and our hook would execute as expected.

Footnotes

1. See relevant issue here https://github.com/dbt-labs/dbt-snowflake/issues/23 ↩