Home › Learn › Databricks interview prep › Row-level security

Databricks interview prep · data engineering

Row-level security and column masking

How row filters, column masks, dynamic views and tag-driven policies work in Unity Catalog, and the performance and bypass questions interviewers ask.

Maintenance commandsRow-level securityCompute choiceJoinsSkew and shuffleAQE, Photon, POCost and performance leversDeclarative and semanticAsset BundlesLakeflow ConnectUniFormVector Searchdbt and ODCSEgress and ingressMaintenance in picturesClustering and compactionMaintenance strategySlow writesBackup and recoveryTaggingQuestion bank

Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.

User runs a queryUnity Catalog adds therow filter and columnmasks to the planEngine scans the filesUser sees only allowedrows

The policy is a SQL function the engine injects into the query plan. The user never sees an unfiltered scan.

Implementation

Row filters and column masks in Unity Catalog

-- the policy function
CREATE FUNCTION sec.region_filter(region STRING)
RETURN is_account_group_member('global_admins')
    OR EXISTS (SELECT 1 FROM sec.user_region_map m
               WHERE m.user_email = current_user() AND m.region = region);

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

-- column mask
CREATE FUNCTION sec.mask_ssn(ssn STRING)
RETURN CASE WHEN is_account_group_member('hr') THEN ssn ELSE '***-**-****' END;

ALTER TABLE hr.people ALTER COLUMN ssn SET MASK sec.mask_ssn;

Compare

The ways to do it

ApproachHow it worksWeakness
Row filters and column masksPolicy attached to the table, enforced everywhereCost on big tables; a few feature limits
Dynamic viewsViews with CASE WHEN is_member(...); users get the view onlyBypassed if someone is granted the base table; policy repeated per view
ABAC (tag-driven policies)Policy attached to a governed tag, so any tagged column or table gets itNewer feature; check current status and limits
A copy of the table per groupPhysically separate dataStorage and sync burden. Avoid.

Hard follow-ups

Where it goes wrong

Performance

Is the filter a UDF? Does Photon still run? How to make it fast

A row filter is a SQL function, not a Python UDF. The engine treats a SQL scalar function as an expression it can fold into the query plan, so built-in functions inside it (comparisons, CASE, IN, group-membership checks) are ordinary expressions that Photon can run. It is not called once per row like a Python function. Photon stops helping only if the function calls something Photon cannot run, such as a Python UDF. Confirm on your runtime by looking for Photon operators around the filter in the query profile; I have not measured this on Azure here.

How to make it fasterWhy
Use built-in expressions onlyKeeps the filter inside Photon; no Python UDFs, no calls to outside services
Use group checks such as is_account_group_memberThey are constant for the whole query, so the engine evaluates them once, not per row
Keep any lookup table smallA subquery against an entitlements table becomes a semi-join; a small table is broadcast and cheap, a huge one is a shuffle on every query
Filter on a plain column compared to a constant or a short listSimple predicates can also help data skipping
Cluster the table on the filter columnSkipping then removes whole files for each user's slice
Avoid non-deterministic functionsThey block optimizations
Precompute for hot dashboardsA gold table or materialized view per audience avoids the filter on the heaviest queries (the security rule must still hold for the source)
MeasureCompare the query profile with and without the filter, and read EXPLAIN

Related

More on this site

Metric views, taught from zero includes row-level security on a semantic layer.

Practice

Questions to rehearse

  1. Implement row-level security and column masking. What are the performance and bypass risks?
  2. Dynamic views versus row filters: which would you choose and why?
  3. How would you give each tenant only their rows in a shared table?
  4. Why is a client-supplied tenant filter not safe?
In an interview, say: Row filters and masks are SQL functions attached to the table and enforced inside the plan. The risks are the lookup cost and any path that reaches the files without going through the engine.

FAQ

Questions people ask

Is a dynamic view as safe as a row filter?

Only if nobody is granted the base table. A row filter travels with the table, so it holds on every route through the engine.

Do row filters slow queries?

They can, especially with a large mapping table. Keep lookups small and cluster on the filter column.

Next

Where to go next

All Databricks interview topics · All data engineering · Databricks on this site