Thousands of enterprise data warehouses still run on Azure Synapse Dedicated SQL Pool. Most teams assume moving to Microsoft Fabric is a straightforward T-SQL migration. Redeploy the objects, point them at a new connection string, done.
It isn't. And the biggest risks in this migration aren't where most teams expect them to be.
Synapse Dedicated SQL Pool has stopped getting new features. Microsoft still patches it for security, but the engine has run its course. Every team still running production workloads on it is now on a forced timeline to Fabric, whether they planned for it this year or not. Microsoft's own documentation covers the schema, code, and data migration options well. What it doesn't tell you is which parts of a real, multi-thousand-object estate actually break during the move, and where teams lose weeks, they didn't budget for. This is that part.
This article is for you if you are:
- Planning a Synapse to Fabric migration
- Managing an enterprise data warehouse
- Assessing Microsoft Fabric adoption
- Responsible for SQL modernization
- Supporting analytics platform modernization
- Typical Azure Synapse Dedicated SQL Pool Architecture
Most Synapse estates look roughly like this:
Azure Synapse Dedicated SQL Pool
│
▼
Tables + Indexes → Views → Stored Procedures → ETL Pipelines → Power BI
Every layer in that stack has Synapse-specific assumptions baked into it, some obvious, some not. That's what makes this a genuinely different migration from a typical lift-and-shift.
Why This Isn't a Simple Connection-String Swap
Fabric Warehouse is T-SQL compliant, which makes this look, on paper, like a redeploy. For a handful of objects, that's roughly true. For an estate with years of accumulated logic, it isn't, for three specific reasons.
| Azure Synapse Dedicated SQL Pool | Microsoft Fabric Warehouse |
|---|---|
| Clustered columnstore indexes | Different optimization model (no traditional user-managed indexes) |
| DWU (Data Warehouse Units) | CU (Capacity Units) with non-linear sizing |
| CETAS for data export | COPY INTO for data ingestion |
| Primary and foreign key constraints enforced | Constraints require validation and may need reimplementation |
| Uses "SQL Pool" terminology | Uses "Warehouse" terminology |
Indexes aren't supported in Fabric Warehouse. If your dedicated SQL pool workload leans on clustered columnstore index tuning for query performance, that tuning approach doesn't carry over. You're not migrating the index, you're redesigning around its absence.
Constraints don't migrate automatically. Primary keys, foreign keys, and check constraints on the source side typically don't move with a straight schema copy. If downstream logic, or a BI tool, silently depended on referential integrity being enforced at the database layer, that assumption breaks quietly, not loudly.
DWU to Fabric Capacity Unit sizing isn't a clean formula. Microsoft's published mapping guidance is benchmarked against TPC-H, and real workloads deviate from TPC-H in ways that matter. A DWU6000 environment doesn't drop cleanly into one specific Fabric SKU. Under-sizing shows up as concurrency throttling weeks after go-live, not on day one.
None of these are blockers. They're planning inputs that need to be scoped before conversion starts, not discovered during it.
Migration Options from Azure Synapse to Microsoft Fabric
Microsoft supports a few paths for schema (DDL), code (DML), and data migration. In practice, teams end up combining them depending on object type and data volume.
Data Factory Copy Assistant works well for smaller databases. It's a drag-and-drop path: connect to the source dedicated SQL pool, convert DDL syntax, copy data. Straightforward, but it doesn't scale gracefully past a modest number of tables before pipeline management becomes its own project.
CETAS to ADLS, then COPY INTO Fabric is the higher-throughput option. You stage data as Parquet in a data lake first, which decouples extraction from load and performs meaningfully better at volume:
-- Stage the source table to Parquet via CETAS
Example: Export with CETAS and Import Using COPY INTO
CREATE EXTERNAL TABLE staging.orders_export
WITH (
LOCATION = '/staging/orders/',
DATA_SOURCE = ADLSGen2Staging,
FILE_FORMAT = ParquetFormat
)
AS
SELECT *
FROM dbo.orders;
-- Load the staged Parquet data into Fabric Warehouse
COPY INTO dbo.orders
FROM 'https://storageaccount.blob.core.windows.net/staging/orders/'
WITH (
FILE_TYPE = 'PARQUET'
);
This is the path we default for anything beyond a handful of terabytes.
dbt Fabric adapter, if the team already runs dbt against the dedicated SQL pool, is worth a serious look before hand-converting DDL and DML manually. It handles a meaningful chunk of the schema and transformation logic conversion natively, though data movement still has to happen through one of the other paths.
Recommended Enterprise Migration Workflow
The mechanics in the previous section cover the easy 70% of this flow. The remaining 30% is where migrations stall, and it's rarely about moving data.
Stored Procedure Challenges
Stored procedures with platform-specific syntax compile differently, or not at all, under Fabric's T-SQL surface. These need object-by-object review, not a find-and-replace pass. Enterprise data platforms often contain thousands of SQL objects, which makes manual review one of the most time-consuming phases of any modernization effort.
ETL Dependencies
ETL pipelines built around dedicated pool-specific patterns, staging table swaps, partition switching tricks, assume dedicated pool internals that don't exist in Fabric. Each of these needs to be re-architected, not just re-pointed.
Hidden Business Logic
Undocumented business logic buried in views and procs is the hardest category, because nobody currently at the company fully understands all of it. It was often written by someone who left three platform migrations ago.
This last category is the one that actually determines your timeline. Microsoft's guidance correctly recommends starting with an inventory and choosing a workload area you can prove out first, typically in the 1 to 10 terabyte range. What it can't give you is a fast way to know, across thousands of objects, which ones are simple lift-and-shift and which ones are landmines.
Migration Risks at a Glance
Indexes → redesign, not migrate
Constraints → re-implement manually
Stored Procedures → object-by-object review
DWU-to-CU Mapping → validate against real concurrency
ETL Dependencies → re-architect, don't re-point
Hidden Dependencies → the real timeline risk
A Practical Way to Sequence It
For a dedicated SQL pool estate of meaningful size, this order tends to hold up:
Inventory everything first, including cross-object dependencies. You can't scope a migration wave plan against an estate you haven't fully mapped.
Score objects by conversion complexity, not just row count. A 50-line stored procedure with three nested CTEs and a nonstandard join pattern is a bigger risk than a 5-million-row fact table with a clean schema.
Pick a small, representative workload for wave one. As a starting point, something like 100 tables, 10 stored procedures, and 5 ETL jobs is enough to prove the pattern, indexes, constraints, sizing, without taking on the risk of the full estate at once.
Convert in bulk once the pattern is validated, with every converted object run through automated reconciliation against the source, not spot-checked on a sample.
Right-size Fabric capacity based on actual observed concurrency, not the DWU-to-CU mapping table alone. Treat the published mapping as a starting point, not a final answer.
Mistakes Worth Naming
Sizing Fabric capacity off the DWU mapping table alone. The published DWU-to-CU guidance is a reasonable starting point, but it's benchmarked against TPC-H, and TPC-H doesn't look like your production workload's concurrency pattern. Teams that skip a capacity validation step tend to find out they under-sized three weeks after go-live, when concurrent query load hits its first real Monday morning.
Treating the migration as one monolithic cutover. A phased, domain-by-domain wave plan, finance first, then supply chain, then reporting, contains risk to one area at a time. A single big-bang cutover means a schema surprise in one domain can stall the entire program.
Skipping reconciliation on "simple" objects. The instinct is to spot-check the complex stored procedures and wave through anything that looks like a clean table copy. In practice, silent data type coercion issues show up just as often in supposedly simple objects as in complex ones. Automated row-and-column-level reconciliation on every converted object, not a sample, is the only way to catch this before a business user does.
The Takeaway
Migration success depends less on copying data and more on understanding the complexity of the existing estate before conversion begins.
Organizations that inventory dependencies, score objects by risk, and validate workloads early are far more likely to hit their Fabric migration timeline. The ones that treat it as a connection-string swap usually find out otherwise around week six.
For teams evaluating a Synapse-to-Fabric migration, an early assessment of dependencies, complexity, and platform-specific risks can significantly reduce project uncertainty.
Learn more about the Microsoft Fabric Migration Accelerator from 3X Data Engineering.
Top comments (0)