skip to content

Should row filtering live in Power BI RLS or in the warehouse when several tools read the same data?

level: principalimportance: nice to knowfreq 30%

answer

  1. ask who else queries the same tables
  2. authoring the rule is not enforcing it
  3. an import model is a full copy
  4. live queries move the cost to the source
  5. one entitlement table, many enforcement points

basics

~20 s

Enforce once, as close to the data as the access pattern allows. Warehouse row policies with DirectQuery and single sign-on cover every tool but cost per query; Power BI RLS is faster and self-contained but re-implements the rule for one tool over a cached copy.

solid answer

~50 s

The question is where the entitlement rule is *enforced* versus merely *expressed*. If several tools — Power BI, a notebook, an ad-hoc SQL client — read the same tables, warehouse-native row policies enforced under the querying user's identity are the only mechanism that covers all of them, and Power BI reaches it through DirectQuery with single sign-on so the source sees the real user. The cost is real: no cached import model, per-query latency and warehouse spend, and a hard dependency on the gateway or SSO path. Power BI RLS is the pragmatic default for an import model, because the data is already copied into the model and the source cannot see who is asking. The compromise most teams land on is a **single entitlement dataset owned upstream**, published from the warehouse, consumed by Power BI's dynamic RLS and by anything else that needs it — one definition of who may see what, enforced in more than one place but authored once.

code

text · 13 lines
text
authored once, enforced where needed

  system of record (HR / access requests)
            |
            v
  entitlement table  (principal, permitted_key)   <- versioned, audited
         /                        \
        v                          v
  warehouse row policy      Power BI dynamic RLS
  (notebooks, SQL, DQ)      [UserEmail] = USERPRINCIPALNAME()

import model  -> fast, but the file holds every row
DirectQuery+SSO -> source enforces, but every visual is a live query

go deeper

for a junior

Know the basic distinction: an import model holds a copy of the data and filters it with roles, while DirectQuery sends each query to the source, which can apply its own security.

for a middle

Explain the trade-off concretely — in-memory speed and a full local copy versus live queries, source-enforced policies and a dependency on passing the user's identity through.

for a senior

Argue from the access pattern: who else queries the tables, how sensitive the copy is, what the query load costs, and how a single shared entitlement table keeps definitions from drifting.

for a principal

Own the enforcement architecture and its audit story — where the rule is authored, where it is enforced, what revocation latency you accept, and who may hold edit rights on models that contain full copies.

## Framing the decision This is not "which feature is better" but two separate questions: **where is the entitlement rule authored**, and **where is it enforced**. Confusing them produces both classic failures — a warehouse policy that Power BI quietly bypasses because the model imported the rows first, and five Power BI models that each encode a slightly different definition of "my region". ## Option 1 — enforce in Power BI (import + RLS) The model imports the data and roles filter it at query time. *Strengths.* Fast: the engine is columnar, in memory, and the security filter is cheap. Self-contained: no gateway identity plumbing, no source-side configuration, works with any source including files. Testable inside the tool. *Weaknesses.* The model **is** a copy of the whole dataset — every row the model imported exists in the file, so anyone with edit rights, download rights or workspace Contributor sees everything, and RLS is a query filter, not encryption. The rule is also re-implemented per model: three models over the same warehouse tables mean three role definitions to keep in step, and none of them protect the notebook querying the warehouse directly. ## Option 2 — enforce in the warehouse (DirectQuery + SSO) Row-access policies live in the database; Power BI connects in DirectQuery with single sign-on so the source receives the end user's identity and applies its own policy. *Strengths.* One enforcement point for every consumer — BI tools, notebooks, ad-hoc SQL. The sensitive rows never leave the source, so a downloaded report file carries nothing. Audit and compliance have a single place to review, which is usually what a security team actually wants. *Weaknesses.* Every visual becomes a live source query: latency, concurrency and warehouse cost scale with dashboard usage, and the fast in-memory model is gone. SSO must be configured and kept working through gateways or cloud connections; when it fails, the fallback is usually a shared service account, which silently destroys the whole design because every user then looks like one identity. Not every source supports the policy or the identity pass-through you need. ## Option 3 — author once, enforce in both In practice most organisations land here. A single **entitlement table** — one row per (principal, permitted key) — is owned upstream: produced from HR or an access-request system, versioned, tested, auditable. The warehouse's own policies read it. Power BI's dynamic RLS imports it and filters with `[UserEmail] = USERPRINCIPALNAME()`. The rule is authored once and enforced wherever it needs to be, and when someone changes teams the change flows everywhere from one edit. The honest cost is refresh latency: an imported entitlement table is as of the last refresh, so a revocation is not instant. If instant revocation is a requirement, put the entitlement table itself in DirectQuery even when the facts are imported, or accept a frequent refresh schedule on that table. ## The questions that decide it - **Who else queries this data?** If the answer is only Power BI, model RLS is proportionate. If notebooks and SQL clients read the same tables, an enforcement point they all pass through is the only complete answer. - **How sensitive is the copy?** If possessing the full dataset is itself the breach — salaries, patient data, per-customer contract terms — do not import it. That decision usually settles the mode before performance is considered. - **What is the query profile?** A dashboard refreshed by hundreds of users every morning is a bad fit for DirectQuery against a warehouse billed by compute; a small, sensitive, occasionally-read dataset is a fine one. - **Who owns the entitlements?** If a BI developer maintains role membership by hand, entitlements will drift from reality. If they come from a system of record, they will not. - **What must the audit show?** "Prove who could see this row last quarter" is answerable from a versioned entitlement table and much harder from role membership edited in a UI. ## Hybrid patterns worth naming Split by sensitivity: import the aggregate that everyone may see, and expose the row-level detail through a DirectQuery table with source enforcement, in a composite model. Or split by audience: an unrestricted departmental model in a locked workspace, and a broadly-shared restricted model, rather than one model doing both jobs badly. ## The failure to warn against The worst outcome is *implicit* trust: a warehouse policy exists, everyone believes the data is protected, and a Power BI import model refreshed under a service account has already copied every row into a workspace where a dozen people hold Contributor. Any answer to this question should end with the operational check — who can edit or download the model, and under whose identity does it refresh.

  • What breaks if DirectQuery single sign-on falls back to a shared service account?
    Every user reaches the source as the same identity, so the source's row policy returns that account's rows to everyone — usually all of them. The dashboard keeps working, which is why it goes unnoticed. Treat SSO failure as a security incident with a hard failure mode, and test it explicitly rather than assuming the connection would break visibly.
  • When is duplicating the rule in both places acceptable rather than sloppy?
    When the rule is authored once and only its enforcement is duplicated. A shared entitlement table read by both the warehouse policy and the model's dynamic RLS gives one source of truth with two enforcement points. What is sloppy is two independently written definitions of the same entitlement, which drift within a quarter and disagree without anyone noticing.
  • How does the choice change if possessing the full dataset is itself the breach?
    It settles the mode before performance is discussed. An import model physically contains every row, so no role protects it from anyone who can download the file or edit the model. Sensitive-by-possession data belongs behind DirectQuery with source enforcement, or in a separate, tightly permissioned model that never leaves a locked workspace.
  • What operational check should close any RLS design review?
    Who holds edit or download rights on the model, and under which identity does it refresh. Those two answers decide whether the roles mean anything: editors bypass RLS entirely, a downloadable import file carries all rows, and a refresh running as a privileged service account is how unrestricted data reaches the model in the first place.

saying these in an interview costs you the question

  • Assuming a warehouse policy protects an imported Power BI model
  • Treating RLS as encryption rather than a query filter
  • Maintaining entitlements by hand in role membership
  • Choosing DirectQuery for security without costing the query load
  • Writing the same entitlement rule twice in two tools

context

resolving links…