/mssql-to-snowflake-dbt-migration

name: mssql-to-snowflake-dbt-migration
description: |
  Convert MS SQL Server source code (stored procs, views, T-SQL scripts) into dbt models targeting Snowflake, with build-first-then-parity, isolated-schema deployment, and a migration dashboard. Use when:
  (1) User says "migrate this T-SQL to dbt on Snowflake", "convert these stored procs end-to-end", "move <schema> from SQL Server to Snowflake"
  (2) Source lives in a repo as `.sql` dumps OR behind a reachable MS SQL connection, AND source rows are available in Snowflake for parity (replicated, staged, or landable)
  (3) Target is a dbt project with a Snowflake adapter
  (4) User expects a layered architecture (Medallion or Data Vault 2.0), parity-validated output, and an isolated-schema deploy
  **Prefer `migrating-sql-to-dbt`** for generic SQL-to-dbt conversion with no cross-engine parity, no isolated deploy, and no T-SQL-specific traps.
  Target wall time: ≤7 min for ≤10 objects; scales roughly linearly.

mssql-to-snowflake-dbt-migration

**Pick architecture → inventory source → convert → confirm → build to isolated schema → parity → verify → dashboard.**

When NOT to use this skill

• Source is PostgreSQL / MySQL / Oracle / BigQuery (not T-SQL) → `migrating-sql-to-dbt`.
• Target is not Snowflake → `migrating-sql-to-dbt` + the appropriate warehouse skills.
• Just one T-SQL snippet with no isolated-deploy or parity requirement → `migrating-sql-to-dbt`.
• Enhancing an existing dbt model → `dbt-model-enhancement`.

Prerequisites

command -v altimate-code     # strongly preferred — carries MS SQL + Snowflake adapters and data-parity
command -v dbt               # required for the deploy phase
command -v git               # required

Must have:

• A dbt project configured for Snowflake (`dbt_project.yml` + a working

`profiles.yml` or env-var profile).

• A Snowflake role with `CREATE SCHEMA` on the target database and

`USAGE` on a warehouse. The skill writes to an **isolated schema**, not prod.

• **A dedicated migration target in `profiles.yml` with a `schema:`

override** (see Phase 5 Method 1). The skill refuses to deploy without it — the `--vars '{target_schema: ...}'` trick is a no-op on standard projects.

• **`dbt_utils` package installed** (`packages.yml` + `dbt deps`) — the

rewrite table recommends `dbt_utils.generate_surrogate_key` as the NULL-safe way to port T-SQL `IDENTITY` columns. If `dbt_utils` isn't installed, add it before Phase 2 or avoid the IDENTITY mapping.

• Source access via **one** of:
• `altimate-code` configured with an MS SQL connection

(`altimate-code providers`), OR

• A directory of `.sql` dumps already in the repo.
• **For parity**: source rows must be readable from Snowflake — either

replicated (Fivetran/Airbyte/pre-existing bronze), or landable via stage + `COPY INTO`. Phase 6 hard-stops if neither is true.

If `altimate-code` is unavailable AND no dump exists, stop and tell the user.

If `dbt` is not installed, Phase 5 cannot run — stop and tell the user.

Time budget

Target end-to-end runtime: **≤7 min** (420s) for ≤10 objects; scales roughly linearly beyond that. Hard ceiling **10 min** (600s). Per-phase caps (sum = 420s): Phase 0 ≤20s · Phase 1 ≤45s · Phase 2 ≤120s · Phase 3 ≤15s · Phase 4 ≤15s · Phase 5 ≤90s · Phase 6 ≤90s · Phase 7 ≤25s.

Inputs

• **Source**: either (a) a reachable MS SQL connection, or (b) a repo path containing `.sql` dumps.
• **Target**: dbt project root (default: `pwd`).
• **Scope**: specific objects, a schema, or "all" (confirm if ambiguous).

Workflow

Phase 0 — Choose layering + initialise state (ask ONCE, ≤20s)

Ask the user:

> Which layering should the Snowflake dbt project use? > **1. Medallion** (bronze → silver → gold) — recommended for most analytics workloads. > **2. Data Vault 2.0** (hubs / links / satellites) — pick if the source is heavily normalized OLTP and auditability matters.

Default to Medallion if the user doesn't answer in one exchange.

**Initialise the run state immediately after the layering decision** — Phase 2's conversion step calls `scripts/record_written.sh`, which requires an active run. If init were deferred to Phase 4 (user confirmation), Phase 2's writes would either fail with "no current run" or write to a stale previous run that Phase 4's `init_run.sh` then overwrites with an empty manifest.

scripts/init_run.sh <Medallion|DataVault> '<migrated_models_selector>' <dbt_root>

What it does (see `scripts/init_run.sh`):

• Validates layering (`Medallion` or `DataVault`) and the selector

**before** touching state.

• Then `source scripts/state.sh init` — creates a fresh `<run_id>`

directory under `$(git rev-parse --git-dir)/skill/mssql_migration/<run_id>/`.

• Computes `TS` once and `ISOLATED_SCHEMA_PREFIX="MIGRATION_${TS}"`;

all later scripts read these from state.

• Persists `REPO_ROOT`, `DBT_ROOT`, `TS`, `ISOLATED_SCHEMA_PREFIX`,

`LAYERING`, `SCOPE_SELECTOR` into state.

**If an earlier attempt left stale state** (e.g. you ran `init_run.sh` with the wrong layering, or interrupted a previous run), the next `init_run.sh` would refuse with `a run is already active`. Either reset:

source scripts/state.sh reset
scripts/init_run.sh …     # retry

…or re-use the same run (if its state is still correct) and update only what changed via `source scripts/state.sh set VAR VALUE`.

The migrated-models selector can be refined in Phase 2 after inventory. **Do NOT re-run `init_run.sh`** — that would create a new `<run_id>`, truncate the written-files manifest, and regenerate `TS` + the schema prefix. Update the single value in place instead:

source scripts/state.sh load
source scripts/state.sh set SCOPE_SELECTOR '<updated_selector>'

Phase 1 — Inventory source (≤45s)

**Live MS SQL (via altimate-code):** pass the prompt via file to avoid shell interpolation of DB/schema names (which may contain quotes):

if command -v altimate-code >/dev/nu