Row-Level & Column-Level Security Across Warehouses: Snowflake, BigQuery, Databricks & Redshift

row-level security and column-level security are the two orthogonal controls that decide, for every query a warehouse ever runs, which rows a reader is allowed to see and which columns of those rows are shown in the clear — and getting them wrong is how a "read-only analyst" ends up staring at another region's revenue or a customer's raw social-security number. Role-based grants (RBAC) answer the coarse question "can this principal touch this table at all," but the moment two teams share one orders table, or one customers table holds both a marketing-safe email and a regulated national ID, coarse grants stop being enough. You need a filter that silently drops rows the reader shouldn't see, and a mask that replaces sensitive column values with *** unless the reader is entitled — enforced by the warehouse engine itself, not by a fragile "remember to add WHERE region = ..." convention in every downstream query.

This guide is the senior-data-engineering walkthrough for building both controls natively in the four warehouses you are most likely to be asked about — Snowflake, BigQuery, Databricks, and Redshift. It covers data warehouse security from the angle interviewers actually probe: where the reader's identity comes from (CURRENT_ROLE, SESSION_USER, group membership), where the policy attaches (table, column, view, or catalog), when to choose dynamic data masking over a hard column access control boundary, and how to share one policy across dozens of tables without copy-pasting predicates. Each warehouse gets its native primitives — the Snowflake row access policy and masking policy, BigQuery authorized views plus row access policies and policy-tag column masking, Databricks Unity Catalog row filters and column masks, and Redshift RLS plus column GRANT and dynamic data masking — and each section pairs a teaching block with a Solution-Tail interview answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse the modelling reps on the design practice library →, and stress-test the schema fundamentals on the database practice library →.

---

On this page

• Why row-level and column-level security decide who sees what
• Snowflake — row access policies and dynamic data masking
• BigQuery — authorized views, row access, and column security
• Databricks Unity Catalog — row filters and column masks
• Redshift — RLS policies, column GRANT, and data masking
• Cheat sheet — RLS and CLS recipes across the four warehouses
• Frequently asked questions
• Practice on PipeCode

---

1. Why row-level and column-level security decide who sees what

Two orthogonal axes on top of RBAC — the choice binds every downstream query

The one-sentence invariant: row-level security decides which rows a reader is allowed to see and column-level security decides which columns are shown in the clear, both layered on top of role-based access control, and both enforced by the warehouse engine so that a single physical table can safely serve readers with wildly different entitlements without any query author remembering to add a filter. RBAC is the gate at the front door — "can this role open the customers table at all." Row-level and column-level security are the two independent dials behind that door: one prunes rows, the other redacts columns, and they compose. A regional analyst can be granted the customers table (RBAC), see only their region's rows (row-level security), and see a masked national_id (column-level security) — all three controls active on one SELECT *.

The four questions every warehouse security design must answer.

• Identity source. Whose entitlement drives the policy? Warehouses expose the caller's identity as CURRENT_ROLE() / CURRENT_USER() (Snowflake), SESSION_USER() (BigQuery), current_user() / is_account_group_member() (Databricks), or CURRENT_USER / current_setting() session context (Redshift). The policy predicate reads that identity and decides. Interviewers open here because it separates people who've shipped policies from people who've only read about them.
• Policy attachment point. Where does the rule live? A row policy attaches to a table (Snowflake, BigQuery, Databricks, Redshift all support this) or is expressed as an authorized view (BigQuery's portable pattern). A column control is either a masking policy on the column (dynamic — the column still exists but returns redacted values) or a hard grant boundary (the column is simply not selectable). Naming the attachment point is what makes an answer concrete.
• Masking vs filtering. Row-level security removes rows; column-level security either masks values in place or removes the column from the grant. Masking keeps the schema stable (downstream SELECT email still runs, it just returns ***); a hard column grant breaks the query for unentitled readers. Choosing the right one per column is a design decision, not a default.
• Maintenance cost. Can one policy cover N tables, or must you author a predicate per table? Can you unit-test the policy? What does it cost the query planner? The senior answer always ends on operability: a mapping table so entitlements live in data not DDL, a policy reused across tables, and a test harness that asserts "role X sees exactly these rows."

The 2026 reality — native RLS and column masking everywhere, authorized views as the portable fallback.

• Snowflake ships first-class ROW ACCESS POLICY objects and MASKING POLICY objects (dynamic data masking), both attachable to many tables, both driven by CURRENT_ROLE() and optional mapping tables.
• BigQuery offers authorized views (the decade-old portable pattern), native row access policies (CREATE ROW ACCESS POLICY ... FILTER USING), and column-level security through policy tags in a Data Catalog taxonomy, with optional dynamic data masking rules on those tags.
• Databricks Unity Catalog implements row-level and column-level security as SQL UDFs: a boolean-returning function becomes a ROW FILTER, and a value-returning function becomes a column MASK, both bound with ALTER TABLE.
• Redshift provides native RLS POLICY objects attached to roles/users, classic column-level GRANT (GRANT SELECT(col1, col2)), and dynamic data masking policies attached per column per role.
• Authorized views / secure views remain the lowest-common-denominator portable pattern when a native policy isn't available or when you must support an older engine — a view encodes the filter and the column projection, and readers are granted the view, never the base table.

What interviewers listen for.

• Do you say "row-level security prunes rows, column-level security redacts columns, both on top of RBAC" in the first sentence? — required framing.
• Do you name a mapping table so entitlements live in data, not in a predicate you re-edit per hire? — senior signal.
• Do you distinguish dynamic data masking (column still selectable, returns redacted) from a hard column grant (column not selectable)? — senior signal.
• Do you mention testing the policy — "assert role X sees exactly these rows" — rather than eyeballing it? — senior signal.
• Do you flag the shared-policy question — "one policy object attached to fifty tables" — instead of copy-pasting predicates? — senior signal.

Worked example — the row-versus-column decision grid

Detailed explanation. The single most useful artifact for a warehouse-security interview is a grid that, for a given column, tells you which control applies. Row-level security is a predicate over the row — region, tenant, owner. Column-level security is a classification of the column — public, internal, restricted PII. Walk through building the grid for a customers table that must serve a regional analyst, a marketing user, and a compliance auditor.

• The table. customers (id, tenant_id, region, name, email, phone, national_id, ltv_cents).
• The readers. Regional analyst (their region only, no raw PII), marketing (all regions, hashed email OK, no national ID), compliance auditor (all rows, all columns in the clear).
• The two axes. Rows are pruned by region/tenant_id; columns are classified public (name), internal (ltv_cents), pii (email, phone), restricted (national_id).

Question. Build the control grid: for each reader, state the row predicate and the per-column visibility.

Input.

Reader	Row predicate	name	email	phone	national_id	ltv_cents
Regional analyst	region = my region	clear	masked	masked	masked	clear
Marketing	all rows	clear	hashed	masked	not granted	clear
Compliance auditor	all rows	clear	clear	clear	clear	clear

Code.

-- The physical table serves all three readers unchanged
CREATE TABLE analytics.customers (
    id           BIGINT       NOT NULL,
    tenant_id    BIGINT       NOT NULL,
    region       VARCHAR      NOT NULL,   -- drives row-level security
    name         VARCHAR,                 -- classification: public
    email        VARCHAR,                 -- classification: pii
    phone        VARCHAR,                 -- classification: pii
    national_id  VARCHAR,                 -- classification: restricted
    ltv_cents    BIGINT                   -- classification: internal
);

-- Two independent controls will attach to this one table:
--   1. a ROW policy keyed on region  (row-level security)
--   2. a MASK/CLASSIFY control per PII column (column-level security)
-- RBAC still gates who can open the table at all.

Step-by-step explanation.

1. The physical table is authored once and never duplicated per reader. This is the whole point of engine-enforced security: one table, many entitlements. The alternative — a per-region table or a per-role view maintained by hand — is the anti-pattern that native RLS/CLS exists to kill.
2. Row-level security is a predicate over a row attribute (region, tenant_id). It answers "which rows." It is orthogonal to any column classification: the regional analyst and marketing user both hit the same row policy engine, they just resolve to different predicates.
3. Column-level security is a classification of a column (email is PII, national_id is restricted). It answers "which columns, and shown how." A column can be masked (returned as *** or a hash) or not granted at all (the query fails or the column is absent).
4. The two axes compose independently — that is why they are drawn as a grid, not a list. The regional analyst gets region = mine (row) and masked PII (column). Marketing gets all rows (row) and hashed email (column). Neither control knows about the other.
5. RBAC sits underneath both: if a role was never granted SELECT on analytics.customers, the row and column policies never even run. Always name all three layers — grant, row policy, column policy — when you describe the design.

Output.

Control layer	Question it answers	Mechanism
RBAC (grant)	Can the role open the table?	GRANT SELECT ON table TO role
Row-level security	Which rows are returned?	policy predicate over region/tenant
Column-level security	Which columns are in the clear?	masking policy or column-scoped grant

Rule of thumb. Draw the grid before you write any DDL: readers down the side, columns across the top, the row predicate in the first column. Every cell is either "clear," "masked," or "not granted." The policies fall straight out of the grid.

Worked example — the mapping-table pattern (entitlements as data)

Detailed explanation. The rookie mistake is to hard-code entitlements into the policy predicate: WHERE region = 'EMEA' AND CURRENT_ROLE() = 'EMEA_ANALYST'. That forces a DDL change and a policy redeploy every time someone changes region or a new region launches. The senior pattern stores entitlements in a mapping table and the policy joins against it — so onboarding a user is an INSERT, not an ALTER POLICY. Walk through the mapping table that every warehouse's RLS design should use.

• The mapping table. entitlements (role_name, region) — one row per (role, region) the role may see.
• The policy. The row predicate becomes EXISTS (SELECT 1 FROM entitlements WHERE role_name = CURRENT_ROLE() AND region = customers.region).
• The payoff. Onboarding = INSERT INTO entitlements. Offboarding = DELETE. No DDL, no redeploy.

Question. Design the mapping table and the generic predicate so a new region or a new analyst is a data change, not a schema change.

Input.

entitlements row	role_name	region
1	EMEA_ANALYST	EMEA
2	AMER_ANALYST	AMER
3	GLOBAL_ANALYST	EMEA
4	GLOBAL_ANALYST	AMER
5	GLOBAL_ANALYST	APAC

Code.

-- Entitlements live in DATA, not in policy DDL
CREATE TABLE governance.entitlements (
    role_name  VARCHAR NOT NULL,
    region     VARCHAR NOT NULL,
    PRIMARY KEY (role_name, region)
);

INSERT INTO governance.entitlements VALUES
    ('EMEA_ANALYST',   'EMEA'),
    ('AMER_ANALYST',   'AMER'),
    ('GLOBAL_ANALYST', 'EMEA'),
    ('GLOBAL_ANALYST', 'AMER'),
    ('GLOBAL_ANALYST', 'APAC');

-- The row predicate every warehouse's RLS will reuse:
--   "the current role is entitled to this row's region"
-- EXISTS ( SELECT 1
--          FROM governance.entitlements e
--          WHERE e.role_name = CURRENT_ROLE()
--            AND e.region    = <row>.region )

Step-by-step explanation.

1. The mapping table has one row per (role, region) pair the role is allowed to see. GLOBAL_ANALYST has three rows because it sees three regions; EMEA_ANALYST has one. The composite primary key prevents duplicate entitlements.
2. The policy predicate never names a specific region or role. It joins the caller's identity (CURRENT_ROLE()) against the mapping table and keeps the row only if a matching entitlement exists. This single predicate covers every current and future region.
3. Onboarding a new analyst for APAC is INSERT INTO governance.entitlements VALUES ('APAC_ANALYST', 'APAC') plus the RBAC grant — no policy edit. Launching a brand-new region needs zero policy changes: the moment an entitlement row exists, the predicate lets it through.
4. The mapping table itself must be locked down: only a governance role can write it, and analysts cannot read it (or can only read their own rows). Otherwise a reader could grant themselves a region. This is the one table whose own access control matters most.
5. This pattern is warehouse-agnostic — Snowflake row access policies, Databricks row-filter UDFs, and Redshift RLS policies all support a subquery/join against a mapping table, and BigQuery encodes the same idea with group-based FILTER USING predicates. Learn it once, apply it four times.

Output.

Caller (CURRENT_ROLE)	Rows kept	Why
EMEA_ANALYST	region = EMEA	one entitlement row matches
AMER_ANALYST	region = AMER	one entitlement row matches
GLOBAL_ANALYST	EMEA + AMER + APAC	three entitlement rows match
UNMAPPED_ROLE	(none)	no entitlement row; predicate false for all

Rule of thumb. Never hard-code a region, tenant, or user into a policy predicate. Put entitlements in a locked-down mapping table and have the policy join against CURRENT_ROLE(). Onboarding becomes an INSERT; the default for an unmapped role is zero rows, which is the safe default.

Worked example — what interviewers actually probe

Detailed explanation. The senior warehouse-security interview has a predictable arc: an ambiguous opener ("we have one orders table and three teams — how do you keep each team to its own data?"), then progressive narrowing to test whether you know the axes and the operability story. Candidates who name row-level and column-level security as distinct controls and reach for a mapping table score highest; candidates who say "we'd make a view per team" score lowest. Walk through the grading rubric.

• Ambiguous opener. "How do you keep three teams to their own rows in one table?" — invites RLS + mapping table.
• Follow-up 1. "Now the table has a national ID column — who sees it?" — probes column-level security / masking.
• Follow-up 2. "A new team launches next week — what changes?" — probes maintenance cost / mapping table.
• Follow-up 3. "How do you prove the policy is correct?" — probes policy testing.
• Follow-up 4. "Same design on Snowflake and BigQuery — what differs?" — probes portability.

Question. Draft a five-minute senior answer that covers identity, attachment, masking-vs-filtering, and operability without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Row control	"a view per team"	"one row access policy joined to a mapping table"
Column control	"drop the column"	"dynamic data masking on the PII column; hard grant on restricted"
New team	"add another view"	"INSERT a mapping row; zero DDL"
Correctness	"we test it manually"	"assert-based tests: role X sees exactly N rows"
Portability	"same everywhere"	"Snowflake policy object vs BigQuery authorized view vs Databricks UDF"

Code.

Senior warehouse-security answer template (5 minutes)
=====================================================

Minute 1 — name the two axes up front
  "Two independent controls on top of RBAC: row-level security
   prunes rows, column-level security redacts columns. One physical
   table serves every team."

Minute 2 — identity + mapping table
  "The policy reads the caller identity (CURRENT_ROLE / SESSION_USER /
   group membership) and joins a locked-down entitlements mapping
   table. Onboarding is an INSERT, not a DDL change. Unmapped role =
   zero rows, the safe default."

Minute 3 — column control
  "For PII I use dynamic data masking: the email column stays
   selectable but returns *** unless the role is entitled. For
   regulated fields like national_id I prefer a hard column grant so
   the column isn't even in the reader's projection."

Minute 4 — testing
  "I ship assert tests: connect as role X, run SELECT, assert the row
   count and that masked columns are redacted. The policy is code; it
   gets a test."

Minute 5 — portability
  "Snowflake: ROW ACCESS POLICY + MASKING POLICY objects. BigQuery:
   authorized views or row access policies + policy-tag CLS.
   Databricks: row-filter and column-mask UDFs in Unity Catalog.
   Redshift: RLS POLICY + column GRANT + DDM. Same design, four
   dialects."

Step-by-step explanation.

1. Minute 1 is the framing that scores. Naming two independent controls on top of RBAC immediately signals you understand the axes; weak candidates conflate "security" into one bucket or reach for hand-maintained views.
2. Minute 2 addresses identity and the mapping table before the interviewer asks about onboarding. Saying "unmapped role = zero rows" shows you default to deny — the correct security posture.
3. Minute 3 splits column control into masking (schema-stable, column still selectable) and hard grant (column absent). Choosing per column, and justifying it, is the senior signal the interviewer is listening for.
4. Minute 4 treats the policy as code with tests. "We eyeball it" is the answer that loses offers; "role X sees exactly N rows, asserted in CI" is the answer that wins them.
5. Minute 5 shows portability fluency — the same conceptual design expressed in four dialects. This is exactly what a multi-cloud shop needs, and it's the differentiator between a one-warehouse engineer and a platform owner.

Output.

Grading criterion	Weak score	Senior score
Names both axes in minute 1	rare	mandatory
Reaches for a mapping table	rare	required
Splits masking vs hard grant	occasional	senior signal
Tests the policy	rare	senior signal
Speaks all four dialects	rare	senior signal

Rule of thumb. The senior warehouse-security answer is a five-minute monologue: two axes on top of RBAC, identity plus mapping table, masking vs hard grant, tested in CI, portable across four dialects. Rehearse it once; deploy it every interview.

Senior interview question on warehouse security design

A senior interviewer often opens with: "You inherit one customers table shared by a regional analytics team, a marketing team, and a compliance team. Regional analysts must see only their region and never raw PII; marketing sees all regions but only a hashed email and no national ID; compliance sees everything. Design the row-level and column-level controls so the single physical table serves all three, onboarding a new region is a data change, and the policy is testable."

Solution Using layered RBAC + a mapping-table row policy + tiered column masking

-- 1. RBAC — the coarse gate (who can open the table at all)
GRANT SELECT ON analytics.customers TO ROLE regional_analyst;
GRANT SELECT ON analytics.customers TO ROLE marketing;
GRANT SELECT ON analytics.customers TO ROLE compliance;

-- 2. Entitlements as data (row-level security source of truth)
CREATE TABLE governance.entitlements (
    role_name VARCHAR NOT NULL,
    region    VARCHAR NOT NULL,
    PRIMARY KEY (role_name, region)
);
-- regional_analyst is region-scoped; marketing + compliance see all regions
INSERT INTO governance.entitlements VALUES
    ('REGIONAL_ANALYST', 'EMEA');   -- one row per analyst-region grant

-- 3. Row-level predicate (pseudocode; each warehouse expresses it natively)
--    keep the row IF:
--      caller is marketing/compliance  (all regions)  OR
--      an entitlement maps caller -> this row's region
-- EXISTS ( SELECT 1 FROM governance.entitlements e
--          WHERE e.role_name = CURRENT_ROLE()
--            AND e.region    = customers.region )
--   OR CURRENT_ROLE() IN ('MARKETING','COMPLIANCE')

-- 4. Column-level tiers
--    national_id : hard column grant  -> only compliance has SELECT on it
--    email       : dynamic mask       -> clear for compliance, hash for
--                                        marketing, *** for regional

-- 5. Policy test harness (assert, do not eyeball)
--    Run as each role; assert row count and redaction.
-- as REGIONAL_ANALYST:
SELECT count(*) AS rows_seen,
       count(*) FILTER (WHERE region <> 'EMEA') AS leaks,   -- must be 0
       count(*) FILTER (WHERE email NOT LIKE '%*%')  AS pii_leaks  -- must be 0
FROM analytics.customers;
-- as COMPLIANCE:
SELECT count(*) AS rows_seen,          -- must equal full table count
       count(*) FILTER (WHERE email LIKE '%*%') AS over_masked  -- must be 0
FROM analytics.customers;

Step-by-step trace.

Reader	RBAC	Row policy result	national_id	email
REGIONAL_ANALYST (EMEA)	granted	region = EMEA only	not granted	*** masked
MARKETING	granted	all regions	not granted	hashed
COMPLIANCE	granted	all regions	clear	clear
UNMAPPED_ROLE	not granted	(never runs)	—	—

After deployment, the regional analyst's SELECT * FROM analytics.customers silently returns only EMEA rows with a masked email and no national ID column in scope; marketing sees every region with a hashed email; compliance sees the full table in the clear. Onboarding an APAC analyst is INSERT INTO governance.entitlements VALUES ('APAC_ANALYST','APAC') plus one grant — no policy DDL. The test harness runs in CI and fails the build if any role's row count or redaction drifts.

Output:

Metric	Before (per-team views)	After (policies + mapping table)
Physical copies of the data	one view per team	one table
Onboard a new region	new view + grants	one INSERT
PII exposure	manual column lists per view	tiered mask + hard grant
Correctness check	eyeball each view	asserted in CI
Blast radius of a mistake	one view, silent	one policy, tested

Why this works — concept by concept:

• RBAC as the gate — the coarse GRANT SELECT decides who can open the table at all; the row and column policies only run for principals who cleared this gate. Three layers, evaluated outermost-first.
• Mapping-table row policy — entitlements live in governance.entitlements, so the predicate joins CURRENT_ROLE() against data. Onboarding is an INSERT; an unmapped role resolves to zero rows, which is deny-by-default.
• Tiered column control — national_id uses a hard column grant (absent from the reader's projection unless compliance), while email uses dynamic data masking (column stays selectable, value redacted). Matching the mechanism to the column's sensitivity is the design skill.
• Assert-based policy tests — the policy is code, so it gets tests: connect as each role, assert row count and redaction. leaks = 0 and pii_leaks = 0 are invariants the CI enforces, not hopes.
• Cost — one mapping-table join per query (indexed, negligible for small entitlement sets), one mask function per protected column (O(rows scanned) but cheap), and zero data duplication. Compared to per-team views the maintenance cost drops from O(teams × tables) hand-authored objects to O(1) policy objects plus O(entitlements) data rows.

SQL
Topic — sql
SQL GRANT, filtering, and access-control problems

Practice →

Design Topic — design Design problems on multi-tenant access control

Practice →

---

2. Snowflake — row access policies and dynamic data masking

Snowflake row access policy objects plus masking policies give you reusable, CURRENT_ROLE-driven RLS and column masking on one table

The mental model in one line: Snowflake implements row-level security as a ROW ACCESS POLICY object — a named boolean expression over a row's columns and CURRENT_ROLE() — attached to a table with ALTER TABLE ... ADD ROW ACCESS POLICY ON (col), and column-level security as a MASKING POLICY object attached with ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY, and because both are standalone schema objects, one policy can protect dozens of tables and a single mapping table can drive every entitlement decision. Every Snowflake data engineer eventually owns both objects; knowing that they are reusable objects rather than per-table WHERE clauses is the difference between a scalable governance layer and a copy-paste sprawl.

The four axes for Snowflake RLS + masking.

• Identity source. CURRENT_ROLE() (the active primary role) or CURRENT_USER(), and for multi-role logic IS_ROLE_IN_SESSION() (checks secondary roles too). Most designs key on CURRENT_ROLE() and join a mapping table.
• Attachment point. A ROW ACCESS POLICY attaches to a table (or view) ON (columns) — the listed columns are passed as arguments to the policy body. A MASKING POLICY attaches to a specific column with SET MASKING POLICY, and a conditional masking policy can read other columns of the same row.
• Masking vs filtering. The row policy returns BOOLEAN — TRUE keeps the row. The masking policy returns the column's type — the original value or a redacted value. They are separate objects and can coexist on one table.
• Reuse + cost. One policy object attaches to N tables. The row policy is evaluated as an extra predicate (Snowflake pushes it into the scan and can prune partitions when the predicate is selective); masking is applied at projection time. A mapping-table subquery is cached per query.

Row access policy anatomy.

• Signature. CREATE ROW ACCESS POLICY p AS (region VARCHAR) RETURNS BOOLEAN -> <expression>. The argument names must match the columns you bind in ON (...).
• Body. Any boolean expression: CURRENT_ROLE() = 'ADMIN' OR EXISTS (SELECT 1 FROM map WHERE ...). Subqueries against mapping tables are allowed and idiomatic.
• Bind. ALTER TABLE t ADD ROW ACCESS POLICY p ON (region). One table can have at most one row access policy; that policy can reference several columns.
• Admin bypass. Give privileged roles an early OR CURRENT_ROLE() IN ('ACCOUNTADMIN', 'GOVERNANCE') so break-glass access is explicit.

Masking policy anatomy.

• Signature. CREATE MASKING POLICY m AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN ... THEN val ELSE '***MASKED***' END. The argument type must match the column type.
• Conditional masking. A masking policy body can reference other columns of the row via extra arguments (a "conditional masking policy") — e.g. mask email only when is_marketable = FALSE.
• Bind. ALTER TABLE t MODIFY COLUMN email SET MASKING POLICY m. One masking policy per column; one policy object reusable across many columns/tables of the same type.
• Tokenization / hashing. The ELSE branch can hash (SHA2(val)), partially reveal (REGEXP_REPLACE), or fully redact — the choice encodes the column's sensitivity tier.

Common interview probes on Snowflake.

• "How do you scope rows by role?" — ROW ACCESS POLICY returning boolean, bound ON (region), joined to a mapping table.
• "How do you hide a PII column but keep the schema stable?" — MASKING POLICY returning the column type; unentitled roles get ***.
• "Can one policy protect many tables?" — yes; policies are schema objects, attached with ALTER TABLE to each.
• "How do you test it?" — USE ROLE per role, SELECT, assert counts and redaction; policies show up in POLICY_REFERENCES.

Worked example — a regional row access policy driven by a mapping table

Detailed explanation. The canonical Snowflake RLS setup: an entitlements mapping table, a ROW ACCESS POLICY that keeps a row when the caller's role is entitled to that row's region (with an admin bypass), and the ALTER TABLE that binds it. Build the whole thing.

• Mapping table. governance.entitlements (role_name, region).
• Policy. rap_region — boolean over region, joined to the mapping table, admin bypass for GOVERNANCE.
• Bind. ALTER TABLE analytics.customers ADD ROW ACCESS POLICY rap_region ON (region).

Question. Write the mapping table, the row access policy, and the binding, then show what each role sees.

Input.

Object	Purpose
governance.entitlements	(role_name, region) entitlement rows
rap_region	ROW ACCESS POLICY returning BOOLEAN
ADD ROW ACCESS POLICY ON (region)	binds policy to customers

Code.

-- 1. Entitlements mapping table (locked down; only GOVERNANCE writes it)
CREATE TABLE governance.entitlements (
    role_name VARCHAR NOT NULL,
    region    VARCHAR NOT NULL
);
INSERT INTO governance.entitlements VALUES
    ('EMEA_ANALYST', 'EMEA'),
    ('AMER_ANALYST', 'AMER');

-- 2. Row access policy — one object, reusable across tables that have a region column
CREATE ROW ACCESS POLICY governance.rap_region
AS (region VARCHAR) RETURNS BOOLEAN ->
    -- break-glass / governance sees everything
    CURRENT_ROLE() IN ('ACCOUNTADMIN', 'GOVERNANCE')
    -- otherwise the caller's role must be entitled to this row's region
    OR EXISTS (
        SELECT 1
        FROM governance.entitlements e
        WHERE e.role_name = CURRENT_ROLE()
          AND e.region    = region        -- policy arg = row's region column
    );

-- 3. Bind the policy to the table (at most one row access policy per table)
ALTER TABLE analytics.customers
    ADD ROW ACCESS POLICY governance.rap_region ON (region);

Step-by-step explanation.

1. The entitlements mapping table holds one row per (role, region) grant. It is written only by the GOVERNANCE role; analysts have no access to it, so no one can self-grant a region. This is the source of truth for row-level security.
2. CREATE ROW ACCESS POLICY ... AS (region VARCHAR) RETURNS BOOLEAN declares a reusable object. The argument region is bound at attach time to the table's region column; the same policy attaches to any table that has a region column.
3. The body first grants break-glass access to ACCOUNTADMIN and GOVERNANCE — always make privileged bypass explicit rather than accidental. Then it evaluates the EXISTS subquery: keep the row only if the caller's CURRENT_ROLE() is entitled to this row's region.
4. ALTER TABLE ... ADD ROW ACCESS POLICY rap_region ON (region) binds the policy. Snowflake now injects the predicate into every scan of analytics.customers. Because the predicate references region, Snowflake can prune micro-partitions whose region range can't match — RLS that is also a performance win when the predicate is selective.
5. A role with no entitlement row and not in the bypass list resolves the body to FALSE for every row — it sees an empty result set, never an error. Deny-by-default is the built-in behaviour.

Output.

Session role	Rows returned from analytics.customers
EMEA_ANALYST	only rows where region = 'EMEA'
AMER_ANALYST	only rows where region = 'AMER'
GOVERNANCE	all rows (bypass)
REPORT_VIEWER (unmapped)	0 rows

Rule of thumb. Author the row access policy as a standalone object with an explicit admin bypass and a mapping-table EXISTS, then bind it to every region-partitioned table with ADD ROW ACCESS POLICY ON (region). One object, many tables, entitlements in data.

Worked example — tiered dynamic data masking on a PII column

Detailed explanation. Column-level security in Snowflake is a MASKING POLICY returning the column's own type. A single policy can encode tiers — clear for compliance, hashed for marketing, fully redacted for everyone else — by branching on CURRENT_ROLE(). Build a tiered email-masking policy and attach it.

• Tiers. COMPLIANCE → clear; MARKETING → SHA2 hash; everyone else → ***MASKED***.
• Policy. mask_email — VARCHAR -> VARCHAR, branching on role.
• Bind. ALTER TABLE analytics.customers MODIFY COLUMN email SET MASKING POLICY mask_email.

Question. Write a tiered masking policy for email and show the value each role sees.

Input.

Role	Desired email visibility
COMPLIANCE	clear ([e-mail])
MARKETING	SHA2 hash
REGIONAL_ANALYST	MASKED

Code.

-- Tiered dynamic data masking policy for an email column
CREATE MASKING POLICY governance.mask_email
AS (val VARCHAR) RETURNS VARCHAR ->
    CASE
        WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN', 'COMPLIANCE')
            THEN val                              -- full clear
        WHEN CURRENT_ROLE() = 'MARKETING'
            THEN SHA2(val, 256)                   -- pseudonymised, join-stable
        ELSE '***MASKED***'                       -- fully redacted default
    END;

-- Attach to the column (schema stays stable; column is still selectable)
ALTER TABLE analytics.customers
    MODIFY COLUMN email SET MASKING POLICY governance.mask_email;

-- The same policy object can protect any VARCHAR email column elsewhere:
-- ALTER TABLE marketing.leads MODIFY COLUMN email SET MASKING POLICY governance.mask_email;

Step-by-step explanation.

1. CREATE MASKING POLICY ... AS (val VARCHAR) RETURNS VARCHAR declares a policy whose argument type (VARCHAR) matches the column type. Snowflake passes each email value in as val and substitutes whatever the body returns.
2. The CASE encodes three sensitivity tiers keyed on CURRENT_ROLE(). Compliance and account admin get the raw value; marketing gets a stable SHA2 hash (same input always hashes the same, so marketing can still join on email without ever seeing it); everyone else gets a constant redaction.
3. ALTER TABLE ... MODIFY COLUMN email SET MASKING POLICY binds the policy to the column. Crucially the column stays in the schema and stays selectable — downstream SELECT email FROM customers keeps running for every role; only the value changes. This is what "dynamic" means and why masking beats dropping the column when you need schema stability.
4. Masking composes with the row access policy from the previous example: a regional analyst first has rows pruned to their region, then sees ***MASKED*** in the surviving rows' email. The two policies are independent objects evaluated together.
5. Because the policy is an object, the same mask_email attaches to every email column in the account (marketing.leads, crm.contacts, …). Change the redaction once, and every attached column updates — governance at O(1).

Output.

Session role	email value returned
COMPLIANCE	[e-mail]
MARKETING	2f9a...c1 (SHA2 hash)
REGIONAL_ANALYST	MASKED
ACCOUNTADMIN	[e-mail]

Rule of thumb. Encode masking tiers in one policy with a CASE on CURRENT_ROLE() — clear, hashed, redacted — so the same object serves compliance, marketing, and analysts. Use a stable hash (not a random token) when a downstream team must still join on the masked column.

Senior interview question on Snowflake row and column security

A senior interviewer might ask: "On Snowflake you have a multi-tenant orders table shared by dozens of customer tenants and internal analysts. Each tenant's app-role must see only its own tenant_id; internal analysts see all tenants but with the customer email masked; a governance role sees everything. Design the row access policy, the masking policy, the mapping-table-driven entitlements, and a test that proves a tenant cannot see another tenant's rows."

Solution Using a mapping-table row access policy + tiered masking policy + policy tests

-- 1. Tenant entitlement mapping (locked down)
CREATE TABLE governance.tenant_map (
    role_name VARCHAR NOT NULL,
    tenant_id NUMBER  NOT NULL
);
INSERT INTO governance.tenant_map VALUES
    ('TENANT_ACME_ROLE',  1001),
    ('TENANT_GLOBEX_ROLE',1002);

-- 2. Row access policy — tenant isolation + internal/governance bypass
CREATE ROW ACCESS POLICY governance.rap_tenant
AS (tenant_id NUMBER) RETURNS BOOLEAN ->
    CURRENT_ROLE() IN ('ACCOUNTADMIN', 'GOVERNANCE', 'INTERNAL_ANALYST')
    OR EXISTS (
        SELECT 1 FROM governance.tenant_map m
        WHERE m.role_name = CURRENT_ROLE()
          AND m.tenant_id = tenant_id
    );

ALTER TABLE analytics.orders
    ADD ROW ACCESS POLICY governance.rap_tenant ON (tenant_id);

-- 3. Masking policy — internal analysts see masked email; governance sees clear
CREATE MASKING POLICY governance.mask_email
AS (val VARCHAR) RETURNS VARCHAR ->
    CASE
        WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','GOVERNANCE') THEN val
        ELSE REGEXP_REPLACE(val, '.+@', '****@')   -- keep domain, hide local part
    END;

ALTER TABLE analytics.orders
    MODIFY COLUMN customer_email SET MASKING POLICY governance.mask_email;

-- 4. Policy test — prove tenant isolation (run in CI as each role)
USE ROLE TENANT_ACME_ROLE;
SELECT
    count(*)                                        AS rows_seen,
    count(*) FILTER (WHERE tenant_id <> 1001)       AS cross_tenant_leaks,  -- must be 0
    count(*) FILTER (WHERE customer_email LIKE '%@%'
                       AND customer_email NOT LIKE '%****@%') AS pii_leaks  -- must be 0
FROM analytics.orders;

USE ROLE GOVERNANCE;
SELECT count(*) AS rows_seen FROM analytics.orders;   -- must equal full count

-- 5. Inspect what policies are attached (governance audit)
SELECT * FROM TABLE(
    information_schema.policy_references(
        ref_entity_name => 'analytics.orders',
        ref_entity_domain => 'table'));

Step-by-step trace.

Layer	Object	Effect
RBAC	GRANT SELECT ON orders	who can open the table
Mapping	governance.tenant_map	(role → tenant_id) as data
Row policy	rap_tenant ON (tenant_id)	tenant isolation + bypass list
Masking	mask_email ON customer_email	analysts see ****@domain
Test	count + FILTER assertions	cross_tenant_leaks = 0 in CI

After deployment, TENANT_ACME_ROLE running SELECT * FROM analytics.orders sees only tenant_id = 1001 rows; INTERNAL_ANALYST sees every tenant but with customer_email reduced to ****@domain.com; GOVERNANCE sees everything in the clear. Onboarding a new tenant is one INSERT into tenant_map. The CI test asserts cross_tenant_leaks = 0 and fails the pipeline the instant a policy regression would leak a tenant.

Output:

Role	rows_seen	customer_email	cross_tenant_leaks
TENANT_ACME_ROLE	ACME rows only	****@domain	0
TENANT_GLOBEX_ROLE	GLOBEX rows only	****@domain	0
INTERNAL_ANALYST	all tenants	****@domain	0
GOVERNANCE	all tenants	clear	0

Why this works — concept by concept:

• ROW ACCESS POLICY object — a reusable boolean over tenant_id and CURRENT_ROLE(). Bound with ADD ROW ACCESS POLICY ON (tenant_id), Snowflake injects it into every scan and prunes micro-partitions when the tenant predicate is selective, so isolation is also a performance win.
• Mapping table (tenant_map) — entitlements as data. Onboarding a tenant is an INSERT; an unmapped role resolves the EXISTS to false and sees zero rows — deny-by-default without any DDL.
• Explicit bypass list — CURRENT_ROLE() IN ('ACCOUNTADMIN','GOVERNANCE','INTERNAL_ANALYST') makes privileged access a visible clause, not an accident. Break-glass is auditable.
• Dynamic data masking with partial reveal — REGEXP_REPLACE(val,'.+@','****@') keeps the email domain (useful for analytics) while hiding the identifiable local part. The column stays selectable, so downstream queries never break.
• Assert-based policy tests + POLICY_REFERENCES — cross_tenant_leaks = 0 is an invariant enforced in CI, and information_schema.policy_references lets governance audit exactly which policies protect which columns. The policy is code; it is tested and inventoried.
• Cost — one indexed mapping-table lookup per query plus one regex per projected email. Row pruning keeps scans O(tenant rows), not O(all tenants). Governance is O(1) policy objects reused across every tenant-partitioned table rather than O(tenants) hand-built views.

SQL
Topic — sql
SQL masking, CASE, and access-policy problems

Practice →

Filtering Topic — filtering Filtering problems on predicate-driven row access

Practice →

---

3. BigQuery — authorized views, row access, and column security

BigQuery authorized views broker access, native row access policies filter rows, and policy-tag column-level security gates columns

The mental model in one line: BigQuery gives you three composable controls — an authorized view that runs on behalf of its owner so readers touch the view without any grant on the base table, a native row access policy (CREATE ROW ACCESS POLICY ... FILTER USING (predicate)) that prunes rows per group, and column-level security via policy tags in a Data Catalog taxonomy (optionally with dynamic data masking rules), so restricted columns are gated by classification rather than by editing every query — and the senior skill is knowing when the portable authorized-view pattern is enough versus when native row access policies and policy tags are worth the extra governance surface. Every BigQuery platform owner has shipped authorized views; policy tags and row access policies are the newer, more granular layer.

The four axes for BigQuery.

• Identity source. SESSION_USER() returns the caller's email; group membership is checked implicitly by granting a row access policy TO GROUP. Policy tags gate columns by IAM role (Data Catalog Fine-Grained Reader) on the tag.
• Attachment point. Authorized views attach at the dataset level (the view's dataset is authorized to read the source dataset). Row access policies attach to a table. Column-level security attaches a policy tag to a column; the tag lives in a taxonomy.
• Masking vs filtering. Row access policies filter rows (FILTER USING). Policy tags by default block the column (unentitled readers can't select it); with data-masking rules, a tag can instead mask the column value (e.g. hash, nullify, default).
• Reuse + cost. One taxonomy of policy tags classifies columns across many tables. Row access policies are per-table but their predicates can reference SESSION_USER() and lookup tables. Authorized views centralise both filter and projection in one view definition.

Authorized views — the portable broker.

• The idea. Readers are granted SELECT on a view in a curated dataset but have no access to the base dataset. The view's dataset is added as an authorized view on the source dataset, so the view reads on the owner's behalf.
• What it encodes. The view's SELECT list is the column projection (drop national_id and you've done column security); the view's WHERE is the row filter (WHERE region = ... or WHERE email = SESSION_USER()).
• Why it's portable. No taxonomy, no policy objects — just a view and a dataset authorization. Works on every BigQuery project and mirrors the "secure view" pattern on other warehouses.
• Its limit. Filtering logic lives in the view SQL; many views means many WHERE clauses to maintain. Native row access policies exist to lift that logic off the view.

Native row access policies.

• Signature. CREATE ROW ACCESS POLICY p ON dataset.table GRANT TO ('group:[e-mail]') FILTER USING (region = 'EMEA').
• Semantics. A reader sees the union of rows permitted by the policies that grant to a group they belong to. No matching policy → no rows (deny-by-default).
• Identity. GRANT TO lists IAM principals (users, groups, domains); FILTER USING can also reference SESSION_USER() for per-user row ownership.

Column-level security via policy tags.

• Taxonomy. A Data Catalog taxonomy holds a hierarchy of policy tags (e.g. PII > email, PII > national_id).
• Attach. ALTER TABLE ... ALTER COLUMN national_id SET OPTIONS (policy_tags = ['projects/.../policyTags/123']). The column is now gated: only principals with Fine-Grained Reader on that tag can select it.
• Masking. A data policy can attach a masking rule (hash, nullify, default) to a tag, so instead of the query failing, unentitled readers get a masked value.

Common interview probes on BigQuery.

• "How do you give a team a filtered slice without granting the base table?" — authorized view in a curated dataset.
• "What's the native row-level control?" — CREATE ROW ACCESS POLICY ... GRANT TO ... FILTER USING.
• "How do you protect one PII column across many tables?" — policy tag from a taxonomy, granted per Fine-Grained Reader.
• "Column blocked vs masked?" — policy tag alone blocks the column; a data-masking rule on the tag masks it instead.

Worked example — an authorized view that filters rows and drops a column

Detailed explanation. The portable BigQuery pattern: a curated reporting dataset holds a view over raw.customers; readers get SELECT on the view only. The view's WHERE enforces per-user row ownership via SESSION_USER(), and its SELECT list simply omits national_id. Build it and authorize it.

• Base. raw.customers — readers have no access.
• View. reporting.customers_secure — filters WHERE sales_rep_email = SESSION_USER(), omits national_id.
• Authorize. Add reporting as an authorized view on raw.

Question. Write the view and the dataset authorization so a sales rep sees only their own accounts and never the national ID.

Input.

Component	Value
Base table	raw.customers
Secure view	reporting.customers_secure
Row filter	sales_rep_email = SESSION_USER()
Dropped column	national_id (column security by omission)

Code.

-- 1. The curated view: row filter in WHERE, column security by projection
CREATE OR REPLACE VIEW reporting.customers_secure AS
SELECT
    id,
    region,
    name,
    email,
    ltv_cents
    -- national_id is intentionally NOT selected -> column-level security
FROM raw.customers
WHERE sales_rep_email = SESSION_USER();   -- per-user row-level security

-- 2. Grant readers the VIEW only (never the base table)
GRANT `roles/bigquery.dataViewer`
  ON TABLE reporting.customers_secure
  TO 'group:[e-mail]';

-- 3. Authorize the view's dataset to read the source dataset
--    (Console / API: add reporting.customers_secure as an
--     "Authorized view" on the raw dataset). Terraform equivalent:
resource "google_bigquery_dataset_access" "authorize_view" {
  dataset_id = "raw"
  view {
    project_id = "my-proj"
    dataset_id = "reporting"
    table_id   = "customers_secure"
  }
}

Step-by-step explanation.

1. The view is the entire security boundary. Its SELECT list omits national_id, so no reader of the view can ever project that column — column-level security by projection, no taxonomy required. Its WHERE sales_rep_email = SESSION_USER() enforces per-user row ownership.
2. SESSION_USER() resolves to the caller's email at query time. Rep [e-mail] sees only rows whose sales_rep_email is [e-mail]; the same view serves every rep with no per-rep configuration.
3. Readers are granted dataViewer on the view, not the base raw.customers. Without the next step this would fail, because the view would try to read a dataset the reader can't access.
4. Adding reporting.customers_secure as an authorized view on the raw dataset lets the view read raw.customers on the owner's behalf — the reader never needs (and never gets) direct access to the base data. This is the broker that makes the pattern safe.
5. The pattern is fully portable: it's just a view plus a dataset authorization. No policy objects, no taxonomy, no IAM tag roles. The cost is that filter and projection logic live in the view SQL, so many secure views mean many definitions to maintain — which is exactly the pain native row access policies and policy tags relieve.

Output.

Caller (SESSION_USER)	Rows from customers_secure	national_id
[e-mail]	rows where sales_rep_email = [e-mail]	absent (not in view)
[e-mail]	rows where sales_rep_email = [e-mail]	absent
unauthorized user	permission denied on the view	—

Rule of thumb. For a portable, no-taxonomy control, encode the row filter in the view's WHERE (use SESSION_USER() for per-user ownership) and the column security in the view's SELECT list, grant readers the view only, and authorize the view on the source dataset. Reach for native policies when the number of views becomes the maintenance cost.

Worked example — native row access policy plus policy-tag column security

Detailed explanation. The native, more granular BigQuery pattern: keep readers on the base table but attach a row access policy per region-group and a policy tag on the PII column. This lifts the filter off a view and classifies the column centrally. Build both.

• Row access policy. emea_filter on analytics.customers, GRANT TO 'group:[e-mail]' FILTER USING (region = 'EMEA').
• Policy tag. A PII taxonomy with an email tag; attach it to the email column.
• Entitlement. Only Fine-Grained Reader on the email tag can select the column.

Question. Create a region row access policy and a policy-tag column control on the base table.

Input.

Control	Mechanism
Row filter (EMEA)	ROW ACCESS POLICY GRANT TO group FILTER USING
Column gate (email)	policy tag on column + Fine-Grained Reader IAM
Default for column	blocked unless entitled (or masked via data policy)

Code.

-- 1. Row access policy: EMEA group sees only EMEA rows
CREATE ROW ACCESS POLICY emea_filter
ON analytics.customers
GRANT TO ('group:[e-mail]')
FILTER USING (region = 'EMEA');

-- A second policy for AMER; a reader sees the UNION of rows their groups allow
CREATE ROW ACCESS POLICY amer_filter
ON analytics.customers
GRANT TO ('group:[e-mail]')
FILTER USING (region = 'AMER');

-- 2. Column-level security: tag the email column with a taxonomy policy tag
ALTER TABLE analytics.customers
ALTER COLUMN email
SET OPTIONS (
    policy_tags = ['projects/my-proj/locations/us/taxonomies/987/policyTags/123']
);
-- Only principals granted roles/datacatalog.categoryFineGrainedReader
-- on policyTag 123 can SELECT email; everyone else is blocked on that column.

-- 3. Optional: a data-masking rule on the tag masks instead of blocking
--    (so a SELECT * still succeeds, email returns a hash)
--    Configured as a Data Policy on policyTag 123 with routine HASH.
--    Readers with the Masked Reader role then see SHA256(email).

Step-by-step explanation.

1. CREATE ROW ACCESS POLICY emea_filter ON analytics.customers GRANT TO (...) FILTER USING (region = 'EMEA') attaches a row filter directly to the base table. Members of [e-mail] now see only EMEA rows on any query — no view needed.
2. Row access policies are additive per group: a reader who belongs to both EMEA and AMER groups sees the union (EMEA ∪ AMER rows). A reader in no granted group sees zero rows — deny-by-default. This union semantics is a common interview gotcha.
3. ALTER COLUMN email SET OPTIONS (policy_tags = [...]) classifies the column with a taxonomy tag. BigQuery now enforces that only principals holding Fine-Grained Reader on that specific tag can select email; a SELECT email by anyone else fails with a policy-tag error, and SELECT * excludes it.
4. One taxonomy classifies columns across the whole project: the same PII/email tag can protect analytics.customers.email, crm.leads.email, and so on. Entitlement is managed by granting the tag's reader role to a group — centrally, once.
5. Attaching a data-masking rule (a Data Policy) to the tag changes the behaviour from block to mask: unentitled-but-masked-reader principals get SELECT * to succeed with email returned as a hash or null. Choose block (hard boundary) vs mask (schema-stable) per column sensitivity — the same decision as Snowflake's hard grant vs masking policy.

Output.

Principal	Rows seen	email column
emea-analysts group	region = EMEA	blocked (no tag reader)
amer-analysts group	region = AMER	blocked
pii-readers (tag reader)	per their row groups	clear
masked-readers (data policy)	per their row groups	SHA256 hash

Rule of thumb. Use native row access policies (GRANT TO group FILTER USING) to lift row filters off views, and policy tags from one taxonomy to classify PII columns across many tables. Remember row access policies grant the union across a reader's groups, and no matching policy means zero rows.

Senior interview question on BigQuery row and column security

A senior interviewer might ask: "On BigQuery, a customers table must serve regional analyst groups (each sees only their region), a data-science group (all regions but the email and national_id columns masked, not blocked, so their feature pipelines don't break), and a governance group (everything clear). Design it with native row access policies and policy tags, explain the union semantics, and describe how a masked column keeps the data-science pipeline running."

Solution Using region row access policies + a PII taxonomy with data-masking rules

-- 1. Region row access policies (one per region group; union semantics)
CREATE ROW ACCESS POLICY emea_rap ON analytics.customers
  GRANT TO ('group:[e-mail]', 'group:[e-mail]',
            'group:[e-mail]')
  FILTER USING (region = 'EMEA');

CREATE ROW ACCESS POLICY amer_rap ON analytics.customers
  GRANT TO ('group:[e-mail]', 'group:[e-mail]',
            'group:[e-mail]')
  FILTER USING (region = 'AMER');
-- data-science + governance are granted on BOTH policies -> they see the
-- union (all regions); each regional group is granted on one -> its region only.

-- 2. PII taxonomy: tag email and national_id
ALTER TABLE analytics.customers ALTER COLUMN email
  SET OPTIONS (policy_tags = ['projects/my-proj/locations/us/taxonomies/987/policyTags/EMAIL']);
ALTER TABLE analytics.customers ALTER COLUMN national_id
  SET OPTIONS (policy_tags = ['projects/my-proj/locations/us/taxonomies/987/policyTags/NATID']);

-- 3. Data policies (masking rules) so data-science gets MASKED, not BLOCKED
--    Tag EMAIL  -> Data Policy "email_hash"  routine = SHA256
--    Tag NATID  -> Data Policy "natid_null"  routine = ALWAYS_NULL
--
-- IAM grants:
--   group:governance    -> Fine-Grained Reader  on EMAIL, NATID  (clear)
--   group:data-science   -> Masked Reader         on EMAIL, NATID  (masked)
--   group:*-analysts     -> (neither)                              (blocked)

-- 4. Policy test — data science pipeline must still run (masked, not error)
--    Run as a data-science principal:
SELECT region,
       email,          -- returns SHA256 hash, query does NOT fail
       national_id     -- returns NULL, query does NOT fail
FROM analytics.customers
LIMIT 10;
-- assert: query succeeds, email matches ^[0-9a-f]{64}$, national_id IS NULL

Step-by-step trace.

Group	Row policies granted	Rows seen	email	national_id
emea-analysts	emea_rap	EMEA only	blocked	blocked
amer-analysts	amer_rap	AMER only	blocked	blocked
data-science	emea_rap + amer_rap	all regions	SHA256 (masked)	NULL (masked)
governance	emea_rap + amer_rap	all regions	clear	clear

After deployment, each regional analyst group is granted exactly one region policy and sees only that region; data-science and governance are granted on both region policies and therefore see the union of all regions. On the column axis, data-science holds the Masked Reader role on the EMAIL and NATID tags, so SELECT email, national_id succeeds but returns a hash and NULL — the feature pipeline keeps running without a permission error, which is the whole reason to mask rather than block. Governance holds Fine-Grained Reader and sees the raw values.

Output:

Principal	query succeeds?	email	national_id
data-science	yes	64-hex hash	NULL
governance	yes	clear	clear
emea-analysts (SELECT email)	no (blocked)	error	error

Why this works — concept by concept:

• Row access policy union semantics — a reader sees the union of rows across every policy that grants their groups. Granting data-science and governance on both region policies gives them all regions; granting each regional group on one policy scopes it — no explicit "all regions" predicate needed.
• Deny-by-default rows — a principal in no granted group matches no policy and sees zero rows. The safe default is built into BigQuery's row-access model.
• Policy tags from one taxonomy — EMAIL and NATID tags classify columns centrally; the same tags can protect the same columns across every table in the project. Column entitlement is an IAM grant on the tag, not a per-table edit.
• Masked Reader vs Fine-Grained Reader — the data-masking rule on the tag turns blocked into masked. Data-science gets SELECT * to succeed with hashed email and NULL national_id, so downstream feature pipelines never hit a permission error; governance's Fine-Grained Reader sees the raw value. Block vs mask is chosen per column.
• Cost — row access policies are pushed into the scan (BigQuery prunes by the FILTER USING predicate), and masking is applied at projection. One taxonomy is O(1) governance for N tables; the alternative — a secure view per group per region — is O(groups × regions) view definitions to maintain.

SQL
Topic — sql
SQL view, projection, and access-control problems

Practice →

Design Topic — design Design problems on taxonomy-driven data governance

Practice →

---

4. Databricks Unity Catalog — row filters and column masks

Databricks Unity Catalog expresses row-level security and column-level security as reusable SQL UDFs bound with ALTER TABLE

The mental model in one line: Databricks Unity Catalog implements row-level security as a row filter — a SQL UDF returning BOOLEAN bound with ALTER TABLE ... SET ROW FILTER f ON (col) — and column-level security as a column mask — a SQL UDF returning the column's type bound with ALTER TABLE ... ALTER COLUMN c SET MASK m — with identity resolved by current_user() and is_account_group_member(), so the same governance function protects every table it is attached to across the catalog. Every Databricks platform owner eventually writes both UDFs; the insight is that the function is the reusable governance object and ALTER TABLE is merely the binding.

The four axes for Databricks Unity Catalog.

• Identity source. current_user() returns the caller's email; is_account_group_member('group') returns a boolean for group membership; is_member('group') checks workspace groups. Filter and mask UDFs branch on these.
• Attachment point. A row filter UDF binds to a table with SET ROW FILTER func ON (columns) — the listed columns are passed as arguments. A column mask UDF binds to a column with ALTER COLUMN col SET MASK func, and the mask function receives the column value (plus optional other columns) as arguments.
• Masking vs filtering. The row-filter UDF returns BOOLEAN — TRUE keeps the row. The mask UDF returns the column's type — original or redacted value. Both are ordinary SQL UDFs governed by Unity Catalog privileges.
• Reuse + cost. One UDF governs many tables. A UDF may JOIN/EXISTS against a mapping table for entitlements. Filters and masks are evaluated by Photon during the scan/projection; a mapping-table lookup is broadcast-joined when small.

Row filter UDF anatomy.

• Signature. CREATE FUNCTION governance.rf_region(region STRING) RETURNS BOOLEAN RETURN <bool expr>. The parameters map positionally to the columns in ON (...).
• Body. is_account_group_member('governance') OR EXISTS (SELECT 1 FROM map WHERE ...) — group bypass plus a mapping-table join.
• Bind. ALTER TABLE t SET ROW FILTER governance.rf_region ON (region). One row filter per table; it can take multiple columns.
• Grant. Readers need EXECUTE on the function (usually granted to the whole account) and SELECT on the table; the filter runs with the definer's rights.

Column mask UDF anatomy.

• Signature. CREATE FUNCTION governance.mask_email(email STRING) RETURNS STRING RETURN CASE WHEN ... THEN email ELSE '***' END.
• Extra columns. A mask function may accept additional columns of the row (USING COLUMNS (other_col)) to make the masking conditional on another value.
• Bind. ALTER TABLE t ALTER COLUMN email SET MASK governance.mask_email. One mask per column; one function reusable across columns/tables of the same type.
• Semantics. The column stays selectable; only the returned value changes — schema-stable dynamic masking, exactly like Snowflake's masking policy.

Common interview probes on Databricks.

• "How do you do RLS in Unity Catalog?" — a boolean row-filter UDF bound with SET ROW FILTER ... ON (col).
• "How do you mask a column?" — a mask UDF bound with ALTER COLUMN ... SET MASK.
• "Where does identity come from?" — current_user() and is_account_group_member().
• "Can one function cover many tables?" — yes; the UDF is the governance object, ALTER TABLE is the binding.

Worked example — a group-based row filter UDF over a mapping table

Detailed explanation. The canonical Unity Catalog RLS setup: a mapping table of (group, region) entitlements, a boolean row-filter UDF that keeps a row when the caller is in a group entitled to that region (with a governance bypass), and the ALTER TABLE that binds it. Build it.

• Mapping table. governance.group_region (group_name, region).
• UDF. governance.rf_region(region STRING) RETURNS BOOLEAN.
• Bind. ALTER TABLE main.analytics.customers SET ROW FILTER governance.rf_region ON (region).

Question. Write the mapping table, the row-filter UDF, and the binding, then show what each group sees.

Input.

Object	Purpose
governance.group_region	(group_name, region) entitlements
rf_region	BOOLEAN row-filter UDF
SET ROW FILTER ON (region)	binds the filter to customers

Code.

-- 1. Entitlements mapping table (governed; only data stewards can write it)
CREATE TABLE governance.group_region (
    group_name STRING NOT NULL,
    region     STRING NOT NULL
);
INSERT INTO governance.group_region VALUES
    ('emea_analysts', 'EMEA'),
    ('amer_analysts', 'AMER');

-- 2. Row-filter UDF — returns TRUE to KEEP the row
CREATE OR REPLACE FUNCTION governance.rf_region(region STRING)
RETURNS BOOLEAN
RETURN
    -- governance group bypasses the filter
    is_account_group_member('governance')
    -- otherwise the caller must belong to a group entitled to this region
    OR EXISTS (
        SELECT 1
        FROM governance.group_region g
        WHERE g.region = region
          AND is_account_group_member(g.group_name)
    );

-- 3. Bind the filter to the table (columns map to UDF params positionally)
ALTER TABLE main.analytics.customers
    SET ROW FILTER governance.rf_region ON (region);

Step-by-step explanation.

1. group_region stores entitlements as data — one row per (group, region). Data stewards write it; analysts cannot. This is the row-level source of truth, mirroring the mapping-table pattern used on every other warehouse.
2. CREATE FUNCTION rf_region(region STRING) RETURNS BOOLEAN is a reusable governance object. The parameter region will be bound to the table's region column at attach time; the same function attaches to any table with a region column.
3. The body bypasses the filter for the governance group, then evaluates an EXISTS that keeps the row only if the caller is_account_group_member of some group entitled to this row's region. is_account_group_member resolves against the caller's identity, so one function serves everyone.
4. ALTER TABLE ... SET ROW FILTER rf_region ON (region) binds it. Unity Catalog now applies the filter to every read of customers, from SQL warehouses, notebooks, and jobs alike — the enforcement is on the table, not the query engine, so it can't be bypassed by choosing a different client.
5. A caller in no entitled group and not in governance makes the body FALSE for every row and sees an empty result — deny-by-default, no error. The filter runs with definer's rights, so readers don't need direct access to the group_region table.

Output.

Caller's group	Rows returned from customers
emea_analysts	region = EMEA only
amer_analysts	region = AMER only
governance	all rows (bypass)
no entitled group	0 rows

Rule of thumb. Write the row-filter as a boolean UDF with a group bypass and a mapping-table EXISTS keyed on is_account_group_member, then bind it with SET ROW FILTER ON (region). The UDF is the reusable governance object; attach it to every region table.

Worked example — a conditional column mask UDF

Detailed explanation. Column-level security in Unity Catalog is a mask UDF returning the column's type. Using USING COLUMNS, the mask can be conditional on another column of the same row — e.g. reveal email only when the customer opted in and the caller is entitled. Build a conditional mask.

• Mask. governance.mask_email(email STRING, is_marketable BOOLEAN) RETURNS STRING.
• Rule. Governance/marketing see the email when is_marketable is true; everyone else sees ***.
• Bind. ALTER TABLE ... ALTER COLUMN email SET MASK governance.mask_email USING COLUMNS (is_marketable).

Question. Write a conditional email mask and show what each caller sees for a marketable vs non-marketable row.

Input.

Caller / row	is_marketable = TRUE	is_marketable = FALSE
governance	clear	clear
marketing	clear	***
analyst	***	***

Code.

-- Conditional column mask: reveal email only to entitled callers on opted-in rows
CREATE OR REPLACE FUNCTION governance.mask_email(email STRING, is_marketable BOOLEAN)
RETURNS STRING
RETURN
    CASE
        WHEN is_account_group_member('governance')          THEN email
        WHEN is_account_group_member('marketing')
             AND is_marketable                               THEN email
        ELSE '***'
    END;

-- Bind the mask to the email column, passing is_marketable as an extra input
ALTER TABLE main.analytics.customers
    ALTER COLUMN email
    SET MASK governance.mask_email USING COLUMNS (is_marketable);

Step-by-step explanation.

1. The mask UDF returns STRING, matching the email column type. Unity Catalog passes the column value in as the first argument and — because of USING COLUMNS (is_marketable) — the row's is_marketable flag as the second, letting the mask branch on another column.
2. The CASE encodes the policy: governance always sees the raw email; marketing sees it only on rows the customer marked marketable; everyone else gets ***. Consent (is_marketable) and entitlement (is_account_group_member) are both required for marketing to see the value.
3. SET MASK ... USING COLUMNS (is_marketable) binds it. The email column stays selectable — SELECT email FROM customers runs for every caller — so downstream jobs and dashboards never break; only the value returned changes. This is dynamic masking, not a dropped column.
4. The mask composes with the row filter from the previous example: a caller first has rows pruned by rf_region, then sees masked or clear email in the surviving rows. The two UDFs are independent governance objects.
5. Because the mask is a UDF, the same mask_email attaches to every email column in the catalog; and because it can read USING COLUMNS, one function handles both unconditional and consent-conditional masking. Governance changes in one place propagate to every attached column.

Output.

Caller	row is_marketable	email returned
governance	true or false	clear
marketing	true	clear
marketing	false	***
analyst	true or false	***

Rule of thumb. Make masks conditional with USING COLUMNS when a second column (consent, sensitivity flag) should gate visibility. The masked column stays selectable, so pipelines keep running; only entitled callers on qualifying rows see the clear value.

Senior interview question on Databricks Unity Catalog security

A senior interviewer might ask: "In Unity Catalog you must protect a transactions table used by regional finance groups (each sees only its region), a fraud-analytics group (all regions but the card_number masked to last four digits), and a governance group (everything clear). Build the row filter and column mask as reusable UDFs over a mapping table, explain definer's rights, and describe how you'd audit which tables the filter is attached to."

Solution Using reusable row-filter + column-mask UDFs governed centrally

-- 1. Region entitlement mapping (data stewards write it)
CREATE TABLE governance.group_region (
    group_name STRING NOT NULL,
    region     STRING NOT NULL
);
INSERT INTO governance.group_region VALUES
    ('finance_emea', 'EMEA'),
    ('finance_amer', 'AMER');

-- 2. Row filter: region isolation + fraud/governance bypass to all regions
CREATE OR REPLACE FUNCTION governance.rf_txn_region(region STRING)
RETURNS BOOLEAN
RETURN
    is_account_group_member('governance')
    OR is_account_group_member('fraud_analytics')   -- sees all regions
    OR EXISTS (
        SELECT 1 FROM governance.group_region g
        WHERE g.region = region
          AND is_account_group_member(g.group_name)
    );

ALTER TABLE main.finance.transactions
    SET ROW FILTER governance.rf_txn_region ON (region);

-- 3. Column mask: governance sees full PAN; everyone else sees last 4
CREATE OR REPLACE FUNCTION governance.mask_pan(card_number STRING)
RETURNS STRING
RETURN
    CASE
        WHEN is_account_group_member('governance') THEN card_number
        ELSE concat('************', right(card_number, 4))
    END;

ALTER TABLE main.finance.transactions
    ALTER COLUMN card_number SET MASK governance.mask_pan;

-- 4. Audit — which tables reference the filter/mask, and test as each group
SELECT * FROM system.information_schema.row_filters
WHERE  filter_name = 'rf_txn_region';           -- attachment inventory

SELECT * FROM system.information_schema.column_masks
WHERE  mask_name = 'mask_pan';

-- Run as finance_emea and assert isolation + masking:
SELECT count(*)                                  AS rows_seen,
       count(*) FILTER (WHERE region <> 'EMEA')  AS region_leaks,      -- must be 0
       count(*) FILTER (WHERE card_number NOT LIKE '************%') AS pan_leaks  -- 0
FROM   main.finance.transactions;

Step-by-step trace.

Layer	Object	Effect
Mapping	governance.group_region	(group → region) as data
Row filter	rf_txn_region ON (region)	region isolation + fraud/gov bypass
Column mask	mask_pan ON card_number	last-4 for all but governance
Definer's rights	UDF runs as its owner	readers need no access to mapping table
Audit	information_schema.row_filters / column_masks	attachment inventory

After deployment, finance_emea sees only EMEA transactions with card_number reduced to ************1234; fraud_analytics sees every region (bypass) but still only the last four digits; governance sees all regions and full card numbers. The filter and mask run with definer's rights, so readers never need SELECT on group_region. Governance queries information_schema.row_filters / column_masks to inventory exactly which tables each UDF protects, and the assert query fails CI if a region or PAN ever leaks.

Output:

Group	rows_seen	card_number	region_leaks
finance_emea	EMEA only	************1234	0
finance_amer	AMER only	************1234	0
fraud_analytics	all regions	************1234	0
governance	all regions	full PAN	0

Why this works — concept by concept:

• Row-filter UDF (RETURNS BOOLEAN) — bound with SET ROW FILTER ON (region), the function is enforced by Unity Catalog on the table itself, so every client (SQL warehouse, notebook, job) gets the same isolation; there is no query path that skips it.
• is_account_group_member bypass + mapping-table EXISTS — group membership drives the decision; the fraud_analytics and governance bypasses are explicit clauses, and the EXISTS scopes regional groups from data, so onboarding is an INSERT.
• Definer's rights — the UDF executes as its owner, so readers need only EXECUTE on the function and SELECT on the table — never access to the sensitive group_region mapping table. Least privilege is preserved.
• Column mask (schema-stable) — mask_pan returns STRING, so card_number stays selectable and fraud pipelines keep running while seeing only ************1234. Governance sees the full PAN via the explicit branch.
• information_schema inventory — row_filters and column_masks give governance a queryable catalog of every attachment, so "which tables does this policy protect?" is a SQL query, not tribal knowledge; the assert test keeps region_leaks and pan_leaks at zero in CI.
• Cost — the filter is a broadcast join against a tiny mapping table plus a boolean per row; the mask is a concat/right per projected value. One UDF is O(1) governance for N tables, versus O(tables) hand-maintained secure views.

SQL
Topic — sql
SQL UDF, CASE, and masking problems

Practice →

Data Transformation Topic — data-transformation Data transformation problems on governed pipelines

Practice →

---

5. Redshift — RLS policies, column GRANT, and data masking

Redshift RLS policies attach to roles, column-level GRANT is least-privilege by column, and dynamic data masking redacts values per role

The mental model in one line: Amazon Redshift implements row-level security as RLS POLICY objects attached to roles/users on a table (ALTER TABLE ... ROW LEVEL SECURITY ON), column-level security two ways — a static GRANT SELECT(col1, col2) that omits sensitive columns from a role's projection, and dynamic data masking policies (CREATE MASKING POLICY + ATTACH MASKING POLICY ON table(col) TO ROLE) that redact values per role with an ordered priority — and identity is the session's roles plus optional current_setting() session context. Every Redshift platform owner combines all three; the nuance interviewers love is that column GRANT and dynamic data masking are different controls that solve different problems.

The four axes for Redshift.

• Identity source. The session's granted roles (RLS POLICY ... TO ROLE), CURRENT_USER, and session context set at connect time via SET SESSION / current_setting('app.region') for app-driven filters.
• Attachment point. An RLS POLICY is created once, then attached to a table for one or more roles; row-level security is toggled per table with ALTER TABLE ... ROW LEVEL SECURITY ON. Column controls attach either as a scoped GRANT SELECT(cols) or as a MASKING POLICY attached ON table(col) TO ROLE.
• Masking vs filtering. The RLS policy filters rows via a USING predicate. Column GRANT removes a column from a role's selectable set (hard boundary). Dynamic data masking keeps the column but redacts its value (schema-stable), with a priority that resolves conflicts when multiple policies match.
• Reuse + cost. One RLS policy attaches to many tables/roles. Masking policies attach per column per role. Redshift combines all RLS policies on a table for a user with AND (intersection) by default — a stricter default than BigQuery's union.

RLS policy anatomy.

• Create. CREATE RLS POLICY p WITH (region VARCHAR(16)) USING (region = current_setting('app.region', TRUE)) — or join a lookup table.
• Attach. ATTACH RLS POLICY p ON analytics.customers TO ROLE analyst; and enable with ALTER TABLE analytics.customers ROW LEVEL SECURITY ON;.
• Combination. If several policies are attached for a user, Redshift applies them combined with AND (all must pass) unless CONJUNCTION TYPE OR is set — the opposite default to BigQuery.
• Bypass. A role with the IGNORE RLS system privilege (e.g. sys:secadmin / superuser) bypasses RLS — the break-glass path.

Column-level security — two distinct tools.

• Static column GRANT. GRANT SELECT(id, region, name) ON analytics.customers TO ROLE analyst; — the analyst literally cannot select national_id; a SELECT * errors or excludes it. A hard boundary, no value returned.
• Dynamic data masking. CREATE MASKING POLICY mask_ssn WITH (ssn VARCHAR(11)) USING ('XXX-XX-' || right(ssn,4)); then ATTACH MASKING POLICY mask_ssn ON analytics.customers(national_id) TO ROLE analyst; — the column is still selectable but returns redacted values.
• Priority. When multiple masking policies could apply, ATTACH ... PRIORITY n resolves which wins; a TO ROLE PUBLIC default policy plus higher-priority per-role overrides is the idiom.

Common interview probes on Redshift.

• "How do you filter rows by role?" — CREATE RLS POLICY ... USING, ATTACH ... TO ROLE, ROW LEVEL SECURITY ON.
• "Two ways to protect a column?" — static GRANT SELECT(cols) (hard) vs dynamic data masking (redact).
• "How do multiple RLS policies combine?" — AND by default (intersection), unlike BigQuery's union.
• "How do you bypass for break-glass?" — a role with IGNORE RLS / superuser.

Worked example — an RLS policy attached to a role with session context

Detailed explanation. The canonical Redshift RLS setup: a policy whose predicate reads a lookup table (or session context), attached to the analyst role, with row-level security enabled on the table. Build both the lookup-driven and session-context variants.

• Lookup variant. RLS POLICY joins governance.role_region on CURRENT_USER/role.
• Attach. ATTACH RLS POLICY ... ON customers TO ROLE analyst.
• Enable. ALTER TABLE customers ROW LEVEL SECURITY ON.

Question. Create an RLS policy that keeps only the caller's entitled regions and attach it to the analyst role.

Input.

Object	Purpose
governance.role_region	(role_name, region) entitlements
rls_region	RLS POLICY with a region predicate
ATTACH ... TO ROLE analyst	binds policy to role on customers

Code.

-- 1. Entitlements lookup table
CREATE TABLE governance.role_region (
    role_name VARCHAR(64) NOT NULL,
    region    VARCHAR(16) NOT NULL
);
INSERT INTO governance.role_region VALUES
    ('emea_analyst', 'EMEA'),
    ('amer_analyst', 'AMER');

-- 2. RLS policy: keep the row if some role the caller has is entitled to it
CREATE RLS POLICY rls_region
WITH (region VARCHAR(16))                 -- column the policy inspects
USING (
    region IN (
        SELECT rr.region
        FROM   governance.role_region rr
        WHERE  pg_has_role(CURRENT_USER, rr.role_name, 'MEMBER')
    )
);

-- 3. Attach the policy to the analyst roles and enable RLS on the table
ATTACH RLS POLICY rls_region ON analytics.customers TO ROLE emea_analyst;
ATTACH RLS POLICY rls_region ON analytics.customers TO ROLE amer_analyst;
ALTER TABLE analytics.customers ROW LEVEL SECURITY ON;

Step-by-step explanation.

1. role_region holds entitlements as data — one row per (role, region). This is the same mapping-table pattern used on every warehouse; only the identity function differs.
2. CREATE RLS POLICY rls_region WITH (region VARCHAR(16)) USING (...) declares a reusable policy object. The WITH clause names the column the policy inspects; the USING predicate returns the rows to keep. pg_has_role(CURRENT_USER, rr.role_name, 'MEMBER') is true when the caller holds that role, so the subquery yields exactly the regions the caller is entitled to.
3. ATTACH RLS POLICY ... ON analytics.customers TO ROLE emea_analyst binds the policy for that role on that table; ROW LEVEL SECURITY ON activates enforcement. Until the table is toggled on, attaching a policy has no effect — a common gotcha.
4. Redshift applies the predicate to every scan for the attached roles. A superuser or a role with IGNORE RLS bypasses it (break-glass). A role that has no attached policy on an RLS-enabled table sees no rows by default — deny-by-default.
5. If you attach multiple policies to a user, Redshift combines them with AND (intersection) unless you set CONJUNCTION TYPE OR — the opposite of BigQuery's union default. Know this: it changes what a multi-role user sees.

Output.

Caller's role	Rows returned from customers
emea_analyst	region = EMEA
amer_analyst	region = AMER
superuser / IGNORE RLS	all rows (bypass)
role with no attached policy	0 rows

Rule of thumb. Create the RLS policy once with a lookup-table USING predicate keyed on pg_has_role(CURRENT_USER, ...), attach it per role, and remember to flip ROW LEVEL SECURITY ON. Multiple attached policies combine with AND by default — set CONJUNCTION TYPE OR if you want union semantics.

Worked example — column GRANT versus dynamic data masking

Detailed explanation. Redshift's two column controls solve different problems. A static GRANT SELECT(cols) gives a role a hard boundary — the omitted column is not selectable at all. A dynamic data masking policy keeps the column selectable but redacts the value, with a priority to resolve overlaps. Build both for the same table and contrast them.

• Hard boundary. GRANT SELECT(id, region, name, ltv_cents) to analyst — national_id omitted.
• Masking. A PUBLIC default policy fully redacts email; a higher-priority marketing policy partially reveals it.
• Contrast. Column GRANT breaks SELECT national_id; masking keeps SELECT email working with a redacted value.

Question. Apply a hard column GRANT to national_id and tiered dynamic data masking to email, and show what the analyst and marketing roles see.

Input.

Column	Control	analyst	marketing	governance
national_id	column GRANT	not selectable	not selectable	selectable
email	dynamic masking	fully masked	partial	clear

Code.

-- 1. Hard column boundary: analyst can select these columns, NOT national_id
GRANT SELECT(id, region, name, email, ltv_cents)
    ON analytics.customers TO ROLE analyst;
-- national_id is simply absent from the grant -> SELECT national_id fails for analyst.
-- governance keeps full column access:
GRANT SELECT ON analytics.customers TO ROLE governance;

-- 2. Dynamic data masking on email — a PUBLIC default plus role overrides
CREATE MASKING POLICY mask_email_full
    WITH (email VARCHAR(320)) USING ('***MASKED***');
CREATE MASKING POLICY mask_email_partial
    WITH (email VARCHAR(320)) USING (regexp_replace(email, '.+@', '****@'));

-- Default: everyone gets fully masked (lowest priority)
ATTACH MASKING POLICY mask_email_full
    ON analytics.customers(email) TO PUBLIC PRIORITY 0;
-- Marketing overrides with partial reveal (higher priority wins)
ATTACH MASKING POLICY mask_email_partial
    ON analytics.customers(email) TO ROLE marketing PRIORITY 10;
-- Governance sees clear: attach an identity policy at highest priority
CREATE MASKING POLICY mask_email_clear
    WITH (email VARCHAR(320)) USING (email);
ATTACH MASKING POLICY mask_email_clear
    ON analytics.customers(email) TO ROLE governance PRIORITY 20;

Step-by-step explanation.

1. GRANT SELECT(id, region, name, email, ltv_cents) lists exactly the columns the analyst may project. national_id is not in the list, so SELECT national_id FROM customers fails for the analyst and SELECT * returns only the granted columns. This is a hard boundary — no value, redacted or otherwise, is ever returned.
2. Dynamic data masking is the opposite tool: the column stays selectable and a policy rewrites its value. Two policies are defined — a full redaction and a partial reveal — plus a clear identity policy for governance.
3. ATTACH MASKING POLICY ... TO PUBLIC PRIORITY 0 sets the safe default: everyone sees ***MASKED*** for email unless a higher-priority policy overrides. Defaulting the whole PUBLIC group to fully masked is deny-by-default for column values.
4. Higher-priority attachments win: marketing's PRIORITY 10 partial-reveal policy overrides the PRIORITY 0 default for marketing sessions; governance's PRIORITY 20 clear policy overrides both for governance. Priority is how Redshift resolves the "which masking policy applies?" conflict deterministically.
5. Choose per column: use a hard GRANT boundary for columns a role should never touch (regulated identifiers like national_id), and dynamic masking for columns a role must still query (join keys, analytics fields) but not read in the clear. Mixing both on one table is the normal, expected design.

Output.

Role	national_id	email
analyst	not selectable (grant error)	MASKED (PUBLIC default)
marketing	not selectable	****@domain.com (priority 10)
governance	clear	clear (priority 20)

Rule of thumb. Use GRANT SELECT(cols) when a role should never even reference a column, and dynamic data masking when the column must stay selectable but redacted. Attach a fully-masked PUBLIC default at priority 0, then layer higher-priority per-role overrides — priority makes the resolution deterministic.

Senior interview question on Redshift row and column security

A senior interviewer might ask: "On Redshift a patients table serves regional care teams (each sees only its clinic's region), a research role (all regions but ssn masked to last four and mrn fully masked), and a compliance role (everything clear). Design the RLS policy, the column controls, explain how multiple RLS policies combine, and how masking priority resolves the research-versus-default conflict."

Solution Using an RLS policy + column GRANT + prioritized dynamic data masking

-- 1. Region entitlements + RLS policy
CREATE TABLE governance.role_region (role_name VARCHAR(64), region VARCHAR(16));
INSERT INTO governance.role_region VALUES
    ('care_emea', 'EMEA'), ('care_amer', 'AMER');

CREATE RLS POLICY rls_patient_region
WITH (region VARCHAR(16))
USING (
    region IN (SELECT rr.region FROM governance.role_region rr
               WHERE pg_has_role(CURRENT_USER, rr.role_name, 'MEMBER'))
);
-- research + compliance see all regions via their own permissive policy
CREATE RLS POLICY rls_all_regions WITH (region VARCHAR(16)) USING (TRUE);

ATTACH RLS POLICY rls_patient_region ON clinical.patients TO ROLE care_emea;
ATTACH RLS POLICY rls_patient_region ON clinical.patients TO ROLE care_amer;
ATTACH RLS POLICY rls_all_regions   ON clinical.patients TO ROLE research;
ATTACH RLS POLICY rls_all_regions   ON clinical.patients TO ROLE compliance;
ALTER TABLE clinical.patients ROW LEVEL SECURITY ON;

-- 2. Column controls: hard-grant mrn only to compliance; mask ssn dynamically
GRANT SELECT(patient_id, region, name, ssn) ON clinical.patients TO ROLE research;
GRANT SELECT ON clinical.patients TO ROLE compliance;   -- all columns incl. mrn
-- (care teams get their own scoped GRANT omitting ssn and mrn entirely)

CREATE MASKING POLICY mask_ssn_last4
    WITH (ssn VARCHAR(11)) USING ('XXX-XX-' || right(ssn, 4));
CREATE MASKING POLICY mask_ssn_clear
    WITH (ssn VARCHAR(11)) USING (ssn);

ATTACH MASKING POLICY mask_ssn_last4 ON clinical.patients(ssn) TO PUBLIC     PRIORITY 0;
ATTACH MASKING POLICY mask_ssn_clear ON clinical.patients(ssn) TO ROLE compliance PRIORITY 10;

-- 3. Policy test — run as each role, assert isolation + masking
-- as care_emea:
SELECT count(*)                                       AS rows_seen,
       count(*) FILTER (WHERE region <> 'EMEA')       AS region_leaks,   -- 0
       count(*) FILTER (WHERE ssn NOT LIKE 'XXX-XX-%') AS ssn_leaks      -- 0
FROM clinical.patients;
-- as research: rows_seen = all regions; ssn LIKE 'XXX-XX-%'; SELECT mrn -> error
-- as compliance: rows_seen = all; ssn clear; mrn clear

Step-by-step trace.

Layer	Object	Effect
RLS (care)	rls_patient_region	region isolation via lookup
RLS (research/compliance)	rls_all_regions USING (TRUE)	all regions
Column GRANT	scoped SELECT lists	care teams never get ssn/mrn; research gets ssn not mrn
Masking (ssn)	last4 PUBLIC pri 0 + clear compliance pri 10	research sees XXX-XX-1234; compliance clear
Test	count + FILTER asserts	region_leaks = ssn_leaks = 0 in CI

After deployment, care_emea sees only EMEA patients and (via its scoped GRANT) cannot select ssn or mrn at all; research sees all regions with ssn masked to XXX-XX-1234 (the PUBLIC priority-0 policy) and cannot select mrn (hard GRANT boundary); compliance sees all regions with ssn and mrn in the clear (priority-10 clear policy plus full column grant). Multiple RLS policies never conflict here because each role has exactly one attached; where a user did hold two, Redshift would intersect them with AND unless CONJUNCTION TYPE OR were set.

Output:

Role	rows_seen	ssn	mrn
care_emea	EMEA only	not selectable	not selectable
research	all regions	XXX-XX-1234	not selectable
compliance	all regions	clear	clear

Why this works — concept by concept:

• RLS POLICY attached to roles — rls_patient_region scopes care teams from a lookup table, while rls_all_regions USING (TRUE) gives research and compliance every region. Enforcement activates only after ROW LEVEL SECURITY ON, and a role with no attached policy sees zero rows.
• AND-combination default — when a user holds multiple attached policies, Redshift intersects them with AND (stricter than BigQuery's union); CONJUNCTION TYPE OR opts into union. Naming this default is the senior distinction.
• Hard column GRANT for mrn — mrn is never in the research or care SELECT lists, so it is unselectable for them — a hard boundary appropriate for a regulated identifier that no research query should reference.
• Prioritized dynamic data masking for ssn — a fully/last-4 masked PUBLIC policy at priority 0 is the safe default; compliance's clear policy at priority 10 overrides it. Priority resolves the conflict deterministically, so ssn stays selectable for research (their pipelines run) but redacted.
• Assert-based tests — region_leaks = 0 and ssn_leaks = 0, checked as each role in CI, turn the policy into tested code; a regression fails the build instead of leaking PHI.
• Cost — the RLS predicate is a small lookup-table semi-join per scan; masking is a string rewrite per projected value; column GRANT is free (planner-level). One RLS policy and one masking policy per column scale across tables at O(1) governance versus O(roles × tables) hand-built views.

SQL
Topic — sql
SQL GRANT, masking, and row-security problems

Practice →

Database
Topic — database
Database problems on roles, grants, and policies

Practice →

---

Cheat sheet — RLS and CLS recipes across the four warehouses

• The two axes, one sentence. Row-level security prunes which rows a reader sees; column-level security controls which columns are shown in the clear — masked in place (schema-stable) or removed from the grant (hard boundary). Both sit on top of RBAC, and both should be driven by an entitlements mapping table so onboarding is an INSERT, not a DDL change. Unmapped principal = zero rows, the safe default.
• Snowflake row access policy. CREATE ROW ACCESS POLICY p AS (region VARCHAR) RETURNS BOOLEAN -> CURRENT_ROLE() IN ('GOVERNANCE') OR EXISTS (SELECT 1 FROM map WHERE role_name = CURRENT_ROLE() AND region = region); then ALTER TABLE t ADD ROW ACCESS POLICY p ON (region);. One policy object, many tables; the predicate prunes micro-partitions when selective.
• Snowflake masking policy. CREATE MASKING POLICY m AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN CURRENT_ROLE() IN ('COMPLIANCE') THEN val WHEN CURRENT_ROLE()='MARKETING' THEN SHA2(val,256) ELSE '***MASKED***' END; then ALTER TABLE t MODIFY COLUMN email SET MASKING POLICY m;. Column stays selectable; tiers keyed on CURRENT_ROLE(); use a stable hash if downstream must still join.
• BigQuery authorized view (portable). CREATE VIEW reporting.v AS SELECT id, region, name FROM raw.t WHERE sales_rep_email = SESSION_USER(); grant readers the view, then add the view as an authorized view on the source dataset. Row filter in WHERE, column security by omitting columns from SELECT; no taxonomy needed.
• BigQuery native row access + policy tags. CREATE ROW ACCESS POLICY p ON ds.t GRANT TO ('group:[e-mail]') FILTER USING (region='EMEA'); (readers see the union across their groups; no policy = zero rows). Column-level security: ALTER TABLE ds.t ALTER COLUMN email SET OPTIONS (policy_tags=['.../policyTags/EMAIL']); gated by Fine-Grained Reader; add a data-masking rule on the tag to mask instead of block.
• Databricks Unity Catalog row filter. CREATE FUNCTION g.rf(region STRING) RETURNS BOOLEAN RETURN is_account_group_member('governance') OR EXISTS (SELECT 1 FROM g.map m WHERE m.region=region AND is_account_group_member(m.group_name)); then ALTER TABLE t SET ROW FILTER g.rf ON (region);. Runs with definer's rights, so readers need no access to the mapping table.
• Databricks Unity Catalog column mask. CREATE FUNCTION g.mask(email STRING) RETURNS STRING RETURN CASE WHEN is_account_group_member('governance') THEN email ELSE '***' END; then ALTER TABLE t ALTER COLUMN email SET MASK g.mask;. Use USING COLUMNS (other_col) for conditional masking; audit with system.information_schema.row_filters / column_masks.
• Redshift RLS policy. CREATE RLS POLICY p WITH (region VARCHAR(16)) USING (region IN (SELECT region FROM map WHERE pg_has_role(CURRENT_USER, role_name, 'MEMBER'))); then ATTACH RLS POLICY p ON t TO ROLE analyst; and ALTER TABLE t ROW LEVEL SECURITY ON;. Multiple attached policies combine with AND by default (CONJUNCTION TYPE OR for union) — the opposite of BigQuery.
• Redshift column controls. Hard boundary: GRANT SELECT(id, region, name) ON t TO ROLE analyst; (omitted columns unselectable). Dynamic masking: CREATE MASKING POLICY m WITH (ssn VARCHAR(11)) USING ('XXX-XX-'||right(ssn,4)); ATTACH MASKING POLICY m ON t(ssn) TO PUBLIC PRIORITY 0; with higher-priority per-role overrides winning.
• Cross-warehouse decision matrix. Identity: Snowflake CURRENT_ROLE(), BigQuery SESSION_USER() + groups, Databricks is_account_group_member(), Redshift roles + pg_has_role/session context. Attach point: policy object (Snowflake/Redshift), view or policy (BigQuery), UDF (Databricks). Column style: masking policy (Snowflake), policy tag ± data-mask (BigQuery), mask UDF (Databricks), column GRANT + DDM (Redshift). Multi-policy rows: BigQuery = union, Redshift = intersection by default.
• Masking vs hard boundary — choose per column. Mask (schema-stable) when a role must still query the column (join keys, analytics fields, feature pipelines) but not read it in the clear. Hard boundary (column GRANT / omit from view / policy-tag block) when a role should never even reference the column (regulated identifiers). Regulated PII usually gets both across tiers: masked for analysts, granted only to compliance.
• Test every policy, inventory every attachment. Treat policies as code: connect as each role, SELECT, assert row count and redaction (leaks = 0) in CI. Inventory attachments with information_schema.policy_references (Snowflake), information_schema.row_filters/column_masks (Databricks), svv_rls_* / svv_masking_policy (Redshift), and taxonomy/RAP listings (BigQuery). "We eyeball it" is the answer that leaks PII in production.

Frequently asked questions

What is the difference between row-level and column-level security?

Row-level security controls which rows a reader is returned from a table; column-level security controls which columns of those rows are shown in the clear. They are orthogonal: a regional analyst might be scoped by row-level security to only their region's customers and by column-level security to a masked email on those customers — both controls active on one SELECT *. Row-level security is expressed as a predicate over a row attribute (region, tenant, owner) that keeps or drops the row; column-level security is expressed either as dynamic data masking (the column stays selectable but returns a redacted value) or as a hard grant boundary (the column is not selectable at all). Both sit on top of RBAC — the coarse grant that decides whether the reader can open the table in the first place — and every major warehouse (Snowflake, BigQuery, Databricks, Redshift) provides native mechanisms for both.

Row-level security vs column masking — when do I use each?

Use row-level security when readers should see different rows of the same table — regional isolation, multi-tenant separation, per-user record ownership. Use column masking (a form of column-level security) when readers should see the same rows but with sensitive columns redacted — a PII email, a card number, a national ID. The two are frequently combined: a tenant sees only its own rows (row-level) and internal analysts see all rows with the customer email masked (column-level). Prefer dynamic data masking over dropping the column when downstream pipelines must still query the column (as a join key or feature) without reading it in the clear — masking keeps the schema stable, so SELECT email still runs and just returns *** or a hash. Prefer a hard boundary (column GRANT on Redshift, omitting the column from a BigQuery authorized view, or a blocking policy tag) when a role should never even reference a regulated column.

How does RBAC relate to row-level and column-level security?

RBAC (role-based access control) is the coarse, outermost layer: it decides whether a role can open a table, database, or schema at all via GRANT. Row-level and column-level security are the fine-grained layers inside that gate. A query is evaluated RBAC-first — if the role has no SELECT grant, the row and column policies never even run. Once RBAC lets the reader in, row-level security prunes the rows and column-level security redacts or removes the columns. The best designs let RBAC handle "can this principal touch this object," then delegate "which rows" and "which columns" to policy objects driven by the caller's identity (CURRENT_ROLE(), SESSION_USER(), group membership) joined against an entitlements mapping table. RBAC alone is insufficient the moment one physical table must serve readers with different row or column entitlements — which is almost always, in a shared warehouse.

Can I share one security policy across many tables?

Yes, and you should — it is the difference between scalable governance and copy-paste sprawl. On Snowflake a ROW ACCESS POLICY and a MASKING POLICY are standalone schema objects: author once, attach to dozens of tables with ALTER TABLE. On Databricks a row-filter or column-mask is a SQL UDF: the function is the reusable governance object, and ALTER TABLE ... SET ROW FILTER / SET MASK is merely the binding, so one function protects every table it is attached to. On BigQuery a policy-tag taxonomy classifies the same PII column across every table in the project, and the entitlement is a single IAM grant on the tag. On Redshift an RLS POLICY and a MASKING POLICY are created once and attached to many tables/roles. The universal enabler is the mapping table: because the policy joins the caller's identity against entitlement data rather than hard-coding regions or users, the same policy body works for every table and every future principal — onboarding is an INSERT, not a policy edit.

Dynamic data masking vs static column grants vs tokenization — which do I choose?

Dynamic data masking keeps the column selectable and rewrites its value at query time based on the caller's role — best when downstream pipelines must still reference the column (as a join key or analytics field) but should not read it in the clear; the schema stays stable, so no query breaks. Static column grants (GRANT SELECT(cols) on Redshift, or omitting the column from a view/adding a blocking policy tag) create a hard boundary — the column is simply not selectable for the role, which is the right choice for regulated identifiers a role should never even reference. Tokenization replaces the sensitive value with a surrogate token backed by a secure vault that can (for authorized callers) be reversed — it is stronger than masking for values that must round-trip (e.g. a card number a payment service later needs), but it adds a token store and a detokenization path. Rule of thumb: mask for read-scoped analytics on stable schemas, hard-boundary for "never touch this column," tokenize when the real value must be recoverable by a trusted service. Many designs use all three across sensitivity tiers.

How do BigQuery authorized views compare to native row-level security?

BigQuery authorized views are the portable, no-taxonomy pattern: a view encodes the row filter in its WHERE (often WHERE x = SESSION_USER()) and the column projection in its SELECT list, readers are granted the view only, and the view is authorized to read the base dataset on the owner's behalf — so readers never touch the base table. Native row access policies (CREATE ROW ACCESS POLICY ... GRANT TO group FILTER USING (predicate)) attach the filter directly to the base table, so multiple groups can share one table without a view per group, and a reader sees the union of rows their granted policies allow. Use authorized views when you want the filter and projection centralised in one portable object, when you're also reshaping the data, or when you must support a pattern that mirrors other warehouses' secure views. Use native row access policies (often with policy-tag column-level security) when you want readers to query the base table directly and manage row filters as first-class, per-group policies rather than as many view definitions. They compose — an authorized view can sit over a table that also has row access policies and policy tags.

Practice on PipeCode

• Drill the SQL practice library → for the GRANT, masking, CASE, filtering, and access-policy problems that underpin every warehouse's row-level and column-level security.
• Rehearse on the design practice library → for the multi-tenant isolation, taxonomy-driven governance, and mapping-table access-control scenarios senior interviewers love.
• Stress-test the fundamentals on the database practice library → for roles, grants, policies, and the schema-design decisions that make RLS and CLS maintainable.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-warehouse decision matrix against real graded inputs.

Lock in row & column security muscle memory

Docs explain the syntax. PipeCode drills explain the decision — when a row access policy beats a per-team view, when dynamic data masking beats a hard column grant, when BigQuery's union semantics differ from Redshift's intersection, and how one mapping table drives every entitlement across Snowflake, BigQuery, Databricks, and Redshift. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →