Row Level Security(RLS) and Data Lake Query Fallback

Few months ago I had penned an article on RLS(Row Level Security) in Microsoft Fabric. You can find that article here.

With a practical example, this article will focus solely on how RLS affects the Data Lake behavior. There are multiple factors that affect Direct Lake behavior. You can find more details here.

A "data lake query fallback" is a scenario that occurs when a query originally meant to access data directly from a "Direct Lake" mode instead defaults to querying a traditional data warehouse or source system through "Direct Query" mode. This typically happens when certain conditions arise such as exceeding memory limits, complex data access needs, or limitations within the data lake. As a result, query performance may slow down due to the need to retrieve data from an alternative source.

Implement Row Level Security

Let’s begin by implementing Row Level Security on a Fabric warehouse. I will apply RLS to an existing warehouse that I created in this article. The warehouse is a star schema.

I will modify the Fact_Sales table in the warehouse and add a new column called Users.

Alter Table Fact_Sales
Add Users varchar(50)

I have three users in my Entra account

• sachin.nandanwar@azureguru.net

• fabricuser_1@azureguru.net

• fabricuser_2@azureguru.net

  

I will randomly update the Users column values with two users(fabricuser_1@azureguru.net and fabricuser_2@azureguru.net)from the Entra account.

In the next step I will create a Table Valued Function in the warehouse to validate the users.

CREATE OR ALTER   FUNCTION [dbo].[tvf_CheckUser](@UserName AS varchar(500))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_CheckUser
WHERE @UserName = USER_NAME()OR USER_NAME() = 'sachin.nandanwar@azureguru.net';
GO

The function ensures that the user sachin.nandanwar@azureguru.net has access to all the rows in the table.

Next, we create a Security Policy that adds a filter predicate of the function tvf_CheckUser created earlier on the table Fact_Sales for column Users

CREATE SECURITY POLICY CustomerOrdersSecurityPolicy
ADD FILTER PREDICATE dbo.tvf_CheckUser([Users])
ON dbo.Fact_Sales
WITH (STATE = ON);
GO

You can confirm the Security Policy by running a query on the system catalog sys.security_policies

select * from sys.security_policies

Now that we have implemented RLS, lets create a semantic model and validate the Data Lake behavior.

Create Semantic Model

Open the warehouse and under Reporting tab select New Semantic Model.

Select the objects in the warehouse for the model, provide a name to the model and click Confirm.

Define the relationships across all the entities in the models

Notice the information icon in red on the Fact_Salestable on top right.

Clicking the Tables option and hovering over the Fact_Sales table in the Data property in the semantic model reveals the details.

To check the Direct Lake Behavior setting, goto the Model tab and click the Semantic Model and confirm the Direct Lake Behavior.

Refresh the Semantic model and the refresh will succeed.

We can also use the DAX function TABLETRAITS() to check the data lake fallback behavior.

Now change the Direct Lake Behavior to Direct Lake Only.

Refresh the Semantic model and the refresh fails as we have forced the Direct Lake behavior on a model having RLS.

Lets now change the Direct lake behavior to Direct Query Only.

This time the refresh succeeds

However, the information icon in red now appears on all the objects of the semantic model.

Running the DAX function TABLETRAITS() returns the fallback behavior.

Automate the Change to the Data Lake fallback behavior setting

We can use the TOM library to automate the change of the Direct Lake behavior of the models of a given workspace.

using Microsoft.AnalysisServices.Tabular;

namespace QueryFallback
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/{Your workspace}";
            string connectString = $"DataSource={workspaceConnection};";

            Server server = new Server();
            server.Connect(connectString);

            foreach (Database db in server.Databases)
            {
                if (db.Model.DirectLakeBehavior == DirectLakeBehavior.DirectLakeOnly || db.Model.DirectLakeBehavior == DirectLakeBehavior.DirectQueryOnly)
                {
                    db.Model.DirectLakeBehavior = DirectLakeBehavior.Automatic;
                    db.Model.SaveChanges();
                }
            }
        }
    }
}

Double check if RLS works

To verify if RLS works as expected, lets log in with different users and check the data points displayed in the report. The values of the data points should vary based on the user login and the user access.

Check the values of Sum of TotalSales by UnitPrice visual on the report across the three users.

Login with user : sachin.nandanwar@azureguru.net and returns the entire data from the semantic model

Login with user : fabricuser_1@azureguru.net

Login with user : fabricuser_2@azureguru.net

The values of the data point in the report change depending on the user logins thus confirming that RLS is working as expected.

Conclusion

In conclusion, a Direct Lake query fallback occurs when a query, originally designed to retrieve data directly from a data lake, instead falls back to querying the source system. This typically happens due to factors such as memory limits, complex data access requirements, or RLS like in our case. While this ensures query execution continues, it may result in slower performance since the data needs to be retrieved from an alternative source.

Thanks for reading !!!