From Warehouse to Microsoft Fabric: A Practical Migration Path for BI Teams

The scenario: a tired warehouse and a nervous BI team

Imagine this setup:

• On‑prem SQL Server data warehouse
• Dozens of SSIS packages feeding it nightly
• A handful of key Power BI reports pointing directly at the warehouse
• One data engineer who built most of it and is about to leave

Leadership has defined a key objective: “Migrate the existing data warehouse to Fabric and modernise BI.” It’s written into your team’s goals and even appears in new BI Analyst job ads.

The pain points are familiar:

• Refreshes overrun the nightly window
• No simple way to share data with external partners
• Governance is mostly Excel-based documentation
• Every change to the warehouse schema risks breaking reports

We’ll use this scenario to anchor the migration steps, so you can map them to your own environment.

Step 1: Decide what “migration to Fabric” actually means

“Move to Fabric” is vague. You need to translate it into concrete components.

For a typical SQL data warehouse, your Fabric target architecture will usually include:

• OneLake – the unified storage layer
• Lakehouse – for files + tables, good for mixed workloads
• Data Warehouse (Fabric DW) – for a more traditional SQL warehouse experience
• Data Pipelines – to replace or complement SSIS
• Dataflows Gen2 – for reusable Power Query logic

For our scenario, a pragmatic target looks like this:

1. Raw zone in a Lakehouse
   • Land source data as parquet/Delta tables (or even CSV initially)
2. Curated model in Fabric Warehouse or Lakehouse tables
   • Replace existing warehouse fact/dim tables incrementally
3. Power BI semantic models in Fabric
   • Point existing and new reports at Fabric, not the old warehouse

Clarify this with your stakeholders early. It turns “migrate the warehouse” into a design you can actually build.

Step 2: Inventory what you have (and what’s fragile)

Before touching Fabric, map the current warehouse. You’re not doing a full enterprise data catalog; you’re identifying what can break.

Focus on:

• Critical tables
  • Fact tables used by many reports (e.g. FactSales, FactOrders)
  • Dimensions with business logic baked in (e.g. DimCustomer, DimProduct)
• Key ETL jobs
  • SSIS packages with complex logic or custom scripts
  • Stored procedures that materialise important tables
• Downstream dependencies
  • Power BI datasets and reports
  • Excel workbooks and other tools pointing at the warehouse

A simple inventory table in Excel or a Fabric Warehouse table is enough:

CREATE TABLE dbo.ObjectInventory (
    ObjectType      NVARCHAR(50), -- Table, View, SSIS, Report
    ObjectName      NVARCHAR(256),
    Criticality     NVARCHAR(20), -- High, Medium, Low
    DependsOn       NVARCHAR(256),
    Notes           NVARCHAR(1000)
);

Populate it as you discover objects. Mark anything that would cause immediate pain if it broke as Criticality = 'High'.

For our scenario, we identify:

• FactSales, DimCustomer, DimDate as critical
• 3 SSIS packages feeding them
• 5 Power BI reports that leadership uses weekly

These become the first candidates for migration.

Step 3: Choose a migration pattern (and avoid the big bang)

You have three broad options:

1. Lift‑and‑shift

   • Recreate the warehouse schema in Fabric Warehouse
   • Rebuild ETL in Fabric Data Pipelines
   • Point reports to the new warehouse
   • Fastest, but carries all your old design issues forward

2. Rebuild and modernise

   • Redesign the model (e.g. star schema improvements, naming, slowly changing dimensions)
   • Adopt Lakehouse patterns and Delta files
   • More work, more long‑term payoff

3. Hybrid, incremental migration (usually best)

   • Start by landing data in OneLake
   • Gradually move transformations and tables into Fabric
   • Keep the old warehouse running until the new path is proven

For our scenario, we pick the hybrid approach:

• Keep the on‑prem warehouse live during the transition
• Start by copying a subset of tables (FactSales, DimCustomer, DimDate)
• Validate in parallel with existing reports

This reduces risk and keeps your nervous stakeholders calm.

Step 4: Land data in OneLake and Fabric

First concrete step: get data from your existing warehouse into Fabric.

Option A: Use Data Pipelines from on‑prem SQL Server

If your warehouse is on‑prem SQL Server, you can:

1. Create a Data Pipeline in Fabric
2. Set up a linked service to your on‑prem SQL via a gateway
3. Use a Copy activity to move tables into a Lakehouse or Fabric Warehouse

A typical pattern:

• Source: dbo.FactSales (SQL Server)
• Sink: Sales table in a Lakehouse or Warehouse
• Schedule: nightly, matching your existing refresh

You can layer incremental logic later. Start with full loads for a small number of tables.

Option B: Use Dataflows Gen2 for lighter ETL

If your logic is already in Power Query (e.g. in Power BI or Excel), you can:

• Lift that M code into Dataflows Gen2
• Output directly into a Lakehouse table or Warehouse

Example: basic M query against your existing warehouse:

let
    Source = Sql.Database("OnPremServer", "LegacyDW"),
    FactSales = Source{[Schema="dbo", Item="FactSales"]}[Data],
    FilteredRows = Table.SelectRows(FactSales, each [OrderDate] >= #date(2023, 1, 1))
in
    FilteredRows

You can point this at Fabric storage instead of loading into a Power BI dataset.

For our scenario, we start with Data Pipelines for the heavy tables and Dataflows Gen2 for a couple of smaller dimensions.

Step 5: Rebuild the core model in Fabric

Once the data lands in OneLake, you need to recreate your semantic model.

Decide: Lakehouse vs Fabric Warehouse

For BI‑centric workloads, both can work:

• Fabric Warehouse
  • T‑SQL friendly
  • Feels familiar to SQL DW teams
• Lakehouse
  • Better for mixed data types and data science
  • Direct access to files and Delta tables

For our scenario (traditional BI, strong SQL skills), we:

• Land raw data in a Lakehouse (bronze layer)
• Build curated tables in a Fabric Warehouse (silver/gold)

Implement transformations

You can:

• Port stored procedures into Warehouse T‑SQL
• Or, use Dataflows Gen2 / Data Pipelines for transformations

Example: simple dimension build in Fabric Warehouse:

CREATE OR ALTER VIEW dbo.DimCustomer_Curated AS
SELECT
    c.CustomerID,
    c.CustomerName,
    c.Segment,
    c.Country,
    CASE WHEN c.IsActive = 1 THEN 'Active' ELSE 'Inactive' END AS Status,
    CAST(c.CreatedDate AS DATE) AS CreatedDate
FROM Lakehouse.dbo.CustomerRaw AS c;

You can materialise this view into a table for performance.

Build the semantic model

In Power BI (inside Fabric):

1. Create a new semantic model connected to the Fabric Warehouse
2. Import FactSales, DimCustomer, DimDate
3. Recreate relationships and measures

Example measure:

Total Sales := SUM ( FactSales[SalesAmount] )

Sales LY := 
CALCULATE ( 
    [Total Sales], 
    DATEADD ( DimDate[Date], -1, YEAR )
)

Customer Count := DISTINCTCOUNT ( DimCustomer[CustomerID] )

Try to reuse measure names and logic from the existing model where possible; it simplifies report migration.

Step 6: Migrate reports without breaking the business

Now you have:

• Data refreshed into Fabric
• A recreated core model (FactSales + key dimensions)

You can start moving reports.

Strategy: parallel, not big bang

For each critical report:

1. Clone the report
   • Save a copy (e.g. "Sales Performance – Fabric")
2. Switch the dataset
   • Point it to the new Fabric semantic model
3. Fix broken visuals
   • Update fields/measures if names changed
4. Validate with users
   • Compare numbers between old and new

You’ll almost always find small differences. Track them explicitly:

• Rounding and data type differences
• Filter logic that changed in the model
• Historical data coverage if you initially migrated a subset

For our scenario, we run the old and new Sales report side‑by‑side for a month. Once business users trust the new one, we retire the old dataset and hide the legacy report.

Step 7: Replace legacy ETL step by step

So far, you’re effectively replicating the warehouse into Fabric. To complete the migration, you need to:

1. Move upstream sources to Fabric
   • Instead of reading from the old warehouse, connect Fabric to the original source systems
2. Rebuild transformations
   • Move logic from SSIS/stored procedures into Data Pipelines, Dataflows Gen2, or Warehouse SQL
3. Turn off redundant jobs
   • Once Fabric outputs are validated, decommission the corresponding SSIS packages

A practical approach:

• For each SSIS package:
  • Identify the target tables it populates
  • Rebuild the flow in a Fabric Data Pipeline
  • Run both for a few cycles and compare row counts and sample records

You can use simple queries in the old and new environments to validate:

-- Old DW
SELECT COUNT(*) AS RowCount, SUM(SalesAmount) AS TotalSales
FROM dbo.FactSales
WHERE OrderDate >= '2024-01-01';

-- Fabric Warehouse
SELECT COUNT(*) AS RowCount, SUM(SalesAmount) AS TotalSales
FROM dbo.FactSales
WHERE OrderDate >= '2024-01-01';

Document each SSIS package you fully replace, and keep a clear cutover list for your operations team.

Step 8: Governance and performance in the new world

Once the core path is working, you need to make Fabric manageable.

Governance basics

• Workspaces
  • Separate dev/test/prod
  • Limit who can publish to production
• Security
  • Use Fabric roles and AD groups
  • Implement row‑level security in semantic models where needed
• Documentation
  • Keep a simple, living data dictionary in a shared location
  • Capture source → transformation → target mappings

Performance basics

• Incremental loads
  • Don’t reload entire fact tables daily
  • Use watermark columns (e.g. ModifiedDate) in pipelines
• Model optimisation
  • Avoid wide tables with unused columns
  • Pre‑aggregate where it makes sense

These are the differences between “we copied some data into Fabric” and “we successfully migrated the warehouse.”

What this means for your role as a BI Analyst

That job ad asking for “experience migrating a data warehouse to Fabric” is really asking for someone who can:

• Understand the existing warehouse structure and dependencies
• Design a realistic target in Fabric (Lakehouse vs Warehouse, pipelines, models)
• Plan and execute an incremental migration
• Keep reports and stakeholders stable during the change

You don’t need to be a full‑time data engineer, but you do need to:

• Read and write basic SQL for Warehouse objects
• Work comfortably with Dataflows Gen2 and semantic models
• Communicate migration risks and trade‑offs clearly

In our scenario, the BI team goes from “we just build reports on top of whatever the warehouse gives us” to “we own the path from source to semantic model in Fabric.” That’s exactly the shift employers are trying to hire for.

One practical next step

Pick one critical report in your environment and:

1. Identify its source tables in the current warehouse
2. Recreate just those tables and measures in Fabric (Warehouse or Lakehouse)
3. Clone the report and point it at the new model

If you can do that reliably and explain the process, you’re already doing a real data warehouse → Fabric migration on a small scale. The rest of the migration is repeating that pattern with more tables, more pipelines, and more stakeholders.