Analysing and serving · Chapter 20

Multi-Tenant and Per-User Reporting

Two questions come up in almost every conversation with a company that sells software to other businesses. How do I show each customer only their own data, and how do I show each person inside a customer only what their job entitles them to see.

The mechanism, briefly #

Unity Catalog enforces both rules through SQL functions attached to a table. A row filter returns a boolean evaluated for every row at query time, so rows returning FALSE never reach the reader, while a column mask is attached to one column and returns either the value or something safer. Both are managed by the table owner or a principal holding MANAGE, both resolve identity through current_user() and group membership through is_account_group_member(), and a column takes one mask whose return type must match the column type or be castable to it.

Here is the shape we use for an auto finance gold table where every row carries a dealer_id.

-- One function, evaluated per row, returning TRUE when the reader may see it
CREATE OR REPLACE FUNCTION originations_prd.gold.loan_tenant_filter(dealer_id STRING)
RETURN is_account_group_member('loans-internal-all-tenants')
    OR is_account_group_member(concat('tenant-', dealer_id, '-readers'));

ALTER TABLE originations_prd.gold.loan_application
  SET ROW FILTER originations_prd.gold.loan_tenant_filter ON (dealer_id);

A column mask follows the same pattern, returning a value rather than a boolean.

CREATE OR REPLACE FUNCTION originations_prd.gold.mask_applicant_ssn(ssn STRING)
RETURN CASE
    WHEN is_account_group_member('loans-pii-readers') THEN ssn
    ELSE concat('XXX-XX-', right(ssn, 4))
  END;

ALTER TABLE originations_prd.gold.loan_application
  ALTER COLUMN applicant_ssn SET MASK originations_prd.gold.mask_applicant_ssn;

The published performance guidance is worth following from the first function you write. Prefer simple expressions and CASE over joins to lookup tables, keep the number of distinct masks on a large table small, pass as few arguments as the logic needs, avoid long chains of AND conjuncts, use deterministic and error-free expressions such as try_divide, and write SQL functions rather than Python ones.

The other limits are worth stating plainly rather than discovering later. Policies attach to tables; view support has been arriving by release, so check the current documentation before designing around it. Neither is honoured by Iceberg REST catalog reads or the Unity REST APIs, and neither can be combined with OpenSharing at table level. A policy function cannot reference a table that itself has active policies, which quietly rules out the most obvious design for entitlement lookups.

Problem one: separating tenant from tenant #

There are three architectures, we have deployed all of them, and they differ mainly in how well they survive growth.

A catalog or schema per tenant gives the strongest isolation story an auditor will accept, because the boundary is a grant rather than a predicate. It suits tens of tenants rather than hundreds, a regulator or large customer contractually requiring physical separation, or tenants needing bespoke schemas. Its cost is that every schema change is a migration run N times, and at a few hundred tenants the object count becomes the dominant engineering problem.

One shared table with a tenant key and a row filter scales to thousands of tenants with one schema to evolve and one set of pipelines to operate. The trade is that the row filter is now security-critical code, and a mistake in it is a cross-tenant disclosure rather than a broken report. We treat those functions as we treat authentication code, with a review requirement, a test suite, and no ad hoc edits in a workspace UI.

The hybrid puts your largest or most regulated tenants in their own catalogs and leaves the long tail in the shared table. Most successful products end up here, and the honest cost is two code paths every new feature must work in.

Our heuristic is straightforward. Below roughly fifty tenants with no expectation of rapid growth, use a catalog per tenant. Above that, or wherever tenants sign up self-service without you noticing, use the shared table with a row filter, and move a tenant out to its own catalog when a contract requires it, treating that as a product feature with a price rather than an engineering favour.

Problem two: entitlements inside a tenant #

Once a tenant is isolated, the second question begins. A regional manager sees their region, a rep sees their own accounts, and a compliance officer sees everything with account numbers masked. This is a mapping problem, solved with account groups plus a table mapping each principal to the scopes they hold.

The temptation is a row filter that joins to that mapping table, and the platform allows it up to a point, but it is the design that gets expensive because the join runs inside every scan. Where the entitlement set is small and stable, encode it in group membership and keep the filter to a CASE expression. Where it is genuinely dynamic, such as a rep's account list changing daily, we materialise the entitlement into the gold table as a derived key that a simple filter can test, refreshed by the pipeline. That moves the join to build time, where it is cheap and observable, instead of query time, where it is neither.

The identity problem, which is where teams get this wrong #

Everything above assumes the query arrives carrying the reader's identity. Whether it does is not a detail.

When the consumer is an identified principal in your identity provider, meaning an employee, an analyst, or a partner onboarded as a Databricks user, row filters work exactly as advertised. The query runs as that person, current_user() returns them, is_account_group_member() resolves against their real groups, and the rule holds whether they arrive through a dashboard, a SQL editor, a notebook, or a JDBC connection from a tool you have never heard of. That is why we push internal entitlements into the catalog rather than the BI layer, since a filter defined in a dashboard is one a determined analyst can go around.

When the consumer is an end customer using your product, none of that applies. They have no identity in your Databricks account, and the query arrives as your application's service principal, so from the platform's point of view there is one reader entitled to everything. The identity has to be carried explicitly, either by the application passing tenant and user context into a parameterised query, or, which we prefer, by putting the read model in Lakebase with Postgres row-level security keyed to a session variable the application sets before issuing any query.

Beyond the basics #

Test the negative case, in CI. The test that matters is not that a permitted reader sees their rows, it is that a restricted reader does not see the ones they should not. We run a suite that connects as a service principal in each entitlement group and asserts an empty result for every scope it does not hold, on every pipeline run rather than once at handover. A row filter that fails open is worse than none, because the organisation believes it is protected.

Proving isolation to somebody else's auditor. Unity Catalog system tables hold the audit log, the grants, and the query history, and lineage records which gold tables feed which dashboard. Between them, the two questions an auditor actually asks, who has been granted access to this tenant's data and who has read it, become queries with dated evidence rather than a policy document.

Performance on a high-cardinality tenant key. Filtering rows the engine has already read is wasted work, so cluster the table on the tenant key and let file pruning discard most of the data before the filter runs. Liquid clustering on dealer_id is usually the highest-value single change available on a large shared table. The related trap is the mapping-table filter above, whose join defeats pruning entirely.

Embedded dashboards versus your own API. An embedded AI/BI dashboard carries the catalog's rules with it, which makes it a strong fit when your customers' users have real identities. When they do not, or when you need heavy branding or entitlements more complex than a filter, build against a serving API as described in Serving Data to Applications.

Onboarding and offboarding. Both cross the medallion layers and deserve a runbook rather than a ticket. Onboarding creates the groups, entitlement rows, and ingestion configuration in one automated operation, because it will run often. Offboarding is harder, since a deletion request has to reach bronze, silver, gold, the serving layer, and any exports. We write the deletion path alongside the ingestion path, because retrofitting it across five layers costs considerably more.

The final row is the point we would make. Power BI row-level security and Looker access filters are both perfectly good, and the difficulty is that they live in the reporting tool, so the rule holds for people arriving through that tool and nobody else, whereas a rule expressed once in the catalog is one a data scientist opening a notebook also gets. For the end-customer case every column requires you to build something, so the argument for Databricks is narrower, being that the serving store sits inside the same governance boundary and is fed by managed synchronisation rather than a reverse ETL job your team maintains.

Our position is that tenant isolation belongs in the lowest layer capable of enforcing it, that the layer is the catalog for identified users and the serving database for end customers, and that application code is never it. The groundwork it depends on is covered in Unity Catalog.

Table of Contents