Skip to main content

Implementing a Snowflake Aggregation Policy (the practical way)

A Snowflake Aggregation Policy lets you force queries to return only aggregated results (with a minimum group size), preventing access to individual records even after you share data. They’re schema-level objects that you assign to tables or views; once assigned, those objects become aggregation-constrained.

We use Aggregation policies when working with tables that may contain PII. Applying aggregation policies ensures non-privileged users can analyze aggregate data without accessing individual users’ data. In particular, this is extremely useful for Snowflake Semantic Views where we never want to expose PII to LLMs

What you’ll need (quick checks)

  • Edition: Enterprise (or higher). Read more in Snowflake Docs
  • Privileges: CREATE AGGREGATION POLICY on the schema to create; APPLY (or account-level APPLY AGGREGATION POLICY) plus table/view ownership to set/unset. The docs include two sample grant patterns (centralized vs. hybrid).

Step 1 — Create the Snowflake Aggregation Policy

Every policy returns an AGGREGATION_CONSTRAINT (or, when allowed, NO_AGGREGATION_CONSTRAINT). The simplest policy enforces a fixed minimum group size:

CREATE AGGREGATION POLICY ap_min5
AS () RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

You can also add conditional logic (e.g., permit admins to bypass):

CREATE OR REPLACE AGGREGATION POLICY ap_conditional
AS () RETURNS AGGREGATION_CONSTRAINT ->
  CASE
    WHEN CURRENT_ROLE() = 'ADMIN' THEN NO_AGGREGATION_CONSTRAINT()
    ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5)
  END;

Step 2 — Assign the policy to a table or view

Apply to an existing table (or view) with ALTER … SET AGGREGATION POLICY:

ALTER TABLE sales_fact
  SET AGGREGATION POLICY ap_min5;

From that moment, queries must aggregate results and satisfy the minimum group size constraint to return anything but NULL (Snowflake may also collapse undersized groups into a remainder group with a NULL group key).

(Optional) Step 3 — Enforce entity-level privacy

Row-level privacy (above) limits exposure of records. If one entity spans many rows (e.g., multiple transactions per customer), switch to entity-level privacy by defining an entity key when you set the policy:

ALTER TABLE sales_fact
  SET AGGREGATION POLICY ap_min5
  ENTITY KEY (customer_id);

Now the minimum group size applies to distinct entities (e.g., at least 5 unique customer_id values per result group), even if the key isn’t used in the query. You can define multiple entity keys and drop them selectively later.

Step 4 — Verify queries behave as expected

Snowflake enforces two things:

  1. The query must aggregate.
  2. Each result group must include at least MIN_GROUP_SIZE rows (row-level) or entities (entity-level).

Some important limitations while a protected object is in play:

  • Use GROUP BY (no ROLLUP, CUBE, GROUPING SETS).
  • Many set operators are disallowed (exception: UNION ALL with per-source group-size compliance.
  • No window functions, recursive CTEs, or certain correlated/lateral patterns touching the constrained portion.
  • If a protected column also has a projection policy, you can’t use it in COUNT(...).

Good patterns (recommended) for Snowflake Aggregation Policy

Start with a small, safe MIN_GROUP_SIZE (e.g., 5–10), validate BI use cases still work, then raise if needed.

Prefer entity keys when multiple rows can reveal a single person/household/account, not just a row.

Combine with other policies intentionally: row access/masking / projection enforced at different phases; know their ordering interactions.