Row Filters, Column Masks & Retention

Dynamic data protection

Row filters

Row filters restrict which rows a user can see:

-- Create a row filter function
CREATE OR REPLACE FUNCTION prod_sales.functions.region_row_filter(region_value STRING)
RETURN
  IS_ACCOUNT_GROUP_MEMBER('global-admins')  -- admins see all
  OR region_value = CURRENT_USER_ATTRIBUTE('region');  -- others see their region only

-- Apply the filter to a table
ALTER TABLE prod_sales.curated.daily_revenue
  SET ROW FILTER prod_sales.functions.region_row_filter ON (region);

What happens at query time:

User	Group	Query Result
Ravi (APAC)	data-engineers	Only APAC rows
Analyst (EMEA)	bi-analysts	Only EMEA rows
Sarah (admin)	global-admins	All rows

The filter is invisible to users — they run SELECT * FROM daily_revenue and get only their rows. No WHERE clause needed.

Removing a row filter

ALTER TABLE prod_sales.curated.daily_revenue DROP ROW FILTER;

Column masks

Column masks transform what value a user sees in a specific column:

-- Create a column mask function
CREATE OR REPLACE FUNCTION prod_sales.functions.email_mask(email_value STRING)
RETURN
  CASE
    WHEN IS_ACCOUNT_GROUP_MEMBER('pii-readers') THEN email_value  -- full email
    ELSE CONCAT(LEFT(email_value, 2), '****@', SPLIT(email_value, '@')[1])  -- masked
  END;

-- Apply the mask to a column
ALTER TABLE prod_sales.curated.customers
  ALTER COLUMN email SET MASK prod_sales.functions.email_mask;

What users see:

User	Group	email Column Value
Mei Lin	pii-readers	john.smith@freshmart.com
BI analyst	bi-analysts	jo****@freshmart.com

-- More masking patterns
-- Hash the value (useful for joins without revealing data)
CREATE FUNCTION hash_mask(val STRING) RETURN SHA2(val, 256);

-- Show NULL instead of the value
CREATE FUNCTION null_mask(val STRING) RETURN NULL;

-- Show last 4 digits only (credit cards)
CREATE FUNCTION card_mask(val STRING) RETURN CONCAT('****-****-****-', RIGHT(val, 4));

Feature	Row Filter	Column Mask
Controls	Which rows are visible	What value a column shows
Applied to	Table (one filter per table)	Column (one mask per column)
Function returns	BOOLEAN (TRUE = visible)	Transformed column value
User awareness	Invisible — filtered rows don't appear	Visible — masked values appear different
Use case	Regional data isolation, tenant separation	PII protection, partial redaction
Remove	ALTER TABLE ... DROP ROW FILTER	ALTER TABLE ... ALTER COLUMN ... DROP MASK

💡 Exam tip: Row filters vs column-level access

Don’t confuse row filters with column-level GRANT:

• Column-level GRANT (Module 6): restricts which columns a user can SELECT. If they try to access a restricted column, they get an error.
• Column mask: lets the user SELECT the column but transforms the value (redaction, hashing, partial masking). No error — just a different value.

If the exam says “hide the column entirely” → column-level GRANT. If it says “show a masked/redacted version” → column mask.

Data retention policies

Data retention controls how long data is kept before it must be deleted or archived:

Delta Lake retention settings

-- Set table-level retention for VACUUM (default: 7 days)
ALTER TABLE prod_sales.curated.daily_revenue
  SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 30 days');

-- Set log retention (how long the transaction log keeps history)
ALTER TABLE prod_sales.curated.daily_revenue
  SET TBLPROPERTIES ('delta.logRetentionDuration' = 'interval 90 days');

Property	Default	Purpose
delta.deletedFileRetentionDuration	7 days	How long deleted data files are kept before VACUUM removes them
delta.logRetentionDuration	30 days	How long the transaction log (time travel history) is preserved

Retention and compliance

At NovaPay, Tomás must comply with financial regulations requiring 7-year data retention:

• Log retention: Set to 7 years (allows time travel queries for audit)
• File retention: Set high enough that VACUUM doesn’t delete files needed for compliance
• Governance tags: Tag tables with retention_years=7 for audit tracking

ℹ️ VACUUM and retention interaction

VACUUM removes old data files that are no longer referenced by the current table version. The deletedFileRetentionDuration controls the safety window:

• Files newer than the retention period are never deleted by VACUUM
• This protects time travel and concurrent reads
• Setting retention too low risks deleting files that active queries still need

Exam tip: The default 7-day retention is safe for most workloads. Lowering it below 7 days requires explicitly disabling the safety check — and the exam considers this a red flag.

Question

What is the difference between a row filter and a column mask?

Click or press Enter to reveal answer

Answer

Row filter: controls WHICH ROWS a user can see (returns TRUE/FALSE per row). Column mask: controls WHAT VALUE a user sees in a specific column (returns transformed value). Both are transparent to the user.

Click to flip back

Question

What are the two key Delta Lake retention properties?

Click or press Enter to reveal answer

Answer

1) delta.deletedFileRetentionDuration (default 7 days) — how long VACUUM keeps deleted files. 2) delta.logRetentionDuration (default 30 days) — how long time travel history is preserved in the transaction log.

Click to flip back

Question

What is the difference between a column mask and column-level GRANT?

Click or press Enter to reveal answer

Answer

Column-level GRANT hides the column entirely (error if accessed). Column mask shows the column but transforms the value (e.g., 'jo****@email.com'). Use GRANT to hide, use masks to redact.

Click to flip back

🎬 Video coming soon

Knowledge check

Knowledge Check

Tomás needs to protect customer email addresses in NovaPay's fraud investigation table. Fraud analysts should see partial emails (first 2 characters + masked domain), while compliance officers should see full emails. Which approach should he use?

ACreate two separate tables — one with full emails, one with masked emailsBApply a column mask function that checks the user's group membershipCUse a row filter to hide rows containing email addressesDRevoke SELECT on the email column for fraud analysts

Check Answer

Knowledge Check

Mei Lin's compliance team at Freshmart requires that deleted transaction data files must be recoverable for at least 90 days after deletion (for audit purposes). Which Delta Lake property should she configure?

Adelta.logRetentionDuration = 'interval 90 days'Bdelta.deletedFileRetentionDuration = 'interval 90 days'Cdelta.enableChangeDataFeed = trueDdelta.autoOptimize.optimizeWrite = true

Check Answer

---

Next up: Lineage, Audit Logs & Delta Sharing — track data flow, log access, and share data securely across organisations.