schemachange

Looking for snowchange? You've found the right spot. snowchange has been renamed to schemachange.

Overview

schemachange is a simple python based tool to manage all of your Snowflake objects. It follows an Imperative-style approach to Database Change Management (DCM) and was inspired by the Flyway database migration tool. When combined with a version control system and a CI/CD tool, database changes can be approved and deployed through a pipeline using modern software delivery practices. As such schemachange plays a critical role in enabling Database (or Data) DevOps.

DCM tools (also known as Database Migration, Schema Change Management, or Schema Migration tools) follow one of two approaches: Declarative or Imperative. For a background on Database DevOps, including a discussion on the differences between the Declarative and Imperative approaches, please read the Embracing Agile Software Delivery and DevOps with Snowflake blog post.

For the complete list of changes made to schemachange check out the CHANGELOG.

To learn more about making a contribution to schemachange, please see our Contributing guide.

For maintainers: See docs/maintainers for repository management guides.

Please note that schemachange is a community-developed tool, not an official Snowflake offering. It comes with no support or warranty.

Quick Start

Get schemachange running in 5 minutes:

1. Install schemachange

pip install schemachange

2. Create your first migration script

mkdir -p migrations
cat > migrations/V1.0.0__initial_setup.sql << 'EOF'
CREATE SCHEMA IF NOT EXISTS my_app;
CREATE TABLE IF NOT EXISTS my_app.customers (
    id INTEGER,
    name VARCHAR(100)
);
EOF

3. Run your first deployment

Option A: Using environment variables (recommended for CI/CD)

export SNOWFLAKE_ACCOUNT="myaccount.us-east-1.aws"
export SNOWFLAKE_USER="my_user"
export SNOWFLAKE_PASSWORD="my_password"  # Or use a PAT token
export SNOWFLAKE_ROLE="MY_ROLE"
export SNOWFLAKE_WAREHOUSE="MY_WH"
export SNOWFLAKE_DATABASE="MY_DB"

schemachange deploy -f migrations

Option B: Using CLI arguments (quick tests)

# Password must be set as environment variable
export SNOWFLAKE_PASSWORD="your_password_or_pat"

schemachange deploy \
  -f migrations \
  -a myaccount.us-east-1.aws \
  -u my_user \
  -r MY_ROLE \
  -w MY_WH \
  -d MY_DB

Option C: Using connections.toml (local development)

# Create ~/.snowflake/connections.toml with your credentials
schemachange deploy -f migrations -C my_connection

4. Verify your deployment

# Check what schemachange sees
schemachange verify -f migrations

# Check Snowflake (using Snowflake CLI)
snow sql -q "SELECT * FROM metadata.schemachange.change_history ORDER BY installed_on DESC LIMIT 5;"

🎯 Next Steps

• Learn the basics: Change Scripts - Understand versioned, repeatable, and always scripts
• Configure properly: Configuration - Choose the right config method for your environment
• Secure your setup: Authentication - Use JWT, PATs, or SSO instead of passwords
• CI/CD integration: Using in a CI/CD Pipeline - Automate your deployments

Table of Contents

1. Overview
2. Quick Start
3. Project Structure
   1. Folder Structure
4. Change Scripts
   1. Versioned Script Naming
   2. Repeatable Script Naming
   3. Always Script Naming
   4. CLI Migration Scripts
   5. Script Requirements
   6. Using Variables in Scripts
      1. Secrets filtering
   7. Jinja templating engine
   8. Gotchas
5. Change History Table
6. Authentication
   1. Password Authentication
   2. External OAuth Authentication
   3. External Browser Authentication
   4. Okta Authentication
   5. Private Key Authentication
7. Configuration
   1. connections.toml File
   2. YAML Config File
      1. Yaml Jinja support
   3. Environment Variables
   4. Configuration Priority
   5. Account Identifier Format
   6. Required Snowflake Privileges
8. Deployment Scenarios
   1. Out Of Order
9. Upgrading to 4.1.0
10. Commands
    1. deploy
    2. render
    3. verify
11. Troubleshooting
12. Running schemachange
    1. Prerequisites
    2. Supported Python Versions
    3. Running the Script
13. Integrating With DevOps
    1. Sample DevOps Process Flow
    2. Using in a CI/CD Pipeline
14. Maintainers
15. Third Party Packages
16. Legal

Project Structure

Folder Structure

schemachange expects a directory structure like the following to exist:

(project_root)
|
|-- folder_1
    |-- V1.1.1__first_change.sql
    |-- V1.1.2__second_change.sql
    |-- V1.1.3__deploy_dbt_project.cli.yml
    |-- R__sp_add_sales.sql
    |-- R__fn_get_timezone.sql
|-- folder_2
    |-- folder_3
        |-- V1.1.4__third_change.sql
        |-- R__fn_sort_ascii.sql
        |-- A__run_cleanup.cli.yml

The schemachange folder structure is very flexible. The project_root folder is specified with the -f, --schemachange-root-folder, or --root-folder argument. schemachange only pays attention to the filenames, not the paths. Therefore, under the project_root folder you are free to arrange the change scripts any way you see fit. You can have as many subfolders (and nested subfolders) as you would like.

Change Scripts

Versioned Script Naming

Versioned change scripts follow a similar naming convention to that used by Flyway Versioned Migrations. The script name must follow this pattern (image taken from Flyway docs):

With the following rules for each part of the filename:

• Prefix: The letter 'V' for versioned change
• Version: A unique version number with dots or underscores separating as many number parts as you like
• Separator: __ (two underscores)
• Description: An arbitrary description with words separated by underscores or spaces (can not include two underscores)
• Suffix: .sql or .sql.jinja (case-insensitive: .SQL, .Sql, .JINJA also work)

For example, a script name that follows this convention is: V1.1.1__first_change.sql. As with Flyway, the unique version string is very flexible. You just need to be consistent and always use the same convention, like 3 sets of numbers separated by periods. Here are a few valid version strings:

• 1.1
• 1_1
• 1.2.3
• 1_2_3

Every script within a database folder must have a unique version number. schemachange will check for duplicate version numbers and throw an error if it finds any. This helps to ensure that developers who are working in parallel don't accidentally (re-)use the same version number.

Repeatable Script Naming

Repeatable change scripts follow a similar naming convention to that used by Flyway Versioned Migrations. The script name must follow this pattern (image taken from Flyway docs:

e.g:

• R__sp_add_sales.sql
• R__fn_get_timezone.sql
• R__fn_sort_ascii.sql

All repeatable change scripts are applied each time the utility is run, if there is a change in the file. Repeatable scripts could be used for maintaining code that always needs to be applied in its entirety. e.g. stored procedures, functions and view definitions etc.

Just like Flyway, within a single migration run, repeatable scripts are always applied after all pending versioned scripts have been executed. Repeatable scripts are applied in alphabetical order of their description.

Always Script Naming

Always change scripts are executed with every run of schemachange. This is an addition to the implementation of Flyway Versioned Migrations. The script name must follow this pattern:

A__Some_description.sql

e.g.

• A__add_user.sql
• A__assign_roles.sql

This type of change script is useful for an environment set up after cloning. Always scripts are applied always last.

CLI Migration Scripts

CLI migration scripts allow you to execute command-line tools as part of your deployment process. This is useful for deploying complex objects in Snowflake that go beyond SQL (such as a dbt Project or Snowpark object) and would require the use of the Snowflake CLI.

CLI migration scripts use a YAML format with the .cli.yml extension (or .cli.yml.jinja for Jinja templating). They follow the same naming conventions as SQL scripts:

• V1.0.0__deploy_dbt_project.cli.yml - Versioned CLI script
• R__refresh_snowpark_function.cli.yml - Repeatable CLI script
• A__cleanup.cli.yml - Always CLI script

YAML Schema

CLI migration scripts define a list of steps to execute:

steps:
  - cli: snow
    command: dbt
    args:
      - "deploy"
      - "--connection"
      - "myconnection"
    working_dir: ./my_dbt_project
    description: "Deploy the Snowflake dbt Project"

  - cli: snow
    command: snowpark
    args:
      - "deploy"
      - "--connection"
      - "myconnection"
    working_dir: ./my_snowpark_function
    description: "Deploy the Snowpark function"

Step Fields

Field	Required	Description
cli	Yes	CLI tool to execute. Currently only snow (Snowflake CLI) is supported.
command	Yes	The command to run (e.g., app, sql, connection).
args	No	List of arguments to pass to the command. Each argument must be a separate list item.
working_dir	No	Working directory for command execution. Resolved relative to root-folder. Defaults to root-folder.
env	No	Environment variables to set for the command (key-value pairs).
description	No	Human-readable description of what this step does.

Important Notes

Arguments must be separate list items: When specifying args, each argument must be its own list item. For example:

# Correct
args:
  - "--connection"
  - "myconnection"

# Incorrect - will not work
args:
  - "--connection myconnection"

Supported CLI tools: Currently only the Snowflake CLI (snow) is supported. The tool must be installed and available in your PATH.

Jinja templating: CLI migration scripts support Jinja templating. Use the .cli.yml.jinja extension and reference variables the same way as in SQL scripts:

steps:
  - cli: snow
    command: sql
    args:
      - "--query"
      - "USE DATABASE {{ database_name }}"
    description: "Switch to {{ environment }} database"

Change history tracking: CLI migration scripts are tracked in the change history table just like SQL scripts. The SCRIPT column will contain the full filename including the .cli.yml extension.

Script Requirements

schemachange is designed to be very lightweight and not impose too many limitations. Each change script can have any number of SQL statements within it and must supply the necessary context, like database and schema names. The context can be supplied by using an explicit USE <DATABASE> command or by naming all objects with a three-part name (<database name>.<schema name>.<object name>). schemachange will simply run the contents of each script against the target Snowflake account, in the correct order. After each script, Schemachange will execute "reset" the context ( role, warehouse, database, schema) to the values used to configure the connector.

Using Variables in Scripts

schemachange supports the jinja engine for a variable replacement strategy. One important use of variables is to support multiple environments (dev, test, prod) in a single Snowflake account by dynamically changing th