Aggregate Visibility Gap

Row-level security restricts each regional manager to their own region's orders. The CFO then asks that every manager's dashboard show the company-wide total next to their region, so each one can see their share. The policy is correct, the attributes are right, and the total comes back as the manager's own region.

A row predicate can only answer one question - may this principal see this row - and the requirement is a function of rows they may not see. No filter expresses it. This is not a bug in the policy but a category difference between row-level and aggregate entitlement, and almost every platform meets it within a year of enforcing row security. What happens next decides whether the controls survive, because the convenient answers all work by removing the policy from the path.

Why it matters

The two shortcuts are the commonest ways row-level security quietly stops applying. A second role that drops the filter is standing access to every row gated by a UI affordance, and the audit log cannot then distinguish a total from a full-table extract. Computing the total in the BI tool under a service account moves enforcement into the reporting tool, where every scheduled export and drill-through inherits the robot's visibility.

Recognising the gap as a design question rather than an exception request is the whole value. Aggregate entitlement is published, not bypassed.

Implementation patterns

• Publish the aggregate as its own object at a deliberately coarse grain - region by month - with no row policy and its own grant. Across 30 regions that is a few thousand rows a year.
• Treat the grain as the access decision. It is the reviewable artefact: anyone can read the definition and say whether that level of sharing is acceptable.
• Test for differencing before shipping. If the aggregate carries region by day by product by store, a manager holding their own detail can subtract their contribution and infer a rival region's single large customer.
• Add a minimum-cohort rule so thin cells are suppressed rather than rounded.
• Refuse new dimensions by default, because every added dimension is a disclosure decision and the requests arrive looking like feature requests. Past about a dozen such tiles the aggregates belong in the semantic layer, where they stay reviewable.

Industry example

The pattern is visible wherever a platform serves both a tenant-restricted detail view and a market benchmark: a merchant sees its own transactions and a category average, a supplier its own fill rate and the retailer's mean. Benchmarks like these run in production as separate aggregates with minimum cohort sizes, because the alternative is giving every tenant a path to the detail. The commercial version of this feature is the same control as the internal one, which is why the aggregate is designed as a product rather than configured as an exception.

Failure scenarios

• The second role, used accidentally for an extract, with no way to tell afterwards whether a row read was a total.
• The service-account tile, after which a drill-through on the total returns other regions' rows to the manager's screen.
• Grain creep: the aggregate gains product and week over two quarters until differencing is trivial, with no single change big enough to trigger a review.

Trade-offs

A published aggregate costs one more model to own, a refresh to schedule, and a standing argument about its grain. The bypass costs nothing today and converts the access story from "the warehouse enforces it" to "the warehouse enforces it except in the tiles where it does not", which is unanswerable in a review. The honest limitation: an aggregate cannot serve a genuinely ad-hoc need, so an analyst who must correlate two regions at arbitrary grain needs row access - a policy decision with a sign-off, not a modelling exercise.

When not to use it

If every manager is in fact permitted to see every order and the row policy exists only to keep dashboards focused, delete the policy. A convenience filter implemented as a security control teaches the organisation that controls are negotiable and generates exactly this class of exception. Confirm what the restriction is for before building around it. And with a handful of audiences and one total, a pre-aggregated table is adequate; a governed aggregate layer in the semantic layer is machinery nobody needs yet.

Interview question

Q: Row-level security is correct and the CFO wants every regional manager to see the group total on their own dashboard. Walk me through the options, say which you would ship, and tell me what you would check before you did.

What a strong answer covers: why no predicate expresses the requirement · the published aggregate and why its grain is the access decision · the differencing check and minimum cohort · why the second role and the service account both end enforcement · and the flip - if everyone may see everything, remove the policy instead.

Quick check

Quiz: Why can row-level security not give a manager a company-wide total? - It decides row visibility only; a total over invisible rows is a different entitlement and needs its own object.

Flashcard: What is the reviewable artefact when publishing a policy-free aggregate? - Its grain, because the grain decides what can be differenced back to rows.