5.1 Dynamic Row Filters & Column Masking Implementation

Microsoft Certified: Azure Databricks Data Engineer Associate DP-750

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Azure Databricks Architecture & Compute Selection

1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute

2 Chapter 2: Compute Configuration, Performance & Cost Management

2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions

3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup

3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown

4 Chapter 4: Unity Catalog Privilege Model & Access Control

4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation

5 Chapter 5: Advanced Data Security, Masking & ABAC

5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks

6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing

6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions

7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming

7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory

8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance

8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg

9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture

9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment

10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables

10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs

11 Chapter 11: Workflow Orchestration with Lakeflow Jobs

11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling

12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles

12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines

13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning

13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics

5.1 Dynamic Row Filters & Column Masking Implementation

Key Takeaways Explain This Topic AI

Unity Catalog row filters and column masks enforce fine-grained access control (FGAC) directly on base Delta tables using standard SQL User-Defined Functions (UDFs).

Row filter UDFs evaluate a boolean predicate using built-in context functions like is_account_group_member() and session_user(), filtering out unauthorized rows before query execution.

Column mask UDFs transform or redact sensitive field values based on user identity or group membership, supporting conditional masking that evaluates secondary table columns.

Unlike legacy dynamic views, row filters and column masks apply directly to base tables, preserving table metadata, write operations, and automated column-level lineage.

Modifying or applying filters and masks requires the EXECUTE privilege on the SQL UDF and the MODIFY or OWNER privilege on the target table.

Last updated: August 2026

5.1 Dynamic Row Filters & Column Masking Implementation

DP-750 Exam Focus: Master fine-grained access control (FGAC) in Unity Catalog using native row filters and column masks. Understand how to write scalar SQL User-Defined Functions (UDFs) with is_account_group_member() and session_user() , attach them to base tables via ALTER TABLE ... SET ROW FILTER and ALTER COLUMN ... SET MASK , evaluate multi-column conditional masking ( USING COLUMNS ), and analyze query rewrite mechanics in the Catalyst optimizer.

1. Evolution of Fine-Grained Access Control (FGAC)

In legacy data architectures (and early Hive Metastore implementations on Databricks), securing subsets of rows or sensitive columns required creating intermediate Dynamic Views . Data engineers wrote views containing CASE statements or WHERE clauses that evaluated user context, revoked base table permissions, and granted users access strictly to the view.

While dynamic views provided basic security, they introduced significant architectural drawbacks:

Namespace Sprawl & Management Overhead: Every security tier or user persona required a distinct view (e.g., customers_us_view , customers_eu_view , customers_redacted_view ).

Broken Write Pathways: Users could not issue INSERT , UPDATE , DELETE , or MERGE operations through views containing complex conditional logic.

Fragmented Metadata & Lineage: Downstream BI tools connected to disparate view names rather than the canonical base table, obscuring data lineage and complicating performance optimization.

Unity Catalog Row Filters and Column Masks solve these challenges by binding security logic directly to the base table metadata . The table maintains a single, unified three-level namespace identifier ( catalog.schema.table ), and Unity Catalog transparently enforces filtering and masking at query runtime across all compute modalities.

Comparative Analysis: Legacy Dynamic Views vs. Unity Catalog FGAC

• Architectural Capability Legacy Dynamic Views Unity Catalog Row Filters & Column Masks
• Target Securable Applied to a virtual view object Applied directly to base Delta tables

Namespace Simplification Requires separate view names per access level Single unified table name for all consumers

Write Path Operations View writes are heavily restricted or unsupported Supports governed DML ( UPDATE , DELETE , MERGE ) on base tables

Data Lineage Tracking Lineage stops or fragments across intermediate views Full end-to-end column-level lineage preserved

Performance Optimization View encapsulation can hinder predicate pushdown Catalyst optimizer pushes predicates through UDF evaluation

Centralized Administration Requires maintaining DDL scripts for dozens of views Centralized SQL UDFs reusable across multiple tables

2. Built-In Contextual Functions

Row filters and column masks rely on Databricks built-in contextual functions to determine the identity, group membership, and runtime environment of the executing session. +-----------------------------------------------------------------------------------------+

• | UNITY CATALOG CONTEXTUAL FUNCTIONS |
• +-----------------------------------------------------------------------------------------+
• | |
• | 1. is_account_group_member('group_name') |
• | - Evaluates whether the active querying user belongs to the specified Databricks |
• | account-level group (e.g., 'finance_analysts', 'compliance_officers'). |
• | - RECOMMENDED: Operates natively with SCIM-synchronized Entra ID groups. |
• | |
• | 2. session_user() / current_user() |
• | - Returns the authenticated user's email address or service principal Application |
• | ID (e.g., 'jane.doe@enterprise.com', 'a1b2c3d4-e5f6-...'). |
• | - session_user() returns the primary interactive user even inside nested contexts. |
• | |
• | 3. is_member('group_name') |
• | - Legacy workspace-level group check. In Unity Catalog, always prefer |
• | is_account_group_member() for multi-workspace consistency. |
• +-----------------------------------------------------------------------------------------+

Exam Tip: For DP-750 questions concerning Unity Catalog security, always choose is_account_group_member() over the legacy is_member() . Unity Catalog identities are managed centrally at the account level and synchronized via Microsoft Entra ID (Azure AD) SCIM; therefore, account group evaluation guarantees uniform authorization across all regional workspaces.

3. Dynamic Row Filters: Architectural Principles & DDL Syntax

A Row Filter is a SQL User-Defined Function (UDF) that returns a BOOLEAN result. When attached to a table, Unity Catalog evaluates the UDF for every candidate row during query execution. If the function evaluates to TRUE , the row is returned to the user; if it evaluates to FALSE or NULL , the row is silently filtered out. ROW FILTER EXECUTION FLOW

• Query: SELECT * FROM prod.sales.orders;
• |
• v
• +--------------------------+
• | Scan Base Delta Table |
• +--------------------------+
• |
• v
• +--------------------------+
• | Evaluate Filter SQL UDF | <--- is_account_group_member('eu_sales')
• +--------------------------+
• / \
• (Evaluates TRUE) (Evaluates FALSE)
• / \
• v v
• +------------------+ +------------------+
• | Row Included in | | Row Filtered Out |
• | Query Result Set | | (Never Returned) |
• +------------------+ +------------------+
• Step-by-Step Row Filter Implementation
• Step 1: Create the Row Filter SQL UDF

The UDF accepts one or more table column arguments and implements the conditional authorization logic: -- Create a reusable row filter function in a shared governance schema

• CREATE OR REPLACE FUNCTION prod.governance.region_row_filter(region_code STRING)
• RETURNS BOOLEAN
• LANGUAGE SQL
• DETERMINISTIC
• CONTAINS SQL
• COMMENT 'Filters rows based on user regional group membership or admin status'
• RETURN
• - System administrators and auditors see all records
• is_account_group_member('enterprise_auditors')
• OR is_account_group_member('data_platform_admins')
• - Regional managers see records matching their specific operating region
• OR (is_account_group_member('na_sales_reps') AND region_code = 'NA')
• OR (is_account_group_member('eu_sales_reps') AND region_code = 'EU')
• OR (is_account_group_member('apac_sales_reps') AND region_code = 'APAC');
• Step 2: Apply the Row Filter to a Base Table

You can attach the filter during initial table creation or alter an existing table: -- Method A: Applying to an existing Delta table

• ALTER TABLE prod.sales.customer_orders
• SET ROW FILTER prod.governance.region_row_filter ON (order_region);

• - Method B: Applying during table creation (CTAS or DDL)
• CREATE TABLE prod.sales.regional_metrics (
• metric_id BIGINT GENERATED ALWAYS AS IDENTITY,
• region_code STRING,
• metric_date DATE,
• revenue_amount DECIMAL(18,2)
• )
• WITH ROW FILTER prod.governance.region_row_filter ON (region_code);
• Step 3: Multi-Column Row Filters

Row filter functions can accept multiple column arguments to evaluate composite business rules: -- UDF accepting both business unit and classification status

• CREATE OR REPLACE FUNCTION prod.governance.multi_attr_filter(dept_name STRING, is_confidential BOOLEAN)
• RETURNS BOOLEAN
• RETURN
• is_account_group_member('executive_board')
• OR (is_account_group_member('hr_department') AND dept_name = 'HR')
• OR (is_account_group_member('general_staff') AND is_confidential = FALSE);

• - Attach multi-column filter
• ALTER TABLE prod.hr.employee_records
• SET ROW FILTER prod.governance.multi_attr_filter ON (department, confidential_flag);
• Step 4: Removing or Updating Row Filters
• To drop an existing filter from a table: ALTER TABLE prod.sales.customer_orders DROP ROW FILTER;
• 4. Column Masking: Architectural Principles & DDL Syntax

A Column Mask is a scalar SQL UDF that accepts the value of a target column (and optionally additional columns) and returns a masked or transformed value of the exact same data type . COLUMN MASK EXECUTION FLOW

• Query: SELECT ssn, full_name, email FROM prod.customers.pii_data;
• |
• v
• +--------------------------+
• | Scan Base Delta Table |
• +--------------------------+
• |
• v
• +--------------------------+
• | Evaluate Mask SQL UDF | <--- is_account_group_member('hr_admins')
• +--------------------------+
• / \
• (Is Member: TRUE) (Is Member: FALSE)
• / \
• v v
• +------------------+ +----------------------+
• | Return Original: | | Return Masked Value: |
• | '123-45-6789' | | '***-**-6789' |
• +------------------+ +----------------------+
• Step-by-Step Column Mask Implementation
• Step 1: Create the Column Mask SQL UDF

The return data type must match the target column data type exactly: -- Create a masking function for Social Security Numbers (SSN)

• CREATE OR REPLACE FUNCTION prod.governance.ssn_mask(ssn_val STRING)
• RETURNS STRING
• LANGUAGE SQL
• DETERMINISTIC
• COMMENT 'Redacts SSN for non-compliance users, showing only last 4 digits'
• RETURN
• CASE
• WHEN is_account_group_member('compliance_hr_specialists') THEN ssn_val
• WHEN ssn_val IS NULL THEN NULL
• ELSE concat('***-**-', substring(regexp_replace(ssn_val, '[^0-9]', ''), 6, 4))
• END;

• - Create a masking function for financial amounts (DECIMAL)
• CREATE OR REPLACE FUNCTION prod.governance.salary_mask(salary_amount DECIMAL(18,2))
• RETURNS DECIMAL(18,2)
• RETURN
• CASE
• WHEN is_account_group_member('payroll_officers') THEN salary_amount
• ELSE CAST(NULL AS DECIMAL(18,2))
• END;
• Step 2: Apply the Column Mask to Table Columns
• Attach the mask using ALTER TABLE ... ALTER COLUMN : -- Apply SSN mask to the ssn column
• ALTER TABLE prod.customers.pii_data
• ALTER COLUMN ssn SET MASK prod.governance.ssn_mask;

• - Apply salary mask to the compensation column
• ALTER TABLE prod.hr.compensation_details
• ALTER COLUMN base_salary SET MASK prod.governance.salary_mask;
• Step 3: Conditional Masking with Additional Columns ( USING COLUMNS )

Unity Catalog allows column masks to evaluate secondary context columns from the same table row to dynamically decide whether to mask the primary column. This is achieved using the USING COLUMNS clause: -- Mask UDF that checks a country_code column in addition to the phone_number

• CREATE OR REPLACE FUNCTION prod.governance.conditional_phone_mask(
• phone STRING,
• country STRING
• )
• RETURNS STRING
• RETURN
• CASE
• - Full access if compliance admin
• WHEN is_account_group_member('global_privacy_admins') THEN phone
• - Country-specific access rules
• WHEN is_account_group_member('eu_support') AND country = 'FR' THEN phone
• WHEN is_account_group_member('eu_support') AND country = 'DE' THEN phone
• WHEN is_account_group_member('us_support') AND country = 'US' THEN phone
• - Otherwise redact everything except the last 3 digits
• ELSE concat('XXX-XXX-', substring(phone, length(phone) - 2, 3))
• END;

• - Apply the mask using the secondary column 'residence_country'
• ALTER TABLE prod.customers.contact_directory
• ALTER COLUMN phone_number
• SET MASK prod.governance.conditional_phone_mask USING COLUMNS (residence_country);
• Step 4: Removing Column Masks
• To remove a column mask from a column: ALTER TABLE prod.customers.contact_directory
• ALTER COLUMN phone_number DROP MASK;
• 5. Execution Engine, Catalyst Optimizer Rewriting, & Security Guarantees

Understanding the runtime mechanics of how Unity Catalog enforces row filters and column masks is essential for performance tuning and troubleshooting on the DP-750 exam. +-----------------------------------------------------------------------------------------+

• | SPARK CATALYST QUERY REWRITE LIFECYCLE |
• +-----------------------------------------------------------------------------------------+
• | |
• | 1. User Submits Query: |
• | SELECT customer_id, ssn, total_spend FROM sales.orders WHERE total_spend > 5000; |
• | |
• | 2. Metastore Security Resolution: |
• | - Validates user has SELECT privilege on sales.orders. |
• | - Discovers active Row Filter: region_row_filter(order_region) |
• | - Discovers active Column Mask: ssn_mask(ssn) |
• | |
• | 3. Logical Plan Transformation (Rewritten Query): |
• | SELECT |
• | customer_id, |
• | ssn_mask(ssn) AS ssn, |
• | total_spend |
• | FROM sales.orders |
• | WHERE region_row_filter(order_region) = TRUE |
• | AND (total_spend > 5000); |
• | |
• | 4. Physical Optimization & Predicate Pushdown: |
• | - Push deterministic filter expressions into Delta scan file pruning. |
• | - Vectorized evaluation of mask UDF in Photon / Spark executor CPU registers. |
• +-----------------------------------------------------------------------------------------+
• Security & Side-Channel Attack Guarantees

A common vulnerability in database security systems is the error-based side-channel attack , where a malicious user attempts to deduce hidden data by injecting expressions that trigger division-by-zero or casting errors on filtered rows (e.g., SELECT * FROM table WHERE 1 / (CASE WHEN secret_col = 'admin_password' THEN 0 ELSE 1 END) = 1 ).

Unity Catalog provides rigorous mathematical guarantees against side-channel leaks:

Predicate Reordering Barrier: The Catalyst optimizer enforces that row filter predicates are evaluated prior to or in strict conjunction with user-supplied WHERE clauses. The engine guarantees that user expressions never evaluate against unauthorized rows.

Deterministic Evaluation: Functions used in row filters and column masks must be deterministic and side-effect free. They cannot execute arbitrary external Python code or invoke un-audited network sockets.

Subquery Restrictions: Row filter and column mask UDFs cannot execute subqueries against other mutable tables (to prevent locking loops and race conditions).

Write-Path Semantics (DML Operations)

How do row filters and column masks behave during write operations ( INSERT , UPDATE , DELETE , MERGE )?

Row Filter Enforcement on Writes:

When a user issues an UPDATE or DELETE on a table with a row filter, the statement only affects rows that the user is permitted to see . If a user tries to run DELETE FROM orders; , they will delete only the records satisfying their row filter; unauthorized rows remain completely untouched.

When inserting rows ( INSERT INTO ), if the inserted row would evaluate to FALSE under the user's row filter, the write is permitted, but subsequent SELECT queries by that same user will not display the newly inserted row.

Column Mask Enforcement on Writes:

Users with write permissions ( MODIFY ) must supply the actual, unmasked data when executing INSERT or UPDATE statements. Masking is strictly applied during read projection ( SELECT ), preventing users from accidentally overwriting raw data with masked string constants.

6. Administrative Privileges, Ownership, & Governance Lifecycle

Implementing fine-grained access control requires specific Unity Catalog privileges across the catalog, schema, function, and table securable hierarchy.

Privilege Matrix for Creating and Applying Policies

Operational Action Required Securable Privileges

Define / Create Filter or Mask UDF USE CATALOG on catalog, USE SCHEMA and CREATE FUNCTION on target schema.

Attach Row Filter to a Table USE CATALOG and USE SCHEMA on table schema, MODIFY or APPLY TAG / OWNER on table, and EXECUTE on the SQL UDF.

Attach Column Mask to a Column USE CATALOG and USE SCHEMA on table schema, MODIFY or APPLY TAG / OWNER on table, and EXECUTE on the SQL UDF.

Query Governed Table (Consumer) USE CATALOG , USE SCHEMA , and SELECT on the target table. ( Note: Consumers do NOT need explicit EXECUTE on the underlying UDF; Unity Catalog executes the bound policy with elevated system context ). -- Administrative Script: Delegating Governance Setup

• - 1. Grant governance team permission to manage functions in governance schema
• GRANT USAGE, CREATE FUNCTION ON SCHEMA prod.governance TO `data_security_officers`;

• - 2. Allow data engineers to execute the mask function
• GRANT EXECUTE ON FUNCTION prod.governance.ssn_mask TO `data_engineers`;
• GRANT EXECUTE ON FUNCTION prod.governance.region_row_filter TO `data_engineers`;

• - 3. Data engineers apply the filter to the sales table
• GRANT MODIFY ON TABLE prod.sales.customer_orders TO `data_engineers`;

• - 4. Analysts only receive SELECT on the table
• GRANT SELECT ON TABLE prod.sales.customer_orders TO `business_analysts`;

Loading diagram... Catalyst Optimizer Dynamic Query Plan Transformation for Row Filters & Column Masks