Migrating from Synapse Dedicated Pools to Fabric Warehouse

Many organisations built their first Azure analytics platform on Synapse dedicated SQL pools. With Microsoft's investment now centred on Fabric, the question is no longer whether to move but how to move without breaking the reports finance looks at every Monday.

This is the plan we follow. It is deliberately unglamorous.

1. Inventory everything

Before converting anything, list what exists and what depends on it:

• Objects: tables, views, stored procedures, functions, external tables, security policies.
• Loads: which pipelines write to which tables, on what schedule.
• Consumers: Power BI datasets, other databases, extracts, applications, people with SQL clients.
• Usage: query logs over a few weeks show which objects are actually used. Expect a meaningful share to be dead.

Put it in a spreadsheet or a small database. It becomes the migration's single source of truth.

2. Plan waves by consumer, not by schema

Group objects by who consumes them, and migrate one consumer group at a time:

A wave might be "everything behind the sales Power BI app". When it is done, one business area has fully moved, which is easier to test and easier to explain than "we have migrated 40% of tables".

3. Convert the code

Most T-SQL moves across unchanged. The differences that cause real work:

• Distribution and indexes. Dedicated pools use DISTRIBUTION = HASH(...), ROUND_ROBIN, REPLICATE and clustered columnstore index options. Fabric Warehouse manages storage layout for you, so these clauses are removed, not translated. Revisit the reasons behind them: a hash distribution chosen to avoid data movement may now be a clustering or query-design concern.
• Unsupported or different features. Check your code against the current Fabric Warehouse T-SQL surface area for things like certain DDL options, some system views, cross-database patterns and specific data types. The list changes as Fabric matures, so check it at the start of each wave, not once.
• Workload management. Resource classes and workload groups do not carry over. Capacity is governed at the Fabric level, so plan how heavy loads and interactive queries share it.
• Security. Map logins and database roles to Microsoft Entra identities and Fabric workspace roles, then reapply object-level permissions. Row-level security and dynamic data masking need testing, not assuming.

Automate the mechanical rewrites with scripts and keep the human effort for the procedures with real logic.

4. Move the data

• Historical data: export to Parquet in the lake and load it into the warehouse, or land it in a lakehouse and expose it, depending on the target design.
• Ongoing loads: repoint pipelines to the new target. Data Factory pipelines usually move with connection changes; complex procedures may need rewriting as Spark or warehouse T-SQL.
• Keep the lake as the boundary. Once data lives in OneLake as Delta, both the warehouse and the lakehouse can read it, which keeps future options open.

5. Run in parallel and reconcile

For each wave, run old and new side by side for at least one full business cycle, usually a month-end. Automate reconciliation:

• Row counts per table per load.
• Sums of key measures by key dimensions.
• A sample of report numbers compared directly.

Differences are usually rounding, time zones, collation or NULL handling. Find them now, while the old system is still there to compare against.

6. Cut over and decommission

• Switch consumers wave by wave, with a named owner signing off each one.
• Keep the dedicated pool paused but restorable for an agreed period.
• Then decommission it. A paused pool that nobody owns becomes a cost and a security risk.

Common traps

• Lifting and shifting tuning hints. Distribution choices made for an MPP engine do not mean the same thing in Fabric.
• Migrating dead objects. Usage data from step 1 saves weeks.
• Skipping the parallel run because the timeline is tight. It is the step that protects the timeline.
• Forgetting the consumers outside Power BI: the CSV extract a partner downloads every night will be the one that breaks.

Where Databricks fits

If Databricks is already part of the estate, some waves may be better served by moving transformations into a lakehouse and keeping Fabric Warehouse for serving. Decide that per wave, on cost, skills and governance, rather than as a one-time platform war.