Snowflake DevOps Deployment

Teams that change production tables by typing SQL into a worksheet eventually break something. DevOps practices bring order: scripts live in version control, changes pass through test environments, and releases follow a repeatable routine. Snowflake supports this style with cloning, Git integration, and automation tools.

The Recipe Book Comparison

A restaurant chain keeps official recipes in one book. Chefs test new dishes in a test kitchen before adding them to every branch. Nobody experiments during dinner service. Database code deserves the same discipline. Version control is the recipe book, the test environment is the test kitchen, and production is dinner service.

DEPLOYMENT PIPELINE
===================

 +-----------+   +-----------+   +-----------+   +-----------+
 | Developer |   |   GIT     |   |   TEST    |   |   PROD    |
 | writes    |-->| repository|-->| database  |-->| database  |
 | SQL files |   | review    |   | auto run  |   | approved  |
 +-----------+   +-----------+   +-----------+   +-----------+
                  pull request    checks pass     release

Environments

Run at least three environments: development, test, and production. Zero-copy cloning builds a realistic test environment in seconds.

CREATE DATABASE analytics_test CLONE analytics_prod;

-- Lower-cost warehouse for non-production work
CREATE WAREHOUSE test_wh WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60;

Separate roles for each environment keep developers away from production data.

Organizing the Repository

snowflake-project/
  migrations/
    V1.0.0__create_schemas.sql
    V1.0.1__create_orders_table.sql
    V1.0.2__add_order_status_column.sql
  views/
    R__sales_summary_view.sql
  tests/
    check_row_counts.sql
  README.md

Versioned files, marked V, run once in order. Repeatable files, marked R, rerun whenever their content changes and suit views and procedures.

Tools That Apply Changes

TOOL            ROLE IN THE PIPELINE
--------------  ---------------------------------------------------------
schemachange    Runs versioned SQL scripts and records history in a table
Snowflake CLI   Deploys apps, Snowpark code, and runs SQL from terminals
Terraform       Manages infrastructure: warehouses, roles, databases
dbt             Builds and tests transformation models
Git integration Runs scripts straight from a repository inside Snowflake

Git Integration Inside Snowflake

CREATE API INTEGRATION git_api
  API_PROVIDER = git_https_api
  API_ALLOWED_PREFIXES = ('https://github.com/myorg')
  ENABLED = TRUE;

CREATE GIT REPOSITORY analytics_repo
  API_INTEGRATION = git_api
  ORIGIN = 'https://github.com/myorg/snowflake-project.git';

-- Pull the latest commits
ALTER GIT REPOSITORY analytics_repo FETCH;

-- Run a script from the main branch
EXECUTE IMMEDIATE FROM @analytics_repo/branches/main/migrations/V1.0.1__create_orders_table.sql;

A Simple Automated Release

1. Developer opens a pull request with new SQL files
2. Reviewer approves the changes
3. Pipeline clones production into a temporary database
4. Pipeline runs the new scripts on the clone
5. Pipeline runs tests (row counts, null checks)
6. Pipeline drops the clone when tests pass
7. Release job runs the same scripts on production
8. Pipeline records the release version

Writing Safe Scripts

  • Use CREATE OR REPLACE for views and procedures, and IF NOT EXISTS for tables.
  • Avoid dropping columns without a backup clone or Time Travel window.
  • Keep one change per file, so reviews stay simple.
  • Use variables for database names to reuse scripts across environments.
  • Store credentials in the pipeline's secret manager and use key pair authentication for deployment users.

Role for Deployments

CREATE ROLE deploy_role;
GRANT CREATE SCHEMA, USAGE ON DATABASE analytics_prod TO ROLE deploy_role;
GRANT ROLE deploy_role TO USER ci_service_user;

A dedicated deployment role with limited rights narrows the damage from any pipeline mistake.

Rollback Options

  • Time Travel restores a table to a state before the change.
  • A pre-release clone provides a full fallback copy.
  • Reverse migration scripts undo structural changes.

A Practical Scenario

A team of six developers edits the same views and tables. Before adopting DevOps, two developers overwrote each other's work and broke a report. The team moves every script into a Git repository and requires a pull request for each change. A pipeline clones production, applies the new scripts, runs row count tests, and reports the result in the pull request. Reviewers merge only passing changes, and production releases happen the same afternoon without drama.

Frequently Asked Questions

Do small teams need this setup?

Even a team of two benefits from version control and a test clone. Start with scripts in Git and add automation as the team grows.

How do I keep secrets out of the repository?

Store credentials in the pipeline's secret manager and pass them to scripts at run time.

Testing Database Changes

Tests prove that a change works before production sees it. Simple tests count rows, check for null keys, and compare totals with the previous version. Teams store those checks as SQL files in the repository and run them automatically after each deployment to the test environment. A failed test blocks the release and sends a message to the author.

Team Habits That Help

Write short commit messages that explain the reason for a change. Review every pull request with a second pair of eyes. Schedule releases at quiet times and announce them to dashboard owners. These habits build trust between engineers and business users.

Key Points

  • Version control holds every SQL change for review and history.
  • Development, test, and production environments separate experiments from live data.
  • Cloning builds test environments quickly with little extra storage.
  • schemachange, Snowflake CLI, Terraform, and dbt automate deployment steps.
  • Git integration lets Snowflake run scripts from a repository.
  • Time Travel and clones give fast rollback paths.
Previous lesson
Back to course
Next lesson