Migrate Azure Synapse to Databricks.

Why Azure Synapse to Databricks is four migrations, not one

An Azure Synapse Analytics workspace looks like one product, but it’s a collaboration boundary that bundles four distinct workloads, each with its own engine: Synapse pipelines, Spark notebooks, dedicated SQL pools, and serverless SQL. Treating them as a single thing is the first mistake a naive lift-and-shift makes. The pipelines are the same JSON model as Azure Data Factory; the notebooks run on serverless Apache Spark pools and write Delta; the dedicated SQL pool is a massively parallel T-SQL warehouse spread across 60 distributions; and serverless SQL is an on-demand query engine that stores nothing and reads files in place. Moving the estate onto Databricks means handling all four, and the difficulty is wildly uneven across them. The agent fleet documents each workload on its own terms first, then rebuilds: autonomous in the loop, accountable at the three human gates of assessment, design, and promotion.

The good news for this pair is that one workload is a genuinely natural fit. Synapse Spark notebooks already run Apache Spark and already write Delta, so they map to Databricks notebooks more closely than to almost any other target. A Spark-notebook-heavy Synapse estate lands on the lakehouse with the least friction of any source we migrate. The hard parts are the SQL pools, where Synapse’s physical design has no Databricks equivalent and must be re-derived rather than copied.

What breaks in a naive lift-and-shift

The dedicated SQL pool is where the copy-paste instinct does the most damage. Its performance model is built entirely around the 60-distribution MPP architecture: you choose a HASH distribution column so co-located joins avoid data movement, you make staging tables ROUND_ROBIN for fast loads, you make small dimensions REPLICATED so a full copy is cached on every compute node, and you partition on a single column (usually a date) so you can do fast partition switching and partition elimination. On Databricks none of these concepts exist. Delta Lake on the lakehouse has no distribution layer; physical layout is handled by liquid clustering and OPTIMIZE, and the Spark optimizer decides broadcast joins on its own, so the REPLICATED choice simply evaporates. Porting DISTRIBUTION = HASH(...) as if it were meaningful Delta DDL produces tables that are at best ignored and at worst misleading. The fleet reads why each distribution and partition choice was made (the access patterns behind it) and re-derives a Delta layout from those patterns.

The dialect breaks too. Even though dedicated-pool T-SQL is already a reduced subset (an 8-level nesting cap instead of 32, no @@NESTLEVEL, no INSERT...EXECUTE, constrained scalar UDFs, blob-type limits that force dynamic SQL to be chunked and run with EXEC()), it is still T-SQL, not Spark SQL. ISNULL becomes coalesce/nvl, GETDATE() becomes current_timestamp(), TOP n becomes LIMIT n, and [bracketed] identifiers become backticks. IDENTITY(1,1) surrogate keys do not behave like Delta’s GENERATED ... AS IDENTITY under parallel writes. Every T-SQL function gets checked against the Spark SQL built-in set; some have no direct equivalent and need rewriting, and the fleet catches these in the loop, not in a hand-off document.

Serverless SQL has its own trap. Because it is compatible with Delta reader version 1 only, a Synapse logical data warehouse may have been built around that ceiling: no deletion vectors, no column renames, no v2 checkpoints. The lakehouse imposes no such limit, so the rebuild as Unity Catalog external tables and views over the same Delta usually gets simpler, but the fleet still has to recognise where the old constraint shaped the design. And the deepest risk is the coupling between workloads: in Synapse the real logic lives across the seams, where a Spark notebook lands Delta, serverless SQL exposes views over it, the dedicated pool holds the modeled warehouse, and pipelines orchestrate the lot. Type mismatches, Delta-version compatibility, and schema-sync delays all hide at those Spark → serverless → dedicated boundaries.

How the fleet handles it

The Documenter reads the logic, not the labels, across all four workloads and follows data across the boundaries rather than documenting each pool in isolation. That inventory (the knowledge base) is what a human signs off at the assessment gate. For Synapse pipelines, the activity graph and its dependsOn conditions (Succeeded, Failed, Skipped, Completed) are reconstructed and re-expressed as Lakeflow Jobs tasks, dependencies, and triggers. For notebooks, the close-fit Spark logic is ported while %run versus mssparkutils.notebook.run chaining and mssparkutils utility calls are reworked for Databricks. For the dedicated pool, layout is re-derived for Delta and SCD2 history that was hand-rolled in T-SQL becomes either dbt snapshots (with dbt_valid_from / dbt_valid_to) on the dbt flavor or an explicit Delta MERGE INTO on the your-framework flavor. For serverless SQL, query-in-place views become Unity Catalog tables and views.

Whichever flavor you choose, the builders generate, the Test agent runs the suite, and the build-test-run loop iterates until green: row counts, SCD2 valid-from/valid-to spans, and aggregate parity all have to match source before anything reaches the promotion gate. Counts come from config, and you get to inventory in minutes. If your destination is Microsoft’s own platform instead, Migrate Azure Synapse to Microsoft Fabric follows the same fleet and the same gates onto Fabric.