Why do I get a 'select active warehouse' error in dbt when trying the table materialization, but not with the view materialization?

Question

I've been working with dbt for a couple of months now, so still fairly new to it. When running a test model, I have no problems when using the view materialization:

{{ config(materialized='view') }}

select 1 as id

Resulting in:

15:30:25 | 1 of 1 START view model dbt.stg_CampaignTableTest.................... [RUN]
15:30:26 | 1 of 1 OK created view model dbt.stg_CampaignTableTest............... [SUCCESS 1 in 1.48s]

However, when I make the switch to a table materialization I get an error message about not having an active warehouse selected in Snowflake:

{{ config(materialized='table') }}

select 1 as id

Resulting in:

15:32:52 | 1 of 1 START table model dbt.stg_CampaignTableTest................... [RUN]
15:32:53 | 1 of 1 ERROR creating table model dbt.stg_CampaignTableTest.......... [ERROR in 1.22s]

Database Error in model stg_CampaignTableTest (models/test/stg_CampaignTableTest.sql)
  000606 (57P03): No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

Of course, it's not possible to include a "use warehouse" statement within my test model as it is inserted into the compiled SQL at the wrong position:

{{ config(materialized='table') }}

use warehouse "AnalysisTeam_WH";

select 1 as id

Because it leads to:

2021-10-07T15:33:59.366279Z: On model.my_new_project.stg_CampaignTableTest: /* {"app": "dbt", "dbt_version": "0.21.0", "profile_name": "user", "target_name": "default", "node_id": "model.my_new_project.stg_CampaignTableTest"} */


      create or replace transient table "AnalysisTeam"."dbt"."stg_CampaignTableTest"  as
      (

use warehouse "AnalysisTeam_WH";
2021-10-07T15:33:59.366342Z: Opening a new connection, currently in state closed
2021-10-07T15:34:00.163673Z: Snowflake query id: 019f7386-3200-ec67-0000-464100e189fa
2021-10-07T15:34:00.163803Z: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 4 at position 0 unexpected 'use'.

I appear to have the correct permissions with my Snowflake 'role' to create tables, views, etc., so I was at a loss to understand why changing from view to table would cause the model to fail. I suspect it could be related to Snowflake permissions rather than a dbt issue but I am not sure. Any ideas would be really appreciated!

Edit: I appeared to make a mistake with my screenshots so I have switched to code snippets which is hopefully clearer.

Answer 1

I would suggest checking two possibilities.

A. The active profile coniguration at "~/.dbt/profiles.yml" Snowflake Profile:

and search for 'warehouse:'

my-snowflake-db:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: [account id]

      # User/password auth
      user: [username]
      password: [password]

      role: [user role]
      database: [database name]
      warehouse: [warehouse name]    <<<<<
      schema: [dbt schema]
      threads: [1 or more]

B. Default warehouse setting for user used for connection ALTER USER:

SHOW USERS;

ALTER USER user_name SET DEFAULT_WAREHOUSE = '<existing_warehouse_name>';

Answer 2

Make sure the Snowflake Role dbt is using has been granted access to the Snowflake Warehouse dbt is using.

show grants on warehouse 'xxxxxxxx'