Overview#

A data warehouse migration to Fabric is not just a lift of SQL scripts. Fabric Warehouse provides a T-SQL analytical surface over OneLake-backed Delta storage, while lakehouses, pipelines, and semantic models often surround the warehouse as ingestion, transformation, and consumption layers [S1] [S2] [S3].

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

Core concepts#

Fabric Warehouse is the SQL-first analytical target when teams need warehouse semantics and T-SQL access over curated data [S1]. Data Factory Copy activity supports large-scale data movement into OneLake and Fabric destinations, while lakehouses can handle Spark-first transformation before data is served to SQL teams [S2] [S3]. Power BI and semantic models remain the governed consumption layer above the migrated warehouse [S4] [S5].

The migration decision is therefore about workload shape: SQL-first marts and reporting databases belong near Warehouse, while file-heavy engineering, complex Spark transformation, or semi-structured inputs often belong in a lakehouse before they become warehouse-ready [S1] [S3].

How it works and best practices#

Inventory source tables, transformation logic, security rules, and downstream reports before choosing the target surface. Use Copy activity for scalable movement, validate unsupported T-SQL patterns early, and decide whether transformations should be rewritten as warehouse SQL, Spark, or a lakehouse-to-warehouse handoff [S1] [S2] [S3].

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

Implementation example#

A warehouse migration should classify each source workload before choosing the Fabric target. SQL-first analytical marts fit Warehouse, Spark-owned curated Delta tables fit a lakehouse plus SQL endpoint, and operational application data fits SQL database in Fabric with analytical replication [S1] [S3].

sql
-- Illustrative migration validation query.
-- Run equivalent aggregates in source and target to compare row counts and business totals.
SELECT
    OrderDate,
    COUNT_BIG(*) AS RowCount,
    SUM(NetSalesAmount) AS NetSalesAmount
FROM warehouse_gold.FactSales
GROUP BY OrderDate
ORDER BY OrderDate;

Inference: the SQL is a validation pattern, not a product limit or migration tool guarantee. The sourced mechanics are Warehouse SQL analytics, Copy activity movement, lakehouse coexistence, and semantic model consumption [S1] [S2] [S3] [S4].

yaml
migration_plan:
  phase_1_profile:
    - source_tables
    - sql_features
    - report_dependencies
  phase_2_land:
    tool: copy_activity
  phase_3_transform:
    choose:
      - warehouse_sql
      - spark_lakehouse
  phase_4_consume:
    semantic_model: certified

The plan reflects sourced Fabric components rather than a single automated migration promise: Copy activity moves data, Warehouse and Lakehouse provide different analytical surfaces, and semantic models serve governed BI [S1] [S2] [S3] [S4].

What goes wrong#

Treating migration as only schema conversion misses the operational changes. Fabric capacity, workload management, caching, file layout, and semantic model reuse all affect the migrated experience [S1] [S4] [S6]. Another common error is keeping duplicated extracts after migration rather than consolidating analytical data in OneLake-backed stores [S3].

Internals#

Architecture & design#

Fabric Warehouse stores analytical data in OneLake while presenting a SQL warehouse surface [S1]. The surrounding migration architecture often uses Data Factory for movement, lakehouses for Spark transformation, and semantic models for certified consumption [S2] [S3] [S4].

How it works internally#

Warehouse query behavior depends on the distributed SQL engine, cache state, statistics, and workload management controls [S6] [S7]. Data loaded or transformed through adjacent Fabric engines still lands in the shared OneLake-oriented architecture [S1] [S3].

Performance characteristics#

Performance work after migration should focus on query shape, cache behavior, statistics, data distribution through the engine, and capacity/workload management rather than assuming source-system tuning rules transfer unchanged [S6] [S7].

Worked example#

A legacy reporting warehouse can be migrated by landing source extracts with Copy activity, rewriting curated star-schema transformations into Warehouse SQL where appropriate, using a lakehouse for Spark-heavy preparation, and publishing a certified semantic model for reports [S1] [S2] [S3] [S4].