Two policies cover this warehouse: one masks the personal columns for everyone who is not on a short list, one restricts rows to a sales territory. Both are attached to the gold schema and neither names a table.
The mask is a UDF whose return type must match the column's type or be castable to it — Databricks automatically casts both the input and the output of a mask resolved from an ABAC policy. All three personal columns on gold.dim_customer are STRING, so here one function covers them with no casting at all; the cast rule is what lets one function cover a class rather than a type.
Why
The platform resolves one distinct column mask per column and user — the scope is the column, not the table. Two mask policies on the same table are fine while they target different columns; the query fails only when two distinct masks resolve for the same column and the same principal, with COLUMN_MASKS_FEATURE_NOT_SUPPORTED.MULTIPLE_MASKS. (Row filters are the stricter case: one distinct filter per table and user.)
That still argues for one generic function per sensitive class rather than a proliferation of masks — but for a design reason, not because the platform forbids the alternative. A tag-driven policy naturally produces exactly one mask per column, so you only meet this limit by writing a second policy that overlaps the first, which is a signal the two policies should have been one.
It also keeps the exempt list short. Two groups appear in EXCEPT, both named in the permissions concept. In the per-table world, the answer to 'who can read customer email' is a sweep of every mask on every table.
How
sqlThe UDF, then the policy — attached to the schema, naming no table
CREATE OR REPLACE FUNCTION dwh_prod.gold.mask_pii(value STRING)
RETURNS STRING
COMMENT 'Redact personal data. NULL stays NULL so counts do not move.'
RETURN CASE WHEN value IS NULL THEN NULL ELSE '***' END;
CREATE OR REPLACE POLICY mask_customer_pii
ON SCHEMA dwh_prod.gold
COMMENT 'Redact every column tagged pii for all but the named readers'
COLUMN MASK dwh_prod.gold.mask_pii
TO `account users`
EXCEPT `dwh_pii_readers`, `dwh_prod_pipelines`
FOR TABLES
MATCH COLUMNS has_tag('pii') AS pii_col
ON COLUMN pii_col;
MATCH COLUMNS binds the alias pii_col to every column satisfying the condition, and ON COLUMN pii_col applies the mask to each of them. Three columns, one policy, and the next tagged column joins without an edit.
Because the platform casts in and out, the reflex "one UDF per column type" is wrong: a STRING mask covers any column a STRING is castable to, and the documented cross-type pattern goes further by declaring the function over VARIANT. What you pay for that reach is when you find out — an illegal cast is a runtime error on an analyst's query, not a rejection at CREATE POLICY. So a mask that spans types has to be exercised against every tagged column type in dwh_dev before the policy reaches prod.
The other way to avoid a function per column is to let one function read the tag. Tag-introspection functions such as get_column_tag_value() take the MATCH COLUMNS alias and return the governed value, which the policy passes into the mask as an ordinary argument:
sqlOne UDF for the whole class, shaped by the tag value
CREATE OR REPLACE FUNCTION dwh_prod.gold.mask_pii_by_kind(value STRING, kind STRING)
RETURNS STRING
COMMENT 'email keeps its domain, phone keeps two digits, anything else is ***'
RETURN CASE
WHEN value IS NULL THEN NULL
WHEN kind = 'email' THEN concat('***@', split_part(value, '@', 2))
WHEN kind = 'phone' THEN concat('*** ', right(value, 2))
ELSE '***'
END;
-- Replaces mask_customer_pii above -- do not deploy both: two masks resolving
-- for the same column and principal is the MULTIPLE_MASKS error below.
CREATE OR REPLACE POLICY mask_customer_pii
ON SCHEMA dwh_prod.gold
COMMENT 'Redact every column tagged pii, shaped by the tag value'
COLUMN MASK dwh_prod.gold.mask_pii_by_kind
TO `account users`
EXCEPT `dwh_pii_readers`, `dwh_prod_pipelines`
FOR TABLES
MATCH COLUMNS has_tag('pii') AS pii_col
ON COLUMN pii_col USING COLUMNS (get_column_tag_value(pii_col, 'pii'));
-- CREATING a policy that calls a tag-introspection function needs
-- Databricks Runtime 18 LTS or above. QUERYING under one does not, so the
-- floor applies to your deploy pipeline, not to the analyst's warehouse.
Start with the flat mask_pii. Reach for the tag-reading version only when one class genuinely needs different shapes — a partly-visible email is a business decision, not a default, and every branch is a branch someone has to review.
sqlWhat a non-exempt analyst sees, and how you check the policy is attached
SELECT customer_id, customer_name, email, city, country
FROM dwh_prod.gold.dim_customer
WHERE _tech_is_current;
-- customer_id | customer_name | email | city | country
-- C-100482 | Nordbau Handel | *** | Hannover | DE
-- C-100483 | Alpin Technik | *** | Innsbruck | AT
SHOW EFFECTIVE POLICIES ON TABLE dwh_prod.gold.dim_customer;
-- policy_name | policy_type | catalog | schema | comment
-- mask_customer_pii | COLUMN_MASK | dwh_prod | gold | Redact every column ...
-- SHOW tells you WHICH policies reach the table and WHERE they are attached.
-- It does NOT return the masking function or the principals. Those come from
-- DESCRIBE, one call per policy, as key-value rows rather than columns:
DESCRIBE POLICY mask_customer_pii ON SCHEMA dwh_prod.gold;
-- info_name | info_value
Doing it
Write mask_pii once, in gold, and grant EXECUTE on it to the same principals the policy names
Keep NULL returning NULLA mask that turns NULL into '***' changes COUNT(email) and someone will reconcile against it.
Deploy the policy to dwh_dev and dwh_test with the same statementA policy that exists only in prod is a policy first exercised in prod.
Verify with SHOW EFFECTIVE POLICIES ON TABLE against a table you did not name in the policyThat is the proof the schema attachment works — then DESCRIBE POLICY for the function and the principals, which SHOW does not return.
Exercise the mask against every column type it can reach, in dwh_dev, before the policy shipsA cast the platform cannot make fails the query at runtime, and CREATE POLICY will not have warned you.
Embedding it
Have an engineer query dim_customer as themselves and as a member of dwh_pii_readers, side by sideTwo result sets persuade better than any diagram, and it is the screenshot for the concept document.
Give the team the second policy to write — the one for a class you have not coveredReview it rather than authoring it.
Let them hit the one-mask-per-column conflict once, in devHave them write a second policy that targets a column the first already masks. The error teaches the constraint better than the docs page — and so does the second policy that targets a different column and works fine.
Defaults
One mask UDF per data class; differentiate with tags, not with a function per columnThat includes differentiating by reading the tag value inside the function.
Return type matches the column type or is castable to itDatabricks casts a policy-resolved mask's input and output, so a type mismatch is not rejected at deploy time; an impossible cast fails the analyst's query instead.
Every column type a mask can reach is covered by a dev testThe type check happens at query time, not at CREATE POLICY.
EXCEPT lists contain groups only, and every group is justified in writing
Gotchas
A second column-mask policy reaching the same column for the same principalOnly one distinct mask can resolve per column, so the query errors instead of masking — and it errors for the analyst, at 08:00, on a dashboard, not for the person who added the policy. The overlap is easy to miss because the two policies name no columns at all; they collide through their tag predicates.
Masking as a substitute for deletionThe value is still stored, still in the Delta history, and still leaves the platform in any export by an exempted identity. Under GDPR the column remains personal data — see the classification route.
TO `account users` includes service principalsEvery job that is not explicitly exempted starts reading '***', usually discovered when a downstream table quietly fills with asterisks that then survive into a report.
Forgetting EXECUTE on the UDFCreating a policy needs MANAGE on the securable and EXECUTE on the masking function, so the statement is rejected outright — and the error names the function rather than the policy, which reads as if the function were never deployed. The bundle order is function, then grant, then policy; get it wrong and the deploy fails on a green-looking pull request.
CREATE OR REPLACE POLICY replaces the whole definition, not the clauses you retypedIn SQL there is no partial edit: ship a change to TO without re-stating EXCEPT and the exemptions are gone, the pipelines start writing '***' on the next run, and the pull request looks like a two-line change. Keep the policy DDL in one file and always edit it whole. (Catalog Explorer's Edit flow and the SDK/REST update_policy do support partial updates — which is a different problem, and the review topic covers it.)
Assuming a type mismatch is caught when the policy is createdIt is not: the cast is attempted per query, so a mask that reaches a column it cannot cast to fails that column's queries at runtime — first noticed by whoever selected the column, on a table the policy author never opened.
This distributor sells into DE, AT, CH and NL, and sales roles should see their own territory. The filter function takes the scoping column and returns a boolean; group membership does the deciding.
Why
The default alternative is a view per territory — dim_customer_de, dim_customer_at — and it multiplies: four countries times every table anyone wants scoped, each view with its own grants, each drifting from the base table's schema the first time a column is added.
The row filter is evaluated at query time against group membership, so adding a salesperson to dwh_territory_ch is the entire onboarding step. No view, no grant, no deployment.
How
sqlOne function, one policy, four territory groups
CREATE OR REPLACE FUNCTION dwh_prod.gold.in_sales_territory(country STRING)
RETURNS BOOLEAN
COMMENT 'True if the caller belongs to the territory group for this country'
RETURN is_account_group_member(concat('dwh_territory_', lower(country)));
CREATE OR REPLACE POLICY territory_rows
ON SCHEMA dwh_prod.gold
COMMENT 'Sales roles see only the countries their territory group covers'
ROW FILTER dwh_prod.gold.in_sales_territory
TO `dwh_sales`
EXCEPT `dwh_prod_pipelines`
FOR TABLES
WHEN has_tag_value('row_filter', 'territory')
MATCH COLUMNS has_tag_value('row_scope', 'country') AS country_col
USING COLUMNS (country_col);
WHEN selects which tables the policy touches, from their tags. MATCH COLUMNS finds the scoping column, and USING COLUMNS passes it as the function's argument. Tag a second table row_filter = territory with a column tagged row_scope = country and it is filtered the same way, immediately.
The EXCEPT dwh_prod_pipelines line looks redundant beside TO dwh_sales — a pipeline is not a salesperson — and it is not. Service principals get added to business groups for an unrelated access request and nobody rereads the policy; a territory-scoped pipeline then writes a partial table, and no test tells a partial load apart from a quiet day.
Doing it
Derive the group names from the dataCountry values are DE, AT, CH and NL, so the groups are dwh_territory_de, _at, _ch, _nl and the mapping is a lower() call rather than a lookup table.
Keep the filter function to group membership and cheap expressionsIt runs per row on every query against every covered table.
Tag the table and the scoping column together, in one statement pairA table can then never be marked filtered without a column to filter on — and assert in the review query that the count of row_scope columns per table is exactly one, because zero leaks and two fails the query.
Test as a member of exactly one territory group, and as a member of none
Embedding it
Have the team add a fifth territory end to end — group, tag, test — while you watchThat is the operation they repeat without you.
Ask what a user in no territory group sees, before showing themThe guess is 'an error'; the answer is zero rows.
Make them write the reconciliation test themselvesIt proves an unfiltered service principal and a DE user disagree by exactly the non-DE rows.
Hand over the decision about filtering facts rather than making itIt is a business question about what a territory manager may see, and it needs a name against it.
Defaults
One filter function, parameterised by the scoping column; territories are groups, not code
Table tag plus column tag applied together — the pair is the contract
Pipeline identities exempted, so builds are not silently territory-scoped
The asymmetry between filtered dimensions and unfiltered facts written into the permissions concept
Gotchas
A user in no territory group sees zero rows, with no errorTheir dashboard shows €0 revenue and they report a data outage; you spend the morning in the pipeline logs. Give every account exactly one territory group at creation and assert it in the access review.
The function runs per rowA filter that joins to a mapping table instead of calling is_account_group_member turns every scan of every covered table into a join, and the cost surfaces as a warehouse that got slower for no attributable reason.
Only one distinct row filter resolves per table and user — stricter than masks, which are per columnA second policy with a different function on the same table fails the query rather than intersecting the conditions, so two teams each scoping the same table their own way take it offline for everyone in both TO lists.
Tagging a table row_filter = territory when no column carries row_scopeNothing errors and nothing is filtered — the table looks governed in the inventory while serving every row.
Tagging two columns row_scope = country on the same tableUSING COLUMNS (country_col) passes one argument, the alias now matches two columns, and the engine cannot choose — so the query fails, for every user the policy covers, on every query against that table. The zero-column case leaks data quietly; this one is a production outage, and it is one careless SET TAG away.
Assuming a policy applies when most of its conditions matchA policy may carry up to three MATCH COLUMNS expressions and all of them must match for it to attach. Match two of three and the policy silently does not apply: no error, no filter, every row served — and the DDL sitting in the repo says the table is filtered.
The unknown member disappearsdim_customer carries an UNKNOWN row so unresolvable facts stay visible instead of being dropped; its country resolves to a dwh_territory_ group that does not exist, and a NULL country makes concat itself NULL, so the row is filtered out for every sales user. The one row that exists to make bad data visible is the one row the filter hides. Decide who owns the unknown territory and coalesce it inside the function.