How to Integrate SQL Databases with Fabric Lakehouses

Why this matters: Data platforms often suffer from the "latency-vs-governance" trade-off, where transactional data sits in a silo away from analytical engines. By orchestrating SQL databases alongside Fabric Lakehouses, you can maintain transactional integrity while enabling high-performance analytics without complex ETL re-engineering.

Many engineers treat the Lakehouse as a monolithic destination for all data, leading to complex ingestion pipelines just to view simple operational state. Consider a hypothetical scenario where you have a primary application database (e.g., Azure SQL Database) and an analytical workspace in Fabric.

The "Before" approach involves heavy ELT:

-- The "Before" trap: Staging everything as raw Parquet
COPY INTO 'abfss://<container>@<storage>.dfs.core.windows.net/raw/'
FROM dbo.Transactions
WITH (FILE_TYPE = 'PARQUET');

The "After" approach, leveraging Fabric’s ability to interact with external SQL sources via shortcuts or Mirrored Databases, allows for a more streamlined architecture. You keep your operational schema where it lives, exposing it to the Lakehouse for unified semantic modeling without constant full-load migrations.

Prerequisites

• A Microsoft Fabric workspace with a capacity (Fabric trial or SKU) assigned.
• An existing Azure SQL Database (or SQL Server 2022 instance) with public or private endpoint connectivity.
• Appropriate permissions: "Contributor" or "Administrator" on the Fabric workspace and "db_datareader" on the source SQL database.
• Knowledge of the Microsoft Fabric Mirroring feature (currently in public preview for Azure SQL Database).

Photo by GuerrillaBuzz on Unsplash

Step 1: Establishing a Mirrored Database in Fabric

Instead of writing scripts to move data, you can use the Mirroring feature to create a replica of your SQL database directly within your Fabric workspace. This process creates a specialized item that synchronizes data near real-time from your source to the OneLake.

Navigate to your workspace, select New, and choose Mirrored Azure SQL Database. You will need the server name and database name. Once configured, Fabric manages the synchronization process.

-- This is handled by the Fabric Mirroring engine
-- Once linked, you can query via T-SQL in the Fabric SQL Analytics endpoint
SELECT TOP 10 * FROM [dbo].[Transactions]
WHERE [TransactionDate] >= DATEADD(day, -7, GETDATE());

This creates a mirrored representation of your data in Delta format inside OneLake, which is automatically cataloged in the Fabric metastore.

Step 2: Referencing Operational Data in Lakehouse Notebooks

Once the mirrored data is available in OneLake, you can join it with other tables in your Lakehouse using PySpark. This is particularly useful for data quality checks where you need to compare transactional totals against landing zone aggregates.

# Accessing the mirrored table using the SQL Analytics endpoint path
df_mirrored = spark.read.format("delta").table("dbo.Transactions")

# Perform a quick validation check
row_count = df_mirrored.count()
print(f"Total records in transactional mirror: {row_count}")

# Hypothetical quality check: ensure no null IDs
if df_mirrored.filter("TransactionID IS NULL").count() > 0:
    raise ValueError("Data quality check failed: Null IDs detected.")

Step 3: Enabling Direct Lake for Operational Reporting

The power of having this operational data in Fabric is the ability to use Direct Lake mode for your semantic models. Direct Lake allows Power BI to read the Delta files directly from OneLake without importing them into the model or requiring a DirectQuery gateway, providing performance comparable to Import mode with the freshness of DirectQuery.

1. In your workspace, create a Semantic model.
2. Select the mirrored table as a source.
3. Ensure the storage mode is set to Direct Lake.
4. Define your DAX measures:

-- DAX measure for operational monitoring
Total Sales = SUM('Transactions'[Amount])

-- Daily average with time-intelligence
Daily Avg Sales = 
CALCULATE(
    AVERAGE('Transactions'[Amount]),
    DATESYTD('Date'[Date])
)

Photo by Brecht Corbeel on Unsplash

Common pitfalls

• Schema Evolution: When using Mirroring, be aware that changes to the source schema might take a moment to propagate. Always validate your downstream Power BI reports after significant DDL changes on the source SQL database.
• Capacity Throttling: Mirroring consumes compute resources within your Fabric capacity. If you have high-frequency transactional data, monitor your "Fabric Capacity Metrics app" to ensure you aren't hitting utilization limits during peak sync periods.
• Permission Mismatches: Ensure that the service principal or user identity used to create the connection has consistent access. A common failure pattern is having "Read" access on the SQL Database but failing to configure the Fabric workspace identity for cross-region data access.
• Preview Limitations: Features like "Mirroring" are subject to change. Always check the official Microsoft Learn documentation for the most current limitations, such as supported data types or specific SQL features that might not be replicated.

Conclusion

By treating the SQL database as a first-class citizen of the Lakehouse rather than a source to be "dumped," you reduce pipeline fragility and latency. You gain the ability to perform complex analytical validation on transactional data without duplicating the storage footprint unnecessarily.

Try it: Identify one operational table that is currently being extracted via a daily batch job. Attempt to enable the Mirrored Azure SQL Database feature in a sandbox workspace and see if you can replace that batch job with a Direct Lake semantic model.

Cover photo by Marc PEZIN on Unsplash.

---

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.