How to set the warehouse per model in dbt on Snowflake

profiles.yml sets one warehouse for the whole run. Most models run fine on a small one, but a few large joins or incremental merges would finish much sooner on a bigger warehouse. snowflake_warehouse switches warehouse for those models only.

Copy and paste

One model:

{{ config(snowflake_warehouse='TRANSFORMING_L') }}

A folder, in dbt_project.yml:

models:
  shop:
    marts:
      finance:
        +snowflake_warehouse: TRANSFORMING_L

Only in production, keeping the profile’s warehouse elsewhere:

{{ config(snowflake_warehouse='TRANSFORMING_L' if target.name == 'prod' else target.warehouse) }}

Give the dbt role access to the extra warehouse:

grant usage on warehouse TRANSFORMING_L to role TRANSFORMER;

How dbt switches

Before a node with snowflake_warehouse runs, dbt-snowflake executes use warehouse TRANSFORMING_L on its connection. After the node, it switches back to the warehouse it was using. When the configured warehouse is the same as the one in the profile, dbt does nothing. Tests and snapshots can have the setting too.

The name is used as written, without quotes, so Snowflake reads it in uppercase.

Choosing which models

• Measure first. The run log shows each model’s duration, and Snowflake’s query history shows the time spent and the bytes scanned. Move the slowest models, not whole projects.
• Size and cost. Each warehouse size uses twice the credits per hour of the previous one, but a query that parallelises well runs in about half the time, so it can cost the same and finish sooner.
• Concurrency. With several threads, models on the default warehouse and models on the large one run at the same time, each on its own warehouse.

If it fails

An error on the use warehouse statement, typically Object does not exist, or operation cannot be performed, means the role in the profile cannot use that warehouse, or the name is wrong. Grant USAGE as above, and check the name.

The switching logic comes from the code of dbt-snowflake 1.12.4; not run against a live Snowflake account.