Replies: 2 comments
-
Short of adding a new feature for this functionality, it seems like I might be able to accomplish it with a custom resolver as described here? |
Beta Was this translation helpful? Give feedback.
0 replies
-
I ended up forking and implementing some of this, but if this is the type of change that would make sense to integrate in the source project I'd love to contribute |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Context
I have a pretty complex db cloning and swapping workflow in my data pipeline which yields a lot of grant management challenges, which I've described in some detail in other discussions in this repo. I also have quite a large number of dbs and schemas to manage, and I am sensing that it could be useful to have a way to consolidate / reduce the volume of automatically generated schema roles and grants. As I allow SnowDDL to create more and more schema roles, resolution performance is starting to suffer due to the large volume of introspection queries that must be submitted to Snowflake, and grant management complexity is rapidly increasing whenever actions such as database clones or swaps are performed outside of SnowDDL.
Propositions
<db>__<schema>__OWNER__S_ROLE
,<db>__<schema>__READ__S_ROLE
,<db>__<schema>__WRITE__S_ROLE
/<database>/<schema>/params.yaml
file. For example, suppose you only wanted the owner and reader role to be generated, the config might look something like this:Or maybe you don't want to generate any schema roles:
schema_role
resolver module: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/resolver/schema_role.pytasks
,sequences
, etc, I see no need to apply these grants. The application of these unecessary grants is creating a ton of noise in thesnowddl plan
invocation outputs, which makes it pretty hard to parse and isolate the actions that are actually important for me/<database>/<schema>/params.yaml
file. For example, it might look something like:Motivation
I have a use case where I would like to manage a data transformation workflow with blue-green deploys of the databases using SnowDDL. Details are in #58 (reply in thread). One detail in the linked comment that I miscommunicated is that for my use case the database clones that must be swapped do actually have known names ahead of time. So in theory I could manage these clones using SnowDDL. The setup looks something like this
Databases / clones
ANALYTICS
is the main production db, andANALYTICS_CLONE1
andANALYTICS_CLONE2
are cloned "candidate" databases where transformations actually take place. When those transformations and tests succeed, they are swapped withANALYTICS
. There are multiple clones because we have multiple run contexts (CI vs scheduled runs via an orchestrator etc)Ideally I would like to leverage the autogenerated schema roles for the
ANALYTICS
database to manage the grants for both theANALYTICS
db AND it's clonesWhat if instead of generating a set of schema roles for each of these clones, we just used the set generated for the
ANALYTICS
database for all of them? So for example, we would have:ANALYTICS__<schema>__OWNER__S_ROLE
ANALYTICS__<schema>__READ__S_ROLE
ANALYTICS__<schema>__WRITE__S_ROLE
And the grants associated with these roles would be applied not only to the
ANALYTICS
database, but also to the clones:grant ownership on all tables in schema ANALYTICS.<schema> to role ANALYTICS__<schema>__OWNER__S_ROLE
grant ownership on all tables in schema ANALYTICS_CLONE1.<schema> to role ANALYTICS__<schema>__OWNER__S_ROLE
grant ownership on all tables in schema ANALYTICS_CLONE2.<schema> to role ANALYTICS__<schema>__OWNER__S_ROLE
. . .
That way when the db swaps occur, the
ANALYTICS__*
schema roles still have access on both theANALYTICS
database, and all of the clones. So the Snowflake state and SnowDDL config remain in parity and with no resource drift at all times, even in the face of clone and swap operationsThe only problem with this is that I would probably need to prevent SnowDDL from automatically generating the default schema roles for those clones. So for example I would need to find a way to configure it to NOT generate a
ANALYTICS_CLONE1<schema>__OWNER__S_ROLE
at all, even if there's aANALYTICS_CLONE1
database defined in the configBeta Was this translation helpful? Give feedback.
All reactions