Safe & unsafe DDL

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.

• "Unsafe" queries may potentially cause loss of data or security issues (e.g. ALTER, DROP). "Unsafe queries" usually do require more attention from reviewers.

By default only "safe" queries are applied by SnowDDL.

Add argument --apply-unsafe when calling SnowDDL CLI interface to apply unsafe queries as well.

Special cases

On top of that, some "unsafe" queries require an additional argument to be applied.

• --apply-replace-table is a safety check to prevent overspending of credits. Unlike ALTER TABLE, CREATE OR REPLACE TABLE ... AS SELECT requires an active warehouse and full rewrite of original table, which may cost a lot if table is big.

• --apply-account-params is a safety check to prevent accidental changes in ACCOUNT PARAMETERS. It may cause security issues if applied to account-level NETWORK POLICY without review. It may cause timeouts during query execution, it may break timestamp-related settings, etc. etc.

• --apply-network-policy is a safety check to prevent changes in NETWORK POLICIES. Unlike "on premise" DWH systems, Snowflake is exposed to anyone on the Internet, and the only thing preventing a free access is NETWORK POLICY. Updates to NETWORK POLICY should not be applied without review.

• --apply-resource-monitor is a safety check to prevent changes in RESOURCE MONITORS. It may cause all sorts of issues from overspending to inability of business users to run even a single query due to resource monitors running out of credits quota.

• --apply-masking-policy and --apply-row-access-policy is a safety check for data security "policies". The main issue with policies is related to lack of transactional DDL in Snowflake. Currently it is not possible to re-create the whole policy "atomically". First all references must be dropped, after that policy can be re-created, and all refs should be re-applied once again. But during all these operations your data remains potentially exposed. All changes to policies should be applied under the strict control.

Suggested queries

All queries which are not "applied" due to safety settings, are "suggested" instead. Suggested queries are outputted to STDOUT and are available for copy-paste and manual review by administrator.

You can apply such queries manually using ACCOUNTADMIN role. All "suggested" queries use fully-qualified identifiers, and the "OWNERSHIP" of schema-level object will remain intact due to FUTURE GRANTS.