Legacy SQL rarely fails all at once. It accumulates in stored procedures, duplicated business rules, undocumented joins, and “temporary” reporting logic that somehow becomes core infrastructure.
AI migration agents now offer a credible way to accelerate cleanup. But as recent examples from Ecolab, Datafold, AWS, and dbt show, the real modernization opportunity is not just converting old SQL into new SQL—it is turning fragile logic into governed, testable, documented data products.
The Promise and Risk of AI-Assisted SQL Modernization
For many engineering and data teams, legacy SQL is one of the most expensive forms of technical debt. It is hard to test, hard to review, and hard to change safely. Business logic is often embedded in sprawling queries rather than explicit models. Ownership is unclear. Performance problems are difficult to isolate. And when a key analyst or engineer leaves, the organization loses critical institutional memory.
AI migration agents are changing the economics of this work. Instead of manually reading thousands of lines of procedural SQL or deeply nested transformations, teams can use agents to analyze logic, identify patterns, propose modular replacements, and generate documentation. That can compress months of tedious migration work into a much shorter cycle.
But there is a catch: AI can generate plausible code that is still wrong.
In data engineering, “plausible” is especially dangerous. A query can run successfully, return reasonable-looking numbers, and still misstate revenue, churn, margin, or customer counts. This is why AI-driven modernization must be paired with governed semantic context: shared definitions, lineage, tests, metrics, ownership, and review workflows that make correctness observable.
What Ecolab and Datafold Showed: AI Can Reduce SQL Debt at Scale
A recent dbt Labs case study, “How Ecolab used an AI migration agent to shrink 84,000 lines of legacy SQL by 6x”, offers a practical example of what AI-assisted modernization can look like when applied to real enterprise data debt.
Ecolab partnered with Datafold to rebuild legacy Snowflake logic as clean, documented dbt models. The starting point was significant: 84,000 lines of legacy SQL. Using an AI migration agent, the team reduced that logic by 6x while moving it into a more maintainable dbt structure.
That reduction matters, but not only because the codebase got smaller. The more important modernization gain is that the logic became easier to reason about. Instead of large blocks of opaque SQL, the work was translated into modular models with documentation and structure. That creates a foundation for testing, review, dependency tracking, and future change.
For CTOs and engineering leaders, this is the distinction that matters: the win is not “AI wrote SQL.” The win is that AI helped convert inherited complexity into maintainable architecture.
Why Fewer Lines of SQL Is Not the Whole Story
A 6x reduction in SQL volume is impressive, but line count is only a proxy for maintainability. A shorter query can still be opaque. A generated model can still duplicate business logic. A migration can still preserve bad assumptions from the old system.
The better question is: did modernization make the data platform easier to operate?
A successful migration should improve several dimensions at once:
- Understandability: Can engineers explain what each model does?
- Traceability: Can teams see where a metric comes from and what upstream data it depends on?
- Testability: Are transformations covered by data quality checks, reconciliation tests, and business validations?
- Change safety: Can teams modify models without breaking downstream reports unexpectedly?
- Ownership: Is there a clear accountable team or domain owner for each model and metric?
AI migration agents can accelerate the mechanical work, but these operating characteristics come from engineering discipline.
The AWS Revenue Example: Plausible AI Can Be Expensively Wrong
The risk side of the story appears clearly in another dbt Labs article, “Why an AI agent's revenue query was $3.5 million wrong, and how AWS fixed it with dbt Semantic Layer”.
In that example, an AI-generated revenue query looked plausible but produced a result that was $3.5 million wrong. The issue was not that the SQL failed syntactically. It was that the agent lacked the right business context to define revenue correctly.
This is the core lesson for modernization teams: AI does not automatically know your company’s semantics.
Revenue may exclude refunds, include only recognized revenue, depend on contract timing, filter out internal accounts, or apply different logic by region or product line. Similar ambiguity exists for common metrics like active users, booked ARR, net retention, gross margin, incidents, conversion, and utilization.
Without a governed semantic layer, an AI agent may infer definitions from table names, column names, or nearby examples. Sometimes that works. Sometimes it creates a confident answer that contradicts how the business actually operates.
Semantic Context Is the Guardrail
Semantic context gives automation a controlled vocabulary. It tells agents which entities, metrics, dimensions, joins, filters, and business rules are approved. In the AWS example, dbt Semantic Layer helped anchor the AI workflow in governed definitions instead of letting the agent invent its own interpretation.
That pattern is essential for modernization. If you are using AI to migrate legacy SQL, the output should not just be “new queries.” The output should be integrated into a governed data architecture where definitions live in version-controlled code and can be reviewed, tested, and reused.
This is consistent with other recent dbt ecosystem examples. Okta’s work on governing context for AI agents at scale reinforces the need for a single source of truth. Sigma’s large semantic layer on dbt and Dagster shows how semantic definitions can be managed as code across thousands of models. Virgin Media O2’s compute improvements from giving dbt better state awareness show another side of the same principle: modernization works best when platforms have memory, metadata, and structure.
From Refactor Button to Modernization Workflow
The wrong way to use an AI migration agent is to treat it like a magic refactor button: point it at legacy SQL, accept the generated output, and move on. That may reduce visible clutter while preserving or even amplifying hidden risk.
The better approach is to embed AI into a governed migration workflow.
1. Inventory the Legacy Surface Area
Before generating replacements, map what you have. Identify stored procedures, scheduled jobs, dashboard queries, one-off reporting scripts, and manually maintained transformations. Capture runtime frequency, downstream consumers, business owner, and known pain points.
This inventory helps teams prioritize. High-value, high-risk transformations should receive more human review and validation. Low-risk cleanup may be a better fit for faster automation.
2. Extract and Normalize Business Logic
Legacy SQL often contains repeated CASE statements, hardcoded filters, date logic, and inconsistent joins. AI agents can help detect these patterns, but teams should use the migration to normalize business logic into reusable models and metrics.
For example, if five queries define “active customer” differently, the goal should not be to generate five cleaner versions. The goal should be to define “active customer” once, document it, test it, and reference it consistently.
3. Convert to Version-Controlled Models
Ecolab’s move from legacy Snowflake logic into dbt models is important because dbt brings software engineering practices to analytics code. Models can be reviewed in pull requests, tested in CI, documented, versioned, and organized by dependency.
For teams modernizing data systems, this matters as much as the AI itself. Generated code that lives outside version control is just a new maintenance liability. Generated code that enters a disciplined development workflow becomes an accelerant.
4. Validate Against Known Outputs
Every migration needs reconciliation. Compare old and new outputs across representative time periods, customer segments, regions, and edge cases. Do not rely only on row counts or total sums. Validate distributions, null behavior, duplicates, late-arriving data, and exception handling.
For financial or executive metrics, involve business stakeholders. The AWS revenue example shows why: correctness depends on business meaning, not just SQL syntax.
5. Document Assumptions and Ownership
AI-generated migrations should produce or update documentation as part of the workflow. Each model should explain what it does, what assumptions it encodes, where its inputs come from, and who owns it.
This is where modernization pays dividends over time. Future engineers should not have to reverse-engineer migrated code the same way current teams are reverse-engineering legacy SQL.
Practical Implications for Engineering Teams
For developers and platform engineers, AI migration agents are best understood as productivity multipliers, not autonomous maintainers. They can accelerate analysis, translation, documentation, and test generation. But teams still need review boundaries and quality gates.
A practical operating model might look like this:
- Use AI to summarize legacy SQL and identify transformation intent.
- Generate candidate dbt models from the legacy logic.
- Require human review for models tied to financial, compliance, customer, or executive reporting.
- Attach tests to every migrated model, including uniqueness, freshness, referential integrity, and accepted values where appropriate.
- Route metrics through a semantic layer rather than letting each agent or analyst redefine them.
- Use CI/CD to run validation before merge.
- Monitor production behavior after cutover.
For CTOs, the governance model is just as important as the tooling choice. AI can make migration cheaper, which means organizations may attempt more migrations faster. Without standards, that speed can create a larger mess. With standards, it can finally make long-deferred modernization feasible.
This also has implications for software maintenance strategy beyond data engineering. The same pattern applies to application upgrades, API migrations, infrastructure-as-code cleanup, and dependency modernization. Automation is valuable when it is constrained by context, tests, ownership, and review.
At Vibgrate, we see this as a core principle of sustainable modernization: accelerate the repetitive work, but govern the meaning. The goal is not to replace engineering judgment. The goal is to spend less engineering time deciphering old systems and more time improving them.
What Teams Should Do Next
If your organization is considering AI-assisted SQL migration, start with a pilot that is important enough to matter but bounded enough to validate. Choose a legacy data pipeline with known consumers and measurable outputs. Define success criteria before automation begins.
Good pilot metrics include:
- Reduction in SQL volume or duplicated logic
- Number of transformations converted into dbt models
- Test coverage added
- Documentation completeness
- Reconciliation accuracy between old and new outputs
- Runtime or compute improvements
- Reduction in support tickets or manual analyst intervention
Most importantly, evaluate whether the migrated system is easier to change safely. Modernization is not complete when the new code runs. It is complete when the next change is less risky than the last one.
Conclusion: AI Can Help Retire Legacy SQL, but Context Makes It Safe
The Ecolab and Datafold example shows that AI migration agents can make a meaningful dent in legacy SQL debt, even at enterprise scale. Shrinking 84,000 lines of Snowflake logic by 6x is a strong signal that automation can accelerate data platform modernization.
The AWS revenue example shows the other half of the story: without governed semantic context, AI can produce results that look right and are materially wrong. A $3.5 million error is not a formatting issue—it is a reminder that business meaning must be explicit.
The path forward is not to avoid AI migration agents. It is to use them inside disciplined modernization workflows: version-controlled models, documented transformations, semantic definitions, validation tests, and accountable ownership. Teams that combine automation with governance will not just migrate faster. They will build data systems that are easier to trust, maintain, and evolve.