repository·master·Indexed 20 days ago
https://github.com/snowflake-labs/schemachangeA Python-based Database Change Management (DCM) tool for Snowflake designed for version control and CI/CD pipelines to enable Data DevOps. It supports managing Snowflake objects using imperative-style migrations, featuring multiple authentication methods including connections.toml, environment variables, JWT, External Browser (SSO), and OAuth tokens. Version 4.3.3 introduces structured YAML configuration (v2) and a prefixed CLI parameter naming convention for improved clarity.
Repeatable scripts are applied every time schemachange runs, provided the file content has changed. They are ideal for objects that should be redefined entirely, such as stored procedures, functions, or views.
Repeatable scripts are always executed after all pending versioned scripts and are applied in alphabetical order of their description.
Pattern: R__<Description>.<Suffix>
R__sp_add_sales.sql
R__fn_get_timezone.sqlVersioned scripts are executed once and tracked. They follow a naming convention inspired by Flyway. Each script in a database folder must have a unique version number.
Pattern: <Prefix><Version><Separator><Description>.<Suffix>
V.) or underscores (_) (e.g., 1.1, 1_2_3).__ (exactly two underscores).__)..sql or .sql.jinja (case-insensitive).V1.1.1__first_change.sqlAlways scripts are executed during every single run of schemachange. These are useful for environment setup tasks that must occur after cloning or initial deployment. Always scripts are applied last.
Pattern: A__<Description>.<Suffix>
A__add_user.sql
A__assign_roles.sqlBecause schemachange uses the Snowflake Python connector's execute_string() method, it splits SQL on semicolons (;) client-side. This breaks Snowflake Scripting blocks (like BEGIN...END in Tasks or Anonymous blocks) because the block is split into invalid fragments before reaching Snowflake.
Option 1: Single Statement (Best for simple tasks)
Remove the BEGIN...END wrapper if the task only executes one statement.
Option 2: EXECUTE IMMEDIATE with $$ (Best for multi-statement blocks)
Wrap your block in EXECUTE IMMEDIATE using dollar-quoted delimiters ($$). This makes the entire block appear as a single statement to schemachange.
CREATE OR REPLACE TASK my_task
WAREHOUSE = my_warehouse
SCHEDULE = '5 minutes'
AS
EXECUTE IMMEDIATE $$
BEGIN
START TRANSACTION;
DELETE FROM archive WHERE created_at < DATEADD(year, -1, CURRENT_DATE);
INSERT INTO archive SELECT * FROM staging;
TRUNCATE TABLE staging;
COMMIT;
END;
$$;Option 3: Call a Stored Procedure (Best for complex logic)
Encapsulate the logic in a stored procedure and have the task call it using CALL.
CREATE OR REPLACE TASK my_task
WAREHOUSE = my_warehouse
SCHEDULE = '5 minutes'
AS
EXECUTE IMMEDIATE $$
BEGIN
START TRANSACTION;
DELETE FROM archive WHERE created_at < DATEADD(year, -1, CURRENT_DATE);
INSERT INTO archive SELECT * FROM staging;
TRUNCATE TABLE staging;
COMMIT;
END;
$$;When you identify content in the experiments/ folder that is valuable enough to be committed to the repository, follow these best practices to ensure it meets project standards:
When configuring the account parameter, use one of the following formats. Do not include snowflakecomputing.com in the identifier.
<orgname>-<account_name> (e.g., myorg-myaccount).<account_locator>.<region>.<cloud> (e.g., xy12345.us-east-1.aws).You can find your identifier by running SELECT CURRENT_ACCOUNT_NAME(); in Snowflake.
The schemachange tool is flexible regarding folder organization. It only cares about the filenames, not the paths. You can organize scripts into nested subfolders under a specified root folder. The root folder is defined using the -f, --schemachange-root-folder, or --root-folder argument.
(project_root)
|-- folder_1
| |-- V1.1.1__first_change.sql
| |-- R__sp_add_sales.sql
|-- folder_2
| |-- folder_3
| |-- V1.1.4__third_change.sqlBy default, schemachange skips any versioned script with a version number less than or equal to the maximum version already applied. This prevents issues when multiple teams work on parallel feature branches where a lower-versioned script might be merged after a higher-versioned one.
To allow versioned scripts to be applied even if their version number is older than the maximum applied version, enable the out-of-order setting. This is highly recommended for CI/CD pipelines in shared environments.
Best Practices for Parallel Development:
V20260122143052__name.sql) to minimize collisions.# Using CLI flag
schemachange deploy --out-of-order
# Using Environment Variable
export SCHEMACHANGE_OUT_OF_ORDER=true
# Using YAML Config
out-of-order: trueWhen multiple configuration sources are provided, schemachange resolves values based on the following priority (highest to lowest):
Example: If a database is defined in connections.toml but a different database is passed via a CLI argument, the CLI argument will be used.
To prevent sensitive data from appearing in logs or consoles, schemachange automatically filters variables identified as 'secrets'. A variable is treated as a secret if:
secret.secrets in your configuration.Note: The render command is an exception and will display secrets.
# Example of secret identification via naming
vars:
bucket_name: S3://...... # Not a secret
secret_key: 567576D8E # Identified as a secret
# Example of secret identification via hierarchy
vars:
secrets:
my_key: 567576D8E # Identified as a secretschemachange supports authentication methods provided by the Snowflake Python Connector. You can set the authenticator in your connections.toml file.
Supported authenticators:
snowflake: Password or Programmatic Access Token (PAT)oauth: External OAuthexternalbrowser: Browser-based SSOhttps://<okta_account_name>.okta.com: Okta SSOsnowflake_jwt: Private Key (JWT)For security reasons, passwords and private key passphrases cannot be passed via CLI arguments. You must provide them using one of the following methods:
SNOWFLAKE_PASSWORD: Password or Programmatic Access Token (PAT).SNOWFLAKE_PRIVATE_KEY_FILE_PWD: Passphrase for encrypted private key files.snowflake.passwordsnowflake.private-key-file-pwdconnections.toml file:passwordprivate_key_file_pwd