In dbt cloud, using snowflake what is a good standard to create a "dbt transformer" role used by DBT RUN - mine does not work

user_10018429 0
Viewed 236

The only way I can get dbt run to work is by connecting as an accountadmin (bad). Any other role gives me "insufficient privileges on table xxx" when executing dbt models. I am using DBT cloud connecting to snowflake. I have created the following with a role that I wanted to use, but it seems that my grants do not work, to allow running my models with this new role.

my dbt cloud "dev" target profile connects as dbt_user, and creates objects in analytics.dbt_ddumas

Below is my grant script, run by an accountadmin:

There must be an easier way than this below, which is not even working :(

Dave

use role accountadmin;
CREATE ROLE dbt_role
GRANT ROLE dbt_role TO ROLE sysadmin
GRANT USAGE ON WAREHOUSE transform_wh TO ROLE dbt_role
GRANT ALL ON database analytics TO ROLE dbt_role
grant ALL ON ALL schemas in database analytics to role dbt_role;
grant ALL ON future schemas in database analytics to role dbt_role;
grant ALL ON ALL tables in SCHEMA analytics.dbt_ddumas to role dbt_role;
grant ALL ON future tables in SCHEMA analytics.dbt_ddumas to role dbt_role;
grant ALL ON ALL views in SCHEMA analytics.dbt_ddumas to role dbt_role;
grant ALL ON future views in SCHEMA analytics.dbt_ddumas to role dbt_role;

CREATE USER dbt_user PASSWORD = 'Password123' MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE dbt_role TO USER dbt_user;
dbt
2 Answers

For anyone who is new to DBT with snowflake, here is what you need to do to setup a dbt role to run models, test, etc with dbt run, dbt test, etc

After much frustration, and a willingness to save others the pain I went through...

This is for a lower environment . Ex dev or test Step 0: Login as an accountadmin, or have an accountadmin do this: Step 1: create a warehouse . Ex. transform_wh Step 2: create a database Ex. Analytics Run this below, substituting the proper password for dbt_user:

NOTE: DO NOT CREATE ANY SCHEMAS AS ACCOUNTADMIN. That was my main problem, so do not make that mistake. The reply from the user who replied to this message did not know that, and his reply would work also. The dbt_loader_dev role will do this as you execute dbt_run. If you do, dbt_loader_dev will get "insufficient privileges" errors when trying to create schemas, tables, and views.

USE ROLE accountadmin;
-- this role is used to load all models in the dev (lower environments) when you do a dbt run, dbt test, etc
CREATE ROLE dbt_loader_dev;

-- custom roles should be granted to sysadmin

grant ROLE dbt_loader_dev TO ROLE sysadmin;

-- these grant are all you need to run so that dbt_loader_dev can do all that it needs to for any dbt run, dbt test, etc

GRANT USAGE ON WAREHOUSE transform_wh TO ROLE dbt_loader_dev;
GRANT all ON database analytics TO ROLE dbt_loader_dev;
GRANT usage ON ALL SCHEMAS IN DATABASE  analytics TO dbt_loader_dev;
GRANT usage ON future SCHEMAS IN DATABASE  analytics TO dbt_loader_dev;
GRANT Monitor ON ALL SCHEMAS IN database analytics TO dbt_loader_dev;
GRANT Monitor ON future SCHEMAS IN database analytics TO dbt_loader_dev;
GRANT MODIFY ON ALL SCHEMAS IN DATABASE  analytics TO dbt_loader_dev;
GRANT MODIFY ON future SCHEMAs IN DATABASE analytics TO dbt_loader_dev;
-- create a user and grant the role
CREATE USER dbt_user PASSWORD = 'Password123' MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE dbt_loader_dev TO USER dbt_user;

That's it! Enjoy! Dave (edited)

user_100184290

I recommend following the excellent article by Claire on the dbt Discourse that covers this exact topic.

It would be helpful to know exactly what table, schema, or database you are unable to read from. You say my dbt cloud "dev" target profile connects as dbt_user, and creates objects in analytics.dbt_ddumas, but what databases and schemas is it reading from? (Where are your sources located)? Most of your grants will be oriented to reading existing data, since dbt Cloud will create your dbt_ddumas schema, and therefore own it and every other relation that it creates.

Assuming your raw data is located in a database called raw, then I would change your script to:

use role accountadmin;
CREATE ROLE dbt_role
GRANT ROLE dbt_role TO ROLE sysadmin
GRANT USAGE ON WAREHOUSE transform_wh TO ROLE dbt_role

grant usage on database raw to role dbt_role;
grant usage on future schemas in database raw to role dbt_role;
grant select on future tables in database raw to role dbt_role;
grant select on future views in database raw to role dbt_role;

grant usage on all schemas in database raw to role dbt_role;
grant select on all tables in database raw to role dbt_role;
grant select on all views in database raw to role dbt_role;

grant usage ON database analytics TO ROLE dbt_role;
grant create schema ON database analytics TO ROLE dbt_role;

CREATE USER dbt_user PASSWORD = 'Password123' MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE dbt_role TO USER dbt_user;
user_108130820
Related