Choosing Between Synapse SQL and Databricks for a Migration
A retail company runs nightly ETL on an on-premises SQL Server. It processes 2 TB of parquet files in a data lake, joins to a 500 GB dimension, and produces 40 aggregate tables. The team knows T-SQL but not Python. The CIO wants the lowest operational overhead on Azure and predictable cost. Peak load is 4 hours; the rest of the day is idle. A proposal suggests Azure Databricks with autoscaling clusters; another suggests Synapse dedicated SQL pools.
1. Compare the two options against the stated constraints and recommend one.
Read the worked answer
Synapse dedicated SQL pools fit T-SQL skills and predictable cost via reserved capacity or pause/resume during idle. But 2 TB parquet transforms and a 500 GB dimension may exceed dedicated pool concurrency and require distribution tuning. Databricks with autoscaling and Photon handles large parquet transforms well and scales down when idle, but requires Python/Scala/Spark SQL skills and introduces cluster management. Given the team's T-SQL background and the 20-hour idle window, a Synapse dedicated pool paused outside the 4-hour window gives predictable cost. If the transform complexity grows, Synapse Spark pools or Databricks can be added later. Recommend Synapse dedicated SQL pool with pause/resume, plus a Spark pool for parquet-heavy steps if needed.
2. What cost-control mechanisms would you put in place for the chosen option?
Read the worked answer
For Synapse dedicated SQL pools, use pause/resume outside the 4-hour window, right-size DWU based on workload, and use workload management to cap concurrency. For Spark, use autoscaling with a minimum of zero and auto-termination. Set budget alerts in Azure Cost Management and tag resources. Monitor DWU utilization and scale down if consistently under 50%. Review storage costs for the 2 TB lake and use lifecycle policies to tier cold data.
3. What risks should be documented before migration?
Read the worked answer
Risk: T-SQL compatibility gaps in Synapse (unsupported features, differences in MERGE, identity, and transactions). Risk: distribution skew on the 500 GB dimension causing slow joins. Risk: team skill gap if Spark is introduced. Risk: cost overrun if pause/resume is not automated. Mitigate with a pilot on 10% of data, a compatibility assessment, and a rollback plan to the on-premises system for 30 days.