Privacy by Design in Fabric: Implementing Shift-Left Data Protection
Privacy by Design in Microsoft Fabric: Implementing "Shift-Left" Data Protection for Enterprise Analytics Abstract While data engineers and architects often prioritize pipeline performance, Medallion architectures, and complex transformations, regulatory compliance and data security are frequently treated as afterthoughts. In highly regulated sectors like banking and finance, failing to protect Personally Identifiable Information (PII) can lead to severe penalties under evolving frameworks such as GDPR, PIPEDA, and other international privacy laws. This article explores the concept of "Shift-Left" data protection within Microsoft Fabric—implementing data masking, hashing, and external tokenization to secure sensitive data early in the lifecycle—while distinguishing robust security controls across OneLake, SQL Analytics Endpoint, and Semantic Models. Introduction Modern data professionals excel at writing efficient PySpark scripts, optimizing SQL queries, and designing multi-layered Medallion Lakehouses. However, technical elegance means little if an analytics platform exposes sensitive operational data to unauthorized risks. With the rise of global data privacy regulations and increasing security threats, data governance can no longer be a secondary phase applied right before dashboard deployment; it must be embedded directly into the ingestion and storage strategy. A fundamental best practice in enterprise data architecture is the "Shift-Left" security pattern: masking, pseudonymizing, or hashing sensitive PII attributes at the earliest possible stage rather than relying solely on downstream presentation layers. Within Microsoft Fabric, data teams can execute this strategy across pipelines, PySpark notebooks, and Semantic Models. This article examines practical techniques for implementing early-stage data protection and dynamic security controls in Fabric, helping data engineers build scalable analytical solutions that support strict regulatory mandates. What is "Shift-Left" Data Protection? In traditional data engineering, security controls are frequently applied at the very end of the pipeline—usually at the presentation layer using Power BI dashboard permissions or SQL database views. This legacy approach creates a critical vulnerability: raw, unmasked Personally Identifiable Information (PII) rests in landing zones or intermediate data lake layers, accessible to anyone with direct storage access. "Shift-Left" Data Protection fundamentally changes this dynamic by pushing security, masking, and pseudonymization controls as far upstream as possible. This approach is highly valuable for organizations navigating cross-border data transfers and cloud migrations. Many international regulatory frameworks strongly recommend or mandate that when transferring sensitive data across borders, the information should be pseudonymized or encrypted to support data sovereignty and localization principles. Failing to comply with privacy frameworks carries severe financial and reputational consequences for enterprises worldwide. By applying cryptographic hashing, masking, or tokenization patterns early in the Medallion Architecture: Exposure is Minimized: Sensitive data (such as national IDs, emails, or account numbers) is restricted or neutralized before persisting in downstream analytical environments. Supports Compliance Initiatives: Organizations are better positioned to align with privacy principles established by global frameworks (like GDPR or PIPEDA) by adopting a Privacy by Design architecture. Governance is Streamlined: Instead of managing complex downstream permission rules across dozens of individual reports, core data security is enforced at the storage and engine layers. To determine whether an enterprise data pipeline truly adheres to a "Shift-Left" architecture, data engineering and compliance teams should verify key architectural control points, such as: [ ] Ingestion Gatekeeping: Are raw PII fields masked in transit before landing in the Bronze layer, or if landing raw in Bronze, is direct storage access strictly limited to service principals and administrators? [ ] Pseudonymization (Bronze to Silver): Are identifying attributes transformed into deterministic hashes (like salted SHA-256) before persisting into Silver Delta tables? [ ] Integration with Token Vaults: Are highly sensitive fields passed to external tokenization services during ingestion, ensuring the decryption vaults are isolated outside the analytics workspace? [ ] Identity and Role Verification: Are workspace roles configured correctly so that downstream consumers only access data via secured endpoints rather than having direct underlying OneLake file access? Demystifying the Security Layers in Microsoft Fabric Implementing a "Shift-Left" framework in Fabric requires understanding that security is not monolithic; it is applied across distinct layers. A robust architecture leverages data-layer controls, transformation techniques, and compute-engine security rules. 1. The Storage & Transformation Layer (Upstream) Source-Side Masking: Using Data Factory pipelines to mask data in transit before it ever writes to OneLake. Hashing as Pseudonymization: Using PySpark notebooks to apply SHA-256 hashing to fields like email addresses. Note: Hashing is pseudonymization, not true anonymization. To prevent dictionary or rainbow table attacks, data teams must implement secure "salting" alongside the hashing algorithm. External Tokenization Integration: Fabric pipelines can call external REST APIs (e.g., third-party tokenization services or Azure Key Vault) to replace sensitive strings with non-mathematical tokens during the Bronze-to-Silver transformation. OneLake Security (Data Access Roles): Fabric increasingly supports native OneLake security controls, allowing administrators to define data access roles directly on the OneLake folders and Delta tables, securing the files regardless of which compute engine accesses them. 2. The Engine & Consumption Layer (Downstream) Once data is curated, Fabric provides specific, engine-level security controls: SQL Analytics Endpoint (T-SQL Controls): Row-Level Security (RLS): Filters specific rows (e.g., restricting a regional manager to only see sales from their territory) using standard T-SQL CREATE SECURITY POLICY statements. Column-Level Security (CLS): Uses T-SQL GRANT SELECT statements to prevent specific roles from querying sensitive columns like Salary or SSN, while allowing them to query the rest of the table. Semantic Models (Direct Lake / Power BI Controls): Row-Level Security (RLS): Configured via DAX to filter data rows natively within the semantic model. Object-Level Security (OLS): Configured via Tabular Editor/XMLA to completely hide entire tables or columns from specific users in the semantic model, preventing report visuals from even discovering the fields. High-Level Architecture in Microsoft Fabric Implementing a "Shift-Left" data protection framework within Microsoft Fabric relies on combining two complementary security layers: In-Flight Data Sanitization (Storage Layer): Using Fabric PySpark Notebooks or Data Factory pipelines to hash, mask, or tokenize PII fields during ingestion or before writing into Silver Delta Tables in OneLake. Dynamic Access Controls (Engine Layer): Enforcing Row-Level Security (RLS) and Column-Level Security (CLS) on top of the SQL Analytics Endpoint or Direct Lake Semantic Models for role-based downstream consumption. To visualize how this defense-in-depth strategy comes together, the following reference architecture outlines how "Shift-Left" sanitization and dynamic query controls are orchestrated within Microsoft Fabric: Understanding the Architectural Diagram The architectural diagram above illustrates a simple example of an end-to-end Microsoft Fabric implementation designed around the "Shift-Left" paradigm. To read and interpret this architecture effectively, consider the flow of data through three core operational layers: 1. Data Sources & Boundary Controls (Left): Data flows from operational systems (On-Premises SQL Databases, REST APIs) toward Microsoft Fabric OneLake. Red Locks (Regulatory Blocking & Ingestion Masking): Represent early security controls applied at the connector level. Sensitive operational fields are masked or blocked before landing in cloud storage to prevent cross-border compliance violations. 2. Medallion Lakehouse & In-Flight Sanitization (Center): Bronze Layer (Raw Ingestion): Stores incoming raw landing data. Shift-Left Sanitization (PySpark Notebook): Before moving data into the Silver Layer, PySpark notebooks perform cryptographic transformations (SHA-256 hashing, masking, tokenization). Silver Layer (Sanitized & Standardized): Contains cleaned, enterprise-ready data where PII has been neutralized at the storage level within OneLake Delta files. 3. Consumption & Dynamic Engine Security (Right): Gold Layer (Aggregated & Enriched): Curated dimensional models built for business analytics. SQL Analytics Endpoint & Direct Lake Semantic Models: As queries hit the serving layer, secondary security rules—such as Column-Level Security (CLS) and Row-Level Security (RLS)—dynamically filter rows or restrict specific columns based on the user's Entra ID (Azure AD) identity, ensuring seamless role-based governance across Power BI reports and T-SQL queries. The Critical Role of Workspace Identity and Direct Storage Access The most common pitfall when designing security in Microsoft Fabric is a misunderstanding of Workspace Roles and Identity Modes. SQL RLS/CLS and Semantic Model RLS/OLS are engine-level controls. If a user is granted the Admin, Member, or Contributor role in a Fabric Workspace, they possess inherent direct storage access to the underlying OneLake files. Because they can access the raw Delta files directly via OneLake Explorer or Notebooks, they will completely bypass SQL RLS/CLS and Semantic Model security. To successfully enforce engine-level security (like SQL CLS or Direct Lake RLS), downstream consumers must be assigned the Viewer role in the workspace. This restricts their access to the defined endpoints (SQL Endpoint or Power BI App) where the security policies are evaluated under their Entra ID credentials, effectively blocking direct access to the unmasked storage files. Key Takeaways for Data Professionals Implementing "Shift-Left" data protection within Microsoft Fabric allows organizations to bridge the gap between high-performance analytics and governance requirements. By moving sanitization controls upstream and combining them with dynamic engine-level security, data teams can build a future-proof architecture that meets strict privacy mandates without sacrificing agility. To successfully apply these principles in your enterprise, keep the following core takeaways in mind: Security is an Architecture Feature, Not a Final Step: Embed data protection requirements into your initial pipeline designs rather than treating them as an afterthought. Protect at the Lowest Layer Possible: Hashing, tokenizing, and masking data early in the Medallion Architecture (Bronze-to-Silver transition) protects sensitive attributes across all downstream environments, including Notebooks, SQL queries, and Direct Lake Power BI reports. Align Controls to the Right Layer: Use OneLake security for raw storage, T-SQL CLS/RLS for the SQL Endpoint, and DAX OLS/RLS for Semantic Models. Audit Workspace Roles: Never rely on SQL or Semantic Model security for users who have Admin, Member, or Contributor access to the workspace. Join the Conversation! Designing enterprise-grade security in modern data platforms is an evolving practice, and different organizations adopt unique strategies depending on their regulatory environment. What additional techniques or governance frameworks do you implement in your organization to protect sensitive PII in Microsoft Fabric? Are you primarily leveraging SQL-based CLS, or shifting toward OneLake data access roles? Share your thoughts, experiences, or questions in the comments below!
ADLS Gen2 and OneLake - Choosing the right storage approach
As Microsoft Fabric adoption grows, a practical architecture question follows: how does OneLake relate to Azure Data Lake Storage Gen2 (ADLS Gen2), and do we need to migrate? Both store analytics data in open formats, but they offer different ways to manage and consume it. I would start with the workload and the investments already in place, rather than with a decision to move storage. For an established lake, keeping ADLS Gen2 and exposing selected data to Fabric may be the simplest path. For a new Fabric-centric workload, storing data directly in OneLake may reduce operational work. The useful unit of decision is a layer or data product, not the entire estate. Start with the operating model What ADLS Gen2 is designed for ADLS Gen2 brings analytics capabilities to Azure Blob Storage through a hierarchical namespace. In this PaaS model, Azure runs the storage service while your team manages accounts, containers, networking, lifecycle policies and access through Azure role-based access control and POSIX-style ACLs. It can hold any file format, giving teams flexibility to choose how different engines process the data. That flexibility is valuable when ingestion pipelines and multiple analytics engines already share a lake. Azure Databricks, Azure Machine Learning and other compatible tools can work with the data where it is. The architectural responsibility sits with the team: bringing storage, processing, discovery and governance together into an operating platform. OneLake: Fabric-managed data lake Built on ADLS Gen2, OneLake is created automatically for a Fabric tenant. Microsoft manages the underlying storage infrastructure, while teams organize and secure data through workspaces and items such as Lakehouse’s. This SaaS model shifts the day-to-day focus from storage accounts to analytics data products. OneLake stores physical data and can also reference data elsewhere through shortcuts. The benefit becomes tangible in the way Fabric workloads work together. Spark notebooks prepare Lakehouse data, SQL analytics experiences expose supported tables, and Power BI can consume suitable tables through Direct Lake. A Lakehouse’s Files area can hold any file format, while its Tables area serves supported table formats and engines, including Delta and supported Iceberg tables through metadata virtualization. Shared storage simplifies integration, while each engine still has its own supported capabilities. The architectural relationship The storage hierarchy is straightforward: tenant, workspace, then data item. Within a Lakehouse, Tables and Files support different consumption needs. Domains group workspaces for organizational ownership and governance, rather than adding a folder level to the storage path. A simple example is: Scope Illustrative organization Fabric tenant OneLake namespace Workspace Reporting workspace containing a Sales Lakehouse Lakehouse Tables Local Delta tables, or a shortcut to supported Gold tables in ADLS Gen2 Lakehouse Files Files and folders, including supported external shortcuts What changes for analytics consumers Shortcuts: Bring existing data into reach A plain shortcut references an internal or external storage location without requiring an ingestion copy. If curated Delta tables already live in ADLS Gen2, a supported Lakehouse Tables shortcut can make them available to Fabric consumers. Files shortcuts expose files instead; making those files into queryable tables is a separate step. This provides an incremental adoption path, although downstream workloads may still cache or materialize data. The access design deserves as much attention as the data path. An ADLS shortcut uses the source connection’s configured credentials alongside Fabric permission checks. It does not automatically carry each reader’s source ACLs into Fabric. Before sharing it, I would check least privilege, network reachability and table compatibility with the identities and workloads that will use it. Shortcut transformations address a different need: preparing data for consumption. Supported CSV, Parquet and JSON transformations use Fabric Spark to copy and convert source content into managed Delta output and keep it synchronized. That can simplify preparation, but it introduces compute, output storage and synchronization to operate. Choose a plain shortcut when referencing the existing data is enough; choose a transformation when the output itself adds value. Direct Lake: Simplify the BI data path Direct Lake loads requested Delta-table columns into VertiPaq memory without requiring a full Import-mode refresh. Metadata refresh, also called framing, still determines the data version available to the model. The practical benefit is a simpler BI data path for suitable workloads. Capacity, table optimization, security and model design still determine performance and freshness. The underlying Delta data can remain in ADLS Gen2 when exposed through a supported OneLake Tables shortcut. That is the bridge to Direct Lake, rather than a direct connection from the semantic model to an ADLS path. I would assess the intended Direct Lake mode and security design, then test representative queries and freshness requirements before making performance commitments. Governance: Discovery and enforcement both matter Governance needs both business context and effective access control. ADLS Gen2 can use Microsoft Purview for discovery and governance alongside its storage permissions. In Fabric, the OneLake catalog brings discovery, metadata, lineage and endorsement into the analytics experience, while workspaces and items provide ownership context. Those capabilities help people find and understand data; the permissions on the actual access path determine who can use it. I would compare security and lifecycle requirements against the specific design, rather than assume one platform always offers more control. Fabric supports tenant- and workspace-level private links and customer-managed keys with supported-item constraints. OneLake also supports lifecycle policies and hot, cool and cold tiers. The decision turns on whether the required workload, connector, encryption and network configuration work together, not simply whether a feature appears on a checklist. AI readiness starts with usable data Both approaches can support AI. ADLS Gen2 is a supported Azure Machine Learning datastore, so an established lake may already be a sound foundation. Fabric adds integrated preparation and consumption options that can be useful without relocating that lake. My starting question would be which capability the team needs and where the data is already well governed. For example, AI-powered shortcut transformations can turn supported .txt files into Delta output containing summaries, translations, sentiment, redacted PII or extracted entities. A team could summarize eligible text sourced from ADLS and analyze the results in Fabric. This capability is in public preview, with regional and capacity requirements. Output quality and sensitive-data handling still need evaluation; automated redaction alone cannot establish compliance. Interoperability depends on the integration path Snowflake illustrates why the integration path matters. Fabric Mirroring can replicate Snowflake-managed table data into OneLake. For Iceberg tables in supported object storage, shortcuts and metadata virtualization provide another route; the Snowflake Iceberg mirroring path uses metadata mirroring and storage shortcuts. These are different architectural choices, with different implications for copies, freshness and ownership. Snowflake on Azure can also write Iceberg tables to OneLake in supported configurations. That integration requires public connectivity, does not support private-link workspaces, and has same-region prerequisites for writes. Azure Databricks can read and write OneLake through authenticated ABFS paths, with compute and authentication requirements. Open formats help, but the chosen engine and network design still need to fit the supported integration. Make the choice layer by layer An existing Databricks lake: should Bronze and Silver move? Consider a team with reliable Azure Databricks pipelines and Bronze, Silver and Gold layers in ADLS Gen2 that now wants Fabric reporting. My recommendation would be to keep the working Bronze and Silver layers unless there is a separate business or technical reason to move them. For Gold, there are two useful starting points. Keep physical Gold in ADLS Gen2 and expose supported Delta tables through Lakehouse Tables shortcuts. Gold becomes available in Fabric without physical move. I would start here when existing storage contracts, authoritative writers and non-Fabric consumers need to remain intact. Alternatively, write or materialize selected Gold data directly in OneLake when Fabric ownership or service-level requirements justify changing the pipeline. Databricks can support this pattern through authenticated ABFS access with the appropriate compute configuration. Moving selected Gold products can be a focused decision, independent of any future Bronze or Silver migration. For either pattern, agree on the authoritative writer, table compatibility, effective permissions, freshness targets, monitoring and total cost. A storage shortcut does not transfer Unity Catalog authorization, so test the security model with representative readers. These responsibilities matter more than whether the diagram shows one lake or two. Count the governance and semantic-model work I would also count the work above storage. A team may maintain security rules and semantic logic in Databricks, then recreate them for Fabric reporting. Repeating policies, metrics, relationships and business definitions creates maintenance and testing work, with a risk of drift whenever requirements change. Reducing that duplication can be a stronger reason to adopt a shared consumption layer than saving a data copy. A useful pattern is to use Databricks for data preparation, make governed Gold data available through OneLake, and use Fabric as the shared consumption layer. OneLake security can centralize data-access policies across supported Fabric engines and access paths. Separately, a deliberately shared Power BI semantic model lets reports and compatible clients reuse measures, relationships and business definitions instead of rebuilding them. These are two distinct opportunities to reduce downstream work: common access control and an explicitly reusable semantic layer, not benefits that storage alone creates. That semantic model reuse does not require moving your Gold layer: supported shortcuts can expose ADLS data, and shared Power BI models can also use other supported sources. OneLake does not automatically unify Unity Catalog policies nor give every Databricks or Spark workload the same semantic layer. Where Unity Catalog remains authoritative, I would evaluate supported integrations and ask: where are policies and metrics defined, which engine enforces them under which identity, which consumers reuse the model, and who maintains and tests necessary differences? A practical guide Your situation Starting point What to validate Successful multi-engine lake; established account/API dependencies Keep ADLS Gen2 as an authoritative store Existing contracts, source security and the specific Fabric access path New Fabric-centric analytics and shared Fabric items Use native OneLake storage Ownership, supported workloads, capacity, security and lifecycle requirements Mixed estate; selective Fabric adoption or duplicated governance and model logic Combine ADLS Gen2 and OneLake; assess a shared consumption layer See the mixed-estate checklist below. A required security or storage feature determines the design Compare the exact supported configurations Connector and workload limits, private networking, CMK item coverage, retention and total cost For a mixed estate, validate: Data access: shortcut compatibility versus materialization. Write ownership: the authoritative writer. Operations: freshness targets and monitoring. Security: effective identities and policy enforcement. Semantics: reuse of a shared semantic model. Governance ownership: who maintains and tests necessary cross-platform differences. Coexistence as a practical starting point Use ADLS Gen2 where its customer-managed storage model and existing integrations serve the workload well. Use native OneLake where Fabric’s managed model and shared analytics experiences provide a clear benefit. For an existing estate adopting Fabric, I would usually start by connecting the two and move data only where the benefit is clear. Coexistence can be a transition or a lasting design; consolidation should earn its place through workload requirements and measurable value.Row-Level Security in Direct Lake Models: The Undocumented Gotchas
When we moved our sales reporting from Import mode to Direct Lake, RLS looked like the easy part. We already had dynamic RLS working in the old Import model: a security table, a USERPRINCIPALNAME() filter, and a couple of relationships. We expected to copy it over and be done in an afternoon. It took two weeks. The RLS logic itself was fine. What caused the trouble was how Direct Lake interacts with workspace permissions, the SQL analytics endpoint, framing, and fallback. Most of it is technically documented, but spread across a dozen pages, and a few behaviors we only found by testing. This post covers what we hit, how we diagnosed it, and what we'd do differently next time. The setup Here's a simplified version of what we built: Lakehouse: LH_Sales with fact_sales, dim_region, dim_product, dim_date Security table: sec_user_region (UserEmail, RegionKey), one row per user per region Semantic model: Direct Lake on the SQL analytics endpoint Users: about 400 sales reps and managers, each seeing only their regions The RLS role was the standard dynamic pattern: // Role: RegionSecurity // Table: sec_user_region [UserEmail] = USERPRINCIPALNAME() The relationship sec_user_region[RegionKey] → dim_region[RegionKey] had "Apply security filter in both directions" enabled, and dim_region filtered fact_sales. It worked when I tested it as myself, and it failed in several different ways once real users got access. Gotcha #1: Workspace roles silently bypass your RLS This is the most common one, and it matters more in Direct Lake. Semantic model RLS only applies to users who have Read permission on the model. Anyone with Admin, Member, or Contributor in the workspace has write permission, so RLS does not apply to them. They see everything. That's the same as Import mode. The Direct Lake-specific problem is the next part. The Viewer role has a hole too. A workspace Viewer is subject to your semantic model RLS, but Viewers can also connect to the lakehouse's SQL analytics endpoint and query fact_sales directly from SSMS, Excel, or a notebook. Your DAX roles don't exist there, so they can read every row. We found this when a regional manager emailed us a pivot table with national numbers. He had connected Excel to the SQL endpoint because he found it faster. The relationship sec_user_region[RegionKey] → dim_region[RegionKey] had "Apply security filter in both directions" enabled, and dim_region filtered fact_sales. It worked when I tested it as myself, and it failed in several different ways once real users got access. What we changed: Removed every business user from workspace roles. Shared the report and semantic model through an App (or item-level sharing) with Read permission only, without "Build" unless it was genuinely needed. Did not grant lakehouse access to end users at all (Gotcha #2 explains how that still works). Rule of thumb: If a user can see the lakehouse, assume they can see all of it unless you've also secured it at the SQL/OneLake layer. Gotcha #2: SSO vs. fixed identity changes who needs lakehouse access By default, a Direct Lake model on the SQL endpoint uses single sign-on (SSO): the viewer's own identity is used to access the underlying data. That means every report viewer needs permission to read the lakehouse, which brings back the problem from Gotcha #1. The fix is to configure the model's data source connection to use a fixed identity through a shareable cloud connection (a service principal or a dedicated account). Then: The fixed identity reads the Delta tables. End users only need Read on the semantic model. Your DAX RLS still applies per user, because USERPRINCIPALNAME() still returns the viewer, not the fixed identity. The catch: once you switch to fixed identity, any RLS you defined on the SQL analytics endpoint (T-SQL security policies) is evaluated against the fixed identity, not the end user. Warehouse-level RLS effectively stops being per-user for this model. So pick one layer as the source of truth: Approach Security lives in Connection Users need lakehouse access? A Semantic model (DAX roles) Fixed identity No B SQL endpoint (T-SQL RLS) SSO Yes We chose A. Mixing both led to confusing results where the same user saw different numbers depending on the path the query took. Gotcha #3: SQL endpoint RLS quietly forces DirectQuery fallback We initially tried approach B, because the data engineering team liked having security defined once in T-SQL: CREATE FUNCTION sec.fn_region_filter(@RegionKey INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS allowed FROM dbo.sec_user_region s WHERE s.RegionKey = @RegionKey AND s.UserEmail = USER_NAME(); CREATE SECURITY POLICY sec.RegionPolicy ADD FILTER PREDICATE sec.fn_region_filter(RegionKey) ON dbo.fact_sales WITH (STATE = ON); Security worked. Performance got much worse. With Direct Lake on the SQL endpoint, any table with RLS (or OLS) defined at the SQL endpoint can't be read directly from Delta, because VertiPaq can't enforce the T-SQL policy. Queries against it fall back to DirectQuery. Our main visuals went from about 300 ms to 4–8 seconds. It fails quietly. Nothing errors. Reports just get slow. How to detect it: Run a trace in DAX Studio or SQL Profiler against the XMLA endpoint and look for DirectQuery Begin/End events. In pure Direct Lake you should see only VertiPaq scan events. In Performance Analyzer in Desktop, a "Direct query" duration on a visual is a clear sign of fallback. How to make it fail loudly instead: set the model's DirectLakeBehavior property (via Tabular Editor or TMDL) to DirectLakeOnly: model Model directLakeBehavior: directLakeOnly Now fallback-triggering queries error instead of silently degrading. We set this in dev and test so we find these issues before users do. In production you may prefer Automatic so reports keep working, but you should make that choice deliberately. Note: Direct Lake on OneLake (the newer flavour) reads Delta directly and doesn't use the SQL endpoint for queries, so SQL endpoint RLS isn't applied there at all. That's another reason not to rely on T-SQL RLS for Direct Lake models. Check the current docs on how OneLake security interacts with it, because that area is still changing. Gotcha #4: Your security table can't be a view or a calculated table In Import mode, our security table was a calculated table that unpivoted a hierarchy of managers and regions using DAX. In Direct Lake (on SQL endpoint), calculated tables and calculated columns over Direct Lake tables aren't supported. The obvious next step is a SQL view. But SQL views in a Direct Lake on SQL endpoint model always fall back to DirectQuery, because they aren't Delta tables. Your security table sits in the filter path of every query, so it pulls every query into DirectQuery with it. What worked: materialise the security table as a real Delta table in the lakehouse, rebuilt by a notebook in the same pipeline that loads the facts: from pyspark.sql import functions as F sec = ( spark.table("stg_user_access") .withColumn("UserEmail", F.lower(F.trim("UserEmail"))) .select("UserEmail", "RegionKey") .dropDuplicates() ) sec.write.mode("overwrite").format("delta").saveAsTable("sec_user_region") Keep it narrow (two columns) and deduplicated. The next gotcha explains why the lower() matters. Gotcha #5: Case sensitivity changes between Direct Lake and fallback This one took us a day to track down. A user could see data in one report page but got blanks on another. Same model, same role. The cause: USERPRINCIPALNAME() returned [email protected]. The security table had [email protected]. In Direct Lake (VertiPaq), string comparison is case-insensitive, so it matched. One page had a visual that fell back to DirectQuery (it hit a guardrail). In DirectQuery, the RLS filter is pushed down as SQL, and the Fabric SQL endpoint's default collation is case-sensitive, so it didn't match. Same user, same role, different result depending on the query path. Fix: normalise on both sides. // Role filter on sec_user_region [UserEmail] = LOWER ( USERPRINCIPALNAME () ) Also lowercase the data during ingestion (see the notebook above). Don't rely on the engine's collation. Gotcha #6: RLS changes don't take effect until the model reframes We removed a departing manager from sec_user_region, the notebook ran, and the Delta table was updated. He could still see his old regions two hours later. Direct Lake doesn't read "live" Delta. It reads the version of the table captured at the last framing operation (a refresh). If you've turned off "Keep your Direct Lake data up to date" (common when you want facts and dimensions to update together), the model keeps using the old security table until the next refresh. Security changes follow your refresh schedule, not your data load. What we do now: The pipeline that rebuilds sec_user_region ends with a semantic model refresh activity, so the model reframes immediately. For urgent revocations such as terminations, we trigger an on-demand refresh and don't wait for the schedule. We added a small "Security last refreshed" card to the report, driven by a LastUpdated column in the security table, so support can check it quickly. Gotcha #7: "Test as role" isn't the same as a real user Testing with Security → Test as role in the service, using "Other user" and typing an email, is useful but has limits: Under SSO, data access still happens with your identity. You may see rows the real user can't access at the lakehouse level, or the reverse. It doesn't show you workspace-role bypass (Gotcha #1), because you're testing the role, not the user's actual permissions. We now add a hidden debug page to every RLS model: Debug_UPN = USERPRINCIPALNAME () Debug_RegionCount = COUNTROWS ( VALUES ( dim_region[RegionKey] ) ) Debug_SecRows = COUNTROWS ( sec_user_region ) For UAT, real test accounts (not admins) open the app and screenshot this page. It shows exactly what UPN the engine sees, which also helped with a B2B guest user whose UPN didn't match the email in our HR feed. Always compare against what USERPRINCIPALNAME() actually returns, not what you expect it to return. Gotcha #8: Large security tables affect memory and cold-cache performance Direct Lake loads columns into memory on demand. The security table and its relationship columns are touched by every query for every user under that role. Our first version of sec_user_region expanded the org hierarchy down to store level: about 2.1 million rows. After a reframe, the first visual for each user was noticeably slow because the security columns had to be transcoded, and memory pressure caused more column evictions under load. Collapsing it to region level (about 3,800 rows) and moving store-level granularity into the dimension fixed the problem. Tips: Filter at the highest grain that meets the business requirement. Avoid high-cardinality string keys in the security relationship; use integer keys. Keep bidirectional filtering limited to the one security relationship, not across the whole model. Consider a warm-up query (a scheduled DAX query after refresh) for large models so the first user of the morning doesn't pay the cold-cache cost. Our checklist now Before any Direct Lake model with RLS goes to production, we check: End users have no workspace role and access through an App or item sharing only End users have no direct lakehouse / SQL endpoint access Connection uses a fixed identity (if security lives in the model) No T-SQL RLS/OLS on tables used by the model (or fallback is intentional) Security table is a physical Delta table, not a view or calculated table Emails are lowercased on both sides of the comparison DirectLakeBehavior = DirectLakeOnly in dev/test to surface fallback Security table rebuild triggers a model refresh Debug page verified by real non-admin test accounts, including guests Security table kept small, integer-keyed, and at the coarsest grain possible Final thoughts The RLS logic in Direct Lake is the same as in Import mode, so it's easy to assume nothing else changes. What does change is the environment around it: permissions are shared with the lakehouse, security can exist in two layers, data only updates when the model reframes, and queries can fall back to DirectQuery without warning. Most of our problems came from those areas, not from DAX. If you're planning a migration, test RLS with real user accounts early, run a trace to check for fallback, and decide at the start which layer owns security. Have you run into other RLS issues with Direct Lake? Please share them in the comments. I'm especially interested in how people are handling OneLake security alongside model RLS as that feature matures.Direct Lake on SQL Endpoint + OneLake Security: Delegated vs. User Identity Mode (A Field Guide)
Direct Lake on SQL Endpoint + OneLake Security: the field guide. Model owned by an SPN, report published, refresh green — but every visual fails with QueryUserError? The reason is which identity actually reads the data, and it's not the one you think. Delegated vs. User identity mode, shortcut + native tables, SPN refresh traps, and the new Fabric default — decoded with diagrams and permission matrices. Save yourself the debugging session.Direct Lake on SQL Endpoint + OneLake Security: Delegated vs. User Identity Mode (A Field Guide)
Direct Lake on SQL Endpoint + OneLake Security: the field guide. Model owned by an SPN, report published, refresh green — but every visual fails with QueryUserError? The reason is which identity actually reads the data, and it's not the one you think. Delegated vs. User identity mode, shortcut + native tables, SPN refresh traps, and the new Fabric default — decoded with diagrams and permission matrices. Save yourself the debugging session.The Fabric Blueprint: Architecting Workspaces for Enterprise Success
This quick guide establishes the organizational standard for managing Microsoft Fabric within our enterprise environment. By adopting these patterns, we ensure security, maintainability and streamlined CI/CD deployments across all data projects.Beyond the Basics: Deep Dive into Microsoft Fabric Row-Level Security (RLS) & Purview Labelling
“Securing your data isn’t about locking it away – it’s about ensuring the right people see the right insights without compromising the rest.” In our previous beginner’s guide, we explored how to establish baseline governance, structure your workspaces and set up your core Admin Portal guardrails. If you fancy reading the previous beginner blog, you can refer it here. But once your data estate is up and running, you inevitably run into a critical next challenge: How do you handle data when different people are allowed to see different subsets of information within the very same table? Moving from basic visibility to active, granular protection is the hallmark of Stage 2 on the Governance Maturity Curve. In this deep dive, we’ll explore how to implement Row-Level Security (RLS) and enforce Microsoft Purview sensitivity labels through a visual journey.Governing the Flow: A Beginner’s Guide to Microsoft Fabric
“Governance is not about restricting access; it’s about providing the right access to the right people at the right time, while ensuring the data remains accurate and secure.” In the world of data, speed is often the enemy of stability. Microsoft Fabric is a powerhouse that allows data to move seamlessly from ingestion to AI, but without structure, that speed can quickly turn into “data sprawl.” Governance is the safety net that allows your team to innovate faster without fearing a security breach or a broken pipeline.
OneLake Security in Microsoft Fabric
Tired of managing security three different ways for SQL, Spark, and Power BI? Microsoft Fabric's OneLake Security changes the game with a single unified layer that enforces table, row, column, and folder-level access control across every engine. Define your security policies once in the portal, and they're automatically enforced whether users query through notebooks, SQL endpoints, or DirectLake reports. This blog walks you through setting up real-world demos with sample data, creating granular roles via UI clicks, and validating enforcement across engines. Ready to simplify your data security? Let's build it step-by-step.Fabric SDD+AI Series (3/3) | Mastery in Production: Governance, CI/CD and Final Checklist [PT/EN/ES]
🇧🇷 PT: Chegamos à reta final! Descubra como garantir um "Go-Live" sem estresse no Microsoft Fabric usando automação CI/CD, governança de dados e o checklist definitivo de prontidão para produção. Aprenda a usar a IA para revisar a segurança do seu projeto antes do lançamento. 🇺🇸 EN: We've reached the final stretch! Discover how to ensure a stress-free "Go-Live" in Microsoft Fabric using CI/CD automation, data governance, and the ultimate production readiness checklist. Learn how to use AI to review your project's security before launch. 🇪🇸 ES: ¡Llegamos a la recta final! Descubra cómo garantizar un "Go-Live" sin estrés en Microsoft Fabric utilizando automatización CI/CD, gobernanza de datos y el checklist definitivo de preparación para producción. Aprenda a usar la IA para revisar la seguridad de su proyecto antes del lanzamiento.
