Schemachange: A Database Change Management tool for Snowflake

Warnings

• breaking Checksum drift for scripts ending with a semicolon when upgrading from v4.3.0/v4.3.1 to v4.3.2. A regression in v4.3.0 accidentally removed trailing semicolon stripping, causing checksum mismatches.
  Fix: Upgrade directly to v4.3.2. If coming from 4.3.0/4.3.1, expect a one-time 'checksum has drifted' warning for affected V-scripts or potential re-execution for R-scripts.
• breaking Minimum `snowflake-connector-python` version was bumped to `>=3.0.0` in v4.3.0, dropping support for `snowflake-connector-python` 2.x.
  Fix: Ensure `snowflake-connector-python` is updated to a version 3.x or higher before upgrading to schemachange v4.3.0 or later.
• gotcha Schemachange, by default, will not create the change history table and will fail if it doesn't exist. It also won't create the database for this table.
  Fix: Either manually create the `METADATA.SCHEMACHANGE.CHANGE_HISTORY` table (or your configured table) in Snowflake, or run `schemachange deploy` with the `--create-change-history-table` flag, or set `create-change-history-table: true` in your `schemachange-config.yml`. Ensure the database for the history table already exists.
• gotcha New CLI authentication parameters (`--snowflake-authenticator`, `--snowflake-private-key-file`, `--snowflake-token-file-path`) were added in v4.1.0, but `--snowflake-private-key-file-pwd` (passphrase) is *intentionally not supported* via CLI for security reasons.
  Fix: For sensitive credentials like private key passphrases, use environment variables (e.g., `SNOWFLAKE_PRIVATE_KEY_PASSPHRASE`) or define them in a `connections.toml` file to avoid exposure in process lists or shell history. Generic `SNOWFLAKE_*` environment variables are also passed through.
• gotcha Out-of-Order Execution, introduced in v4.3.0, changes how versioned scripts are applied. If enabled, scripts can be applied even if their version is older than the maximum applied version.
  Fix: Understand the implications of `--out-of-order` (or `SCHEMACHANGE_OUT_OF_ORDER=true` / `out-of-order: true` in config) for your deployment strategy, especially in parallel development workflows. It is recommended to use timestamp-based versioning to minimize collisions.
• deprecated The `--verbose` flag for command-line output was deprecated.
  Fix: While still functional, consider using alternative logging configurations if precise control over verbosity is needed.
• deprecated Parameters `private_key_path` and `private_key_passphrase` are slated for removal in the upcoming v5.0.0 major release.
  Fix: Plan to migrate to the newer `--snowflake-private-key-file` and environment variables for passphrases (`SNOWFLAKE_PRIVATE_KEY_PASSPHRASE`) or `connections.toml` configurations.

Install

• pip install schemachange Install latest version

Imports

• schemachange CLI

  import subprocess
  import os
  
  # Most common usage is via CLI
  subprocess.run(["schemachange", "deploy", "-f", "./migrations"], env=os.environ)

  Schemachange is primarily a Command Line Interface (CLI) tool. Direct programmatic imports for deploying migrations are less common for typical quickstart scenarios; users generally interact with it by executing `schemachange` commands via `subprocess` or directly in a shell.

Quickstart

This quickstart demonstrates how to set up a basic `schemachange` project and run a deployment using Python's `subprocess` module to execute the `schemachange` CLI. It creates a dummy `migrations` folder and an initial SQL script, then attempts to deploy it to a Snowflake instance using credentials provided via environment variables. The `--create-change-history-table` flag is included to automatically create the change history table if it doesn't exist. Ensure your Snowflake account, user, password, role, warehouse, and database environment variables are correctly set for authentication.

import os
import subprocess

# Create a dummy migrations folder and script for demonstration
if not os.path.exists('migrations'):
    os.makedirs('migrations')

with open('migrations/V1.0.0__initial_setup.sql', 'w') as f:
    f.write("CREATE SCHEMA IF NOT EXISTS MY_APP;\n")
    f.write("CREATE TABLE IF NOT EXISTS MY_APP.CUSTOMERS (ID INTEGER, NAME VARCHAR(100));")

# Set environment variables for Snowflake connection (replace with your actual details)
# For production, use secure methods like secrets management or connections.toml
# NOTE: For quickstart, ensure these are actual values or use a dummy Snowflake connection if possible
env = os.environ.copy()
env['SNOWFLAKE_ACCOUNT'] = os.environ.get('SNOWFLAKE_ACCOUNT', 'your_account_identifier')
env['SNOWFLAKE_USER'] = os.environ.get('SNOWFLAKE_USER', 'your_user')
env['SNOWFLAKE_PASSWORD'] = os.environ.get('SNOWFLAKE_PASSWORD', 'your_password')
env['SNOWFLAKE_ROLE'] = os.environ.get('SNOWFLAKE_ROLE', 'SYSADMIN')
env['SNOWFLAKE_WAREHOUSE'] = os.environ.get('SNOWFLAKE_WAREHOUSE', 'COMPUTE_WH')
env['SNOWFLAKE_DATABASE'] = os.environ.get('SNOWFLAKE_DATABASE', 'DEMO_DB')
env['SCHEMACHANGE_CHANGE_HISTORY_TABLE'] = os.environ.get('SCHEMACHANGE_CHANGE_HISTORY_TABLE', 'DEMO_DB.SCHEMACHANGE.CHANGE_HISTORY')

print("Running schemachange deploy...")
try:
    result = subprocess.run(
        [
            "schemachange",
            "deploy",
            "-f", "./migrations",
            "--create-change-history-table",
            "--verbose"
        ],
        env=env, # Pass environment variables to the subprocess
        check=True, # Raise an exception for non-zero exit codes
        capture_output=True, # Capture stdout and stderr
        text=True # Decode stdout/stderr as text
    )
    print("Schemachange deployment successful.")
    print("STDOUT:", result.stdout)
    if result.stderr:
        print("STDERR:", result.stderr)
except subprocess.CalledProcessError as e:
    print(f"Schemachange deployment failed with error: {e}")
    print("STDOUT:", e.stdout)
    print("STDERR:", e.stderr)
except FileNotFoundError:
    print("Error: 'schemachange' command not found. Is schemachange installed and in your PATH?")