# 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](https://www.azureguru.net/row-level-security-in-microsoft-fabric).

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](https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview#directquery-fallback).

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](https://www.azureguru.net/implement-medallion-architecture-with-scd-type-2-in-microsoft-fabric-part-2) article. The warehouse is a star schema.

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740599053704/c568a82d-1282-477c-9287-fc882f7aa35b.png align="left")

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

```sql
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
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1740609950984/d00ba2a7-7a6e-4452-9959-70988f23c069.png align="left")
    

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

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740599359012/6d206008-b1e5-41df-b6f8-585a91559a09.png align="left")

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

```sql
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`

```sql
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`

```sql
select * from sys.security_policies
```

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740600094616/837bc414-ac4a-4fb0-b166-3a345324f4e7.png align="left")

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`.

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740600396900/f96801c7-1aa8-4c13-abb9-b7b6991b0560.png align="left")

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

![RLS and Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740600780541/c62701f3-8ac6-4ed0-89c3-456f317bb21d.png align="left")

Define the relationships across all the entities in the models

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740602178797/ca3cedbd-b01c-446e-817d-1dc66fb7481b.png align="left")

Notice the information icon in red on the `Fact_Sales`table 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.

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740602247401/d366c52f-9ee7-4269-979e-19c94453385f.png align="center")

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

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740602148042/84038b42-6047-46ad-b3d6-aa335ec5cd62.png align="left")

Refresh the Semantic model and the refresh will succeed.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1740602501453/f988ed02-25eb-40c8-b5c6-18127e48fd9c.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1740609186215/6cdd8224-602c-49bd-800e-32ed822cf04a.png align="center")

Now change the Direct Lake Behavior to Direct Lake Only.

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740602593900/9e5704ed-d7c2-400f-b2c4-d8bc6c462e9d.png align="left")

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

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740602914114/1df7735e-0c5f-4a42-9ee2-2a82e3ea4d80.png align="left")

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

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740603047797/99136541-60ec-4e10-a2a0-cb23e22fbde7.png align="left")

This time the refresh succeeds

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1740603098760/59f5f45e-9a9e-4fe3-841c-81cf2ad10add.png align="center")

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

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740603289897/e5144a8f-cbd7-4680-a3b9-51ac9950a0e0.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1740609314628/d1aedba6-df8a-4bdf-a7da-401e3844860f.png align="center")

### 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.

```csharp
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

![RLS and Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740604718119/3977c859-b8a3-402a-aec8-d602c918f85a.png align="left")

Login with user : `fabricuser_1@azureguru.net`

![Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740604753782/b4c9310a-eccb-40a2-a25f-9d9fe21d3693.png align="left")

Login with user : `fabricuser_2@azureguru.net`

![RLS and Direct Lake Behavior](https://cdn.hashnode.com/res/hashnode/image/upload/v1740604837866/5d42453a-aff4-464c-9621-33fe86cae081.png align="left")

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 !!!