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

Takeaways

1. 1

   Dedicated SQL Pools usually require the most redesign because business logic, stored procedures, distribution strategies, indexing, performance optimisations, and dependencies have accumulated over time.

2. 2

   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.

3. 3

   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.