Goal#

Move an existing traditional or cloud data warehouse onto Microsoft Fabric so that data lands once in OneLake, the target exposes the T-SQL surface the workload needs, and reporting keeps running through a controlled parallel-run cutover — while sizing capacity for the concurrency and ingestion volume the warehouse actually carries.

Recommended architecture#

Original diagram: data-warehouse-to-Fabric migration phases - source DW to landing/mirroring into OneLake, to Fabric Warehouse on OneLake Delta, to semantic layer and cutover

Fabric Data Warehouse is an enterprise-scale relational warehouse built on a data lake foundation, aimed at star/snowflake schemas, curated corporate data marts, and governed semantic models for BI [S1]. For a T-SQL-first warehouse migration it is the recommended target store: development is primarily in T-SQL on a large SQL Database Engine surface, including full multi-table ACID transactions, materialized views, functions, and stored procedures [S1]. Microsoft ships a Fabric Migration Assistant for Data Warehouse to move workloads from Azure Synapse Analytics dedicated SQL pools, SQL Server, and other SQL Database Engine platforms [S1].

The foundation that makes the whole migration a single-copy design: warehouse tables are physically stored as Delta tables — Parquet data files plus a file-based transaction log — the same open format used for all Fabric data, and warehouse data is automatically published to OneLake in Delta format so other engines can read it without export jobs [S1]. Cross-database queries let a single T-SQL statement combine multiple Fabric data sources in the workspace with zero data duplication [S1].

Target-store decision#

Original diagram: decision tree for the migration target store - Fabric Warehouse (writable T-SQL default), lakehouse SQL analytics endpoint (read-only), SQL database (OLTP), and mirroring as a transitional bridge

The target-store choice is about the query surface, not a second copy of the data — all of the options below read the same OneLake Delta tables.

Fabric Warehouse (default for a T-SQL DW)#

Choose a warehouse over a lakehouse when you want enterprise-scale T-SQL-first work over structured or semi-structured data with minimal setup; choose a lakehouse when Spark is the primary tool over heterogeneous, largely unstructured data — both run the same SQL engine for T-SQL queries, and either can be added later [S1]. The warehouse is the only one of these options that gives a writable, transactional T-SQL surface [S1] [S15].

Lakehouse SQL analytics endpoint (read-only T-SQL)#

A SQL analytics endpoint provides T-SQL querying over items that are not warehouses themselves — it is available for lakehouses, mirrored databases, and SQL databases in Fabric — while the warehouse item supplies full transactional T-SQL like a traditional enterprise data warehouse [S15]. If the landing pattern is Spark-first and consumers only need read/BI T-SQL, the lakehouse SQL analytics endpoint may be enough — it delivers T-SQL query access over the lakehouse without a separate warehouse item [S2]; a warehouse can be added later if writable T-SQL becomes a requirement [S1].

SQL database in Fabric (transactional, not analytical)#

SQL database in Fabric is a developer-oriented transactional database engine that shares the same underlying SQL Database Engine as Azure SQL Database, making it the designated home for OLTP workloads within the Fabric platform [S3]. It is the right target only when the workload being migrated is transactional rather than an analytical warehouse. Provisioning a SQL database automatically creates a companion SQL analytics endpoint, giving read-only analytical access to the replicated OneLake data without affecting the transactional workload [S3], and the platform continuously replicates its data into OneLake in near-real time as Parquet so downstream analytics can consume it without a manual export [S3]. SQL database in Fabric is built on the Hyperscale distributed-functions architecture, which separates the relational query-processing engine from long-term storage and lets storage scale out smoothly up to 128 TB while read compute scales independently of the primary OLTP write path [S16]. Portability into Fabric is handled via SqlPackage, which supports .bacpac import/export and .dacpac schema deployments for migration from Azure SQL Database [S3].

Mirroring as a transitional bridge#

Fabric Mirroring is a fully managed, serverless replication service that continuously brings data from external operational and analytical databases into OneLake, converting it to Parquet files in Delta Lake format, without users building or maintaining ETL pipelines [S4]. For organisations with data already in Azure SQL Database, mirroring provides a low-latency, low-cost path to continuously replicate that existing estate into OneLake without rebuilding the source database [S3]. Supported database mirroring sources (GA or preview as of June 2026) include Azure Cosmos DB, Azure SQL Database, Azure SQL Managed Instance, Azure Database for PostgreSQL/MySQL, SQL Server, Snowflake, Oracle, SAP, Google BigQuery, and Fabric SQL database, with Azure Databricks and Dremio via metadata mirroring [S4].

Inference (Fabric Codex): mirroring is best treated as a bridge in a migration, not the finish line — it lets Fabric BI run in parallel against a live replica while the source stays the system of record, after which a curated warehouse is built via Data Factory. This staging is a design choice, not a documented product requirement.

Data flow#

  1. Backfill (one-time bulk). Data Factory pipelines with Copy activity are Microsoft's recommended mechanism for large-scale one-time migrations (data lake or enterprise data warehouse into OneLake) and for high-volume recurring ingestion, because the engine is serverless, performant at scale, and cost-effective [S6]. A warehouse can also be loaded through COPY INTO, Dataflows, Spark bulk writes directly to the Delta tables, or cross-database CTAS / INSERT..SELECT / SELECT INTO [S1]. To scale ingestion beyond one Copy activity, fan out at the orchestration layer — drive multiple Copy activities in parallel from a ForEach loop, partitioning by table or file range [S6].
  2. Incremental sync. Data Factory offers three data-movement options: Copy job for simplified movement with built-in bulk, incremental, and CDC delivery styles; Copy activity for fully customizable movement with manual control of parallelism; and Mirroring for near-real-time database replication into OneLake [S5]. Mirroring is surfaced from Data Factory as a way to keep a near-real-time replica of an operational database in OneLake so analytics run against the replica instead of the source [S5]. Its internal replicator polls a landing zone at high frequency and merges incremental Delta files immediately; changes can propagate into OneLake in as little as 15 seconds under optimal conditions [S4], and mirroring's background replication compute is free and does not consume capacity units [S4].
  3. Serve. Because mirrored data lands in Delta Lake format in OneLake, Power BI semantic models can connect to it using Direct Lake mode for near-real-time reporting without importing or duplicating the data [S4]. Cross-database T-SQL queries can span mirrored databases, Fabric warehouses, and lakehouse SQL analytics endpoints in one statement using three-part naming, because all expose data through OneLake [S4].

Component responsibilities#

  • Data Factory — connects to 170+ sources across cloud, multicloud, and on-premises (via gateways), moves and orchestrates the migration, and is positioned by Microsoft as the next generation of Azure Data Factory with a published ADF/Synapse migration guide [S5]. Pipelines chain movement, notebooks/Spark/stored procedures/SQL scripts/dbt jobs, and control flow in a low-code designer [S5].
  • Mirroring — the managed replication layer for keeping a live replica during the bridge phase [S4].
  • Fabric Warehouse — the curated, writable T-SQL target; tables are Delta on OneLake [S1].
  • Semantic models — Direct Lake models read the Delta tables directly for BI [S4].

Cutover strategy#

Inference (Fabric Codex): the low-risk sequence is a parallel run — stand up the Fabric copy (via mirroring for the bridge and/or Data Factory Copy for the curated warehouse), reconcile results against the legacy DW, then repoint semantic models from the old source to Fabric. The following are grounded facts that shape that plan:

  • Direct Lake semantic models can sit on the mirrored/warehouse Delta tables during the parallel run [S4], so BI can be validated on Fabric before the legacy platform is retired.
  • After a Fabric deployment pipeline deploys a mirrored database item to a target workspace, replication does not start automatically — the target receives a configured but empty item and an operator must manually trigger replication, which performs a complete initial load from the current source state [S14]. Plan an explicit start step per environment.
  • Mirroring delivers a continuously updated current-state replica, not a change stream; stopping and restarting mirroring causes a full reseed from the present source [S14], so treat the bridge as durable until cutover completes.

Schema & T-SQL surface differences to plan for#

  • Oversized string types hurt performance: declare varchar(n) sized to real data instead of varchar(8000) or varchar(max), because statistics and cost estimation degrade as declared length diverges from actual values; Spark string columns with no length appear to the warehouse as varchar(8000) [S7].
  • The warehouse runs on MPP, but some semantics (TOP, global sorts, final result merging) force single-node execution and raise a 'non-scalable operation' warning; reduce the filtered dataset or apply OPTION (FORCE DISTRIBUTED PLAN) when single-node execution is not semantically required [S7].
  • Statistics (histogram, average column length, cardinality) are created and maintained automatically for the optimizer; CREATE/UPDATE STATISTICS remain available for single-column histograms during maintenance windows [S7].
  • Mirroring does not carry source security forward: row-level security, column-level security, and dynamic data masking defined at the source are not replicated, and must be rebuilt at the Fabric layer before exposing mirrored data to end users [S14]. Some source types are lossy — PostgreSQL mirroring silently drops unsupported column types (geometric, network, range, JSON/JSONB, XML, INTERVAL), and an Oracle NUMBER without explicit precision/scale blocks the whole table with InvalidDecimalPrecision [S14].

Performance#

  • Reading from the data lake is the dominant I/O cost for warehouse queries, so the engine layers local memory and SSD caches over remote storage; caching is fully transparent and always on across warehouse tables and shortcuts, and users cannot disable, configure, or clear it [S9]. A first execution can suffer a cold start (data fetched from OneLake, statistics generated, paused nodes resumed) — never judge performance from a first run, and detect remote reads via the queryinsights.exec_requests_history view [S7].
  • V-Order, a write-time Parquet sorting/compression optimization, is enabled by default on all warehouses to speed reads; it adds small ingestion overhead and cannot be re-enabled once disabled, so a common pattern is a V-Order-disabled staging warehouse feeding a V-Order-enabled read warehouse [S7].
  • CoddSpeed, a hardware-accelerated (GPU) query execution capability now built into the Fabric Data Warehouse engine, runs transparently under existing SQL — queries need no rewrite or hints because the engine decides internally which fragments are eligible [S13]. On TPC-H at 1 TB the accelerated engine reaches up to roughly a 30x speedup over the non-accelerated baseline, and end-to-end SQL-endpoint speedups versus a comparable cloud DW grow with concurrency (roughly 3x at 1 user, 6x at 16, 7x at 64) [S13].
  • Reading via a SQL analytics endpoint adds roughly a 30–60 second synchronisation delay on top of Delta replication latency; reading directly from the Delta layer via Spark avoids that extra lag when the freshest data is required [S14].

Governance & security#

  • Authentication to a Fabric SQL database is exclusively via Microsoft Entra ID (no SQL authentication), and a principal needs at least Read item permission before any connection succeeds [S3]; SQL database security includes RLS, OLS, workspace-level customer-managed keys, and SQL auditing [S3].
  • Rebuild source-defined RLS/CLS/masking at the Fabric layer for any mirrored data [S14].
  • Mirroring is not a disaster-recovery backup — mass deletion or corruption at the source will eventually replicate into the mirror, and the mirror cannot serve as a recovery point if the source is lost [S14]. For workloads that need both current-state access and an immutable history, combine mirroring for the live replica with a separate lightweight append-only ingestion pipeline [S14].

Cost & capacity#

  • Each Fabric capacity is a distinct resource pool; F SKUs run from F2 to F2048, the SKU number equals its capacity units, and each step doubles compute [S10]. F64 is the licensing threshold at which free-license users can view Power BI content with a workspace viewer role [S10].
  • Warehouse workloads benefit from burstable capacity and from smoothing, which spreads compute-consumption evaluation over time so spiky peaks offset idle periods [S8]. Warehouse operations are classified as background to benefit from 24-hour smoothing, though a background operation triggered by an interactive chain can be promoted to interactive throttling rules [S11].
  • By default the warehouse's compute is split 50/50 into an isolated SELECT pool and non-SELECT (ETL/ingestion) pool, so ingestion cannot starve read queries; workspace admins can replace this with custom SQL pools [S8]. During a heavy backfill this matters: ingestion and reporting compete on the same capacity.
  • The Warehouse and SQL analytics endpoint enforce a limit of 2048 user sessions per workspace, returning an explicit error when reached [S8]. To raise the concurrent read ceiling, a recommended pattern is OneLake shortcuts exposing read-only replicas of tables in other workspaces, spreading load across multiple SQL engines [S8].
  • Mirroring storage for replicated data is free up to 1 TB per capacity unit (an F64 includes 64 TB of free mirroring storage); charges apply beyond that or while the capacity is paused [S4].
  • If Spark is used for landing/transformation, each capacity unit grants two Spark vCores with a 3x burst multiplier, so an F64 exposes 384 Spark vCores, capping a custom Spark pool [S17].

Risks & anti-patterns#

  • Lift-and-shift that ignores the Delta/Polaris grain. Avoid trickle DML: each small INSERT/UPDATE/DELETE writes a new Parquet file, producing fragmented row groups, slower scans, higher cost, and more reliance on background compaction — batch writes and perform updates/deletes in batches instead [S7]. For COPY INTO, use source files of 100 MB–1 GB, many files for parallelism, parallel statements into different tables, and scale to a larger capacity for big volumes [S7]. Carrying over varchar(max) and unsized strings degrades statistics and plan costing [S7].
  • Treating mirroring as the migration end state. It drops source security, is not a backup, and a restart triggers a full reseed [S14]; when Block Public Internet Access is enabled at the tenant, most native mirroring connectors pause replication (only Open Mirroring, Cosmos DB, Azure SQL Managed Instance, and SQL Server 2025 keep running) [S14].
  • Private-link gaps. Pipeline Copy activity to and from Fabric Data Warehouse is unsupported when private links are enabled [S14, network] — see the source; validate connectivity constraints before committing an ingestion path (this claim is graded tier 4 community).

Assumptions#

  • The migrating workload is a T-SQL-first analytical warehouse (star/snowflake), so Fabric Warehouse is the default target; a Spark-first or OLTP workload would shift the decision to a lakehouse endpoint or SQL database respectively [S1] [S3] [S15].
  • A parallel-run cutover is acceptable operationally (the legacy DW can stay live until reconciliation completes).
  • Data volumes and concurrency are within a single capacity's practical range, sized per the Cost & capacity section; multi-capacity isolation is out of scope here.

Open questions#

  • Exact source-to-target type-mapping fidelity for a specific legacy engine beyond the mirroring caveats documented for PostgreSQL and Oracle [S14] — verify per source with the knowledge-curator if a source is not covered by a claim.
  • Precise capacity SKU for a given query concurrency and data volume — the claims give the levers (smoothing, 2048-session cap, split pools) but not a sizing formula; benchmark against a representative workload.

Internals#

Architecture & design#

The Fabric Warehouse and the SQL analytics endpoint share one underlying distributed processing architecture, and the backend is serverless: compute scales up and down autonomously to match demand rather than being provisioned by the user [S8]. Storage and compute are separated, which allows near-instant scaling, and workload management is autonomous — the distributed query engine has no user-facing tuning knobs [S1]. When a query arrives, the SQL frontend optimizes it and hands the plan to the Distributed Query Processing (DQP) engine, which splits it into smaller queries called tasks — the units of distributed execution — that run on backend compute nodes, with results flowing back through the frontend [S8]. The DQP scheduler represents each query as a DAG of tasks so independent tasks run simultaneously or out of order, giving both intra-query parallelism and cross-query concurrency [S8]. This is why a migrated warehouse needs no manual distribution/partition tuning of the kind legacy MPP platforms demanded.

How it works internally#

The warehouse's distributed engine descends from Polaris, Microsoft's cloud-native distributed SQL query engine built to converge data-warehouse and big-data workloads on one engine and to fully separate compute from state so execution is stateless and elastically scalable [S12]. Polaris abstracts every dataset as a grid of data cells indexed on a user-defined partition function (for aggressive partition pruning) and a system-chosen hash-distribution function (spreading cells across arbitrarily many nodes) [S12]; the cell abstraction makes the DQP storage-agnostic, operating at cell granularity while single-node extraction inside a cell is delegated to the local execution engine, letting the same framework query Parquet/Delta and transactional stores alike [S12]. Compute is stateless because all durable state — data, metadata, transaction logs — is externalized to remote storage and highly-available services; only caches live on compute nodes, and since caches rebuild lazily from persisted data their loss never fails in-flight work, enabling partial query restarts and online topology changes [S12]. Query compilation is two-phase: the SQL Server Cascades optimizer generates the logical MEMO, then a distributed cost-based phase enumerates physical plans using distribution properties both as correctness filters and as interesting properties, picking the plan that minimizes data movement [S12]. Task placement is affinitized to compute by consistently assigning the same data cells to the same nodes, preserving buffer-pool and local SSD caches across topology changes — shrinking loses only removed nodes' caches, growing preserves all existing caches while new nodes warm up [S12]. Ingestion runs through the same task engine: a task reads files from OneLake, joins/groups/orders data from other tasks, and for ingestion writes results to destination tables [S8]; every INSERT/UPDATE/DELETE appends a JSON file to the Delta log, and automatic checkpointing summarizes the log so readers parse only the latest checkpoint plus newer entries, sharply cutting metadata I/O on frequently updated tables [S7]. Because data lives in immutable Parquet, transaction rollback is fast — the engine reverts to previous file versions rather than undoing row changes [S7].

Performance characteristics#

The Polaris paper reports executing all 22 TPC-H queries at 1 PB on a pool of 420 execution nodes (heaviest joins Q9 and Q21 under two hours) and 5,000 concurrent TPC-DS sessions on just 10 compute nodes completing roughly 550,000 tasks with up to ~9,000 packed concurrently [S12] — the scale-and-concurrency evidence behind the serverless engine a migration inherits. On first access the engine transcodes file-format data into a compressed columnar in-memory representation, which improves compression and enables parallel execution across columns to exploit multi-core processors [S9]; anything in the in-memory cache is also serialized to a larger SSD second tier, and rehydrating memory from SSD is far faster than refetching from remote storage [S9]. When the scheduler detects resource pressure it autonomously scales out the topology online — in-flight queries continue while nodes are added (node acquisition typically takes a few seconds with no SLA), so the engine is not optimized for consistently sub-second latency on queries needing distributed processing [S8]. The engine is fault tolerant at task level: operations on an unhealthy node are redistributed to healthy nodes rather than failing the query [S8]. On the accelerated path, the CoddSpeed engine descends from the Tensor Query Processor lineage that reformulates relational operators (filter, join, group-by, order-by) as tensor operations, targeting PyTorch as a hardware-abstraction layer so the same operators run across accelerators, and reports outperforming CPU-only execution by more than an order of magnitude across production and benchmark scenarios [S13].