3.4 Aggregation Policies, Projection Policies & External Tokenization

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.

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):

1. Data is tokenized by a third-party provider before it is loaded, so Snowflake stores only tokens.
2. 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.
3. 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.