Onboard messy environment

[dandanaila]

Hello,

My team is trying to onboard management of our Snowflake databases into snowddl. Currently, this is not feasible in one step due to:

1. Large number of databases: O(1000).
2. Edge cases that will be required to be tackled one at a time for these databases.

However, if we split the Databases into classes, then the problem becomes manageable. We can define the following classes:

1. Databases sharing the same prefix: foo, bar, etc.
   1.a. These databases are currently managed by different services.
2. Others:
   2.a. Those are O(10), which makes it manageable to import using the snowddl-convert tool.

A few ideas that for handling this would be:

1. Using the include_databases flag to limit snowddl to the dbs onboarded.
   1.a. Not scalable for handling the prefixed databases (for a prefix there can be O(1000) databases).
2. Adding support for a exclude_databases flag that would support regex.
   2.a. This would be nice as we could leave the management of some of these databases.
3. Create new owners for the prefixed databases.
   3.a. This may require delicate surgery on production services to ensure no accidental access loss.

I am open to other suggestions/ideas.

Thanks!

[littleK0i]

Alright, it is an interesting topic with lots of considerations.

Ownership
When using multiple tools to manage the same account simultaneously, separate OWNERSHIP is the best option. It is not only about DATABASES. SnowDDL creates other account-level objects in global namespace, like schema roles. It would be very difficult to prevent clashes and undefined behaviour without clear OWNERSHIP separation.

Programmatic config
It is very rare for such a large number of objects to be created manually. I suspect you may have some external source of data, which can be used to automate and streamline creation of such objects. For example, "load list of clients -> create one database per client". If it is feasible, consider using programmatic config feature instead of converting everything into thousands of directories.

Normal config can be used along with programmatic config, with some objects managed manually and other objects managed in the code.

Reducing number of databases
Unless you have some very specific use-cases around SHARES, maybe you do not need so many databases in the first place? If we realistically can merge multiple prefixed databases into one, it may simplify the migration process quite a lot.

For each prefix:

• Leave "old" databases intact;
• Create a "new" merged database in SnowDDL;
• Adjust the code service to use new object names, usually it can be automated;
• Copy data from "old" databases into "new" database;
• Switch to the new code;
• If everything is alright, drop "old" databases;

With this approach you will have a smooth gradual transition to SnowDDL + do refactoring and reduce number of account-level objects.

Let's be honest. Human beings have a limited attention span, and scrolling through thousands of directories is going to reduce productivity quite a lot, even with the best tools.

Local install for conversion
When you convert so many objects, there is a high probability you'll need too tweak something. You may download this repository, install it locally with pip install -e . and change the code in-place. SnowDDL internally is a plain Python. Config is a collection of Pydantic models. Easy to adjust for specific use cases.

[littleK0i]

I suggest to consider the following approach:

• Keep full config in one folder, manage it with Git.
• Changes pushed by service owners should be checked automatically via hooks ("linter", "naming convention", etc.);
• Manual review is also highly recommended;
• Create a second "DEV" Snowflake account for tests. Service owners should be able to run anything on DEV account and possibly demonstrate results to super admin.
• Service owners should not be able to run anything on "PROD" account. Changes to "PROD" should be applied via automated scripts or by super admin.

With this approach you will be able to ensure best possible config quality, introduce uniform standards, resolve disputes before anything is pushed on PROD.

Also, most importantly, you will be able to create grants and procedures refering to ALL objects on account. With many different OWNERs running their own SnowDDLs you won't be able to cross-reference objects reliably.

[littleK0i]

Since SnowDDL introduces its own permission model and role hierarchy, it usually means compelte overhaul of privileges.

On the bright side, you probably need to fix permissions anyway. I am yet to see an account with privileges not in a state of complete mess, unless this process was automated very early.

[littleK0i]

If you follow the approach described above, you probably won't need to detect databases not managed by SnowDDL, since it will be clear from the code. Everthing not listed in code is managed by something else, or probably abandoned.

This problem only exists if creation of account-level objects is not cetralized and strictly controlled. And it should definitely be centralized, especially for accounts with many objects. Once you do that, the problem simply goes away.

---

If you go with "multiple owners" path, basic ownership check of SHOW DATABASES command should be sufficient. Databases managed by SnowDDL are always owned by SnowDDL admin role.

[dandanaila]

Actually that is the initial approach taken (using a single config folder).

However, trying to apply snowddl on the whole account with all the resources on proved quite challenging:

1. Dynamically generated databases (with the same prefix).
   a. This is addressed by the template custom code.
2. Short lived schemas.
   a. Services create “temporary” schemas inside some DBs, which they delete as well. This results in snowddl not being able to process DBs as the schemas disappear while it tries to analyze the objects inside the schemas.

2.a. requires more investigation. So, for the moment we fell back to:

1. Track onboarding databases using --include-object-types DATABASE.
2. Validate individual databases using snowddl-singledb.

That's why the idea of ownership folders sounded like it would make onboarding databases smoother, but yeah, it comes with its set of challenges.

[littleK0i]

Hmm, short lived schemas sounds like a problem for Snowflake in general, since DDL is not transactional by design, and creating a schema usually involves a lot of additional queries (future grants, roles, etc). I think it may also have negative impact on user experience. Objects in object browser may disappear, and auto-complete might be messed up.

Any chance to get rid of it? Regardless of tool, it should help a lot. It is still ok to create & drop individual tables and probably leave schemas empty, if necessary.

---

Please note, SingleDB does not involve grants and role hierarchy. Not sure if it can be safely mixed with normal mode.