schemachange 4.3.2
A Database Change Management tool for Snowflake
License: Apache-2.0
Requires Python: >=3.10

# schemachange

<img src="https://github.com/user-attachments/assets/8bc170c9-4171-48c7-812c-6d76c07ee364" alt="schemachange" title="schemachange logo" width="600" />

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

[![pytest](https://github.com/Snowflake-Labs/schemachange/actions/workflows/master-pytest.yml/badge.svg)](https://github.com/Snowflake-Labs/schemachange/actions/workflows/master-pytest.yml)
[![PyPI](https://img.shields.io/pypi/v/schemachange.svg)](https://pypi.org/project/schemachange)

## Overview

schemachange is a simple python based tool to manage all of your [Snowflake](https://www.snowflake.com/) objects. It
follows an Imperative-style approach to Database Change Management (DCM) and was inspired by
the [Flyway database migration tool](https://www.red-gate.com/products/flyway/community/). 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](https://www.snowflake.com/blog/embracing-agile-software-delivery-and-devops-with-snowflake/)
blog post.

For the complete list of changes made to schemachange check out the [CHANGELOG](CHANGELOG.md).

To learn more about making a contribution to schemachange, please see our [Contributing guide](.github/CONTRIBUTING.md).

**For maintainers:** See [docs/maintainers](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
```bash
pip install schemachange
```

### 2. Create your first migration script
```bash
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)**
```bash
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)**
```bash
# 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)**
```bash
# Create ~/.snowflake/connections.toml with your credentials
schemachange deploy -f migrations -C my_connection
```

### 4. Verify your deployment
```bash
# 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](#change-scripts) - Understand versioned, repeatable, and always scripts
- **Configure properly:** [Configuration](#configuration) - Choose the right config method for your environment
- **Secure your setup:** [Authentication](#authentication) - Use JWT, PATs, or SSO instead of passwords
- **CI/CD integration:** [Using in a CI/CD Pipeline](#using-in-a-cicd-pipeline) - Automate your deployments

## Table of Contents

1. [Overview](#overview)
1. [Quick Start](#quick-start)
1. [Project Structure](#project-structure)
    1. [Folder Structure](#folder-structure)
1. [Change Scripts](#change-scripts)
    1. [Versioned Script Naming](#versioned-script-naming)
    1. [Repeatable Script Naming](#repeatable-script-naming)
    1. [Always Script Naming](#always-script-naming)
    1. [CLI Migration Scripts](#cli-migration-scripts)
    1. [Script Requirements](#script-requirements)
    1. [Using Variables in Scripts](#using-variables-in-scripts)
        1. [Secrets filtering](#secrets-filtering)
    1. [Jinja templating engine](#jinja-templating-engine)
    1. [Gotchas](#gotchas)
1. [Change History Table](#change-history-table)
1. [Authentication](#authentication)
    1. [Password Authentication](#password-authentication)
    1. [External OAuth Authentication](#external-oauth-authentication)
    1. [External Browser Authentication](#external-browser-authentication)
    1. [Okta Authentication](#okta-authentication)
    1. [Private Key Authentication](#private-key-authentication)
1. [Configuration](#configuration)
    1. [connections.toml File](#connectionstoml-file)
    1. [YAML Config File](#yaml-config-file)
        1. [Yaml Jinja support](#yaml-jinja-support)
    1. [Environment Variables](#environment-variables)
    1. [Configuration Priority](#configuration-priority)
    1. [Account Identifier Format](#account-identifier-format)
    1. [Required Snowflake Privileges](#required-snowflake-privileges)
1. [Deployment Scenarios](#deployment-scenarios)
    1. [Out Of Order](#out-of-order)
1. [Upgrading to 4.1.0](#upgrading-to-410)
1. [Commands](#commands)
    1. [deploy](#deploy)
    1. [render](#render)
    1. [verify](#verify)
1. [Troubleshooting](#troubleshooting)
1. [Running schemachange](#running-schemachange)
    1. [Prerequisites](#prerequisites)
    1. [Supported Python Versions](#supported-python-versions)
    1. [Running the Script](#running-the-script)
1. [Integrating With DevOps](#integrating-with-devops)
    1. [Sample DevOps Process Flow](#sample-devops-process-flow)
    1. [Using in a CI/CD Pipeline](#using-in-a-cicd-pipeline)
1. [Maintainers](#maintainers)
1. [Third Party Packages](#third-party-packages)
1. [Legal](#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](https://documentation.red-gate.com/fd/versioned-migrations-273973333.html). The script name
must follow this pattern (image taken
from [Flyway docs](https://documentation.red-gate.com/fd/versioned-migrations-273973333.html)):

<img src="https://github.com/user-attachments/assets/a71297d9-4a3c-4d30-82d3-c634be88fe54" alt="Flyway naming conventions" title="Flyway naming conventions" width="300" />

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](https://documentation.red-gate.com/fd/repeatable-migrations-273973335.html). The
script name must follow this pattern (image taken
from [Flyway docs](https://documentation.red-gate.com/fd/repeatable-migrations-273973335.html):

<img src="https://github.com/user-attachments/assets/06abd883-58b7-42d5-97b2-581158d8b121" alt="Flyway naming conventions" title="Flyway naming conventions" width="300" />

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. stores
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](https://documentation.red-gate.com/fd/versioned-migrations-273973333.html).
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:

```yaml
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:

```yaml
# 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:

```yaml
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 the database name during
deployment. To use a variable in a change script, use this syntax anywhere in the script: `{{ variable1 }}`.

To pass variables to schemachange, check out the [Configuration](#configuration) section below. You can either use
the `--vars` command line parameter or the YAML config file `schemachange-config.yml`. For the command line version you
can pass variables like this: `--vars '{"variable1": "value", "variable2": "value2"}'`. This parameter accepts a flat
JSON object formatted as a string.

> *Nested objects and arrays don't make sense at this point and aren't supported.*

schemachange will replace any variable placeholders before running your change script code and will throw an error if it
finds any variable placeholders that haven't been replaced.

#### Secrets filtering

While many CI/CD tools already have the capability to filter secrets, it is best that any tool also does not output
secrets to the console or logs. Schemachange implements secrets filtering in a number of areas to ensure secrets are not
writen to the console or logs. The only exception is the `render` command which will display secrets.

A secret is just a standard variable that has been tagged as a secret. This is determined using a naming convention and
either of the following will tag a variable as a secret:

1. The variable name has the word `secret` in it.
   ```yaml
      config-version: 1
      vars:
         bucket_name: S3://......  # not a secret
         secret_key: 567576D8E  # a secret
   ```
2. The variable is a child of a key named `secrets`.
   ```yaml
      config-version: 1
      vars:
      secrets:
         my_key: 567576D8E # a secret
      aws:
         bucket_name: S3://......  # not a secret
         secrets:
            encryption_key: FGDSUUEHDHJK # a secret
            us_east_1:
               encryption_key: sdsdsd # a secret
   ```

### Jinja templating engine

schemachange uses the Jinja templating engine internally and
supports: [expressions](https://jinja.palletsprojects.com/en/3.0.x/templates/#expressions), [macros](https://jinja.palletsprojects.com/en/3.0.x/templates/#macros), [includes](https://jinja.palletsprojects.com/en/3.0.x/templates/#include)
and [template inheritance](https://jinja.palletsprojects.com/en/3.0.x/templates/#template-inheritance).

These files can be stored in the root-folder but schemachange also provides a separate modules
folder `--modules-folder`. This allows common logic to be stored outside of the main changes scripts.
The [demo/citibike_demo_jinja](demo/citibike_demo_jinja) has a simple example that demonstrates this.

schemachange uses Jinja's [`PrefixLoader`](https://jinja.palletsprojects.com/en/stable/api/#jinja2.PrefixLoader), so
regardless of the `--modules-folder` that's used, the file paths (such as those passed to [`include`](https://jinja.palletsprojects.com/en/stable/templates/#include))
should be prefixed with `modules/`.

The Jinja auto-escaping feature is disabled in schemachange, this feature in Jinja is currently designed for where the
output language is HTML/XML. So if you are using schemachange with untrusted inputs you will need to handle this within
your change scripts.

### Common Pitfalls

Within change scripts, be aware of:

- **Tasks with multi-statement bodies**: Use `EXECUTE IMMEDIATE $$...$$;` or call a stored procedure. Single-statement tasks don't need `BEGIN...END`. Note: `$$` delimiters are NOT valid directly in task definitions.
- **UTF-8 BOM characters**: Automatically stripped by schemachange (fixed in 4.2.0)
- **Trailing comments**: When comments appear on new lines after the final `;`, schemachange automatically appends `SELECT 1;` to prevent "Empty SQL Statement" errors
- **Snowflake Scripting blocks**: The `execute_string()` method splits on semicolons client-side, breaking `BEGIN...END` blocks. See [TROUBLESHOOTING.md](TROUBLESHOOTING.md) for solutions.

For detailed troubleshooting and solutions, see [TROUBLESHOOTING.md](TROUBLESHOOTING.md)

## Change History Table

schemachange records all applied changes scripts to the change history table. By default, schemachange will attempt to
log all activities to the `METADATA.SCHEMACHANGE.CHANGE_HISTORY` table. The name and location of the change history
table can be overriden via a command line argument (`-c`, `--schemachange-change-history-table`, or `--change-history-table`)
or the `schemachange-config.yml` file (`change-history-table`). The value passed to the parameter can have a one, two, or
three part name (e.g. "TABLE_NAME", or "SCHEMA_NAME.TABLE_NAME", or "DATABASE_NAME.SCHEMA_NAME.TABLE_NAME"). This can be
used to support multiple environments (dev, test, prod) or multiple subject areas within the same Snowflake account.

By default, schemachange will not try to create the change history table, and it will fail if the table does not exist.
This behavior can be altered by passing in the `--schemachange-create-change-history-table` or `--create-change-history-table`
argument or adding `create-change-history-table: true` to the `schemachange-config.yml` file.

**For first-time deployments**, simply add the `--create-change-history-table` flag:
```bash
schemachange deploy --create-change-history-table
```

If the change history table already exists, it will be used. If it doesn't exist, it will be created. This works correctly for:
- First-time production deployments
- Parallel CI/CD test jobs (each creating their own schema)
- Ephemeral preview environments
- Developer local testing

**Note**: Even with the `--create-change-history-table` parameter, schemachange will not attempt to create the database for the change history
table. That must be created before running schemachange.

The structure of the `CHANGE_HISTORY` table is as follows:

| Column Name    | Type          | Example                            |
|----------------|---------------|------------------------------------|
| VERSION        | VARCHAR       | 1.1.1                              |
| DESCRIPTION    | VARCHAR       | First change                       |
| SCRIPT         | VARCHAR       | V1.1.1__first_change.sql           |
| SCRIPT_TYPE    | VARCHAR       | V                                  |
| CHECKSUM       | VARCHAR       | 38e5ba03b1a6d2...                  |
| EXECUTION_TIME | NUMBER        | 4                                  |
| STATUS         | VARCHAR       | Success                            |
| INSTALLED_BY   | VARCHAR       | SNOWFLAKE_USER                     |
| INSTALLED_ON   | TIMESTAMP_LTZ | 2020-03-17 12:54:33.056 -0700      |

A new row will be added to this table every time a change script has been applied to the database. Both SQL scripts (`.sql`) and CLI migration scripts (`.cli.yml`) are tracked in this table. schemachange will use
this table to identify which changes have been applied to the database and will not apply the same version more than
once.

Here is the current schema DDL for the change history table (found in the [schemachange/cli.py](schemachange/cli.py)
script), in case you choose to create it manually and not use the `--create-change-history-table` parameter:

```sql
CREATE TABLE IF NOT EXISTS SCHEMACHANGE.CHANGE_HISTORY
(
    VERSION        VARCHAR,
    DESCRIPTION    VARCHAR,
    SCRIPT         VARCHAR,
    SCRIPT_TYPE    VARCHAR,
    CHECKSUM       VARCHAR,
    EXECUTION_TIME NUMBER,
    STATUS         VARCHAR,
    INSTALLED_BY   VARCHAR,
    INSTALLED_ON   TIMESTAMP_LTZ
)
```

## Authentication

Schemachange supports the many of the authentication methods supported by
the [Snowflake Python Connector](https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connect).
The authenticator can be set by setting an `authenticator` in the [connections.toml](#connectionstoml-file) file

The following authenticators are supported:

- `snowflake`: [Password or PAT](#password-authentication)
- `oauth`: [External OAuth](#external-oauth-authentication)
- `externalbrowser`: [Browser-based SSO](#external-browser-authentication)
- `https://<okta_account_name>.okta.com`: [Okta SSO](#okta-authentication)
- `snowflake_jwt`: [Private Key](#private-key-authentication)

If an authenticator is unsupported, an exception will be raised.

**Security Note:** For detailed security guidance on credential management, authentication best practices, and preventing credential leakage, please see [SECURITY.md](SECURITY.md).

### Password Authentication

⚠️ **SNOWFLAKE AUTHENTICATION REQUIREMENTS:**
- **Service users:** Password authentication is **NOT SUPPORTED**. Must use PAT, Key Pair (JWT), OAuth, or WIF.
- **Human users (CLI/CI/CD):** **PREFERRED** to use PAT, Key Pair (JWT), or OAuth over password+MFA (avoids interactive prompts).
- **Human users (Interactive):** Password+MFA is acceptable but PAT or Key Pair is preferred.

**Recommended Authentication Methods (in order of preference):**
1. **[Private Key (JWT)](#private-key-authentication)** - Best for service accounts and automation
2. **[Programmatic Access Token (PAT)](#password-authentication-with-programmatic-access-token-pat)** - Best for human users in CLI/CI/CD
3. **[External Browser/SSO](#external-browser-authentication)** - Best for interactive use
4. **[OAuth](#external-oauth-authentication)** - For OAuth-enabled workflows
5. **Password+MFA** - Allowed for human users but not recommended for automation (requires interactive MFA prompts)

#### Password Authentication with Programmatic Access Token (PAT)

Password authentication is the default authenticator (or set `authenticator: snowflake` explicitly).

**For CLI/CI/CD automation**, it is **strongly recommended** to use a **Programmatic Access Token (PAT)** instead of your regular password+MFA.

**What is a PAT?**
A Programmatic Access Token is a long-lived token that allows automated tools to authenticate without MFA prompts. It's Snowflake's recommended approach for automation and is required for service accounts.

**How to use a PAT:**

```bash
export SNOWFLAKE_PASSWORD="<your_pat_token>"  # PAT, not your regular password
schemachange deploy
```

**How to generate a PAT:**
1. Log into Snowflake Web UI
2. Go to your user preferences
3. Generate a new Programmatic Access Token
4. Copy the token and use it in place of your password

**For detailed PAT setup and best practices, see:**
- [Snowflake PAT Documentation](https://docs.snowflake.com/en/user-guide/ui-snowsight-profile#generate-a-programmatic-access-token)
- [SECURITY.md](SECURITY.md) for comprehensive authentication guidance

#### Password+MFA Authentication (Not Recommended for Automation)

⚠️ **For Human Users Only:** Password+MFA authentication is allowed for human users in interactive sessions but is **not recommended** for CLI/CI/CD automation due to interactive MFA prompts.

```bash
export SNOWFLAKE_PASSWORD="your_password"  # Will prompt for MFA code
schemachange deploy  # ⚠️ Requires manual MFA input (blocks automation)
```

**For Automation, Use Instead:**
- **Service accounts:** PAT or Private Key (JWT) - password not supported
- **Human accounts:** PAT or Private Key (JWT) - avoids MFA prompts

#### Legacy Password-Only (No MFA) - Deprecated

❌ **NOT SUPPORTED:** Password-only authentication (without MFA) is deprecated by Snowflake and will not work on modern accounts.

### External OAuth Authentication

External OAuth authentication can be selected by supplying `oauth` as your authenticator. A `token_file_path` must be
supplied in the [connections.toml](#connectionstoml-file) file

**Schemachange no longer supports the `--oauth-config` option.**  Prior to the 4.0 release, this library supported
supplying an `--oauth-config` that would be used to fetch an OAuth token via the `requests` library. This required
Schemachange to keep track of connection arguments that could otherwise be passed directly to the Snowflake Python
connector. Maintaining this logic in Schemachange added unnecessary complication to the repo and prevented access to
recent connector parameterization features offered by the Snowflake connector.

### External Browser Authentication

External browser authentication can be selected by supplying `externalbrowser` as your authenticator. The client will be
prompted to authenticate in a browser that pops up. Refer to
the [documentation](https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-use.html#setting-up-browser-based-sso)
to cache the token to minimize the number of times the browser pops up to authenticate the user.

### Okta Authentication

External browser authentication can be selected by supplying your Okta endpoint as your authenticator (e.g.
`https://<org_name>.okta.com`). For clients that do not have a browser, can use the popular SaaS Idp option to connect
via Okta. A `password` must be supplied in the [connections.toml](#connectionstoml-file) file

_** NOTE**: Please disable Okta MFA for the user who uses Native SSO authentication with client drivers. Please consult
your Okta administrator for more information._

### Private Key Authentication

Private key authentication can be selected by supplying `snowflake_jwt` as your authenticator. The filepath to a
Snowflake user-encrypted private key must be supplied as `private_key_file` in the [connections.toml](#connectionstoml-file)
file. If the private key file is password protected, supply the password as `private_key_file_pwd` in
the [connections.toml](#connectionstoml-file) file. If the variable is not set, the Snowflake Python connector will
assume the private key is not encrypted.

**Parameter Name Migration:**

For better alignment with the Snowflake Python Connector, schemachange now supports both old and new parameter names:

| Configuration Source | Old Name (Deprecated) | New Name (Recommended) |
|---------------------|----------------------|------------------------|
| **Private Key Path** | | |
| CLI | `--snowflake-private-key-path` | `--snowflake-private-key-file` |
| Environment Variable | `SNOWFLAKE_PRIVATE_KEY_PATH` | `SNOWFLAKE_PRIVATE_KEY_FILE` |
| connections.toml | `private_key_path` | `private_key_file` |
| YAML Config | `snowflake-private-key-path` | `snowflake-private-key-file` |
| **Private Key Passphrase** | | |
| Environment Variable | `SNOWFLAKE_PRIVATE_KEY_PASSPHRASE` | `SNOWFLAKE_PRIVATE_KEY_FILE_PWD` |
| connections.toml | `private_key_passphrase` | `private_key_file_pwd` |
| YAML Config | `snowflake-private-key-passphrase` | `snowflake-private-key-file-pwd` |

**Note:** Passphrases are **not** supported via CLI for security reasons (they would be visible in process lists and shell history).

The old parameter names continue to work but show deprecation warnings. Please migrate to the new names to match the Snowflake Python Connector's parameter naming convention.

## Configuration

schemachange supports multiple configuration methods for both Snowflake connection parameters and schemachange-specific settings. Configuration can be supplied through (in order of priority):

1. **Command Line Arguments** - Explicit flags passed to the CLI
2. **Environment Variables** - `SNOWFLAKE_*` prefixed variables (as of v4.1.0)
3. **YAML Config File** - `schemachange-config.yml` configuration file
4. **connections.toml File** - Snowflake Python Connector's connection file (as of v4.0)

Higher priority sources override lower priority sources, allowing flexible configuration management across different environments.

**Note:** As of 4.0, `vars` provided via command-line argument will be merged with vars provided via YAML config.
Previously, one overwrote the other completely.

Please see [Usage Notes for the account Parameter (for the connect Method)](https://docs.snowflake.com/en/user-guide/python-connector-api.html#label-account-format-info) for more details on how to structure the account name.

### connections.toml File

#### What is connections.toml?

A standard [Snowflake configuration file](https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connect#connecting-using-the-connections-toml-file) for storing connection parameters and credentials. Think of it as your personal Snowflake address book.

#### When should you use it?

**✅ Great for:**
- **Local development** - Juggle multiple Snowflake accounts (dev, staging, prod) without juggling credentials
- **Team consistency** - Share connection configurations (without secrets) across your team
- **Secure storage** - Keep credentials in one secure file with proper permissions (`chmod 600`)

**❌ Skip it for:**
- **CI/CD pipelines** - Use environment variables instead (easier secret management)
- **Production deployments** - Service accounts should use ENV vars or vault systems
- **Quick experiments** - Just use CLI arguments

#### How does schemachange find connections.toml?

**Important:** schemachange only uses `connections.toml` when you explicitly opt-in by specifying **at least one** of:
- A `connection-name` (which profile to use)
- A `connections-file-path` (where to find the file)

If you specify **neither**, `connections.toml` is skipped entirely and parameters come from CLI > ENV > YAML only.

**When you do use it:**

| What you specify | How schemachange finds it |
|-----------------|---------------------------|
| Only `--connection-name dev` | Looks in `~/.snowflake/connections.toml` for `[dev]` profile |
| Only `--connections-file-path ./team.toml` | Looks in `./team.toml` for `[default]` profile |
| Both specified | Uses exactly what you specified |

**File path precedence** (highest to lowest):
1. CLI: `--connections-file-path`
2. ENV: `SCHEMACHANGE_CONNECTIONS_FILE_PATH` (or legacy `SNOWFLAKE_CONNECTIONS_FILE_PATH`)
3. YAML: `connections-file-path`
4. Default: `$SNOWFLAKE_HOME/.snowflake/connections.toml` (where `$SNOWFLAKE_HOME` defaults to your home directory)

**Connection name precedence** (highest to lowest):
1. CLI: `--connection-name` or `-C`
2. ENV: `SCHEMACHANGE_CONNECTION_NAME` (or legacy `SNOWFLAKE_DEFAULT_CONNECTION_NAME`)
3. YAML: `connection-name`
4. Default: `default`

**💡 Pro tip:** Paths support tilde expansion (`~`), so `~/configs/snowflake.toml` works everywhere.

#### Example: Create a connections.toml file

Create `~/.snowflake/connections.toml`:

```toml
# Development environment
[dev]
account = "myorg-dev"
user = "developer"
role = "DEV_ROLE"
warehouse = "DEV_WH"
database = "DEV_DB"

# Production environment (using JWT authentication)
[prod]
account = "myorg-prod"
user = "deploy_service"
authenticator = "snowflake_jwt"
private_key_file = "~/.ssh/snowflake_prod.p8"  # Recommended parameter name (matches Snowflake connector)
private_key_file_pwd = "my_secure_passphrase"   # Recommended parameter name (matches Snowflake connector)
role = "DEPLOY_ROLE"
warehouse = "PROD_WH"
database = "PROD_DB"

# NOTE: private_key_path is deprecated but still supported for backwards compatibility
# Please migrate to private_key_file to match Snowflake Python Connector naming

# Optional: Set session parameters for this connection
[prod.parameters]
QUERY_TAG = "my_app_prod"
QUOTED_IDENTIFIERS_IGNORE_CASE = false
```

**Secure your file:**
```bash
chmod 600 ~/.snowflake/connections.toml
```

**Use it:**
```bash
# Deploy to dev
schemachange deploy -f migrations -C dev

# Deploy to prod
schemachange deploy -f migrations -C prod
```

#### Session Parameters in connections.toml

Session parameters let you control Snowflake session behavior (like `QUERY_TAG`, date formats, query timeouts, etc.).

**Quick example:**
```toml
[my_connection.parameters]
QUERY_TAG = "my_app"
TIMESTAMP_OUTPUT_FORMAT = "YYYY-MM-DD HH24:MI:SS"
```

<details>
<summary>📘 <strong>Advanced: How session parameters merge across all config sources</strong></summary>

You can set session parameters in multiple places, and schemachange intelligently merges them:

**Sources** (in priority order):
1. **CLI**: `--snowflake-session-parameters '{"PARAM": "value"}'`
2. **ENV**: `SNOWFLAKE_SESSION_PARAMETERS='{"PARAM": "value"}'`
3. **YAML v2**: Under `snowflake.session-parameters`
4. **connections.toml**: Under `[connection_name.parameters]`

**How merging works:**
- Higher priority sources override lower ones **per parameter**
- Only explicitly-set parameters from connections.toml are used (not Snowflake defaults)
- Parameters from all sources are combined and passed once to Snowflake (efficient!)

**Special case: QUERY_TAG appends instead of overriding:**

`QUERY_TAG` is special - values are **appended** with semicolons instead of replaced. This lets you track queries at multiple levels:

| Layer | Source | Value |
|-------|--------|-------|
| 🏠 Application | connections.toml | `"my_app"` |
| 🌍 Environment | CLI session params | `"deployment"` |
| 🎯 Run-specific | `--query-tag` | `"production"` |
| 🔧 Tool | schemachange (auto) | `"schemachange 4.1.0"` |
| **📊 Final** | **Snowflake sees** | **`"my_app;deployment;production;schemachange 4.1.0"`** |

**Example:**

```bash
# connections.toml has QUERY_TAG = "my_app"
export SNOWFLAKE_SESSION_PARAMETERS='{"QUERY_TAG": "ci_pipeline"}'
schemachange deploy -C prod --query-tag "release-v2.0"

# Snowflake query history shows:
# QUERY_TAG = "my_app;ci_pipeline;release-v2.0;schemachange 4.1.0"
```

This makes it easy to filter queries by application, environment, or specific deployment in Snowflake's query history!

</details>

### YAML Config File

By default, Schemachange expects the YAML config file to be named `schemachange-config.yml`, located in the current
working directory. The YAML file name can be overridden with the
`--config-file-name` [command-line argument](#commands). The folder can be overridden by using the
`--config-folder` [command-line argument](#commands)

schemachange supports two YAML configuration formats:

#### Config Version 2 (Recommended)

Config version 2 separates schemachange-specific parameters from Snowflake connector parameters into distinct sections, providing better organization and clarity:

```yaml
config-version: 2

schemachange:
  # The root folder for the database change scripts
  root-folder: './migrations'

  # The modules folder for jinja macros and templates to be used across multiple scripts
  modules-folder: './modules'

  # Override the default connections.toml file path
  connections-file-path: '~/.snowflake/connections.toml'

  # Override the default connections.toml connection name
  connection-name: 'my-connection'

  # Used to override the default name of the change history table (default: METADATA.SCHEMACHANGE.CHANGE_HISTORY)
  change-history-table: 'METADATA.SCHEMACHANGE.CHANGE_HISTORY'

  # Define values for variables to be replaced in change scripts
  vars:
    var1: 'value1'
    var2: 'value2'
    secrets:
      var3: 'value3' # This is considered a secret and will not be displayed in any output

  # Create the change history schema and table if they do not exist (default: false)
  create-change-history-table: true

  # Enable autocommit feature for DML commands (default: false)
  autocommit: false

  # Run schemachange in dry run mode (default: false)
  dry-run: false

  # A string to include in the QUERY_TAG that is attached to every SQL statement
  query-tag: 'my-project'

  # Log level: DEBUG, INFO, WARNING, ERROR, or CRITICAL (default: INFO)
  log-level: 'INFO'

  # Regex pattern for version number validation
  version-number-validation-regex: '^[0-9]+\.[0-9]+\.[0-9]+$'

  # Raise exception when versioned scripts are ignored (default: false)
  raise-exception-on-ignored-versioned-script: false

  # Allow out-of-order versioned script execution for parallel development (default: false)
  out-of-order: false

snowflake:
  # Snowflake connection parameters (these can also come from connections.toml or environment variables)
  account: 'myaccount.us-east-1.aws'
  user: 'my_user'
  role: 'MY_ROLE'
  warehouse: 'MY_WH'
  database: 'MY_DB'
  schema: 'MY_SCHEMA'

  # Authentication parameters (optional, based on auth method)
  authenticator: 'snowflake_jwt'  # snowflake, oauth, externalbrowser, snowflake_jwt, or okta URL
  private-key-path: '~/.ssh/snowflake_key.p8'

  # Additional Snowflake Python Connector parameters
  # Any valid connector parameter can be specified here
  client-session-keep-alive: true
  login-timeout: 60
  network-timeout: 120
```

**Benefits of Config Version 2:**
- Clear separation between schemachange config and Snowflake connector parameters
- All Snowflake Python Connector parameters are supported in the `snowflake` section
- Better organization and maintainability
- Forward-compatible with future schemachange releases

#### Config Version 1 (Legacy, Backward Compatible)

Config version 1 uses a flat structure. This format is still supported for backward compatibility:

```yaml
config-version: 1

# The root folder for the database change scripts
root-folder: '/path/to/folder'

# The modules folder for jinja macros and templates to be used across multiple scripts.
modules-folder: null

# Override the default connections.toml file path at snowflake.connector.constants.CONNECTIONS_FILE (OS specific)
connections-file-path: null

# Override the default connections.toml connection name. Other connection-related values will override these connection values.
connection-name: null

# Used to override the default name of the change history table (the default is METADATA.SCHEMACHANGE.CHANGE_HISTORY)
change-history-table: null

# Define values for the variables to replaced in change scripts. vars supplied via the command line will be merged into YAML-supplied vars
vars:
  var1: 'value1'
  var2: 'value2'
  secrets:
    var3: 'value3' # This is considered a secret and will not be displayed in any output

# Create the change history schema and table, if they do not exist (the default is False)
create-change-history-table: false

# Enable autocommit feature for DML commands (the default is False)
autocommit: false

# Display verbose debugging details during execution (