BigQuery Data Clean Rooms: Sharing Sensitive Data Without Copying It

A retailer and a CPG brand both want to know whether a joint promotion worked. Neither can hand the other a customer list. The usual outcome is a six-week legal negotiation, a hashed-email file dropped in a bucket, and an analysis nobody fully trusts.

BigQuery data clean rooms exist for exactly this standoff. They sit on top of BigQuery sharing (the platform formerly called Analytics Hub), and they let two or more parties join and analyse each other's data without either side getting raw row access, and without a single byte being copied.

This tutorial walks the whole loop: publish a resource, attach an analysis rule, subscribe, query under the rule, and monitor usage. It also covers the limitations that quietly turn a "clean room" into an ordinary shared dataset if you get one step wrong.

Sharing vs. clean rooms: what's actually different

BigQuery sharing already lets you publish a dataset as a listing in an exchange; a subscriber gets a linked dataset in their own project, which is a read-only symbolic link to your storage. No copy, no transfer job, no staleness. Subscribers pay for the compute they run; you pay for the storage.

A data clean room is a special kind of exchange, created with a DCR configuration, that adds three things on top:

	Standard sharing	Data clean room
Subscriber sees raw rows	Yes	No, if analysis rules are set
Analysis rules (aggregation threshold, differential privacy, list overlap)	No	Yes
Data egress controls (block copy/export of results)	No	Yes
Query templates (only pre-approved queries run)	No	Yes
Usage visibility for the publisher	Listing metrics	Per-job usage, optionally with subscriber email

Three roles matter. The clean room owner manages membership and visibility (needs roles/analyticshub.admin). Data contributors publish listings into the room. Subscribers subscribe and query. In most two-party deals each side is owner of its own room, or contributor to a shared one.

Step 1: enable sharing and create the room

gcloud services enable analyticshub.googleapis.com

Then create the room. The console has a Create clean room button on the Sharing (Analytics Hub) page; the API call is the part worth knowing, because it is the flag dcr_exchange_config that makes an exchange a clean room:

curl -H "Authorization: Bearer $(gcloud auth print-access-token)" \
  -H "Content-Type: application/json" -X POST \
  "https://analyticshub.googleapis.com/v1/projects/PROJECT_ID/locations/us/dataExchanges?data_exchange_id=joint_promo_room" \
  -d '{
        "display_name": "Joint Promo Clean Room",
        "sharing_environment_config": {"dcr_exchange_config": {}},
        "log_linked_dataset_query_user_email": true
      }'

Two decisions you cannot easily undo later:

• Location. A clean room can only list resources in its own region. Multi-region listings are not supported inside clean rooms. Pick the region your source tables already live in.
• Subscriber email logging. Once enabled and saved, it cannot be turned off without deleting and recreating the room. Turn it on. The principal shows up in job_principal_subject in INFORMATION_SCHEMA.SHARED_DATASET_USAGE, and you will want it the first time someone asks "who ran that?"

Step 2: publish a view, never a table

This is the single most common mistake, and the docs are blunt about it: analysis rules can only be set on views. If a contributor shares a table, a materialized view, or a view with no analysis rule, subscribers get raw access to everything in it. The clean room branding does not protect you; the rule does.

So build a deliberate sharing view over the underlying table, projecting only the columns the deal covers:

CREATE OR REPLACE VIEW promo.shared_transactions AS
SELECT
  customer_hash,
  store_region,
  product_category,
  DATE_TRUNC(txn_ts, WEEK) AS txn_week,
  basket_value
FROM promo.transactions_raw
WHERE txn_ts >= '2026-01-01';

Step 3: attach an analysis rule

Rules are set as a privacy_policy JSON string in the view options (or through the clean room UI). Three rules are supported.

Aggregation threshold

The workhorse. It forces every output row to be backed by at least N distinct privacy units — usually distinct customers — and it silently drops rows that fall short.

CREATE OR REPLACE VIEW promo.shared_transactions
OPTIONS (
  privacy_policy = '''{
    "aggregation_threshold_policy": {
      "threshold": 50,
      "privacy_unit_column": "customer_hash"
    },
    "join_restriction_policy": {
      "join_condition": "JOIN_ANY",
      "join_allowed_columns": ["customer_hash"]
    }
  }'''
) AS (
  SELECT customer_hash, store_region, product_category,
         DATE_TRUNC(txn_ts, WEEK) AS txn_week, basket_value
  FROM promo.transactions_raw
  WHERE txn_ts >= '2026-01-01'
);

The privacy unit column can never be projected directly — only aggregated. The join restriction policy is what stops a subscriber joining on store_region to slice their way down to individuals; JOIN_ANY requires at least one allowed column in the join, JOIN_ALL requires all of them, JOIN_BLOCKED forbids joins entirely.

Existing views can be amended without a rebuild:

ALTER VIEW promo.shared_transactions
SET OPTIONS (privacy_policy = '{"aggregation_threshold_policy": {"threshold": 100, "privacy_unit_column": "customer_hash"}}');

Differential privacy

Adds calibrated noise and enforces a privacy budget: once cumulative epsilon or delta is spent, the subscriber simply cannot query the view again. Right for published statistics and audience insights; wrong for anything a finance team has to reconcile, because the numbers move.

List overlap

Lets subscribers see overlapping rows only after a conforming join. This is the audience-matching rule: "tell me the size and shape of our shared customers, nothing about the rest."

Step 4: subscribe and query under the rule

The subscriber accepts the invitation and gets a linked dataset in their own project. Queries against a threshold-protected view must use the AGGREGATION_THRESHOLD clause and a supported aggregate function:

SELECT WITH AGGREGATION_THRESHOLD
  s.store_region,
  s.product_category,
  COUNT(DISTINCT s.customer_hash) AS customers,
  AVG(s.basket_value)             AS avg_basket
FROM linked_promo.shared_transactions AS s
JOIN my_brand.exposed_audience       AS a
  ON a.customer_hash = s.customer_hash
GROUP BY s.store_region, s.product_category;

Region/category pairs backed by fewer than 50 distinct customers disappear from the result. They are not zero, and they are not an error — they are absent. Explain that to stakeholders before the first readout, or someone will "reconcile" the missing rows against their own totals and declare the clean room broken.

Drop the WITH AGGREGATION_THRESHOLD clause, or reach for SELECT *, and the query fails outright. That is the rule doing its job.

Step 5: lock down egress and, if it matters, queries

Egress controls stop subscribers copying or exporting the linked data, and can be extended to block export of results. Turn them on for any deal where the counterparty's incentive is to accumulate rows rather than answer a question.

Where the stakes are higher still, use query templates: the room owner publishes table-valued functions representing pre-approved analyses, and subscribers get nothing but those functions. Google's own caution is worth repeating — analysis rules are privacy controls, not a guarantee against a determined adversary composing many narrow queries. Query templates are the answer to that, because nothing unapproved ever runs.

Step 6: monitor

Publishers can see usage per job:

SELECT
  job_start_time,
  job_principal_subject,
  data_exchange_id,
  listing_id,
  job_id
FROM `region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGE
WHERE job_start_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
ORDER BY job_start_time DESC;

Two patterns are worth alerting on: a sharp rise in query volume from one subscriber, and repeated near-threshold queries that differ only in their filter predicates. Both are the signature of someone probing rather than analysing.

Limitations to design around

• Analysis rules apply to views only — not tables, not materialized views.
• You cannot publish a whole dataset into a clean room, nor models or routines (query templates aside).
• 100 shared resources per clean room.
• Subscribers cannot query INFORMATION_SCHEMA on linked datasets, and shared resources are not discoverable in the catalogue.
• Clean rooms only exist in BigQuery sharing regions, and everything must be co-located.
• Deleting a clean room breaks every downstream linked dataset. Treat it as a production dependency with a named owner.

A sane rollout

1. Agree the questions first, in writing, before touching any SQL. Analysis rules are the technical restatement of a commercial agreement; if the agreement is vague, the rule will be too loose.
2. Build a dedicated sharing view per counterparty. Never point a listing at a base table.
3. Set the threshold with the counterparty's legal team, not the analyst. 50 is a common starting point; regulated data often needs more.
4. Add a join restriction policy at the same time as the threshold. A threshold alone is weaker than most people assume.
5. Dry-run the whole thing internally: create a second project, subscribe to yourself, and try to break your own rule. If you can extract a single customer, so can they.
6. Turn on subscriber email logging and egress controls at creation time, because retrofitting means recreating the room.

Clean rooms are one of the few BigQuery features where the hard part is not the SQL — it is deciding what the other party is allowed to learn, and then encoding that decision precisely enough that the warehouse enforces it.

If you are negotiating a data collaboration and want the technical design pinned down before the contract is signed — or you have a clean room in production and no idea whether it is actually protecting anything — get in touch. We build and audit these with clients in retail, media, and financial services, and the first conversation is a short one.