🏭🗒️Microsoft Fabric: Direct Lake [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 13-Oct-2026

[MF] Direct Lake

• {def} Power BI semantic model table storage mode option available in MF [1]
• optimized for large volumes of data to be quickly loaded into memory from Delta tables available in the OneLake [1] 
• leverages VertiPaq query engine [1]
• {benefit} deliver query performance comparable to Import mode 
• without the management overhead of data refresh cycles to load the entire data volume [1]
• similar to Import mode
• {difference}refreshes copies only metadata (aka framing)
•  [Import mode] replicates the data and creates an entire cached copy of the data for the semantic model [1]
•  depends on data preparation being done in the data lake [1]
• {benefit} enables high-performance interactive analysis [1]
•  ideal for semantic models connecting to large data sources
• e.g. lakehouse, warehouse and other data source with Delta tables [1]
• performance depends on well-tuned Delta tables [1]
•  {benefit} performance
• normally outperforms DirectQuery queries when loading and interacting with visuals in reports [1]
• {benefit}maximizes ROI because 
• analyzed data volumes can exceed capacity's max memory limits [1]
• only the needed data to answer a query is loaded into memory [1]  
• {benefit} minimizes data latencies 
•  makes new data available without refresh schedules [1]
• quickly and automatically synchronizing a semantic model with its sources [1]
• {operation} refresh 
• low-cost operation that analyzes the metadata of the latest version of the Delta tables and updates references to the latest files in OneLake
• DL moves data preparation to OneLake
• <-- uses the full breadth of Fabric technologies for data prep
• incl. Spark jobs, T-SQL DML statements, dataflows, pipelines, etc.
• {operation} transcoding
• the process of loading data from OneLake only when a query requests a column for the first time 
• {action} the semantic model receives a DAX (or MDX) query 
• {step} determines what columns are needed to produce the result
• {step} determines which columns are already resident in memory [2]
• {step} loads the missing columns from OneLake [2]
•  typically a fast operation, however it can depend on factors 
•  e.g. the cardinality of data stored in the columns
•  remains resident until there's reason for it to be removed (evicted) from memory
• {reason} the model or table was refreshed after a Delta table update at the source [2]
• {reason} no query used the column for some time [2]
• {reason} other memory management reasons [2]
•  e.g. memory pressure in the capacity due to other, concurrent operations [2]
•  SKU determines the maximum available memory for each Direct Lake semantic model on the capacity [2]
• {operation} framing 
• an operation triggered by a refresh of a semantic model [2]
• it usually takes only a few seconds to complete
• <-- because it's a low-cost operation where the semantic model analyzes the metadata of the latest version of the Delta Lake tables and updates to reference the latest Parquet files in OneLake [2]
• the process might evict resident table column segments and dictionaries from memory if the underlying data changed [2]
• the point in time of the refresh becomes the new baseline for all future transcoding events [2]
• DL queries only consider data in the Delta tables as of the time of the most recent framing operation [2]
• DL tables are queried to return data based on the state of the Delta table at the point of the most recent successful framing operation [2]
• that time isn't necessarily the latest state of the Delta tables [2]
• the semantic model analyzes the Delta log of each Delta table during framing to drop only the affected column segments and to reload newly added data during transcoding [2]
• {optimization} dictionaries usually aren't dropped when incremental framing takes effect, and new values are added to the existing dictionaries [2]
• helps reduce the reload burden and benefits query performance [2]
• when a Delta table receives no updates, no reload is necessary for columns already resident in memory [2]
•  queries show far less performance impact after framing because incremental framing essentially enables the semantic model to update substantial portions of the existing in-memory data in place [2]
• can fail if a Delta table exceeds the Fabric capacity guardrails [2]
•  e.g. when a Delta table has more than 10,000 parquet files [2]
•  {architecture} Direct Lake on OneLake
•  isn't coupled with the SQL analytics endpoint [2]
•  offers tighter integration with OneLake features such as OneLake security [2]
•  offers more efficient DAX query plans because [2]
• <-- isn’t required to check for SQL based security [2]
•  fallback isn't supported [2]
• {architecture} Direct Lake on SQL analytics endpoint
•  a DAX query might use DirectQuery fallback [2]
•  involves seamlessly switching to DirectQuery mode [2]
•  DirectQuery fallback
• retrieves data directly from the SQL analytics endpoint of the lakehouse or the warehouse [2]
•  fallback occurs when SQL based security is detected in the SQL analytics endpoints
•  a DirectQuery operation sends a query to the SQL analytics endpoint. Fallback operations might result in slower query performance [2]
•  {setting} Keep your Direct Lake data up to date
• performs automatic updates of Direct Lake tables [2]
• via semantic model settings >> Refresh 
• {enabled|default} ensures that data changes in OneLake are automatically reflected in the Direct Lake semantic model [2]
•  the semantic model performs a framing operation whenever data modifications in the underlying Delta tables are detected [2]
• {recommendation} leave the setting on, especially when you have a small or medium-sized semantic model
• useful in case of low-latency reporting requirements and Delta tables are modified regularly [2]
•  {disabled} it might be needed to disable automatic updates to allow completion of data preparation jobs or an ETL process before exposing any new data to consumers of the semantic model [2]
•  users can trigger a refresh manually in the Fabric portal [2]
•  via refresh schedule, or programmatically by using the Power BI REST API or TOM [2]
• 
  

References: 
[1] Microsoft Learn (2026) Direct Lake overview [link]
[2] Microsoft Learn (2026) 

Acronyms:
API - Application Programming Interface
DL - Direct Lake 
MDX - Multidimensional Expressions
MF - Microsoft Fabric
REST
SKU - Stock Keeping Unit
TOM - Tabular Object Model
T-SQL - Transact SQL

 

🏭🗒️Microsoft Fabric: Quotas [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 13-Apr-2025

[Microsoft Fabric] Quotas

• {def} assigned number of resources for an Azure subscription
• depend on the Azure subscription type [5]
• set and enforced in the scope of the subscription [1]
• each subscription has a default value for each quota [1]
• determine the maximum number of CUs for each of the capacities on the subscription [5]
• customers don't get charged for quotas, but on capacities [5]
• each quota represents a specific countable resource
• e.g. number of VMs that can be created [2]
• e.g. the number of storage accounts that can be used concurrently [2]
• e.g. the number of networking resources that can be consumed [2]
• e.g. the number of API calls to a particular service that can be made [2]
• designed to help protect customers from unexpected behavior [1]
• e.g. inaccurately resourced deployments, mistaken consumption
• helps minimize risks from deceptive or inappropriate consumption and unexpected demand [1]
• limitations
• ⇐ some limits are managed at a regional level [2]
• ⇐ variable and dependent on several factors
• {type} adjustable quotas
• quotas for which customers can request quota increases
• via Azure Home My quotas page
• based on amount or usage percentage and submitting it directly [1]
• each subscription has a default quota value for each quota [1]
• the quickest way to increase quotas [1]
• {type} non-adjustable quotas
• quotas which have a hard limit, usually determined by the scope of the subscription [1]
• to make changes, customers must submit a support request for the Azure support team [1]
• [Fabric] limit the number of CUs customers can provision across multiple capacities in a subscription
• calculation based on
• subscription plan type
• Azure region
• {action} provision a new capacity
• {level} Azure management group limits
• {level} Azure subscription limits
• {level} Azure resource group limits
• {level} Template limits
• {level} Microsoft Entra ID limits
• {level} Azure API Center limits
• {level} Azure API Management limits
• {level} API Management v2 tiers
• {level} Azure App Service limits
• {level} Azure Automation limits
• {action} request quota increase
• via support request
• there is no cost associated with requesting a quota increase [1]
• ⇐ costs are incurred based on resource usage, not the quotas themselves [1]
• should be based on workloads' characteristics [1]
• {action} view quota
• [permission] contributor role, or another role that includes contributor access [5]
• {action} manage quota
• [permission] see Quota Request Operator
• [Azure] quota alerts
• notifications triggered when the usage of a specific Azure resource nears the predefined quota limit [4]
• facilitates proactive resource management [4]
• multiple alert rules can be created for a given quota or across quotas in a subscription [4]

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2024) Fabric: Quotas overview [link] 

[2] Microsoft Learn (2025) Fabric: Azure subscription and service limits, quotas, and constraints [link] 

[3] Microsoft Learn (2025) Fabric: Quota monitoring and alerting [link] 

[4] Microsoft Fabric Update Blog (2024) Announcing the launch of Microsoft Fabric Quotas [link]
[5] Microsoft Learn (2025) Fabric: Microsoft Fabric capacity quotas [link]

Resources:

[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms:

API - Application Programming Interface
CU - Capacity Units

VM - Virtual Machine

🏭🗒️Microsoft Fabric: [Azure] Service Principals (SPN) [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 20-Jan-2025

[Azure] Service Principal (SPN)  

• {def} a non-human, application-based security identity used by applications or automation tools to access specific Azure resources [1]
• can be assigned precise permissions, making them perfect for automated processes or background services
• allows to minimize the risks of human error and identity-based vulnerabilities
• supported in datasets, Gen1/Gen2 dataflows, datamarts [2]
• authentication type 
• supported only by [2]
• Azure Data Lake Storage
• Azure Data Lake Storage Gen2
• Azure Blob Storage
• Azure Synapse Analytics
• Azure SQL Database
• Dataverse
• SharePoint online
• doesn’t support
• SQL data source with Direct Query in datasets [2]
• when registering a new application in Microsoft Entra ID, a SPN is automatically created for the app registration [4]
• the access to resources is restricted by the roles assigned to the SPN
• ⇒ gives control over which resources can be accessed and at which level [4]
• {recommendation} use SPN with automated tools [4]
• rather than allowing them to sign in with a user identity  [4]
• {prerequisite} an active Microsoft Entra user account with sufficient permissions to 
• register an application with the tenant [4]
• assign to the application a role in the Azure subscription [4]
• ⇐ requires Application.ReadWrite.All permission [4]
• extended to support Fabric Data Warehouses [1]
• {benefit} automation-friendly API Access
• allows to create, update, read, and delete Warehouse items via Fabric REST APIs using service principals [1]
• enables to automate repetitive tasks without relying on user credentials [1]
• e.g. provisioning or managing warehouses
• increases security by limiting human error
• the warehouses thus created, will be displayed in the Workspace list view in Fabric UI, with the Owner name of the SPN [1]
• applicable to users with administrator, member, or contributor workspace role [3]
• minimizes risk
• the warehouses created with delegated account or fixed identity (owner’s identity) will stop working when the owner leaves the organization [1]
• Fabric requires the user to login every 30 days to ensure a valid token is provided for security reasons [1]
• {benefit} seamless integration with Client Tools: 
• tools like SSMS can connect to the Fabric DWH using SPN [1]
• SPN provides secure access for developers to 
• run COPY INTO
• with and without firewall enabled storage [1]
• run any T-SQL query programmatically on a schedule with ADF pipelines [1]
• {benefit} granular access control
• Warehouses can be shared with an SPN through the Fabric portal [1]
• once shared, administrators can use T-SQL commands to assign specific permissions to SPN [1]
• allows to control precisely which data and operations an SPN has access to  [1]
• GRANT SELECT ON <table name> TO <Service principal name>  
• warehouses' ownership can be changed from an SPN to user, and vice-versa [3]
• {benefit} improved DevOps and CI/CD Integration
• SPN can be used to automate the deployment and management of DWH resources [1]
• ⇐ ensures faster, more reliable deployment processes while maintaining strong security postures [1]
• {limitation} default semantic models are not supported for SPN created warehouses [3]
• ⇒ features such as listing tables in dataset view, creating report from the default dataset don’t work [3]
• {limitation} SPN for SQL analytics endpoints is not currently supported
• {limitation} SPNs are currently not supported for COPY INTO error files [3]
• ⇐ Entra ID credentials are not supported as well [3]
• {limitation} SPNs are not supported for GIT APIs. SPN support exists only for Deployment pipeline APIs [3]
• monitoring tools
• [DMV] sys.dm_exec_sessions.login_name column [3] 
• [Query Insights] queryinsights.exec_requests_history.login_name [3]
• Query activity
• submitter column in Fabric query activity [3]
• Capacity metrics app: 
• compute usage for warehouse operations performed by SPN appears as the Client ID under the User column in Background operations drill through table [3]

Previous Post  <<||>>   Next Post

References:

[1] Microsoft Fabric Updates Blog (2024) Service principal support for Fabric Data Warehouse [link]

[2] Microsoft Fabric Learn (2024) Service principal support in Data Factory [link]

[3] Microsoft Fabric Learn (2024) Service principal in Fabric Data Warehouse [link] 

[4] Microsoft Fabric Learn (2024) Register a Microsoft Entra app and create a service principal [link]

[5] Microsoft Fabric Updates Blog (2024) Announcing Service Principal support for Fabric APIs [link]

Resources:

[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link] 

 

Acronyms:

ADF - Azure Data Factory

API - Application Programming Interface

CI/CD - Continuous Integration/Continuous Deployment

DMV - Dynamic Management View

DWH - Data Warehouse

SPN - service principal

SSMS - SQL Server Management Studio

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part VIII: Permissions) [new feature]

Data-based solutions usually target a set of users who (ideally) have restricted permissions to the functionality. Therefore, as part of the process are defined several personas that target different use cases, for which the permissions must be restricted accordingly. 

In the simplest scenario the user must have access to the underlying objects for querying the data. Supposing that an Entra User was created already, the respective user must be given access also in the Fabric database (see [1], [2]). From database's main menu follow the path to assign read permissions:
Security >> Manage SQL Security >> (select role: db_datareader)

Manage SQL Security

Manage access >> Add >> (search for User)

Manage access

(select user) >> Share database >> (select additional permissions) >> Save

Manage additional permissions

The easiest way to test whether the permissions work before building the functionality is to login over SQL Server Management Studio (SSMS) and check the access using the Microsoft Entra MFA. Ideally, one should have a User's credentials that can be used only for testing purposes. After the above setup was done, the new User was able to access the data. 

A second User can be created for testing with the maximum of permissions allowed on the SQL database side, which is useful for troubleshooting. Alternatively, one can use only one User for testing and assign or remove the permissions as needed by the test scenario. 

It's a good idea to try to understand what's happening in the background. For example, the expectation was that for the Entra User created above also a SQL user is created, which doesn't seem to be the case, at least per current functionality available. 

 Before diving deeper, it's useful to retrieve User's details: 

-- retrieve current user
SELECT SUser_Name() sys_user_name
, User_Id() user_id 
, USER_NAME() user_name
, current_user [current_user]
, user [user]; 

Output:

sys_user_name	user_id	user_name	current_user	user
JamesClavell@[domain].onmicrosoft.com	0	JamesClavell@[domain].onmicrosoft.com	JamesClavell@[domain].onmicrosoft.com	JamesClavell@[domain].onmicrosoft.com

Retrieving the current User is useful especially when testing in parallel functionality with different Users. Strangely, User's ID is 0 when only read permissions were assigned. However, a valid User identifier is added for example when to the User is assigned also the db_datawriter role. Removing afterwards the db_datawriter role to the User keeps as expected User's ID. For troubleshooting purposes, at least per current functionality, it might be a good idea to create the Users with a valid User ID (e.g. by assigning temporarily the db_datawriter role to the User). 

The next step is to look at the Users with access to the database:

-- database access 
SELECT USR.uid
, USR.name
--, USR.sid 
, USR.hasdbaccess 
, USR.islogin
, USR.issqluser
--, USR.createdate 
--, USR.updatedate 
FROM sys.sysusers USR
WHERE USR.hasdbaccess = 1
  AND USR.islogin = 1
ORDER BY uid

Output:

uid	name	hasdbaccess	islogin	issqluser
1	dbo	1	1	1
6	CharlesDickens@[...].onmicrosoft.com	1	1	0
7	TestUser	1	1	1
9	JamesClavell@[...].onmicrosoft.com	1	1	0

For testing purposes, besides the standard dbo role and two Entra-based roles, it was created also a SQL role to which was granted access to the SalesLT schema (see initial post):

-- create the user
CREATE USER TestUser WITHOUT LOGIN;

-- assign access to SalesLT schema 
GRANT SELECT ON SCHEMA::SalesLT TO TestUser;
  
-- test impersonation (run together)
EXECUTE AS USER = 'TestUser';

SELECT * FROM SalesLT.Customer;

REVERT; 

Notes:
1) Strangely, even if access was given explicitly only to the SalesLT schema, the TestUser User has access also to sys.sysusers and other DMVs. That's valid also for the access over SSMS
2) For the above created User there are no records in the sys.user_token and sys.login_token DMVs, in contrast with the user(s) created for administering the SQL database. 

Let's look at the permissions granted explicitly:

-- permissions granted explicitly
SELECT DPR.principal_id
, DPR.name
, DPR.type_desc
, DPR.authentication_type_desc
, DPE.state_desc
, DPE.permission_name
FROM sys.database_principals DPR
     JOIN sys.database_permissions DPE
	   ON DPR.principal_id = DPE.grantee_principal_id
WHERE DPR.principal_id != 0 -- removing the public user
ORDER BY DPR.principal_id
, DPE.permission_name;

Result:

principal_id	name	type_desc	authentication_type_desc	state_desc	permission_name
1	dbo	SQL_USER	INSTANCE	GRANT	CONNECT
6	CharlesDickens@[...].onmicrosoft.com	EXTERNAL_USER	EXTERNAL	GRANT	AUTHENTICATE
6	CharlesDickens@[...].onmicrosoft.com	EXTERNAL_USER	EXTERNAL	GRANT	CONNECT
7	TestUser	SQL_USER	NONE	GRANT	CONNECT
7	TestUser	SQL_USER	NONE	GRANT	SELECT
9	JamesClavell@[...].onmicrosoft.com	EXTERNAL_USER	EXTERNAL	GRANT	CONNECT

During troubleshooting it might be useful to check current user's permissions at the various levels via sys.fn_my_permissions:

-- retrieve database-scoped permissions for current user
SELECT *
FROM sys.fn_my_permissions(NULL, 'Database');

-- retrieve schema-scoped permissions for current user
SELECT *
FROM sys.fn_my_permissions('SalesLT', 'Schema');

-- retrieve object-scoped permissions for current user
SELECT *
FROM sys.fn_my_permissions('SalesLT.Customer', 'Object')
WHERE permission_name = 'SELECT';

Notes:
1) See also [1] and [4] in what concerns the limitations that apply to managing permissions in SQL databases.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Fabric: Share your SQL database and manage permissions [link]
[2] Microsoft Learn (2024) Microsoft Fabric: Share data and manage access to your SQL database in Microsoft Fabric  [link]
[3] Microsoft Learn (2024) Authorization in SQL database in Microsoft Fabric [link]
[4] Microsoft Learn (2024) Authentication in SQL database in Microsoft Fabric [link]

[5] Microsoft Fabric Learn (2025) Manage access for SQL databases in Microsoft Fabric with workspace roles and item permissions [link]