Snowflake Automation using SnowSql CLI || Create Azure DevOps CI-CD using Snowsql CLI

Hello Guys,

I am writing this blog to understand how we can automate snowflake databases using CI-CD pipelines in DevOps, I am using Azure DevOps tool to automate the snowflake pipeline.

To start it let’s have a repository and create a folder inside called Migrations and inside it creates a .sql file or you can have multiple .sql files if you want to execute the multiple files.

file1.sql — create a table using a Sequence

use role sysadmin;

use database snowstages_db;

use schema public;

create or replace table test

(

Id integer default SEQ_1.nextval,

name varchar(50),

Dept_number int

);

file2.sql — create a clone table using first one

use role sysadmin;

use schema public;

Create transient table if not exists Test_Clone1 clone Test;

Go inside the Library in DevOps Portal and add Config variables:

Once you have added the env variables, let's go to the pipeline and create a new one, create Azure repo git .Yaml file, copy-paste the code below and make sure you have set up the right configurations in yaml file.

Note: There is a problem with .snowsql cli, it doesn’t support reading sql files from a folder or repository, so either we have to use a separate tool like schema change or we can give filenames separately if those are not more in the count.

A few more points I am adding: 1. AWS Configuration Variables is your configuration name.

# Deploy database changes using snowsql

trigger:

branches:

include:

- main

paths:

include:

- /Migrations

pool:

vmImage: ‘ubuntu-latest’

variables:

- group: Snowflake_DB_Configs

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 snowsql’

curl -O https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap

/1.2/linux_x86_64/snowsql-1.2.9-linux_x86_64.bash

SNOWSQL_DEST=~/snowflake SNOWSQL_LOGIN_SHELL=~/.profile bash snowsql-1.2.9-linux_x86_64.bash

echo ‘Test installation’

~/snowflake/snowsql -v

echo ‘run the code file’

~/snowflake/snowsql -f $(PROJECT_FOLDER)/Migrations/file1.sql -f

$(PROJECT_FOLDER)/Migrations/file2.sql -a $(SF_ACCOUNT) -u $(SF_USERNAME)

-r $(SF_ROLE) -w $(SF_WAREHOUSE) -d $(SF_DATABASE)

env:

SNOWSQL_PWD: $(SF_PASSWORD)

Once your build is succeeded, you will get your tables created in snowflake database.

Enjoy :) Happy automation 😊 .