DEV Community: Hariharan Arulmozhi

Stop Relying on Tribal Knowledge: Automate Metadata Extraction from Legacy SQL

Hariharan Arulmozhi — Wed, 26 Aug 2026 05:16:43 +0000

Every data engineering organization has a "Dave." Dave is the brilliant Principal Architect who wrote the core data warehouse pipelines a decade ago. He knows exactly why a random nested subquery on line 4,000 of a legacy stored procedure overrides the global pricing array. He is the person who understands the undocumented, delicate network of dependencies that keeps the daily analytics dashboards from completely collapsing.
When Dave goes on vacation, code deployments freeze. If Dave leaves the company, the engineering organization faces immediate operational paralysis.

Relying on an individual’s mental map is not a badge of honor; it is an active operational risk. In data organizations, this reliance acts as an immediate single point of failure (SPOF). Yet, the traditional industry fix forcing engineers to manually update markdown files or fill out static data catalogs during post-sprint documentation workflows—is fundamentally broken. Static documentation is dead code. It is obsolete the exact second a new pull request is merged into your main production branch.

As developers, we shouldn’t be spending our engineering cycles writing manual documentation. We should be building automated metadata processing systems that extract it natively from our environment. Here is a conceptual deep dive into how to leverage automated metadata extraction to reverse engineer legacy SQL and eliminate engineering tribal knowledge permanently.

The Core Technical Bottleneck: Hidden Lineage

When dealing with an enterprise-scale data estate, our primary architectural adversary is invisible data infrastructure. Pipelines grow organically over years, accumulating layers of views on top of views, ad-hoc patches, and cross-database joins. This systemic lack of visibility manifests in three concrete data team bottlenecks:

Impact Analysis Paralysis:

A developer needs to modify an upstream table schema, optimize a data type, or drop a redundant column. However, because the dependencies are opaque, the developer changes nothing. They are paralyzed by a rational fear that altering a single line of old SQL will trigger a silent data corruption error in a downstream business intelligence tool three layers away. As a result, technical debt compounds, performance degrades, and cloud compute costs surge unchecked.

The Stored Procedure Black Box:

Legacy data warehouse migrations to modern cloud platforms regularly stall during the discovery phase. This happens because extraction logic hidden inside thousands of lines of nested stored procedures behaves like a black box. Data teams spend weeks performing forensic manual parsing exercises, trying to interview upstream business units to guess what the original developer intended a decade ago.

Slower Developer Velocity:

When an engineering team spends more time figuring out where data comes from than actually writing transformation logic, innovation drops to zero. New senior data engineers spend their first four months playing detective rather than deploying high-leverage data products. They map dependencies on whiteboards, ask repetitive questions in Slack channels, and attempt to trace source-to-target pathways manually.

To break this cycle of operational friction, data engineering organizations must shift away from human data cataloging. We must extract reality directly from our primary database systems of record: Query Logs, Information Schemas, and Orchestration Repositories.

How do you auto generate data lineage from undocumented legacy code bases

To auto-generate data lineage, you must pass legacy SQL scripts, DDL statements, and query logs through an automated parsing engine. The engine tokenizes the code, maps column-level transformations, and structures the structural framework into an active data graph. This eliminates manual developer input and creates a self-healing system of record directly from the code repository.

The Concept Paradigm: Active Metadata vs. Static Catalogs

Understanding why traditional data catalogs fail requires distinguishing between passive metadata and active metadata infrastructure. A passive data catalog is a repository where information goes to die. It relies on data stewards or developers manually logging into a UI to type descriptions for tables and columns. It operates completely detached from the execution environment. The moment a pipeline schema changes via an automated migration script, the manual documentation is broken.

[Legacy Codebase] > [Automated Metadata Extraction] > [Active Metadata Layer]
                                                                │
                                                                ▼
                                                   [Dynamic Lineage Graph]

Active metadata, conversely, treats documentation as code. An active system intercepts running metadata directly from the execution layer. Instead of asking a developer what a pipeline does, an active metadata engine answers that question algorithmically by observing what the system actually executed.

When metadata is handled as structured, programmatic data rather than flat text files, it integrates natively with your existing data pipeline monitoring and DevOps stacks. It opens up several advanced capabilities:

- Programmatic Impact Previews: Integrating lineage checks directly into your CI/CD pipelines allows the system to automatically flag if a pull request will break a downstream analytics dashboard before the code is ever deployed.

- Redundancy Identification: Automated parsing engines routinely discover duplicate pipeline execution paths, orphan tables that consume storage but serve zero downstream reports, and redundant transformation steps that waste cloud compute budgets.

- Automated Governance: Instead of manually auditing tables for compliance, active metadata allows systems to automatically trace the flow of sensitive personally identifiable information (PII) from raw ingress points to consumption layers, ensuring complete security visibility.

The Abstracted Processing Lifecycle

To build or evaluate an automated metadata processing system, developers must understand the four primary phases of the programmatic extraction lifecycle:

Phase 1: Ingestion and Code Collection
The extraction engine gathers all raw code assets from source code management systems, database catalogs, and historical execution logs. This includes table creation DDL, view definitions, stored procedures, orchestration files, and query history logs. This collection must be comprehensive, as missing a single script can break the dependency graph downstream.

Phase 2: Lexing and Tokenization
The raw code is broken down into structured components. The parsing engine strips out irrelevant formatting, whitespace, and code commentary, converting the plain text scripts into a stream of logical tokens (such as Identifiers, Operators, Keywords, and Expressions).

Phase 3: Abstract Syntax Tree (AST) Generation
The tokens are assembled into a hierarchical tree structure based on the specific grammatical rules of the source database language (e.g., Teradata SQL, Oracle PL/SQL, or T-SQL). This structural tree isolates exactly how columns are manipulated, which functions are applied, and how tables are joined across complex conditional statements.

Phase 4: Graph Compilation and Visualization
The hierarchical syntax tree is translated into a graph database format, mapping concrete nodes (tables, views, columns, dashboards) and edges (data flows, joins, lookups). This final layer outputs an interactive, living lineage graph that can be queried programmatically by developers or browsed visually by business stakeholders.

Reclaiming Developer Velocity in Data Teams

Shifting from institutional memory to programmatic clarity immediately unlocks developer velocity in data teams. When an engineer can instantly check a live, column-level lineage graph before running an optimization script, the time spent performing impact analysis drops from days to seconds.

This transparency provides a reliable foundation for enterprise-scale platform modernizations. Teams can confidently approach major cloud database migrations because they possess an algorithmic, fact-based inventory of their entire data footprint. They no longer have to guess which pipelines are critical and which ones can be safely deprecated.

[Traditional Approach]:  Manual Forensic Analysis ──> Slow, Fragmented Migrations

[Automated Approach]:   Active Metadata Extraction ──> Rapid, Risk-Free Modernization

Ultimately, eliminating tribal knowledge is about optimizing the developer experience (DevEx). Data engineers are happiest when they are building new features, optimizing performance, and delivering clean data products—not when they are writing stale markdown documentation files. By deploying a dedicated AI documentation tool for engineering, organizations can free their senior architects from answering repetitive pipeline dependency questions, allowing them to focus exclusively on building scalable, high-leverage data systems.

Transitioning a complex, legacy estate into a documented, risk-free modern cloud environment requires moving beyond human discovery workshops. Utilizing structured frameworks like the 8-Day Modernization Canvas approach allows engineering leaders to systematically map out underlying pipeline dependencies, identify deep technical debt, and establish absolute structural readiness before writing a single line of target code.

For an open, in-depth look at how modern teams automate code translation, parse legacy database logic, and eliminate data debt natively, exploring 3X Data Engineering's Modernization Accelerators provides an excellent operational blueprint for enterprise grade data automation.

What Has to Happen Between a Business Question and a Trustworthy Natural Language Query Answer

Hariharan Arulmozhi — Fri, 21 Aug 2026 11:34:44 +0000

Ask a natural language query tool "what were our top products last quarter" and, in a demo, it usually works fine. The system parses the sentence, builds a query, and returns a chart. It looks close to magic on a narrow demo dataset.

Real enterprise data isn't a narrow demo dataset. The same question, asked against a warehouse with a decade of schema changes, three definitions of "quarter" depending on which system reported it, and a "top products" metric that finance and sales calculate differently, is a different problem entirely. That gap between a question a person can type in plain English and an answer a business can actually trust rarely shows up in a product walkthrough. It's also what determines whether a natural language query deployment holds up once it leaves the sandbox.

The short answer, up front: a trustworthy result depends less on how well the system understands your sentence and more on whether it can ground that sentence in your actual schema, your actual metric definitions, and a validation step that catches the answer before a person sees it. Here's why, and what that looks like in practice.

A Chat Box Is Not an Architecture

The instinct when evaluating a natural language query system is to test whether it understands the question. That's the wrong first test. Understanding the words in a sentence is close to solved for any modern language model. The real difficulty is understanding what those words mean inside your specific data environment, and that's a data problem, not a language one.

Take a simple question: "which region had the highest churn rate last month." A language model parses this without effort. Answering it correctly requires knowing which table defines "region" consistently across business units that may use different regional hierarchies, which column represents "churn" when the warehouse has three candidate columns with similar names and different meanings, and what "last month" means relative to a fiscal calendar that doesn't match the standard one. None of that lives in the sentence. All of it lives in metadata, and if that metadata is incomplete or inconsistent, the system has nothing solid to ground its answer in.

This is exactly the gap legacy SQL estates tend to have. Years of tribal knowledge about what a column really means, business logic buried in old ETL jobs and never documented, metadata that was never captured systematically: all of it shows up as ambiguity a language model can't resolve on its own, however capable that model is.

Five Things That Have to Happen Before an Answer Is Trustworthy

Breaking the process into stages makes the architecture decisions clearer than treating the whole thing as one black box.

The first stage is intent parsing: identifying whether the question is a comparison, a trend, a ranking, or a value lookup. This is largely language-model territory, and it's the most mature part of the stack today.

The second stage is semantic grounding, where the parsed intent gets mapped onto real enterprise concepts: which tables, columns, and business definitions actually apply. This depends entirely on the quality of the underlying metadata, and it's where most failures actually originate, even though the failure usually shows up downstream as a wrong number rather than an obvious error here.

The third stage is query construction, where the system builds the actual logic once it knows which tables and columns are relevant. Schema complexity, join paths, and platform quirks all live here.

The fourth stage is validation. Before an answer reaches anyone, the result should get checked against sanity constraints: does the row count look plausible, does the aggregate fall within a reasonable range against historical values. This stage gets skipped constantly in early implementations, and it's the single biggest predictor of whether a system stays trustworthy over time.

The fifth stage is response formatting: turning a result set back into a chart, table, or summary that actually answers the question asked, not just a dump of whatever the query returned.

A Finance Question That Looks Easy Until You Look Closer

Picture an operations leader typing "which warehouse had the most delayed shipments this quarter." The system correctly identifies this as a ranking question and correctly maps "warehouse" to a location dimension table. But the company has two shipment tracking systems left over from an acquisition that were never fully merged, and "delayed" is defined differently in each: one measures against the promised delivery date, the other against the originally scheduled one.

If the semantic layer hasn't resolved which definition applies, or silently picks one without surfacing that choice, the leader gets a confident, specific answer that may not reflect what they're actually trying to understand. Nothing about the interaction looks wrong. The chart renders cleanly. The number is precise. It might just be measuring the wrong thing, and there's no way to know that from the interface alone.

That's the real risk profile of natural language query at enterprise scale: not systems that obviously fail, but systems that confidently answer the wrong version of the question.

What to Actually Check Before You Trust the Answer

A few questions are worth running through before you rely on any natural language query system, whether you're building one or evaluating a vendor.

Is business terminology mapped to actual schema objects, or does the system guess from column names alone? Guessing produces plausible-looking wrong answers, worse than obviously broken ones because nobody thinks to double check them. Are business metrics defined once and reused consistently, or scattered across teams with silent variations, where two people can ask what sounds like the same question and get two different, both defensible, answers?

Does every generated query get checked against sanity constraints before the result is shown? Without this, errors surface as business decisions rather than visible system failures, a far more expensive place to catch a mistake. When a term has more than one valid interpretation, does the system ask for clarification or quietly choose one? And can a person actually see what data and logic produced a given answer? Without that, trust depends entirely on the system never being wrong, which isn't sustainable at enterprise scale.

The practical implication follows directly: build the semantic layer before the conversational interface, not after, since a polished chat experience sitting on thin metadata produces confident wrong answers faster than useful ones. Treat ambiguity as a signal to ask, not to guess, and test against real historical edge cases from your own data rather than a clean demo scenario, since demo data rarely contains the ambiguity a decade of real operations accumulates.

Where a Good-Looking Answer Can Still Be Wrong

A few patterns show up repeatedly in enterprise deployments. Treating natural language query as primarily a language modeling problem is the most common one: that part is largely solved, while the metadata and semantic grounding part usually isn't, and that's where things actually break.

Skipping validation because query generation "looked right" during testing is another, since a clean sample dataset almost never surfaces the ambiguity that years of real data accumulate. Assuming one semantic layer definition will satisfy every business unit is a third mistake: different teams often have genuinely different, both valid, definitions of the same term, and the architecture needs to surface that rather than silently picking a winner. And launching without an audit trail tends not to matter until the day someone makes a real decision based on an answer and needs to explain how that number was produced.

What Changes as These Systems Mature

As natural language query systems mature, the competitive difference between implementations is going to come down almost entirely to the quality of the metadata and semantic layer underneath them, not the sophistication of the model doing the parsing. The parsing problem is converging toward being a commodity. The grounding problem, understanding what your enterprise's data actually means, is where the durable engineering work still lives, and legacy, poorly documented data estates make that work significantly harder than a clean, well modeled warehouse would.

Getting This Right Is an Architecture Decision, Not a Feature Choice

A natural language query interface is only as trustworthy as the architecture standing behind it. The conversational layer is the visible part, but it's rarely the part that determines whether an answer is actually correct. Getting this right means treating metadata quality, definition consistency, and validation as first class engineering concerns from the start, not implementation details to handle once the demo works.

If you've deployed something like this against real enterprise data, where did the gap between demo and production actually show up for you: metadata, ambiguity, or something else entirely?

If you want to see how this kind of architecture gets approached in practice, 3X Data Engineering's Ask Data accelerator is a useful concrete reference point for how schema grounding and validation fit together.

Why Generic AI Coding Copilots Fail at Data Engineering

Hariharan Arulmozhi — Thu, 13 Aug 2026 13:59:23 +0000

The Copilot That Didn't Know What It Didn't Know

A few months ago, I watched a data engineer ask an AI coding assistant to refactor a transformation script that joined five tables and computed a rolling revenue metric. The assistant produced clean, well formatted code in seconds. It also silently changed a LEFT JOIN to an INNER JOIN because the rewritten version looked more idiomatic. Nobody caught it until a downstream report started showing numbers that didn't reconcile, three weeks later.

This isn't a story about a bad tool. GitHub Copilot, Cursor, and similar assistants are genuinely useful for what they were built for: completing functions, suggesting boilerplate, catching obvious syntax errors. The problem is that a lot of teams have quietly started treating them as a general-purpose solution for data engineering work too, and the failure mode above is exactly what happens when you do that.

Why the Same Approach Doesn't Transfer

Software engineering and data engineering look similar from a distance. Both involve writing code, reviewing it, testing it, deploying it. The similarity stops there, and the differences matter enormously for what an AI tool can safely automate.

Application code is largely stateless and self contained. A function takes inputs, returns outputs, and its correctness rarely depends on what happened in a different part of the system last Tuesday. Data pipelines are the opposite. They are schema bound, meaning the same code behaves differently against a different schema. They are stateful, meaning a bad transformation contaminates every downstream table that reads from it. And their correctness depends on business logic that lives in the data itself, not just in the code, which a general purpose model has no way to infer by reading a script in isolation.

A generic copilot reads code linearly, token by token, the same way it would read a JavaScript file. Data engineering is fundamentally a graph problem: tables depend on views, views depend on other views, jobs run in a specific order for reasons that aren't written down anywhere. A tool with no awareness of that graph will confidently generate syntactically correct code that is semantically wrong, and it will do so without any signal that something is off.

Where Generic Copilots Fail in Data Engineering

Three patterns come up constantly once teams start relying on generic copilots for pipeline work.

Join and Filter Drift

This is the pattern from the opening story. A model asked to "clean up" or "modernize" a query will often make small structural changes that look like improvements but change the result set. It has no way of knowing that the original LEFT JOIN was intentional, because that intent lived in a person's head, not in the syntax. From the model's perspective, an INNER JOIN and a LEFT JOIN are both valid SQL. Only one of them preserves the rows the business actually needed.

Type and Null Handling

Legacy SQL dialects handle nulls, implicit casting, and rounding differently from modern cloud platforms. A copilot converting T-SQL to Spark SQL will produce something that runs without error far more often than it produces something that computes the same result, and the two are not the same thing. A query that "runs clean" has told you nothing about whether it's numerically equivalent to what it replaced. Silent precision loss and null coercion don't throw errors. They just quietly change the numbers.

Dependency Blind Spots

Ask a generic assistant to modify a stored procedure and it will happily do so without knowing that four other objects call it, or that a nightly job assumes it runs in a specific order relative to two other jobs. It isn't being careless. It genuinely cannot see what it was never given. A model working from a single file has no access to the object's place in the broader dependency graph, so it optimizes locally in a way that can break something several hops downstream.

What a Purpose-Built Approach Looks Like

The fix isn't to stop using AI on data engineering work. It's to change what the AI is given to reason over, and what checks run on what it produces before anything reaches production.

Context Before Generation

A purpose-built approach feeds the model schema definitions, table relationships, and known business rules before it generates or modifies anything, rather than asking it to infer all of that from a single script. This is the difference between a model guessing at intent and a model working from actual metadata about how the estate fits together.

Validation Before Production

Every AI-generated transformation should go through a check that compares data behavior, not just syntax, against the original. In practice this means comparing row counts between the source and the converted version, checking whether null-preserving rows survived the conversion, and flagging anything where the row count dropped unexpectedly. A drop in row count after a "cleanup" pass is one of the clearest signals that a join type changed underneath you. This kind of check would have caught the join change from the opening story automatically, well before it reached a production report. The underlying principle matters more than any specific check: AI-generated data engineering code needs a validation layer that understands data behavior, not just a human glancing at whether the syntax looks reasonable.

Dependency and Lineage Awareness

Before an AI assisted change touches any object, something needs to answer a simple question: what else depends on this? That means pulling the list of referencing objects, whatever else queries or calls the thing about to be modified, and putting that list in front of the model, and the human reviewing the change, before the change happens. Feeding that dependency context in ahead of time means the model is no longer working blind. It knows what else might break. That single addition changes the risk profile of AI assisted data engineering work more than any amount of prompt tuning does.

Common Mistakes Teams Make

Trusting confident output as correct output. A model that produces clean, well formatted SQL has told you nothing about whether the SQL is semantically equivalent to what it replaced.

Treating copilot suggestions and pipeline conversion as the same category of task. They require fundamentally different levels of context and validation, even though they both involve writing code.

Skipping dependency analysis because "the AI seemed to understand the query." Understanding a single query and understanding its place in a broader dependency graph are different things, and a model's fluent explanation of the former doesn't imply the latter.

No reconciliation step after AI assisted conversion. If you can't compare row counts, aggregates, and a sample of row level hashes between the original and the AI generated version, you don't actually know if the conversion worked.

What Changes as Agentic AI Becomes More Autonomous

The gap between generic copilots and purpose built data engineering acceleration is going to keep widening, not close. As more teams adopt agentic AI tools that take multi step actions instead of just responding to prompts, the cost of an ungrounded assumption compounds across every step the agent takes on its own. An agent that makes one bad assumption about a join and then acts on that assumption three more times downstream, without a human in the loop, turns a small error into a systemic one. The tools that will actually hold up are the ones that treat metadata, lineage, and dependency graphs as first class inputs, not as details a model is expected to infer from a single file.

Conclusion

None of this is an argument against using AI in data engineering. It's an argument for being precise about which AI, given which context, for which task. A copilot that reads code linearly will keep making the same category of mistake regardless of how capable the underlying model gets, because the problem was never model capability. It was the absence of the schema, dependency, and lineage context that data engineering work actually requires to be done safely.

What's your experience been? Have you caught an AI generated pipeline change that looked fine but wasn't? I'd like to hear the specific failure mode, because I suspect most of us are seeing variations of the same three patterns.

Learn More

For a closer look at how dependency aware automation applies across a full legacy estate, see 3X Data Engineering's AI-Augmented Data Engineering overview.

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.