Improve dev experience for creating Streamlit apps in Snowflake

[ian-r-rose]

Our current RBAC structure doesn't work very well for developing streamlit apps based on data in the prod marts database (ANALYTICS_PRD):

1. Streamlit apps are database objects that need to be created in a specific place with a specific role
2. When developing a streamlit app, it makes most sense to use the ANALYTICS_DEV database and REPORTER_DEV role.
3. But that role doesn't have access to the prod data
4. Normally when using external BI tools, people can connect using the REPORTER_PRD role. This is pretty safe, since it only has read-only permissions on that data.
5. But if they enable the TRANSFORMER_PRD role, they have to create the streamlit app in the ANALYTICS_PRD database. This isn't particularly satisfying as a developer to create test objects in prod.

There are a few things we might want to consider:

1. Are there any tweaks to the RBAC structure that would make this work better?
2. Can we solve this with documentation? Perhaps the best way is to just have the user build the appropriate marts in ANALYTICS_DEV and develop against that. With appropriate dbt deferral, it needn't be super onerous to do that.
3. Can we change the execution role of a streamlit app after it is created?

[jkarpen]

It seems like the ideal scenario is they develop in the dev environment/role, then switch to the prod environment/role once the app is production-ready. I did a quick scan of the documentation here and don't see a way to change the associated role once the app is created. But I'm wondering if the developer could simply copy the SQL used by the app and create a new version with the production role/environment when they are done with development?

https://docs.snowflake.com/en/developer-guide/streamlit/owners-rights

Another thought - maybe this is worth scheduling time with Gabe to discuss?

[ian-r-rose]

Yeah, I might discuss this with Gabe the next time we meet. What I'm worried about is that in order to create the app, we would need the transformer role (the reporter role can't create objects). But then the transformer role is used when running the app, and I'd rather have a role with read-only permissions used for running the app.