Author: Prashant Sharma
Overview and Architecture
Azure DevOps Overview:
Azure DevOps provides all the services in a single service which helps a team in planning the work, collaborating on the code development, and the build/deployment of the application.
Organizations can use cloud Azure DevOps Service or On-Premise Service using Azure DevOps Server.
It has all the end-to-end development tools required in a Software Development Life Cycle within a single cloud offering by Azure. It has below listed offerings in particular:
Azure Boards: A suite of Agile tools either using Scrum or Kanban to support project planning and tracking, bugs and issues tracking, etc
Azure Repos: Git repositories for managing the source control and workflow of the code.
Azure Pipelines: Continuous integration and delivery of applications supported by build and release services.
Azure Test Plans: Continuous testing and manual/exploratory testing provided via different tools help in testing the applications.
Azure Artifacts: Platform to share libraries and packages, such as npm, pip, Maven, etc., from both public and private sources for integration in CI/CD pipelines.
Schemachange is a Database Change Management (DCM). DCM’s are used to manage different objects in a database via some tools or processes.
Schemachange (previously known as Snowchange) is a lightweight Python-based tool to manage all Snowflake objects. It follows an imperative-style approach to managing changes within a database. Using CI/CD and Version Control tools with Schemachange, modern DevOps practices can be applied for approving and deploying changes to databases through a pipeline.
In this implementation, we have used Azure Reops and Azure Pipeline with Schemachange as our DCM to manage our Snowflake Account. The Following architecture states the flow:
1. Active Snowflake Account
2. Database named demo_cars_db
3. A snowflake user with all permission on demo_cars_db. Especially create object permission 4. Functional Azure Account
5. An Azure DevOps Organization
6. An Azure DevOps Project in that Organization
7. An Azure DevOps Repo for the Git Repository
8. IDE and Git Repository cloned into it
Setting Up: Initial Version
Creating First Database Migration:
1. Open the IDE with the cloned repository
2. Create a folder migration
3. Create a Script with the following name: V1.1.1__initial_table_creation.sql (Ensure to add double underscore (__) after version)
Note: Schema Change follows file versioning for controlling re-execution of the already executed script. So, if a version file is deployed successfully and any change is made, it will not be reflected in Snowflake. The file is skipped.
4. In the Script, add the following table creation script:
CREATE OR REPLACE SCHEMA demo_cars;
CREATE OR REPLACE TABLE cars
5. Commit the changes and push them into the repository.
Setting Up: Azure Pipeline
Creating Pipeline Variable Group:
1. Goto Pipelines
2. Select Library -> Create Variable Group -> Name it: Snowflake CI-CD Group
3. Add the following Variables:
Don’t add .snowflakecomputing.com
Role with privileges to work on demo_cars_db
4. Save the Group
5. Update Pipeline Permission, for now, keep it without any restrictions i.e., Allow access to all pipelines
Create Pipeline and Run a Deployment:
1. Navigate to Pipeline
2. Create New Pipeline -> Select Azure Repos Git
3. Select a Repository (A repo with the project name will be present; select it)
4. Select Starter Pipeline
5. Add the Following YAML:
# Deploy database changes using snowchange
# (see https://aka.ms/yaml for the YAML schema reference)
- group: Snowflake CI-CD Group
- task: UsePythonVersion@0
displayName: 'Use Python 3.8.x'
- task: Bash@3
echo 'Starting bash task'
echo "PROJECT_FOLDER $(PROJECT_FOLDER)"
echo 'Step 1: Installing schemachange'
pip install schemachange --upgrade
echo 'Step 2: Running schemachange'
schemachange -f $(PROJECT_FOLDER)/migrations -a $(SNOWFLAKE_ACCOUNT_NAME) -u $(SNOWFLAKE_DEVOPS_USERNAME) -r $(SNOWFLAKE_ROLENAME) -w $(SNOWFLAKE_WAREHOUSE) -d $(SNOWFLAKE_DATABASE) -c $(SNOWFLAKE_DATABASE).SCHEMACHANGE.CHANGE_HISTORY --create-change-history-table
6. Click Save and Run
7. Click on the Job on Pipeline Run Overview Page. Your newly created pipeline will be executed with outputs of the pipeline execution. Check bash for any kind of errors.
1. A file name azure-pipelines.yml will be created in your git repo. This name should be reconfigured in case you have multiple pipeline definitions in your repository.
2. Trigger definition configures the pipeline to automatically run on a committed change anywhere in the “migrations” folder on the “main” branch of the repository. Changes committed outside of that folder or in a different branch will not trigger the pipeline to run.
Setting Up: New Version Commit
Create Version 2 for testing of automatic deployment:
1. In your IDE, Create a file V1.1.2__update_table_object.sql
2. Add the following script:
USE SCHEMA demo_cars; ALTER TABLE cars ADD COLUMN car_type VARCHAR;
3. Commit the changes and Push them to the git Repo
4. Navigate to Azure Pipeline cars -> Select Pipeline (It will be named as per your project)
5. In Pipeline Run Overview, Select the latest Run from Run's Table
6. Verify your pipeline is executed successfully
Time to Check Snowflake:
1. Open your Snowflake Account
2. Check for newly created schema
3. Verify the Query History
1. While executing the pipeline for the first time, you’ll get the agent pool error in the pipeline job. The only solution for this in a trial account is to fill out a support form.
2. After submitting, Microsoft takes around 48-72 hours to complete your request 3. Link to the form Azure Agent Pool.
3. Fill out the above form with the requested account information