Lakehouse vs Traditional SQL Data Warehouse on Azure: A Practical Migration Checklist for BI Teams

Scenario: A BI Team Stuck Between Two Worlds

Picture a BI team supporting finance and operations:

• Current platform: Azure SQL Database or Azure SQL Managed Instance as the data warehouse, fed by Azure Data Factory / Synapse pipelines.
• Storage: Some data is already landing in Azure Data Lake Storage Gen2, but only as an ETL staging area.
• BI: Power BI import models and some DirectQuery datasets; a few SSRS-style paginated reports.
• New requirements: Data science notebooks, semi-structured data (JSON from APIs), and near-real-time telemetry.

Leadership wants "a lakehouse" on Azure. The question is whether to stay with the traditional SQL-centric warehouse or move to a lakehouse pattern using Azure Databricks (Delta Lake) or Microsoft Fabric (Lakehouse + Warehouse). The team needs a migration checklist that keeps:

• Existing Power BI reports running.
• Data quality and governance intact.
• ETL complexity from exploding.

The sections below walk that scenario from assessment to cutover.

1. Clarify What “Lakehouse” Means in Your Azure Stack

On Azure, "lakehouse" usually means one of these patterns:

1. Databricks Lakehouse

   • Storage: ADLS Gen2
   • Format: Delta Lake tables (Parquet + transaction log)
   • Compute: Databricks clusters / SQL warehouses
   • Access: Spark, SQL, Python, R, Power BI via Import or DirectQuery to Databricks SQL warehouses; Power BI can also ingest Delta/Parquet via Dataflows/Power Query.

2. Microsoft Fabric Lakehouse + Warehouse

   • Storage: OneLake (logical layer over Azure storage)
   • Format: Delta tables
   • Objects:
     • Lakehouse: Files + tables, optimized for big data and notebooks.
     • Warehouse: SQL-first interface over the same or related data.
   • Access: Power BI can use Import, Direct Lake (for supported Fabric items such as Warehouse and some Lakehouse tables), or DirectQuery.

3. Synapse-style SQL + Data Lake (not a full lakehouse, but common)

   • Storage: ADLS Gen2
   • Compute: Synapse SQL pools (dedicated or serverless) over Parquet/CSV.
   • Limited lakehouse semantics compared to Delta Lake or Fabric.

Before planning migration, decide:

• Primary engine for transformation: Spark (Databricks/Fabric) vs T-SQL (SQL DW/MI).
• Primary format for curated data: Delta tables vs relational tables only.
• Primary consumption path for BI: Power BI over lakehouse/warehouse vs direct SQL DB.

For our scenario, assume the team is moving to Databricks Lakehouse on ADLS Gen2 or Fabric Lakehouse+Warehouse, while keeping Azure SQL for a while to serve legacy reports.

2. Inventory: What You Actually Need to Migrate

Start with a hard inventory of what depends on the existing SQL data warehouse.

2.1 Data Assets

List, per schema/table:

• Fact tables (grain, volume, refresh frequency).
• Dimension tables (slowly changing, reference, security-related).
• Staging tables and materialized views.
• Stored procedures used for ETL or business logic.

Capture:

• Data types, constraints, indexes.
• Partitioning strategy (if any).
• Row-level security or filtering logic.

2.2 Downstream Consumers

For each dataset/report:

• Power BI datasets: Import vs DirectQuery, data source type, refresh schedule.
• Paginated reports: data sources and critical queries.
• External apps: anything reading directly from the warehouse.

Flag:

• Reports that must stay near-real-time.
• Reports with complex T-SQL logic embedded (window functions, temp tables, etc.).

This inventory drives which parts can move to lakehouse first and which stay on SQL longer.

3. Architecture Comparison: Where Lakehouse Changes Your Life

Use the scenario to compare lakehouse vs traditional SQL DW along a few key axes.

3.1 Storage and Format

Traditional SQL DW (Azure SQL DB/MI)

• Data is stored in relational tables inside the database.
• Schema-on-write: you must define schema before load.
• Good for structured data with clear relational models.

Lakehouse (Databricks/Fabric)

• Data stored in Delta tables on a data lake.
• Handles structured and semi-structured data.
• ACID transactions, time travel, and schema evolution supported (with constraints and caveats depending on engine and configuration).

Impact on our BI team:

• Ingesting JSON, CSV, and logs becomes simpler.
• You can keep raw, bronze, silver, and gold layers in the same lake.
• Data scientists can share the same curated tables as BI.

3.2 Compute and Transformation

Traditional SQL DW

• ETL in T-SQL, SSIS-like patterns, or Data Factory/Synapse pipelines.
• Strong for set-based relational transformations.

Lakehouse

• ETL/ELT in Spark (PySpark, Scala, SQL) or Fabric Dataflows/Notebooks.
• Supports streaming and batch pipelines.

For the scenario:

• Slowly changing dimensions can be implemented with Delta merges instead of stored procedures.
• Complex business logic may need to be re-implemented in Spark SQL or left in a SQL warehouse layer.

3.3 BI Connectivity

From Azure SQL DW to Power BI

• Import or DirectQuery via the SQL connector.
• Mature, predictable behavior.

From Lakehouse to Power BI

• Databricks: Power BI can connect via:
  • Import or DirectQuery to Databricks SQL warehouses using the Databricks connector.
  • Import from Parquet/Delta via Dataflows or Power Query.
• Fabric: Power BI can use:
  • Direct Lake for supported Fabric items (e.g., Warehouse and supported Lakehouse tables).
  • Import or DirectQuery to the Fabric Warehouse or Lakehouse SQL endpoint.

For our team, the key is to avoid breaking existing Power BI models while introducing lakehouse-backed datasets gradually.

4. Migration Checklist: From SQL DW to Lakehouse Without Breaking BI

Use this checklist as a practical sequence rather than a theoretical roadmap.

Step 1: Stabilize the Data Lake as System of Record

Even if you still load into SQL DW, ensure:

• All raw data lands in ADLS Gen2 (or OneLake in Fabric) first.
• You have clear folder/zone conventions:
  • /bronze/source/system/date/ (raw)
  • /silver/domain/table/ (cleaned, conformed)
  • /gold/mart/subject_area/ (BI-ready)

This allows you to:

• Rebuild curated structures in lakehouse without reworking upstream ingestion.

Step 2: Recreate Core Facts and Dimensions as Delta Tables

Pick a limited scope first: e.g., the finance fact tables and shared dimensions.

Example: Migrating a Fact Table to Delta (Databricks)

Assume you have a FactSales table in Azure SQL. You create a matching Delta table in your lakehouse:

-- Databricks SQL example: create Delta table in the lakehouse
CREATE TABLE lakehouse_gold.FactSales
USING DELTA
LOCATION 'abfss://gold@yourstorageaccount.dfs.core.windows.net/sales/fact_sales'
AS
SELECT
    s.SalesId,
    s.OrderDate,
    s.CustomerId,
    s.ProductId,
    s.Quantity,
    s.NetAmount,
    s.CurrencyCode
FROM sql_dw.FactSales s;

Notes:

• sql_dw.FactSales here is a configured external connection to your existing SQL data warehouse (via Databricks SQL or JDBC) exposed as a catalog/schema in Databricks.
• The USING DELTA + LOCATION pattern persists the table as a Delta Lake table in ADLS Gen2.

Repeat this pattern for key dimensions, ensuring:

• Keys and grain match the current warehouse.
• Critical business columns are present and correctly typed.

Step 3: Implement SCD Logic in the Lakehouse

Slowly changing dimensions often rely on SQL stored procedures. In a lakehouse, use MERGE into Delta tables.

Example for a type 2 dimension in Databricks SQL:

MERGE INTO lakehouse_silver.DimCustomer AS target
USING lakehouse_bronze.CustomerIncremental AS source
ON target.CustomerId = source.CustomerId
AND target.IsCurrent = TRUE
WHEN MATCHED AND (
    target.CustomerName <> source.CustomerName OR
    target.CustomerSegment <> source.CustomerSegment
) THEN
  UPDATE SET target.IsCurrent = FALSE, target.ValidTo = current_timestamp()
WHEN NOT MATCHED THEN
  INSERT (
    CustomerId,
    CustomerName,
    CustomerSegment,
    IsCurrent,
    ValidFrom,
    ValidTo
  ) VALUES (
    source.CustomerId,
    source.CustomerName,
    source.CustomerSegment,
    TRUE,
    current_timestamp(),
    TIMESTAMP '9999-12-31 23:59:59'
  );

This preserves the SCD behavior while moving it into your lakehouse processing.

Step 4: Expose Lakehouse Tables to Power BI Safely

Do not switch all reports at once. Instead:

1. Create new Power BI datasets that point to lakehouse tables:

   • Databricks: use the Databricks connector to a SQL warehouse, or Power Query/Dataflows over Delta/Parquet.
   • Fabric: create a dataset over the Fabric Warehouse or Lakehouse tables (using Direct Lake where supported, or Import/DirectQuery).

2. Mirror existing models:

   • Use the same table names and relationships where possible.
   • Keep measure definitions in DAX identical initially.

3. Validate data parity:

   • Build a Power BI report comparing key metrics between old (SQL DW) and new (lakehouse) datasets.
   • Use visuals like tables or KPIs showing differences by date, product, etc.

Only when parity is acceptable should you:

• Repoint existing reports to the new dataset.
• Or retire the old dataset and reassign reports.

Step 5: Decide What Stays in SQL (At Least for Now)

Not everything needs to move immediately. For our scenario, typical candidates to stay in SQL for a while:

• Highly transactional reporting that benefits from SQL engine behavior.
• Legacy paginated reports with complex stored procedure dependencies.
• Third-party tools that only support traditional SQL connections.

Use these patterns:

• Keep a small curated SQL layer fed from the lakehouse (via scheduled loads) for those workloads.
• Let new analytics and self-service BI hit the lakehouse directly.

Step 6: Governance, Security and Lineage

You cannot treat lakehouse as a side project. For a BI team, the key governance tasks are:

• Identity and access:

  • Use Azure AD-based access control for ADLS Gen2 or Fabric workspaces.
  • Map data lake permissions to roles already used in SQL DW.

• Row-level security (RLS):

  • In Power BI, keep RLS definitions in the dataset; they work regardless of SQL vs lakehouse source.
  • If you had RLS in SQL, move the logic to either Power BI or a Fabric Warehouse/Databricks SQL layer that supports equivalent filtering.

• Catalog and lineage:

  • Use a catalog (e.g., Unity Catalog for Databricks or Fabric’s item catalog) to register tables.
  • Document mappings: SQLDW.Schema.Table → Lakehouse.Zone.Table.

For the scenario, this is often where finance gets nervous. The mitigation is clear, consistent access controls and visible lineage from lakehouse tables back to source systems.

5. Performance and Cost: Avoiding Surprises

Moving to a lakehouse changes performance and cost behavior.

5.1 Performance Considerations

• Lakehouse engines are optimized for large scans and analytical workloads.
• Query performance depends heavily on:
  • Partitioning strategy of Delta tables.
  • File sizes and layout.
  • Use of caching or materialized views (depending on engine).

For BI workloads:

• Use gold tables specifically shaped for BI (denormalized fact tables, conformed dimensions).
• Avoid pushing very complex row-by-row logic into Power BI; keep heavy transformations in lakehouse pipelines.

5.2 Cost Considerations

• SQL DW: predictable compute and storage billing; performance tiers and DTUs/vCores.
• Lakehouse: separate costs for storage (ADLS/OneLake) and compute (Databricks/Fabric).

Checklist items:

• Monitor job runtimes and cluster usage during migration.
• Compare cost of maintaining both SQL DW and lakehouse vs consolidating.
• Avoid running overlapping pipelines (SQL ETL and lakehouse ETL doing the same work) longer than necessary.

6. Cutover Strategy for the BI Team

Bring the scenario to a clean end-state using a phased cutover.

1. Phase 1 – Dual-run core marts

   • Finance fact and dimension tables exist in both SQL DW and lakehouse.
   • BI team validates parity and performance.

2. Phase 2 – Power BI pivot

   • New reports use lakehouse-backed datasets.
   • Critical existing reports are gradually repointed.

3. Phase 3 – SQL DW slimming

   • Remove ETL into SQL DW for tables now sourced from lakehouse.
   • Keep a minimal SQL layer if needed for legacy tools.

4. Phase 4 – Full lakehouse ownership

   • Lakehouse becomes the primary curated data store.
   • SQL DW is either decommissioned or reduced to a thin compatibility layer.

Throughout, keep:

• A clear rollback path (e.g., ability to point datasets back to SQL DW if needed).
• Versioned scripts for Delta table creation and ETL logic.

Practical Takeaway

For an Azure BI team, the safest way to move from a traditional SQL data warehouse to a lakehouse is to treat the lakehouse as a new curated layer built side-by-side, prove parity with your existing marts, and only then flip Power BI and other consumers over in phases. If you start with a disciplined inventory and a small, high-value subject area, you can learn the lakehouse patterns without putting your core reporting at risk.