Help with Programmatic config to Skip certain types of objects in DEV environment

[JefStat]

I'm attempting to skip modifying a particular view using Programmatic config. I'm looking for some help in how to achieve it. My idea is to replace the view blueprint with the definition found in Snowflake. I figure this is the way to get the ViewResolver to return a NO_CHANGE ResolveResult.

I've got the view blueprint I want to ignore

def handler(config: SnowDDLConfig):
views=config.get_blueprints_by_type(ViewBlueprint)
  for key in views.keys():
    print(views[key])
    if "RETAIL" in key:
      retailView = views[key]

What I'm unsure of is:

• how to get an instance of the query engine to run the query

     cur = self.engine.execute_meta(
            "SHOW VIEWS IN SCHEMA {database:i}.{schema:i}",
            {
                "database": schema["database"],
                "schema": schema["schema"],
            },
        )```
- creating a new instance of ViewBlueprint from the schema context

Second approach Idea which I'm just looking into now.
- using converter/view.py to update the yml on disk
- reload the view blueprint with the new yml file contents

[littleK0i]

I would generally suggest to avoid it and try different approach.

If you add some specific logic to preserve objects on DEV, it means one of the core features of "dropping and re-creating environment at any time" will be lost. If something drops or alters this VIEW, original code will be gone anyway.

So.. what can we do instead? For example..

1. Move this VIEW and other similar objects into a separate database or share from PROD account. It should be managed outside of SnowDDL (defined by ownership).
2. Grant access to this database or share using global roles.
3. When you refer to this VIEW in config (e.g. from other VIEWs), do not use env_prefix.

You may still manage this database on PROD using SnowDDL. On DEV you may simply remove blueprints for DATABASE, SCHEMA and all objects inside by iterating and calling config.remove_blueprint().

Sometimes it is very handy to have a DATABASE on PROD, but SHARE with the same name on DEV. No need to change any queries, everything will be compatible.

Alternatively, you may try to build VIEW text separately for DEV and PROD envirvonments, but without using current state of objects in Snowflake. This SQL code probably comes from somewhere, maybe it can be replicated in pure Python.

Engine is not available during config building. Engine is created only when config was fully initialised and validated.

You may try to open a separate Snowflake connection directly in handle, but it would introduce "chicken & egg" problem. May cause some major issues later on.

[JefStat]

I like the suggestions. Multiple databases does seem like a solution, but I find splitting into multiple databases a bit too daunting.
About the third suggestion 3. Refer to the view how can a view in snowddl refer to other views that are not managed by snowddl and still validate?
To give more context the reason I want to skip replacing this view in snowddl is that the view definition selects from other views not managed by snowddl.
I'd like snowddl to manage this view since I have a user defined in snowddl that has single access to just this view and is used to pull data from snowflake from this view. But the full view definition references a whole chain of other db objects not managed by snowddl.

[littleK0i]

Aha. The following approach might work:

1. Create global role granting read access to objects referred in our VIEW.
2. Grant global role to schema owner role using parameter owner_global_roles: https://docs.snowddl.com/basic/yaml-configs/schema
3. Define VIEW with original SQL text. Do not add anything in depends_on. We assume all external objects already exist.

SnowDDL will be able to create view referring to external objects. More importantly, it will be able to detect when this view becomes broken due to various changes in source objects. It is very common for views with SELECT * FROM ... . Such view will be recreated automatically.