DEV Community

Cover image for Azure Synapse Databricks: A Migration Playbook That Survives Production
Firat Celik
Firat Celik

Posted on

Azure Synapse Databricks: A Migration Playbook That Survives Production

#databricks #azure #dataengineering #etl

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.

Table of contents

  1. Why this migration is harder than the slide deck
  2. What you are actually migrating
  3. Program shape: discovery → waves
  4. Four parallel workstreams
  5. Data movement: skip the JDBC tax
  6. Code conversion: syntax is easy, semantics bite
  7. Dimensional patterns on Delta (SCD2)
  8. Reconciliation & cutover
  9. Power BI / consumption cutover
  10. 30 / 60 / 90 plan
  11. Anti-patterns I keep seeing
  12. Field rules I will not compromise on
  13. FAQ
  14. Sources

1) Why this migration is harder than the slide deck

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.

2) What you are actually migrating

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.

3) Program shape: discovery → waves

Do not start with “transpile everything.” Start with a program.

flowchart LR
  A[1 Discovery<br/>Lakebridge Profiler] --> B[2 Assessment<br/>Analyzer / T-SQL]
  B --> C[3 Design<br/>Hybrid lift + modernize]
  C --> D[4 Pilot<br/>Lighthouse E2E]
  D --> E[5 Waves<br/>Parallel workstreams]

Phase A — Discovery

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.

Phase B — Assessment

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.

Phase C — Design (usually hybrid)

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.

Phase D — Pilot (lighthouse)

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.

Phase E — Waves

Each wave should ship a visible business win, not just “another schema moved.”

4) Four parallel workstreams

After the lighthouse, scale on four tracks together. Sequential “ETL first, BI last” is how stakeholders only feel pain.

flowchart TB
  P[Lighthouse Pilot]
  P --> I[Ingestion<br/>ADF/Synapse Pipelines → Lakeflow Connect]
  P --> T[Transformation<br/>T-SQL / procs → Databricks SQL / Spark]
  P --> O[Orchestration<br/>Schedules → Lakeflow Jobs]
  P --> C[Consumption<br/>Power BI → Databricks SQL Warehouse]
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

Sequencing tip that builds trust

A BI-aware sequence often works better than pure ETL-first:

  1. Land or expose business-facing marts early (even interim / federation patterns where appropriate).
  2. Put a trusted KPI dataset in front of users.
  3. 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.”

5) Data movement: skip the JDBC tax

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

flowchart LR
  S[Synapse Dedicated SQL Pool] -->|CETAS| L[ADLS Gen2 Parquet]
  L -->|Spark read / Delta write| D[Unity Catalog Delta]
  S -.->|JDBC slow path ❌| D

Why this path wins

  • 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.

Example — CETAS from Synapse Dedicated SQL Pool 

-- 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';
Enter fullscreen mode Exit fullscreen mode

Example — land as Delta on Databricks 

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")
)
Enter fullscreen mode Exit fullscreen mode

Physical design: do not cargo-cult Synapse

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.

6) Code conversion: syntax is easy, semantics bite

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.

Common remaps

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)

Semantic trap #1 — case sensitivity

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'
Enter fullscreen mode Exit fullscreen mode

Build this into reconciliation: pick string dimensions and compare distinct counts before you argue about revenue.

Semantic trap #2 — stored procedures

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

Semantic trap #3 — TRY/CATCH vs workflow design

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.

7) Dimensional patterns on Delta (SCD2)

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.

Example pattern — expire then insert (illustrative) 

-- 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;
Enter fullscreen mode Exit fullscreen mode

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.

8) Reconciliation & cutover

Budget reality: validation often costs more calendar time than conversion.

Minimum reconcile pack (every wave)

  1. Row counts by business date / partition
  2. Aggregates — revenue, quantity, distinct business keys
  3. Hash / checksum compares on ordered business-key sets
  4. Tolerance checks for floats / FX / rounded measures
  5. String-dimension sanity (case folding surprises)

Lakebridge Reconcile supports structured comparison patterns across these dimensions.

Cutover pattern that keeps executives calm

  1. Parallel run (Synapse + Databricks) for the lighthouse mart
  2. Side-by-side Power BI validation with the business owner
  3. Flip the dataset connection
  4. Pause Synapse compute (do not delete yet)
  5. Keep rollback for a defined soak window
  6. Only then decommission

Early decommissioning is how “successful migrations” become weekend rollbacks.

9) Power BI / consumption cutover

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.

10) 30 / 60 / 90 plan

Days 1–30 — foundations

  • [ ] 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

Days 31–60 — prove the path

  • [ ] 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

Days 61–90 — scale without drama

  • [ ] 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

11) Anti-patterns I keep seeing

  1. Transpile-first, assess-never — migrating abandoned procs with confidence.
  2. JDBC for multi-billion-row facts — slow, expensive, fragile.
  3. HASH key → Delta partition — tiny files, sad Spark UI.
  4. Big-bang BI cutover — no dual-run, no owner, no soak.
  5. Recreating every Synapse knob — preserving constraints without preserving reasons.
  6. Skipping enablement — platform is fine; team still thinks in distributions.
  7. Decommission on day one of “green” — optimism is not a rollback strategy.

12) Field rules I will not compromise on

  1. Hybrid > pure lift-and-shift > big-bang rewrite
  2. Assessment before conversion
  3. ADLS + Parquet/Delta over JDBC for big moves
  4. Do not map Synapse HASH keys to partitions
  5. Reconcile every wave
  6. Do not decommission Synapse early
  7. Enablement is a workstream, not a lunch-and-learn
  8. Migrate outcomes (trusted KPIs), not just objects

13) FAQ

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.

14) Sources

  1. Databricks — Navigating a Synapse Migration to Databricks https://www.databricks.com/blog/navigating-synapse-migration-databricks
  2. Databricks — Introducing Lakebridge https://www.databricks.com/blog/introducing-lakebridge-free-open-data-migration-databricks-sql
  3. Lakebridge docs — Synapse Profiler https://databrickslabs.github.io/lakebridge/docs/assessment/profiler/synapse/
  4. Lakebridge docs — Transpile https://databrickslabs.github.io/lakebridge/docs/transpile/
  5. 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

Top comments (0)