Databricks interview questions: Row filters and column masks

Say this first: 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.

Why: 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.

CREATE FUNCTION sec.region_filter(region STRING)
RETURN is_account_group_member('emea_analysts') AND region = 'EMEA'
  OR is_account_group_member('global_analysts');

ALTER TABLE sales.orders SET ROW FILTER sec.region_filter ON (region)

Common mistake: Calling a filtered view equivalent security, when it depends entirely on nobody having base-table access.

Follow-up they will ask: What happens when an engineer is granted SELECT on the base table for a debugging task?

Say this first: 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.

Why: 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.

CREATE FUNCTION sec.mask_nid(nid STRING) RETURN
  CASE WHEN is_account_group_member('compliance') THEN nid
       ELSE concat('*****', right(nid, 4)) END;

ALTER TABLE hr.people ALTER COLUMN national_id SET MASK sec.mask_nid

Common mistake: Writing a mask that returns a different type than the column, or returning NULL where the column is non-nullable in a consumer's contract.

Follow-up they will ask: How would you mask only the rows flagged as restricted rather than the whole column?

Say this first: The querying principal needs EXECUTE on the filter or mask function, and USE on the schema holding it, not just SELECT on the table.

Why: 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.

GRANT USE SCHEMA ON SCHEMA prod.sec TO `account users`;
GRANT EXECUTE ON FUNCTION prod.sec.region_filter TO `account users`

Common mistake: Granting SELECT on the table and assuming the policy function comes along with it.

Follow-up they will ask: Why is granting EXECUTE broadly on these functions not a security problem?

Say this first: 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.

Why: 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.

Senior depth: I treat masking as something with a measurable unit cost rather than a free governance checkbox, so I benchmark before and after on the real dashboard query, not on a SELECT of one column. Most of the time the answer is not to weaken the policy — it is that the dashboard was selecting forty columns to display six. Beyond that I keep mask bodies to plain SQL expressions, avoid lookups inside a mask, and for the genuinely hot path consider serving an aggregate where the sensitive column never appears. The governance conversation I want to avoid is one where performance pressure leads to dropping masks instead of fixing the query.

Common mistake: Blaming the warehouse size when the plan shows the added cost sitting in per-row expression evaluation.

Follow-up they will ask: Which of those twelve columns would you argue does not need a mask at all?

Say this first: 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.

Why: 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.

CREATE OR REPLACE FUNCTION sec.region_filter(region STRING) RETURN
  is_account_group_member('etl_service') 
  OR (is_account_group_member('emea_analysts') AND region = 'EMEA')

Senior depth: The pattern I insist on is that every policy function has an explicit branch for automated principals, written first, so a pipeline never falls through to a false predicate by accident. Alongside that, policy rollouts get a row-count check on the downstream tables for the first few runs, because a silent zero is worse than a failure — a failure pages someone. Longer term I would rather not apply row filters to the raw layer at all: pipelines read the full grain, and the restrictions live on the serving tables humans actually query.

Common mistake: Testing the filter only as a human analyst and never as the identity the pipelines run under.

Follow-up they will ask: How would you catch a silently empty load before the dashboard does?

Say this first: 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.

Why: 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.

ALTER TABLE hr.people ALTER COLUMN national_id
  SET TAGS ('data_classification' = 'pii_high');

SELECT * FROM system.information_schema.column_tags
WHERE tag_name = 'data_classification'

Senior depth: At that scale the control is the tagging pipeline, not the policy syntax, so that is where I would put the engineering. Classification on ingest, a periodic scan that flags columns matching sensitive patterns without the tag, and a report of untagged candidates that goes to the data owner rather than to the platform team. The policy itself becomes a small number of rules that almost never change. The failure mode to design against is a confident dashboard saying one hundred percent of tagged columns are masked, while the real exposure is the columns nobody tagged.

Common mistake: Scripting four thousand ALTER statements and treating the one-time run as the end of the problem.

Follow-up they will ask: How would you find a sensitive column that nobody tagged?

Say this first: 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.

Why: 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.

Senior depth: I answer audits with the mechanism and the exception list, because a claim with no exceptions is never believed and usually wrong. The mechanism is UC-level enforcement, which covers every client that goes through the catalog. The exceptions are direct storage access, non-UC legacy compute, and anyone who can edit the policy function — so the audit deliverable includes who holds those, and ideally that it is nobody outside a break-glass group. That framing turns the conversation from 'is it secure' into 'here are the three paths we control', which is the only version of the answer that survives scrutiny.

Common mistake: Claiming enforcement is universal while a legacy cluster or a storage credential still allows raw path reads.

Follow-up they will ask: Who in your workspace can still read the underlying files directly?

Say this first: 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.

Why: 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.

CREATE FUNCTION sec.tenant_filter(tenant_id STRING) RETURN
  is_account_group_member('platform_etl')
  OR is_account_group_member(concat('tenant_', tenant_id));

ALTER TABLE app.events SET ROW FILTER sec.tenant_filter ON (tenant_id)

Senior depth: The architecture is only as good as the weakest write to the mapping, so I design around that: tenant groups provisioned from the identity provider rather than created by hand, change review on the policy function, and a very small set of principals who can ALTER the table's filter. I would also insist on a detective control alongside the preventive one — a scheduled check that joins audit reads against the tenant mapping and alerts on anything inconsistent — because in multi-tenant systems the cost of a single wrong group membership is a disclosure incident, not a slow dashboard.

Common mistake: Building one view or one table per tenant, then discovering every schema change is a hundred migrations.

Follow-up they will ask: What is the single change that would expose one tenant's rows to another?