Using SnowDDL only for Role Hierarchy

[richard-cybersyn]

Just wanted to say that this tool looks awesome, and I'm exploring using it for my data lake. However, it seems like my particular use case isn't nominally supported. Currently I already have a lot of defined databases with schemas and tables already defined within dbt. Ideally I'd like to leave the table & view management to dbt as they're pretty stable pipelines and I don't want to disrupt them.

However I do want to leverage SnowDDL to manage roles and access at a database and schema level. I tried playing around with the tool today and found that I have to explicitly define every database and schema out in order for the CLI to be able to run through its blueprints and parse out a plan. This is a bit of a pain for me as I don't want to manage two source of truths, table management in dbt as well as SnowDDL. I'm not too familiar with Snowflake's RBAC so not sure if defining out all objects is necessary for only role management or just a function of how SnowDDL works.

What do you recommend here? Should I look into something else if all I want is a role/permission manager? Or in your opinion should snowflake DDL management be done with this tool?

[littleK0i]

You may consider the following approach:

1. Create empty directories for databases and schemas in SnowDDL config;
2. Add params.yaml files for databases and / or schemas with parameter is_sandbox: true;
3. Create business role for DBT with parameter schema_owner pointing to schemas for DBT objects;
4. Create user for DBT with business role defined in previous step;

Databases, schemas, schema roles, business roles, user roles and users will be managed by SnowDDL. Individual tables & views in "sandbox" schemas will be managed by DBT. SnowDDL will not drop objects in such schemas.

---

In my view DBT is generally an anti-pattern, despite being very popular and actively promoted by DWH vendors. It may easily lead to explosion in model complexity & compute costs if not managed carefully.

Creating all objects in SnowDDL and ingesting data with custom ETL pipelines is much better. But it depends on the amount of data / complexity of sources / availability of engineers.

DBT might be fine if complexity is low, and everything can be recalculated in a few seconds.

[minghungcho]

@richard-cybersyn Currently, my team uses SnowDDL and dbt to manage Snowflake data lake & data warehouse projects. We split the responsibility based on ELT design. SnowDDL handles everything E & L touches, dbt for T, and SnowSQL for pre- & post- deployment scripts. And we use Azure DevOps to tie everything nicely together.

However, when it comes to User & Role Management, we delegate a set of Snowflake stored procedures (managed by SnowDDL) and ServiceNow to orchestrate the management. Since usually, you would want to have a workflow to approve/decline access to a database/row, managing them in scripts is challenging.

Exactly as suggested by @littleK0i, we use params.yaml to set is_sandbox to true, and we use MANAGED_ACCESS to simplify object ownership.

Although we heavily leverage dbt for transformation, dbt does violate software control in many cases when it alters tables and views in production environment. Unless you carefully only do incremental, snapshots, or tests. This is where SnowDDL is more reliable when it comes to CI/CD practice.

Since SnowDDL and dbt do not "recognize" each other, there are some project management challenges. In dbt, you can use source to introduce objects managed by dbt. However, there is really no easy way to introduce dbt objects to SnowDDL other than defining needed objects in YAML file. If you follow ELT paradigm, this would not be an issue. However, for functions/stored procedures managed by SnowDDL but using tables/views maintained by dbt, you might run into some dependencies issues.

@littleK0i It would be nice to have some suggestions in SnowDDL document regarding working with dbt. I think these 2 tools complement well each other.

[richard-cybersyn]

@minghungcho Thank you so much for running through how you've managed to integrate SnowDDL & dbt together at your organization, super insightful.

On workflow for your developers who are actively creating/updating/destroying these objects on a day to day basis, what does their workflow look like with SnowDDL? If someone wants to create a database/schema/table, do they first submit a PR to the internal snowddl repository, on merge, some CI/CD will run snowddl apply against all of the generated queries?

I assume the intention is for folks to not be able to run CREATE statements. Do you differentiate DEV and PROD objects and further segment read/write privileges by role?

[minghungcho]

for developers, we either provision a feature database as a clone of an existing database or ask people to use a shared DEV database so developers are free to do pretty much whatever they want in these environments. However, when they need to formally introduce these changes, they need to add the changes to either SnowDDL project or dbt project, and go through PR and Azure DevOps deployment.

Since we use MANAGE ACCESS on schema, we don't allow anyone to create databases or schemas in any Snowflake environment. We use ServiceNow to orchestrate the creation process. It also allows DBA team to close monitor the costs and prevent proliferation of databases and accesses.

In the non-production environment, we use --apply-unsafe. This would allow developers to catch any potential breaking changes early. And with dbt test, we can closely monitor any unexpected data quality issues.

We heavily utilize SnowDDL singldb mode to manage databases in different environment such as SAMPLE_DEV, SAMPLE_QA, SAMPLE_UAT, or SAMPLE_PROD. So no one is allow to introduce CREATE statement. The only exception is that we use SnowSQL to manage pre- & post- deployment scripts where certain ALTER and DROP statements are used.

[littleK0i]

In theory, it should be possible to introduce any external config source to SnowDDL. YAML configs are not required.

Blueprint objects are basic dataclasses: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/blueprint/blueprint.py

Here is the base "app", which is responsible for parsing arguments and running SnowDDL from CLI: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/app/base.py

It can be extended and overloaded. You may overload init_config method, build custom blueprint objects and add to config using .add_blueprint() method.

If DBT is capable of providing full table structure for materialized models and view text for non-materialized models, it is possible to create everything in SnowDDL. But I am not sure DBT actually knows the structure of columns for tables & views before execution.

[richard-cybersyn]

Thank you both for all of the help! A followup question on how best to manage users that are provisioned through Okta. I assume since passwords are not automatically refreshed, as long as the configuration doesn't explicitly define a password, any snowddl apply will not modify any existing authentication.

[littleK0i]

Yes, password is optional. You should leave it empty for users authenticated with Okta.

If you want yo refresh passwords for normal users, the explicit CLI option --refresh-user-passwords should be added.

Snowflake does not provide any way to compare "old" and "new" passwords. So SnowDDL applies passwords on user creation or when CLI option was specified explicitly.

[richard-cybersyn]

A followup question, I noticed that even with is_sandbox: true, CREATE DATABASE statements are still ran. Nothing consequential occurs as the Database already exists, but running that statement feels incorrect. Should is_sandbox also exclude it from the query builder?

SnowDDL/snowddl/resolver/database.py

Lines 15 to 17 in b329396

	def create_object(self, bp: DatabaseBlueprint):
	query = self.engine.query_builder()
	query.append("CREATE")

[littleK0i]

is_sandbox applies to objects inside schemas only (e.g. TABLE, VIEW, FILE_FORMAT).

Databases and schemas are still managed by SnowDDL. It is required to ensure MANAGED ACCESS for schemas and to create "schema roles" automatically (https://docs.snowddl.com/guides/role-hierarchy#schema-roles)

[littleK0i]

CREATE DATABASE should run only once, if it does not exist yet.

It should not appear on the second run.

[richard-cybersyn]

Gotcha, it seems like my issues stem from the fact that I'm granting roles the ability to CREATE DATABASE. As then the ownership belongs to that particular role, unless ownership is given back to SNOWDDL_ADMIN, SnowDDL cannot actually grant privileges correctly.