Snowflake DDL Deployment

[praneeth1987]

We are planning to build the Snowflake DDL pipeline from Azure Devops. We are looking for version controlling at component level(Similar to Snowflake DB objects like DB--> Schema --> Tables, Views, Functions, Procedures etc.. ie each object will have physical file available in DevOps where we have version controlling available at object level

Can we have achieve this using Schemachange? Thanks

[praneeth1987]

Any update on this please?

[littleK0i]

@praneeth1987 , schemachange is probably not the best choice for this specific use case. It keeps track of migrations, but not of state of specific objects.

Recently I've open sourced a declarative-style tool SnowDDL, which has a config with 1 file per object. Maybe it can work for you a little bit better.

[praneeth1987]

@littleK0i thanks for the update. Also I have roles and schemas based on environment, where I want to deploy the code in all the 3 environments(DEV, QA & PROD) where as source artifact is same. How to variabilize this roles & schemas per environment in Azure DevOps and deploy in particular environment based on particular stage(DEV,QA & PROD) in stage?

It would be really helpful if you have any working example for this

[littleK0i]

@praneeth1987 , yes, it is possible.

Normally I would suggest to create separate Snowflake accounts for every environment. It helps to simplify things a lot.

Alternatively, it is possible to use a feature called "env prefix". It adds prefixes to most account-level objects, while applying the same config.

For example: DEV__DB, QA__DB, PROD__DB.

However, SnowDDL uses its own role hierarchy, which is currently semi-enforced. It is definitely not an easy "drop-in" replacement for schemachange.

I think we should definitely move to SnowDDL discussions section, if you have more questions.

Thank you.

[praneeth1987]

@littleK0i : Thanks for the update, one last question regarding this We are planning to configure the new Snowflake environment where we will be using schemachange to create warehouses, databases, schemas , roles and grant privileges,

What would be the best folder structure you suggest to have all these SQL related to admin activties

• going to have separate folder for databases, schemas, warehouses, roles & grants privileges SQL files
• Clubbing together in single folders

[sfc-gh-tmathew]

Hello @praneeth1987

We are reviewing open issues. Were you able to leverage Schemachange as intended.

To answer your questions, specifically, there is no restriction on folder structure. However, you can change the order of operation by following Approaches discussed in #110 OR execute the scripts in each folder using -f switch and executing the object types in a specific order of your choosing. The choice really depends on your use case.

Let us know if you have applied a different approach and if we could close this issue instead.