dbt profile file usage - multiple targets and roles segregation

Help
, ,
1

I have various roles in snowflake
CI_CD_ROLE - to be used for deploying dbt projects to snowflake ( for snow dbt deploy …)
EXEC_ROLE - to be used for executing the dbt project from snowflake after deploy (for dbt run … will be executd by human from UI or programetic)

CI_CD_ROLE & EXEC_ROLE have distinct access levels & need to be used for its own action

my profiles.yml file looks like

<profile_name>:
target: dev
outputs:
dev:
type: snowflake
account: “account”
database: “MY_DB”
schema: “MY_SCHEMA”
warehouse: “MY_WH”
role: “CICD_ROLE” # this is where the problem is
threads: 4
client_session_keep_alive: false

This profile file deploys the dbt project to snowflake , but fails as the CI_CD_ROLE do not have permissions to dbt run

If i change the role in profile file to “EXEC_ROLE” - the deployment fails as CI_CD_ROLE cannot inherit EXEC_ROLE for deploy.

How can this be handled..

I tried keeping two differnt blocks /env one with CI_CD role and one with EXEC role, like
<profile_name>:
target: dev
outputs:
dev:
type: snowflake
account: “account”
database: “MY_DB”
schema: “MY_SCHEMA”
warehouse: “MY_WH”
role: “CICD_ROLE” # this is where the problem is
threads: 4
client_session_keep_alive: false
exec_role1:
type: snowflake
account: “account”
database: “MY_DB”
schema: “MY_SCHEMA”
warehouse: “MY_WH”
role: “EXEC_ROLE”
threads: 4
client_session_keep_alive: false

but this fails too as there is no env or target as exec_role1: in snowflake to “run”. and also the default target “dev” gets added to run automatically failing

2

When you are using snow dbt deploy, you have an argument as —role that you can use to overwrite the role mentioned in the connection config. So in your case you can keep the role as EXEC_ROLE in profiles.yaml and while doing deploy you can pass the CI_CD_ROLE