DEV Community: Hariharan Arulmozhi

How to Build an AI-Assisted SQL Migration Pipeline for Modern Data Platforms

Hariharan Arulmozhi — Wed, 29 Jul 2026 14:00:00 +0000

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.

Synapse Dedicated SQL Pool to Microsoft Fabric: The Migration Risks Nobody Warns You About

Hariharan Arulmozhi — Wed, 15 Jul 2026 13:31:15 +0000

Thousands of enterprise data warehouses still run on Azure Synapse Dedicated SQL Pool. Most teams assume moving to Microsoft Fabric is a straightforward T-SQL migration. Redeploy the objects, point them at a new connection string, done.

It isn't. And the biggest risks in this migration aren't where most teams expect them to be.

Synapse Dedicated SQL Pool has stopped getting new features. Microsoft still patches it for security, but the engine has run its course. Every team still running production workloads on it is now on a forced timeline to Fabric, whether they planned for it this year or not. Microsoft's own documentation covers the schema, code, and data migration options well. What it doesn't tell you is which parts of a real, multi-thousand-object estate actually break during the move, and where teams lose weeks, they didn't budget for. This is that part.

This article is for you if you are:

Planning a Synapse to Fabric migration
Managing an enterprise data warehouse
Assessing Microsoft Fabric adoption
Responsible for SQL modernization
Supporting analytics platform modernization
Typical Azure Synapse Dedicated SQL Pool Architecture

Most Synapse estates look roughly like this:

Azure Synapse Dedicated SQL Pool
                │
                ▼
Tables + Indexes → Views → Stored Procedures → ETL Pipelines → Power BI

Every layer in that stack has Synapse-specific assumptions baked into it, some obvious, some not. That's what makes this a genuinely different migration from a typical lift-and-shift.

Why This Isn't a Simple Connection-String Swap

Fabric Warehouse is T-SQL compliant, which makes this look, on paper, like a redeploy. For a handful of objects, that's roughly true. For an estate with years of accumulated logic, it isn't, for three specific reasons.

Azure Synapse Dedicated SQL Pool	Microsoft Fabric Warehouse
Clustered columnstore indexes	Different optimization model (no traditional user-managed indexes)
DWU (Data Warehouse Units)	CU (Capacity Units) with non-linear sizing
CETAS for data export	COPY INTO for data ingestion
Primary and foreign key constraints enforced	Constraints require validation and may need reimplementation
Uses "SQL Pool" terminology	Uses "Warehouse" terminology

Indexes aren't supported in Fabric Warehouse. If your dedicated SQL pool workload leans on clustered columnstore index tuning for query performance, that tuning approach doesn't carry over. You're not migrating the index, you're redesigning around its absence.

Constraints don't migrate automatically. Primary keys, foreign keys, and check constraints on the source side typically don't move with a straight schema copy. If downstream logic, or a BI tool, silently depended on referential integrity being enforced at the database layer, that assumption breaks quietly, not loudly.

DWU to Fabric Capacity Unit sizing isn't a clean formula. Microsoft's published mapping guidance is benchmarked against TPC-H, and real workloads deviate from TPC-H in ways that matter. A DWU6000 environment doesn't drop cleanly into one specific Fabric SKU. Under-sizing shows up as concurrency throttling weeks after go-live, not on day one.

None of these are blockers. They're planning inputs that need to be scoped before conversion starts, not discovered during it.

Migration Options from Azure Synapse to Microsoft Fabric

Microsoft supports a few paths for schema (DDL), code (DML), and data migration. In practice, teams end up combining them depending on object type and data volume.

Data Factory Copy Assistant works well for smaller databases. It's a drag-and-drop path: connect to the source dedicated SQL pool, convert DDL syntax, copy data. Straightforward, but it doesn't scale gracefully past a modest number of tables before pipeline management becomes its own project.

CETAS to ADLS, then COPY INTO Fabric is the higher-throughput option. You stage data as Parquet in a data lake first, which decouples extraction from load and performs meaningfully better at volume:

-- Stage the source table to Parquet via CETAS 

Example: Export with CETAS and Import Using COPY INTO 

CREATE EXTERNAL TABLE staging.orders_export 
WITH ( 
   LOCATION    = '/staging/orders/', 
   DATA_SOURCE = ADLSGen2Staging, 
   FILE_FORMAT = ParquetFormat 
) 
AS 
SELECT * 
FROM dbo.orders; 

-- Load the staged Parquet data into Fabric Warehouse 

COPY INTO dbo.orders 
FROM 'https://storageaccount.blob.core.windows.net/staging/orders/' 
WITH ( 
   FILE_TYPE = 'PARQUET' 
);

This is the path we default for anything beyond a handful of terabytes.

dbt Fabric adapter, if the team already runs dbt against the dedicated SQL pool, is worth a serious look before hand-converting DDL and DML manually. It handles a meaningful chunk of the schema and transformation logic conversion natively, though data movement still has to happen through one of the other paths.

Recommended Enterprise Migration Workflow

The mechanics in the previous section cover the easy 70% of this flow. The remaining 30% is where migrations stall, and it's rarely about moving data.

Stored Procedure Challenges

Stored procedures with platform-specific syntax compile differently, or not at all, under Fabric's T-SQL surface. These need object-by-object review, not a find-and-replace pass. Enterprise data platforms often contain thousands of SQL objects, which makes manual review one of the most time-consuming phases of any modernization effort.

ETL Dependencies

ETL pipelines built around dedicated pool-specific patterns, staging table swaps, partition switching tricks, assume dedicated pool internals that don't exist in Fabric. Each of these needs to be re-architected, not just re-pointed.

Hidden Business Logic

Undocumented business logic buried in views and procs is the hardest category, because nobody currently at the company fully understands all of it. It was often written by someone who left three platform migrations ago.

This last category is the one that actually determines your timeline. Microsoft's guidance correctly recommends starting with an inventory and choosing a workload area you can prove out first, typically in the 1 to 10 terabyte range. What it can't give you is a fast way to know, across thousands of objects, which ones are simple lift-and-shift and which ones are landmines.

Migration Risks at a Glance

Indexes             →  redesign, not migrate
Constraints         →  re-implement manually
Stored Procedures   →  object-by-object review
DWU-to-CU Mapping   →  validate against real concurrency
ETL Dependencies    →  re-architect, don't re-point
Hidden Dependencies →  the real timeline risk

A Practical Way to Sequence It

For a dedicated SQL pool estate of meaningful size, this order tends to hold up:

Inventory everything first, including cross-object dependencies. You can't scope a migration wave plan against an estate you haven't fully mapped.

Score objects by conversion complexity, not just row count. A 50-line stored procedure with three nested CTEs and a nonstandard join pattern is a bigger risk than a 5-million-row fact table with a clean schema.

Pick a small, representative workload for wave one. As a starting point, something like 100 tables, 10 stored procedures, and 5 ETL jobs is enough to prove the pattern, indexes, constraints, sizing, without taking on the risk of the full estate at once.

Convert in bulk once the pattern is validated, with every converted object run through automated reconciliation against the source, not spot-checked on a sample.

Right-size Fabric capacity based on actual observed concurrency, not the DWU-to-CU mapping table alone. Treat the published mapping as a starting point, not a final answer.

Mistakes Worth Naming

Sizing Fabric capacity off the DWU mapping table alone. The published DWU-to-CU guidance is a reasonable starting point, but it's benchmarked against TPC-H, and TPC-H doesn't look like your production workload's concurrency pattern. Teams that skip a capacity validation step tend to find out they under-sized three weeks after go-live, when concurrent query load hits its first real Monday morning.

Treating the migration as one monolithic cutover. A phased, domain-by-domain wave plan, finance first, then supply chain, then reporting, contains risk to one area at a time. A single big-bang cutover means a schema surprise in one domain can stall the entire program.

Skipping reconciliation on "simple" objects. The instinct is to spot-check the complex stored procedures and wave through anything that looks like a clean table copy. In practice, silent data type coercion issues show up just as often in supposedly simple objects as in complex ones. Automated row-and-column-level reconciliation on every converted object, not a sample, is the only way to catch this before a business user does.

The Takeaway

Migration success depends less on copying data and more on understanding the complexity of the existing estate before conversion begins.

Organizations that inventory dependencies, score objects by risk, and validate workloads early are far more likely to hit their Fabric migration timeline. The ones that treat it as a connection-string swap usually find out otherwise around week six.

For teams evaluating a Synapse-to-Fabric migration, an early assessment of dependencies, complexity, and platform-specific risks can significantly reduce project uncertainty.

Learn more about the Microsoft Fabric Migration Accelerator from 3X Data Engineering.