home / skills / sfc-gh-dflippo / snowflake-dbt-demo / schemachange

schemachange skill

safe

This skill helps you manage Snowflake schema changes with version-controlled migrations and CI/CD pipelines for safe deployments.

npx playbooks add skill sfc-gh-dflippo/snowflake-dbt-demo --skill schemachange

Review the files below or copy the command above to add this skill to your agents.

Files (2)

SKILL.md

2.4 KB

---
name: schemachange
description:
  Deploying and managing Snowflake database objects using version control with schemachange. Use
  this skill when you need to manage database migrations for objects not handled by dbt, implement
  CI/CD pipelines for schema changes, or coordinate deployments across multiple environments.
---

# Schemachange

Deploy and manage Snowflake database changes using version control and CI/CD pipelines with
schemachange's migration-based approach.

## Quick Start

**Script Types:**

- **V\_\_ (Versioned)** - One-time structural changes (run exactly once)
- **R\_\_ (Repeatable)** - Objects that can be safely recreated (runs when new/modified)
- **A\_\_ (Always)** - Scripts that run every deployment (must be idempotent)

## Script Naming

### Versioned Scripts (V\_\_)

```sql
V1.0.0__initial_setup.sql
V1.1.0__create_base_tables.sql
V2.0.0__restructure_schema.sql
```

Use for: CREATE TABLE, ALTER TABLE, CREATE SCHEMA

### Repeatable Scripts (R\_\_)

```sql
R__Stage_01_create_views.sql
R__Stage_02_alter_procedures.sql
R__Stage_03_utility_functions.sql
```

Use for: CREATE OR ALTER VIEW, CREATE OR ALTER PROCEDURE, CREATE OR REPLACE STREAM

### Always Scripts (A\_\_)

```sql
A__refresh_permissions.sql
A__update_config_values.sql
```

Use for: Jobs that must run every deployment (idempotent only)

## Key Concepts

### Execution Order

1. Versioned scripts (V\_\_) in numeric order
2. Repeatable scripts (R\_\_) in alphabetic order (use naming to control)
3. Always scripts (A\_\_) in alphabetic order

### CREATE OR ALTER vs CREATE OR REPLACE

- **CREATE OR ALTER** - Preserves data, tags, policies, grants (preferred)
- **CREATE OR REPLACE** - Drops and recreates (loses metadata)

See `CREATE_OR_ALTER_REFERENCE.md` for supported objects.

## Configuration

```yaml
# schemachange-config.yml
root-folder: migrations
create-change-history-table: true
connection-name: default
change-history-table: MY_DB.SCHEMACHANGE.CHANGE_HISTORY
```

## Deployment

```bash
# Dry run
schemachange deploy --config-folder . --dry-run

# Deploy
schemachange deploy --config-folder .

# With variables
schemachange deploy --vars '{"env":"prod","schema":"data"}'
```

## Resources

- `schemachange-config.yml` - Complete configuration template
- `SCRIPT_PATTERNS.md` - Examples for V**, R**, A\_\_ scripts (coming soon)
- `CREATE_OR_ALTER_REFERENCE.md` - Supported object types (coming soon)
- `CI_CD_EXAMPLES.md` - GitHub Actions and Azure DevOps patterns (coming soon)

Overview

This skill automates deploying and managing Snowflake database objects using schemachange’s migration-driven approach. It organizes SQL changes into versioned, repeatable, and always-run scripts so schema changes are tracked, auditable, and deployable via CI/CD. Use it to coordinate safe migrations across environments and preserve important object metadata.

How this skill works

The skill scans a configured migrations folder and executes scripts in a defined order: versioned (V__) scripts once in numeric order, repeatable (R__) scripts when changed in alphabetic order, and always (A__) scripts on every deployment. It relies on a change history table in Snowflake to track applied versioned migrations and supports passing runtime variables for environment-specific deployments. Configuration is handled via a YAML config file and commands support dry-run and full deploy modes.

When to use it

You need to manage schema changes that dbt does not handle (procedures, streams, grants, etc.).
You want versioned, auditable migrations that run once and preserve change history.
You need repeatable deploy logic for views, procedures, or objects that should update when modified.
You must run idempotent actions every deployment (permissions refresh, configuration updates).
You are implementing CI/CD pipelines to promote schema changes across dev, staging, and prod.

Best practices

Use V__ scripts for one-time structural changes (CREATE/ALTER table, schema restructuring) and increment semantic version numbers.
Use R__ scripts for CREATE OR REPLACE or CREATE OR ALTER objects that should reapply when modified; control order with alphabetic prefixes.
Prefer CREATE OR ALTER when supported to preserve data, tags, policies, and grants; avoid CREATE OR REPLACE when metadata must be retained.
Keep A__ scripts idempotent so they are safe to run every deployment (refresh permissions, upsert config values).
Maintain a dedicated schemachange-config.yml and enable a change history table to audit applied migrations.
Run dry-run locally or in CI before full deploys to validate ordering and variable substitution.

Example use cases

Apply a sequence of structural upgrades across environments: V1.0.0__init, V1.1.0__add_columns, V2.0.0__restructure.
Automate view and stored-procedure updates using R__ scripts that reapply when changed.
Enforce permissions and config consistency on every deployment with A__ scripts (refresh roles, update settings).
Integrate schemachange into GitHub Actions or Azure DevOps pipelines to gate and promote schema changes.
Use variables (--vars) to switch target schema or environment at deploy time without changing scripts.

FAQ

How are scripts ordered during deploy?

Versioned scripts run first in numeric order, then repeatable scripts in alphabetic order, then always scripts in alphabetic order.

When should I use CREATE OR ALTER versus CREATE OR REPLACE?

Prefer CREATE OR ALTER when supported to preserve data and metadata; CREATE OR REPLACE should be used only when dropping/recreating objects is acceptable.

Can I test a deployment without applying changes?

Yes — use schemachange deploy with --dry-run to simulate execution and validate ordering and variables.