Snowflake CI/CD Using SchemaChange

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 Overview:

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.

Architecture:

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:

Prerequisites:

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 

 ( 

 Model VARCHAR, 

 Launch_Year NUMBER(4,0) 

 );

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:

Variable Name	Variable Value	Secret	Remarks
PROJECT_FOLDER	$(System.DefaultWorkingDirectory)	No	Don’t add .snowflakecomputing.com
SNOWFLAKE_ACCOUNT_IDENTIFIER	<Organization_Name>.<Account_Region>	No	-
SNOWFLAKE_DATABASE	demo_cars_db	No	-
SNOWFLAKE_DEVOPS_SECRET	Snowflake_user_password	Yes	-
SNOWFLAKE_DEVOPS_USERNAME	snowflake_user_name	Yes	-
SNOWFLAKE_ROLE_NAME	Snowflake_role	No	Role with privileges to work on demo_cars_db
SNOWFLAKE_WAREHOUSE	Snowflake_warehouse	No	-

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

# https://github.com/Snowflake-Labs/snowchange

# (see https://aka.ms/yaml for the YAML schema reference)

trigger:

  branches:

    include:

    - main

  paths:

    include:

    - /migrations

pool:

  vmImage: 'ubuntu-latest'

variables:

- group: Snowflake CI-CD Group

steps:

- task: UsePythonVersion@0

  displayName: 'Use Python 3.8.x'

  inputs:

    versionSpec: '3.8.x'

- task: Bash@3

  inputs:

    targetType: 'inline'

    script: |

      echo 'Starting bash task'

      echo "PROJECT_FOLDER $(PROJECT_FOLDER)"

      python --version

      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

  env:

    SNOWFLAKE_PASSWORD: $(SNOWFLAKE_DEVOPS_SECRET)

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.

Note:

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

Runtime Problems

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