Snowflake DevOps: Building a CI/CD Pipeline with GitHub and Snowflake CLI

Introduction:

DevOps is a set of practices that combines software development (Dev) and IT operations (Ops) to enhance collaboration, automation, and efficiency in the development lifecycle. It aims to streamline workflows, reduce manual efforts, and improve deployment reliability.

Implementing DevOps best practices ensures that changes to tables, data pipelines, and other database objects are controlled, repeatable, and secure. By integrating continuous integration (CI) and continuous deployment (CD), teams can automate testing, monitor changes, and enforce security policies, leading to faster and more reliable software releases. DevOps fosters a culture of collaboration, helping development and operations teams work together seamlessly while minimizing risks and downtime.

Snowflake DevOps enables developers to streamline and automate the software development lifecycle for their Snowflake environments. By leveraging CI/CD best practices, deployment automation, and infrastructure management, Snowflake DevOps tools ensure seamless integration between development and operational tasks. This approach integrates Git version control, declarative object management, and automated CI/CD workflows.

Key Benefits:

• Streamline workflows by keeping assets in a Git repository connected with Snowflake
• Maintain separate environments for development, test, and production
• Parameterize scripts with Jinja templates
• Manage changes with declarative code and versioning
• Automate CI/CD jobs with GitHub Actions

Prerequisites:

• A Snowflake Account (With ACCOUNT ADMIN Access)
• A GitHub Account
• VS Code Editor

Steps to Implement:

• Create API Integration in Snowflake

CREATE OR REPLACE API INTEGRATION git_api_integration
API_PROVIDER = git_https_api
API_ALLOWED_PREFIXES = ('https://github.com/')
ENABLED = TRUE;

• Create a Public Repository in GitHub and clone it in VS Code Editor.
• Create a branch named prod and set it as the default branch.
• Push a test file to prod.
• Create additional branches dev and exchange_rate_scripts, based on prod.
• In Snowflake, Create Database, Schema and Git Repository Object:

CREATE DATABASE DEVOPS;
CREATE SCHEMA COMMON;
CREATE OR REPLACE GIT REPOSITORY devops.common.git_repo
API_INTEGRATION = git_api_integration
ORIGIN = '<insert url of GitHub repo>';

• Verify Repository Access:

ls @DEVOPS.common.git_repo/branches/prod/;

• For this use case, we will use a dataset from the Snowflake Marketplace. Get the dataset from: Snowflake Data Finance & Economics

• Set Up a Local Sandbox Environment in Snowflake. As part of the script, a database, schema, table, and task will be created to fetch data using the dataset from Snowflake Data Finance & Economics.

CREATE OR ALTER DATABASE SANDBOX;
CREATE OR ALTER SCHEMA SANDBOX.LOAD;
CREATE OR ALTER TABLE SANDBOX.LOAD.STG_EXCHANGE_RATE
(
VARIABLE VARCHAR,
VARIABLE_NAME VARCHAR,
BASE_CURRENCY_ID VARCHAR,
QUOTE_CURRENCY_ID VARCHAR,
BASE_CURRENCY_NAME VARCHAR,
QUOTE_CURRENCY_NAME VARCHAR,
DATE DATE,
VALUE FLOAT
) DATA_RETENTION_TIME_IN_DAYS = 0; CREATE OR ALTER TASK SANDBOX.LOAD.TASK_EXCHANGE_RATE_REFRESH_DLY
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 09 * * * AMERICA/LOS_ANGELES'
AS
INSERT
OVERWRITE INTO SANDBOX.LOAD.STG_EXCHANGE_RATESELECT * FROM FINANCE__ECONOMICS.CYBERSYN.FX_RATES_TIMESERIES WHERE QUOTE_CURRENCY_ID='INR' ORDER BY DATE DESC LIMIT 1;

• This setup has been completed in the sandbox, and the next step is to move it to further environments: Dev and Prod.

• Now, switch to the branch Exchange_Rate_development in VS Code Editor.

• As part of the CI/CD pipeline workflow, the Snowflake CLI will be installed and used to execute the files. Snowflake CLI requires a config.toml file with a default template. The necessary authentication details will be provided in GitHub Actions secrets, and Snowflake CLI will use these details.

Create a file named config.toml in Exchange_Rate_development Branch with the following content:

default_connection_name = "DEFAULT"

[connections]
[connections.DEFAULT]

• Then, create a Scripts folder in the branch and add files for each execution separately, as shown below.

• But wait! Our code is currently pointing to the sandbox environment, but we need to move the same code to Dev and Prod. This is where Jinja comes into play.
• We will parameterize the database name using the environment Jinja variable and the data retention settings using the Retention Jinja variable. Additionally, we will define different retention values based on the environment in the workflow yml file.

CREATE OR ALTER DATABASE {{environment}};
CREATE OR ALTER SCHEMA {{environment}}.LOAD;
CREATE OR ALTER TABLE {{environment}}.LOAD.STG_EXCHANGE_RATE (
VARIABLE VARCHAR,
VARIABLE_NAME VARCHAR,
BASE_CURRENCY_ID VARCHAR,
QUOTE_CURRENCY_ID VARCHAR,
BASE_CURRENCY_NAME VARCHAR,
QUOTE_CURRENCY_NAME VARCHAR,
DATE DATE,
VALUE FLOAT
)
DATA_RETENTION_TIME_IN_DAYS = {{retention_time}}; CREATE OR ALTER TASK {{environment}}.LOAD.TASK_EXCHANGE_RATE_REFRESH_DLY
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 09 * * * AMERICA/LOS_ANGELES'
AS
INSERT OVERWRITE INTO {{environment}}.LOAD.STG_EXCHANGE_RATE
SELECT * FROM FINANCE__ECONOMICS.CYBERSYN.FX_RATES_TIMESERIES WHERE QUOTE_CURRENCY_ID='INR' ORDER BY DATE DESC LIMIT 1;

• In the workflow yml file, when it is executed, the environment is passed, and retention is handled accordingly as per the environment.
• Now, the Scripts folder and the config file for Snowflake CLI are ready. Before moving on to the workflow file, let’s set the secrets in GitHub.
• In Secrets, add your Snowflake Account Identifier, Username, and Password. I have named them as shown below. Note these down, as we will need to use them in the workflow file.

• Once all the changes are done, let’s commit and push the code to the branch.
• Next, go to the GitHub repository, click Actions, then select New Workflow → Configure Workflow.

Below is the workflow file: The workflow will be triggered for every push to the Dev branch. It will then wait for manual approval from the approvers before merging the code to Prod and executing it.

Here, you can see that we are using the secrets created earlier in the workflow file for authentication, along with the GitHub repository Snowflake object name and additional configurations, such as setting the default database, schema, and warehouse.

In the workflow file, you can also see that the Snowflake CLI is installed, the latest changes from the repository are fetched, the data retention parameter logic is applied based on the environment, and scripts are executed using the CLI.

name: Deploy data pipeline

# Action will run on pushes to selected branches
on:
  push:
    branches:
      - dev

  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:

jobs:
  deploy-dev:
    runs-on: ubuntu-22.04

    env:
      REPO_NAME: "devops.common.git_repo"
      # Read connection secrets
      SNOWFLAKE_CONNECTIONS_DEFAULT_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_CONNECTIONS_DEFAULT_USER: ${{ secrets.SNOWFLAKE_USER }}
      SNOWFLAKE_CONNECTIONS_DEFAULT_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
      SNOWFLAKE_CONNECTIONS_DEFAULT_DATABASE: devops
      SNOWFLAKE_CONNECTIONS_DEFAULT_SCHEMA: common 
      SNOWFLAKE_CONNECTIONS_DEFAULT_WAREHOUSE: COMPUTE_WH
 
    steps:
      # Checkout step is necessary if you want to use a config file from your repo
      - name: Checkout repository
        uses: actions/checkout@v4

      # Install Snowflake CLI GitHub Action and point to config file
      - name: Install snowflake-cli
        uses: Snowflake-Labs/snowflake-cli-action@v1.5
        with:
          cli-version: "latest"
          default-config-file-path: "config.toml"

      # Update Snowflake's copy of the repository
      - name: Fetch repository changes
        run: snow git fetch "${REPO_NAME}"

      # Deploy pipeline with branch specific parameters
      - name: Deploy data pipeline to dev
        run: |
          BRANCH_NAME=dev
          if [ "${BRANCH_NAME}" == "prod" ]; then
            RETENTION_TIME=1
          else
            RETENTION_TIME=0
          fi
          snow git execute \
            "@${REPO_NAME}/branches/dev/scripts/0*" \
            -D "environment='dev'" \
            -D "retention_time=${RETENTION_TIME}"

  manual-approval:
    name: Manual Approval
    runs-on: ubuntu-latest
    needs: deploy-dev
    if: success() 

    permissions:
      issues: write
          
    steps:
      - name: Await Manual Approval
        uses: trstringer/manual-approval@v1
        with:
          secret: ${{ github.TOKEN }}
          approvers: <your git usernmae> # Replace with GitHub usernames, with the free version you can only have an issue assigned to one username
          minimum-approvals: 1
          issue-title: "Manual Approval Required for Production Deployment"
          issue-body: "Please approve or deny the deployment."

  merge-branch:
    needs: manual-approval    
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - uses: everlytic/branch-merge@1.1.2
        with:
          github_token: ${{ github.token }}
          source_ref: ${{ github.ref }}
          target_branch: 'prod'
          commit_message_template: '[Automated] Merged {source_ref} into target {target_branch}'

  deploy-prod:
    needs: merge-branch
    runs-on: ubuntu-22.04

    env:
      REPO_NAME: "devops.common.git_repo"
      # Read connection secrets
      SNOWFLAKE_CONNECTIONS_DEFAULT_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_CONNECTIONS_DEFAULT_USER: ${{ secrets.SNOWFLAKE_USER }}
      SNOWFLAKE_CONNECTIONS_DEFAULT_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}  
      SNOWFLAKE_CONNECTIONS_DEFAULT_DATABASE: devops
      SNOWFLAKE_CONNECTIONS_DEFAULT_SCHEMA: common 
      SNOWFLAKE_CONNECTIONS_DEFAULT_WAREHOUSE: COMPUTE_WH         

    steps:
      # Checkout step is necessary if you want to use a config file from your repo
      - name: Checkout repository
        uses: actions/checkout@v4

      # Install Snowflake CLI GitHub Action and point to config file
      - name: Install snowflake-cli
        uses: Snowflake-Labs/snowflake-cli-action@v1.5
        with:
          cli-version: "latest"
          default-config-file-path: "config.toml"

      # Update Snowflake's copy of the repository
      - name: Fetch repository changes
        run: snow git fetch "${REPO_NAME}"

      # Deploy pipeline with branch specific parameters
      - name: Deploy data pipeline to prod
        run: |
          BRANCH_NAME=prod
          if [ "${BRANCH_NAME}" == "prod" ]; then
            RETENTION_TIME=1
          else
            RETENTION_TIME=0
          fi
          snow git execute \
            "@${REPO_NAME}/branches/prod/scripts/0*" \
            -D "environment='prod'" \
            -D "retention_time=${RETENTION_TIME}"

• Grant Read and Write permissions for the workflow under Settings → Actions → General → Workflow Permissions, as the merging of the Dev branch to Prod is handled through the GitHub Actions workflow.

• Create a pull request (PR) to merge the branch into Dev..
• Verify the changes and merge the PR. Once the PR is merged, the workflow will be triggered.

• As per the setup, the Dev execution will take place first.

• Then, it will request manual approval. For now, you can set your ID as the approver. You will receive a notification email and an open issue in GitHub Issues.

• Once the request is approved, the merge from Dev to Prod will be executed.

• After the merge, the execution will continue using the code available in the Prod branch.

Conclusion:

By integrating Git, Snowflake CLI, and GitHub Actions, we have seamlessly moved code across environments, ensuring a structured and automated deployment process.

In DevOps, Infrastructure as Code (IaC) is a common pattern applied when making infrastructure changes. Managing our database resources in a common, versioned source allows us to effectively collaborate and have a source off truth that is consistent across all deployments. IaC if often done in a declarative manner using tools such as terraform.

Declarative definitions are concise, idempotent and easy to understand as we don’t need to understand the entire stack of historic changes leading up to the current state as in tools like schemachange.

This is where the CREATE OR ALTER command shines. It allows us to have one single source of truth for the current state of the object. Potential rollbacks are super easy to do thanks to the straightforward integration with version control tools such as Git.

Please feel free to reach out to us for your Snowflake solution needs. Cittabase is a Premier partner with Snowflake.