schema-migration

Schema Migration Analysis

Requirements

Agent: any (read-only analysis) Tools used: altimate_core_migration, altimate_core_schema_diff, schema_diff, read, glob, bash (for git operations)

When to Use This Skill

Use when the user wants to:

• Analyze a DDL migration for data loss risks before applying it
• Compare two schema versions to find breaking changes
• Review ALTER TABLE / CREATE TABLE changes in a PR
• Validate that a model refactoring doesn't break the column contract

Do NOT use for:

• Writing new models -> use dbt-develop
• Analyzing downstream impact of SQL logic changes -> use dbt-analyze
• Optimizing queries -> use query-optimize

Workflow

1. Get the Schema Versions

For DDL migrations (ALTER TABLE, CREATE TABLE):

• Read the migration file(s) from disk
• The "old" schema is the current state; the "new" schema is after applying the migration

For dbt model changes (comparing before/after SQL):

# Get the old version from git
git show HEAD:<path/to/model.sql> > /tmp/old_model.sql
# The new version is the current file

For schema YAML changes:

• Read both versions of the schema.yml file

2. Analyze DDL Migration Safety

Call altimate_core_migration to detect data loss risks:

altimate_core_migration(old_ddl: <old_ddl>, new_ddl: <new_ddl>, dialect: <dialect>)

This checks for:

• Type narrowing: VARCHAR(100) -> VARCHAR(50) (truncation risk)
• NOT NULL without default: Adding NOT NULL column without DEFAULT (fails on existing rows)
• Dropped columns: Data loss if column has values
• Dropped constraints: Unique/check constraints removed (data integrity risk)
• Type changes: INTEGER -> VARCHAR (irreversible in practice)
• Index drops: Performance regression risk

3. Diff Schema Structures

For YAML/JSON schemas: Call altimate_core_schema_diff to compare two schema definitions:

altimate_core_schema_diff(schema1: <old_schema>, schema2: <new_schema>)