Plan generation and objects ownership

[fabiomx]

We are starting to use SnowDDL in SingleDB mode.

Our goal is to handle at least Stages/Pipes/(raw)Tables via Terraform (with Terraform ownership) and downstream tables and the rest of the objects via SnowDDL (with its ownership).

When we generate the plan without including YAML files for tables owned by Terraform, SnowDDL plans to drop them, even if it can't do that since Terraform's role owns them. As far as I know, SnowDDL should completely ignore objects owned by other roles, but in this case, it plans a drop (which later fails, obviously).

Any suggestion?

We discard the option of creating two separate schemas and using the is_sandbox flag :)

[littleK0i]

Yeah, currently SnowDDL expects all objects in schema to be owned by "schema owner" role or by SnowDDL admin role. Otherwise it will not work well with env_prefix feature, since objects managed outside of SnowDDL are not going to be replicated. Even if I add some extra checks, it won't fix this fundamental issue.

I suggest to consider the following options:

1. Make a hard-split on database level for objects managed by Terraform and objects managed by SnowDDL. Create a separate role in Terraform granting access to objects managed by Terraform. Grant this role to SnowDDL admin role. Grant this role to specific business roles via global_roles.

2. Alternatively, stages and pipes can be managed by SnowDDL as well, and blueprints can be generated dynamically in Python code. Any logic from terraform can be replicated in raw Python. You can mix YAML configs and dynamically generated configs.

3. Finally, Terraform can be used to generate YAML configs for pipes and stages automatically after each deployment. The logic will stay largely in Terraform, but DDL operations will be managed by SnowDDL.

[littleK0i]

By the way, future grants which are automatically assigned to "schema owner" role should currently change ownership for tables and pipes created by Terraform.

Ownership will be assigned to "schema owner" role automatically via future grant. You won't be able to distinguish tables in single schema into multiple types by ownership, since future grants applies to all tables equally.

[mirkanos]

@littleK0i I'd like to add a point regarding the first suggestion you made:

We could run into the following issue: Let's say SnowDDL is supposed to create a view that points to a table created by Terraform, meaning it's in the schema dedicated to Terraform as you suggested. My understanding is, when SnowDDL runs the statement "create view as select from [Terraform table]", this operation would fail because for the SnowDDL ROLE, this table doesn't exist, correct?

[littleK0i]

In base case scenario - yes, it is correct.

It is a problem even when all schemas are managed by SnowDDL, since in order to create such view the "schema owner" role should have read access to objects in the source schema. Currently it can be mitigated by using "owner_schema_read" in schema config: https://docs.snowddl.com/basic/yaml-configs/schema

In theory, it should be possible for me to add "owner_global_roles" feature for schemas, similar to "global_roles" for business roles.

You'll be able to create a role with READ-only access to Terraform managed tables and assign it with "owner_global_roles". It should allow to create views based on objects in externally managed schemas.

Please let me know if you're happy to give it a try, and I'll push a small patch.

Thank you,

[mirkanos]

Right now, our proposal is to use the SingleDB mode, so we don't manage the creation of roles via Snowddl. However, I think you've enlightened us with this owner_schema_read parameter that I hadn't noticed before (sorry). We'll give it a try and let you know. Thank you so much for your help!

[fabiomx]

Thank you for the suggested options; we are considering which one might be the best for our case.

We have also thought of another option that would require a new feature in SnowDDL, but perhaps it's simple to implement and could be useful for other use cases as well.

All our objects created via Terraform have specific prefixes. Just as there is an argument to exclude objects by type (--exclude-object-types), would it be possible to implement another one that excludes objects by name pattern?

Thank you very much!

[littleK0i]

It is technically possible. But it is not going to solve the problem (in my view) due to "ownership" problem mentioned above.

Snowflake implementation of "OWNERSHIP FUTURE GRANT" means that objects in one schema can have only one owner. All objects created via Terraform will be assigned to SnowDDL "owner" role automatically. It is possible that Terraform will not be able to ALTER such objects later.

You may test it by setting is_sandbox: true parameter for schema with mixed objects. It will prevent unknown objects from being dropped automatically, but "ownership" problem will likely persist.

[mirkanos]

The schema that will contain Terraform objects does not have the FUTURE GRANTS OWNERSHIP to SnowDDL role. Therefore, the objects will have Terraform as their owner since that role created them. (Remember that we are using SIngleDBmode)

[littleK0i]

Ok, since SingleDB is quite a big stretch of the original concept, it should be ok to extend it a little bit.

What if we add two more options to schema params, in addition to is_sandbox?

1. sandbox_object_types - apply "sandbox" mode to specific schema object types only;
2. sandbox_object_name_prefix - apply "sandbox" mode to schema objects with specific prefix only;

it should help to keep "sandbox"-related params in one place and it should cover majority of odd use-cases.

[fabiomx]

I think that would be great! 🙂Thanks!

[fabiomx]

After thinking about that again, I think that the “sandbox” naming wouldn't be appropriate in some cases, the “exclude-object”argument option seems better to me since it is environment-agnostic

[mirkanos]

Thank you @littleK0i

While this sounds like a good option, it might not be necessary for my use case. Here's why:

Database Schemas Overview:

SANDBOX_SOURCE(is_sandbox)
SANDBOX_ANALYTICS(is_sandbox)

SHARED_SOURCE(is_sandbox) Not really, but added for Terraform compatibility. [managed by Terraform]
SHARED_ANALYTICS - [managed by Snowddl]

Purpose of Schemas:
Sandbox Schemas: True to their name, with the "is_sandbox" parameter fitting perfectly, especially as users sometimes create Snowflake objects directly.
Shared Schemas: Intended for both SNOWDDL (SHARED_ANALYTICS) and TERRAFORM (SHARED_SOURCE) to function as DCM. This represents our production environment.

Tools:
Terraform: Manages objects closely linked to AWS, needing AWS configurations, e.g., setting up notifications. Hence, responsible for Stages, Pipes, target tables of the pipes, and Integrations.
Snowddl: Handles everything else.

Snowflake Roles Overview:

SA_DB_DEPLOY: Role for Snowddl user.
Ownership of objects: SA_DB_DEPLOY
Permissions: Write access to SHARED schemas. Mainly creates objects in SHARED_ANALYTICS.

TERRAFORM: Role for Terraform.
Ownership of objects: TERRAFORM
Permissions: Creates specific objects (stages, pipes, src tables) in SHARED_SOURCE schema.
Note: No FUTURE GRANTS OWNERSHIP for Snowddl set.

ADMIN_DB: User role.
Permissions: Write in SANDBOX. Limited operations in SHARED, e.g., just operate on pipes not being able to create them on SHARED.

Solution Proposal:
Flagging SHARED_SOURCE with "is_sandbox", even if it isn't genuinely one, seems sufficient.

Reasons:
Snowddl won't attempt to delete from this schema as is SANDBOX, the issue we had before.

And the most important part, no one besides TERRAFORM can create objects there. It can't truly function as a SANDBOX, as the ADMIN_DB role lacks permissions.

That's the idea, wdyt?

[littleK0i]

In my view, the best option is to overload the SnowDDL BaseApp class and use dynamic blueprint generation for objects which are currently managed by Terraform. From technical standpoint, it is very easy to do.

Create a Python script like this:

...
from snowddl import *


class MyCustomApp(BaseApp):
    def init_config(self):
        # Init config from YAML files
        config = super().init_config()

        self.add_custom_tables(config)
        self.add_custom_stages(config)
        self.add_custom_pipes(config)

        return config

    def add_custom_tables(config):
        bp = TableBlueprint(
            full_name=SchemaObjectIdent("MY_CUSTOM_DB", "SHARED_SOURCE", "MY_SOURCE_TABLE_1"),
            columns=[
                TableColumn(
                    name=Ident("ID"),
                    type=DataType("NUMBER(38,0)"),
                    not_null=False,
                    default=None,
                    expression=None,
                    collate=None,
                    comment=None,
                ),
                TableColumn(
                    name=Ident("NAME"),
                    type=DataType("VARCHAR(255)"),
                    ...
                ),
            ]
        )

        config.add_blueprint(bp)

    def add_custom_stages(config):
        bp = StageBlueprint(
            name=SchemaObjectIdent("MY_CUSTOM_DB", "SHARED_SOURCE", "MY_CUSTOM_STAGE_1"),
            url="s3://....",
            storage_integration=Ident("MY_STORAGE_INTEGRATION"),
            ...
        )

        config.add_blueprint(bp)

    def add_custom_pipes(config):
        ...


app = MyCustomApp()
app.execute()

And run this script instead of snowddl CLI command. Parameters and settings are all the same. The only difference is that some blueprints are generated dynamically instead of being provided as YAML configs.

You may find the full range of available blueprints in this file: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/blueprint/blueprint.py

And IDE autocomplete should help you with argument types.

You may access ENV-variables and external parameter / config stores using plain Python. You may call any imaginable API directly.

The main advantages of this approach:

1. You invest time to build and set up this only once, but you will be able to generate ANY objects in Snowflake dynamically, based on any possible external configuration. 100% flexibility.
2. All SnowDDL advanced features, like "env prefix", are going to be fully supported.
3. All schema objects will be managed by one piece of software.

[littleK0i]

Alternatively, you may consider generating YAML configs for source tables, stages and pipes in Terraform dynamically and putting these files in place before running SnowDDL in the same container.

Obviously, storing credentials in plain files is not the best idea. But if storage is not persistent, and you do everything in one container, which is destroyed after execution, it should not be an issue.

All SnowDDL features will be supported as well, but it will be harder to debug comparing to previous approach. Also, you'll have some YAML-generation logic in Terraform, which is probably not so great.

[littleK0i]

@mirkanos , please note.

I've recently updated SnowDDL. It is now possible to define config programmatically in pure Python without any overloading. All you need to do is add basic Python modules to config directory.

You may use any external data sources and any other Python packages. Hopefully, it will simplify things a little bit.

More about this feature: https://docs.snowddl.com/advanced/programmatic-config

Thank you.