Implementing entity-level privacy with aggregation policies¶

About entity-level privacy¶

An entity refers to a set of attributes that belong to a logical object (for example, a user profile or household information). These attributes can be used to identify an entity within a dataset. Entity-level privacy is a feature of privacy-enhancing technologies (PET) that protects the privacy of an entity that is stored in a shared dataset. It ensures that queries cannot expose sensitive attributes of an entity, even if those attributes are found in multiple records.

To achieve entity-level privacy, Snowflake allows you to specify which columns identify an entity (an entity key). This lets Snowflake identify all of the records that belong to a particular entity within a dataset. For example, if the entity key is defined as the column email, then Snowflake can determine that all records where email=joe.smith@example.com belong to the same entity.

When you define multiple entities for a table, the aggregation policy is evaluated separately for each entity key.

The policy is applied to a query even if the key columns do not appear in the query. For example, given a policy that applies to entity key (user_id), the query SELECT age FROM T1 GROUP BY age; will still apply the min_group_size restriction for user_id in each group, although user_id does not appear in the query.

About minimum group sizes¶

Every aggregation policy specifies a minimum group size. Without entity-level privacy, the minimum group size defines the number of records that must be included in an aggregation group. When an entity key is specified, the minimum group size defines the minimum number of unique entities that must appear in the group to allow it to appear in final results. Remember that aggregation functions such as SUM and AVG return one group, whereas GROUP BY columns return one group per unique value in the grouped columns.

The following column-level policies do not affect how Snowflake calculates whether there are enough entities in an aggregation group:

• Projection policies are enforced after aggregation policies.

• Masking policies are enforced before aggregation policies. Any aggregation functions or policies work on masked data.

In cases where name references are used several times (for example, in JOIN or UNION operators), Snowflake enforces the minimum group size for each name reference of each dataset separately. This applies even when the reference points to the same dataset several times.

Enforce entity-level privacy with aggregation policies¶

To enforce entity-level privacy with aggregation policies, do the following:

1. When executing the CREATE AGGREGATION POLICY command to create the aggregation policy, specify the number of entities that must be included in each aggregation group.

2. Define the entity key when assigning the aggregation policy to a table or view.

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

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

ALTER TABLE transactions ADD AGGREGATION POLICY ap ENTITY KEY (user_id, user_email);
ALTER TABLE transactions ADD AGGREGATION POLICY ap ENTITY KEY (vendor_id);

ALTER TABLE transactions ADD AGGREGATION POLICY ap ENTITY KEY (user_id) ENTITY KEY (vendor_id);

CREATE TABLE t1
  WITH AGGREGATION POLICY my_agg_policy
  ENTITY KEY (first_name,last_name);

Deferred aggregation policies¶

If a query has subqueries, Snowflake will attempt to enforce any entity aggregation policies on the innermost query. If that query has a GROUP BY clause, and the GROUP BY columns match the entity key for an aggregation policy, that aggregation policy will not be applied to that subquery but to the parent query of that subquery. This deferment continues up the chain until either a query is reached that doesn’t have a set of GROUP BY columns that match the entity key of the policy, or until the top-level query is reached; in either case, the aggregation policy will be applied to that query. An aggregation policy is applied only once in a query chain.

For example, suppose you have an aggregation policy my_agg_policy with entity key (name, zipcode). In the following pseudo query, the inner query has a GROUP BY set that matches the entity key for my_agg_policy, and so the policy is deferred to its parent. The policy is applied at the parent because it is a top-level query, even though the GROUP BY columns also match the policy columns.

SELECT age, name, zipcode FROM(                        -- Outermost query: my_agg_policy enforced.
  SELECT name, zipcode FROM T GROUP BY name, zipcode   -- Matches my_agg_policy entity key: my_agg_policy deferred
)
  GROUP BY age, name, zipcode;

Note that GROUP BY columns can be a superset of the entity key columns to trigger a deferment, and policies are deferred only when GROUP BY columns are matched; aggregation functions do not trigger deferment.

Each aggregation policy is applied separately to all query blocks in the query. A query comprised of multiple blocks through a set operator (such as UNION) will evaluate the aggregation policies separately for each query block.

Aggregation deferment has some useful effects, demonstrated in the following example.

WITH bucketed AS (
  SELECT
    CASE
      WHEN SUM(transaction_amount) BETWEEN 0 AND 100 THEN 'low'
      WHEN SUM(transaction_amount) BETWEEN 101 AND 100000 THEN 'high'
    END AS transaction_bucket,
    zipcode,               -- zipcode and email need not appear in the select list, but this lets us compute entity_count below
    email
  FROM my_transactions
  GROUP BY zipcode, email  -- This would not work if it was only GROUP BY zipcode, since the entity key is (zipcode, email)
)
SELECT
  transaction_bucket,
  COUNT(DISTINCT zipcode, email) AS entity_count
FROM
  bucketed
GROUP BY transaction_bucket;

Removing entity key constraints¶

To remove an aggregation policy for a single entity key:

-- Drop agg policy ap associated with entity key user_id
ALTER TABLE transactions DROP AGGREGATION POLICY ap ENTITY KEY (user_id)

To remove an aggregation policy for multiple entity keys, remove each policy separately:

-- Drop the agg policies associated with two separate keys
ALTER TABLE transactions DROP AGGREGATION POLICY ap ENTITY KEY (user_id)
ALTER TABLE transactions DROP AGGREGATION POLICY ap ENTITY KEY (vendor_id)

To remove an aggregation policy together with all its entities, omit ENTITY KEY from the DROP statement:

-- Drop agg policy ap from the table entirely
ALTER TABLE transactions DROP AGGREGATION POLICY ap

Restrictions¶

The following restrictions apply when working with tables that have multiple entity keys or aggregation policies defined:

• An entity key may be associated with at most one policy. Attempting to assign another policy for an entity key that is already mapped to a policy will result in an error.

• A policy cannot be used for both row-level privacy and entity-level privacy.

• At most one policy may be used for row-level privacy. Attempting to assign another policy as the row-level aggregation policy will result in an error.

Querying an aggregation-constrained table¶

The requirements for querying an aggregation-constrained table that has an entity key is the same as querying a table without one. For information about what types of queries conform to these requirements, see Query requirements.