Direct Lake on SQL with Fabric Data Warehouse

Author: Justin Barry - Sr. Cloud Solution Architect

____________________________________________________

Introduction

We’re kicking off a multi-part series on Direct Lake best practices in Microsoft Fabric. In this installment, we’ll focus on Direct Lake on SQL backed by a Fabric Data Warehouse, examining how it behaves in this architecture and why Fabric Data Warehouse is particularly well-suited for this storage mode. Be sure to stay tuned for our next post, where we will explore Direct Lake on OneLake using a Fabric Data Warehouse.

Modern analytics teams are expected to deliver fast, interactive insights over ever-growing datasets without duplicating data or maintaining fragile refresh pipelines. Traditional approaches force tradeoffs: imported models deliver performance but require additional data copies and refresh overhead, while query-based models preserve freshness but often struggle with responsiveness at scale.

Direct Lake addresses this tension by changing how Power BI semantic models access data in Fabric. Instead of importing data into a separate storage layer, Direct Lake transcodes Delta Parquet data stored in OneLake into memory on demand. Data is loaded when first queried and retained until evicted due to inactivity, data changes, or memory pressure. Once resident in memory, queries achieve import-like performance while maintaining near real-time freshness, without duplicating data. Because performance is governed by available memory, organizations can select the appropriate Fabric capacity SKU based on the expected size and workload characteristics of the semantic model.

Problem context and relevance

Across industries, analytics teams face similar challenges when scaling business intelligence on large datasets.

• Retail organizations struggle to deliver fast, interactive reporting on continuously changing sales and inventory data without relying on frequent semantic model refreshes.

• Healthcare analytics teams require responsive dashboards over large and operational datasets, while maintaining strict governance and near real-time data access.

• Financial services organizations must balance high performance expectations with governed access to centralized warehouse data, often resulting in complex BI architectures and duplicated semantic models.

In many cases, these challenges stem not from limitations of the data warehouse itself, but from how BI models interact with it. Traditional patterns often force teams to choose between duplicating data into imported semantic models or issuing live queries for every user interaction. As data scales and concurrency increases, both approaches introduce friction—either through increased operational overhead or inconsistent query performance. The following diagram shows a comparison of storage modes in Fabric for traditional patterns, alongside the new Direct Lake storage mode:

Figure: Semantic Model Storage Modes.

Solution overview

How the architecture works with Direct Lake on SQL

Power BI semantic models connect to the SQL analytics endpoint of the Fabric DW and read Delta tables from OneLake using Direct Lake mode. As previously mentioned, data in a Direct Lake storage mode pages data into memory from Delta Parquet files. The same underlying Delta tables remain accessible through the SQL analytics endpoint from the warehouse for data transformations, composite model scenarios, or when Direct Lake falls back to DirectQuery. The following high-level architecture diagram of Direct Lake on SQL illustrates when transcoding occurs as data is paged into memory for processing by the VertiPaq engine within a semantic model:

Figure: How Transcoding Works.

A Note on Direct Lake Options: It's important to recognize that Microsoft Fabric offers two flavors of Direct Lake—Direct Lake on SQL and Direct Lake on OneLake. This post focuses on Direct Lake on SQL, which remains widely used today and is the right choice when relying on SQL-based security enforced at the warehouse level. However, Direct Lake on OneLake is the more strategic path forward and will receive greater investment over time. Direct Lake on OneLake connects directly to Delta tables in OneLake, bypassing the SQL analytics endpoint and enabling scenarios like multi-source semantic models across Lakehouses and Warehouses.

Additionally, OneLake Security will eventually be the long-term replacement for SQL-based security in Fabric, providing unified row-level and column-level security enforcement across all Fabric engines. Our follow-up post will cover Direct Lake on OneLake in depth.

Advantages of using Direct Lake with Fabric DW

Unlike Direct Lake on Lakehouse, which requires explicit management of OPTIMIZE, VACUUM, V-ORDER, and clustering strategies, Fabric Data Warehouse automates these storage optimization mechanisms to ensure consistently performant and guardrail-compliant Direct Lake workloads through:

• File Consolidation: Continuously consolidates small parquet files into the optimal 100 MB to 1 GB size range, preventing file proliferation that degrades performance and triggers guardrail violations.
• Statistics Management: Maintains up-to-date histogram and cardinality statistics for the Query Optimizer, ensuring efficient query plans without manual intervention.
• V-Order by Default: Applies V-Order to all tables automatically for read optimization. V-Order reorganizes parquet data to improve compression and query performance for Direct Lake transcoding.
• Reduced Operational Overhead: Data engineers can focus on data modeling and business logic rather than scheduling maintenance scripts or monitoring file fragmentation.
• Guardrail Protection: By automatically maintaining optimal file sizes and counts, Fabric DW reduces the risk of Direct Lake fallback due to exceeding parquet file limits.
• Data Clustering: With the use of data clustering in Fabric DW, this technique is used to organize and store data based on similarity. It improves query performance and reduces compute and storage access costs by group similar records together.

Best practices for using Direct Lake on SQL

Optimize for cardinality

Cardinality is the most important factor for Direct Lake performance. High‑cardinality columns such as transaction IDs and timestamps compress poorly and consume significantly more memory than low‑cardinality columns like status codes and categories.

Design principles

• Use integer surrogate keys instead of string business keys: An integer (INT or BIGINT) compresses dramatically better than a string when repeated millions of times.

• Move high‑cardinality descriptive columns to dimension tables: Customer names and product descriptions belong in dimensions, not facts.
• Avoid calculated columns in warehouse tables: Use DAX measures instead; they calculate on demand without increasing model size.
• Right‑size data types for your data: Use INT for smaller dimensions (under 2 billion rows) and BIGINT for large dimensions and fact table foreign keys. Avoid NVARCHAR(100) when TINYINT or SMALLINT would suffice for status codes with limited values.

Build focused star schemas

While only referenced columns are loaded in Direct Lake, wide fact tables or dimensions increase the likelihood that unnecessary columns are paged into memory through visuals, filters, relationships, or DAX, leading to higher memory consumption than required. For more information, review the guidance on building recommended star schemas.

Keep tables lean

• Include only columns used in reports.

• Keep fact tables narrow, containing measurements and foreign keys only.
• Use role‑playing dimensions instead of duplicating date columns.
• Separate historical data when it is not required for reporting.

Be aware of Direct Lake on SQL fallback concepts

Direct Lake falls back to DirectQuery when model size exceeds capacity limits, SQL views are used as sources, or SQL based security is detected in the SQL analytics endpoint. For SQL-based security, DirectQuery operations send a query to the SQL analytics endpoint, which might result in slower query performance.

Direct Lake fallback guidance

• Use base tables as sources instead of views and create relationships in Power BI. SQL views are not supported as Direct Lake sources and will always use DirectQuery.
• Row-level security (RLS) applied at the SQL layer forces fallback to DirectQuery. Instead, if different users must be restricted to subsets of data, enforce RLS only at the semantic model layer. That way, users benefit from high performance in-memory queries. In this case, it's strongly recommended that the cloud connection uses a fixed identity instead of SSO.
• Be aware of thresholds that when exceeded, can cause performance degradation by triggering fallback to DirectQuery. These limits are evaluated independently, meaning violating even one will result in fallback. Key thresholds to monitor include:
• Maximum row count per table: Exceeding this limit can increase memory usage and lead to slower query performance, ultimately forcing a fallback to DirectQuery.
• Maximum number of Parquet files per table: Too many small Parquet files can result in inefficient queries, as the system must scan a larger number of files again triggering fallback to Direct Query.
• Total model memory limits: When the model exceeds available memory, Direct Lake must fall back to DirectQuery, impacting performance by shifting from in-memory to real-time queries.
• Monitor model size proactively and create focused models by business domain before limits are reached. Be aware that DirectQuery has its own set of limitations that may impact report functionality if fallback occurs.
• Using data types Direct Lake handles efficiently, this reduces memory footprint, improves compression ratios, and helps ensure the model stays within capacity guardrails.
• Set DirectLakeBehavior to “DirectLakeOnly” in development to identify queries which would trigger fallback. Use “Automatic” (default) in production to allow fallback when needed, ensuring reports remain functional.

Create efficient relationships

• Use single‑column relationships with integer keys.
• Ensure one‑to‑many relationships from dimension to fact tables.
• Avoid bidirectional filtering unless necessary.
• Mark date tables appropriately for time intelligence.

Use composite models strategically

Composite models combine Direct Lake with other storage modes. Use them when integrating external data sources or adding small reference tables. Keep designs simple when possible. Pure Direct Lake models are easier to troubleshoot and tune. But it is important to design for both modes. The dual-mode design means that even if your model grows beyond Direct Lake limits, your reports continue performing well if needed in DirectQuery storage mode.

Understand cache state behavior

Direct Lake performance varies based on the cache state of the data. Understanding these states helps set realistic expectations and guides optimization strategies:

• Cold Cache: The first query after model deployment or eviction requires transcoding Delta Parquet files into memory. This initial load is slower but still significantly faster than traditional import refresh.

• Semi-Warm Cache: Some columns are in memory from previous queries, but new columns or tables are being accessed for the first time.
• Warm Cache: Most frequently accessed columns are in memory, providing near-import performance for typical queries.
• Hot Cache: All actively used data is resident in memory, delivering maximum performance.

Efficient permission management

Power BI periodically queries the SQL analytics endpoint to check for permission changes, ensuring security policies remain synchronized. To optimize these:

• Limit the tables exposed to the Power BI semantic models.

• Grant access only to tables which are used for reporting.

This reduces the overhead of permission validation queries and improves efficiency.

Monitoring and optimization tools

Implementing Direct Lake best practices requires ongoing monitoring and optimization to ensure the semantic model maintains optimal performance and stays within capacity guardrails. Community-built tools enable proactive optimization of Direct Lake's key performance factors: data paging speed into memory and query execution times. While Fabric DW automatically handles delta file optimization, these tools help you understand how your warehouse table design choices and semantic model design impact Direct Lake performance, enabling data-driven optimization decisions.

• Delta Analyzer: The Delta Analyzer evaluates the physical storage layout of Delta tables to assess their suitability for Direct Lake. It surfaces storage-level metrics including parquet file counts, file size distribution, row group cardinality, and partition characteristics. This helps identify structural inefficiencies such as small files, poorly sized row groups, or partition skew before they impact semantic model performance or trigger guardrail violations.
• VertiPaq Analyzer (via DAX Studio): DAX Studio is an external tool designed for analyzing and optimizing DAX queries. It also bundles the VertiPaq Analyzer, which allows inspection of the semantic model's internal structure, including table and column sizes, cardinality, and compression ratios for when data is paged into the model.
• Semantic Link Labs: Semantic Link Labs is a Python library for Microsoft Fabric notebooks that extends Semantic Link for managing semantic models. It helps evaluate Direct Lake guardrails, identify tables at risk of falling back to DirectQuery, diagnose fallback causes, and reduce cold start delays by warming up key columns or datasets. This code-first tool supports continuous optimization of Direct Lake model performance and reliability.

Real-world impact

Organizations implementing Direct Lake best practices with Fabric Data Warehouse report significant operational benefits across their analytics platforms.

Common observable benefits include:

• Import-like query performance without scheduled refresh overhead.

• Real-time data availability replacing stale data from refresh cycles.

• Elimination of refresh windows and refresh failure troubleshooting.

• Simplified architecture by consolidating models that previously required separate refresh schedules.

• Automatic storage optimization handled by Fabric DW eliminates manual file maintenance.

• Consistent governance and security across SQL and BI workloads through unified access to the same Delta tables.

Success with Direct Lake on Fabric Data Warehouse comes from balancing semantic model design and leveraging warehouse capabilities. Optimizing the model through cardinality, star schema, and relationship design, combined with Fabric DW's automatic delta optimization, data clustering, and governed access, maximizes Direct Lake efficiency while maintaining strong performance when fallback to DirectQuery occurs.

Call to action

Ready to transform your analytics architecture?

Begin your Direct Lake journey with a single, high-impact use case. Validate the approach, measure the results, then expand across your organization. The capability of Direct Lake semantic models is evolving rapidly. Make sure to check out the latest list of considerations and limitations.

Be sure to explore Microsoft Fabric documentation for implementation details and advanced scenarios. Also, don’t forget to check out the Microsoft Fabric Blog for anything and everything Fabric!

Next in this series: Direct Lake on OneLake using a Fabric Data Warehouse. Stay tuned!