Azure Synapse Databricks: A Migration Playbook That Survives Production

Azure Synapse → Databricks: A Migration Playbook That Survives Production A field-style guide for Lead / Senior Data Engineers moving Dedicated SQL Pools, pipelines, and BI off Synapse without burning trust, budget, or the on-call team. Why this migration is harder than the slide deck What you are actually migrating Program shape: discovery → waves Four parallel workstreams Data movement: skip the JDBC tax Code conversion: syntax is easy, semantics bite Dimensional patterns on Delta (SCD2) Reconciliation & cutover Power BI / consumption cutover 30 / 60 / 90 plan Anti-patterns I keep seeing Field rules I will not compromise on FAQ Sources If you have lived in Azure Synapse for years, the marketing line sounds simple: Move to Databricks. Unity Catalog. Lakehouse. Done. In production programs, that is not the hard part. The hard part is that Synapse is not one system. Under one brand you usually own years of Dedicated SQL Pool logic, ADF/Synapse Pipelines, serverless SQL over the lake, Spark pools, permission models, and Power BI datasets wired to Synapse endpoints. Treat that estate as one workstream with one deadline, and the schedule slips. Every time. I work as a Lead Data Engineer across Azure Synapse, Microsoft Fabric, Azure Data Lake, Databricks/Spark, SQL/T-SQL, and Power BI. This playbook is the version I want in the room before the first converted stored procedure lands in production. Thesis: Migration success is not “all notebooks on Databricks.” It is decision-ready data, trusted KPIs, and a platform your team can operate. Synapse piece Typical complexity What “done” looks like on Databricks Dedicated SQL Pools Highest — procs, distributions, CCI, years of tuning Databricks SQL / Spark jobs, Delta tables, Liquid Clustering / predictive optimization Serverless SQL Medium — views & external tables over ADLS Views / tables in Unity Catalog over the same lake paths (often simpler) Spark pools Lower — already Spark-shaped Databricks Runtime notebooks / jobs (often few changes) ADF / Synapse Pipelines High — orchestration + connectors Lakeflow Connect / Lakeflow Jobs / Workflows Governance High — SQL perms + Purview stitching Unity Catalog (and stop dual-governing forever) Power BI / semantic models High politically Databricks SQL Warehouse endpoints + dataset remaps Dedicated SQL Pools usually consume most of the calendar. Orchestration and BI consume most of the organizational risk. Do not start with “transpile everything.” Start with a program. Inventory what actually runs: pools, databases, schemas, object counts top queries / heavy procs by CPU & IO pipeline inventory (ADF / Synapse) downstream BI datasets and owners unused objects (your cheapest scope cut) Lakebridge Profiler (Databricks Labs) is built for Synapse estate profiling — metadata, utilisation, query patterns, baselines for a TCO conversation. Classify the T-SQL estate: low / medium / high complexity unsupported constructs dependency graphs (what breaks if this view moves) Lakebridge Analyzer helps here. Start waves with low/medium complexity and clear business owners — not the ugliest proc on day one. Three strategies show up in every steering committee: Approach When it works Failure mode Lift-and-shift Hard deadline off Synapse You recreate Synapse-shaped pain on a new bill Big-bang rewrite Greenfield team, tiny estate Timeline fantasy Hybrid Most real enterprises Needs discipline: automate bulk convert, modernize in waves For most Synapse migrations, hybrid wins: automated conversion gets you off Synapse on a schedule; modernization (Delta patterns, Liquid Clustering, Lakeflow, UC) happens after workloads are stable. Pick one end-to-end slice: source → ingest → transform → gold mart → Power BI Prove: storage + UC layout job standards reconciliation pack BI remap on-call runbook The pilot’s job is not heroics. It is producing reusable assets for later waves. Each wave should ship a visible business win, not just “another schema moved.” After the lighthouse, scale on four tracks together. Sequential “ETL first, BI last” is how stakeholders only feel pain. Track From To Ingestion ADF / Synapse Pipelines Lakeflow Connect / Jobs (or open ingest into Delta) Transformation T-SQL / procs / views Databricks SQL / Spark with clear contracts Orchestration Synapse schedules & deps Lakeflow Jobs / Workflows Consumption Power BI / semantic models Databricks SQL Warehouses A BI-aware sequence often works better than pure ETL-first: Land or expose business-facing marts early (even interim / federation patterns where appropriate). Put a trusted KPI dataset in front of users. Modernize the uglier ETL underneath while the business already sees progress. Enablement is a fifth unofficial track. Synapse-native teams underinvest here and then call the platform “not ready.” For large Dedicated SQL Pool tables, JDBC Synapse → Databricks is how you burn cluster hours and patience. Prefer: Synapse CETAS → Parquet on ADLS Gen2 → Databricks Spark read → Delta in Unity Catalog Synapse writes columnar Parquet in parallel (CETAS). ADLS is the shared landing zone both engines already understand. Databricks reads Parquet with strong scan performance, then writes Delta (ACID, time travel, UC governance). You avoid row-by-row JDBC serialization. -- File format CREATE EXTERNAL FILE FORMAT ParquetFF WITH ( FORMAT_TYPE = PARQUET, DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec' ); -- Landing zone on ADLS Gen2 CREATE EXTERNAL DATA SOURCE MigLanding WITH ( LOCATION = 'https://<storage>.dfs.core.windows.net/<container>', TYPE = HADOOP ); -- Export a bounded slice (always partition/filter on first runs) CREATE EXTERNAL TABLE ext.fact_sales_export WITH ( LOCATION = '/migration/fact_sales/ds=2026-09-03/', DATA_SOURCE = MigLanding, FILE_FORMAT = ParquetFF ) AS SELECT sales_id, customer_nk, product_nk, business_date, net_amount, quantity FROM dbo.fact_sales WHERE business_date >= '2024-01-01'; from pyspark.sql import functions as F src = "abfss://<container>@<storage>.dfs.core.windows.net/migration/fact_sales/" df = ( spark.read.parquet(src) .withColumn("ingested_at", F.current_timestamp()) ) ( df.write .format("delta") .mode("overwrite") .option("overwriteSchema", "true") .saveAsTable("sales_uc.gold.fact_sales") ) In Synapse you spent years on: DISTRIBUTION = HASH(...) ROUND_ROBIN / REPLICATE clustered columnstore choices On Databricks / Delta, those directives do not travel. Mapping HASH(customer_id) to a Delta partition is a classic self-own: high-cardinality keys create tiny-file nightmares. Prefer: Liquid Clustering / CLUSTER BY AUTO predictive optimization / compaction as an operating model partitions only for coarse, stable predicates (e.g. business_date) when they truly help Rule: Drop physical Synapse directives. Re-tune with lakehouse primitives after you have real query telemetry — not before. Automated conversion (Lakebridge transpile and friends) often handles a large share of syntax — commonly cited in the ~80–90% range for bulk translation. Your engineers live in the remaining 10–20%: cursors, dynamic SQL, weird error handling, and semantic mismatches. T-SQL Databricks SQL GETDATE() CURRENT_TIMESTAMP() ISNULL(a, b) COALESCE(a, b) / IFNULL(a, b) LEN(s) LENGTH(s) CHARINDEX(sub, str) LOCATE(sub, str) SELECT TOP 10 ... SELECT ... LIMIT 10 CONVERT(INT, col) CAST(col AS INT) Dedicated SQL Pools are often case-insensitive by collation. Databricks SQL is case-sensitive by default. -- May silently change results after migration WHERE status = 'active' -- Make intent explicit WHERE LOWER(status) = 'active' Build this into reconciliation: pick string dimensions and compare distinct counts before you argue about revenue. Migrate structure first (parameters, control flow, set-based DML). Optimize second. Rewrite candidates (do not pretend transpile “finished” them): cursors / row-by-row loops heavy dynamic SQL Synapse-only performance hints temp-table choreography that exists only to fight distribution skew Simple audit logging often stays SQL-shaped. Multi-step compensation and downstream signaling usually belong in Lakeflow Jobs (retries, quarantine tasks, alerting) rather than a 2,000-line proc that tries to be an orchestrator. Slowly Changing Dimensions are where Synapse estates accumulate snowflake uniqueness. Your goal is not to clone the old proc line-for-line. Preserve the business rule: history + current row queryability. -- 1) Expire changed current rows MERGE INTO dim_customer AS t USING staging_customer AS s ON t.customer_nk = s.customer_nk AND t.is_current = true WHEN MATCHED AND ( t.segment <> s.segment OR t.country <> s.country ) THEN UPDATE SET t.is_current = false, t.valid_to = current_timestamp(); -- 2) Insert new current versions INSERT INTO dim_customer ( customer_nk, segment, country, valid_from, valid_to, is_current ) SELECT s.customer_nk, s.segment, s.country, current_timestamp(), CAST(NULL AS TIMESTAMP), true FROM staging_customer s LEFT ANTI JOIN dim_customer t ON t.customer_nk = s.customer_nk AND t.is_current = true; Delta’s ACID behavior makes multi-step SCD flows safer than “hope the warehouse lock ordering holds.” Still: wrap critical loads in jobs with clear quarantine paths. Budget reality: validation often costs more calendar time than conversion. Row counts by business date / partition Aggregates — revenue, quantity, distinct business keys Hash / checksum compares on ordered business-key sets Tolerance checks for floats / FX / rounded measures String-dimension sanity (case folding surprises) Lakebridge Reconcile supports structured comparison patterns across these dimensions. Parallel run (Synapse + Databricks) for the lighthouse mart Side-by-side Power BI validation with the business owner Flip the dataset connection Pause Synapse compute (do not delete yet) Keep rollback for a defined soak window Only then decommission Early decommissioning is how “successful migrations” become weekend rollbacks. Technical remap is usually: Synapse endpoint → Databricks SQL Warehouse refresh credentials / gateway assumptions validate DAX measures against reconcile packs Political remap is harder: who signs off KPI parity? which report is the source of truth during dual-run? what is the communication plan when numbers differ by 0.3% due to case folding or timezone? Treat consumption as a first-class workstream with named business owners, not a Friday afternoon connection string change. [ ] Run Lakebridge Profiler + Analyzer on Dedicated pools [ ] Cut dead objects from scope (document why) [ ] Choose one lighthouse: pipeline → gold mart → one Power BI dataset [ ] Stand up Unity Catalog layout + storage conventions [ ] Define job naming, environments, and CI basics [ ] Write the reconcile notebook/job v0 [ ] CETAS → ADLS → Delta for large facts (no JDBC heroics) [ ] Transpile + hand-fix the top procs for the lighthouse [ ] Repoint one BI workspace to Databricks SQL Warehouse [ ] Parallel run + signed business acceptance [ ] Capture runbooks: failure modes, quarantine, reruns [ ] Wave 2/3 domains with the same pack [ ] Orchestration parity on Lakeflow Jobs [ ] Enablement plan for Synapse-native engineers [ ] Cost review (warehouse sizing vs old pool bills) [ ] Only then: formal Synapse decommission proposal Transpile-first, assess-never — migrating abandoned procs with confidence. JDBC for multi-billion-row facts — slow, expensive, fragile. HASH key → Delta partition — tiny files, sad Spark UI. Big-bang BI cutover — no dual-run, no owner, no soak. Recreating every Synapse knob — preserving constraints without preserving reasons. Skipping enablement — platform is fine; team still thinks in distributions. Decommission on day one of “green” — optimism is not a rollback strategy. Hybrid > pure lift-and-shift > big-bang rewrite Assessment before conversion ADLS + Parquet/Delta over JDBC for big moves Do not map Synapse HASH keys to partitions Reconcile every wave Do not decommission Synapse early Enablement is a workstream, not a lunch-and-learn Migrate outcomes (trusted KPIs), not just objects Q: Should we move to Fabric instead of Databricks? A: Different target, overlapping Azure story. This article assumes a Databricks Lakehouse landing zone. If Fabric is in play, treat that as an explicit architecture decision — do not accidentally run two migrations under one slogan. Q: Can we keep Synapse Serverless for “just a few views”? A: Sometimes as a bridge. Long-term dual query planes usually recreate the governance tax you are trying to escape. Q: How perfect must automated conversion be? A: Good enough to move bulk syntax. Your senior engineers should own semantic diffs, SCD logic, and orchestration. Q: What is the first metric of a healthy pilot? A: Not “job duration.” It is signed KPI parity with a business owner plus a rerun runbook a junior can execute. Databricks — Navigating a Synapse Migration to Databricks https://www.databricks.com/blog/navigating-synapse-migration-databricks Databricks — Introducing Lakebridge https://www.databricks.com/blog/introducing-lakebridge-free-open-data-migration-databricks-sql Lakebridge docs — Synapse Profiler https://databrickslabs.github.io/lakebridge/docs/assessment/profiler/synapse/ Lakebridge docs — Transpile https://databrickslabs.github.io/lakebridge/docs/transpile/ Practical large-table movement discussion (CETAS → ADLS → Delta) https://medium.com/@prabhakarankanniappan/copy-3-5-billion-rows-from-synapse-to-databricks-in-30-minutes-fda15e0ffca0 Suggested LinkedIn teaser (after you publish on DEV.to) Synapse → Databricks is not a notebook move. It’s Dedicated SQL Pools + pipelines + T-SQL semantics + Power BI trust. I wrote a practical playbook with diagrams, CETAS→Delta examples, reconcile guidance, and a 30/60/90. Link: <your DEV.to URL> Firat Celik — Lead Data Engineer · Azure Synapse · Microsoft Fabric · Databricks · ETL/ELT · Power BI