Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 36 ยท Unity Catalog and Governance

Lineage, Row Filters and Column Masks

Protect sensitive data in Databricks: row filter and column mask functions, dynamic views, tags, data lineage, and audit through system tables, with runnable stand-ins.

Table-level grants are coarse: someone can see a table or not. Real data often needs finer rules. Sales managers should see only their region's rows. Analysts may see an email column only as a***@example.com. Unity Catalog supports this with row filters, column masks, dynamic views and tags, and it records lineage so you know where data came from and who uses it.

Databricks cannot run on this site. The SQL is read-only; the runnable blocks are plain Python stand-ins that implement the same filtering and masking ideas.

Row filters

A row filter is a SQL function that returns a boolean. Attach it to a table, and every query automatically keeps only the rows for which it returns true.

SQL
CREATE FUNCTION prod.sales.region_filter(region STRING)
RETURNS BOOLEAN
RETURN is_account_group_member('global-sales') OR region = 'EU';

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

Here members of global-sales see all rows and everyone else sees only EU. is_account_group_member() (and current_user()) evaluate for the person running the query. The filter is part of the table, so no query path can skip it. Remove it with ALTER TABLE ... DROP ROW FILTER.

Column masks

A column mask is a function that receives the column's value and returns what the viewer should see.

SQL
CREATE FUNCTION prod.sales.mask_email(email STRING)
RETURNS STRING
RETURN CASE
  WHEN is_account_group_member('pii-readers') THEN email
  ELSE concat(left(email, 1), '***@', split_part(email, '@', 2))
END;

ALTER TABLE prod.sales.customers
ALTER COLUMN email SET MASK prod.sales.mask_email;

Privileged users get the real value; others get a***@example.com. The result type must match the column type.

Python Stand-in: a row filter and a column mask

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Dynamic views

Before row filters and masks existed, teams built dynamic views: a view with CASE/WHERE using is_account_group_member(). They still work, and are handy when you want a restricted projection of a table rather than a rule on the table itself:

SQL
CREATE VIEW prod.sales.orders_public AS
SELECT order_id, region, amount,
       CASE WHEN is_account_group_member('finance') THEN customer_id END AS customer_id
FROM prod.sales.orders;

GRANT SELECT ON VIEW prod.sales.orders_public TO `analysts`;   -- but not on the base table

Analysts can read the view without any permission on the table underneath, because the view owner's rights are used. Prefer row filters and masks when the same rule should follow the table everywhere.

Tags and discovery

Tags are key/value labels on catalogs, schemas, tables and columns. Use them to mark sensitivity and ownership, then search by tag in Catalog Explorer or build policies on them.

SQL
ALTER TABLE prod.sales.customers SET TAGS ('domain' = 'sales', 'sensitivity' = 'confidential');
ALTER TABLE prod.sales.customers ALTER COLUMN email SET TAGS ('pii' = 'email');

Newer Databricks versions can also apply attribute-based access control policies that match tags, so "mask every column tagged pii" is one rule rather than dozens. Check your workspace's documentation for the current feature status.

Lineage

Unity Catalog records lineage automatically for queries and jobs that run on Unity Catalog compute: which upstream tables, files and notebooks produced a table, down to column level, and which dashboards, jobs and models consume it. You see it in Catalog Explorer's Lineage tab as a graph, and can query it with system tables such as system.access.table_lineage and system.access.column_lineage.

flowchart LR
    R["raw volume files"] --> B["bronze.orders"]
    B --> S["silver.orders"]
    S --> G["gold.daily_revenue"]
    G --> D["Dashboard"]
    G --> M["Model: demand_forecast"]

Lineage answers two questions: where did this come from, and what breaks if I change it?

Typical uses: impact analysis before changing a column, tracing a wrong dashboard number to its source, and showing auditors how a metric is built. Lineage is captured for reads and writes through Spark and SQL; data copied around by hand outside Databricks is invisible.

Python Stand-in: impact analysis over a lineage graph

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Audit

Alongside lineage, audit logs record who accessed what and which permissions changed. They are exposed as system tables (for example system.access.audit) that you can query, alert on and dashboard. Governance is the combination: grants decide who may, filters and masks decide what they see, lineage says where it came from, and audit says who did.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a row filter return?

    It is a SQL function returning true for rows the viewer may see.
  2. 2. Which function lets a mask decide based on group membership?

    is_account_group_member() tells whether the current user belongs to a group.
  3. 3. What does column-level lineage help with?

    It supports impact analysis and tracing wrong values.
  4. 4. Why can analysts read a dynamic view without access to the base table?

    That is how views expose a restricted projection safely.

Summary

  • Row filters and column masks are functions attached to tables, so they apply to every query.
  • Dynamic views expose restricted projections; tags label sensitive data.
  • Lineage (table and column level) and audit system tables show origin, impact and access.
  • Combine grants, filters, masks, lineage and audit for real governance.

Next: MLflow Tracking