Forum Discussion
Onelake security with large tables
Hello,
I am somewhat green in the Data engineering world, especially when it comes to Fabric so I'd appreciate any advice. But here are broadly the steps / decisions taken to where I'm at:
We landed a bunch of business data in our gold lakehouse and created a seperate analytics lakehouse with shortcuts to gold. Next step was to create a semantic model, and someone made it a "DirectLake on SQL" model. We got some reports working. So far so good.
Next we wanted RLS and have it controlled through Entra groups, so we set up groups for specific domains i.e. group_domain_1 - group_domain_9 + one group for group_domain_all. We decided to go with OneLake security for RLS since it is no longer in preview but this is where the struggles started. I could not make it work consistently and it seems almost random when visuals will error out and when I can get some data through. I read this sql-analytics-endpoint-onelake-security so it seems like it should be possible to combine a DirectLake on SQL model with OneLake security but I could not make it work consistently.
I found that "DirectLake on OneLake" is recommended when you want to use OLS so I created a new model with that and everything was good for a while again. Then I increased our amount of data from ~20M rows to 250M~ and turns out I started hitting the limit of our SKU F8 capacity and "DirectLake on OneLake" can not use "fallback to DirectQuery" which means the report just stopped working for all users.
So where did I go wrong? Is DirectLake on SQL supposed to work with OneLake security or how are one supposed to use OneLake security with large tables? Seems like I would have to 4x the cost of our capacity to make DirectLake on Onelake work and then I guess a custom implementation is better.
Happy for any advice,
Thank you for the response,
What is so confusing for me is that I could make it work with a DirectLake on SQL model, just in a very inconvenint way:
1. Give user read on the lakehouse
2. Give user read role in Onelake security
3. This is the confusing one, I had to explicitly add an RLS rule on all tables I wanted the user to see.
With no RLS rules added, I didn't see everything as one would expect, I saw nothing. My only guess to why this worked is that when RLS applied, the query got small enough to not having to fallback to DirectQuery and thus not going through the SQL endpoint, but this is just my guess, I have no clue tbh. This is however not a feasible solution so I moved on.
Anyways, it turned out we had not used any V-Ordering on the table, so I turned V-Order on and ran optimize + z-order and i shrunk the table by almost half, so now DirectLake on OneLake is working again.
5 Replies
Hi FabricJacob,
What size spark nodes are you using?
THe way OneLake security works, it spins up additional spark instances to check for access, so if you reduce the size of the default node in your workspace, you should have a better experience. I wish this was documented better.Also, DIrectLake on SQL models do work with OneLake Security, there's just one more step, you need to switch the SQL Endpoint auth mode to user identity mode instead of delegated identity mode. You can do that by opening the SQL endpoint in Fabric, clicking on the security tab, and then in there you can change the auth mode. Once it is set to User Identity, OneLake security applies to the SQL Endpoint and to all DirectLake on SQL models.
To be honest I know very little of what's going on with Spark Nodes, but Node size in all my workspaces is Medium 1 - 6 nodes. What's the drawback on reducing the size?
I should have mentioned it, but User identity mode has been set during all my testing of this, and the semantic model uses Default SSO connection.
Hello FabricJacob
There's a small nuance to this if you're using a semantic model to access data - Microsoft documentation says that
to access data with RLS from a semantic model, the semantic model needs to use Direct Lake on OneLake.This is probably the reason why you see odd bevaviour with the visuals.Thank you for the response,
What is so confusing for me is that I could make it work with a DirectLake on SQL model, just in a very inconvenint way:
1. Give user read on the lakehouse
2. Give user read role in Onelake security
3. This is the confusing one, I had to explicitly add an RLS rule on all tables I wanted the user to see.
With no RLS rules added, I didn't see everything as one would expect, I saw nothing. My only guess to why this worked is that when RLS applied, the query got small enough to not having to fallback to DirectQuery and thus not going through the SQL endpoint, but this is just my guess, I have no clue tbh. This is however not a feasible solution so I moved on.
Anyways, it turned out we had not used any V-Ordering on the table, so I turned V-Order on and ran optimize + z-order and i shrunk the table by almost half, so now DirectLake on OneLake is working again.
Hi FabricJacob,
Thank you for sharing the outcome. It's great to hear that enabling V-Order and running OPTIMIZE/Z-ORDER reduced the table size significantly and that Direct Lake on OneLake is working again. This information will likely help other community members who encounter similar Direct Lake capacity or performance challenges with large tables.
Thank you!
