Most Snowflake accounts still get changed by someone pasting SQL into a worksheet. It works until it doesn't: the staging schema drifts from production, a masking policy exists in one account but not the other, and nobody can say which version of a stored procedure is running. Snowflake now ships enough native DevOps machinery — Git integration, CREATE OR ALTER, declarative object files, the Snowflake CLI, and EXECUTE IMMEDIATE FROM — that you can run a real pull-request workflow without bolting on a third-party migration tool.
This tutorial builds a working pipeline: repo layout, a Git-connected Snowflake account, idempotent object definitions, a dev/prod promotion path, and a GitHub Actions job that deploys on merge.
The three ways to change an object, and when to use each
| Approach | What it looks like | Use it for |
|---|---|---|
| Imperative migrations | Numbered scripts: V003__add_column.sql | Data backfills, one-off fixes, anything not idempotent |
CREATE OR ALTER | CREATE OR ALTER TABLE orders (...) — Snowflake diffs current state against your definition | Tables, views, tasks, streams: the everyday objects |
| Declarative object files | A YAML/SQL definition of an object applied by the CLI or EXECUTE IMMEDIATE FROM | Whole-schema definitions you want to converge, not migrate |
The pragmatic mix most teams land on: CREATE OR ALTER for the bulk of the schema, a small migrations/ folder for the handful of changes that need data movement, and everything under version control.
CREATE OR ALTER in practice
CREATE OR ALTER TABLE analytics.orders (
order_id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
order_ts TIMESTAMP_NTZ NOT NULL,
amount DECIMAL(12,2),
status STRING,
CONSTRAINT pk_orders PRIMARY KEY (order_id)
);
Run it on an empty account and it creates the table. Run it again after adding a column to the file and Snowflake issues the equivalent ALTER. Run it unchanged and it is a no-op. That property — the same script is safe to run on every environment, every time — is what makes a repo the source of truth instead of a diary.
Limits worth knowing before you standardise on it: not every object type is supported, and not every change is expressible as an ALTER (narrowing a column type, dropping a clustering key on some object types, some constraint changes). Those raise an error rather than silently rebuilding, which is the behaviour you want — it tells you to write an explicit migration. Check the current supported-object list in the docs before assuming coverage.
Step 1: put the repo inside Snowflake
Snowflake can clone a Git repo into a stage-like object and execute files from it directly.
-- Secret with a repo access token (or use a secret with a PAT / deploy key)
CREATE OR REPLACE SECRET deploy_git_secret
TYPE = PASSWORD
USERNAME = 'ci-bot'
PASSWORD = 'ghp_xxxxxxxxxxxxxxxxxxxx';
CREATE OR REPLACE API INTEGRATION github_api
API_PROVIDER = GIT_HTTPS_API
API_ALLOWED_PREFIXES = ('https://github.com/acme-data')
ALLOWED_AUTHENTICATION_SECRETS = (deploy_git_secret)
ENABLED = TRUE;
CREATE OR REPLACE GIT REPOSITORY ops.public.warehouse_repo
API_INTEGRATION = github_api
GIT_CREDENTIALS = deploy_git_secret
ORIGIN = 'https://github.com/acme-data/snowflake-warehouse.git';
ALTER GIT REPOSITORY ops.public.warehouse_repo FETCH;
LS @ops.public.warehouse_repo/branches/main/;
Now any file in the repo is addressable:
EXECUTE IMMEDIATE FROM @ops.public.warehouse_repo/branches/main/deploy/deploy_all.sql
USING (env => 'DEV');
EXECUTE IMMEDIATE FROM runs a SQL script file, including Jinja-style templating with USING, which is how one script serves several environments.
Step 2: a repo layout that survives contact with reality
snowflake-warehouse/
deploy/
deploy_all.sql -- orchestrates the rest
schemas/
analytics/
tables/orders.sql -- CREATE OR ALTER TABLE
views/v_orders.sql -- CREATE OR REPLACE VIEW
tasks/refresh.sql
governance/
tags.sql
masking_policies.sql
migrations/
2026_03_01_backfill_status.sql
roles/
grants.sql
tests/
smoke.sql
deploy/deploy_all.sql is deliberately dumb:
EXECUTE IMMEDIATE $$
BEGIN
USE ROLE deployer;
USE WAREHOUSE deploy_wh;
USE DATABASE IDENTIFIER('{{ env }}_ANALYTICS');
EXECUTE IMMEDIATE FROM './schemas/analytics/tables/orders.sql';
EXECUTE IMMEDIATE FROM './schemas/analytics/views/v_orders.sql';
EXECUTE IMMEDIATE FROM './governance/masking_policies.sql';
EXECUTE IMMEDIATE FROM './roles/grants.sql';
RETURN 'deployed';
END;
$$;
Relative paths resolve inside the Git repository stage, so the same file works from any branch.
Step 3: environments without copy-paste
Two patterns, and you will probably use both:
- Database-per-environment:
DEV_ANALYTICS,STG_ANALYTICS,PRD_ANALYTICS, selected by theenvparameter above. Simple, and grants stay separate. - Zero-copy clone for ephemeral checks: give every pull request a real copy of production data for the length of a CI run.
CREATE DATABASE ci_pr_842 CLONE prd_analytics;
-- run the deploy + tests against ci_pr_842
DROP DATABASE ci_pr_842;
Cloning is metadata-only, so a 40 TB production database clones in seconds and costs storage only for what the tests change. This is the single biggest quality-of-life win of Snowflake CI/CD over traditional databases — use it.
Step 4: the Snowflake CLI in CI
snow is the command-line entry point: connections, SQL execution, and project deployment.
pip install snowflake-cli
# Key-pair auth is the right choice for CI; passwords are being phased out for service users
snow connection add \
--connection-name ci \
--account "$SNOWFLAKE_ACCOUNT" \
--user CI_BOT \
--private-key-file ./rsa_key.p8 \
--role DEPLOYER \
--warehouse DEPLOY_WH
snow sql -c ci -q "ALTER GIT REPOSITORY ops.public.warehouse_repo FETCH"
snow sql -c ci -q "EXECUTE IMMEDIATE FROM @ops.public.warehouse_repo/branches/main/deploy/deploy_all.sql USING (env => 'PRD')"
Set up the CI user with key-pair authentication and a dedicated role:
CREATE USER ci_bot TYPE = SERVICE RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';
CREATE ROLE deployer;
GRANT ROLE deployer TO USER ci_bot;
GRANT USAGE ON WAREHOUSE deploy_wh TO ROLE deployer;
GRANT ALL ON DATABASE prd_analytics TO ROLE deployer;
Give deployer ownership of the objects it manages. Half of all failed deployments are a grant problem, not a SQL problem.
Step 5: the GitHub Actions workflow
name: snowflake-deploy
on:
pull_request:
push:
branches: [main]
jobs:
deploy:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-python@v5
with: { python-version: '3.11' }
- run: pip install snowflake-cli
- name: Write key
run: echo "${{ secrets.SNOWFLAKE_PRIVATE_KEY }}" > rsa_key.p8 && chmod 600 rsa_key.p8
- name: Configure connection
run: |
snow connection add --connection-name ci --no-interactive \
--account "${{ secrets.SNOWFLAKE_ACCOUNT }}" --user CI_BOT \
--private-key-file ./rsa_key.p8 --role DEPLOYER --warehouse DEPLOY_WH
- name: PR — clone prod and deploy to the clone
if: github.event_name == 'pull_request'
run: |
DB=CI_PR_${{ github.event.number }}
snow sql -c ci -q "CREATE OR REPLACE DATABASE $DB CLONE PRD_ANALYTICS"
snow sql -c ci -f deploy/deploy_all.sql --variable env=$DB
snow sql -c ci -f tests/smoke.sql --variable env=$DB
snow sql -c ci -q "DROP DATABASE $DB"
- name: main — deploy to production
if: github.ref == 'refs/heads/main' && github.event_name == 'push'
run: |
snow sql -c ci -q "ALTER GIT REPOSITORY OPS.PUBLIC.WAREHOUSE_REPO FETCH"
snow sql -c ci -q "EXECUTE IMMEDIATE FROM @OPS.PUBLIC.WAREHOUSE_REPO/branches/main/deploy/deploy_all.sql USING (env => 'PRD')"
Two paths, one repo: pull requests prove the change applies cleanly against production-shaped data and then throw the clone away; merges to main apply the same script to production from the branch Snowflake fetched itself.
Step 6: tests that are worth running
Keep tests/smoke.sql short enough that people do not disable it:
-- Structural: does every expected object exist?
SELECT table_name FROM identifier('{{ env }}' || '.information_schema.tables')
WHERE table_schema = 'ANALYTICS';
-- Contract: no unexpected nulls or duplicate keys
SELECT 'orders_pk' AS test,
IFF(COUNT(*) = COUNT(DISTINCT order_id), 'PASS', 'FAIL') AS result
FROM identifier('{{ env }}' || '.analytics.orders');
-- Governance: policies actually attached
SELECT ref_entity_name, policy_name
FROM TABLE(information_schema.policy_references(ref_entity_name => 'ANALYTICS.ORDERS',
ref_entity_domain => 'TABLE'));
If you already run dbt, its dbt build on the clone is a perfectly good CI stage; native DCM covers the objects dbt does not own — warehouses, roles, grants, tasks, policies, integrations.
Rollback, or the honest version of it
Snowflake gives you two real rollback tools and one myth.
- Time Travel /
UNDROPfor accidents:UNDROP TABLE analytics.orders;orCREATE OR REPLACE TABLE analytics.orders CLONE analytics.orders BEFORE (STATEMENT => '<query_id>'); - Re-deploy the previous commit — because the scripts are idempotent,
git revertplus a deploy is usually the cleanest fix. - The myth is transactional DDL. Snowflake DDL commits, so a half-finished deploy leaves a half-changed schema. Order your deploy script so the risky pieces come last, and take a clone of the target schema before large production deploys:
CREATE DATABASE prd_analytics_pre_r42 CLONE prd_analytics;costs nothing and buys you an afternoon.
A sane adoption order
- Get every object definition into Git, even if deploys are still manual. Source of truth first.
- Convert table and view scripts to
CREATE OR ALTER/CREATE OR REPLACEso they are re-runnable. - Add the CI user, key-pair auth, and a
deployerrole with real ownership. - Turn on PR builds against a zero-copy clone. This is where the bugs start getting caught.
- Only then automate the production deploy on merge.
Teams that try step 5 first usually roll it back within a month. Teams that do steps 1–4 rarely want to go back to worksheets.
PowderInsights builds Snowflake DevOps pipelines — repo structure, declarative object management, CI/CD, environment strategy, and the RBAC underneath it. Get in touch with your current setup and we will map the shortest path to safe, automated deployments.