Study guide

3.1 Column-Level Masking & Row Access Policies

SnowPro Advanced: Architect ARA-C01

Track your progress as you read

Finished sections get a check mark.

1 Introduction: The ARA-C01 Exam

About the SnowPro Advanced: Architect (ARA-C01) Exam

2 Chapter 1: Account Strategy & Organization Architecture

1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture

3 Chapter 2: Identity, Access Control & Storage Integrations

2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies

4 Chapter 3: Governance, Data Privacy, Network Security & Compliance

3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)

5 Chapter 4: Data Modeling in Snowflake

4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration

6 Chapter 5: Data Sharing & Collaboration Solutions

5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms

7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity

6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs

8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications

7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps

9 Chapter 8: Data Ingestion Architecture

8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture

10 Chapter 9: Continuous Pipelines & Stream Transformation

9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design

11 Chapter 10: Semi-Structured Data & Ecosystem Tooling

10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures

12 Chapter 11: Virtual Warehouse Architecture & Concurrency

11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service

13 Chapter 12: Query Profiling & Performance Diagnostics

12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse

14 Chapter 13: Storage Optimization, Cost Governance & Observability

13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer

3.1 Column-Level Masking & Row Access Policies

Key Takeaways Explain This Topic AI

Dynamic Data Masking (DDM) operates dynamically at query compilation without rewriting micro-partitions; the masking policy signature input and return types must match the target column data type exactly.

In masking policy logic, CURRENT_ROLE() evaluates only the user's active primary role, whereas IS_ROLE_IN_SESSION() verifies whether a role is active within the primary role hierarchy or secondary roles.

Conditional masking policies evaluate multiple columns simultaneously (e.g., masking an SSN or salary based on a country code or VIP flag in the same row) via additional arguments specified in the USING clause.

When Row Access Policies (RAP) and Masking Policies coexist on a table, Snowflake evaluates Row Access Policies first to prune inaccessible rows before evaluating column masking transformations.

Masking policies cannot be set directly on external-table virtual columns (protect the VALUE column instead), and a column already protected by a masking policy can be referenced by a row access or conditional masking policy only if that masking policy sets EXEMPT_OTHER_POLICIES = TRUE.

Last updated: September 2026

3.1 Column-Level Masking & Row Access Policies

In enterprise data architectures, securing sensitive information requires granular access controls that operate seamlessly without duplicating physical storage. Snowflake provides two foundational policy-driven security primitives: Dynamic Data Masking (DDM) for column-level security and Row Access Policies (RAP) for row-level security. Both mechanisms dynamically intercept queries at compilation time, injecting policy logic directly into the execution plan while leaving underlying micro-partitions completely unchanged.

Dynamic Data Masking Architecture

Dynamic Data Masking operates as a schema-level object that transforms plain-text column values into masked or obfuscated representations at query runtime based on the caller's session context. -- Create a centralized security schema

CREATE SCHEMA IF NOT EXISTS governance_db.security_policies;

  • - Define a Dynamic Data Masking policy for PII strings
  • CREATE OR REPLACE MASKING POLICY governance_db.security_policies.mask_pii_string
  • AS (val STRING) RETURNS STRING ->
  • CASE
  • WHEN IS_ROLE_IN_SESSION('PRIVILEGED_ANALYST_ROLE') THEN val
  • WHEN IS_ROLE_IN_SESSION('SUPPORT_ROLE') THEN CONCAT(LEFT(val, 2), '***', RIGHT(val, 2))
  • ELSE '********'
  • END;
  • Signature Rules and Return Types

When authoring masking policies, Snowflake enforces strict signature contract rules:

Data Type Equivalence : The return data type of the masking expression must match exactly the input data type of the target column. A policy accepting STRING must return STRING ; a policy accepting NUMBER(10,2) must return NUMBER(10,2) . Returning VARCHAR from a NUMBER masking policy causes a compilation error.

Type Coercion Limits : Snowflake does not perform implicit lossy conversions inside policy expressions. For date, timestamp, or numeric columns, masking values must be valid instances of that type (e.g., '1970-01-01'::DATE or -999999 for numeric columns).

Single Policy per Column : A column can have only one masking policy applied directly at any given time.

Context Functions: CURRENT_ROLE() vs IS_ROLE_IN_SESSION()

A critical distinction tested on the SnowPro Advanced: Architect exam is the behavior of session authorization functions:

Function Evaluation Mechanism Role Hierarchy & Secondary Roles

CURRENT_ROLE() Evaluates the single, active primary role in the current user session ( CURRENT_ROLE() = 'ADMIN' ). Ignores inherited roles in the RBAC hierarchy. Ignores roles enabled via secondary roles ( USE SECONDARY ROLES ALL ).

IS_ROLE_IN_SESSION('ROLE_NAME') Evaluates whether the specified role is active in the session, either as the primary role, an inherited parent role in the active hierarchy, or through secondary roles. Respects full RBAC role hierarchies and secondary role configurations. Recommended for production policies.

Architect Tip : Always prefer IS_ROLE_IN_SESSION() over CURRENT_ROLE() . Using CURRENT_ROLE() = 'DATA_OFFICER' breaks when a user inherits DATA_OFFICER through an executive role or activates multiple roles simultaneously in modern BI tools.

Applying and Unsetting Masking Policies

To bind a policy to an existing table or view column, execute ALTER TABLE ... MODIFY COLUMN : -- Bind masking policy to customer email

  • ALTER TABLE sales_db.public.customers
  • MODIFY COLUMN email SET MASKING POLICY governance_db.security_policies.mask_pii_string;
  • - Detach masking policy
  • ALTER TABLE sales_db.public.customers
  • MODIFY COLUMN email UNSET MASKING POLICY;
  • To replace an existing policy on a column, use the FORCE keyword: ALTER TABLE sales_db.public.customers
  • MODIFY COLUMN email SET MASKING POLICY governance_db.security_policies.mask_pii_v2 FORCE;
  • Conditional Masking Policies

Standard masking policies evaluate only the column being masked. However, enterprise compliance often requires conditional masking , where masking logic depends on the values of other columns within the same row (such as country code, classification level, or account tier).

Conditional Masking Syntax

In a conditional masking policy, the first argument represents the column being masked, while subsequent arguments represent additional context columns passed from the same table: -- Create conditional policy: first argument is target column, subsequent arguments are conditional

  • CREATE OR REPLACE MASKING POLICY governance_db.security_policies.mask_salary_conditional
  • AS (salary NUMBER(12,2), employee_country STRING, is_executive BOOLEAN)
  • RETURNS NUMBER(12,2) ->
  • CASE
  • WHEN IS_ROLE_IN_SESSION('PAYROLL_ADMIN') THEN salary
  • WHEN is_executive = TRUE AND NOT IS_ROLE_IN_SESSION('C_SUITE_ROLE') THEN NULL
  • WHEN employee_country = 'DE' AND NOT IS_ROLE_IN_SESSION('EU_DPO_ROLE') THEN -1.00
  • ELSE salary
  • END;
  • Binding Conditional Policies via USING

When applying a conditional masking policy, you must specify the conditional columns in the USING clause in the exact positional order declared in the policy signature: ALTER TABLE hr_db.corp.employees

  • MODIFY COLUMN base_salary
  • SET MASKING POLICY governance_db.security_policies.mask_salary_conditional
  • USING (base_salary, country_code, is_exec);

Exam Trap : The first argument in the USING clause must be the column being modified. The additional arguments can be any columns from the same table, provided their data types match the policy signature.

Row-Level Security: Row Access Policies (RAP)

Row Access Policies determine which rows are visible in the result set when querying a table or view. Like Dynamic Data Masking, Row Access Policies are schema-level objects evaluated dynamically at query compilation.

Row Access Policy Mechanics

A Row Access Policy takes one or more column arguments and returns a boolean value ( TRUE allows the row to be returned; FALSE filters the row out): CREATE OR REPLACE ROW ACCESS POLICY governance_db.security_policies.tenant_isolation_rap

  • AS (tenant_id STRING) RETURNS BOOLEAN ->
  • IS_ROLE_IN_SESSION('ENTERPRISE_ADMIN')
  • OR tenant_id = CURRENT_ROLE();
  • Apply the policy to a table using ADD ROW ACCESS POLICY : ALTER TABLE saas_db.app.transactions
  • ADD ROW ACCESS POLICY governance_db.security_policies.tenant_isolation_rap ON (tenant_id);
  • Entitlement Mapping Tables

Hardcoding role names into policy expressions is unmaintainable in enterprise environments with thousands of users, departments, or geographical boundaries. The architect-grade design pattern uses Entitlement Mapping Tables (also known as security lookup tables): -- Entitlement mapping table

  • CREATE TABLE governance_db.security_policies.region_entitlements (
  • role_name STRING NOT NULL,
  • allowed_region STRING NOT NULL
  • );
  • - Row Access Policy referencing the mapping table
  • CREATE OR REPLACE ROW ACCESS POLICY governance_db.security_policies.regional_sales_rap
  • AS (sales_region STRING) RETURNS BOOLEAN ->
  • IS_ROLE_IN_SESSION('SALES_GLOBAL_VP')
  • OR EXISTS (
  • SELECT 1
  • FROM governance_db.security_policies.region_entitlements e
  • WHERE e.allowed_region = sales_region
  • AND IS_ROLE_IN_SESSION(e.role_name)
  • );
  • Mapping Table Optimization Guidelines

When implementing mapping tables in high-concurrency production environments:

Keep Mapping Tables Compact : Mapping tables should ideally fit within a few micro-partitions so they are readily cached in virtual warehouse memory.

Avoid Volatile Functions : Do not call non-deterministic functions (like RANDOM() or dynamic external calls) inside the RAP subquery.

Grant Select Privileges : Ensure the role creating or owning the policy has SELECT privileges on the mapping table. When a user queries the protected table, policy subqueries run under the context of the policy, but object authorization must remain intact.

Policy Interactions and Execution Order

A common architectural challenge is understanding how Snowflake behaves when both a Row Access Policy and one or more Dynamic Data Masking policies are applied to the same table. Query Submitted

  • │
  • ▼
  • 1. Query Compilation & Parsing
  • │
  • ▼
  • 2. Row Access Policy (RAP) Evaluation ──► Filters rows out of the query scope
  • │
  • ▼
  • 3. Dynamic Data Masking (DDM) Evaluation ──► Obfuscates column values on surviving rows
  • │
  • ▼
  • 4. Micro-Partition Pruning & Execution Engine
  • │
  • ▼
  • Caller Receives Filtered & Masked Result Set
  • Why Execution Order Matters

Snowflake's query rewrite engine guarantees that Row Access Policies are evaluated FIRST, and Masking Policies are evaluated SECOND :

Security Boundary Integrity : If masking occurred before row filtering, conditional row access logic might attempt to filter on values that were already masked into '********' , resulting in invalid row elimination or runtime errors.

Side-Channel Prevention : Evaluating row filters first ensures that masking transformations and expensive cryptographic hashing functions are never executed on records the user is not entitled to see.

Performance Efficiency : Pruning unauthorized rows early minimizes the number of records processed by column-level masking expressions in downstream execution stages.

Architectural Limitations and Exam Traps

Understanding policy limitations is essential for passing the SnowPro Advanced: Architect exam:

1. External Tables and Virtual Columns

Dynamic Data Masking cannot be applied to virtual columns in external tables.

Masking policies can be applied to the VALUE column of an external table, or standard columns on regular internal tables, but virtual columns derived from parsed JSON payloads ( METADATA$EXTERNAL_TABLE_PARTITION or expressions like VALUE:cust_id::STRING ) are unsupported.

Workaround : Create a secure view over the external table and apply the masking policy or masking logic inside the secure view definition.

2. Join Elimination and Query Optimizer Impact

Row Access Policies that reference mapping tables via subqueries introduce implicit joins into every query against the target table.

The Snowflake query optimizer relies on constraint-based join elimination to prune unused tables. When a RAP introduces an active subquery against an entitlement table, the optimizer cannot eliminate joins against that table, which can suppress partition pruning and degrade query latency on large fact tables.

Mitigation : Cluster entitlement tables effectively, minimize the number of columns in the mapping table, and consider using session variables or role-name string patterns if subqueries cause query bottlenecks.

3. Policies That Reference Masked Columns ( EXEMPT_OTHER_POLICIES )

A column can have only one masking policy set directly at a time.

By default, a row access policy or a conditional masking policy cannot reference a column that is already protected by a masking policy.

To allow it, create or alter that column's masking policy with EXEMPT_OTHER_POLICIES = TRUE . The other policy then evaluates the column's real value, and the masking policy is ignored for that reference.

For external tables, EXEMPT_OTHER_POLICIES = TRUE on the policy protecting the VALUE column also lets a policy set on a virtual column override the one it inherits from VALUE .

4. Cloning and Replication Behavior

When cloning a table ( CREATE TABLE cloned_tbl CLONE source_tbl ), the policy assignments on its columns and rows are retained on the clone.

If a database or schema containing policies is cloned, the cloned objects reference the policies in the source container unless both policy and table reside in the same cloned schema.

5. There Is No "Bypass Everything" Privilege

Snowflake has no account privilege that silently exempts a role from all masking and row access policies. Exceptions are written inside each policy's logic (for example IS_ROLE_IN_SESSION('PAYROLL_ADMIN') ), which keeps them auditable.

Privileges such as APPLY MASKING POLICY and APPLY ROW ACCESS POLICY control who may attach or detach policies — a separation-of-duties control for a central governance team — not who may read unmasked data.

Policy Architecture Comparison

Security Feature Scope Policy Object Type Evaluation Order Primary Architectural Use Case

Row Access Policy (RAP) Row-Level Filtering Schema Object ( ROW ACCESS POLICY ) Evaluated 1st Multi-tenant data isolation, geographical data residency, regional sales filtering

Dynamic Data Masking (DDM) Column-Level Transformation Schema Object ( MASKING POLICY ) Evaluated 2nd PII/PCI masking (SSN, credit card, salary, email) based on role privilege

Conditional Masking Multi-Column Dependent Masking Schema Object ( MASKING POLICY ... USING ) Evaluated 2nd Masking column A based on the plaintext value of column B (e.g., country code)

Secure Views Row and Column Level Schema Object ( SECURE VIEW ) Wrapper Layer Obfuscating underlying query definition, external table virtual column security

Loading diagram... Row Access Policy and Dynamic Masking Query Execution Pipeline