Snowflake DevOps¶

Streamline workflows by keeping assets in a Git repository connected with Snowflake¶

You can ensure consistency, simplify collaboration, and streamline rollbacks if needed by keeping your data assets, code, and configurations centrally managed and version-controlled in a Git repository and using the repository from Snowflake.

You can connect your Snowflake acccount with your Git repository so that Snowflake can execute code in files cloned from the repository. The result is a Git repository stage that represents a full clone of your repository. From within Snowflake, you can access version-controlled files at a certain commit, branch, or tag.

With the repository stage, you can create a Snowpark function or procedure from a version controlled file and directly execute versioned SQL scripts with EXECUTE IMMEDIATE FROM or use the Snowflake CLI to execute Python scripts. This way, you can declaratively define database objects with SQL or Python and then deploy those objects with a robust and easy to set up CI/CD pipeline.

Maintain separate environments for development, test, and production¶

By maintaining separate environments for development, testing, and production, your teams can isolate development activities from the production environment, reducing the likelihood of unintended consequences and data corruption.

When you separate workflows into multiple environments, each gets its own Snowflake database — typically an identical copy.

CREATE OR ALTER TASK {{ environment }}.my_schema.my_task
  WAREHOUSE = my_warehouse
  SCHEDULE = '60 minute'
  AS select pi();

import os
from snowflake.core import Root, CreateMode
from datetime import timedelta
from snowflake.core.task import Task

my_task = Task(
    name="my_task",
    warehouse="my_warehouse",
    definition="select pi()",
    schedule=timedelta(minutes=60)
)
root = Root(Session.builder.getOrCreate())
tasks = root.databases[os.environ["environment"]].schemas["my_schema"].tasks
tasks.create(my_task, mode=CreateMode.or_replace)

snow git execute @my_git_repo/branches/main/path/to/my_scripts" \
    -D "environment='preprod'"

Manage changes with declarative code and versioning¶

You can more easily manage changes to your database resources with reusable configuration files in your Git repository.

You can define database objects using the CREATE OR ALTER <object> command, which creates the object or alters it to match the definition specified by the command. By using this command from a versioned file in a repository, you can more easily roll back changes to a previous version: you merely execute a previous version of the file.

CREATE OR ALTER TABLE vacation_spots (
  city VARCHAR,
  airport VARCHAR,
  avg_temperature_air_f FLOAT,
  avg_relative_humidity_pct FLOAT,
  avg_cloud_cover_pct FLOAT,
  precipitation_probability_pct FLOAT
) data_retention_time_in_days = 1;

from snowflake.core import Root
from snowflake.core.table import PrimaryKey, Table, TableColumn

my_table = root.databases["my_db"].schemas["my_schema"].tables["vacation_spots"].fetch()
my_table.columns.append(TableColumn(name="city", datatype="varchar", nullable=False]))
my_table.columns.append(TableColumn(name="airport", datatype="varchar", nullable=False]))
my_table.columns.append(TableColumn(name="avg_temperature_air_f", datatype="float", nullable=False]))
my_table.columns.append(TableColumn(name="avg_relative_humidity_pct", datatype="float", nullable=False]))
my_table.columns.append(TableColumn(name="avg_cloud_cover_pct", datatype="float", nullable=False]))
my_table.columns.append(TableColumn(name="precipitation_probability_pct", datatype="float", nullable=False]))

my_table_res = root.databases["my_db"].schemas["my_schema"].tables["vacation_spots"]
my_table_res.create_or_alter(my_table)

Automate CI/CD jobs with GitHub Actions¶

You can use GitHub Actions to automate the jobs that constitute a CI/CD pipeline. With GitHub Actions or similar CI/CD runners, you can define workflows that automate build, test, and deployment tasks.

In these workflows, you can use other features of Snowflake and GitHub to put the pieces together. You can do the following tasks:

• Store your Snowflake credentials in a GitHub secret so that GitHub Actions can connect to Snowflake.

• Easily install the Snowflake CLI and connect to Snowflake with the help of the native Snowflake CLI GitHub action.

• Execute commands in Snowflake, by using the Snowflake CLI. First, fetch the most recent changes from your Git repository to Snowflake. Next, execute your declarative and parametrized scripts to deploy your changes to an environment of your choice.

The GitHub Actions workflow excerpt in the following example deploys a pipeline. The workflow authenticates with Snowflake by retrieving the needed values from previously configured secrets.

To deploy the pipeline, the workflow executes the following snow git commands:

• snow git fetch pulls the latest from the GitHub repository to the cloned my_git_repo Git repository stage in Snowflake.

• snow git execute runs Python and SQL scripts in the /scripts directory from the main branch of the repository.

name: Deploy scripts to preprod

on:
  push:
    branches:
      - main

jobs:
  deploy:
    runs-on: ubuntu-latest

    env:
      SNOWFLAKE_CONNECTIONS_DEFAULT_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWFLAKE_CONNECTIONS_DEFAULT_USER: ${{ secrets.SNOWFLAKE_USER }}
      SNOWFLAKE_CONNECTIONS_DEFAULT_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Install snowflake-cli
        uses: Snowflake-Labs/snowflake-cli-action@v1.5
        with:
          cli-version: "latest"
          default-config-file-path: ".snowflake/config.toml"

      - name: Fetch repository changes
        run: snow git fetch my_git_repo

      - name: Deploy scripts to preprod environment
        run: snow git execute @my_git_repo/branches/main/scripts/* \
            -D "environment='preprod'"