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 snowflake pipeline. 

To start it lets have a repository and create a folder inside like called Migrations and inside it create 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 added the env variables, lets go to the pipeline and create a new one, create Azure repo git .Yaml file, copy paste the below code and make sure you have setup the right configurations in yaml file.

Note: There is a problem with .snowsql cli, it doesn't support to read sql files from a folder or repository, so either we have to use the separate tool like schemachange or we can give files name separately if those are not more.. 

few more points i am adding : 1.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 😊😊😊😊 if any question, write in comment box.