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): ```bash # 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>) ``` Returns: added/removed/modified tables and columns, type changes, constraint changes, breaking change detection. **For SQL model changes:** Call `schema_diff` to compare two SQL models for column-level breaking changes: ``` schema_diff(old_sql: <old_sql>, new_sql: <new_sql>, dialect: <dialect>) ``` Returns: dropped columns (BREAKING), type changes (WARNING), potential renames (Levenshtein distance matching). ### 4. Present the Analysis ``` Schema Migration Analysis ========================= Migration: alter_orders_table.sql Dialect: snowflake BREAKING CHANGES (2): [DATA LOSS] Dropped column: orders.discount_amount -> Column has 1.2M non-NULL values. Data will be permanently lost. [TRUNCATION] Type narrowed: orders.customer_name VARCHAR(200) -> VARCHAR(50) -> 3,400 rows exceed 50 chars. Values will be truncated. WARNINGS (1): [CONSTRAINT] Dropped unique constraint on orders.external_id -> Duplicates may be inserted after migration. SAFE CHANGES (3): [ADD] New column: orders.updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP [ADD] New column: orders.version INTEGER DEFAULT 1 [WIDEN] Type widened: orders.amount DECIMAL(10,2) -> DECIMAL(18,2) Recommendation: DO NOT apply without addressing BREAKING changes. 1. Back up discount_amount data before dropping 2. Verify no values exceed 50 chars, or widen the target type 3. Confirm external_id uniqueness is no longer required ``` ### 5. For dbt Model Refactoring When the user is refactoring a dbt model (renaming columns, changing types): 1. Run `schema_diff` on old vs new compiled SQL 2. Cross-reference with `dbt-analyze` to check downstream consumers 3. Flag any downstream model that references a dropped/renamed column ## Usage - `/schema-migration migrations/V003__alter_orders.sql` -- Analyze a DDL migration file - `/schema-migration models/staging/stg_orders.sql` -- Compare current file against last commit - `/schema-migration --old schema_v1.yml --new schema_v2.yml` -- Compare two schema files