Deleted DB, USERS and etc. on DB creation.

[mpatn]

So I created a test folder called:

test/test_database/params.yaml

with the following data:

is_sandbox: true comment: Test database

and ran an apply, with:

-- account, u_name, password
-c test 
--apply-unsafe 
apply

This command deleted over 10 objects in Snowflake, why did this happen?

[littleK0i]

It is hard to tell without more information, but SnowDDL should output logs naming specific objects and applied operations.

The most likely scenario is the user with u_name was assigned with role which owned something else, except database TEST_DATABASE and objects in it. So all the extra stuff was dropped due to not being mentioned in config.

Btw, you may use plan instead of apply to preview upcoming changes. Also, you may add --show-sql CLI option to see specific SQL commands executed by SnowDDL.

[mpatn]

So it pulls role based account metadata?

Ya, I assumed the apply was fine because I wasn't explicitly tearing down any resources.

[mpatn]

So essentially the main difference between your solution and say a TF, is that TF uses state and you essentially maintain state through role ownership?

Is that about right?

[littleK0i]

That's correct. SnowDDL does not maintain a separate "state".

It checks objects which currently exist in Snowflake account and owned by role assigned to SnowDDL user. And compares this actual metadata with desired YAML or Python config.

In my view, this approach is superior. You may "break" objects in account by doing any kind of manual changes, and SnowDDL will be able to detect and "repair" it.

"State" cannot become desynchronised from actual objects, since there is no "state".

Role ownership is essential. It helps to distinguish objects managed automatically by SnowDDL and objects managed manually by administrator or by other tools, like Terraform.

[mpatn]

Ya, I don't dislike the approach, I just think you could be clearer here in the key features:

it reads current metadata from Snowflake account,

to something like:

it reads current metadata from Snowflake account, based role applying SnowDDL discretionary privileges

My suggestion is a jumbled mess, but I just think account is a little unclear.

[mpatn]

This gave me the impression it was account based when instead its role based, which I found rather puzzling when reading but assumed I just wasn't familiar enough with Snowflake metadata.

Cool tool though, I like the change from Terraform policies and having custom tools for Data Platform infra.

[littleK0i]

Thank you for pointing this out. Originally I am not a native english speaker, so some of the phrasing in README and documentation might be odd.

I'll see if I can improve it. Maybe even do a full pass with professional human editor, if lib gets a bit more popular.

[mpatn]

Also while I have you, is there a way to name change an object without tearing it down?

[littleK0i]

Good question. It is one of a very few change types which do not work well with purely declarative approach.

Originally in Bumble we used an optional plain SQL file for each release. It was stored in GIT near the config, and it had to be applied before running SnowDDL. It contained the following changes:

• all kinds of RENAME commands;
• complicated transformations and bug fixes for existing data (UPDATE, DELETE, MERGE);
• CREATE commands for INTEGRATION objects, which normally require some actions outside of Snowflake to finish setup;

But the good news are..

1. We did not need this file for 95%+ of releases.
2. We only needed it for PROD environment. Numerous DEV and STAGE environments could be entirely destroyed and re-created from scratch without any worries about renames.

---

If there is a good way to implement fully automated renames without introducing "state", I would be happy to do that.

But the main limitation here is the Snowflake architecture. Since it is not possible to execute multiple DDL commands in one transaction, Snowflake account might get stuck with config being partially applied. On the second run tool does not know which renames were actually applied and which renames were not.

Also, as an icing on the cake, the edge case of renaming is column name swapping. E.g., A -> B, B -> A.

As far as I know, it is currently not possible to swap column names without using some other temporary name (C) or without full table rewrite via CREATE OR REPLACE TABLE ... AS SELECT.

---

So.. it might seem like a simple feature on paper, but hard to do right in practice.

[mpatn]

In my opinion, its claimed to be stateless but really stateful when you really think about it. Snowflake is just managing the state, which I feel like is somewhat risky if Snowflake decides to make any changes on how role tables are managed.

Would it not be smarter to manage state with your own database, I'm not sure if that is what terraform does but then you could give each table an ID?

[mpatn]

Also I'm somewhat new to Snowflake, so what I'm saying might be gibberish.

[littleK0i]

SnowDDL is "stateless" relative to other tools. Terraform has an internal "state" which can get out of sync. Schemachange has "history table" which can be accidentally deleted. Etc.

There is still a "state" in Snowflake account, yes. But it is fundamentally maintained by Snowflake itself and not related to any specific tools. If something goes wrong with that state, it would be a major incident for Snowflake, of course. This state is very unlikely to break.

But... you're actually correct. What can change is the output of DESC and SHOW commands returning information about state of objects in Snowflake to our tools and scripts. New output columns can be added and old columns can be changed at any time with no notice. It is very rare, but things might break suddenly.

That's why we've introduced a concept of "safe" and "unsafe" changes in SnowDDL, and I would normally never apply "unsafe" changes to production account without an additional review. If something goes wrong with "Snowflake interpretation of state", there is a chance for data engineer to notice that and adapt accordingly.

As far as I know, it is (was?) a major issue for Terraform.