Navigating a Synapse Migration to Databricks

Summary

The post presents migration from Azure Synapse to Databricks as a way to reduce platform complexity while supporting data engineering, analytics, machine learning, real-time pipelines, and AI applications. It distinguishes migration paths for Dedicated SQL Pools, serverless SQL, Spark Pools, orchestration, governance, and downstream BI connectivity, noting that accumulated T-SQL logic and dependencies require the most redesign, testing, and validation. The proposed programme uses discovery and assessment with Lakebridge Profiler and Analyzer, then selects an architecture, sequences workloads, pilots a real use case, and scales migration in waves. It recommends automating code conversion, reconciling row counts, aggregations, hashes, and tolerances, avoiding direct Synapse distribution-to-Delta partition mappings, and planning compaction, enablement, and rollback. Reported examples include Casey’s halving operational data delivery times from eight hours to four and Italgas cutting workload costs by 73%.

Context

Synapse environments often span Dedicated SQL Pools, serverless SQL, Spark Pools, Azure Data Factory or Synapse Pipelines, governance tools, legacy SSIS workloads, and downstream BI connections. This distributed setup increases integration, governance, monitoring, permissions, and operational overhead, while warehouse-centric designs may not support expanding analytics, machine learning, real-time, and AI requirements efficiently.

Approach / What changed

The migration is structured as a programme covering discovery, assessment, design, piloting, and phased execution. Lakebridge Profiler inventories the estate, while Lakebridge Analyzer assesses T-SQL complexity and dependencies. The recommended approach commonly combines automated conversion with incremental modernisation, prioritises lower- and medium-complexity workloads, validates each migration wave, maintains Synapse temporarily for rollback, and plans operational readiness and change management.

Takeaways

• Dedicated SQL Pools usually require the most redesign because business logic, stored procedures, distribution strategies, indexing, performance optimisations, and dependencies have accumulated over time.
• Lakebridge handles 80–90% of code conversion, allowing engineering effort to focus on the 10–20% requiring human judgment, including cursors, dynamic SQL, and complex error handling.
• Directly mapping Synapse HASH distribution keys to Delta Lake partition columns can create unnecessary complexity and poor performance; liquid clustering and automated optimisation are presented as alternatives for many workloads.

Related reading