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.
Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.
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
| Approach | How it works | Weakness |
|---|---|---|
| Row filters and column masks | Policy attached to the table, enforced everywhere | Cost on big tables; a few feature limits |
| Dynamic views | Views with CASE WHEN is_member(...); users get the view only | Bypassed 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 it | Newer feature; check current status and limits |
| A copy of the table per group | Physically separate data | Storage and sync burden. Avoid. |
Hard follow-ups
Where it goes wrong
- Performance. A mapping-table lookup becomes a semi-join on every query. Keep the mapping table small and broadcastable, cluster the main table on the filter column, and read EXPLAIN to confirm the filter does not block pushdown.
- Bypass paths. Direct access to the cloud files, or compute that does not enforce policies, leaks data. Managed tables with storage credentials locked down close the path.
- Dedicated compute. Fine-grained policies there are enforced through serverless filtering behind the scenes, which can add cost and delay. Standard access mode enforces them natively.
- Sharing. Tables with row filters have limits when shared through Delta Sharing. Check before promising a design.
- The function. Keep it cheap and predictable. Do not call outside services from it.
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 faster | Why |
|---|---|
| Use built-in expressions only | Keeps the filter inside Photon; no Python UDFs, no calls to outside services |
| Use group checks such as is_account_group_member | They are constant for the whole query, so the engine evaluates them once, not per row |
| Keep any lookup table small | A 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 list | Simple predicates can also help data skipping |
| Cluster the table on the filter column | Skipping then removes whole files for each user's slice |
| Avoid non-deterministic functions | They block optimizations |
| Precompute for hot dashboards | A gold table or materialized view per audience avoids the filter on the heaviest queries (the security rule must still hold for the source) |
| Measure | Compare the query profile with and without the filter, and read EXPLAIN |
Related
Metric views, taught from zero includes row-level security on a semantic layer.
Practice
Questions to rehearse
- Implement row-level security and column masking. What are the performance and bypass risks?
- Dynamic views versus row filters: which would you choose and why?
- How would you give each tenant only their rows in a shared table?
- Why is a client-supplied tenant filter not safe?
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