Study guide
3.4 Aggregation Policies, Projection Policies & External Tokenization
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.4 Aggregation Policies, Projection Policies & External Tokenization
Key Takeaways Explain This Topic AI
Aggregation and projection policies are Enterprise Edition, schema-level policy objects that let a data owner control how protected data can be used, even after it is shared with another account.
An aggregation policy forces queries to aggregate into groups of at least MIN_GROUP_SIZE rows or entities; smaller groups are folded into a remainder group whose GROUP BY key is NULL.
Queries on an aggregation-constrained table may use only AVG, COUNT [DISTINCT], HLL, and SUM as aggregates and cannot use window functions, recursive CTEs, or ROLLUP/CUBE/GROUPING SETS.
A projection policy lets a column be used in WHERE clauses and joins while preventing it from being projected in the final result, so a non-allowed role sees NULL in the output.
External tokenization (Enterprise Edition) stores provider-generated tokens in Snowflake and uses a masking policy that calls an external function to detokenize values only for authorized roles.
Last updated: September 2026
Why Architects Need More Than Masking
Dynamic data masking and row access policies (Section 3.1) answer two questions: which rows can a role see and what does each column value look like for that role . Data collaboration raises a third question: what kind of query is allowed at all? A retailer may be willing to let a partner count overlapping customers, but not to list them. A bank may let analysts join on a customer's email address, but never display it. Tokenization vendors may require that real card numbers never be stored in Snowflake.
The ARA-C01 blueprint (objective 1.2) lists aggregation policies , projection policies , and external tokenization alongside dynamic masking and row access policies. All of them are Enterprise Edition features and all are schema-level objects , so a central governance team can own them in a dedicated governance schema and attach them to tables owned by other teams.
- Control Question it answers Typical use
- Row access policy Which rows may this role see? Multi-tenant filtering, regional access
- Masking policy What value does this role see in this column? PII masking, partial reveal
Aggregation policy Must this query aggregate, and how large must each group be? Sharing statistics without exposing individuals
Projection policy May this column appear in the final result? Joining or filtering on identifiers without revealing them
External tokenization Should the real value exist in Snowflake at all? PCI DSS scope reduction with a tokenization provider
Secure view Can consumers see the view definition and push predicates inside it? Sharing curated datasets
Aggregation Policies
An aggregation policy makes a table or view aggregation-constrained : queries against it must aggregate data, and every group returned must include at least a minimum number of records (the minimum group size ). -- Require groups of at least 25 rows, except for the data owner's admin role
- CREATE AGGREGATION POLICY governance.policies.min_group_25
- AS () RETURNS AGGREGATION_CONSTRAINT ->
- CASE
- WHEN CURRENT_ROLE() = 'CUSTOMER_DATA_ADMIN'
- THEN NO_AGGREGATION_CONSTRAINT()
- ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 25)
- END;
- ALTER TABLE sales.public.loyalty_members
- SET AGGREGATION POLICY governance.policies.min_group_25;
- How queries behave
A query must aggregate — either with GROUP BY or with a scalar aggregate over the whole set. SELECT * fails.
Allowed aggregate functions are AVG , COUNT [DISTINCT] , HLL , and SUM .
If some groups contain fewer rows than the minimum, Snowflake combines them into a remainder group and returns its aggregate with a NULL grouping key . For example, states with too few members appear together in one row where state is NULL.
Not allowed against an aggregation-constrained table: window functions, recursive CTEs, GROUP BY ROLLUP/CUBE/GROUPING SETS , most set operators (UNION ALL is allowed if each branch meets the minimum), and correlated subqueries or lateral joins that reach into the aggregated portion.
External tables cannot be protected by an aggregation policy.
Row-level versus entity-level privacy
Without an entity key, the policy protects individual rows . If one person appears in many rows (for example, many purchases), a group of 25 rows might describe only one customer. Adding an entity key — ALTER TABLE ... SET AGGREGATION POLICY ... ENTITY KEY (customer_id) — makes the minimum group size count distinct entities instead of rows.
Architect Tip: Aggregation policies travel with shared data. A provider can share a table with an aggregation policy attached, and consumers can only run aggregate queries against it — a building block for clean-room style collaboration.
Projection Policies
A projection policy controls whether a column may appear in the final output of a query. A column with a projection policy is projection-constrained . When the active role is not allowed to project it, the column's values come back as NULL in the result, but the column can still be used in WHERE clauses, joins, and inner queries. CREATE PROJECTION POLICY governance.policies.no_project_email
- AS () RETURNS PROJECTION_CONSTRAINT ->
- CASE
- WHEN CURRENT_ROLE() = 'PRIVACY_OFFICER'
- THEN PROJECTION_CONSTRAINT(ALLOW => true)
- ELSE PROJECTION_CONSTRAINT(ALLOW => false)
- END;
- ALTER TABLE crm.public.customers
- MODIFY COLUMN email SET PROJECTION POLICY governance.policies.no_project_email;
-- A marketing analyst can match on email without ever seeing it:
- SELECT COUNT(*)
- FROM crm.public.customers c
- JOIN partner_share.public.audience a ON c.email = a.email;
Key rules:
A column can have one projection policy at a time; it can also have a masking policy, and its table can have a row access policy.
A column that cannot be projected cannot be inserted into another table and cannot be passed to an external function or stored procedure.
Projection policies cannot be tag-based , and cannot be set on external-table virtual columns or the VALUE column (use a view instead).
Because filtering is still allowed, a determined user could infer values through repeated predicates. Combine projection policies with aggregation policies when you need stronger guarantees.
External Tokenization
Tokenization replaces a sensitive value (such as a card number) with an undecipherable token. With External Tokenization (Enterprise Edition):
Data is tokenized by a third-party provider before it is loaded, so Snowflake stores only tokens.
A masking policy on the column calls an external function (through an API integration) that asks the provider to detokenize values — but only for authorized roles.
Everyone else sees the token, even ACCOUNTADMIN , unless the policy allows it. CREATE MASKING POLICY governance.policies.detokenize_pan
- AS (val STRING) RETURNS STRING ->
- CASE
- WHEN IS_ROLE_IN_SESSION('FRAUD_INVESTIGATOR') THEN governance.ext.detokenize(val)
- ELSE val -- the token itself
- END;
Because the policy is an ordinary masking policy, it can also be attached to a tag for tag-based external tokenization across many columns. The trade-offs are an external dependency (latency and availability of the provider's API) and external function costs on every detokenizing query.