Orchestrating Migration, Schema and Pipeline Agents Together

Orchestrating Migration, Schema and Pipeline agents together works when you give each agent one verb (detect, decide or act), pass a typed context object at every handoff, and put a human approval gate before anything writes to production. We run these three as MCP servers, and the Model Context Protocol spec is what lets one coordinator call all of them with the same tool-call contract. The rest of this post is the framework we use, including where it breaks.

Key Takeaways

• •Split the job by verb: the Migration Agent analyzes and proposes, the Schema Agent assesses risk, the Pipeline Agent executes. No agent both decides and acts on production.
• •Every handoff carries a structured context object (source lineage, target model, blast radius, rollback plan), not a chat summary.
• •Approval gates belong before writes to production and before any destructive schema change. Read-only analysis can run unattended.
• •When one agent fails, the coordinator halts downstream steps, keeps the partial artifacts, and escalates with the failing agent's last context. It does not retry blindly.
• •The OSS edition (dw-claw, Apache 2.0) is read-only by default, so the analysis half of this workflow runs locally with no write risk.

Orchestrating Migration, Schema and Pipeline Agents Together: the shared job

The shared job is moving a legacy workload (stored procedures, hand-written ETL, a warehouse-specific SQL estate) to a modern target such as dbt, SQLMesh or Iceberg tables, without breaking the consumers that depend on it. That job has three distinct failure modes: translating the logic wrongly, changing a schema that something downstream reads, and deploying a pipeline that runs but produces different numbers. One agent handling all three mixes concerns that have different risk profiles.

So we separate them. The Migration Agent reads legacy SQL and ETL definitions and emits target models. The Schema Agent detects column-level drift between legacy and target and projects downstream impact. The Pipeline Agent builds and maintains the jobs in Airflow, Dagster, Prefect, dbt or Fivetran. The Orchestration Agent sits above them and owns sequencing, not domain logic.

Who detects, who decides, who acts

Detection, decision and action are different jobs, and mapping them to agents makes the approval points obvious. In our framework the Migration Agent detects what the legacy code does and proposes a target. The Schema Agent decides whether the proposed change is safe by projecting its blast radius. The Pipeline Agent acts, and only after the decision is recorded and approved.

• •Detect (Migration Agent): parse legacy SQL/ETL, extract tables, joins, transformations and implicit business rules, then emit candidate dbt, SQLMesh or Iceberg models.
• •Decide (Schema Agent): compare legacy and target column definitions, flag type changes, renamed columns and dropped fields, and answer questions like "What's the blast radius if I drop this column?" before anything ships.
• •Act (Pipeline Agent): create or update orchestrator jobs, wire dependencies, run the backfill and schedule the cutover.
• •Coordinate (Orchestration Agent): order the steps, hold the gates, and route failures.

The point of this split is auditability. If a wrong number reaches a dashboard, you can ask which agent proposed it, which assessed it and which deployed it, and each answer is a separate recorded step.

The handoff table

Handoffs are where multi-agent systems usually lose information. We pass a structured object at each one rather than prose, so the receiving agent does not have to re-derive what the sender already knew.

Handoff 4 is the one teams skip. The Pipeline Agent deploying successfully only means a job ran. The Schema Agent checking the built schema against the approved plan is what catches silent divergence.

Approval points: where humans stay in the loop

We put gates on writes, not on reasoning. Analysis, lineage extraction and impact projection are read-only, so they run without a person. The OSS edition (dw-claw) is read-only by default, which means you can run handoffs 1 and 2 locally against a real legacy estate before enabling anything that writes. Pro adds writes, approval workflows and audit trails for handoffs 3 through 5.

• •Gate A, before any breaking schema change: a human confirms the consumers listed in the blast radius are known and notified.
• •Gate B, before the first production write: a human approves the ordered plan and the rollback path.
• •Gate C, before cutover: a human compares the new output against the legacy output and signs off.

We have not seen a good reason to remove Gate C. Even when validation passes, the cutover is the one step where the cost of being wrong lands on consumers rather than engineers.

What happens when one agent fails

Failure handling is the part of an orchestration framework that gets written last and matters most. Our rule is that the coordinator halts, preserves and escalates; it does not retry a failed step with the same inputs.

• •Migration Agent fails (cannot parse a vendor-specific SQL construct): the coordinator marks that object as manual, continues with the rest, and lists the unparsed objects in the report. Partial migrations are expected for legacy estates.
• •Schema Agent fails or returns low confidence: the plan does not advance. Without a blast-radius answer, handoff 3 cannot be approved, so the failure blocks safely.
• •Pipeline Agent fails mid-deploy: the coordinator stops downstream steps, leaves the legacy job untouched, and passes the failing agent's last context and error to a human. Any created jobs are listed so they can be removed or inspected.
• •Orchestration Agent fails: the three domain agents are independent MCP servers, so each can still be invoked directly from Claude Code or Cursor while you fix the coordinator.

Because the legacy job keeps running until Gate C, a failure in any agent leaves production on the old path. That property comes from the sequencing, not from the agents themselves.

Running it from Claude Code or Cursor

Each agent is an MCP server, so you invoke the workflow from the tool your engineers already use. In Claude Code's MCP support you register the servers once, then ask in plain language. We cover the broader pattern in our posts on Claude Code for data engineering, and the same registration works in Cursor, Windsurf and VS Code.

Reader-style prompts that map to this workflow include "What changed in the orders schema since last deploy?", "Is the customer_id join safe after the migration?" and "What's the blast radius if I drop this column?" The first two go to the Schema Agent, the third is the Gate A question. Our Incidents Agent is the natural next hop if cutover causes a regression, and our Catalog Agent keeps lineage current so the Migration Agent starts from accurate metadata.

Where this framework is weaker than alternatives

We should be direct about limits. If your migration is a single dbt project upgrade with no schema changes, a coordinator and three agents is more machinery than you need, and a single agent or a plain script is the better choice. Dedicated migration vendors and cloud-provider conversion tools also have years of dialect-specific translation work behind them, and for a single well-supported source such as one warehouse's SQL dialect, they may translate more completely than a general agent. The multi-agent framework earns its keep when the estate is mixed, consumers are many, and the risk is in the seams between translation, schema and deployment.

We have not published benchmark numbers for migration accuracy, so treat any claim about translation coverage as something to test on your own estate. A read-only pass with the OSS edition is the cheapest way to do that.

Frequently Asked Questions

Why use three agents instead of one for a data migration? Translation, risk assessment and deployment have different failure modes and different approval needs. Separating them means the agent that proposes a change is not the one that approves or deploys it, which gives you a clear audit trail and lets analysis run read-only.

Which agent should hold the approval gate? None of them. Gates belong to the Orchestration Agent and a human approver. Domain agents recommend; the coordinator enforces the stop and records who approved.

Can I run only part of this workflow? Yes. Each agent is a separate MCP server, so you can run the Migration and Schema agents for analysis alone, using the read-only OSS edition, and add the Pipeline Agent later once writes and approval workflows are in place.

What target formats does the Migration Agent emit? It analyzes legacy SQL and ETL and emits dbt, SQLMesh or Iceberg targets. Which one fits depends on your stack; the handoff context records the choice so the Schema Agent compares against the right definitions.

What happens to the legacy pipeline during migration? It stays live until the cutover sign-off. The new pipeline runs in parallel, so any agent failure or validation mismatch leaves production on the old path.

Does data leave our infrastructure? No. Data Workers is designed so data stays in the customer's infrastructure, and the OSS edition runs locally.