drive-snowflake

Writing a migration

Every schema change to a raw table, or any Snowflake object dbt doesn't own outright, is a file in src/stock/dbt/migrations/ or src/redbook/dbt/migrations/ — never a direct ALTER TABLE. schemachange applies these, tracking what's run per project. See Snowflake resources & RBAC for where this fits against Terraform.

Two file types

PrefixRunsUse for
V{n}__description.sqlOnce, ever, in ascending version orderTable/column DDL — anything that changes structure
R__description.sqlEvery deploy where its checksum has changed since the last runStored procedures, views, anything meant to be replaced wholesale each time

Neither project has an A__ (always-run) migration — only V and R are in use.

A versioned (V) file is never edited once merged. schemachange validates each applied version's checksum against the file on disk; changing a V file after it's run breaks that check for everyone who deploys after you. If a V migration was wrong, write a new V{n+1} that corrects it — don't edit the old one.

A repeatable (R) file is designed to be edited — that's the point. Every deploy re-applies it if the content changed, so CREATE OR REPLACE PROCEDURE-style definitions live here, not in a versioned file.

Real examples

Stock is on V1-V5 plus one R file (a post-processing stored procedure). Redbook currently has no versioned migrations at all — its five raw tables were all bootstrapped as R__create_..._raw_table.sql files.

A versioned migration, adding a column:

snippet.sqlsql
USE DATABASE VEHICLE_STOCK_DB;
USE SCHEMA VEHICLE_STOCK_SCHEMA;

ALTER TABLE STOCK_RAW ADD COLUMN IF NOT EXISTS
    redbook_vehicle_key_source VARCHAR(50) DEFAULT NULL
    COMMENT 'Populated once the VIN fallback resolves a key; NULL when dealer-supplied.';

A repeatable migration, a stored procedure dbt calls from a post-hook:

snippet.sqlsql
USE DATABASE VEHICLE_STOCK_DB;
USE SCHEMA VEHICLE_STOCK_SCHEMA;

CREATE OR REPLACE PROCEDURE update_raw_stock_status()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
...
$$;

Conventions to follow

  • USE DATABASE / USE SCHEMA at the top of every file — don't rely on the connection's default schema.
  • DDL is idempotent where the syntax allows it: CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS. A migration that's already been applied once should be safe to reason about even if someone re-runs schemachange deploy against a database that's already current — schemachange's own version tracking is what actually prevents re-execution, but idempotent SQL is a second line of defence.
  • Comment non-obvious columns inline (COMMENT '...') — the migration file is the schema's source of truth, and that comment is what shows up in DESCRIBE TABLE.

See also

Esc
Esc Close