Azure Synapse Analytics to Databricks: a consulting playbook

A practitioner's guide to scoping and running Synapse → Azure Databricks migrations. Written for offering this as a service, not for any one client. Target architecture follows the hub: Unity Catalog, env + medallion catalogs, VNet injection + Private Link, Terraform for the platform and DABs for workloads. See lessons-learned/platform-lessons.md, best-practices/terraform-vs-dabs.md, best-practices/unity-catalog-grants.md, and best-practices/identity-entra-scim.md instead of re-deriving those here.

Why companies move off Synapse#

Be accurate. Microsoft has not announced a full retirement or EOL for Azure Synapse Analytics, and there is no "no new customers" policy. Synapse follows the Modern Lifecycle Policy and Microsoft says it has no plans to retire it (verify against the lifecycle page before quoting a client). What is true:

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.
So the practical client framing is: "your platform still runs, but it is a dead end. Microsoft's roadmap is Fabric, a SaaS model with its own lock-in. If you'd rather land on an open lakehouse you control, now is the planning window." That is the honest pitch. Do not tell a client Synapse is being shut off 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, which is the single biggest mental-model shift to sell the client.

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

Key point for clients: in Synapse, getting the distribution column wrong means 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 platform-lessons.md on sane defaults). For an existing classic-cluster estate, the databricks-serverless-migration skill covers Spark Connect compatibility.

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

Two valid paths. State the tradeoff explicitly; do not pick silently.

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 "resist service sprawl" in platform-lessons.md (don't keep ADF if DABs already orchestrates Databricks).
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 isn't 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. See best-practices/identity-entra-scim.md and best-practices/unity-catalog-grants.md.

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 (see `governance-pii-abac.md`)

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

Carry over the hard-won lessons from platform-lessons.md: you cannot resize a Databricks subnet once assigned — size generously (/21 per VNet has proven plenty). Clusters don't need public IPs with a NAT/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 gotchas: (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 — clients often already tolerate gaps
`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 are the sharpest gotcha. Dedicated-pool IDENTITY increments independently per distribution and gaps are normal; it can't be a CTAS target. Don't promise clients globally sequential keys their old warehouse never actually guaranteed. Use Delta generated identity columns where a sequence is needed, or 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 quoting capabilities) is the assessment + 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 split from platform-lessons.md: 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 (dev/test/prod) should exist before migration workloads land — catalogs, groups, policies, service principals, networking via Terraform; workloads via DABs. Don't start in notebooks.

Phase	Work	Deliverables
0. Assessment	Run Lakebridge Synapse profiler; inventory pools, pipelines, Spark, perms, network; rank workloads	Assessment report, target architecture, ROM cost/effort, migration backlog
1. Platform foundation	Terraform: workspaces, UC metastore + env/medallion catalogs, VNet injection + Private Link, access connector, Entra groups, policies	Running platform, IaC repo, 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 won't 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.