Querying data protected by differential privacy¶

Join operators¶

Each join must be an equi join that uses a single operator. For example, t1.c1 == t2.c1 is supported, but col1 > col2 and col1 + 10 = col2 are not. Unconditioned joins are not supported.

Joins must use the JOIN operator. The WHERE syntax for joins is not supported. For more information about join syntax, see Implementing Joins.

Supported joins¶

Joins in a differentially private query must be one of the following:

• INNER

• { LEFT | RIGHT | FULL } OUTER

• NATURAL

Both sides of the join must have the same query pattern. For example, the following joins are supported:

Both sides are identifiers

SELECT COUNT(*)
FROM t1 INNER JOIN t2 ON t1.a=t2.a;

Copy

Both sides are subqueries

SELECT COUNT(*)
FROM (SELECT a, SUM(b) FROM t1 GROUP BY a) AS g1
    INNER JOIN (SELECT * FROM t2) AS g2
    ON g1.a=g2.a;

Copy

Joining an identifier with a subquery is currently not supported.

For information about the supported query syntax related to joins, see Query syntax.

Using entity keys in joins¶

When working with tables protected with entity-level privacy, you can minimize the amount of noise by including the entity key column as part of the join key, especially if it doesn’t semantically change the query.

For example, consider the following tables where the entity is customers:

Table	Description
customers	Customer directory, where each row is a customer and has a customer_id.
transactions	Customer transactions, where each customer can have multiple transactions.
transaction_lines	Unique items that were purchased in a transaction. There can be multiple rows in a single transaction.

If they are following best practices, the data provider has structured the data so that each of these tables has the entity key customer_id. For this data schema, each transaction line can only belong to one transaction, and each transaction can only belong to one customer. This relationship is not evident from the data itself, so without additional information the amount of noise added for differential privacy will be higher than it needs to be.

You can minimize the amount of noise by including the entity key customer_id as part of the join key, even if it is redundant. For example, joining the table transactions with transaction_lines typically only requires the join key transaction_id. However, joining on both transaction_id and customer_id will result in a lower amount of noise.

Data types and privacy domains¶

When joining two tables, the data types of the join key columns from either side must be the same. For differential privacy, the data type of a column includes whether or not it has a privacy domain.

For example, if you had a privacy-protected table transactions and an unprotected table product_lookup, and you wanted to join them on product_id, the product_id column in both tables must be the same data type (for example, a string) and must each have a privacy domain.

To meet this requirement, the administrator for the analyst might need to define a privacy domain just like the data provider defines them. For information on how to set a privacy domain for a table, see Setting a privacy domain.

Uniqueness requirement¶

Joins can potentially duplicate rows of data, which can cause the amount of noise added to a query result to become unbounded. To ensure that privacy-protected data is not duplicated in a join, the join key (that is, the columns on which the tables are joined) for privacy-protected tables must match only one record in the other table. This means that when joining with a privacy-protected table, the join key on the opposite side must be de-duplicated.

To satisfy the uniqueness requirement for joins, the query can use a GROUP BY on a subset of the join columns to group duplicate rows into one result.

For example, suppose the patients table is protected by differential privacy and the geo_lookup table is not. The analyst wants to join these two tables on zip_code so that they can filter the patients table on State. In order to ensure that the records in the privacy-protected patients table are not duplicated, the query must de-duplicate the zip_code table on the join key. This must be done explicitly even if the geo_lookup table is already unique on zip_code. This ensures that Snowflake can correctly account for privacy.

SELECT COUNT(*)
  FROM patients
  LEFT JOIN (SELECT zip_code, ANY_VALUE(state) AS residence_state
            FROM geo_lookup
            GROUP BY zip_code)
  USING zip_code
  WHERE birth_state = residence_state;

df_patients = session.table("patients")
df_geo = session.table("geo_lookup")
df_geo_deduped = df_geo.group_by("zip_code").agg(f.any_value("state").as_("state"))
df_patients.join(df_geo_deduped, on="zip_code", join_type="left")\
          .where(f.col("birth_state") == f.col("residence_state")).select(f.count("*"))

Executing transaction-level queries¶

The deduplication requirement for entity-level differential privacy does not prevent you from executing transaction-level queries. However, you must first group the data to the entity-level, and then aggregate on those groups.

For example, suppose you have a table doctor_visits and that the data provider has defined an entity key patient_id to implement entity-level privacy. A transaction-level query might be: “How many doctor visits weren’t for a regular checkup?” The following is an example of how to write this query:

SELECT SUM(num_visits)
  FROM (SELECT SUM((visit_reason<>'Regular checkup')::INT) AS num_visits
        WHERE visit_reason IS NOT NULL
        GROUP BY patient_id)
  WHERE num_visits > 0 AND num_visits < 20;

The subquery groups by patient_id to deduplicate the data. The aggregate column num_visits captures the number of visits per patient that were not for a regular checkup. The query then aggregates again on that per-patient column to get the total number of visits. Note that the WHERE clause on the outer query is required in order to specify a privacy domain on the data.