Synapse to Fabric migration: what does not move

A Synapse to Fabric migration usually reports success before it is finished. The Migration Assistant runs, the object count looks right, the warehouse opens, and a query returns rows. Everything that failed loudly has already been fixed, because failing loudly is what the tool is good at.

What is left is the set of things that succeeded and changed anyway. A column that lost its time zone. A function that was never migrated and never flagged as missing. A security model that came across intact except for the part that decides who can read the sensitive columns.

This piece is about that set: the differences between Synapse dedicated SQL pools and Fabric Data Warehouse that survive a clean migration run.

What the Migration Assistant actually does

The Fabric Migration Assistant is built into Fabric rather than run alongside it, and it covers more ground than most teams expect. It copies metadata and data from the source, converts the source schema to Fabric Data Warehouse, and offers Copilot assistance on the scripts that fail conversion.

There are two ways in. You upload a DACPAC file, or you connect directly to the source system, which is still a preview capability at the time of writing. The direct connection uses Power Query underneath, which is worth knowing because when it stalls on the source screen the problem is usually Power Query authentication rather than anything to do with the warehouse.

The object metadata it captures is broad:

Data itself moves separately, through a copy job in Fabric Data Factory, after the schema is in place. The sequence matters more than it appears: schema first, then fixes, then data, then a parallel comparison against the source before anything is rerouted.

One practical trick from Microsoft’s own runbook that teams routinely miss. You can scale the dedicated SQL pool up temporarily to speed the migration, then scale it back down. You are paying for the source anyway during the parallel run, and extraction is usually the slowest link.

The dependency graph behaves counterintuitively

Failed scripts are split into primary objects, which depend on nothing, and dependent objects, which depend on one or more others. Dependent objects will not migrate until their primaries are fixed, so the tool sorts primaries by how many things are waiting on them.

Here is the part that confuses people mid-migration. Fixing a primary object can make the primary object count go up, not down. If a view was broken because of three separate upstream problems and you fix one of them, it stops being blocked by that dependency and can be promoted into the primary list in its own right.

Teams read a rising number as regression and start over. It is progress. The number to watch is total unmigrated objects, not the primary count.

The type mappings are where data quietly changes

This is the section I would read first.

Several Synapse types have no direct equivalent in Fabric Data Warehouse, so the migration maps them. Most of the mappings are harmless widenings. One is not.

datetimeoffset maps to datetime2, and datetime2 does not store a time zone offset. Fabric Data Warehouse does not support datetimeoffset for persisted storage, so the offset has to be extracted into a separate column before you migrate, or it is gone.

There is a second loss in the same mapping that is easier to miss. Fabric limits datetime2 and time to six digits of precision on fractions of seconds, while Synapse datetimeoffset carries a precision of 0 to 7. So a source column at full precision loses the seventh digit as well as the offset. For most workloads that is irrelevant. For anything ordering events at sub-microsecond resolution it is not.

Nothing errors in either case. The column arrives, it holds a timestamp, and the timestamp is right in whatever local sense it was recorded. What has vanished is the information that told you which local sense that was.

Worth knowing that the type is not banned outright, only as a stored column. You can still CAST to datetimeoffset and use AT TIME ZONE in queries. That is what makes the workaround practical: persist UTC in datetime2, carry the offset or the time zone name in its own column, and reconstruct local time at query time rather than trying to store it.

The nchar to char and nvarchar to varchar mappings deserve a second look too, for a different reason. There is no unicode type in Parquet, which is why the mapping exists, and char and varchar in a UTF-8 collation can use more storage than the nchar and nvarchar they replace. Check your collation and encoding assumptions rather than assuming the mapping is free.

In our migrations, the type mapping table is the artefact we ask for first, before anyone runs anything. It takes an afternoon to produce a column-level inventory of every affected type in the source, and it is the cheapest insurance in the project.

Where the offset loss actually surfaces

The reason to care about this is not the column. It is the reporting boundary that sits on top of it.

The estates where we have seen this bite are multi-region transactional systems: financial trade logging, point-of-sale settlements, multi-market ERP. In all three, the offset is not decoration. It is what distinguishes one market’s end-of-day from another’s.

Drop it, and distinct local end-of-day cutoffs collapse into either raw UTC or a naive local timestamp with no way to tell which. Everything built on that boundary then shifts quietly by a few hours:

• Daily reconciliation models bucket transactions into the wrong fiscal day
• Monthly financial close boundaries move, so a trade at the edge of a period lands in the wrong month
• Localised regulatory audit reporting reports against a day that is not the day the regulator means
• Point-in-time comparisons between Synapse and Fabric stop agreeing, which is the symptom people usually notice first

That last one is the cruel part. During a parallel run the two platforms disagree, and the disagreement looks like a data movement fault. Teams go looking for a broken pipeline. The pipeline is fine. The boundary moved when the type changed, and the evidence needed to prove it was discarded at migration.

This is why the type inventory has to happen before anything runs, rather than as a defect list afterwards.

What the assistant will not bring across

Three categories, and they are worth separating because they call for different responses.

Not migrated, but supported. Scalar user-defined functions are not migrated by the Migration Assistant. Fabric Data Warehouse does support scalar UDFs, but only where they are inlineable, and inlining is itself in preview. So these need identifying and handling by hand, and the tool will not raise them as failures because it never attempted them.

Not supported, needs a replacement. SQL authenticated users have to become Microsoft Entra users, which means confirming each one can sign in to Fabric through Entra and then granting them access on the warehouse. Column-level encryption has no equivalent, and the suggested alternatives are encryption at the application layer plus dynamic data masking for obfuscation. Those are not the same control, and if column-level encryption was a compliance commitment rather than a preference, that gap belongs in the migration plan rather than in a defect list afterwards.

External tables and multi-statement table-valued functions also fall here.

So do several column types that never appear in the mapping table, because they have no mapping at all. Fabric cannot persist geometry or geography, and the alternatives are a latitude and longitude column pair, a varbinary column holding well-known binary, or varchar holding well-known text. json becomes varchar. xml and CLR user-defined types have no equivalent whatsoever. Each of these is a schema redesign rather than a conversion, which is why they belong in the inventory before anyone commits to a date.

One further trap sits inside a type that *is* supported. uniqueidentifier has no matching Delta Parquet type, so Fabric stores it as binary. Warehouse can read and write it, but the SQL analytics endpoint cannot read those values back, and cross-joins between a Warehouse and the SQL analytics endpoint on a uniqueidentifier column do not work as expected. If your model joins on GUIDs across those two surfaces, that is worth testing before migration rather than discovering during the parallel run. Note also that varchar(max) and varbinary(max) currently cap at 16 MB in Fabric.

Not needed any more. Indexes and transparent data encryption do not come across because Fabric does not want them. Fabric handles performance optimisation automatically, and encrypts data through its own mechanisms. Your existing indexes are still useful as evidence, though. They tell you where the previous platform struggled, which is a reasonable prior for where the new one might.

The security point is the one to sequence correctly. Fix the security objects that failed to migrate before you copy any data, not after. The failure mode of doing it the other way round is a populated warehouse with permissions that have not been reconstructed yet, which is a short window in which the wrong people can read the sensitive tables.

Lift and shift, or rebuild

Microsoft is unusually direct about when each approach fits, and the criteria are practical.

Lift and shift suits you when there are few warehouses to move, the data is already in a well designed star or snowflake schema, and you are under time pressure. In other words, when the current design is working and Fabric is a change of platform rather than a change of model.

The phased approach with architectural change is for warehouses that have accumulated over years and would need re-engineering to hold their performance, or where you specifically want the newer engines.

There is a real trap in choosing lift and shift for the wrong reason. Design adjustments are cheaper before migration than after, and warehouses built for scale have their own patterns, so traditional approaches are not always the right ones to carry over. A lift and shift chosen because the schema is genuinely good is sound engineering. A lift and shift chosen because nobody wants to open the schema question is a decision to migrate the problem along with the data.

The other instruction in the runbook that gets skipped: start small and simple, and prepare for multiple small migrations. Estates that attempt one large cutover generally discover the T-SQL surface area differences at the worst possible point, which is after the business has been told a date.

Our position: the parallel run is the deliverable

In our view the migration is not the hard part of a Synapse to Fabric migration. The tooling is good and getting better, and the schema conversion is largely solved.

The hard part is proving equivalence, and this is where we would not compromise. Run both platforms in parallel and reconcile results before rerouting anything, even when the migration reports zero errors, and especially then. A clean run is evidence that nothing failed. It is not evidence that everything matches.

We do not recommend the pattern that keeps appearing, which is to treat the parallel period as an optional buffer at the end of the plan that gets compressed when the project runs late. It is the only stage that tests the thing you actually care about, which is whether the numbers are the same. Everything before it tests whether the objects exist.

Across our estates, the differences that matter almost never show up in the object counts. They show up in an aggregate that is out by a fraction of a per cent because a type changed underneath it.

How long, and what to reconcile on

The two questions we get asked immediately, so here are our actual answers rather than a principle.

Run it for two full business reporting cycles. In practice that is two to four weeks minimum, and it must cover at least one month-end or fiscal close. A single week only exercises steady-state ELT. It tells you the pipelines run. What it cannot tell you is whether back-dated adjustments, snapshot boundaries and batch-closing logic behave the same, because none of those occur in an ordinary week. Month-end is where migrations fail, and a parallel run that ends before month-end has tested the easy part.

Reconcile in three layers, in this order. Each one is cheaper than the one below it, so failing early saves the effort of the rest.

Layer 1, structural and object integrity. Schema and DDL parity, explicitly validating how the unsupported types were mapped, datetimeoffset above all. Direct table row counts matched at daily partition level rather than in total, because two offsetting errors net to zero on a full-table count. Primary key uniqueness and non-null constraint adherence in the destination Delta tables.

Layer 2, numerical and aggregate consistency. Column-level hash comparisons, MD5 or SHA256, per batch partition. Additive metric reconciliation across the fields the business actually reports on, summed per day and per region: gross amount, tax, units. And a high-precision decimal rounding audit, to confirm parity between floating-point behaviour and fixed-precision Parquet storage. That last one catches the differences that are too small to see and too consistent to be noise.

Layer 3, business logic and temporal boundaries. Period-boundary cutoff validation, reconciling month-end and time zone specific batch totals, which is where the datetimeoffset problem shows itself if it is going to. Power BI semantic model and Direct Lake cache parity against the legacy DirectQuery or Import models, because the warehouse being right does not mean the report is. And incremental pipeline delta verification, confirming CDC and watermark logic produce identical daily upsert volumes on both platforms.

Layer 3 is the one that gets skipped under time pressure, and it is the layer that tests what the business will actually notice.

What this shares with the rest of Fabric

Two things worth connecting, because they land on the same teams.

Migrating into Fabric puts your warehouse onto shared capacity, which behaves differently from a dedicated SQL pool you sized yourself. If you have not worked through capacity planning and chargeback, the first month after cutover is a poor time to start.

And once the warehouse is in Fabric, its definition becomes something you can put under source control, with the caveats about what actually syncs through Fabric git integration. Migrating is the natural point to establish that, because you are creating the objects fresh anyway.

Where to start

Do not start with the Migration Assistant. Start with an inventory.

Produce a column-level list of every type in the source that appears in the mapping table, with datetimeoffset columns marked separately. Then list your scalar UDFs, your SQL authenticated users, any column-level encryption, external tables, and multi-statement table-valued functions. That list is your actual migration scope, because everything on it needs a human decision that no tool will make for you.

Then run the assistant against a small, self-contained warehouse first, so the surface area differences surface against something you can afford to redo.

Veratas runs data migration programmes onto Microsoft Fabric, including the reconciliation work that decides whether a migration is genuinely finished.

If you are planning a move off Synapse dedicated SQL pools and want the type and object inventory done before you commit to a date, talk to our team. It is a short piece of work and it changes the plan more often than not.

Frequently asked questions

Does the Fabric Migration Assistant move data as well as schema? It copies metadata and data, but the data movement runs through a copy job in Fabric Data Factory after the schema is in place. The order is schema, then fixes, then data, then parallel comparison.

What happens to datetimeoffset columns in a Synapse to Fabric migration? They map to datetime2, which does not store the time zone offset. Fabric Data Warehouse does not currently support datetimeoffset, so the offset has to be extracted into a separate column before migration or the information is lost silently.

Are scalar functions migrated? No. Scalar user-defined functions are not migrated by the Migration Assistant. Fabric supports them only where they are inlineable, which is itself in preview, so they need handling manually.

Why did the number of primary objects go up after I fixed one? That is expected. An object blocked by several upstream failures can become a primary object in its own right once one of those is cleared. Track total unmigrated objects rather than the primary count.

Do indexes and transparent data encryption need recreating in Fabric? No. Fabric handles performance optimisation automatically and encrypts data by its own means, so neither is needed. Your old indexes are still useful as a signal of where the previous platform struggled.

How long should the parallel run be? Two full business reporting cycles, which in practice means two to four weeks minimum and must include at least one month-end or fiscal close. A single week only tests steady-state pipelines and misses back-dated adjustments, snapshot boundaries and batch-closing logic, which is where migrations actually fail.

What should we reconcile on during a parallel run? Three layers, cheapest first. Structural integrity: schema and DDL parity, row counts at daily partition level, key uniqueness and null constraints. Numerical consistency: column-level MD5 or SHA256 hashes per partition, additive metric totals per day and region, and a decimal rounding audit. Business logic: period-boundary cutoffs, Power BI semantic model and Direct Lake parity against the legacy model, and CDC watermark verification that daily upsert volumes match.

Which data types have no equivalent in Fabric at all? geometry, geography, xml and CLR user-defined types cannot be persisted. json becomes varchar, and geometry and geography need a latitude and longitude pair, well-known binary in varbinary, or well-known text in varchar. These are schema redesigns rather than conversions.

Should we lift and shift or redesign? Lift and shift fits a small number of warehouses already in a sound star or snowflake schema under time pressure. Redesign fits warehouses that have evolved over years. Design changes are considerably cheaper before migration than after.

Divya Pal Singh

Azure and Business Intelligence Architect with 17+ years of experience delivering cloud data and analytics solutions. Focus areas include Azure data platforms, ETL and ELT architecture, and enterprise BI on Microsoft Azure.