The Modern Data Engineer Part 14: One Policy, Every Grant

The grant nobody could explain

A previous client had one person who handled access for the whole BI estate: reports scattered across several generations of legacy BI tooling, plus everything that had moved to Power BI. Requests reached him by email, by phone, and by being caught in a corridor, and nothing was logged. He was the only person who knew who could see what and why, and he never asked whether a requester should have the access, only which report they meant. He had years of stored leave and was away half the year, and the rest of us could execute a grant without knowing whether it made sense.

The waiting was the visible part. The access state underneath it could not be reconstructed from anything except him, so the audit question of why a person could read a table had one place to go.

A different client showed me the other half of the bill. I sat on a central data team serving a company whose sites each ran their own deeply specialized analysts, and much of every week went to supplying them data. When we started asking what for, the answer kept coming back the same: the analyst was rebuilding something that already existed company-wide, with no way to have found that out, because the only route to data was asking us. Their numbers then drifted from the company-wide ones, which is the fork Part 13 traced to competing definitions, reached here through a failure to discover.

Both places had a person doing a platform’s job: at the first client the access-control system was a man with a phone and a memory, and at the second the search engine was a data team asking every requester what they were really building.

Why the chokepoint has to be the warehouse

Part 13’s headless resolver works because of what happens when a consumer ignores it. Ignoring the resolver produces a wrong number that somebody eventually catches and reconciles, so a resolver survives being routed around. An access rule ignored produces a breach nobody takes back, so its enforcement point has to sit where every consumer physically passes through, and a warehouse-centric platform has exactly one such place.

Part 4A warned that centralizing decision rights slows change and pushes shadow logic back into BI, and that warning still holds, aimed at authorship. Who may see the workforce domain is the workforce domain’s decision, written into a policy file its own people review in Git, and only the evaluation is centralized.

Dehghani puts the principle plainly, that access control policies can be defined centrally but enforced at runtime by each individual data product, as versioned, tested, computationally evaluated code [1]. A medallion warehouse borrows that treatment of policy without taking on the per-product execution the rest of data mesh argues for, because a mesh enforces inside every data product where a warehouse enforces in one place.

Access as a function of identity and classification

Two inputs decide every grant, and an organization already maintains both. The first is identity attributes: role, department, region, clearance, synced from the IdP as Okta or Entra groups over SCIM. That works until the attribute you need is missing. At the first client we shipped a leave-balances data product to roughly 7,000 managers, and Active Directory had no manager group, so the first batch meant granting 200 of them access one at a time. The attribute was derivable all along: a manager is someone with more than zero direct reports, which the warehouse could compute in one query.

The second input is data classification: domain, sensitivity, PII level, region, declared as tags on the model at build time through dbt’s meta and tags, so it ships with the model.

A change in either input closes a loop that would otherwise have been an access request. HR moves someone into a new department, SCIM syncs the new attribute, and their entitlements change on the next compile. A new Gold mart ships carrying its tags and is born governed. Grants appear that way and, more usefully, disappear that way, with nobody waiting on an approval.

The whole scheme fits in one line: access = policy(identity attributes, data classification). Humans do two things here, both reviewed in Git: author the policy mapping attributes to entitlements, and classify the data. Part 9 put classification into a declarative artifact for this reason, a file a privacy officer can audit without reading pipeline code.

# declared with the model, at build time
model: gold.employee_leave_balance

classification:
  domain: workforce
  sensitivity: confidential
  region: eu

# authored once, reviewed in git
policy: workforce_confidential

rules:
  - match:
      domain: workforce
      sensitivity: confidential
    grants:
      # managers see only their own reports, direct and indirect
      - identity:
          role: manager
        rows: employee_id in reports_of(identity.employee_id)

      # cleared hr staff see every row
      - identity:
          department: hr
          clearance: elevated
          region: eu
        rows: all

The same client built an embryo of that for the datasets that followed, a dbt macro reading a YAML file that declared which row-level filter applied to which group, under source control and code review. The step up is deriving the membership from attributes.

The primitives have been there for years. Row-level security filtered each manager at the first client down to their own reports, and the same control ships as a row access policy in Snowflake and BigQuery, and a row filter in Unity Catalog.

What the derivation has to get right

Data carrying no classification is visible to nobody until someone tags it. That is Part 11’s asymmetry of error moved to the far end of the platform: wrong permissively and you expose something you cannot un-expose, wrong strictly and a dataset stays invisible for a week, which is irritating and recoverable. The rows still land in Bronze and stay there.

The policy compiles to a desired state and the warehouse gets reconciled to it, the way Terraform reconciles infrastructure or Permifrost reconciles Snowflake grants, so deprovisioning runs on the same schedule as provisioning. At that first client the desired state was a BigQuery query returning everyone with direct reports, and the reconciliation was pasting the result into the Active Directory GUI, 100 lines at a time. Around 5,000 managers have been granted access that way. Nothing has ever been deprovisioned. The row-level filter softened the rot, because it computed each viewer’s rows from their current reports, so a manager whose grant had outlived its validity opened a dashboard with nothing in it. The part of the control that was derived kept itself correct, and the part that was issued by hand is what decayed. Part 9 admitted a hole of the same shape, that any data engineer can always restore from the archives.

Region, department and clearance belong in the policy as attributes the function reads at evaluation time. Enumerate them as roles and every combination needs one, so four regions, six departments and three clearance levels leave seventy-two roles nobody can audit. ABAC is the term of art.

Break glass is load bearing. Without an audited, time-boxed exception path, the first real emergency gets solved by somebody with admin rights issuing a grant by hand, and the scheme carries an undocumented bypass from then on.

The chokepoint also has one leak: tools that cache. An import-mode Power BI model reads the warehouse once under a service identity and serves every viewer from its own copy, so the warehouse policy never sees who is looking. Some lake-native platforms now resolve the querying user’s identity at the storage layer, so one set of row and column rules covers every engine reading the table, though the per-viewer case still lands in the BI tool. The answer is to treat the importing identity as a consumer in its own right. At that client, a team importing a dataset got its service principal added to the policy, the row filter scoped the extract to that team’s slice, and nothing was left to enforce inside the tool. That does not save everything. The leave-balances dashboard served seven thousand managers from one shared model, each entitled to different rows, so its per-viewer filtering had to live inside Power BI after all. Filtering like that is the exception, and it derives from the same policy file or it forks into a second one. Where even a scoped copy is too much risk, the classification can forbid caching outright.

Discoverable by default, readable per policy

One mechanism settles both bills, because computing grants from a policy makes every grant’s reason readable and also puts a dataset in front of someone the moment their attributes entitle them to it. At the second client our answer to invisibility was show-and-tell sessions and Power BI trainings, which reached whoever showed up. A catalog would have put the company-wide report in front of the analyst who was about to rebuild it.

Available by default sounds like the opposite of a security argument until existence and contents are separated. Existence is metadata: which datasets there are, what they are for, who owns them, what grain they carry. The same policy function decides who may see that too, and for most data the right answer is everyone, because awareness is the thing that was missing. Reading the rows is a separate and stricter decision. And where a dataset’s existence is itself sensitive, the classification says so, and the entry simply does not appear for anyone the policy does not name.

Part 12 set the bar for what earns the word self-service: find it without asking, work out the fields without a call, trust one agreed definition, and reach it through whatever tool you already work in. Part 13 supplied the agreed definition. Deriving access supplies finding it without asking, and uniform enforcement supplies reaching it from any tool.

A catalog everyone can browse is also the raw material for a marketplace. Dehghani describes a discovery function that ranks data products on usage, satisfaction and quality and demotes low-rated duplicates, warning that the loop runs success to the successful, with winners accumulating dependents until they grow fragile to change [1].

Who owns the map

Every grant issued by hand contains a policy decision, made in the moment and recorded nowhere. Most platforms still run on those because writing the policy down means somebody has to decide, in a file, which attributes entitle a person to which data, and then be answerable the day that decision turns out wrong. Part 13 ran into the same wall over who may define a number, and deciding who may see what is the harder version, which no warehouse feature settles and no governance suite settles either, however smoothly it routes the approval.

Pick one table in your warehouse and ask why the last person granted access may see it. If the answer begins with a filename, you have a policy. If it begins with somebody’s name, you have a ledger in one head, and a holiday calendar deciding when your governance works.

The reason to settle it now is the consumer arriving next. Conversational BI puts an agent between the question and the warehouse: it takes a plain-language question, turns it into a query, and answers at machine speed. A person blocked by access files a ticket and waits. An agent does not; it answers with whatever it could reach and says nothing about what it could not. In Part 15 I take apart how that confidently incomplete answer moves a decision before anyone thinks to check it. Pointed at the chokepoint, though, an agent cannot query what the policy disallows, and a catalog it may browse is what lets it find what it is entitled to.

References

[1] Z. Dehghani, Data Mesh: Delivering Data-Driven Value at Scale. Sebastopol, CA, USA: O’Reilly Media, 2022. Ch. 3, “Secure”; Ch. 5, “Introduce feedback loops”. [Online]. Available: https://www.oreilly.com/library/view/data-mesh/9781492092384/