How can I choose a different virtual warehouse size for Snowflake with dbt?

user_132438 0
Viewed 533

How can I choose a different virtual warehouse size with dbt, depending on how its being run?

For example, run on an XL when executing a full dbt build manually, but default to XS when running a specific model (as in dbt build --select models/test.sql).

dbt snowflake-cloud-data-platform
1 Answers

You could do something like this with a macro:

{% macro warehouse_resize(prod_size, stage_size) %}

  {% if target.name == "prod" or target.name == "prod_backfill" %}
  ALTER WAREHOUSE {{ target.warehouse }} SET WAREHOUSE_SIZE = {{ prod_size }};

  {% else %}
  ALTER WAREHOUSE {{ target.warehouse }} SET WAREHOUSE_SIZE = {{ stage_size }};

  {% endif %}

{% endmacro %}

and have it as an on-run-start and on-run-end hook to resize.

(thx Emily!)

Update: Check the macro snowflake_utils.warehouse_size():

https://github.com/Montreal-Analytics/dbt-snowflake-utils/tree/0.3.0/

user_1324380
Related