automate snowDDL

[PradeepReddy-epi]

Hi,
We're using snowDDL singleDB to manage SNF objects and currently we are running snowDDL commands locally through CLI. Are there any tools/solutions to automate snowDDL singleDB like terraform CI automation, reviewing plan from PR comments and applying ?
Is it through github workflows? are there any github actions available?

Thanks

[littleK0i]

In my opinion, running SnowDDL from CLI is likely the best and the safest way to use it. I'll explain why.

Snowflake is a cloud DWH. Unlike traditional "on-premises" DWH, as a customer you have no control over Snowflake version updates. It is possible for Snowflake to release an update with bug or incompatible behaviour change. It may affect schema management tools and cause unintended consequences.

Also, Snowflake does not support transactions for DDL changes. It is possible to get stuck in between of "old" and "new" state.

---

The most reasonable release workflow seems to be the following:

1. Run snowddl apply, but do not add --apply-unsafe. Only "safe" changes will be applied automatically. SQL for "unsafe" changes will be dumped to STDOUT.
2. Copy-paste "unsafe" changes to Snowflake SQL console. Review and apply such changes 1-by-1 manually. If something goes wrong, you should stop and investigate.
3. Run snowddl apply to make sure no more changes are suggested.

---

If you want to go with further automation and add GitHub actions + review, it should be relatively easy to implement. Just take the output of snowddl apply command and put it on review as SQL script. If review is successful, apply SQL script.

However, with this approach you open the possibility for a few issues:

• Passwords and access keys might be exposed during review.
• Something or someone might change object schema between generation of SQL for review and actual application of this SQL.
• You may have to run snowddl apply again after application of reviewed SQL script to detect and apply additional changes. Which may produce even more "unsafe" SQL.

In my opinion, it is much safer to keep using CLI at least until Snowflake introduces transactional DDL. Otherwise the automation will break something eventually. It applies to Terraform as well.

[PradeepReddy-epi]

Thanks for detailed reply, that's so helpful.

[PradeepReddy-epi]

@littleK0i reg. passwords, keys exposure issues, If i just automate snowddl-singled (which doesn't deal with account level objects), and use github secrets for storing service account passwords.. then there is no issue with password, keys exposure issue right?

[littleK0i]

@PradeepReddy-epi , yes, it is generally safe.

I see only a few scenarios when you may expose something with schema-level objects:

• FUNCTION contents, if used for encryption of sensitive data;
• MASKING_POLICY contents;
• ROW_ACCESS_POLICY contents;
• EXTERNAL STAGE: bucket and path, custom encryption key (rarely used in practice);

Raw access key + secret key auth is not allowed for external stages, so this is not an issue.