BigQuery data clean rooms and analysis rules

A BigQuery data clean room lets several parties query each other's data without seeing the raw rows. Analysis rules on views enforce it, with an aggregation threshold, differential privacy or join restrictions. Rules are not a guarantee, which is why query templates exist.

A data clean room is a BigQuery sharing exchange for sensitive data. Contributors put views in it, and subscribers query them through a linked dataset. Subscribers get aggregated answers and never see the rows, because each view carries an analysis rule that BigQuery enforces on every query. A query that breaks the rule fails with an error, and one that follows it returns a result that satisfies it. Analysis rules are also a feature on their own: you can put one on a view in your own project with plain DDL, no clean room needed.

How a clean room differs from a normal exchange

There are three roles, which map onto sharing roles: the owner (Analytics Hub Admin) creates the room and decides who can contribute or subscribe, a contributor (Publisher) adds data, and a subscriber (Subscriber plus Subscription Owner) queries it. Several parties can be contributors in the same room, which is what makes it useful for joining two companies’ data.

When a contributor adds a table, the console creates a view over it with the analysis rule attached, and lists the view. The source table isn’t added. You can’t add an entire dataset, models, or routines other than query-template table functions.

The cost split is the same as in ordinary sharing: contributors pay for storage, subscribers pay for the compute of their queries. Attaching an analysis rule is free.

The four rules

An analysis rule is a JSON privacy_policy option on a view. A view carries one rule: an aggregation threshold or differential privacy, each with an optional join restriction, or a join restriction alone, which is the list overlap rule.

The privacy unit is the entity being protected, such as a customer ID. It is named by the privacy_unit_column.

Aggregation threshold

CREATE OR REPLACE VIEW mydataset.ExamView
OPTIONS (
  privacy_policy = '{"aggregation_threshold_policy": {"threshold": 3, "privacy_unit_column": "last_name"}}'
)
AS SELECT * FROM mydataset.ExamTable;

SELECT WITH AGGREGATION_THRESHOLD
  test_id, COUNT(DISTINCT last_name) AS student_count
FROM mydataset.ExamView
GROUP BY test_id;

We ran this on eleven rows of test scores for five students. Groups with fewer than three distinct students vanish from the result rather than returning an error: test C83 had only two students, so it is not in the output.

What else we saw when we tried to get around the rule:

• SELECT * FROM view fails: “You must use SELECT WITH AGGREGATION_THRESHOLD for this query because a privacy policy has been set by a data owner.”
• Grouping by the privacy unit column fails: “You cannot GROUP BY privacy unit column when using SELECT WITH AGGREGATION_THRESHOLD.”
• A query can raise the threshold with OPTIONS(threshold=5) but not lower it. With threshold=2 against a view that requires 3, the error is “Aggregation threshold is too low: 2, policy for table … requires at least 3”. With threshold=5 the query runs and returned no rows, with no error and no hint that rows were dropped.
• Only some aggregate functions work. COUNT, SUM and AVG ran; MAX failed with “max is not supported in a SELECT WITH AGGREGATION_THRESHOLD query.”
• HAVING works on the aggregated output.

Google lists these limits: you can’t put the rule on a materialized view, time travel is disabled on the view, and a query that uses the view must use the same privacy unit column.

Differential privacy

The differential privacy rule is for numeric statistics where a hard threshold isn’t enough. The policy sets a per-query epsilon ceiling, a total epsilon budget, delta values and max_groups_contributed. Subscribers write SELECT WITH DIFFERENTIAL_PRIVACY OPTIONS(epsilon=...). See differential privacy for how the clause works.

CREATE OR REPLACE VIEW mydataset.ExamView
OPTIONS (
  privacy_policy = '{"differential_privacy_policy": {
    "privacy_unit_column": "last_name",
    "max_epsilon_per_query": 10.01, "epsilon_budget": 25.6,
    "delta_per_query": 0.01, "delta_budget": 0.1,
    "max_groups_contributed": 2}}'
)
AS SELECT * FROM mydataset.ExamTable;

Here is what BigQuery did when we queried it:

• Right after we created the view, the first queries failed with “Privacy budget not initialized for all tables in query. There may be a privacy budget operation that is processing.” A few seconds later they ran.
• epsilon=1e20 was refused: “Epsilon is too high: 1e+20, policy for table … allows max 10.01”.
• With epsilon=10, the first two queries ran. The third failed with “Privacy budget is not sufficient for table … in this query”, because 30 is more than the budget of 25.6. Google says a spent budget can’t be refilled: you create a new view.
• Passing delta in the query is rejected on a view with this rule (“Unsupported option for SELECT WITH DIFFERENTIAL_PRIVACY: delta”). The delta comes from the policy.
• CLAMPED BETWEEN inside AVG was rejected too (“not allowed in the function call arguments”), so subscribers can’t set their own bounds. On our five-student table the noised averages came back as 0.0. Expect noisy, even useless, answers on small data.
• A plain SELECT against the view fails with the same “you must use” error as the threshold rule.

List overlap and join restrictions

A join restriction controls which columns the view may be joined on:

With only a join restriction on the view you have a list overlap rule. This is the “which of my customers are also yours?” case: the subscriber can read rows from the view only for keys that match their own table.

CREATE OR REPLACE VIEW mydataset.ExamView
OPTIONS (
  privacy_policy = '{"join_restriction_policy": {"join_condition": "JOIN_ALL", "join_allowed_columns": ["test_score", "last_name"]}}'
)
AS SELECT * FROM mydataset.ExamTable;

We joined that view to a five-row student table. Joining on last_name alone failed with “Joining must occur on all of the following columns [test_score, last_name] on table …”. So did reading the view without a join. Joining on both columns returned five rows: only the exam rows that match a student row exactly. Hansen has three exam rows in the view and one came back, the one whose score matches.

JOIN_BLOCKED is not valid alone: creating a view with only that policy failed with “JOIN_BLOCKED requires an aggregation threshold or differential privacy analysis rule.” Combined with an aggregation threshold it worked, and a query that joined the view failed with “Join occurring on a restricted column”.

Two more limits from Google’s page. Once you combine a join restriction with another policy, you can’t change the other policy. And you can’t switch a view between aggregation threshold and differential privacy: we tried, and ALTER VIEW failed with “Cannot change from aggregation threshold to differential privacy analysis rule.”

Rules are not a guarantee

Google’s own documentation says analysis rules “might not fully prevent sophisticated queries from extracting unauthorized data”. They block raw SELECT *, but Google doesn’t promise more than that, and recommends query templates when you need to control exactly what runs.

Two more places where raw data leaks:

• Analysis rules can be set only on views. If a contributor adds a table, a materialized view, or a view without a rule, subscribers get the raw data. The console shows a warning, and you can still confirm.
• Listing a resource gives every current and future subscriber access to it.

Query templates

The stronger control is a query template: the contributors agree on queries in advance and publish them as table-valued functions. A subscriber can only call the function with their own table as the argument and gets back what the function returns, never the shared tables or the function body.

query_template1(t1 TABLE<year INT64>) AS (
  SELECT * FROM `project_id.dataset_id.table_id` WHERE year = t1.year
)

A template has a creator, who writes it, and an approver, the owner of the data, who must approve it before it can be used. It starts as a draft and can only be edited while it is a draft. A template can reference at most two data sources (the function’s own query and the table parameter), and all tables inside the function must belong to the same party. These examples are not run: they need an exchange, which we didn’t create.

Matching customers without a shared ID

Two parties often have no common identifier. BigQuery’s entity resolution framework calls remote functions that run an identity provider’s matching in the provider’s own environment, and the provider writes the results to a dataset in your project. You can use it to match records before contributing them to a room. It is available on on-demand pricing and every edition. It is a separate setup from the clean room itself.

Limits that apply

• 100 shared resources per room (Google gives a contact address to ask for more).
• Rooms exist only in regions where BigQuery sharing is available, and listings can’t span multiple regions.
• Everything on the sharing limits list applies, for instance 1,000 linked datasets per shared dataset.
• Subscriber email logging, if you want to know who ran which query, has to be turned on at creation and can’t be turned off.

Deleting a room or listing unlinks all subscribers, and their queries start to fail.