Azure Synapse Analytics to Databricks migration

A guide to the assessment and migration of Synapse workloads to Azure Databricks. The target uses Unity Catalog, separate environments, Private Link, Terraform, and DABs.

Read the related guidance for platform architecture, Terraform and DABs, Unity Catalog grants, and identity.

Why organizations migrate from Synapse#

Microsoft has not announced the full retirement of Azure Synapse Analytics. Synapse follows the Modern Lifecycle Policy. Check the linked lifecycle page before you plan a migration.

All new investment goes to Microsoft Fabric. Every dedicated SQL pool doc page now opens with a tip steering new warehousing to Fabric Data Warehouse and links a "dedicated SQL pool workloads can upgrade to Fabric" migration path plus a Fabric Migration Assistant. Fabric (GA Nov 2023) is positioned as the successor. Synapse is in maintenance, not development.
Targeted component retirements are real. Synapse Data Explorer (preview) retired Oct 7 2025 (→ Fabric Eventhouse); Compute Optimized data flows retired Aug 31 2024; Synapse Link for Cosmos DB is closed to new projects (→ Fabric mirroring). The pattern is piecemeal deprecation, not a single EOL date.
The roadmap has changed. Microsoft directs new warehousing investment to Fabric, but existing Synapse services continue to operate. Base the migration decision on requirements, cost, and target architecture. Do not claim that Microsoft will retire Synapse next year.

Cost and capability drivers that hold up independently of the roadmap:

Always-on DWU cost. Dedicated SQL pool is billed on provisioned Data Warehouse Units. Teams pay to keep a pool warm or take on the operational burden of pause/resume scheduling. Databricks SQL serverless warehouses start and stop in seconds and auto-stop on idle.
Open format and one platform. Delta/Parquet in your own ADLS, queryable by Spark, SQL, and ML, instead of proprietary dedicated-pool storage. No separate Spark pool, SQL pool, and Synapse pipeline silos.
ML and data science. Synapse Spark pools are a bolt-on; Databricks is one governed lakehouse for ELT, BI, and ML/AI on the same tables.

Component mapping#

Synapse	Databricks target	Notes
Dedicated SQL pool (T-SQL, DWUs)	Delta tables + SQL warehouse (serverless)	Distribution/index concepts disappear; see below
Serverless SQL pool (query over lake)	Databricks SQL / external tables / UC volumes	Point a SQL warehouse at the same ADLS files
Spark pools	Job/serverless clusters	DBR Spark, Photon, governed by UC
Synapse Pipelines / mapping data flows	Lakeflow Jobs + DABs, or keep ADF	Tradeoff below
PolyBase / COPY INTO	COPY INTO, Auto Loader, Lakeflow Connect	Match the tool to the load pattern
Synapse RBAC + SQL perms	Unity Catalog grants + account/workspace model
Managed VNet + managed PE	VNet injection + Private Link

Dedicated SQL pool → Delta + SQL warehouse#

This is the core of the work. Dedicated pool is an MPP system: a Control node fans T-SQL across 60 distributions, and the physical design (distribution method, clustered columnstore index) is what you tune. On Databricks, most of that physical tuning disappears. This is a major architecture change for teams that operate dedicated SQL pools.

Dedicated pool concept	Databricks equivalent	What changes
`DISTRIBUTION = HASH(col)`	Nothing to declare	Spark shuffles per query; no fixed distribution column. Optionally `CLUSTER BY` the former hash key
`DISTRIBUTION = ROUND_ROBIN`	Default	Just a Delta table
`DISTRIBUTION = REPLICATE`	Broadcast join (automatic)	Photon/AQE broadcasts small dims; no replicated table type
`CLUSTERED COLUMNSTORE INDEX`	Delta (Parquet columnar)	Columnar + compression is the default storage
Partitioning + index tuning	Liquid clustering (`CLUSTER BY`)	Redefine keys without rewriting data; replaces partitioning and ZORDER
`CTAS ... WITH (DISTRIBUTION=...)`	`CREATE TABLE ... AS SELECT`	Drop the `WITH (DISTRIBUTION/INDEX)` clause
Statistics maintenance, index rebuilds	Predictive Optimization	Auto OPTIMIZE/clustering on UC managed tables

A key difference is that an incorrect Synapse distribution column requires a full table rewrite. Liquid clustering keys can be changed without rewriting data (GA on Delta since mid-2024, DBR 15.4 LTS+; default recommendation over partitioning + ZORDER). That removes the highest-stakes design decision in the old model. Set CLUSTER BY on the columns the team used to hash-distribute or filter on, enable Predictive Optimization on UC managed tables, and let it adapt.

Serverless SQL pool → Databricks SQL / external tables#

Serverless pool is pay-per-TB-scanned ad hoc T-SQL over files in the lake (often via OPENROWSET / external tables). Map to a Databricks SQL warehouse reading the same ADLS data through Unity Catalog external tables or volumes. If the files are already Parquet/Delta, this is mostly re-pointing and re-granting, not a data move.

Spark pools → clusters / serverless compute#

Synapse Spark pool notebooks port to Databricks notebooks or jobs. Watch for: mssparkutils → dbutils; spark.read paths from abfss:// carry over; linked services / TokenLibrary auth → UC volumes and credentials. Prefer serverless or job compute under a cluster policy. See Databricks platform lessons for policy guidance. For an existing classic compute environment, review Spark Connect compatibility before you select serverless compute.

Pipelines / mapping data flows → Lakeflow Jobs + DABs, or keep ADF#

Two valid paths exist. Record the tradeoff before you select a path.

Re-platform to Lakeflow Jobs + DABs (recommended target). Orchestration lives with the workload, version-controlled in a bundle, owned by data engineering. Mapping data flows become Spark/SQL transforms in Lakeflow Declarative Pipelines. This is the clean end state and aligns with the guidance to limit platform services.
Keep ADF / Synapse pipelines, point at Databricks. ADF has a native Databricks activity (notebook/jar/python). Lowest migration friction; lets a team move compute first and orchestration later. Justified when there are many pipelines, non-Databricks activities (Logic Apps, on-prem copy), or a team that owns ADF and is not ready to move. The cost is a second orchestration plane to keep in sync. Treat it as a transition state with a sunset date, not the destination.

Recommendation: lift orchestration to DABs for net-new and high-value pipelines; leave low-value legacy pipelines on ADF pointing at Databricks until they age out.

PolyBase / COPY INTO → COPY INTO / Auto Loader / Lakeflow Connect#

Source pattern	Databricks tool
One-time / batch file load from ADLS	`COPY INTO` (simple) or Auto Loader (scalable)
Continuous file arrival in ADLS	Auto Loader (`cloudFiles` / `read_files`), default in Lakeflow Declarative Pipelines
CDC from SQL Server / Azure SQL	Lakeflow Connect SQL Server connector (gateway + landing volume + serverless pipeline)
SaaS sources (Salesforce, Workday)	Lakeflow Connect managed connectors (direct-to-Delta)

Use COPY INTO for simple idempotent batch loads. Use Auto Loader for continuous file arrival or directories with very large file counts. Auto Loader can run inside Lakeflow Declarative Pipelines so the pipeline manages the schema and checkpoint state. Use abfss:// because WASB is deprecated. For ongoing replication from a SQL Server source during a phased cutover, Lakeflow Connect supports change tracking and CDC. Its gateway needs classic compute, and its pipeline runs on serverless compute.

Security mapping#

Synapse mixes two control planes: Synapse workspace RBAC (Azure roles, Synapse Studio roles) and SQL-engine permissions on the logical SQL server (SQL logins/users, GRANT, database roles) which are where dedicated-pool data access actually lives. Databricks consolidates this into Unity Catalog at the account level. Read Identity with Entra ID and SCIM and Unity Catalog grants.

Synapse	Databricks / Unity Catalog
Synapse RBAC roles (workspace)	Account + workspace admin model
SQL `GRANT`/`DENY`, DB roles, schema perms	UC `GRANT` on catalog/schema/table to groups
Entra ID users/groups	AIM-managed account groups; use SCIM only when AIM does not apply
Workspace managed identity (pipeline/ADF auth)	Access Connector managed identity for storage
SQL logins / `CREATE USER FROM EXTERNAL PROVIDER`	Entra group → account group, grant on group
Column/row security, dynamic data masking	UC row filters + column masks / ABAC. Read PII and ABAC governance

Translation rules that matter in practice: grant to groups, not users; run production jobs as service principals scoped to the catalogs they need; storage access is a UC managed identity on an Azure Databricks Access Connector, not a SQL credential. Map each dedicated-pool database role to a UC group and re-derive its grants; do not try to replicate SQL-server-level logins one-for-one.

Networking#

Synapse	Databricks
Managed workspace VNet (Synapse-managed)	VNet injection — your VNet, your subnets
Managed private endpoints (Synapse creates)	Private endpoints you create (or NCC for serverless)
Auto private links to multitenant SQL pools	Front-end + back-end Private Link to the workspace
Data exfiltration protection	Private Link + storage firewall + UC external locations

Use the platform network guidance: you cannot resize a Databricks subnet once assigned — size generously (/21 per VNet has proven plenty). Clusters do not need public IP addresses with a NAT or firewall egress route.

Budget the workspace on the Premium tier. Azure has only Standard and Premium — no Enterprise tier — and every Private Link leg requires Premium. Standard is also end-of-life: Premium-only for new workspaces since 2026-04-01, and existing Standard workspaces upgrade automatically on 2026-10-01. Private Link still pays for itself on egress, because every non-serverless cluster pulls a ~15 GB control-plane image at start and that NAT data-processing cost adds up.

ADLS access uses the chain: external location → storage credential → Access Connector managed identity → Storage Blob Data Contributor on the account. Two requirements need special attention: (1) the ADLS Gen2 private endpoint sub-resource must be dfs (hierarchical namespace), and the dfs privatelink DNS zone must resolve; (2) with a storage firewall, the UC control plane needs access for metadata operations, so enable "Allow Azure trusted services." Serverless compute reaches storage via a Network Connectivity Config (NCC), not the injected VNet — plan both paths.

Schema & T-SQL conversion specifics (dedicated pool)#

Dedicated pool syntax	Databricks SQL	Action
`WITH (DISTRIBUTION = HASH(k), CLUSTERED COLUMNSTORE INDEX)`	(omit) optionally `CLUSTER BY (k)`	Strip the `WITH` clause; add liquid clustering if k is a hot filter/join key
`CTAS ... WITH (...) AS SELECT`	`CREATE TABLE t AS SELECT ...`	Drop distribution/index options
`IDENTITY(1,1)` surrogate keys	Delta `GENERATED ... IDENTITY`, or `monotonically_increasing_id` / hash key	Synapse IDENTITY is per-distribution, not globally sequential, and not guaranteed unique
`SELECT ... INTO`	`CREATE TABLE AS SELECT`	Direct
`sys.dm_pdw_*` DMVs, `DBCC PDW_SHOWSPACEUSED`	System tables / `DESCRIBE DETAIL`	Rewrite ops queries
`GETDATE()`, `ISNULL`, `LEN`, `TOP n`, `[brackets]`, temp `#tables`	`current_timestamp()`, `coalesce`/`nvl`, `length`, `LIMIT n`, backticks, temp views	Dialect translation
Stored procedures, `MERGE`, control flow	Delta `MERGE`, SQL UDFs, notebooks/Spark	Procedural logic often becomes Lakeflow steps

Surrogate keys need special attention. Dedicated-pool IDENTITY increments independently per distribution and gaps are normal. It cannot be a CTAS target. Do not require globally sequential keys when the old warehouse did not guarantee them. Use Delta generated identity columns where a sequence is necessary. Alternatively, use a deterministic hash surrogate (xxhash64/sha2 over business keys), which also makes reconciliation easier.

Tooling. Databricks Lakebridge (free, open, Databricks Labs — verify the current release on GitHub before you plan its use) is the assessment and conversion accelerator. It now has a Synapse profiler for the assessment phase and LLM-assisted T-SQL → Databricks SQL conversion (Analyzer / Converter / Validator; transpilers BladeBridge, Morpheus, Switch). It is provided AS-IS with no Databricks SLA, so scope manual remediation into every estimate. Lakebridge accelerates; it does not eliminate the conversion line item.

Data migration#

Bulk export. Export dedicated-pool tables to Parquet/Delta in ADLS. CETAS (CREATE EXTERNAL TABLE AS SELECT) from the pool to ADLS, or Spark read of the pool, is the usual path. Land in a staging container.
Stage in ADLS. One staging area, governed as a UC external location. Bronze ingestion reads from here (Auto Loader / COPY INTO).
Load to Delta with the medallion model from Databricks platform lessons: bronze schema-per-source, silver/gold schema-per-data-product, per environment.
Ongoing CDC during parallel run. For sources still feeding Synapse, set up incremental ingestion (Lakeflow Connect CDC for databases, Auto Loader for files) so Databricks stays current until cutover.
Cutover. Repoint BI (Power BI / clients) to the SQL warehouse, freeze writes to the pool, do a final delta sync, validate, switch.

Phased delivery#

A workspace-per-environment platform (development, test, and production) must exist before migration workloads land — catalogs, groups, policies, service principals, networking through Terraform; workloads through DABs. Do not start with notebooks.

Phase	Work	Deliverables
0. Assessment	Run Lakebridge Synapse profiler; inventory pools, pipelines, Spark, permissions, and networks; rank workloads	Assessment report, target architecture, ROM cost and effort, migration backlog
1. Platform foundation	Terraform: workspaces, UC metastore, catalogs, VNet injection, Private Link, access connector, Entra groups, and policies	Platform, IaC repository, networking diagram
2. Pilot workload	Migrate one representative schema + pipeline end to end; prove conversion + reconciliation	Working pilot, conversion playbook, validation harness
3. Bulk migration	Convert schemas/T-SQL (Lakebridge + manual), port pipelines to DABs or repoint ADF, backfill data	Migrated tables/jobs in DABs, CI/CD
4. Validation & cutover	Row-count + aggregate + checksum reconciliation; repoint BI; parallel run; switch	Reconciliation sign-off, runbook, cutover record
5. Decommission	Pause then delete dedicated pool, retire Synapse workspace, remove redundant ADF	Decommission checklist, cost-savings report

Validation / reconciliation. Reconcile per table: row counts, control-total sums on numeric columns, and row-level checksums (hash of business columns) across source and target. Lakebridge has a Validator/reconciliation step; budget manual reconciliation for transformed tables where a 1:1 hash does not match. Get written sign-off per data product before cutover. Run source and target in parallel long enough for the business to trust the numbers.

Scoping inputs: number and size of dedicated pools (DWU + TB); count of tables, stored procedures, and pipelines/data flows; T-SQL complexity (procedural logic, dynamic SQL, MERGE-heavy); BI surface to repoint; networking constraints (Private Link, exfiltration protection); whether ADF stays.