Implementing entity-level privacy with aggregation policies¶

Aggregation policies without entity-level privacy¶

By default, aggregation policies require analysts to run queries that aggregate data rather than retrieving individual rows, thereby achieving row-level privacy. However, row-level privacy does not prevent a query from exposing attributes of an entity when those attributes are found in multiple rows (for example, in a table containing transactional data).

For example, suppose a streaming service, ActonViz, has a transactional table that contains the email address (user_id) and household (household_id) of each viewer as they watch shows.

ActonViz can use an aggregation policy to force the advertisers to aggregate data into groups that contain at least 2 records. This prevents the advertisers from retrieving data from an individual record (row-level privacy). If each viewer and household only appeared once in the table, that would be enough to protect their privacy.

However, an advertiser’s query could still learn information about both viewers and their households. A query could create a group that consists entirely of records from household 12345 or, even worse, a group that consisted entirely of records for viewer dave_sr. In both cases, the number of records in the group would meet the requirements set by ActonViz (minimum of 2 records per group).

Aggregation policies with entity-level privacy¶

To achieve entity-level privacy, Snowflake allows you to specify an entity key when assigning an aggregation policy to a table or view. After the entity key is defined, the groups returned by a query against an aggregation-constrained table or view must contain the specified number of entities, not just a specified number of rows.

In the preceding example, suppose ActonViz defines household_id as the entity key because it uniquely identifies each household. The privacy of each household is now preserved. Before the change, a group could consist entirely of records where household_id = 12345, but now it must contain at least two distinct values of household_id.

Note that the entity key is not always the same as the primary key of a table. In this example, the table might use user_id as the primary key because it uniquely identifies a viewer. But in this case, ActonViz wants to protect the privacy of an entire household, which consists of multiple viewers, so they chose household_id as the entity key.

Specify the minimum number of entities¶

The syntax for creating an aggregation policy with CREATE AGGREGATION POLICY does not change if you are using an entity key to achieve entity-level privacy. You still use the MIN_GROUP_SIZE argument of the AGGREGATION_CONSTRAINT function to specify a minimum group size. As soon as you define an entity key, the minimum group size changes from a requirement on the number of records in a group to the number of entities in a group.

For example, the following code creates an aggregation policy that has a minimum group size of 5. As long as you define an entity key when assigning the policy to a table, each aggregation group must contain at least 5 entities.

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

For complete details about creating aggregation policies, including an example of a conditional aggregation policy that enforces different restrictions under different circumstances, see Create an aggregation policy.

Define an entity key¶

You define an entity key for a table when you assign the aggregation policy to the table or view. You can define the entity key when creating a new table or view, or when updating an existing table of view.

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

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