schemachange
schemachange copied to clipboard
Snowflake-Labs
Snowflake DDL Deployment
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 , 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.
@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
@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.
@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