How do you build an AI-assisted SQL migration pipeline? Structure it as four stages wired together with a version-controlled workflow: an inventory stage that pulls object definitions from the source system, a conversion stage where an LLM drafts the target-platform SQL against a dialect ruleset, a validation stage that reconciles source and target output row by row, and a deployment stage that promotes only validated objects through CI/CD into Fabric or Databricks.
The Pipeline Most Teams Build Backwards
Most teams that start experimenting with AI-assisted SQL migration make the same early decision: they open a chat window, paste in a stored procedure, and ask for a Databricks or Fabric equivalent. It works, once. It even works twice. Then someone tries to run 400 of these conversions before a Friday deadline and realizes there's no pipeline underneath the demo, just a person copying and pasting into a browser tab.
That gap between "AI can convert SQL" and "we have a pipeline that converts SQL at scale, reliably, with an audit trail" is where most migration efforts stall. This piece walks through the pipeline itself: how the stages connect, what runs where, and the parts of the workflow that are easy to skip in a proof of concept but expensive to skip in production.
What the Pipeline Actually Needs to Do
Before writing any code, it helps to separate the pipeline into stages that do genuinely different jobs. Treating them as one blob of "AI migration" is usually where things go sideways.
Inventory: pull every object definition, its dependencies, and its metadata directly from the source system. Not from a spreadsheet someone maintained two years ago.
Conversion: send each object, along with dialect-specific context, to a language model and get back a first-pass translation into the target platform's SQL dialect.
Validation: run the converted object against representative data and compare output to the source, catching both syntax errors and quieter semantic drift.
Promotion: move validated objects through source control and CI/CD into the target environment, with a clear record of what changed and why.
Each of these stages can be built independently, and honestly, they should be. Coupling them tightly is a common early mistake that makes the whole system harder to debug when a batch run partially fails.
Stage 1: Inventory, Pulled Straight From the Source
Skip anything that depends on someone's memory or an out-of-date wiki page. Pull object definitions and metadata directly from the system catalog.
For a SQL Server source feeding a Fabric migration:
SELECT
s.name AS schema_name,
o.name AS object_name,
o.type_desc,
m.definition,
o.modify_date
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.type IN ('P', 'V', 'FN', 'TF')
ORDER BY o.modify_date DESC;
Store the output as one record per object: name, type, full definition text, dependency references, and a rough size metric (line count works fine as a first pass). This becomes the work queue for everything downstream, so get the schema right before moving on.
**Stage 2: Conversion, With Dialect Context Baked Into the Prompt
The single biggest lever for conversion quality isn't the model, it's the context you give it. A prompt that just says "convert this to Databricks SQL" produces mediocre results. A prompt that includes the specific dialect differences relevant to the object produces something much closer to production-ready.
*A simplified conversion request for a T-SQL to Databricks SQL translation: *
def build_conversion_prompt(
object_definition: str,
source_dialect: str = "tsql",
target_platform: str = "databricks"
) -> str:
"""
Build an AI prompt for converting SQL objects between dialects
while preserving business logic and identifying unsupported constructs.
"""
dialect_notes = {
"tsql": [
"TOP N has no direct equivalent. Use LIMIT after ORDER BY.",
"IDENTITY columns become GENERATED ALWAYS AS IDENTITY in Delta tables.",
"Replace ISNULL() with COALESCE().",
"Replace GETDATE() with current_timestamp()."
]
}
notes = "\n".join(
f"- {note}" for note in dialect_notes.get(source_dialect, [])
)
prompt = f"""
Convert the following {source_dialect.upper()} object into {target_platform.title()} SQL.
Requirements:
- Preserve business logic exactly.
- Do not guess unsupported syntax.
- Flag constructs that require manual review.
- Maintain object names and comments where possible.
- Optimize only when the semantic behavior remains identical.
Dialect Notes:
{notes}
Source Object:
{object_definition}
"""
return prompt
Two things matter here that are easy to miss. First, explicitly instructing the model to flag ambiguity rather than guess reduces the number of silently wrong conversions you'll catch later, if you catch them at all. Second, keep the dialect notes in version control alongside your pipeline code. Every migration surfaces new edge cases, and that file should grow with the program, not live in someone's head.
Stage 3: Batch Orchestration on Fabric and Databricks
Once conversion logic works on a single object, the next problem is running it across a few thousand objects without babysitting the process. On Databricks, a Workflow job with a parameterized notebook handles this cleanly:
# Databricks notebook
# Process a batch of pending SQL objects for AI-assisted conversion
batch_objects = spark.sql(
f"""
SELECT
object_name,
object_type,
definition
FROM migration.object_inventory
WHERE batch_id = {batch_id}
AND status = 'pending'
"""
).collect()
for obj in batch_objects:
# Convert the SQL object using the AI conversion service
converted_sql = call_conversion_service(
definition=obj.definition,
object_type=obj.object_type
)
# Store the converted SQL
write_conversion_result(
object_name=obj.object_name,
converted_definition=converted_sql,
batch_id=batch_id
)
# Mark the object as successfully converted
update_status(
object_name=obj.object_name,
status="converted"
)
markdown
On Microsoft Fabric, the equivalent pattern runs as a Data Factory pipeline with a ForEach activity iterating over the inventory table, calling a Fabric notebook or an Azure Function per object, and writing results to a Lakehouse table. The orchestration tool differs; the shape doesn't. Batch by a manageable unit (50 to 200 objects per run works well in practice), checkpoint progress so a failure doesn't force a restart from zero, and keep conversion and validation as separate job steps so a validation failure doesn't require reconverting anything.
Stage 4: Validation That Catches More Than Syntax Errors
A converted object that runs without error is not the same as a converted object that produces correct output. Validation needs to check both.
from pyspark.sql import functions as F
# Load source and migrated tables
source_df = spark.read.table("source_catalog.orders")
target_df = spark.read.table("target_catalog.orders")
# --------------------------------------------------------------------
# Validation 1: Row Count Check
# --------------------------------------------------------------------
row_count_match = source_df.count() == target_df.count()
# --------------------------------------------------------------------
# Validation 2: Aggregate Parity Check
# --------------------------------------------------------------------
source_total = (
source_df
.agg(F.sum("amount").alias("total"))
.first()["total"]
)
target_total = (
target_df
.agg(F.sum("amount").alias("total"))
.first()["total"]
)
aggregate_match = abs(source_total - target_total) < 0.01
# --------------------------------------------------------------------
# Validation 3: Row-Level Hash Comparison
# Detect silent data drift after migration
# --------------------------------------------------------------------
comparison_columns = [
"order_id",
"amount",
"status"
]
source_hashed = source_df.withColumn(
"row_hash",
F.sha2(F.concat_ws("|", *comparison_columns), 256)
)
target_hashed = target_df.withColumn(
"row_hash",
F.sha2(F.concat_ws("|", *comparison_columns), 256)
)
mismatches = (
source_hashed.alias("src")
.join(
target_hashed.alias("tgt"),
on="order_id"
)
.filter(F.col("src.row_hash") != F.col("tgt.row_hash"))
)
print(f"Row Count Match : {row_count_match}")
print(f"Aggregate Match : {aggregate_match}")
print(f"Mismatched Rows : {mismatches.count()}")
markdown
Run all three checks (row count, aggregate parity, row-level hash) on every converted object, not a sample. Objects that fail any check get flagged for manual review instead of silently promoted. This is the step teams skip under deadline pressure, and it's almost always the one that costs the most time later, usually discovered by an analyst noticing a dashboard number looks wrong three weeks post-migration.
Git Workflow and CI/CD: Treat Converted SQL Like Any Other Code Change
Converted objects should go through the same review discipline as hand-written code, not a special exemption because "the AI wrote it."
A practical branching pattern: one branch per migration wave, with converted objects committed as they pass validation. A GitHub Actions or Azure DevOps pipeline runs on pull request:
name: SQL Migration Validation Pipeline
on:
pull_request:
paths:
- "migrations/**/*.sql"
jobs:
validate-migration:
name: Validate SQL Migration
runs-on: ubuntu-latest
steps:
# Check out the repository
- name: Checkout Repository
uses: actions/checkout@v4
# Validate SQL syntax before review
- name: Validate SQL Syntax
run: |
python scripts/validate_syntax.py migrations/
# Verify data reconciliation rules
- name: Run Data Reconciliation
run: |
python scripts/run_reconciliation.py migrations/
# Automatically flag complex SQL objects
# that require manual engineering review
- name: Flag High-Complexity Objects
run: |
python scripts/flag_high_complexity.py migrations/
# Publish validation summary
- name: Migration Validation Complete
run: |
echo "✅ SQL syntax validation passed."
echo "✅ Reconciliation checks completed."
echo "✅ High-complexity objects flagged for review."
Deployment to Databricks can use Databricks Asset Bundles to promote validated notebooks and jobs from a staging workspace to production. On Fabric, deployment pipelines move validated items (notebooks, Lakehouses, warehouse objects) between Dev, Test, and Production workspaces. Either way, nothing reaches production without passing through the same gate every other code change does.
Best Practices Worth Adopting Early
Version-control your dialect mapping rules separately from pipeline code; they change more often and get reused across projects.
Batch by complexity tier, not alphabetically or by ticket order; low-complexity objects should never wait behind a stuck high-complexity one.
Log every conversion attempt, including failures and flagged ambiguities; this becomes your audit trail when someone asks "why does this look different from the source."
Keep a human review queue explicitly separate from the automated pipeline; don't bury manual review steps inside automation scripts where they're easy to skip.
Re-run validation after any manual edit to a converted object, not just after the initial AI conversion.
Common Mistakes to Avoid
Skipping the inventory stage and converting off tickets. Without a complete inventory, you don't actually know your object count or dependency graph, which makes wave planning a guess.
Trusting a clean run as proof of correctness. A stored procedure that executes without error can still silently drop rows or miscompute an aggregate. Validation exists precisely because syntax success and semantic correctness are different things.
Building one giant script instead of separable stages. When inventory, conversion, validation, and deployment are tangled together, a bug in validation logic forces you to reconvert objects that were already fine.
Ignoring performance until it's a production incident. Batch size, cluster sizing, and rate limits on the conversion service all matter well before you hit object number one thousand.
Performance Considerations for Scale
Conversion calls to an LLM API have latency and rate limits that matter once you're running thousands of objects. Parallelize conversion calls with a bounded worker pool rather than a tight sequential loop, and cache conversion results by a hash of the source object definition so re-running a batch doesn't reconvert unchanged objects. On the Databricks side, size your cluster for the validation workload (which does real data processing) separately from the conversion workload (which is mostly API calls and light transformation) rather than running both on the same undersized cluster.
None of this pipeline replaces engineering judgment; it just moves that judgment to the places where it actually matters, architecture decisions, ambiguous business logic, and edge cases, instead of spending it on retyping SQL that a dialect mapping rule could have handled. Build the four stages as separable pieces, wire them together with the same rigor you'd apply to any production data pipeline, and the "AI-assisted" part becomes a detail of stage two rather than the entire system.
Key Takeaways
- Split the migration pipeline into inventory, conversion, validation, and promotion; treating it as one process makes debugging batch failures much harder.
- Dialect-specific context in the conversion prompt matters more than model choice for output quality.
- Validate every converted object with row count, aggregate, and row-hash checks, not a sample.
- Route converted SQL through the same Git and CI/CD discipline as any other code change.
- Batch size, caching, and cluster sizing become real constraints once you're converting thousands of objects, not dozens.
If you're planning a large-scale SQL modernization project, the conversion engine is only one piece of the puzzle. Consistent validation, dialect-aware translation, and structured orchestration are what turn AI-assisted migration into a repeatable engineering process. If you'd like to see how this approach works in practice, explore our Code Conversion Accelerator to understand how automated SQL conversion is combined with enterprise-grade validation and review.
Top comments (0)