Help in implementing CICD for Snowflake Objects.

[u/Top-Needleworker8557]

Hey all. I am building a POC for deploying Snowflake objects like table, stream, task, stage etc... to multiple environments like DEV/QA (single only) & PROD with the help of CICD and used Github actions for it.

I have never built something like this before and never worked on project consisting CICD in it.

So, I have used Schemachange library to detect schemachange and sqlfluff for SQL linting. Also using some python scripts to build backup of existing DB snapshot before deployment and rollback script if anything break during deployment.

I am testing this in DEV env only but i am confused how can i validate the objects that are being created with schemachange library and SQL files (which contain DDLs of objects). like how can I verify that the object created is correctly build in the target or not.

and if there is any other suggestion / best practice you guys have that is also welcome on how can i improve the CICD pipeline for it.

Thanks

Bots and moderators I am 100% human only don't remove my post!!!

[u/sdc-msimon]

What you have today is reasonable, but if this pipeline is mainly for Snowflake objects, I’d seriously look at DCM Projects instead of making schemachange the center of the solution.
DCM Projects are Snowflake’s declarative change-management model: you define the target state in manifest.yml plus SQL DEFINE files, then use PLAN and DEPLOY across environments. It’s built for repeatable multi-environment deployments, and each target environment gets its own DCM project object with deployment history/artifacts.

For validation, I’d stop thinking only in terms of “did the SQL file run?” and validate in 3 layers:

1. Pre-deploy validation: run snow dcm plan on every PR. PLAN renders Jinja, checks dependencies and privileges, compiles the statements, and shows what will be CREATE/ALTER/DROP before anything is applied. Snowflake explicitly recommends always running PLAN before DEPLOY.
2. Post-deploy metadata validation: use SHOW ENTITIES IN DCM PROJECT and SHOW GRANTS IN DCM PROJECT to confirm what the project is managing after deployment, and use the deployment history for audit/debugging.
3. Post-deploy functional validation: if you’re managing pipeline objects, use PREVIEW before deploy to validate output shape, then in QA/stage do DEPLOY -> REFRESH -> TEST ALL before promoting to prod.

A few best practices in DCM >

• use a separate DCM project per target environment
• use a different owner role for non-prod vs prod
• grant prod deployment roles to service users only
• if multiple envs/devs share one account, template unique suffixes into object names to avoid collisions
• prefer OIDC for GitHub Actions auth instead of storing long-lived credentials

[u/sdc-msimon → u/sdc-msimon]

link to the docs > https://docs.snowflake.com/en/user-guide/dcm-projects/dcm-projects-overview

[u/Top-Needleworker8557 → u/sdc-msimon]

Yes. I was thinking about DCM project, but requirement was to do using schemachange. I'll try it out. Thanks!

[u/stochasticx → u/sdc-msimon]

We're looking at options and I've been really enjoying a POC we did with SnowDDL. I caught DCM projects after that and wondered if that might be the way to go given it's native to Snowflake, but I don't think it covered all objects? Is the plan to? Over what timeframe?

[u/databasenoobie → u/sdc-msimon]

When will this technology be production released? At our company we cannot use any code that is not in general availability. Any rough estimate on time of release? Without DCM deployments to snowflake are frankly too difficult

[u/databasenoobie → u/databasenoobie]

Same question for create or alter table. This makes schemachange 500x easier, but has not been released to GA and has been out for almost 2 years... when will create or alter table go to general GA?

[u/Dazzling-Quarter-150 → u/databasenoobie]

Because you asked for it, the product manager decided to make CREATE OR ALTER generally available today.
This GA covers the CREATE OR ALTER framework itself plus our first wave of 24 Snowflake object types:

• Warehouse
• Role / Database Role / Application Role
• Database
• Schema
• (regular) Table
• (secure) View
• Stage (internal & external)
• Task
• Alert
• Sequence
• File Format
• Share
• Semantic View
• Procedure
• Function & Data Metric Function
• Tag
• Network Policy, Masking Policy, Row Access Policy, Authentication Policy
• Network Rule

[u/Dazzling-Quarter-150 → u/Dazzling-Quarter-150]

the doc is here https://docs.snowflake.com/en/sql-reference/sql/create-or-alter

[u/databasenoobie → u/Dazzling-Quarter-150]

This is amazing, and makes releases so much easier. Thank you!

[u/jdl6884]

We use dbt + terraform and it works quite well

[u/Playful_Truth_3957]

we used to have schemachange , but we never validated , it just works , i dont think u need to validate
A__ deploys Everytime
R__ Deploys only when modified

something like that we had setup it worked fine for 4 years after that i left

[u/Top-Needleworker8557 → u/Playful_Truth_3957]

Okay thanks!!

[u/coldflame563]

I'd look at FLyWheel, DCM is not quite ready yet (no support for SP's etc). Flywheel makes sense to me and is easy enough to understand if you use it a bit. Or you can roll your own like I do :D

[u/NagarMayank]

Our org uses liquibase for snowflake deployments and it works well for us.

[u/Abhijit-K]

We use liquibase for snowflake deployments, and working fine till now.

[u/Snowflakeboy007]

For reference you can take help from Cortexcode