Best Practice for Pre- and Post- deployment script

[minghungcho]

What's the recommendation to add pre- or post- deployment script?
For instance, to handle column rename, or data type change, I can run post-deployment script to clean up unused data or objects.

[littleK0i]

Ok, it is a very interesting question. I'll describe the "best practice" from my point of view, followed by advice about automation.

1) It is not recommended to fully automate DDL management for Snowflake

Reasons:

1. Snowflake does not support transactions for DDL operations. Snowflake account may get stuck between "old" and "new" object schema version very easily.
2. Snowflake may release changes which are not compatible with existing code without warning. For example, SHOW TABLES output may change and break existing scripts.

So at least a small amount of manual DDL review & execution is required, especially for business critical Snowflake accounts.

SnowDDL introduces a concept of "safe" and "unsafe" DDL (doc) to reduce the burden of manual review. "Safe" DDL is supposed to be applied automatically, and "unsafe" DDL is supposed to be reviewed and applied manually.

2) SQL files with release notes

If you already have a process of applying "unsafe" DDL manually, you may simply ask developers to include basic SQL files with renames. These files should be applied manually before SnowDDL execution.

However, the good thing about SnowDDL is...

3) You'll need such SQL files very rarely

SnowDDL is able to handle most operations out of the box:

• Data type change is fully supported by ALTER TABLE (if possible) or CREATE TABLE ... AS SELECT ....
• Unused objects are detected and deleted automatically using "unsafe" DDL.
• etc.

The only problematic operation is renaming, since declarative-style tools can only rely on names to identify objects.

4) What if I still want to run a pre- or post-deployment script automatically?

Assuming you already have a CI/CD pipeline with SnowDDL, you may simply add one more step running (SnowSQL + SQL file) before or after.

Make sure to use the same credentials and to abort execution if something fails.