Snowflake AIM Migration Agent: A Technical Deep Dive Into What It Actually Does

The Migration Coordination Problem Nobody Talks About

I’ve run three large-scale migrations to Snowflake. The first one — 200+ objects from SQL Server — taught me something uncomfortable: the SQL conversion was maybe 20% of the actual work. The other 80% was coordination. Which objects depend on which. Who’s working on what. A fix I applied to one procedure that should have propagated to twelve similar ones but didn’t because nobody tracked it. Test cases that existed in someone’s head but never got written down.

That coordination layer is where migrations actually stall. Not at the syntax conversion — at the sequencing, the handoffs, the things that fall through cracks between Tuesday’s session and Thursday’s.

Snowflake AIM Migration Agent is built specifically for that coordination layer. It’s bundled with the Snowflake CoCo CLI and provides a stateful, dependency-aware workflow from source connection through validated deployment. I want to walk through what it actually does — particularly the parts that surprised me compared to running a migration with a general-purpose coding assistant.

Note: Snowflake AIM Migration Agent is bundled with the Snowflake CoCo CLI. Some sub-features (cloud data migration and cloud data validation) are in Preview. Check the official release notes for current availability by source system.

TL;DR

• The six-stage workflow handles the full arc from source connection to validated deployment — not just the code conversion piece that gets all the attention.
• Session persistence is the quiet killer feature: close your terminal, come back days later, resume at the exact object you left off on. No context rebuilding.
• Deployment waves with dependency analysis prevent the “missing object” failures that typically surface at 2 AM during a deployment window.
• The fix-rule engine is where migrations get cheaper over time — extract a correction once, propagate it to every matching object in the project.
• Two-sided testing catches functional equivalence bugs that row-count validation never will.

What this doesn’t cover: the Virtualization path (Teradata/Datometry), Spark-to-Snowpark migration, or a step-by-step tutorial. This is an architecture and capability walkthrough for teams evaluating the tool.

The Difference Between a Coding Agent and a Migration Agent

The first thing I had to unlearn: CoCo is already good at converting individual stored procedures. You paste in T-SQL, it gives you Snowflake SQL. So why do you need a separate migration agent?

Because a migration isn’t a collection of independent conversion tasks. It’s a project with dependencies, state, sequencing, and institutional knowledge that accumulates over weeks. The AIM Migration Agent adds the structure that a general-purpose agent can’t maintain across sessions:

Structured multi-stage workflow with a prescribed order that prevents you from deploying before dependencies exist. Automatic state tracking that resumes across terminal sessions — not approximately, exactly. SnowConvert AI integration that runs deterministic conversion before AI touches anything, so you start from a high-quality baseline. Source database connectivity for extraction and data migration. Deployment wave planning with full dependency analysis. An automated testing loop that captures baselines, validates equivalence, and patches failures. A fix-rule engine where corrections compound over the project lifetime. ETL pipeline conversion for SSIS and Informatica. Power BI report repointing to close the reporting layer gap.

The single-line comparison: CoCo handles individual tasks. The AIM Migration Agent handles the project.

Six Stages — But Stage 6 Is Where You Live

The agent structures work into six stages. What surprised me is how fast you move through the first four — and how much time you spend in Stage 6.

| Stage             | Description                                                                                                            |
| ----------------- | ---------------------------------------------------------------------------------------------------------------------- |
| Stage 1: Connect  | Source database connection setup.                                                                                      |
| Stage 2: Init     | Local project creation.                                                                                                |
| Stage 3: Register | DDL/code extraction from source, or `.sql` file import.                                                                |
| Stage 4: Convert  | Deterministic translation via SnowConvert AI, plus an EWI (Errors, Warnings, Issues) report.                           |
| Stage 5: Assess   | Interactive assessment with deployment waves, object exclusions, dynamic SQL analysis, and ETL analysis.               |
| Stage 6: Migrate  | Deploy, migrate data, validate, and resolve errors iteratively. Wave by wave. Often across multiple sessions and days. |

Every session opens with a progress checklist showing exactly where you stand. You can jump to any stage at any time by describing what you want — the prescribed order is a guardrail, not a cage.

Architecture note: AI conversion and verification (Stage 4 onward) runs server-side in your Snowflake account via Cortex AI and SPCS. Deterministic conversion still uses the SnowConvert AI CLI locally — but the agent auto-installs and orchestrates it. Cloud data migration uses an Orchestrator + Worker model: the Orchestrator manages workflow metadata in a SNOWCONVERT_AI database, Workers connect to both source and Snowflake, deployable on SPCS or locally.

Session Persistence — The Feature Nobody Demos

Here’s what actually happens on a real migration: you work intensely for three days, then get pulled into an incident. A week passes. You come back and spend an hour remembering where you were, what worked, what was still broken, which objects your teammate already handled.

The AIM Migration Agent eliminates that re-orientation tax entirely. It tracks project state locally and resumes at the exact point you stopped. Multiple engineers can work the same project simultaneously — the agent tracks which code units each person owns, so you’re not duplicating effort or stepping on each other’s changes.

For any team where “who’s working on what” currently lives in a spreadsheet or a Slack thread, this is built-in coordination that doesn’t require discipline to maintain.

Deployment Waves — Preventing the 2 AM Failures

I’d argue this is the most underrated capability, because it prevents a failure mode that’s hard to anticipate until you’ve lived it: deploying a view before its referenced function exists, or a procedure before the table it inserts into.

The assessment stage analyzes object dependencies and produces an ordered deployment sequence. Objects within a wave have no inter-dependencies. Across waves, dependencies are always deployed first. The output is a self-contained HTML report with an interactive wave editor where you can reprioritize objects, adjust wave sizes, relocate specific items between waves, and lock the plan before deployment begins.

The assessment also flags objects you probably don’t need to migrate (temporary tables, staging artifacts, deprecated objects), scores dynamic SQL patterns by migration complexity, and classifies ETL packages (SSIS and Informatica) by type — Ingestion, Transformation, Export, Orchestration, or Hybrid — with effort estimates.

The Fix-Rule Engine — Where Migrations Get Cheaper

This is the part that changes the economics of the project. Here’s the pattern: you spend time diagnosing a T-SQL construct that Snowflake handles differently. You fix it for one object. Fifteen similar objects are waiting in later waves. Without a rule engine, that’s fifteen copies of the same manual work.

The fix-rule engine lets you extract a correction into a reusable rule — stored in Snowflake — and propagate it across every matching object. Rules operate in two modes: regex (mechanical find-and-replace, deterministic, best for type mappings and function name rewrites) and AI-guided (the rule provides context and strategy, the AI interprets and applies with judgment, best for procedural rewrites and dynamic SQL remediation).

Four sub-capabilities make it work: Extract creates a rule from a fix you just made (works from a before/after diff or retroactively from Git history). Search scans a file against all rules using regex and Cortex semantic search. Apply executes matched rules — regex rules automatically, AI rules with review. Propagate finds every applicable code unit and batch-applies a rule across all matches.

The compounding effect is real: every correction extracted as a rule becomes available to all subsequent objects. Early investment in rule extraction pays dividends in later waves. By Wave 5, the agent is handling patterns you spent hours on in Wave 1 — automatically.

Two-Sided Testing — The Part That Caught Real Bugs

Row-count validation is the default because it’s easy. But a stored procedure that returns the correct count while producing different output for edge-case inputs is a production bug waiting for the worst possible moment.

The agent provides automated two-sided testing for functions and procedures: capture source-side baselines (inputs and expected outputs), deploy the converted object to Snowflake, run the same inputs against it, compare outputs. On failure: diagnose, patch, re-test. This loop repeats until outputs match or you decide to skip.

Get Satish Kumar’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Three strategies for generating test cases:

Query Logs pull actual EXEC calls from your source system’s execution history. Best when you have CSV logs of historical procedure runs. Available for SQL Server and Redshift.

AI-Assisted Swarm spawns parallel agents that probe the source database directly, generating test cases that cover business logic, data-driven scenarios, and edge cases. Best when no logs exist but you have source DB access. Available for SQL Server and Redshift.

Synthetic analyzes source SQL branches to generate test artifacts with synthetic INSERT data — no source connection needed. Planned for Teradata and Oracle.

After data migration, three-level validation runs: L1 (schema comparison), L2 (metrics and aggregates — counts, sums, distributions), and L3 (optional row-level comparison for business-critical tables).

ETL Pipeline Conversion — SSIS and Informatica to dbt

Beyond SQL objects, most real migrations include ETL pipelines that nobody wants to manually rewrite. The agent converts SSIS packages (.dtsx) and Informatica PowerCenter workflows (.xml) into two artifacts: a dbt project for transformation logic, and Snowflake Task orchestration SQL for control flow.

The testing approach here is fundamentally different from proc/function testing. Instead of capturing live source output, the agent derives expected behavior from the source definition file and generates dbt-native assertions: synthetic seed CSVs (3–5 rows per source table), then schema tests and singular assertion queries. Tests are generated before fixes — failures are the baseline. A dbt-fixer agent iterates until all tests pass. No source database connection needed.

For Informatica workflows specifically, assertion patterns cover lookup cache modes, aggregator modes (GROUP BY vs. incremental window functions), router DEFAULT group routing, and mapping variable branching. Orchestration testing uses SQL assertions against Snowflake with clone-based isolation.

This is deterministic conversion with optional AI-assisted remediation, available across all supported platforms.

Power BI Repointing and Cloud Data Migration

Two capabilities that close gaps most migration plans leave open:

Power BI report repointing redirects reports to Snowflake data sources post-migration. Supported for SQL Server, Redshift, Teradata, Oracle, PostgreSQL, and Azure Synapse. Without this, your reporting layer keeps pointing at the system you’re trying to decommission.

Cloud data migration (Preview) orchestrates actual data movement using a Worker + Orchestrator model. The Orchestrator manages metadata in Snowflake; Workers connect to both source and target. Deployable on SPCS or locally. Available for SQL Server, Redshift, Teradata, Oracle, and PostgreSQL. Three-level cloud data validation (Preview) runs post-migration: schema, aggregates, and optional row-level comparison.

Supported Source Systems — What’s Available Today

Not every capability exists for every platform. Here’s the honest breakdown:

Full support (extraction, AI conversion/verification, deploy, testing): SQL Server and Amazon Redshift.

Substantial support (extraction, deterministic conversion, deploy, cloud data migration/validation [Preview], ETL conversion, Power BI repointing): PostgreSQL. AI conversion/verification and testing framework are planned.

Extended support (deterministic conversion, cloud data migration/validation [Preview], ETL conversion, AI assessment, Power BI repointing): Teradata. Code extraction, AI conversion/verification, deploy, and testing are planned.

Partial support (deterministic conversion, cloud data migration/validation [Preview], ETL conversion, Power BI repointing): Oracle. Code extraction, AI conversion/verification, deploy, testing, and AI assessment are planned.

Deterministic conversion only (via SnowConvert AI): Azure Synapse, Sybase IQ, Google BigQuery, Greenplum, Netezza, Spark SQL, Databricks SQL, Vertica, Hive, IBM DB2.

Important: Even where the agent doesn’t support direct extraction, Snowflake provides standalone extraction scripts for Teradata, SQL Server, Synapse, Oracle, Redshift, Netezza, Vertica, DB2, Hive, BigQuery, Databricks, and Sybase IQ. Run the script, import the .sql files, and you’re in. Platforms like Teradata and Oracle have a usable path today — you’re not blocked waiting for native extraction.

Key Lessons

The surprise: The fix-rule engine’s compounding effect is more significant than I expected. By the fourth deployment wave, rules extracted in Wave 1 were handling patterns automatically that took hours of manual diagnosis initially. If I were starting over, I’d invest more heavily in rule extraction during the first wave — even when it feels slower upfront.

The limitation the docs don’t emphasize: The testing framework (Query Logs and AI-Assisted Swarm) is currently only available for SQL Server and Redshift. If you’re migrating from Teradata or Oracle, you’re relying on Synthetic testing (planned) or manual test creation for now. The extraction scripts bridge the code gap, but testing coverage is uneven across platforms.

My recommendation: Don’t skip the assessment stage. The deployment wave analysis alone prevented what would have been hours of “missing dependency” debugging during our deployment window. And the dynamic SQL scoring gives you an honest view of which objects will need manual attention — before you’re mid-deployment wondering why something won’t compile.

One more thing: if CoCo’s context window compacts during a long session and the agent stops recognizing migration commands, type /migrate <your prompt> to re-trigger it directly. This isn’t obvious from the docs.

Getting Started

Prerequisites: Python, Git, Snowflake CoCo CLI (the agent is bundled), a Snowflake account with a connection configured in ~/.snowflake/config.toml or ~/.snowflake/connections.toml, and a source database accessible from your machine. All other dependencies (uv, SnowConvert, Python packages) auto-install on first run.

To start a new migration:

mkdir my-migration && cd my-migration
cortex
> start a database migration

To resume:

cortex
> continue my migration

To import existing SQL files instead of extracting from source:

> import SQL files from ./my-exported-scripts/

AdventureWorksDW works as an end-to-end test workload if you want to evaluate the full flow before pointing it at production.

Summary

The AIM Migration Agent isn’t a smarter code converter — it’s a project coordinator that happens to include code conversion. The deployment waves prevent failures most teams discover the hard way. The fix-rule engine makes the project get cheaper as it progresses rather than more expensive. The two-sided testing catches bugs that row-count validation can’t see. And the ETL conversion and Power BI repointing close gaps that most migration plans leave for “later” — which usually means “never.”

It doesn’t eliminate judgment calls. Dynamic SQL, complex procedural logic, and platform-specific behaviors still need an architect who understands the source system. But it removes the coordination tax that consumes a disproportionate share of migration time — and that’s where most projects actually stall.

Documentation: Snowflake AIM Migration Agent | Quick start for Snowflake AIM Migration Agent

This article represents the author’s personal views and experience, not those of any employer.

#Snowflake #DataEngineering #DatabaseMigration #DataArchitecture #CortexAI