I need to structure the roles in snowflake and need advise for it.
Currently, there are two databases: basedb and computedb. basedb is the database where we load the data and the data is not transformed. more like a landing zone.
computedb is where the transformation happens and there are dozens of functional teams and each would have a schema like content team has content schema and there could a schema which can be shared across multiple teams.
all the teams/ schema inside computedb can read from basedb but there are teams that are reading from transform schema of other teams and that is where I need assistance.
all custom roles are under sysadmin and we have a read and write role per schema.
Now analytics schema wants to read from sales and marketing schema as well.
should just give the read access of sales and marketing to analytics? like shown in picture (issue is that the teams might need to access more schemas in future from other schemas in transform)
or should we create a new role for all the schema in computedb which needs access to other schemas of computedb?
Open to more suggestions.