Question 1

The current design gives each region a filtered view over the same fact table. Why is a row filter on the table a stronger control?

Accepted Answer

A view only protects people who cannot reach the base table; a row filter is enforced at the table, so every access path gets filtered. With the view approach, security depends on withholding SELECT on the base table from everyone who should see only part of it. The moment someone gets base access for a legitimate reason — a debugging session, a pipeline that needs the raw grain, a migration script — the restriction is gone and nothing records that. A row filter attached with ALTER TABLE ... SET ROW FILTER applies inside Unity Catalog when the table is read, so the same rules hold through a SQL warehouse, a notebook, a JDBC client, or a downstream view. You also stop maintaining one view per audience, which is where the drift lives. The filter is a BOOLEAN user-defined function, so the logic is defined once and referenced by every table that needs it. A filtered view protects only those denied base-table access. A row filter is enforced at the table, on every access path. The filter is a BOOLEAN UDF referenced by ALTER TABLE. One function replaces a view per audience and the drift between them.

Question 2

Write me a column mask that shows the full national ID to the compliance group and the last four digits to everyone else. What are the rules the function has to obey?

Accepted Answer

A column mask is a UDF that returns the same type as the column, takes the column as an argument, and is attached with ALTER COLUMN ... SET MASK. The mask function receives the column value and returns a value of the same data type, so a STRING column needs a STRING back — you cannot return NULL of a different type or a struct. Inside the function you branch on identity using is_account_group_member() for group membership or current_user() for the individual. Attaching it with ALTER TABLE ... ALTER COLUMN ... SET MASK means the rewrite happens in Unity Catalog on every read of that column, regardless of client. You can pass other columns of the same row into the function as extra arguments if the masking decision depends on them, for example masking only rows flagged as restricted. The function lives in a schema like any other object, and callers need EXECUTE on it. The mask UDF must return the column's own data type. Branch on is_account_group_member() or current_user() inside the function. ALTER COLUMN ... SET MASK attaches it at the table. Extra row columns can be passed in when the decision depends on them.

Question 3

A user with SELECT on the table now gets an error mentioning the masking function instead of rows. What is missing?

Accepted Answer

The querying principal needs EXECUTE on the filter or mask function, and USE on the schema holding it, not just SELECT on the table. A row filter or column mask is a function invocation injected into the query plan, and Unity Catalog checks privileges on that function like any other. If the policy functions live in a dedicated security schema, readers need USE CATALOG and USE SCHEMA on that schema plus EXECUTE on the function. This bites during rollout because the person who tested it was the function's owner and never hit the check. The practical pattern is to grant EXECUTE on the security schema's functions to a broad group — account users or a wide analyst group — since the function itself is what enforces the restriction and seeing its definition does not give anyone data. Test as a non-owner principal before you declare the rollout done. Readers need EXECUTE on the policy function, plus USE on its schema. The function owner never hits this, so it surfaces only after rollout. Granting EXECUTE broadly is safe; the function enforces the restriction. Always test as a non-owner principal before shipping.

Question 4

After adding masks to twelve columns, dashboard latency roughly tripled. Explain the mechanism and how you would confirm it.

Accepted Answer

A mask is an expression evaluated per row per masked column, so twelve masks on a wide scan add twelve function calls to every row, visible in the query profile. Unity Catalog rewrites the plan so each masked column becomes a function call over the underlying value. That is cheap for one simple CASE expression and expensive when you multiply it by twelve columns and hundreds of millions of rows, especially if a mask does string manipulation or calls something that cannot be evaluated natively. In the query profile you see time concentrated in the project operator rather than in the scan, and the row counts tell you the multiplier. The first fix is usually the query, not the policy: dashboards that select every column pay for masks on columns nobody displays, so narrowing the projection removes most of the cost. Then simplify the mask bodies themselves, keeping them to native SQL expressions so they can be evaluated efficiently rather than row-by-row in a slower path. Each mask becomes a per-row expression on its column. Twelve masks on a wide scan means twelve evaluations per row. Query profile shows the cost in the project operator, not the scan. Narrowing SELECT removes masks on columns nobody displays.

Question 5

The nightly ETL service principal started writing an empty gold table the day a row filter went live on its source. Why?

Accepted Answer

The filter applies to the service principal too, and it is not in any of the groups the function checks, so every row evaluates to false. Row filters are evaluated against the querying identity, and a job running as a service principal is just another identity. If the function is written as is_account_group_member('emea_analysts') OR is_account_group_member('global_analysts') and the service principal belongs to neither, the predicate is false for every row and the read returns nothing. Nothing fails, which is the dangerous part: the MERGE runs, matches nothing, and the downstream table is empty or frozen. The fix is to decide deliberately whether pipelines are in scope for the policy. Usually they are not — the ETL needs the full grain and the restriction belongs on the serving tables — so you add a branch for a pipeline group, and you verify by running the read as the service principal rather than as yourself. Filters evaluate against the querying identity, including service principals. An unmatched principal sees zero rows, with no error raised. Downstream tables go silently empty rather than failing loudly. Add an explicit pipeline-group branch and test as the service principal.

Question 6

You need the same PII masking on four thousand tables across twenty schemas. Attaching functions table by table is not viable. What do you propose?

Accepted Answer

Tag the sensitive columns and apply attribute-based policies against the tags, so the rule is defined once and follows the tag rather than the table name. Per-object ALTER statements do not scale because every new table is a new chance to forget, and nobody can answer 'is this rule applied everywhere' without a script. The attribute-based approach inverts it: columns carry governed tags such as a pii classification, and the policy says that columns with that tag are masked for anyone outside the compliance group. New tables inherit protection the moment their columns are tagged, and tagging can be driven by classification scanning rather than by hand. You still need the tagging to be right, which makes it the thing to audit — the information schema exposes tag assignments so you can report untagged columns whose names look sensitive. The per-table ALTER then stays for genuine exceptions rather than being the mechanism. Per-table ALTER does not scale and silently misses new tables. Tag the columns and write the policy against the tag. New tables are protected as soon as their columns are tagged. Audit tag coverage, because tagging becomes the real control.

Question 7

An auditor asks you to prove that an analyst cannot see other regions' rows through any tool, including a BI client over JDBC. How do you answer?

Accepted Answer

Because enforcement is in Unity Catalog at the table, not in the client or in a view, the same filter applies to JDBC, ODBC, notebooks, and downstream views alike. The argument is about where the rule lives. A BI tool connecting over JDBC to a SQL warehouse still goes through Unity Catalog, and the row filter is injected into the logical plan before the query's own predicates are applied, so there is no ordering trick that reveals filtered rows. The same holds for a notebook on a UC-enabled cluster and for a view defined on top of the table, since the view reads the base table through the same path. To demonstrate it rather than assert it, run the same query as the analyst's identity across each access path and show identical row counts, then show the grants and the function definition. Add system.access.audit evidence that queries from that principal are recorded with the table accessed. The remaining hole to be honest about is anyone with direct storage path access to the underlying files, which is why those grants should not exist. Enforcement lives in Unity Catalog, so every UC client inherits it. The filter is applied in the plan before the user's own predicates. Demonstrate by running as the analyst across each access path. Direct storage path access bypasses it, so remove those grants.

Question 8

Design the access model for a multi-tenant lakehouse where each customer's analysts may only see their own rows, in a single shared table.

Accepted Answer

One row filter driven by a governed mapping from principal to tenant, applied at the table, with pipeline identities explicitly scoped and the mapping itself audited. A table per tenant does not scale past a few dozen and makes every schema change a migration, so a shared table with a row filter is the right shape. The filter should not hard-code tenant names: it resolves the caller's tenant from a governed mapping — group membership per tenant, or a lookup the function consults — so onboarding a customer is a membership change rather than a code change. Pipeline and service identities get an explicit branch so the ETL reads the full grain, and that branch is the thing you review most carefully because it is the bypass. Sensitive columns get masks driven by tags rather than per-table ALTER, so the policy survives new tables. Finally, make the mapping auditable: who is in which tenant group, who changed it, and a periodic report from system.access.audit that no principal ever read rows outside its tenant. One shared table with a row filter beats a table per tenant. Resolve tenant from governed group membership, not hard-coded names. Give pipeline identities an explicit branch and review it hardest. Audit the mapping and the reads, because the mapping is the real control.