Profile.yml is configured with ROLE_1. When I create model view from dbt, the FROM clause having table with ROLE_2. I have not configured ROLE_2 anywhere and not sure where to configure.
When I compile the view from the model, it says schema does not exist because ROLE_2 has this schema.
Can someone please help how do I configure ROLE_2 so that my modeled view can be created in the ROLE_! but using the ROLE_2 schema table.
Please help.
What I’ve already tried
Some example code or error messages
Put code inside backticks
to preserve indentation
which is especially important
for Python and YAML!
I think there is no such feature as of now.
It’s better to create a role for dbt and the role should have create object permissions on the databases ur using to create objects and read access on the sources db’s you are using in dbt.
For more information
Thanks Surya for reply. View should be created in the DB_CREATE database and the view is refering to DB_READ database. Both this DB’s are residing in different roles. What are you suggesting here is to create dbt specific role on both these database so that dbt specific role will have permission on both the roles and this way we can use DB_READ to create view in DB_CREATE. Is this understanding correct?
If so, I have question on how can I at run-time inform dbt to read from DB_READ but create view into DB_CREATE. I have used pre-hook in dbt_project.ym file to reference like below
models:
+pre-hook: “USE ROLE DB_READ_DBT_ROLE”
And in project.yml file, I have reference to DB_CREATE_DBT_ROLE to create view in the DB_READ.
NotE: I have tried config option in individual view .sql in model like below as well but no success.
{{
config(
pre_hook=“USE ROLE DB_READ_DBT_ROLE”
)
}}
Above is not working though and it always gives error DB_WRITE database not found! Any idea and instruction to solve this issue? Thanks well in advance!