BQ pain points: permissions and RBAC

[dataders]

background

our friends at BigQuery are very interested to hear how they can improve the product for users of dbt. They'd like to target specific pain points. The more context you can share on your request the better. Think about including things along the lines of:

• scenario -- a clear and concise description of what you do or don't want to happen.
• minimum reproducible example -- I know this is hard, but makes it so easy to have the problem fixed!
• workarounds-- A clear and concise description of any alternative solutions or features you've considered.
• benefit/impact -- what kind of use case would this improve? What dbt workflows get better?

focus area for this discussion

This discussion is specifically related to issues and challenges with permissioning and role-based access control (RBAC). We've heard of a number of opportunities in this area. Here's a few examples (feel free to grab one and flesh it out more!)

• "RBAC is always a pain and requires involvement from our GCP IAM admins"
• The error message when you don't have access could be more helpful by including the name of the user who doesn't have access instead of just "user".

  “Access Denied: User does not have permission to access/query table.”.

• syntax of querying connected, external databases (e.g. Cloud SQL) is not compatible with dbt

[github-christophe-oudar]

I absolutely agree it's complex to create dedicated service accounts on BQ for dbt especially if you're building a monolithic dbt project and try to setup an account per domain/team. One of the issues is that you can't do a partial dbt compile: so you're going to read all tables (and therefore need rights for all the input/destination datasets for the compile). But even if you have the rights for the compile, if you're using variables, dbt will recompile the project because there is no way to declare variables that wouldn't change precompiled queries.

For rights management, overall I would suggest to create a dbt package that generates the required rights to run in IAC output (ie Terraform).