Snowflake Aggregation Policies: Enforce Aggregation and Protect Privacy

Aggregation Policy is a Snowflake data governance feature that enforces that every aggregation group in a query result contains at least a minimum number of rows (MIN_GROUP_SIZE). It structurally eliminates the risk of inferring individual or small-group data and is available on Enterprise Edition and above.

Why Enforce Aggregation?

When you aggregate data with GROUP BY in analytics, if a specific group has only 1-2 rows, the aggregate value can reveal an individual. For example, if the group "region=Hokkaido, age=65+, condition=diabetes" has only one row, the average or sum directly represents that individual's value. Aggregation Policy prevents these Small Group Attacks.

CREATE AGGREGATION POLICY Syntax

-- Basic Aggregation Policy
CREATE OR REPLACE AGGREGATION POLICY privacy_db.policies.min_group_5
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

-- Conditional policy: vary aggregation constraint by role
CREATE OR REPLACE AGGREGATION POLICY privacy_db.policies.role_based_agg
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    CASE
      WHEN CURRENT_ROLE() IN ('DATA_SCIENTIST', 'ADMIN')
        THEN AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 0)
      WHEN CURRENT_ROLE() = 'ANALYST'
        THEN AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5)
      ELSE
        AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 20)
    END;

The policy body is written as a SQL expression that returns AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => N). Setting MIN_GROUP_SIZE to 0 disables the aggregation constraint (raw data access becomes possible). By using CURRENT_ROLE() or IS_ROLE_IN_SESSION() inside a CASE expression, you can switch the constraint level per role.

Applying to Tables and Views

-- Apply Aggregation Policy to a table
ALTER TABLE medical_db.public.patient_records
  SET AGGREGATION POLICY privacy_db.policies.min_group_5;

-- Apply with ENTITY KEY (enforce aggregation per entity)
ALTER TABLE medical_db.public.patient_records
  SET AGGREGATION POLICY privacy_db.policies.min_group_5
  ENTITY KEY (patient_id);

-- Also applicable to views
ALTER VIEW medical_db.public.patient_summary
  SET AGGREGATION POLICY privacy_db.policies.min_group_5
  ENTITY KEY (patient_id);

-- Unset the policy
ALTER TABLE medical_db.public.patient_records
  UNSET AGGREGATION POLICY;

The Role of ENTITY KEY

When you specify ENTITY KEY, MIN_GROUP_SIZE is evaluated against the "number of unique entities" rather than "row count". For example, with ENTITY KEY (patient_id) and MIN_GROUP_SIZE=5, the query is rejected unless each group contains at least 5 distinct patients. Even if a single patient has multiple rows, what matters is patient count, not row count.

Query Behavior After Policy Application

-- Example queries on a policy-applied table
-- MIN_GROUP_SIZE=5 / ENTITY KEY=patient_id

-- OK: succeeds if each region has 5+ patients
SELECT region, AVG(age) AS avg_age, COUNT(*) AS cnt
FROM medical_db.public.patient_records
GROUP BY region;

-- NG: the entire query errors out if any region has fewer than 5 patients
-- Error: Aggregation policy violation

-- Workaround: exclude small groups with HAVING
SELECT region, AVG(age) AS avg_age, COUNT(*) AS cnt
FROM medical_db.public.patient_records
GROUP BY region
HAVING COUNT(DISTINCT patient_id) >= 5;

-- Non-aggregated SELECT * is blocked (when MIN_GROUP_SIZE > 0)
SELECT * FROM medical_db.public.patient_records;
-- Error: Query does not contain an aggregate function

Governance Policy Comparison

Policy Management SQL

-- List defined policies
SHOW AGGREGATION POLICIES IN SCHEMA privacy_db.policies;

-- Describe a policy
DESCRIBE AGGREGATION POLICY privacy_db.policies.min_group_5;

-- Check where a policy is applied
SELECT *
FROM TABLE(
  INFORMATION_SCHEMA.POLICY_REFERENCES(
    POLICY_NAME => 'privacy_db.policies.min_group_5'
  )
);

-- View all policy references in Account Usage
SELECT policy_name, ref_entity_name, ref_entity_domain
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE policy_kind = 'AGGREGATION_POLICY';

-- Drop a policy (must be unset from objects first)
DROP AGGREGATION POLICY privacy_db.policies.min_group_5;

Required Privileges

Design and Operational Best Practices

• Centralize policies in a dedicated schema: consolidate all Aggregation Policies in a schema like privacy_db.policies and grant APPLY privilege only to specific roles
• Always specify ENTITY KEY: row-count-based protection is weakened by duplicate records, so specify the entity's primary key
• Validate MIN_GROUP_SIZE in a test environment: investigate in advance how many existing queries will violate the policy and notify the analytics team
• Roll out gradually with conditional policies: exclude the ADMIN role with MIN_GROUP_SIZE=0 first, then apply constraints to analyst roles and roll out in phases
• Combine with Masking Policy and Row Access Policy: beyond aggregation constraints, layer in masking of sensitive columns and row-level filters to build defense in depth

Check Your Understanding

Frequently Asked Questions