Limitations & workarounds
Partial application of config
One of fundamental limitations of Snowflake is lack of transaction support for DDL commands. All DDL commands are executed 1-by-1 and are committed immediately.
In practice, it means that only some DDL commands might be executed, and the object schema will be stuck somewhere in between the original state and desired state described in config.
All object management tools are affected by this problem, but SnowDDL is generally in better position. You may fix technical issues and restart it any number of times, and SnowDDL will do its best to "repair" the object schema and bring it to the final state.
Please make sure to always keep & check SnowDDL execution logs and detect warnings early.
Renaming of objects
Renaming is currently not supported. Full object names are used as unique identifiers to match configuration entries with existing objects in Snowflake account.
If you already use env prefix feature for DEV and QA, usually it is not a problem. You may destroy & create all objects with specific env prefix from scratch, which handles renaming automatically.
For PROD it's a bit more tricky. As a workaround, we suggest to create a file with "release notes" for each release describing actions which should be performed manually.
Manual renames should be applied before SnowDDL "apply" command.
Also, it is highly recommended to avoid --apply-unsafe option for PROD and review all "unsafe" changes manually. It will help to prevent potential loss of data even if release engineer forgets to apply renaming prior to SnowDDL launch.
Lower case identifiers
Lower case identifiers cause a lot of problems down the line and are not supported on purpose. Please check rationale on Object Identifiers documentation page.
Tags
Object tagging is currently not supported due to latency of up to 2h on TAG_REFERENCES view and TAG_REFERENCES_WITH_LINEAGE function. There is no way to find all objects referenced by specific tag reliably.
As soon as Snowflake improves this situation, the SnowDDL will support tags.
Masking policy, row access policy
Snowflake has a fundamental limitation related to policies. In certain cases policy has to be re-created entirely from scratch.
When this happens, old policy has to be detached from all associated objects first, and new policy has to be reattached to all objects afterwards. It opens the opportunity for potential race condition, when users are able to access objects "unprotected" by policy.
In order to mitigate this problem, it is advised to have a small "maintenance" window when business users cannot login and access protected objects at all. And you should apply changes to existing policies only during this window.
Alternatively, basic secure VIEWS with checks on CURRENT_ROLE() might be a better option to achieve the same result.
Snowpark & UDF (latest features)
Snowpark and UDF functions are currently in a very active development by Snowflake. There are many "preview" and "undocumented" features.
If you notice a UDF feature which is currently missing, please raise a ticket on GitHub, and I'll add it in a few days.
File format
It is possible to set file format options for EXTERNAL TABLES, PIPES and STAGES using name references to FILE FORMAT objects. Currently it is not possible to use inline format options. It is an intentional design decision.
The reasons are following:
it helps to improve clarity by storing format options in one object type only;
it helps to reduce duplication of format options in config;
it helps to reduce complexity of resolvers, especially when it comes to edge cases;
it makes it easier to get rid of short hash eventually;
Unfortunately, it may force you to create a few named FILE FORMAT objects when you would normally have none. But, in my opinion, it is a small price for all the benefits provided by this approach.
Last updated