Azure Synapse to Databricks SQL Conversion using LLMs — A Practical POC

Introduction

Migrating SQL workloads from Azure Synapse Analytics to Databricks is a common challenge in modern data platforms. While both systems are powerful, their SQL dialects differ in subtle but important ways — function syntax, date handling, aggregation patterns, and system-specific behaviors.

At scale, these differences turn migration into a time-consuming and error-prone exercise, often requiring:

• Manual query rewrites
• Custom rule-based transformation engines
• Extensive validation cycles

Traditional approaches rely heavily on static mappings and scripts, which quickly become difficult to maintain as query complexity increases.

To explore a simpler alternative, I built a lightweight Proof of Concept (POC) using a Large Language Model (LLM) hosted directly within Databricks. Instead of relying on rule-based transformations, this approach leverages the reasoning capability of models like Llama 3.1 405B Instruct to understand and rewrite SQL across dialects.

The goal was intentionally minimal:

Can we convert Synapse SQL to Databricks SQL using just a model + prompt — without complex frameworks — and still maintain clarity, reliability, and measurable cost?

The answer: Yes — and surprisingly effectively.

This POC was executed entirely within the Databricks environment using a model serving endpoint, with no additional infrastructure or external dependencies.

Objective

This POC was designed with strict principles:

• Keep the logic simple and transparent
• Avoid rule engines or heavy parsing frameworks
• Use native Databricks capabilities
• Produce clean, review-ready SQL output
• Provide basic observability (token usage)

This is not a production system — it’s a capability demonstration.

Architecture Overview

Synapse SQL Input ↓ Prompt Construction ↓ LLM Inference (Databricks Model Serving) ↓ Databricks SQL Output (HTML Rendering) ↓ Token Usage

The solution follows a clean, linear pipeline:

1. Synapse SQL Input

The user provides a Synapse SQL query.

Example:

SELECT TOP 3 WITH TIES
    employee_id,
    salary
FROM dbo.employees
ORDER BY salary DESC;

2. Prompt Construction

A carefully designed system prompt guides the model to:

• Convert Synapse-specific syntax
• Preserve query intent
• Generate valid Databricks SQL
• Avoid explanations (SQL-only output)

Key transformation rules included:

Date Functions

• GETDATE() → current_timestamp()
• DATEDIFF adjustments

String Functions

• ISNULL → coalesce
• LEN → length

Aggregation

• STRING_AGG → concat_ws(',', collect_list(col))

Row Limiting

• TOP n → LIMIT n
• TOP WITH TIES → RANK() / DENSE_RANK()

👉 The prompt replaces what would traditionally be hundreds of transformation rules.

3. LLM Inference

The prompt is sent to a hosted model endpoint in Databricks using Llama 3.1 405B Instruct

What is this model?

This is a large-scale instruction-tuned language model based on the Llama 3.1 family.

• 405B parameters → one of the largest openly available models
• Instruction-tuned → optimized to follow structured prompts
• General-purpose → capable of reasoning, transformation, and code generation

The model:

• Understands the intent of the query
• Applies dialect transformations
• Generates equivalent Databricks SQL

How it’s used in this solution

The model is accessed via a Databricks Model Serving endpoint, which allows you to:

• Send structured prompts (system + user messages)
• Control behavior using parameters (e.g., temperature)
• Receive generated outputs in real time

Key configuration used:

• Temperature = 0 → Ensures deterministic and repeatable outputs
• Max Tokens = 2000 → Supports moderately complex SQL queries

4. Databricks SQL Output

The notebook displays:

• Input Synapse SQL
• Generated Databricks SQL

Rendered using HTML for a clean demo experience.

Sample output

5. Token Usage

Instead of estimating cost (which varies in Databricks), the POC exposes:

• Prompt tokens
• Completion tokens
• Total tokens

This provides:

• Transparency
• A proxy for cost
• Insight into model behavior

🎯 Why This Approach Works

Simplicity

No complex architecture, no dependencies — easy to understand and demo.

Determinism

Using temperature = 0 ensures consistent outputs across runs.

Flexibility

Prompt can be extended to support more patterns without changing code.

Speed

End-to-end conversion happens in seconds.

⚠️ Limitations

This is important for credibility.

• Output is not guaranteed to be 100% correct
• Complex edge cases may need manual adjustments
• No validation or execution testing is included
• Some Synapse features may not map directly

Execution Environment

This POC was implemented using:

• Databricks Free Edition
• Model Serving endpoint
• Notebook-based execution

🚀 Key Takeaways

• LLMs can significantly reduce SQL migration effort
• Prompt engineering can replace complex transformation logic
• Simpler architectures are more effective for quick wins / early-stage solutions
• Transparency (tokens vs cost) improves trust in demos

🧠 Final Thoughts

This POC demonstrates a shift in how we approach data engineering problems. Instead of building increasingly complex systems, we can:

Leverage intelligent models with simple pipelines to achieve meaningful results faster

While not a replacement for full-scale migration tools, this approach:

• Accelerates development
• Reduces manual effort
• Opens new possibilities in SQL modernization

Try it yourself

Code:

https://github.com/msprasad83/SynapsetoDBX

Databricks Free edition:

Free Edition | Replacing Databricks Community Edition

References:

A New Standard in Open Source AI: Meta Llama 3.1 on Databricks | Databricks Blog