SnowDDL
Search…
πŸ“‹
Main features

1) SnowDDL is "stateless"

Unlike schemachange and Terraform, SnowDDL does not maintain any kind of "state". Instead, it reads current metadata from Snowflake account, compares it with desired configuration and generates DDL commands to apply changes.
You may use one configuration for multiple accounts. You may repair problems caused by human errors, incorrect manual interventions, unexpected bugs, system outages, etc.

2) SnowDDL can revert changes

SnowDDL can revert object schema to any point in the past. You may simply checkout previous version of configuration from Git and apply it with SnowDDL.
Lack of option to revert changes is one of the biggest problems of imperative-style object management tools. It does not exist in SnowDDL.

3) SnowDDL supports ALTER COLUMN

Most changes to Snowflake table structure requires full re-creation of table and micro-partitions with data. For large tables it may incur significant additional costs.
But some changes are possible with ALTER TABLE ... ALTER COLUMN statement, which executes instantly and costs nothing. SnowDDL detects if it is possible to use ALTER TABLE before suggesting costly CREATE OR REPLACE TABLE ... AS SELECT.
New columns can be added and some data types can be changed instantly, without full table rewrite and at no extra cost.

4) SnowDDL provides built-in "role hierarchy" model

Snowflake documentation mentions benefits of role hierarchy, but it does not provide any real world examples.
SnowDDL offers a well thought 3-tier role hierarchy model. It is easy to understand, largely automated and requires minimal configuration. Also, it is crystal clear for security officers and external auditors.
GRANTS will no longer be a problem when organization complexity grows.

5) SnowDDL re-creates invalid views automatically

Views may become invalid when underlying objects were changed. SnowDDL detects such views using a free .describe() call, and re-creates such views if necessary.
You'll get less complaints from users about invalid views. There is no need to maintain a separate script to fix views.

6) SnowDDL simplifies code review

DDL queries are classified into "safe" and "unsafe" categories.
"Safe" queries can be applied and reverted with little to no risk (e.g. CREATE). "Safe" queries usually do not require code review and can be applied immediately.
"Unsafe" queries may potentially cause loss of data or security issues (e.g. ALTER, DROP). "Unsafe queries" usually do require more attention.
This classification helps to manage code review process better, but it is optional.
You decide which DDL categories to "apply" immediately and which categories to "suggest" for manual review and manual application by ACCOUNTADMIN later. SnowDDL will not accidentally DROP your database, unless you explicitly allow it to happen.

7) SnowDDL supports creation of isolated "environments" for individual developers and CI/CD scripts

Multiple independent versions of the same configuration can be applied to one Snowflake account using env prefix. Unique prefix will be added to name of each account-level object, which allows multiple developers to work on the same code simultaneously without conflicts.
It is also helpful for automated testing, when each set of tests will be executed in a separate "environment".
For example, you have a production database called BOOKINGS. Alice can create her own dev copy called ALICE__BOOKINGS, and Bob can create his own dev copy called BOB__BOOKINGS. Alice and Bob will never clash during development. Such "environments" can be created and destroyed instantaneously at any time.

8) SnowDDL strikes a good balance between dependency management overhead and parallelism

Different object types are resolved sequentially. But objects of the same type are resolved in parallel. It provides great performance for configurations with large number of objects, but it also simplifies dependency management.
All views are created after all tables. All tables are created after all schemas. Only rare dependencies within the same object type should be maintained (e.g. view depends on another view).

9) SnowDDL configuration can be generated dynamically in Python code

SnowDDL can run in "basic mode", using provided CLI interface and YAML configs. It should be enough for most users.
However, it can also run in "advanced mode", when configuration is generated dynamically in Python code.
You may build custom automation based on any data source when basic configs are no longer enough.

10) SnowDDL can manage packages for Java and Python UDF scripts natively

Recently, Snowflake introduced Snowpark and UDF functions written in Java, Scala, Python. Such functions may rely on external packages and libraries, which should be uploaded to internal stages. Changes in packages should be synchronised with changes in UDF function code, and SnowDDL can do it for you using special object type STAGE FILE.
Copy link
On this page
1) SnowDDL is "stateless"
2) SnowDDL can revert changes
3) SnowDDL supports ALTER COLUMN
4) SnowDDL provides built-in "role hierarchy" model
5) SnowDDL re-creates invalid views automatically
6) SnowDDL simplifies code review
7) SnowDDL supports creation of isolated "environments" for individual developers and CI/CD scripts
8) SnowDDL strikes a good balance between dependency management overhead and parallelism
9) SnowDDL configuration can be generated dynamically in Python code
10) SnowDDL can manage packages for Java and Python UDF scripts natively