Projection, aggregation and differential privacy policies in Snowflake

1.Projection policies: usable but not displayable

A projection policy is a schema-level object that decides whether a column can appear in a query's final output. A column can have only one. The signature takes no arguments and returns PROJECTION_CONSTRAINT. The body, which can include CASE expressions and context functions, returns PROJECTION_CONSTRAINT(ALLOW => true|false). It must never return NULL to block projection. A policy administrator creates the policy, for example from a proj_policy_admin role holding CREATE PROJECTION POLICY on the schema and APPLY PROJECTION POLICY on the account, then assigns it with ALTER TABLE ... MODIFY COLUMN ... SET PROJECTION POLICY.

CREATE OR REPLACE PROJECTION POLICY mypolicy AS () RETURNS  ?  -> PROJECTION_CONSTRAINT(ALLOW => false);

That flexibility is the whole design, and also its weakness. A blocked column can't be output, inserted into another table, or passed to an external function or stored procedure. But users can still target an individual with a filter. If they join the protected column to the same value in an unprotected table, they can project the value from that table. Snowflake tracks lineage through views, UDFs and CTEs, so a column derived from a constrained column stays blocked. Projection policies suit partners you already trust. To prevent leakage completely, drop the column or use differential privacy.

When a table carries all three policy types, Snowflake applies the row access policy's filters first, then checks projection constraints, then applies column masks.

Sources1

2.Aggregation policies: answers only in groups

An aggregation policy forces queries to return groups rather than individual rows. Its body can't reference UDFs, tables or views. It returns NO_AGGREGATION_CONSTRAINT() for unrestricted access, or AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => n) to set the smallest group a query may return. With MIN_GROUP_SIZE 1, every group must include at least one row from the protected table. With 0, an outer join can return groups made entirely of rows from another table. Groups that are too small are folded into a remainder group.

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

By default the policy counts records, so one person with many rows could fill a group alone. An entity key, defined when you assign the policy, changes the minimum group size to count distinct entities. Entity keys are additive.

Roles that aren't exempt must follow these query rules:

- Allowed aggregates are AVG, COUNT [DISTINCT], HLL and SUM. Any other aggregate, such as MIN or MAX, fails. - Grouping must use a plain GROUP BY or a scalar aggregate. ROLLUP, CUBE and GROUPING SETS are rejected. - Window functions, recursive CTEs, and most set operators are rejected. UNION ALL is the exception, and each group must still meet every protected table's minimum. - Correlated subqueries or lateral joins that reference the aggregating part of the query are rejected. - External functions are rejected unless another part of the query has already aggregated.

In a join, each group must take enough rows from each constrained table. Masking runs before aggregation; projection runs after it. External tables can't carry an aggregation policy.

ALTER TABLE viewership_log
  SET AGGREGATION POLICY my_agg_policy
  ENTITY KEY (first_name,last_name);

Sources234

3.Differential privacy policies and privacy budgets

Aggregation policies can be worn down by repeated queries. Differential privacy adds noise to results and caps the total privacy loss. A privacy policy has the signature AS () RETURNS PRIVACY_BUDGET. The body returns PRIVACY_BUDGET(BUDGET_NAME => ...) or NO_PRIVACY_POLICY(), never NULL, and it can branch on context functions such as CURRENT_USER, CURRENT_ROLE or CURRENT_ACCOUNT.

Setup takes three steps. Create the policy. Assign it with ALTER TABLE ... ADD PRIVACY POLICY, usually with an ENTITY KEY; a table has at most one privacy policy. Only then grant SELECT, because granting earlier would give analysts full access. Users tied to a budget can't run non-aggregated SELECTs, and they get noisy results. Exempt users get exact results, and Snowflake doesn't track their loss.

CREATE PRIVACY POLICY my_priv_policy AS () RETURNS PRIVACY_BUDGET -> CASE WHEN CURRENT_USER() = 'ADMIN' THEN NO_PRIVACY_POLICY() ELSE PRIVACY_BUDGET(BUDGET_NAME => 'analysts') END;

Naming a budget in the policy body creates it. Budgets are namespaced to their policy and then to each consumer account, so every account's cumulative loss is tracked separately. Each successful query adds its loss to that total. A query that would exceed the limit fails until the budget refreshes. Managing a budget requires OWNERSHIP of the policy. To reduce noise before you touch the budget, narrow the privacy domain on the protected columns.

ALTER PRIVACY POLICY users_policy SET BODY ->
  PRIVACY_BUDGET(BUDGET_NAME=>'analysts',
  BUDGET_LIMIT=>300,
  MAX_BUDGET_PER_AGGREGATE=>0.1);

Sources5678

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.ALTER PRIVACY POLICY ... SET BODY changes only the budget parameters you name and keeps the others.Why is that wrong?

   Any of BUDGET_LIMIT, MAX_BUDGET_PER_AGGREGATE or BUDGET_WINDOW left out of the statement reverts to its default.

   Covered in Differential privacy policies and privacy budgets

2. 2.Any aggregate function satisfies an aggregation policy, so MIN and MAX are fine.Why is that wrong?

   Only the listed aggregates are permitted. Any other aggregate makes the query fail.

   Covered in Aggregation policies: answers only in groups

3. 3.Without an entity key, an aggregation policy protects each person even when they have many rows.Why is that wrong?

   Without an entity key, the policy protects individual records, so several rows from one entity can fill a group.

   Covered in Aggregation policies: answers only in groups