Data Clean Rooms: Snowflake, BigQuery, Habu, AWS for Privacy-Safe Joins

data clean room is the shared-compute enclave that quietly replaced third-party cookies as the plumbing behind every serious cross-party analytics workload — and the single most under-taught building block in the 2026 senior-data-engineering interview loop. A clean room is not a database, not a warehouse, and not a data share; it is a governed compute boundary where two (or three, or ten) organisations can join their datasets on a hashed key and see only an aggregated or differentially-private output, with neither side ever laying eyes on the other's raw rows. Once you internalise the four axes — who runs the compute (partition), how privacy is enforced (differential privacy vs k-anonymity vs both), what the join key looks like (SHA-256 with per-collaboration salt), and what shape the output is allowed to take (raw / aggregated / DP-noised) — every clean-room architecture question collapses into the same decision tree.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "compare snowflake clean room and bigquery data clean room for a retailer-brand overlap analysis" or "what's your epsilon budget for a 30-day differential privacy deployment across three brands?" or "when do you reach for a habu clean room instead of building on the native cloud primitive?" It walks through why the cookie deprecation forced ad-tech, retail, and healthcare to rebuild their collaboration stack around clean rooms, the Snowflake Native App Framework that hosts clean-room logic in the provider's own account, BigQuery's Analytics Hub plus the WITH DIFFERENTIAL_PRIVACY OPTIONS query surface, the AWS Clean Rooms configuredTable + analysisRule model with cryptographic hash joins, the independent Habu / InfoSum / LiveRamp platforms that unlock cross-cloud collaboration, and the governance patterns — salt rotation, k-anon minimums, epsilon-budget accounting, output classification — that senior engineers ship into every regulated deployment. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the design axis with the optimization practice library →.

---

On this page

• Why clean rooms replaced cookies and data-lake shares
• Snowflake Data Clean Room
• Google BigQuery Data Clean Rooms
• AWS Clean Rooms + Habu and independent platforms
• Clean-room patterns and governance
• Cheat sheet — Data clean room recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why clean rooms replaced cookies and data-lake shares

From third-party cookies to privacy-safe joins — the shift that made the data clean room the default primitive for cross-party analytics

The one-sentence invariant: a data clean room is a shared, governed compute environment where two or more parties join their data on a hashed key and see only an aggregated or differentially-private output — the raw rows never leave either party's boundary, and no side ever gains a copy of the other's dataset. That definition matters because it is the exact primitive that the deprecation of third-party cookies broke a hole in the market for: for two decades, retailers and brands stitched customer identities together through the ad-tech pipes; when Chrome, Safari, and iOS shut those pipes down, the entire industry had to rebuild the "who overlapped, who converted" workflow on something that could satisfy GDPR, CCPA, HIPAA, and the growing list of state-level privacy statutes. That something is the clean room.

The four "must-answer" axes interviewers probe.

• Partition — who runs the compute? The clean room's defining trait is that neither party's raw data crosses the trust boundary. That leads to three architectures: (a) provider-runs — the provider (usually the larger dataset holder) executes the query inside its own tenant, then ships aggregates back (Snowflake Native App pattern); (b) broker-runs — an independent platform (Habu, InfoSum, LiveRamp) runs the compute in a neutral enclave; (c) cloud-runs — the cloud (AWS, BigQuery) runs the compute in a managed clean-room service where the customer chooses the region. The senior signal is naming which partition you'd pick for which regulator and why.
• Privacy — differential privacy, k-anonymity, or both? Three privacy technologies dominate the answer set: k-anonymity (every output group must aggregate at least k records — the minimum-cell-size rule); differential privacy (mathematically-provable noise is added to every output so a single record's presence or absence cannot be inferred, parameterised by an epsilon budget ε); secure multi-party computation / homomorphic encryption (rarer in production but often mentioned). The right answer usually combines k-anonymity as a hard floor with differential privacy as a probabilistic guarantee.
• Join key — how do you match records without sharing PII? The canonical answer is hashed identity keys: SHA-256 of a normalised email address (lowercase → trim → sha256) with a per-collaboration salt so hashed keys cannot be replayed across engagements. Both parties compute the hash locally; the clean room only ever sees the hashes. Deterministic identity resolvers (LiveRamp RampID, Unified ID 2.0, Google Ads Data Hub) plug in as pre-hashed key providers.
• Output policy — what shape can the answer take? The clean room enforces the output shape at the compute layer, not the presentation layer. Common rules: minimum aggregation cell (k ≥ 50), maximum result cardinality, no row-level export, differential-privacy noise floor, and output classification tags (raw / aggregated / dp-noised). The senior signal is describing the output policy as a first-class governance object, not a query template.

What actually changed in 2026 (why this topic exploded).

• Chrome third-party cookies were fully deprecated by mid-2024 across ~85% of Chrome traffic, following Safari (ITP, 2020) and Firefox (ETP, 2019). By 2026 the ad-tech industry cannot rely on shared identifiers for cross-domain measurement.
• Regulatory expansion. GDPR (EU), CCPA/CPRA (California), Colorado CPA, Virginia CDPA, Utah UCPA, Connecticut CTDPA — 2026 has ~20 state-level privacy laws in the US alone, plus emerging federal action, plus HIPAA for healthcare and GLBA for finance. Every one of them tightens what "sharing" means.
• First-party data value. Retailers, banks, and streamers realised their first-party data is the strategic asset — sharing it raw is a give-away, but they still need to collaborate (media measurement, audience overlap, propensity modelling). The clean room is the compromise: collaborate on the insight, keep the asset.
• Cloud-native maturity. Snowflake (Native App Framework, 2023), BigQuery (Data Clean Rooms GA, 2024), AWS (Clean Rooms GA, 2023, plus Clean Rooms ML in 2024) each shipped native clean-room primitives — by 2026 you don't build a clean room from scratch; you pick a platform.

Why plain data-lake shares are not enough.

• Data-lake shares leak the raw rows. Snowflake Secure Data Share, BigQuery Analytics Hub (in share-mode), Databricks Delta Sharing, Iceberg REST catalogues — all give the consumer the raw rows. That is exactly what regulated collaboration cannot do. A share is a distribution primitive; a clean room is a joint compute primitive.
• Row-level access controls only work inside a trust boundary. Snowflake row access policies and BigQuery row-level security do enforce filters, but they assume both parties trust one Snowflake / BigQuery tenant to enforce the policy. Cross-organisation collaboration usually rejects that assumption.
• De-identification is not a substitute. Hashing an email or dropping PII columns before sharing looks like a control but leaks information through joins, quasi-identifiers (zip + gender + DOB uniquely identifies ~87% of the US population — Sweeney's classic result), and re-identification attacks. A clean room prevents the raw rows crossing at all, which is the only durable defence.
• The regulatory read. GDPR Article 6 and the ICO's clean-room guidance treat provider-runs clean rooms as joint controllers or processors depending on the topology; k-anon + DP output puts the collaboration outside the definition of "personal data" for the recipient. That is the legal hook that makes clean rooms defensible.

What interviewers listen for.

• Do you say "neither party sees the other's raw rows" as the first-sentence definition? — senior signal.
• Do you name the four axes (partition, privacy, join key, output policy) as the decision framework? — senior signal.
• Do you distinguish a clean room from a secure data share? — required answer.
• Do you cite k-anonymity + differential privacy + hashed join keys as the three technologies that make it work? — required answer.

Worked example — clean room vs Snowflake Secure Data Share

Detailed explanation. A retailer wants to share aggregated purchase counts with a brand for a co-marketing measurement. Team A proposes a Snowflake Secure Data Share of the orders table filtered to the brand's SKUs; Team B proposes a Snowflake Native App clean room. Walk an interviewer through why the second is the correct 2026 answer.

• Symptom of the wrong choice. The Secure Data Share hands the brand a queryable view over the retailer's orders — the brand sees every row, every customer_id, every timestamp, and can compute any aggregate.
• The compliance question. Under GDPR Article 6, the retailer just became a data controller transferring personal data to a third party. That requires a lawful basis, a DPA, and often data subject notification.
• The strategic question. The brand now has a queryable copy of the retailer's first-party purchase data. The strategic asset walked out the door.
• The clean-room fix. A Native App clean room hosts the aggregation logic inside the retailer's account; the brand submits queries via the app; the retailer's policies enforce k-anon and output shape; nothing leaves the retailer's tenant except the aggregated numbers the brand explicitly asked for.

Question. For a retailer sharing "customers who bought brand X in the last 30 days broken down by state and week", contrast the Secure Data Share and clean-room architectures on (a) what the brand sees, (b) legal classification of the transfer, and (c) governance controls.

Input.

Aspect	Secure Data Share	Native App Clean Room
What the brand queries	Row-level orders (filtered view)	The clean-room app's sample queries
What the brand sees	customer_id, order_ts, sku, state per row	Aggregates: state, week, purchase_count
Data movement	Rows exposed to consumer account	No rows leave the provider account
Governance layer	Row access policy, masking policy	App logic + policy + output row minimum
Legal transfer	Personal-data transfer (GDPR Art. 6)	Joint compute; often outside PII definition

Code.

-- Wrong: Secure Data Share exposes rows to the consumer
CREATE OR REPLACE SECURE VIEW share_db.brand_x_orders AS
SELECT customer_id, order_ts, sku, state, gross_revenue
FROM   retailer_db.orders
WHERE  brand_id = 'BRAND_X'
  AND  order_ts >= DATEADD('day', -30, CURRENT_DATE());

CREATE OR REPLACE SHARE brand_x_share;
GRANT USAGE ON DATABASE share_db TO SHARE brand_x_share;
GRANT SELECT ON VIEW share_db.brand_x_orders TO SHARE brand_x_share;
ALTER SHARE brand_x_share ADD ACCOUNTS = brand_account;
-- Brand now has full row-level access to filtered orders.

-- Right: Native App clean room enforces aggregation and minimum cell size
CREATE APPLICATION PACKAGE retailer_cleanroom_pkg;

CREATE APPLICATION ROLE app_public;

CREATE OR REPLACE PROCEDURE app_public.brand_overlap_by_state_week(
    brand_id STRING,
    window_days INT
)
RETURNS TABLE(state STRING, week DATE, purchase_count NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
  min_cell CONSTANT NUMBER := 100;
BEGIN
  RETURN TABLE(
    SELECT state,
           DATE_TRUNC('week', order_ts)::DATE AS week,
           COUNT(DISTINCT customer_id)         AS purchase_count
    FROM   retailer_db.orders
    WHERE  brand_id = :brand_id
      AND  order_ts >= DATEADD('day', -:window_days, CURRENT_DATE())
    GROUP  BY 1, 2
    HAVING COUNT(DISTINCT customer_id) >= :min_cell
  );
END;
$$;

GRANT USAGE ON PROCEDURE app_public.brand_overlap_by_state_week(STRING, INT)
TO APPLICATION ROLE app_public;

Step-by-step explanation.

1. The Secure Data Share pattern creates a filtered view over the retailer's orders table and grants SELECT to the brand's account. The brand can now issue any query against the filtered rows, including customer-level tracking. The retailer has effectively transferred personal data.
2. The clean-room pattern (CREATE APPLICATION PACKAGE) hosts a stored procedure inside the retailer's account. The brand invokes the procedure; the procedure returns aggregates only. The brand never runs raw SQL against orders.
3. The HAVING COUNT(DISTINCT customer_id) >= :min_cell clause enforces k-anonymity at the query surface — any state/week bucket with fewer than 100 customers is dropped from the output. That is the output policy axis expressed in SQL.
4. The clean room's application role grants the brand USAGE on the procedure, not SELECT on the underlying table. Snowflake's RBAC prevents the brand from bypassing the procedure to touch raw rows.
5. Legally, the brand now receives aggregated statistics with a minimum cell size of 100 — under most European and US privacy frameworks this falls outside "personal data". The retailer keeps its first-party asset intact and the brand gets exactly the insight it needs.

Output.

Dimension	Secure Data Share	Native App Clean Room
Rows exposed to brand	Row-level orders	0
Aggregate rows returned	N/A (brand computes)	~500 (state × week buckets with count ≥ 100)
Legal classification	Personal data transfer	Non-personal aggregated statistics
Retailer asset leakage	High (raw rows in consumer tenant)	None
Time to production	1 day (view + share)	1 week (app package + policy + review)

Rule of thumb. Data shares distribute rows; clean rooms distribute answers. If the collaboration is with an external party under any regulatory umbrella (GDPR, CCPA, HIPAA, PCI), the default answer is a clean room, not a share. Shares are for internal cross-account transfers within one legal entity.

Worked example — the four axes applied to a healthcare collaboration

Detailed explanation. A pharmaceutical company and a hospital network want to measure treatment adherence for a new medication without either party seeing the other's raw patient records. This is a HIPAA-hard problem: PHI on both sides, no permissible transfer, and yet a clinically-valuable question. Walk through the four axes to design the collaboration.

• The clinical question. "Of patients prescribed our medication, what fraction refilled the prescription at least 3 times in the first 90 days, broken down by age band and comorbidity cluster?"
• The data. Pharma has prescription-issue records with hashed patient IDs. Hospital has patient demographics, adherence, and comorbidities. The join key is the hashed patient ID.
• The HIPAA constraint. The answer must be aggregated to Safe Harbor de-identification (18 identifiers removed; expert determination for low re-identification risk).

Question. For this pharma-hospital collaboration, specify each of the four clean-room axes (partition, privacy, join key, output policy) with the concrete choices you would defend to the interviewer.

Input.

Axis	Options considered	Constraint
Partition	provider-runs, broker-runs, cloud-runs	HIPAA BAA required with any processor
Privacy	k-anon only, DP only, both	Expert determination requires low re-id risk
Join key	plain hash, HMAC, salted SHA-256	Hashed patient IDs must not be replayable
Output policy	row-level, aggregate, DP-noised	Age band × comorbidity aggregate

Code.

-- Axis 1: Partition — broker-runs on AWS Clean Rooms with BAA
-- Axis 2: Privacy — k-anon 100 (Safe Harbor) + DP epsilon 1.0 (defence in depth)
-- Axis 3: Join key — HMAC-SHA-256 with per-collaboration salt
-- Axis 4: Output policy — aggregate only, minimum cell = 100, DP noise on counts

-- Both parties compute their hashed patient key before ingestion
SELECT
  ENCODE(
    HMAC_SHA256(
      LOWER(TRIM(patient_ssn_last4 || patient_dob_iso)),
      DECODE('c0ffee-collab-2026-salt-v1', 'base64')  -- shared salt
    ),
    'hex'
  ) AS patient_hkey,
  age_band,
  comorbidity_cluster,
  adherence_flag
FROM pharma.prescriptions
WHERE prescription_date BETWEEN '2026-01-01' AND '2026-03-31';

-- Inside the AWS Clean Rooms analysis rule (see H2-4 for full config):
SELECT age_band,
       comorbidity_cluster,
       COUNT(DISTINCT p.patient_hkey)                                             AS n_patients,
       AVG(CASE WHEN p.refill_count >= 3 THEN 1.0 ELSE 0.0 END)                   AS adherence_rate
FROM   pharma.prescriptions p
JOIN   hospital.demographics d ON p.patient_hkey = d.patient_hkey
WHERE  p.prescription_date >= DATE '2026-01-01'
GROUP  BY age_band, comorbidity_cluster
HAVING COUNT(DISTINCT p.patient_hkey) >= 100;
-- Differential privacy noise is applied by the AWS Clean Rooms DP add-on
-- with epsilon budget = 1.0 across the collaboration lifetime.

Step-by-step explanation.

1. Axis 1 (Partition). AWS Clean Rooms is chosen because AWS signs Business Associate Agreements (BAAs) as a HIPAA-covered processor, and the two parties can each have their data in their own AWS account without a copy transferring. Provider-runs (Snowflake Native App) would work but forces one party into the other's Snowflake tenant; broker-runs (Habu) requires the broker to sign a BAA, which most brokers do only for enterprise contracts.
2. Axis 2 (Privacy). k-anon k=100 is the hard floor — no output row summarises fewer than 100 patients. Differential privacy ε=1.0 layered on top adds calibrated noise to each count so the presence or absence of a single patient cannot be inferred. Defence in depth: k-anon protects against small-cell attacks; DP protects against differencing attacks (running two nearly-identical queries and subtracting).
3. Axis 3 (Join key). HMAC-SHA-256 with a per-collaboration salt is the strongest option. Plain SHA-256 is vulnerable to rainbow tables on known identifier populations (all US SSN suffixes fit in a small table); HMAC with a shared secret makes the hash unforgeable and non-replayable. Both parties compute the HMAC locally; the salt lives in a shared KMS key.
4. Axis 4 (Output policy). Aggregate-only, minimum cell size = 100, and the analysis-rule template restricts the query surface to SELECT age_band, comorbidity_cluster, COUNT(...), AVG(...). No SELECT patient_hkey allowed; no ungrouped result rows allowed. Output classification: dp-noised aggregate.
5. Legally, the output is de-identified aggregate statistics with a mathematically-provable privacy guarantee — Safe Harbor plus expert determination is comfortable. The BAA covers AWS as processor; each party signs a joint-controller / independent-controller MoU depending on the exact data flow.

Output.

Axis	Choice	Why
Partition	AWS Clean Rooms	BAA signed; each party's data stays in own AWS account
Privacy	k-anon k=100 + DP ε=1.0	Defence in depth: cell floor + noise
Join key	HMAC-SHA-256 + shared salt	Non-replayable, unforgeable
Output	aggregate only, cell ≥ 100	Safe Harbor + expert determination compatible
Legal	joint compute under BAA	No raw PHI transfer

Rule of thumb. For every clean-room collaboration, write down all four axes explicitly before touching any platform SQL. Nine out of ten clean-room compliance failures trace back to a fudged axis — usually a join key that turned out to be replayable, or an output policy that let a small cell through.

Worked example — quantifying a differencing attack against a weak clean room

Detailed explanation. A clean room that returns exact aggregate counts is vulnerable to a differencing attack: an attacker with side knowledge issues two nearly-identical queries and subtracts the results to infer whether a single individual is present. This example quantifies the attack for a naive clean-room design and shows how differential privacy defeats it.

• The setup. A retailer clean room returns COUNT(*) of customers who bought product X, filtered by any predicate the analyst chooses.
• The attacker knows. A specific person's name, email hash, and zip code — perhaps from a leaked breach corpus.
• The attack. Query 1: SELECT COUNT(*) WHERE email_hash != <target>. Query 2: SELECT COUNT(*). Subtract: 1 if target is present, 0 otherwise.

Question. Show the attack against an exact-count clean room, then show how LAPLACE_NOISE(ε=1.0) on the count makes the differencing attack statistically inconclusive.

Input.

Parameter	Value
True customer count (with target)	12547
True customer count (without target)	12546
Attacker's confidence threshold	95%
DP epsilon	1.0

Code.

-- Attack against an exact-count clean room:
SELECT COUNT(*) AS n_with_target
FROM   retailer.customers
WHERE  product_bought = 'X';
-- Returns 12547 exactly

SELECT COUNT(*) AS n_without_target
FROM   retailer.customers
WHERE  product_bought = 'X'
  AND  email_hash != 'ab12cd34ef56...';
-- Returns 12546 exactly

-- Difference = 1 → target is present. Attack succeeded.

-- Defence: Laplace noise with sensitivity 1, epsilon 1.0
-- Noise ~ Laplace(0, 1/epsilon) = Laplace(0, 1.0)
-- Standard deviation = sqrt(2) / epsilon ≈ 1.41

SELECT COUNT(*)
     + LAPLACE_NOISE(0, 1.0)  AS n_with_target_dp
FROM   retailer.customers
WHERE  product_bought = 'X';
-- Might return 12545, 12549, 12546, ...

SELECT COUNT(*)
     + LAPLACE_NOISE(0, 1.0)  AS n_without_target_dp
FROM   retailer.customers
WHERE  product_bought = 'X'
  AND  email_hash != 'ab12cd34ef56...';
-- Might return 12546, 12544, 12548, ...

-- Difference is Laplace(0, sqrt(2)/1.0) — indistinguishable from ±1 noise floor.
-- Attacker cannot cross the 95% confidence threshold on a single query pair.

Step-by-step explanation.

1. Without DP, the exact-count answers give the attacker a +1 difference that is a mathematical certainty — the target is in the dataset. The attack is one-shot, no side channel needed.
2. Laplace noise with scale b = sensitivity / ε = 1 / 1.0 = 1.0 is added to each count. The count COUNT(*) has sensitivity 1 (adding or removing a single record changes the count by exactly 1), so scale 1.0 gives ε=1.0 differential privacy.
3. The difference of two Laplace-noised counts is a random variable with standard deviation ~1.41. The attacker sees a difference sampled from a distribution centred at either 0 (target absent) or 1 (target present); the two hypotheses are statistically hard to distinguish from one sample.
4. To gain confidence, the attacker would need to repeat the query many times — but each repetition consumes a chunk of the epsilon budget. If the collaboration allocates ε=1.0 total, the attacker exhausts the budget before achieving 95% confidence.
5. Real DP implementations (BigQuery DIFFERENTIAL_PRIVACY, AWS Clean Rooms DP, Snowflake DP UDFs) track the epsilon budget per (query, dataset) pair automatically and reject queries once the budget is exhausted.

Output.

Query	Exact count	DP-noised count	Attacker infers
With target	12547	12547 ± Laplace(0, 1)	true / false with ~50% confidence
Without target	12546	12546 ± Laplace(0, 1)	—
Difference	1 (deterministic)	~1 ± Laplace(0, √2)	Cannot cross 95% confidence

Rule of thumb. Exact-count outputs are always vulnerable to differencing attacks. Every clean room that returns numerical aggregates should either enforce k-anon (drop small cells) and DP (noise remaining cells), or hand out only categorical outputs. If your platform doesn't support DP natively, you either add it via a UDF layer or accept the risk in writing.

Senior interview question on the clean-room primitive

A senior interviewer often opens with: "Walk me through what a data clean room actually is, why it replaced third-party cookies, and the four axes you would defend for a retailer-brand co-marketing collaboration under GDPR."

Solution Using the four-axis decision framework

-- Axis 1 — Partition
-- Choice: Snowflake Native App (provider-runs).
-- Both parties are already on Snowflake; the retailer's app runs in its own account;
-- the brand invokes procedures via a Consumer of the Native App.

-- Axis 2 — Privacy
-- Choice: k-anonymity k=50 as hard floor. DP is optional first pass;
-- add DP if the brand ever runs "small-cell probing" queries.

-- Axis 3 — Join key
-- Choice: SHA-256(LOWER(TRIM(email))) plus per-collaboration salt held in a
-- Snowflake external stage. Both parties compute the hash locally before ingest.
CREATE OR REPLACE FUNCTION cleanroom.email_hkey(email STRING, salt STRING)
RETURNS STRING
AS $$ SHA2(CONCAT(LOWER(TRIM(email)), salt), 256) $$;

-- Axis 4 — Output policy
-- Choice: aggregate only, minimum cell = 50, output tagged as
-- classification = 'aggregated-non-personal'.
CREATE OR REPLACE PROCEDURE cleanroom.overlap_by_state(salt STRING)
RETURNS TABLE(state STRING, overlap_count NUMBER)
LANGUAGE SQL
AS
$$
BEGIN
  RETURN TABLE(
    WITH retailer_h AS (
      SELECT cleanroom.email_hkey(email, :salt) AS hkey, state
      FROM   retailer_db.customers
    ),
    brand_h AS (
      SELECT cleanroom.email_hkey(email, :salt) AS hkey
      FROM   brand_share.customers   -- pushed in via a Native App Consumer share
    )
    SELECT r.state,
           COUNT(DISTINCT r.hkey) AS overlap_count
    FROM   retailer_h r
    JOIN   brand_h b ON r.hkey = b.hkey
    GROUP  BY r.state
    HAVING COUNT(DISTINCT r.hkey) >= 50
  );
END;
$$;

Step-by-step trace.

Step	Action	Effect
1	Both parties compute email_hkey(email, salt) locally	Neither party ever sees the other's raw email addresses
2	Brand loads its hashed keys into a share the Native App consumes	Only hashes cross the account boundary
3	Consumer invokes cleanroom.overlap_by_state(salt)	Query runs entirely inside the retailer's account
4	JOIN r.hkey = b.hkey matches records	Neither raw email addresses nor customer IDs appear in the join
5	HAVING COUNT(DISTINCT ...) >= 50 drops small cells	Output satisfies k-anon k=50
6	Consumer receives only (state, overlap_count) tuples with count ≥ 50	No row-level data leaves the retailer's account

After the rollout, the brand sees "overlap by state" with cells of at least 50 customers. The retailer never exports raw rows, the brand never gains a copy of the retailer's customer list, and the collaboration sits comfortably outside the GDPR definition of personal data.

Output:

Metric	Data-lake share	Native App clean room
Raw rows exposed to brand	Full filtered orders	0
Aggregate rows returned to brand	N/A	~50 (states with overlap ≥ 50)
GDPR classification	Personal data transfer	Aggregate non-personal
Retailer's first-party asset	At risk	Intact
Salt rotation	Not applicable	Quarterly per DPA

Why this works — concept by concept:

• Partition — running the compute inside the retailer's Snowflake account means the raw rows never cross a trust boundary. Snowflake Native App is the operational form; the semantic is "provider runs the SQL, consumer gets only the answer".
• Privacy — k-anonymity k=50 is a hard floor that prevents any small-cell disclosure. Defence in depth would add DP noise; the interview signal is naming both technologies and picking the cheapest one that satisfies the regulator.
• Salted hash join key — SHA-256 on lowercase-trimmed email plus a per-collaboration salt makes the hash non-replayable across engagements. Both parties compute the hash locally so no PII crosses.
• Output policy — HAVING COUNT(DISTINCT ...) >= 50 is the k-anon rule expressed in SQL. The stored procedure is the only surface the consumer can invoke; direct SELECT on the underlying tables is not granted.
• Cost — one Native App package + one procedure per query template + one salt-management runbook. The cost of not doing this is a GDPR fine (up to 4% of global revenue) and a strategic gift of the first-party asset. The clean room pays for itself the first time you avoid either.

SQL
Topic — sql
SQL clean-room and privacy-safe join problems

Practice →

Design Topic — design Design problems on cross-party analytics architectures

Practice →

---

2. Snowflake Data Clean Room

snowflake clean room runs on the Native App Framework — the provider owns the compute, the consumer sees only sample queries

The mental model in one line: a snowflake clean room is a Snowflake Native App published by the data provider, containing sample queries, restricted UDFs, row access policies, and output-minimum enforcement — the consumer installs the app in its own account and invokes procedures, but every SQL statement runs inside the provider's account against the provider's data. Every other Snowflake clean-room question is a consequence of that architecture — where the compute lives, what the consumer can and cannot invoke, and how policies enforce the output shape.

The four axes on Snowflake.

• Partition — provider-runs via Native App. The Native App Framework hosts the clean-room logic inside the provider's account. The consumer sees an "application" object in its own account but every underlying query executes on the provider's compute (a specific virtual warehouse that the provider bills). The consumer pays for its own share load; the provider pays for its own compute.
• Privacy — row access policies + masking policies + minimum output rows. Snowflake ships row access policies (per-row filter), masking policies (per-column redaction), and the classic aggregation-only pattern (HAVING COUNT(...) >= k). Differential privacy is provided by external UDFs (there is no first-party DP-noise SQL function like BigQuery's, though a preview exists in some accounts).
• Join key — hashed columns pre-computed by both parties. The typical pattern: each party runs SHA2(LOWER(TRIM(email)) || :salt, 256) in its own account and stores the hash in a column exposed to the clean room. The Native App joins on the hash column, never on the raw column.
• Output policy — sample queries + application role RBAC. The provider publishes a fixed set of sample queries (stored procedures) inside the app. The consumer's application role can only invoke those procedures; direct SELECT on the underlying tables is not granted. Output-row minimums are enforced inside each procedure.

The Native App Framework in one paragraph.

• Application Package. A container object in the provider's account that holds versions of the app (versioned SQL / procedures / policies).
• Application (installed). The consumer's local proxy — installed via a listing or a private share.
• Setup script. SQL that runs at install time in the consumer's account (typically creates a schema, defines the application role, grants USAGE).
• Consumer data mapping. The pattern by which the consumer's own data (its hashed email column) is exposed to the app — usually via a reference object (CREATE REFERENCE) that the consumer grants at install time.

Sample queries and what makes them "clean-room safe".

• Fixed query surface. Sample queries are stored procedures; the consumer sees only the procedure signatures. No ad-hoc SQL against provider tables.
• Aggregate-only return type. Sample queries return TABLE(...) shaped as aggregates; row-level return is not permitted by the review process.
• k-anon enforcement inside the procedure. HAVING COUNT(DISTINCT ...) >= k at the end of every aggregation.
• Immutable at install time. Once the consumer installs a version, the provider cannot modify the procedure behaviour without publishing a new version. The consumer chooses when to upgrade.

Row access policies and masking policies — the two secondary controls.

• Row access policy. SQL predicate attached to a table that filters rows visible to a session based on the caller's identity. In a clean room, the row access policy on the provider's table restricts what the sample query sees when the consumer invokes it.
• Masking policy. SQL expression attached to a column that returns a redacted value for unauthorised sessions. Rarely used inside clean rooms (since the consumer never touches raw columns), but the underlying table often carries masking policies for internal safety.
• Aggregation policy (preview). A newer Snowflake feature that enforces "must aggregate to at least k rows" at the table level, independent of the query. Complements the manual HAVING clause.

Provider-consumer economics.

• Provider pays for the warehouse that runs the clean-room queries. The provider chooses the warehouse size and can bill the consumer via a separate contract.
• Consumer pays for its own share load (CREATE STAGE, COPY INTO) and for any procedure invocation the app runs against the consumer's own tables via REFERENCE objects.
• Reserve pool. The provider often runs a dedicated warehouse per collaboration to isolate the workload.

Common interview probes on Snowflake clean rooms.

• "How do you make sure the consumer never sees raw rows?" — Native App runs in provider's account; consumer only invokes procedures.
• "What is a REFERENCE object?" — the mechanism by which the app is granted USAGE on a consumer-side table (typically the consumer's hashed key column).
• "How does k-anon get enforced?" — inside the procedure via HAVING COUNT(...) >= k, and (newer) via aggregation policies.
• "How does the consumer trust the provider's app?" — code review of the app package during listing publication, plus the Snowflake Marketplace security review.

Worked example — Native App clean room for retailer-brand overlap

Detailed explanation. A retailer wants to publish a clean room where a brand can compute the count of the retailer's customers who also appear in the brand's own CRM, broken down by state and week. The retailer owns the compute, the brand contributes its hashed CRM email column, and the output is aggregate-only with k=100 minimum cell size.

• Provider data. retailer.customers(customer_hkey, state, first_purchase_ts, ...). customer_hkey is the pre-computed SHA-256(email + salt).
• Consumer data. The brand's brand.crm(email_hkey, marketing_source). The brand exposes this table via a REFERENCE object at install time.
• Sample query. overlap_by_state_week(window_days INT) → TABLE(state, week, overlap_count), k=100.

Question. Write the complete Native App setup script, the sample-query procedure, and demonstrate how the consumer installs and invokes it.

Input.

Component	Value
Provider account	retailer_acct
Consumer account	brand_acct
Application package	retailer.brand_overlap_pkg
Sample query	overlap_by_state_week
k-anon minimum	100
Salt	shared secret via KMS

Code.

-- Provider account (retailer): create the application package and the app schema
USE ROLE accountadmin;
CREATE APPLICATION PACKAGE retailer.brand_overlap_pkg;

USE APPLICATION PACKAGE retailer.brand_overlap_pkg;
CREATE SCHEMA app_public;
CREATE APPLICATION ROLE app_public;

-- The setup script runs in the consumer's account at install time
CREATE OR REPLACE PROCEDURE app_public.setup(salt STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE FUNCTION app_public.hkey(email STRING)
  RETURNS STRING
  AS $$ SHA2(CONCAT(LOWER(TRIM(email)), :salt), 256) $$;
  RETURN 'ok';
END;
$$;
GRANT USAGE ON PROCEDURE app_public.setup(STRING) TO APPLICATION ROLE app_public;

-- The sample query — the ONLY entry point available to the consumer
CREATE OR REPLACE PROCEDURE app_public.overlap_by_state_week(
    window_days INT,
    consumer_ref STRING          -- REFERENCE name for the brand's crm table
)
RETURNS TABLE(state STRING, week DATE, overlap_count NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
  min_cell CONSTANT NUMBER := 100;
BEGIN
  RETURN TABLE(
    WITH brand_crm AS (
      SELECT email_hkey
      FROM   IDENTIFIER(:consumer_ref)
    )
    SELECT r.state,
           DATE_TRUNC('week', r.first_purchase_ts)::DATE AS week,
           COUNT(DISTINCT r.customer_hkey)                AS overlap_count
    FROM   retailer.customers r
    JOIN   brand_crm b
      ON   r.customer_hkey = b.email_hkey
    WHERE  r.first_purchase_ts >= DATEADD('day', -:window_days, CURRENT_DATE())
    GROUP  BY 1, 2
    HAVING COUNT(DISTINCT r.customer_hkey) >= :min_cell
  );
END;
$$;
GRANT USAGE ON PROCEDURE app_public.overlap_by_state_week(INT, STRING)
TO APPLICATION ROLE app_public;

-- Add the manifest and publish version v1
ALTER APPLICATION PACKAGE retailer.brand_overlap_pkg
  ADD VERSION v1
  USING '@retailer.stage/v1/';
ALTER APPLICATION PACKAGE retailer.brand_overlap_pkg
  SET DEFAULT RELEASE DIRECTIVE VERSION = v1 PATCH = 0;

-- Consumer account (brand): install and invoke the clean-room app
USE ROLE accountadmin;
CREATE APPLICATION brand_overlap
  FROM APPLICATION PACKAGE retailer.brand_overlap_pkg
  USING VERSION v1;

-- Bind the brand's CRM table as a REFERENCE object the app can read
CALL SYSTEM$SET_REFERENCE('BRAND_CRM_REF', 'brand.crm.customers');

-- Invoke the sample query
CALL brand_overlap.app_public.overlap_by_state_week(
  30,
  'BRAND_CRM_REF'
);

Step-by-step explanation.

1. The provider (retailer) creates an application package retailer.brand_overlap_pkg and defines an app_public schema plus an APPLICATION ROLE — this is the role the consumer will assume when invoking procedures. Nothing outside the sample-query procedures is grantable.
2. The setup procedure runs at install time in the consumer's account. It creates a UDF app_public.hkey(email) that computes the salted SHA-256 — the consumer can now pre-hash any of its own tables consistently. The salt is passed in at install time (from a KMS-backed secret, not hard-coded).
3. The overlap_by_state_week procedure is the entry point. It takes a window_days integer and a consumer_ref string that names a REFERENCE object the consumer will bind at install time to its own brand.crm.customers table.
4. Inside the procedure, IDENTIFIER(:consumer_ref) resolves to the consumer's table via the REFERENCE binding. The provider's retailer.customers is joined on customer_hkey = email_hkey. The HAVING COUNT(DISTINCT r.customer_hkey) >= 100 clause enforces k-anonymity.
5. The consumer installs the app, binds the CRM table via SYSTEM$SET_REFERENCE, and invokes the procedure. The query runs entirely inside the provider's account against the provider's data (with a limited join to the consumer's REFERENCE table). The consumer receives only the aggregated result set.

Output.

Row	state	week	overlap_count
1	CA	2026-06-15	3421
2	CA	2026-06-22	3287
3	NY	2026-06-15	2104
4	NY	2026-06-22	1994
...	...	...	...
N	WY	2026-06-15	(dropped, count = 62 < 100)

Rule of thumb. Snowflake clean rooms are the strongest fit when both parties are already on Snowflake and the collaboration is provider-heavy (one party contributes most of the data, the other contributes a small matching key). The Native App Framework is the mechanism; the sample-query surface is the governance.

Worked example — row access policy defence in depth

Detailed explanation. Even inside a Native App, a stored procedure that queries the underlying tables is just SQL — if the provider ever exposes a SELECT procedure by mistake, the consumer sees raw rows. The belt-and-braces answer is a row access policy on the underlying table that only allows the procedure to see the rows it needs. That way, even a bug in the procedure cannot leak more than the row access policy allows.

• The risk. A future provider engineer writes a "sample query" that accidentally returns row-level data. Without a row access policy, the consumer sees the leak.
• The defence. A row access policy on retailer.customers that returns FALSE unless the caller is the specific application role AND the query is going through a sanctioned procedure (tracked via a session variable).
• The result. Direct SELECT * FROM retailer.customers inside the app returns zero rows unless the sanctioned procedure sets the session variable.

Question. Add a row access policy to the retailer.customers table so the clean-room app can only see rows when a sanctioned procedure is executing. Show the policy definition and the procedure change.

Input.

Component	Value
Underlying table	retailer.customers
Policy name	retailer.customers_ra_policy
Session flag	app_query_context = 'overlap_by_state_week'
Procedure	app_public.overlap_by_state_week

Code.

-- Row access policy — only lets rows through when the session context flag is set
CREATE OR REPLACE ROW ACCESS POLICY retailer.customers_ra_policy
AS (customer_hkey STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'RETAILER_CLEANROOM_APP_OWNER'
  AND SYSTEM$GET_CONTEXT('cleanroom_flag') IN
      (
        'overlap_by_state_week',
        'overlap_by_dma_week'
      );

ALTER TABLE retailer.customers
  ADD ROW ACCESS POLICY retailer.customers_ra_policy ON (customer_hkey);

-- Update the procedure to set the session context flag at entry and clear it at exit
CREATE OR REPLACE PROCEDURE app_public.overlap_by_state_week(
    window_days INT,
    consumer_ref STRING
)
RETURNS TABLE(state STRING, week DATE, overlap_count NUMBER)
LANGUAGE SQL
AS
$$
BEGIN
  CALL SYSTEM$SET_CONTEXT('cleanroom_flag', 'overlap_by_state_week');
  BEGIN
    RETURN TABLE(
      WITH brand_crm AS (
        SELECT email_hkey FROM IDENTIFIER(:consumer_ref)
      )
      SELECT r.state,
             DATE_TRUNC('week', r.first_purchase_ts)::DATE AS week,
             COUNT(DISTINCT r.customer_hkey)                AS overlap_count
      FROM   retailer.customers r
      JOIN   brand_crm b ON r.customer_hkey = b.email_hkey
      WHERE  r.first_purchase_ts >= DATEADD('day', -:window_days, CURRENT_DATE())
      GROUP  BY 1, 2
      HAVING COUNT(DISTINCT r.customer_hkey) >= 100
    );
  EXCEPTION WHEN OTHER THEN
    CALL SYSTEM$SET_CONTEXT('cleanroom_flag', '');
    RAISE;
  END;
  CALL SYSTEM$SET_CONTEXT('cleanroom_flag', '');
END;
$$;

Step-by-step explanation.

1. The row access policy takes each row's customer_hkey and returns TRUE only when both the calling role is RETAILER_CLEANROOM_APP_OWNER and the session context flag matches one of the sanctioned procedure names. Any other query — ad-hoc, a mistake, a rogue "sample query" — sees zero rows.
2. The SYSTEM$SET_CONTEXT call at procedure entry sets the flag to the procedure's own name. The EXCEPTION handler clears the flag if anything goes wrong; the final SET_CONTEXT at the end clears it on success. The flag is never left set across procedure boundaries.
3. Even if a future engineer adds a new procedure that does SELECT * FROM retailer.customers, the row access policy filters everything out unless that procedure name is added to the whitelist inside the policy body — an explicit review gate.
4. The defence works because Snowflake's row access policies are evaluated on every row access, not once per session. The policy cannot be bypassed by session-level tricks; only by adding a new procedure name to the whitelist.
5. Operationally, the whitelist inside the policy body becomes the audit surface. Adding a procedure name = adding a sanctioned query type. The clean-room review process gains one clear artefact to review.

Output.

Query	Role	Context flag	Rows visible
CALL overlap_by_state_week(...)	app owner	overlap_by_state_week	All matching rows
SELECT * FROM retailer.customers	app owner	(none)	0
SELECT * FROM retailer.customers	analyst	(none)	0 (no grant)
CALL rogue_procedure()	app owner	(rogue not on whitelist)	0

Rule of thumb. In a production clean room, always pair a stored procedure with a row access policy that whitelists that procedure's name. The procedure defines the shape of the output; the row access policy defends against the procedure being bypassed or replaced.

Worked example — aggregation policy enforced at the table level

Detailed explanation. Snowflake ships a newer feature — aggregation policy — that enforces "the query must aggregate to at least k rows" at the table level, independent of any procedure. This is even stronger than the manual HAVING clause because it prevents accidental row-level output regardless of the query shape.

• What it does. An aggregation policy attached to a column forces every query touching that column to be a GROUP BY with at least k rows per output group. Row-level queries return an empty result.
• When to use. As a belt-and-braces enforcement for the small-cell rule. Even if a future procedure forgets the HAVING, the aggregation policy catches it.
• What it doesn't do. It doesn't add differential privacy noise; it doesn't prevent differencing attacks; it doesn't enforce cross-query epsilon budgets. It is only the k-anon floor.

Question. Define an aggregation policy on retailer.customers.customer_hkey with k = 100. Show how it interacts with a query that forgets the HAVING clause.

Input.

Component	Value
Table	retailer.customers
Column	customer_hkey
Policy k	100

Code.

-- Create the aggregation policy
CREATE OR REPLACE AGGREGATION POLICY retailer.customer_agg_policy
AS () RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 100);

-- Attach it to the sensitive column
ALTER TABLE retailer.customers
  MODIFY COLUMN customer_hkey
  SET AGGREGATION POLICY retailer.customer_agg_policy;

-- Now any query touching customer_hkey must aggregate to at least 100 rows.
-- Correct query — aggregates:
SELECT state, COUNT(*) AS n
FROM   retailer.customers
GROUP  BY state;
-- Returns rows where n >= 100; drops states with n < 100.

-- Broken query — row-level access:
SELECT customer_hkey, state
FROM   retailer.customers;
-- Fails: "Query violates aggregation policy: row-level access not permitted"

-- Also broken — small group by:
SELECT dma_code, COUNT(*) AS n
FROM   retailer.customers
GROUP  BY dma_code;
-- Rows returned only for dma_codes with n >= 100; smaller dma_codes filtered.

Step-by-step explanation.

1. The AGGREGATION POLICY is a new type of Snowflake policy object. Its body returns an AGGREGATION_CONSTRAINT — currently only MIN_GROUP_SIZE, but future extensions will add more constraints.
2. Attaching the policy to customer_hkey forces every query that reads that column to satisfy the constraint. Snowflake's query compiler inspects the query at parse time and either drops rows in small groups or errors on row-level access.
3. A correct clean-room query (SELECT state, COUNT(*) ... GROUP BY state) is compiled normally, but the result set is filtered to drop groups with fewer than 100 rows. The k-anon floor is enforced even without a HAVING clause.
4. A row-level query fails to compile — the compiler recognises that no aggregation is present and rejects the query. This is stronger than the HAVING pattern, which only filters at output time.
5. The defence-in-depth stack is now: (a) application role RBAC prevents direct SELECT grants; (b) row access policy hides rows outside sanctioned procedures; (c) aggregation policy enforces the k-anon floor at the compiler level. Any one of the three could catch a mistake alone; combined, they make a leak nearly impossible.

Output.

Query type	Aggregation policy verdict
GROUP BY state with all groups ≥ 100	Runs, all rows returned
GROUP BY state, some groups < 100	Runs, small groups filtered
Row-level SELECT	Fails: policy violation
SELECT into scratch table (row-level)	Fails: policy violation

Rule of thumb. For any column that participates in a clean-room join key, attach an aggregation policy. It closes the last "someone wrote the query wrong" attack vector and moves the k-anon guarantee from application-code discipline to compiler-level invariant.

Senior interview question on Snowflake Native App clean rooms

A senior interviewer might ask: "Design a Snowflake clean room where a media company shares aggregated audience overlap with an advertiser. Walk me through the Native App package, the sample-query surface, the row access policy, and the k-anon enforcement."

Solution Using a full Native App package with three defence layers

-- Provider: media company
USE ROLE accountadmin;

-- 1) Application package + version stage
CREATE APPLICATION PACKAGE media.audience_overlap_pkg;

-- 2) Aggregation policy on the audience hkey column
CREATE OR REPLACE AGGREGATION POLICY media.audience_agg_policy
AS () RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 100);

ALTER TABLE media.audience_events
  MODIFY COLUMN audience_hkey
  SET AGGREGATION POLICY media.audience_agg_policy;

-- 3) Row access policy scoped to sanctioned procedures
CREATE OR REPLACE ROW ACCESS POLICY media.audience_ra_policy
AS (audience_hkey STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'MEDIA_CLEANROOM_APP_OWNER'
  AND SYSTEM$GET_CONTEXT('cleanroom_flag') IN
      ('overlap_by_segment_week', 'reach_by_creative_week');

ALTER TABLE media.audience_events
  ADD ROW ACCESS POLICY media.audience_ra_policy ON (audience_hkey);

-- 4) Sample query procedures inside the package
USE APPLICATION PACKAGE media.audience_overlap_pkg;
CREATE SCHEMA app_public;
CREATE APPLICATION ROLE app_public;

CREATE OR REPLACE PROCEDURE app_public.overlap_by_segment_week(
    window_days INT,
    consumer_ref STRING
)
RETURNS TABLE(segment STRING, week DATE, overlap_count NUMBER)
LANGUAGE SQL
AS
$$
BEGIN
  CALL SYSTEM$SET_CONTEXT('cleanroom_flag', 'overlap_by_segment_week');
  RETURN TABLE(
    WITH advertiser AS (
      SELECT audience_hkey FROM IDENTIFIER(:consumer_ref)
    )
    SELECT m.segment,
           DATE_TRUNC('week', m.event_ts)::DATE   AS week,
           COUNT(DISTINCT m.audience_hkey)         AS overlap_count
    FROM   media.audience_events m
    JOIN   advertiser a ON m.audience_hkey = a.audience_hkey
    WHERE  m.event_ts >= DATEADD('day', -:window_days, CURRENT_DATE())
    GROUP  BY 1, 2
    HAVING COUNT(DISTINCT m.audience_hkey) >= 100
  );
END;
$$;

GRANT USAGE ON PROCEDURE app_public.overlap_by_segment_week(INT, STRING)
  TO APPLICATION ROLE app_public;

ALTER APPLICATION PACKAGE media.audience_overlap_pkg
  ADD VERSION v1 USING '@media.stage/v1/';

Step-by-step trace.

Layer	Control	What it catches
1	Application role RBAC	Direct SELECT grants — consumer has no SELECT on audience_events
2	Row access policy on audience_hkey	Any query outside the whitelisted procedure sees 0 rows
3	Aggregation policy on audience_hkey	Any query returning row-level data fails at compile time
4	HAVING COUNT(...) >= 100 inside procedure	Small cells filtered before return
5	Session context flag	Reset in every EXCEPTION path; can't leak across procedure boundaries

After the rollout, the advertiser installs the app, binds its own audience table via a REFERENCE object, and invokes the two sample queries. The output is aggregate rows only, with k=100 minimum cells. The three defensive layers make a leak nearly impossible even under future engineering changes.

Output:

Metric	Result
Direct SELECT by advertiser	Not grantable
Rogue procedure attempt	Row access policy filters rows
Small-cell aggregation	Aggregation policy filters cells
Sanctioned overlap query	Returns aggregate rows k ≥ 100
Auditable whitelist	One policy body per package

Why this works — concept by concept:

• Native App package as the compute boundary — the Native App Framework runs the SQL inside the provider's account. The consumer's account holds only the "application object" proxy. Raw rows cannot cross the boundary because they never leave the provider's warehouse.
• Application role RBAC — the only grantable operations are USAGE on the specific sample-query procedures. There is no path to SELECT on the underlying tables.
• Row access policy scoped by session context — the policy body evaluates the session context flag, which only the whitelisted procedures set. Bypass requires editing the policy body, which is an auditable change.
• Aggregation policy for k-anon at compile time — the newer Snowflake feature enforces the k-anon floor at the compiler, not at the query author's discipline. This is the strongest defence.
• Cost — one application package + one procedure per query template + two policies per sensitive table. The provider absorbs the warehouse cost of running the queries; the consumer absorbs the cost of hashing its own data. Runtime cost is dominated by the join; the policy overhead is negligible.

SQL
Topic — sql
SQL Snowflake clean-room and policy problems

Practice →

Design Topic — design Design problems on Native App architecture

Practice →

---

3. Google BigQuery Data Clean Rooms

bigquery data clean room runs on Analytics Hub plus WITH DIFFERENTIAL_PRIVACY OPTIONS — the strongest built-in DP surface in the industry

The mental model in one line: a bigquery data clean room is an Analytics Hub "listing" that exposes datasets to a data consumer, plus the BigQuery-native SELECT WITH DIFFERENTIAL_PRIVACY OPTIONS (epsilon = ..., delta = ..., max_groups_contributed = ...) clause that adds calibrated noise to every aggregate and rejects queries that violate the epsilon budget. That combination — Analytics Hub for the sharing surface, differential privacy for the output — is the fastest path to a defensible clean room on any hyperscaler in 2026.

The four axes on BigQuery.

• Partition — Analytics Hub with per-listing subscriber controls. Analytics Hub is BigQuery's dataset-sharing surface — the provider publishes a dataset as a listing; the consumer subscribes; queries run against the shared view. The clean-room variant adds subscriber controls, query surface restrictions, and differential-privacy enforcement.
• Privacy — first-party WITH DIFFERENTIAL_PRIVACY OPTIONS. BigQuery is unique in shipping DP as a language construct. Aggregations (ANON_COUNT, ANON_SUM, ANON_AVG, ANON_QUANTILES) automatically inject Laplace or Gaussian noise scaled to the query sensitivity and the epsilon budget.
• Join key — join by hash column materialised in the shared view. BigQuery clean rooms typically expose a hashed view of the underlying table — a CREATE VIEW ... AS SELECT SHA256(...) AS user_hkey, ... — and the join lives against that hkey.
• Output policy — DP-noised aggregates only, no SELECT *. The DP clause forces the query to be an aggregation; row-level SELECT against the DP view errors out. Column policies and column-level access controls tighten the surface further.

The Analytics Hub model.

• Data exchange. A container the provider owns; groups listings together. Providers publish exchanges to specific consumers or make them public.
• Listing. A pointer to a specific BigQuery dataset (a "shared dataset"). The listing carries description, taxonomy, subscription controls.
• Subscribed dataset. In the consumer's project, a linked dataset that points at the provider's shared dataset. Queries against the linked dataset are transparently routed to the provider's data.
• Clean-room specific controls. In "clean-room" mode, listings enforce: (a) query-surface restrictions (only DP queries allowed); (b) join-key registration (only specific columns can be used as join keys); (c) result-cardinality caps.

WITH DIFFERENTIAL_PRIVACY OPTIONS — the language surface.

• Syntax. SELECT WITH DIFFERENTIAL_PRIVACY OPTIONS(epsilon = 1.0, delta = 1e-10, max_groups_contributed = 5) segment, ANON_COUNT(*) FROM ... GROUP BY segment;.
• Epsilon (ε). The privacy budget. Smaller = stronger privacy, more noise. 1.0 is common; 0.1 is strong; 10.0 is weak.
• Delta (δ). The failure probability. 1e-10 is typical — one in ten billion chance the DP guarantee is violated.
• max_groups_contributed. The per-user contribution bound. Limits how many groups a single user contributes to, which caps the query's sensitivity.
• Aggregation functions. ANON_COUNT, ANON_COUNT_DISTINCT, ANON_SUM, ANON_AVG, ANON_QUANTILES, ANON_STDDEV_POP. All are DP-safe variants that inject noise proportional to sensitivity / ε.

Epsilon budget management.

• Per-listing budget. Each Analytics Hub listing carries a lifetime ε budget (e.g. ε=10.0 across all queries by all subscribers). Each query consumes some of the budget.
• Composition. DP composes: two queries with ε=1.0 each cost ε=2.0 total (basic composition) or less (advanced composition, sub-additive with high probability).
• Budget exhaustion. Once the budget is exhausted, the listing rejects further DP queries. The provider must publish a new listing (with a new budget) to resume.
• Practical rule. Start with ε=1.0 per query, budget for ~10 queries over the collaboration lifetime. Adjust based on real utility measurements.

Where BigQuery beats Snowflake.

• First-party DP. BigQuery is the only hyperscaler that ships DP as a language construct. Snowflake requires UDFs.
• Analytics Hub simplicity. Analytics Hub is a lighter share model than Snowflake Native App; the listing is a pointer, not an app package.
• BigLake / cross-cloud read. BigQuery Omni + BigLake makes the same DP clause work against data in AWS S3 or Azure ADLS — a niche win for cross-cloud clean rooms.

Where Snowflake beats BigQuery.

• Native App is a more expressive surface. Snowflake procedures + policies + REFERENCE objects give the provider more control over what the consumer can and cannot do. BigQuery's clean-room mode is simpler but less programmable.
• Row access + aggregation policies. Snowflake's policy stack is more mature.

Common interview probes on BigQuery clean rooms.

• "How is DP enforced in BigQuery clean rooms?" — the WITH DIFFERENTIAL_PRIVACY OPTIONS clause plus per-listing epsilon budgets.
• "What is max_groups_contributed?" — the per-user contribution cap that bounds query sensitivity.
• "How does an Analytics Hub listing become a clean room?" — clean-room subscription settings enable DP-only queries and join-key registration.
• "What if the epsilon budget runs out?" — the provider republishes with a new budget; there is no automatic top-up.

Worked example — DP-noised impression-conversion overlap

Detailed explanation. A publisher and an advertiser want to measure "how many users saw an ad AND converted, broken down by creative variant and week", without either party seeing the other's raw event stream. Publisher has impression events; advertiser has conversion events. Both expose hashed user_hkey columns. The clean room runs on BigQuery with DP epsilon 1.0.

• Publisher data. pub.impressions(user_hkey, creative_variant, event_ts).
• Advertiser data. adv.conversions(user_hkey, order_value, event_ts).
• Query. For each creative_variant × week, DP count of overlap users and DP sum of order value.

Question. Write the DP query, show the WITH DIFFERENTIAL_PRIVACY OPTIONS clause with a defensible epsilon, and quantify the noise floor.

Input.

Parameter	Value
DP epsilon	1.0
DP delta	1e-10
max_groups_contributed	5 (limit user contribution to 5 (creative × week) buckets)
Sensitivity of ANON_COUNT	1 per user per group
Expected Laplace noise scale	1 / (ε / groups) = 5.0

Code.

-- Setup: publisher shares its impressions view via Analytics Hub;
-- advertiser subscribes as a linked dataset called adv.pub_impressions

-- Clean-room DP query on the advertiser side
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(
      epsilon                = 1.0,
      delta                  = 1e-10,
      max_groups_contributed = 5,
      privacy_unit_column    = i.user_hkey
    )
  i.creative_variant,
  DATE_TRUNC(i.event_ts, WEEK) AS week,
  ANON_COUNT(*)                                              AS overlap_users,
  ANON_SUM(c.order_value CLAMPED BETWEEN 0 AND 500)          AS overlap_gmv
FROM   adv.pub_impressions       i
JOIN   adv.conversions           c
  ON   i.user_hkey = c.user_hkey
WHERE  i.event_ts BETWEEN TIMESTAMP('2026-06-01') AND TIMESTAMP('2026-06-30')
GROUP  BY 1, 2;

Step-by-step explanation.

1. The advertiser subscribes to the publisher's Analytics Hub listing; the linked dataset appears as adv.pub_impressions in the advertiser's project. Queries against it are transparently routed to the publisher's data.
2. The WITH DIFFERENTIAL_PRIVACY OPTIONS clause tells BigQuery to inject DP noise into the aggregations. The privacy_unit_column is the user_hkey — the identifier across which DP is measured (the "individual" whose presence or absence must not be inferrable).
3. max_groups_contributed = 5 bounds how many (creative_variant, week) groups a single user can contribute to. This caps the query's sensitivity; without it, a user who saw 100 impressions could dominate the noise scaling.
4. ANON_COUNT(*) adds Laplace noise scaled by sensitivity / ε = groups × 1 / 1.0 = 5.0. The output count is unbiased but noisy: expected value is the true count, standard deviation is √2 × 5 ≈ 7.07.
5. ANON_SUM(c.order_value CLAMPED BETWEEN 0 AND 500) — the CLAMPED clause bounds each contribution to [0, 500], capping sensitivity to 500 per user per group. Noise scale becomes 500 × 5 / 1.0 = 2500. For a bucket with a true GMV of $50000 that noise is ~5%; for a bucket with $500 GMV that noise dominates.

Output.

creative_variant	week	overlap_users (true)	overlap_users (DP)	overlap_gmv (true)	overlap_gmv (DP)
A	2026-06-01	4820	4826 ± 7	128400	129100 ± 2500
A	2026-06-08	5104	5099 ± 7	135700	133200 ± 2500
B	2026-06-01	3910	3915 ± 7	102300	104800 ± 2500
B	2026-06-08	4211	4207 ± 7	108900	107100 ± 2500

Rule of thumb. Pick epsilon based on the utility floor of the smallest bucket you care about. If the smallest bucket you need to measure has 500 users, epsilon=1.0 gives ~7-user noise — negligible. If the smallest bucket has 50 users, epsilon=1.0 gives ~7-user noise — 14% relative error, borderline. Push epsilon up (weaker privacy) for coarser buckets; push it down (stronger privacy) for larger buckets.

Worked example — epsilon budget accounting over a 30-day collaboration

Detailed explanation. A media collaboration runs for 30 days. Multiple queries are issued daily. The Analytics Hub listing carries an epsilon budget of 10.0 total. Show how to track budget consumption, alert before exhaustion, and plan the query allocation.

• Budget. ε=10.0 total (BigQuery listing-level).
• Composition rule. Basic composition: sum of per-query epsilons.
• Query pattern. ~1 query per day, epsilon=0.33 per query, would run out on day 30. Reserve budget for ad-hoc probes.

Question. Build a budget-accounting SQL that tracks epsilon spent per day, alerts at 80% consumption, and reports remaining budget.

Input.

Parameter	Value
Total budget	10.0
Alert threshold	80% (i.e. 8.0 consumed)
Planned queries	30
Reserved for ad-hoc	3.0
Effective per-query budget	(10.0 - 3.0) / 30 = 0.23

Code.

-- Table that logs every DP query issued against the listing
CREATE TABLE IF NOT EXISTS cleanroom_governance.dp_query_log (
  query_id      STRING,
  query_ts      TIMESTAMP,
  listing_id    STRING,
  epsilon_spent FLOAT64,
  delta_spent   FLOAT64,
  purpose       STRING,
  subscriber    STRING
);

-- Every query is registered before it runs
INSERT INTO cleanroom_governance.dp_query_log
VALUES ('q_20260615_001',
        CURRENT_TIMESTAMP(),
        'listing_media_2026',
        0.23,
        1e-10,
        'weekly_overlap_report',
        'advertiser_x');

-- Budget accounting query
WITH budget AS (
  SELECT 10.0 AS total_budget,
         8.0  AS alert_threshold_abs
),
consumed AS (
  SELECT SUM(epsilon_spent) AS spent
  FROM   cleanroom_governance.dp_query_log
  WHERE  listing_id = 'listing_media_2026'
)
SELECT b.total_budget,
       c.spent,
       b.total_budget - c.spent                     AS remaining,
       ROUND(c.spent / b.total_budget * 100, 1)     AS pct_consumed,
       CASE WHEN c.spent >= b.alert_threshold_abs
            THEN 'ALERT: 80% consumed — plan re-listing'
            ELSE 'OK'
       END                                          AS status
FROM   budget b, consumed c;

Step-by-step explanation.

1. Every DP query is logged in dp_query_log with the epsilon spent, delta, purpose, and subscriber. The registration is done before the query runs so the budget cannot be over-spent.
2. The budget CTE holds the constants — total epsilon budget and alert threshold. In production these live in a governance config table, not inline.
3. The consumed CTE sums the epsilon spent so far for the specific listing. Under basic composition, epsilons add linearly.
4. The main query reports total, spent, remaining, and a status flag. The alert fires when 80% is consumed, giving the provider time to publish a fresh listing (with a new budget) before the current one exhausts.
5. Advanced composition (sub-additive with high probability under Rényi DP) can be substituted for basic composition to squeeze more queries out of the same budget — at the cost of a slightly relaxed delta. Most collaborations use basic composition for simplicity.

Output.

Day	Queries	ε spent that day	ε cumulative	% consumed	Status
1	1	0.23	0.23	2.3%	OK
15	1	0.23	3.45	34.5%	OK
25	1	0.23	5.75	57.5%	OK
35	1	0.23	8.05	80.5%	ALERT: 80% consumed
40	1	0.23	9.20	92.0%	ALERT — imminent exhaustion
43	1	0.23	9.89	98.9%	Publish new listing NOW

Rule of thumb. Treat the epsilon budget like a database credit balance. Log every debit, alert at 80%, plan the top-up (re-listing) at 90%. Never let a query run without a pre-registered budget slot; the ex-post accounting only works if every query is bookkept.

Worked example — column policies as a defence layer

Detailed explanation. Even with DP, some columns simply must not appear in a clean-room output — free-text notes, exact email, exact phone. BigQuery column policies (via Policy Tags in Data Catalog) provide a table-level control that hides these columns from anyone without a specific tag grant. Combined with DP, they provide belt-and-braces defence.

• Policy tag. A named tag attached to a column. Access to the column requires a role granted the "Data Catalog Fine-Grained Reader" role for that tag.
• Clean-room use. The provider tags every PII column with pii-restricted. The clean-room subscriber's project role has no grant on pii-restricted, so those columns return NULL in every query.
• Interaction with DP. DP protects aggregate outputs from small-cell inference; column policies protect against ever seeing the raw column in the first place.

Question. Define column policies on pub.impressions.user_email and demonstrate that DP queries succeed while row-level access to user_email fails.

Input.

Component	Value
Policy tag	pii-restricted
Tagged columns	pub.impressions.user_email, pub.impressions.user_phone
Clean-room subscriber role	no grant on pii-restricted

Code.

-- Create the policy tag (via Data Catalog API — SQL shown as pseudocode)
-- CREATE POLICY TAG pii-restricted ON dataset pub;
-- ATTACH POLICY TAG pii-restricted TO COLUMN pub.impressions.user_email;
-- ATTACH POLICY TAG pii-restricted TO COLUMN pub.impressions.user_phone;

-- Clean-room subscriber attempts a row-level query — fails because
-- the subscriber's role has no Fine-Grained Reader grant on pii-restricted
SELECT user_hkey, user_email, event_ts
FROM   adv.pub_impressions
LIMIT  10;
-- ERROR: Access denied on column pub.impressions.user_email
-- (Policy Tag: pii-restricted, no reader role for subscriber)

-- Clean-room subscriber's DP aggregate query — succeeds because
-- it does not reference the tagged column
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(
      epsilon                = 1.0,
      delta                  = 1e-10,
      max_groups_contributed = 5,
      privacy_unit_column    = user_hkey
    )
  creative_variant,
  ANON_COUNT(*) AS impressions
FROM   adv.pub_impressions
GROUP  BY creative_variant;
-- Returns DP-noised counts by creative. No PII involved.

Step-by-step explanation.

1. The policy tag pii-restricted is created in Data Catalog and attached to the sensitive columns. Any query that references those columns triggers an access check; only roles granted "Fine-Grained Reader" on the tag can see the column.
2. The clean-room subscriber's service account has no such grant, by design. This means row-level queries that reference user_email immediately fail — no data leaks, no partial results.
3. DP aggregate queries that touch only user_hkey, creative_variant, and event timestamps succeed. The DP layer + column policies combine as follows: column policies say "you cannot see raw PII"; DP says "aggregates cannot expose individuals via inference".
4. This defence works even against malicious queries. A subscriber that tries SELECT ANY_VALUE(user_email) fails on the column policy, not on the DP layer. A subscriber that tries a differencing attack via multiple DP queries exhausts the epsilon budget before achieving confidence.
5. The compliance story is stronger with column policies + DP than with either alone. Column policies are a classification control (this is PII, nobody outside sees it); DP is an inference control (aggregates cannot leak individuals). Both are needed under a strict data-protection regime.

Output.

Query	Column policy verdict	DP verdict	Result
SELECT user_email FROM pub_impressions	DENY (policy tag)	N/A	Access denied
SELECT user_hkey FROM pub_impressions	ALLOW	N/A	Rows returned (still governed by row access)
SELECT WITH DP ANON_COUNT(*)	ALLOW	ALLOW (ε=1.0)	DP-noised counts returned
Ad-hoc SELECT *	DENY on tagged columns	N/A	Fails on first tagged column

Rule of thumb. Column policies are the "cannot see" layer; DP is the "cannot infer" layer; k-anon is the "cannot enumerate small cells" layer. Every mature BigQuery clean room deploys all three.

Senior interview question on BigQuery clean rooms

A senior interviewer might ask: "Design a BigQuery clean room where two brands collaborate on aggregated customer overlap for a co-marketing campaign. Cover Analytics Hub, DIFFERENTIAL_PRIVACY, epsilon budgeting, and the three defence layers."

Solution Using Analytics Hub + DP + column policies + budget log

-- Provider (brand A): shared dataset with hashed user column
CREATE OR REPLACE VIEW brand_a.share_view AS
SELECT SHA256(LOWER(TRIM(email)) || @salt) AS user_hkey,
       segment,
       state,
       event_ts
FROM   brand_a.customers;

-- Provider publishes an Analytics Hub listing pointing at share_view.
-- The listing enables clean-room mode:
--   - DP-only queries
--   - privacy_unit_column = user_hkey
--   - lifetime epsilon budget = 10.0
--   - min_cell_size = 100

-- Column policy tags on any residual PII columns
-- (email is dropped by the view; segment/state considered non-PII)

-- Consumer (brand B) subscribes; linked dataset appears as brand_b.a_share.
-- Consumer runs DP overlap query — DP + budget log:

INSERT INTO cleanroom_governance.dp_query_log
VALUES ('q_20260615_005',
        CURRENT_TIMESTAMP(),
        'listing_brand_a_2026',
        0.5, 1e-10,
        'weekly_overlap_by_state',
        'brand_b');

SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(
      epsilon                = 0.5,
      delta                  = 1e-10,
      max_groups_contributed = 3,
      privacy_unit_column    = a.user_hkey
    )
  a.state,
  DATE_TRUNC(a.event_ts, WEEK) AS week,
  ANON_COUNT(*)                 AS overlap_users
FROM   brand_b.a_share            a
JOIN   brand_b.customers          b
  ON   a.user_hkey = SHA256(LOWER(TRIM(b.email)) || @salt)
WHERE  a.event_ts BETWEEN TIMESTAMP('2026-06-01') AND TIMESTAMP('2026-06-30')
GROUP  BY 1, 2;

Step-by-step trace.

Layer	Control	Effect
1	Analytics Hub shared view	Only hashed columns and non-PII dims exposed
2	Clean-room listing mode	Only DP queries allowed; row-level SELECT rejected
3	privacy_unit_column = user_hkey	DP guarantee measured per user
4	epsilon = 0.5 per query, budget 10.0 lifetime	~20 queries per collaboration
5	max_groups_contributed = 3	Per-user contribution capped; sensitivity bounded
6	Budget log	Pre-registration + alerting
7	Column policies (if PII columns remain)	Belt-and-braces PII redaction

After the rollout, Brand B receives DP-noised counts of overlap users by state and week. The counts have expected error ~9 users (√2 × 3 / 0.5 × ~2). The budget log tracks epsilon consumption; alerts fire at 80% (ε=8.0 consumed). Brand A never exports raw rows; Brand B never gains a copy of Brand A's customer list.

Output:

Metric	Result
Raw rows exposed to Brand B	0
DP-noised rows returned	~200 (state × week)
Epsilon budget consumed per query	0.5
Queries available in lifetime	~20
Column policy protection	Any residual PII column blocked
Legal classification	Aggregate DP outputs — non-personal

Why this works — concept by concept:

• Analytics Hub as the sharing surface — the provider's data lives in its project; the consumer's linked dataset is a pointer. The compute runs on the consumer's slots against a governed view.
• Clean-room listing mode — enables DP-only queries and rejects any query that doesn't use WITH DIFFERENTIAL_PRIVACY. The query surface is programmatically bounded.
• WITH DIFFERENTIAL_PRIVACY — first-party DP language construct. Every aggregation gets calibrated noise; row-level results are rejected; sensitivity is capped by max_groups_contributed.
• Epsilon budget log — every query pre-registers its epsilon spend. The provider can enforce a lifetime budget across all subscribers and all queries.
• Cost — one shared view + one Analytics Hub listing + a budget log table. The compute cost is the DP query itself, which is a modest premium over plain aggregation (10–20% more CPU for the noise-generation pass). The regulatory cost saving is enormous.

SQL
Topic — sql
SQL differential-privacy and aggregation problems

Practice →

Optimization Topic — optimization Optimization problems on privacy-budget tuning

Practice →

---

4. AWS Clean Rooms + Habu and independent platforms

aws clean room runs on configuredTable + analysisRule — cryptographic hash joins + optional DP; Habu / InfoSum / LiveRamp handle cross-cloud

The mental model in one line: AWS Clean Rooms is a native single-cloud service where each party keeps its data in its own AWS account and shares a configuredTable under an analysisRule that constrains the query surface, while independent platforms like habu clean room / InfoSum / LiveRamp specialise in cross-cloud collaboration where the parties are not on the same hyperscaler. Which one you pick is a cross-cloud vs single-cloud question first, and a build vs buy question second.

AWS Clean Rooms — the four axes.

• Partition — cloud-runs in a specific AWS region. Each participant is an AWS account; each contributes a configuredTable (a Glue table with an analysis rule). The collaboration is a resource; queries run on managed Athena-like compute inside the collaboration owner's account.
• Privacy — analysis rules + optional Clean Rooms Differential Privacy. The analysisRule constrains what SQL the collaboration can run — aggregation columns, join columns, allowed operators, minimum cell size. DP is an optional add-on (Clean Rooms DP, launched 2024) with per-collaboration epsilon budget.
• Join key — cryptographic hash join (encrypted join columns). AWS Clean Rooms supports cryptographic joins where the join column is encrypted with a shared cryptographic key (managed via AWS KMS + a party-specific key derivation). This prevents even the AWS control plane from seeing the join values.
• Output policy — analysisRule-driven aggregation. The output shape is bound by the analysis rule — aggregation type rules require GROUP BY + minimum cell; list type rules allow row-level output only if all parties allow it (rarely used in privacy-sensitive collaborations).

The core AWS Clean Rooms objects.

• Collaboration. The top-level object that holds member accounts, query engine settings, DP settings, and analytics engine (SQL vs Spark).
• Configured table. Each member registers one or more Glue tables as configuredTable resources, then attaches an analysisRule (the query constraint).
• Analysis rule. JSON that specifies the allowed query shape — aggregation type, join columns, aggregate columns, minimum cell size, allowed operators (SUM, COUNT, AVG, ...).
• Configured table association. Attaches a configured table to a specific collaboration. One table can be shared into multiple collaborations under different rules.

Habu / InfoSum / LiveRamp — independent multi-cloud platforms.

• Habu. Multi-cloud clean-room platform (acquired by LiveRamp in 2024). Sits on top of Snowflake, BigQuery, Databricks, Redshift; routes queries into whichever tenant hosts the data. Strong for advertiser collaborations across hyperscalers.
• InfoSum. UK-based cross-cloud clean room with a distinctive "no data movement" architecture — each party runs an InfoSum "Bunker" inside its own environment; only encrypted intermediate results move between Bunkers. Popular in EU privacy-sensitive verticals.
• LiveRamp Safe Haven / RampID. Identity-resolution + clean-room combo. RampID is the identity graph; Safe Haven is the clean room. Deep ad-tech integration.
• When to pick an independent. Cross-cloud collaboration (retailer on GCP, brand on Snowflake, agency on AWS), specialised identity resolution (LiveRamp RampID for ad-tech), or a multi-party collaboration where no single hyperscaler is neutral.

AWS Clean Rooms vs Snowflake vs BigQuery — the trade-off table.

• AWS Clean Rooms wins for AWS-native shops, cryptographic joins (unique feature), and pay-per-query pricing on Athena-like compute.
• Snowflake Native App wins for Snowflake-native shops and provider-runs architectures where the provider wants tight control.
• BigQuery clean rooms wins for GCP-native shops and first-party DP.
• Habu / InfoSum win for cross-cloud (the hyperscaler natives don't span clouds well).

AWS Clean Rooms DP.

• Enabled per collaboration. DP is a collaboration-level setting; once enabled, every query gets DP noise.
• Epsilon per query. Configured per collaboration; typical values 1.0–5.0 per query.
• Sensitivity handling. AWS computes sensitivity automatically from the analysis rule (aggregate columns, per-user contribution).
• Where it lags BigQuery. No max_groups_contributed equivalent; simpler epsilon accounting.

Common interview probes on AWS Clean Rooms.

• "What is a cryptographic join?" — the join column is encrypted with a shared key; AWS never sees plaintext join values.
• "How does an analysis rule enforce output shape?" — it constrains SQL to the declared aggregate columns and enforces a minimum cell size.
• "When would you pick Habu over AWS Clean Rooms?" — when the parties are on different hyperscalers or you need pre-built ad-tech identity resolution.
• "What's the DP story?" — Clean Rooms DP is optional; simpler epsilon accounting than BigQuery.

Worked example — AWS Clean Rooms 3-party collaboration configuration

Detailed explanation. A retailer, a brand, and a measurement agency want a 3-party collaboration where the agency runs queries against the joined retailer×brand data. All three are on AWS. The retailer and brand each configure a Glue table with an analysis rule; the agency is a query-only member (contributes no data but can run analyses). AWS Clean Rooms is the fit.

• Retailer. retailer.orders(customer_hkey, product_id, order_ts, gross_revenue).
• Brand. brand.exposures(customer_hkey, campaign_id, exposure_ts).
• Agency. Query-only member.
• Analysis rule. Aggregation type, join on customer_hkey, aggregate SUM(gross_revenue) and COUNT(*), minimum cell 100.

Question. Produce the configuredTable + analysisRule JSON for the retailer's table and the collaboration create call.

Input.

Party	Account	Table	Analysis rule
Retailer	111111111111	orders	aggregation, k=100
Brand	222222222222	exposures	aggregation, k=100
Agency	333333333333	(no data)	query only

Code.

# Retailer's analysis rule for the orders table
analysisRule:
  type: AGGREGATION
  policy:
    aggregation:
      aggregateColumns:
        - columnNames: [gross_revenue]
          function: SUM
        - columnNames: [order_ts]
          function: COUNT
      joinColumns: [customer_hkey]
      joinRequired: QUERY_RUNNER
      dimensionColumns: [campaign_id, order_month]
      scalarFunctions: [TRUNC, DATE_TRUNC]
      outputConstraints:
        - columnName: gross_revenue
          minimum: 100                # k-anon: cell must summarise ≥ 100 records
          type: COUNT_DISTINCT

configuredTable:
  name: retailer_orders_cr
  tableReference:
    glue:
      databaseName: retailer_analytics
      tableName: orders
  allowedColumns:
    - customer_hkey       # join column (cryptographically hashed)
    - product_id
    - order_ts
    - gross_revenue
    - campaign_id
    - order_month
  analysisMethod: DIRECT_QUERY

{
  "collaborationName": "retailer-brand-agency-2026",
  "creatorMemberAbilities": ["CAN_QUERY", "CAN_RECEIVE_RESULTS"],
  "queryLogStatus": "ENABLED",
  "members": [
    {
      "accountId": "111111111111",
      "displayName": "Retailer",
      "memberAbilities": []
    },
    {
      "accountId": "222222222222",
      "displayName": "Brand",
      "memberAbilities": []
    },
    {
      "accountId": "333333333333",
      "displayName": "Agency",
      "memberAbilities": ["CAN_QUERY", "CAN_RECEIVE_RESULTS"]
    }
  ],
  "dataEncryptionMetadata": {
    "allowCleartext": false,
    "allowDuplicates": false,
    "allowJoinsOnColumnsWithDifferentNames": false,
    "preserveNulls": false
  },
  "privacyBudget": {
    "epsilonPerQuery": 1.0,
    "epsilonLifetime": 10.0
  }
}

Step-by-step explanation.

1. Each data-contributing member (retailer, brand) creates a configuredTable in its own AWS account. The analysisRule is AGGREGATION type — the query surface is bounded to GROUP BY queries with SUM / COUNT / AVG over the declared aggregateColumns.
2. joinColumns: [customer_hkey] declares which column can be used as a join key. AWS Clean Rooms will only allow joins on those columns; the agency's query cannot join on any other column (blocks a fishing-expedition style attack).
3. outputConstraints.minimum: 100 is the k-anon floor — every output cell must summarise at least 100 distinct records. The type: COUNT_DISTINCT field says the minimum is measured on distinct customer_hkey values.
4. The collaboration create call adds the three members. The agency has CAN_QUERY and CAN_RECEIVE_RESULTS; the retailer and brand have empty abilities (they contribute data, they don't query). dataEncryptionMetadata.allowCleartext: false enforces cryptographic joins.
5. privacyBudget enables Clean Rooms DP with epsilonPerQuery = 1.0 and epsilonLifetime = 10.0 — the collaboration can run ~10 queries under basic composition before exhausting the budget.

Output.

Component	Result
configuredTable rows exposed	0 (each stays in owner's account)
Allowed query shape	aggregation on gross_revenue, count of order_ts
Join surface	customer_hkey only
Minimum cell size	100 distinct customer_hkey
DP status	epsilon 1.0 / query, 10.0 lifetime
Query log	ENABLED (audit trail in each member account)

Rule of thumb. AWS Clean Rooms' analysis rule is the strongest programmatic query-surface constraint of any hyperscaler primitive. Use AGGREGATION type by default; LIST type only when all parties explicitly approve row-level output for a specific use case (rare in privacy-sensitive collaborations).

Worked example — cryptographic join column with AWS KMS

Detailed explanation. AWS Clean Rooms supports cryptographic joins — the join column is encrypted with a shared cryptographic key so that even the AWS Clean Rooms control plane never sees the plaintext join value. This is stronger than a plain hash because the encryption key is party-controlled.

• The mechanism. Both parties pre-encrypt the join column with the same deterministic encryption scheme (AES-SIV or similar) and a shared key held in AWS KMS.
• The property. Same plaintext → same ciphertext. The join WHERE a.enc_hkey = b.enc_hkey matches on ciphertext.
• The security win. The plaintext key never appears anywhere except inside each party's account. AWS runs the join on ciphertext.

Question. Show the C3R (Cryptographic Computing for Clean Rooms) client configuration each party uses to encrypt its join column before uploading to S3.

Input.

Component	Value
Encryption scheme	AES-SIV via C3R client
Shared key	KMS-backed, shared via key policy across accounts
Join column plaintext	customer_hkey (already hashed email)
Join column ciphertext	c3r_encrypted_customer_hkey

Code.

# C3R client config used by both parties before uploading to S3
c3r_config:
  schema_version: 1
  input_file:  raw/orders.csv
  output_file: encrypted/orders.csv
  columns:
    - name: customer_hkey
      type: sealed              # deterministic encryption for join
      encrypt: true
      key_arn: arn:aws:kms:us-east-1:111111111111:key/shared-collab-key
    - name: order_id
      type: cleartext
    - name: product_id
      type: cleartext
    - name: order_ts
      type: cleartext
    - name: gross_revenue
      type: cleartext
  collaboration_id: retailer-brand-agency-2026
  encrypt_algorithm: AES_SIV

# CLI invocation, run by each party in its own environment
c3r-cli encrypt \
    --config c3r_config.yaml \
    --collaboration-id retailer-brand-agency-2026 \
    --shared-secret-key alias/collab-2026-key \
    --output-file encrypted/orders.csv

# The encrypted file is uploaded to the party's S3 bucket
aws s3 cp encrypted/orders.csv \
    s3://retailer-cleanroom-in/orders/

# The Glue table's configuredTable then points at the encrypted file
# and declares customer_hkey as a joinColumn — but the underlying data
# is already ciphertext.

Step-by-step explanation.

1. Both parties compute their customer_hkey (salted SHA-256 of the email) locally in their own environments. This gives a deterministic identifier — same email → same hash on both sides.
2. Each party runs the C3R client to further encrypt the hkey column with AES-SIV using a shared KMS-backed key. The KMS key's policy grants both party accounts Decrypt and Encrypt; AWS Clean Rooms never has decrypt access.
3. The encrypted file (encrypted/orders.csv) is uploaded to the party's own S3 bucket; the Glue table's configuredTable points at the encrypted file. The analysisRule treats the encrypted column as opaque bytes.
4. When the agency runs a JOIN, AWS Clean Rooms compares the ciphertext bytes. Because AES-SIV is deterministic (same plaintext + same key → same ciphertext), matching rows produce matching ciphertext, and the JOIN works transparently.
5. The security property: even AWS engineers with control-plane access cannot see the plaintext join key. The only decrypt path is through the shared KMS key, which lives in the two participant accounts.

Output.

Layer	Sees plaintext hkey?	Notes
Party's local env before C3R	Yes	The parties themselves know their own data
Party's S3 after upload	No	Only ciphertext
AWS Clean Rooms query engine	No	Joins on ciphertext bytes
Agency member (query runner)	No	Sees only aggregated output
KMS key policy	Decrypt limited to parties	AWS service role has Encrypt only

Rule of thumb. For high-regulation collaborations (healthcare, finance, cross-border), enable cryptographic joins. The extra operational overhead (KMS key rotation, C3R client integration) is small; the security posture upgrade from "AWS trusts itself" to "AWS never sees plaintext" is enormous.

Worked example — Habu cross-cloud match-rate query

Detailed explanation. A brand (on Snowflake) and a retailer (on BigQuery) want an audience overlap analysis. Neither will move data to the other's cloud. Habu (now under LiveRamp) is the platform choice — Habu's data-connector layer plugs into both Snowflake and BigQuery, orchestrates the DP + k-anon query, and returns the result.

• The topology. Brand's data lives in Snowflake account X. Retailer's data lives in BigQuery project Y. Habu runs a control plane in its own cloud.
• The query pattern. Habu pushes the aggregation query into each party's tenant (as SQL that only reads that party's data), then combines the intermediate result inside its own enclave.
• The output. Aggregated overlap counts per segment.

Question. Sketch the Habu match-rate query, showing the data-connector setup, the SQL pushed to each party, and the combined intermediate result.

Input.

Component	Value
Party A	Brand on Snowflake
Party B	Retailer on BigQuery
Match key	Salted SHA-256 email
Aggregation	Segment × week overlap count
k-anon	100

Code.

# Habu clean-room configuration (schematic)
clean_room:
  name: brand-retailer-overlap-2026
  members:
    - id: brand
      connector:
        type: snowflake
        account: brand.snowflakecomputing.com
        role: HABU_CONNECTOR_ROLE
        warehouse: CLEANROOM_WH
        database: BRAND_CRM
        schema: SHARED
      shared_table: customers_hashed
      match_column: email_hkey
    - id: retailer
      connector:
        type: bigquery
        project: retailer-analytics
        service_account: [e-mail]
        dataset: shared
      shared_table: customers_hashed
      match_column: email_hkey
  privacy:
    k_anon_min: 100
    differential_privacy:
      enabled: true
      epsilon_per_query: 1.0
      epsilon_lifetime: 10.0
  outputs:
    - name: overlap_by_segment_week
      query: |
        SELECT segment,
               week,
               COUNT(DISTINCT email_hkey) AS overlap_users
        FROM   {brand} JOIN {retailer}
               ON {brand}.email_hkey = {retailer}.email_hkey
        GROUP  BY segment, week
        HAVING COUNT(DISTINCT email_hkey) >= 100

-- Habu pushes this SQL to the brand's Snowflake tenant:
CREATE TEMP TABLE brand_intermediate AS
SELECT SHA2(LOWER(TRIM(email)) || '<collab-salt>', 256) AS email_hkey,
       segment,
       DATE_TRUNC('week', last_seen_ts)::DATE           AS week
FROM   BRAND_CRM.SHARED.customers_hashed;
-- Only aggregate blooms / minhash sketches leave the brand's tenant.

-- Habu pushes this SQL to the retailer's BigQuery:
CREATE TEMP TABLE retailer_intermediate AS
SELECT SHA256(LOWER(TRIM(email)) || '<collab-salt>') AS email_hkey,
       segment,
       DATE_TRUNC(last_seen_ts, WEEK)                 AS week
FROM   `retailer-analytics.shared.customers_hashed`;
-- Only sketches leave the retailer's tenant.

-- Habu combines the sketches inside its enclave, applies k-anon and DP,
-- and returns the aggregated result to both parties.

Step-by-step explanation.

1. Each party grants Habu a service account (Snowflake role / GCP SA) that has SELECT on the shared hashed-key table only. Habu never has broader access.
2. Habu's control plane splits the overlap query into two intermediate queries — one per party's tenant — that compute a bloom filter or minhash sketch of the hashed keys. The sketch is a fixed-size, tune-able-precision summary.
3. The sketches (fixed-size, non-reversible) are pulled into Habu's enclave. Combining two bloom filters gives an estimate of set intersection — the overlap count — without either party's raw hash set ever leaving its tenant.
4. Habu applies the collaboration-level k-anon minimum (drop segment × week buckets with overlap < 100) and DP noise (calibrated to epsilon = 1.0).
5. Both parties receive the same aggregated overlap table via their respective Habu clean-room UI. Habu logs every query for audit; neither party ever runs raw SQL against the other's data.

Output.

Component	Effect
Raw rows leaving brand tenant	0 (only sketches move)
Raw rows leaving retailer tenant	0 (only sketches move)
Sketch precision	~1% error on overlap count
k-anon minimum	100
DP epsilon per query	1.0
Final output	overlap by segment × week, both parties see it

Rule of thumb. For cross-cloud collaboration, an independent platform (Habu, InfoSum, LiveRamp Safe Haven) is almost always the right answer. Trying to bridge Snowflake and BigQuery via manual pipeline plumbing is a compliance minefield; the independents specialise in exactly that problem.

Senior interview question on AWS Clean Rooms vs Habu

A senior interviewer might ask: "You need to run a 3-party collaboration where two parties are on AWS and one is on GCP. Compare AWS Clean Rooms and Habu; pick one and defend the choice."

Solution Using Habu as the cross-cloud broker with per-tenant push-down queries

# Chosen: Habu (or InfoSum) — the AWS Clean Rooms native surface cannot reach GCP.
# Decision framework:
#   1) Data locality: 2 on AWS, 1 on GCP → cross-cloud required
#   2) AWS Clean Rooms scope: single-cloud only (AWS accounts as members)
#   3) BigQuery clean rooms scope: single-cloud only (GCP projects as members)
#   4) Habu spans Snowflake / BigQuery / Redshift / Databricks
#   5) Governance: DP + k-anon controllable at Habu clean-room level
#   6) Trade-off: Habu is a paid platform (~$150–500k/yr enterprise)
#                 vs building the cross-cloud plumbing from scratch (6+ months)

clean_room:
  name: threeparty-cross-cloud-2026
  members:
    - id: aws_party_a
      connector: {type: redshift,  cluster: pa.redshift.aws}
      shared_table: audiences
      match_column: user_hkey
    - id: aws_party_b
      connector: {type: redshift,  cluster: pb.redshift.aws}
      shared_table: exposures
      match_column: user_hkey
    - id: gcp_party_c
      connector: {type: bigquery,  project: partyc-analytics}
      shared_table: conversions
      match_column: user_hkey
  privacy:
    k_anon_min: 100
    differential_privacy:
      enabled: true
      epsilon_per_query: 1.0
      epsilon_lifetime: 10.0
  outputs:
    - name: exposure_to_conversion
      query: |
        SELECT segment,
               week,
               COUNT(DISTINCT user_hkey) AS converted_users,
               SUM(order_value)          AS gmv
        FROM   {aws_party_a} a
        JOIN   {aws_party_b} b ON a.user_hkey = b.user_hkey
        JOIN   {gcp_party_c} c ON b.user_hkey = c.user_hkey
        GROUP  BY segment, week
        HAVING COUNT(DISTINCT user_hkey) >= 100

Step-by-step trace.

Consideration	AWS Clean Rooms	BigQuery Clean Rooms	Habu
Cross-cloud	No	No	Yes
DP	Optional add-on	First-party language	Platform-level
k-anon	Analysis rule minimum	Manual HAVING	Platform-level
Governance	Native to AWS	Native to GCP	Multi-cloud
Cost	Pay-per-query	Pay-per-query	Enterprise SaaS ($150–500k/yr)
Setup time	Days	Days	Weeks (procurement + integration)
For this scenario	Fails: no GCP	Fails: no AWS	Fits

Habu is the answer: it's the only option that reaches all three tenants. The trade-off is a paid platform vs the multi-month cost of building a bespoke cross-cloud pipeline. For an enterprise 3-party ad-tech collaboration, the paid platform is universally cheaper than the DIY path.

Output:

Metric	Result
Cross-cloud reach	AWS × 2 + GCP × 1
Governance model	Habu platform-level DP + k-anon
Per-tenant data movement	0 raw rows; sketches only
Time to first collaboration	~4 weeks (procurement + connectors)
Legal footprint	Habu signs DPA + BAA (if healthcare)

Why this works — concept by concept:

• Cross-cloud broker — Habu / InfoSum / LiveRamp sit above the hyperscalers; each party's connector lives in its own tenant with least-privilege grants. The broker's control plane never holds raw data.
• Sketch-based intermediate results — bloom filters and minhash sketches let the broker compute set-intersection estimates without pulling raw hashes. This is the technical trick that makes cross-cloud clean rooms cheap.
• Platform-level DP + k-anon — Habu configures both centrally; the same policy applies regardless of which underlying tenant is queried.
• Enterprise contract absorbs the DPA / BAA — the broker signs data-processing agreements once; every collaboration reuses them. That is a huge legal-time saver vs building bilateral contracts per collaboration.
• Cost — Habu / LiveRamp / InfoSum enterprise contracts are $150–500k/yr. Build-your-own cross-cloud clean room is 6+ months of senior-engineering time (~$500k+) plus ongoing maintenance. The paid platform is cheaper for anything above a handful of collaborations.

SQL
Topic — sql
SQL AWS Clean Rooms and analysis-rule problems

Practice →

Design Topic — design Design problems on cross-cloud collaboration

Practice →

---

5. Clean-room patterns and governance

Salt the join, enforce k-anon, budget epsilon, classify outputs — the four levers behind every production clean room

The mental model in one line: every production clean room ships with the same four governance levers — a SHA-256 + per-collaboration salt join key with a rotation schedule, a k-anon minimum output cell (k=50 to k=100 typically), a differential privacy epsilon budget tracked per query, and an output classification tag that flows into downstream audit logs — the platforms differ in how they expose these levers, but the levers themselves are universal. Miss any one and the collaboration ships with a preventable weakness.

Lever 1 — Join-key hashing.

• The recipe. SHA-256(LOWER(TRIM(<identifier>)) || <collab_salt>) — normalise, concatenate a per-collaboration salt, hash. Or HMAC-SHA-256 with the salt as the key for stronger unforgeability.
• Salt rotation. Rotate the salt every 90 days (or per DPA schedule). Both parties re-hash their inputs; the collaboration re-derives the joined dataset.
• Salt storage. Shared KMS key (AWS KMS multi-region, GCP Cloud KMS, Azure Key Vault) or a shared secret in a dedicated vault (HashiCorp Vault, Secrets Manager).
• Common mistakes. No salt (rainbow-table attackable on known ID populations); shared salt across multiple collaborations (compromised salt breaks all of them); salt stored in git (SOC 2 finding waiting to happen).

Lever 2 — k-anonymity minimum aggregation cell.

• Typical values. k=50 for ad-tech collaborations, k=100 for financial services, k=500 for healthcare, k=1000 for census-scale releases.
• Enforcement layer. SQL HAVING COUNT(...) >= k, plus platform-level enforcement (Snowflake aggregation policy, BigQuery min_cell_size in Analytics Hub, AWS Clean Rooms analysis-rule outputConstraints.minimum).
• Belt-and-braces. Enforce both in the query (HAVING) and at the platform level. A missing HAVING in a future query author's SQL still gets caught by the platform.
• Common mistakes. Applying k-anon only on the primary aggregation but forgetting quasi-identifiers (state + gender + age together may still identify small buckets); enforcing k-anon at query time but not on cached materialised outputs.

Lever 3 — Differential privacy epsilon budget.

• Typical values. ε=1.0 per query, ε=10.0 lifetime for most collaborations; ε=0.1 per query for high-sensitivity healthcare; ε=5.0 per query for coarse ad-tech reports.
• Composition rule. Basic composition — sum epsilons across all queries. Advanced (Rényi DP) composition — sub-additive with high probability, more queries per budget. Use advanced only when you have a DP specialist reviewing.
• Budget book-keeping. Pre-register every query's epsilon in a governance log. Alert at 80% consumption. Reject queries once the budget is exhausted.
• Common mistakes. Not budgeting for ad-hoc probes; forgetting that DP composes across parallel query runners; not tracking budget across shard-per-week partitioned queries.

Lever 4 — Output classification.

• The taxonomy. raw (row-level PII — should never leave the clean room), aggregated (grouped counts / sums with k-anon), dp-noised (DP-noised aggregates), sketch (bloom / minhash intermediate).
• Classification tag. Every clean-room output carries a classification tag that flows into downstream audit / DLP systems.
• Downstream controls. DLP tools (AWS Macie, GCP DLP) alert on any raw-classified data appearing outside the clean room boundary. aggregated and dp-noised outputs are OK downstream.
• Common mistakes. Not classifying at all (audit finding); classifying inconsistently across collaborations (audit nightmare); allowing raw outputs anywhere.

The output-audit story.

• Query logs. Every DP query, every k-anon-filtered aggregate, every subscriber lookup logged with (query_id, timestamp, subscriber, epsilon_spent, k_anon_min, output_classification).
• Storage. Immutable audit store (S3 Object Lock, GCS Bucket Lock, or a dedicated audit warehouse table). Retention per compliance framework — often 7 years.
• DPA compliance. DPO / privacy office queries this log to answer regulator questions ("what data did party X see from party Y in Q2 2026?").

The salt rotation runbook.

• Trigger. Quarterly schedule, or on suspected compromise, or per DPA clause.
• Steps. (1) Generate new salt in shared KMS. (2) Both parties re-hash their source tables. (3) Update the clean-room configuration to point at the new hashed tables. (4) Retire the old salt (do not delete — needed for historical audit).
• Zero-downtime pattern. Run both salts in parallel for 24–48 hours to allow queries in flight to complete on the old salt.

Common interview probes on governance.

• "How do you rotate the join-key salt?" — quarterly, via shared KMS, with a parallel-run window.
• "What k-anon minimum do you pick and why?" — per vertical; ad-tech 50, financial 100, healthcare 500+; defensible under Article 29 Working Party guidance.
• "How do you track epsilon budget?" — pre-register every query in a log, alert at 80%, reject at 100%.
• "What is output classification?" — a tag on every clean-room output (raw / aggregated / dp-noised); downstream DLP alerts on misuse.

Worked example — salt rotation with zero downtime

Detailed explanation. A retailer-brand clean room runs quarterly salt rotations. During rotation, in-flight queries must complete against the old salt while new queries begin using the new salt. Show the parallel-run pattern using dual hashed columns.

• Constraint. No downtime; in-flight queries have up to 24 hours to complete.
• Approach. Both parties compute both hkey_v1 (old salt) and hkey_v2 (new salt) columns during the rotation window. Clean-room queries are updated to use hkey_v2; old queries continue to use hkey_v1 until the deadline.

Question. Design the SQL for the parallel-run rotation window and the cut-over.

Input.

Component	Value
Old salt	salt_v1 (KMS alias alias/collab-2026-q1)
New salt	salt_v2 (KMS alias alias/collab-2026-q2)
Parallel-run window	48 hours
Cut-over	delete hkey_v1 column at hour 48

Code.

-- Step 1 — Add hkey_v2 column and populate with new salt
ALTER TABLE retailer.customers
  ADD COLUMN hkey_v2 STRING;

UPDATE retailer.customers
SET    hkey_v2 = SHA2(LOWER(TRIM(email)) || (SELECT salt FROM kms.aliases WHERE alias = 'alias/collab-2026-q2'), 256);

-- Repeat on brand side
ALTER TABLE brand.customers
  ADD COLUMN hkey_v2 STRING;

UPDATE brand.customers
SET    hkey_v2 = SHA2(LOWER(TRIM(email)) || (SELECT salt FROM kms.aliases WHERE alias = 'alias/collab-2026-q2'), 256);

-- Step 2 — Publish a new sample query using hkey_v2
CREATE OR REPLACE PROCEDURE app_public.overlap_v2(consumer_ref STRING)
RETURNS TABLE(state STRING, week DATE, overlap_count NUMBER)
LANGUAGE SQL
AS
$$
BEGIN
  RETURN TABLE(
    SELECT r.state,
           DATE_TRUNC('week', r.first_purchase_ts)::DATE AS week,
           COUNT(DISTINCT r.hkey_v2) AS overlap_count
    FROM   retailer.customers r
    JOIN   IDENTIFIER(:consumer_ref) b
      ON   r.hkey_v2 = b.hkey_v2
    GROUP  BY 1, 2
    HAVING COUNT(DISTINCT r.hkey_v2) >= 100
  );
END;
$$;

-- Step 3 — Register both procedures in the app for 48 hours; retire hkey_v1 after
-- (Governance log entry recorded — old salt deprecation date, retirement date)

-- Step 4 — At t + 48h, drop hkey_v1
ALTER TABLE retailer.customers DROP COLUMN hkey_v1;
ALTER TABLE brand.customers    DROP COLUMN hkey_v1;
-- Update the app to remove overlap_v1 procedure.

Step-by-step explanation.

1. Both parties add a new hkey_v2 column and populate it by re-hashing every row with the new salt. Because SHA-256 is deterministic, both sides produce the same hkey_v2 for a given email, which lets the join work post-cut-over.
2. A new procedure overlap_v2 is published alongside the existing overlap_v1. The consumer can invoke either during the 48-hour parallel-run window. Old integrations continue on v1; new integrations switch to v2.
3. The governance log records the salt-v1 deprecation timestamp and the scheduled retirement timestamp. Every query in the audit log is tagged with the salt version it used, enabling post-hoc audit if the old salt is later compromised.
4. At t+48h, the retailer and brand drop the hkey_v1 column and the corresponding procedure. Any query attempted on the old salt fails immediately. The rotation is complete.
5. Optionally, the old salt itself is retained in KMS (marked disabled but not deleted) for audit purposes — regulators may ask "what did party X see under salt v1 in Q1?"

Output.

Time	State	Retailer hkey column	Brand hkey column	Active procedure
t = 0	Before rotation	hkey_v1	hkey_v1	overlap_v1
t + 1h	Parallel-run begin	hkey_v1, hkey_v2	hkey_v1, hkey_v2	overlap_v1, overlap_v2
t + 24h	Consumers migrated	hkey_v1, hkey_v2	hkey_v1, hkey_v2	overlap_v1, overlap_v2
t + 48h	Cut-over	hkey_v2 only	hkey_v2 only	overlap_v2

Rule of thumb. Every clean-room platform supports the parallel-run pattern via multiple hashed columns. Never do a big-bang salt cut-over; the 48-hour window is nearly free and prevents in-flight query failures.

Worked example — output classification pipeline with DLP alerting

Detailed explanation. Every output from the clean room carries a classification tag (raw / aggregated / dp-noised / sketch). Downstream systems (data warehouses, BI tools, downstream pipelines) inherit the tag. A DLP tool watches for any raw-classified data appearing outside the clean-room boundary and alerts.

• Tag propagation. The clean-room procedure attaches a tag to every output row (as a column, a table-level metadata property, or a Data Catalog tag).
• Downstream inheritance. ETL jobs that transform the output inherit the tag. Any pipeline that would strip the tag is flagged as high-risk.
• DLP watch. AWS Macie, GCP DLP, or a custom Presidio-based scanner watches S3 / GCS / BQ for raw tags outside the clean-room bucket.

Question. Design the tag-propagation SQL for a BigQuery clean-room output and the DLP-alerting query.

Input.

Component	Value
Output table	analytics.overlap_by_segment_week
Tag column	output_classification
Tag value	'dp-noised'
DLP scan interval	hourly

Code.

-- Clean-room output attaches the classification tag
CREATE OR REPLACE TABLE analytics.overlap_by_segment_week
PARTITION BY DATE_TRUNC(week, MONTH)
OPTIONS(
  labels = [('output_classification', 'dp-noised'),
            ('salt_version',          'salt_v2'),
            ('epsilon_spent',         '1.0'),
            ('collaboration_id',      'listing_brand_a_2026')]
)
AS
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon = 1.0, delta = 1e-10, max_groups_contributed = 3,
            privacy_unit_column = user_hkey)
  segment,
  DATE_TRUNC(event_ts, WEEK) AS week,
  ANON_COUNT(*)               AS overlap_users,
  'dp-noised'                 AS output_classification
FROM   brand_b.a_share
JOIN   brand_b.customers b USING (user_hkey)
GROUP  BY 1, 2;

-- DLP-alert query — flag any table lacking the expected classification
SELECT project_id,
       dataset_id,
       table_id,
       -- look for tables containing "clean_room" or in the analytics dataset
       -- that don't have the required label
       ARRAY(SELECT label FROM UNNEST(labels) label WHERE label.name = 'output_classification') AS classification_labels
FROM   `region-us`.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE  (LOWER(table_id) LIKE '%overlap%' OR LOWER(table_id) LIKE '%clean_room%')
  AND  option_name = 'labels'
  AND  ARRAY_LENGTH(
         ARRAY(SELECT 1 FROM UNNEST(labels) label WHERE label.name = 'output_classification')
       ) = 0;
-- Any row returned = table lacking classification; alert on-call privacy officer.

Step-by-step explanation.

1. The clean-room output table is created with BigQuery labels that carry the classification tag, salt version, epsilon spent, and collaboration ID. These labels become the audit trail for the table.
2. Every downstream ETL job that materialises a new table from this input inherits the labels via a job template. Any new table lacking output_classification is a policy violation.
3. The DLP-alert query scans INFORMATION_SCHEMA.TABLE_OPTIONS hourly, looking for tables that match a clean-room naming pattern but lack the classification label. Missing labels trigger a PagerDuty alert to the privacy officer.
4. If a raw-classified table appears outside the clean-room-only dataset, the DLP alert is high-severity: someone has moved raw PII outside the boundary. The runbook is to freeze the downstream pipeline and revoke access.
5. Long-term, the labels feed a Grafana dashboard that shows the classification distribution of every collaboration output — a single glance tells the privacy officer whether governance is being followed.

Output.

Table	classification	Alert?
analytics.overlap_by_segment_week	dp-noised	OK
analytics.overlap_v1_backup	(missing)	ALERT: classify or delete
dev.temp_overlap_raw	raw	ALERT: raw outside CR boundary
analytics.summary_dashboard	aggregated	OK

Rule of thumb. Every clean-room output must carry a classification tag before it lands. The DLP scan is your "someone will forget" safety net; every large data organisation has had one such incident and every one wishes they had the DLP in place before it happened.

Worked example — the four axes on a governance dashboard

Detailed explanation. A senior privacy officer wants a single dashboard that shows the health of every active clean-room collaboration on the four axes. Design the metrics and the SQL that populates them.

• Axis 1 — Partition health. For each collaboration: which platform (Snowflake / BQ / AWS / Habu), which member accounts, salt rotation status.
• Axis 2 — Privacy budget. Epsilon consumed vs budgeted per collaboration.
• Axis 3 — Join key freshness. Last salt rotation timestamp, next scheduled rotation.
• Axis 4 — Output classification distribution. Fraction of outputs by classification type.

Question. Write the SQL that populates the dashboard from the governance log tables.

Input.

Table	Role
governance.collaborations	one row per active collaboration
governance.salt_rotations	one row per rotation event
governance.dp_query_log	one row per DP query issued
governance.output_registry	one row per clean-room output table

Code.

-- Axis 1 — Partition health
SELECT c.collaboration_id,
       c.platform,
       ARRAY_TO_STRING(c.member_accounts, ',') AS members,
       c.status
FROM   governance.collaborations c
WHERE  c.status = 'ACTIVE';

-- Axis 2 — Privacy budget consumption per collaboration
SELECT c.collaboration_id,
       c.epsilon_budget,
       COALESCE(SUM(l.epsilon_spent), 0)                   AS epsilon_consumed,
       c.epsilon_budget - COALESCE(SUM(l.epsilon_spent),0) AS epsilon_remaining,
       ROUND(COALESCE(SUM(l.epsilon_spent),0) / c.epsilon_budget * 100, 1) AS pct_consumed
FROM   governance.collaborations c
LEFT   JOIN governance.dp_query_log l USING (collaboration_id)
WHERE  c.status = 'ACTIVE'
GROUP  BY c.collaboration_id, c.epsilon_budget;

-- Axis 3 — Join-key freshness
SELECT c.collaboration_id,
       MAX(s.rotated_at)                                        AS last_rotation,
       DATE_DIFF(CURRENT_DATE(), MAX(DATE(s.rotated_at)), DAY)  AS days_since_rotation,
       DATE_ADD(MAX(DATE(s.rotated_at)), INTERVAL 90 DAY)       AS next_scheduled_rotation
FROM   governance.collaborations   c
LEFT   JOIN governance.salt_rotations s USING (collaboration_id)
WHERE  c.status = 'ACTIVE'
GROUP  BY c.collaboration_id;

-- Axis 4 — Output classification distribution
SELECT c.collaboration_id,
       o.classification,
       COUNT(*)                                            AS n_outputs,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*))
             OVER (PARTITION BY c.collaboration_id), 1)    AS pct
FROM   governance.collaborations c
JOIN   governance.output_registry o USING (collaboration_id)
WHERE  c.status = 'ACTIVE'
GROUP  BY c.collaboration_id, o.classification;

Step-by-step explanation.

1. Axis 1 — the partition table lists which platform hosts each collaboration and who the members are. It's the "org chart" of your clean-room estate. Anything not on this list is unmanaged and needs to be brought into governance.
2. Axis 2 — the epsilon-budget query joins the query log to the collaboration definition and computes remaining budget. The dashboard highlights collaborations that are >80% consumed (red), >50% (yellow), <50% (green).
3. Axis 3 — the salt-freshness query pulls the last rotation timestamp per collaboration. Any collaboration where days_since_rotation > 90 is flagged for rotation; the automated rotation runner picks these up nightly.
4. Axis 4 — the classification distribution shows what shape the outputs take. Healthy collaborations are dominated by aggregated and dp-noised; any nonzero raw fraction is an emergency.
5. The four axes together give the privacy officer a single view of every collaboration's health. Red on any axis triggers a runbook: budget red → publish new listing; salt red → rotate; classification red → freeze the pipeline; partition red → engage the platform owner.

Output.

Axis	Metric	Threshold
Partition	(n active collaborations, n platforms)	manual review
Privacy budget	pct_consumed	alert >80%
Salt freshness	days_since_rotation	rotate >90
Output classification	pct of raw	must be 0%

Rule of thumb. Build the four-axes dashboard on day one of the clean-room programme. Waiting for the "first collaboration to go live" before building governance is how programmes accumulate compliance debt. Governance-first, collaborations-second.

Senior interview question on clean-room governance

A senior interviewer might ask: "You inherit a data engineering team that runs three active clean rooms with no salt rotation, no epsilon accounting, and no output classification. Walk me through the first 30 days of getting the governance to a defensible state."

Solution Using a 30-day governance retrofit plan

Day 1–3   — Inventory
  - List every collaboration: platform, members, tables, salt version
  - Populate governance.collaborations table
  - Identify unmanaged clean-room outputs in ETL / BI

Day 4–7   — Join-key hardening
  - Rotate all salts to a KMS-backed source of truth
  - Enforce SHA-256 + salt across all hashed columns
  - Add hkey_v2 columns with new salts; parallel-run pattern

Day 8–12  — k-anon enforcement
  - Add HAVING COUNT(...) >= 100 to every aggregate procedure
  - Add platform-level enforcement (Snowflake agg policy /
    BigQuery Analytics Hub min_cell / AWS analysisRule minimum)
  - Reject queries that would return small cells

Day 13–17 — Epsilon budget
  - Add governance.dp_query_log
  - Pre-registration wrapper for every DP query
  - Alert at 80% consumption; runbook to publish new listing

Day 18–22 — Output classification
  - Add labels/tags to every clean-room output
  - DLP scan for raw outside CR boundary
  - PagerDuty alert to privacy officer

Day 23–27 — Dashboard + audit trail
  - Four-axes governance dashboard (Grafana / Looker)
  - Immutable audit log to S3 Object Lock (7-year retention)

Day 28–30 — Runbooks + training
  - Salt rotation runbook (quarterly)
  - Epsilon exhaustion runbook (re-listing procedure)
  - Classification violation runbook (freeze + revoke)
  - Team training + tabletop exercise

Step-by-step trace.

Phase	Activity	Output
Inventory	Enumerate collaborations	governance.collaborations populated
Join-key hardening	Rotate salts	KMS-backed rotation live
k-anon	Enforce cell minimum	100 across the board
Epsilon	Budget log + alerts	dp_query_log live
Classification	Tag every output	Labels/tags on every table
Dashboard	Four-axes view	Grafana dashboard live
Runbooks	Rotation, exhaustion, violation	Documented + trained

After the 30-day retrofit, every active collaboration is on the four-axes dashboard, salts rotate on a 90-day cadence, epsilon budgets are logged and alerted, output classification propagates through DLP, and the privacy officer has an audit trail suitable for a SOC 2 or ISO 27701 review.

Output:

Governance lever	Before	After
Salt rotation	ad-hoc / never	KMS-backed, 90-day cadence
k-anon	inconsistent	k=100 enforced platform-wide
Epsilon budget	not tracked	logged, alerted, runbook
Output classification	none	tagged, DLP-scanned
Governance dashboard	none	four axes, live
Audit log retention	none	7-year immutable

Why this works — concept by concept:

• Four-axes decomposition — reducing "clean room governance" to four named levers means every audit finding maps to a specific control. No lever is optional.
• Retrofit sequencing — inventory first (you cannot govern what you cannot see); join keys next (the most consequential defect); k-anon and epsilon in parallel (both are query-time controls); classification last (it depends on the earlier layers).
• Platform-level + query-level enforcement — every control is enforced twice: once at the query (SQL HAVING, DP OPTIONS clause) and once at the platform (aggregation policy, Analytics Hub min cell, analysis rule minimum). Any single-point-of-failure is caught by the other layer.
• Immutable audit log — the entire governance story falls apart without a retained audit trail. S3 Object Lock or equivalent WORM store is non-negotiable; 7 years is the typical retention.
• Cost — 30 senior-engineering-days for the retrofit, plus ongoing quarterly rotation runbook (~1 day per quarter). The avoided cost of one GDPR fine (up to 4% of global revenue) pays for the entire programme.

SQL
Topic — sql
SQL governance and audit-log problems

Practice →

ETL
Topic — etl
ETL problems on classification and DLP propagation

Practice →

---

Cheat sheet — Data clean room recipes

• When to use a clean room instead of a data share. Any cross-organisation collaboration under GDPR / CCPA / HIPAA / PCI where the parties must not exchange row-level data. Data shares are for internal cross-account transfers within one legal entity; clean rooms are the correct primitive for external collaborations, especially in advertising, retail-brand, healthcare, and financial services.
• The four-axis decision framework. For every clean room, write down (a) partition — provider-runs / broker-runs / cloud-runs; (b) privacy — k-anon and/or differential privacy; (c) join key — SHA-256 or HMAC-SHA-256 with per-collaboration salt; (d) output policy — aggregate-only, minimum cell size, DP noise floor. Never touch platform SQL before all four are locked.
• Snowflake Native App clean-room template. CREATE APPLICATION PACKAGE; sample-query stored procedures returning aggregate TABLE(...); HAVING COUNT(DISTINCT ...) >= k inside every procedure; row access policy on underlying tables scoped by SYSTEM$GET_CONTEXT session flag; aggregation policy at the column level for compile-time k-anon; consumer binds its own tables via REFERENCE objects at install time.
• BigQuery Analytics Hub + DIFFERENTIAL_PRIVACY. Publisher creates a view exposing only hashed keys + non-PII dims; Analytics Hub listing in clean-room mode; every query uses WITH DIFFERENTIAL_PRIVACY OPTIONS(epsilon = 1.0, delta = 1e-10, max_groups_contributed = 5, privacy_unit_column = <hkey>); ANON_COUNT / ANON_SUM / ANON_AVG; column policies via Data Catalog for belt-and-braces PII redaction; pre-registered epsilon budget log.
• AWS Clean Rooms configuration. Each member creates a configuredTable with an analysisRule of type AGGREGATION; outputConstraints.minimum: 100 enforces k-anon; joinColumns whitelist restricts joins; dataEncryptionMetadata.allowCleartext: false mandates cryptographic joins via C3R client + shared KMS key; optional Clean Rooms DP with epsilonPerQuery and epsilonLifetime.
• Habu / InfoSum / LiveRamp for cross-cloud. Pick an independent platform when parties span AWS + GCP + Snowflake + Databricks. Habu / InfoSum route queries into each party's tenant, combine sketches (bloom / minhash) in a neutral enclave, enforce platform-level DP + k-anon. Enterprise contracts $150–500k/yr; procurement + connector integration in ~4 weeks.
• Join-key hashing recipe. SHA-256(LOWER(TRIM(<identifier>)) || <collab_salt>) — normalise, salt, hash. For high-regulation collaborations use HMAC-SHA-256 for unforgeability. Salt lives in shared KMS with per-collaboration key alias; rotate every 90 days; parallel-run pattern with hkey_v1 + hkey_v2 columns for 48 hours during cut-over.
• k-anonymity minimums by vertical. Ad-tech k=50; financial services k=100; healthcare k=500 (Safe Harbor comfort); census-scale releases k=1000. Enforce at both query level (HAVING) and platform level (Snowflake aggregation policy / AWS outputConstraints.minimum / BQ min cell). Don't apply only to the primary aggregation — quasi-identifiers (state × gender × age) can identify small buckets.
• Differential privacy epsilon budget accounting template. Governance log table with (query_id, timestamp, listing, epsilon_spent, delta_spent, purpose, subscriber). Pre-register every query before it runs. Alert at 80% consumption. Reject at 100%. Basic composition: sum of per-query epsilons. Advanced (Rényi) composition only with a DP specialist review.
• Output classification taxonomy. Every clean-room output tagged as raw / aggregated / dp-noised / sketch. Tags propagate through downstream ETL. DLP scan (AWS Macie / GCP DLP) alerts on raw outside the clean-room boundary or on missing classification. Retention: 7-year immutable audit log (S3 Object Lock).
• Differencing-attack defence. Exact-count outputs are always vulnerable — pair k-anon with DP noise. Where DP is unavailable (older Snowflake), enforce large k (200+) and cap query cardinality per subscriber. Track query similarity metrics; block query pairs that differ only in a WHERE predicate over a hashed identifier.
• Four-axes governance dashboard. Grafana / Looker view over governance.collaborations, governance.salt_rotations, governance.dp_query_log, governance.output_registry. One row per collaboration with the four axes as columns; red / yellow / green thresholds on each; alerts wired to the privacy officer.
• Cost model. Snowflake / BigQuery clean rooms: per-query compute on top of standard warehouse / slot pricing. AWS Clean Rooms: per-query pricing on managed Athena-like compute. Habu / InfoSum / LiveRamp: enterprise SaaS $150–500k/yr. DIY cross-cloud: 6+ months of senior-engineering time (~$500k+) plus maintenance. Independents win for cross-cloud; natives win for single-cloud.
• 30-day governance retrofit. Inventory (days 1–3) → join-key hardening (4–7) → k-anon enforcement (8–12) → epsilon budget log (13–17) → output classification (18–22) → dashboard + audit trail (23–27) → runbooks + training (28–30). Never postpone governance beyond the first collaboration going live.

Frequently asked questions

What is a data clean room and why did it replace third-party cookies?

A data clean room is a governed compute environment where two or more organisations join their data on a hashed key and receive only an aggregated or differentially-private output — the raw rows never leave either party's boundary, and no side gains a copy of the other's dataset. The primitive replaced third-party cookies because Safari (ITP, 2020), Firefox (ETP, 2019), and Chrome (fully deprecated by mid-2024) removed the shared identifier that ad-tech relied on for cross-domain measurement — but retailers, brands, and agencies still needed to collaborate on audience overlap, media measurement, and propensity modelling. Clean rooms let those parties collaborate on the insight while keeping the asset — the retailer's first-party customer list, the brand's CRM, the agency's exposure log — inside each party's own trust boundary. Regulatory expansion (GDPR, CCPA, ~20 US state privacy laws, HIPAA, GLBA) accelerated adoption; by 2026 the clean room is the default cross-party analytics primitive.

Snowflake clean room vs BigQuery clean room — which do I pick?

Snowflake clean room (Native App Framework) is the strongest fit when both parties are already on Snowflake, when the collaboration is provider-heavy (one party contributes most of the data and wants tight control over the query surface), and when you need row access policies + aggregation policies + application-role RBAC as a defence-in-depth stack. BigQuery data clean room (Analytics Hub + WITH DIFFERENTIAL_PRIVACY OPTIONS) is the strongest fit when both parties are on GCP, when you want first-party differential privacy as a language construct (no UDF layer needed), and when the collaboration wants a lighter listing-based sharing surface. Practical rule: if your organisations are already on the same hyperscaler, pick that hyperscaler's native primitive. For cross-cloud collaborations, both fall short and you reach for Habu / InfoSum / LiveRamp instead. Snowflake wins on programmable defence layers; BigQuery wins on first-party DP; both are defensible under GDPR / CCPA when properly configured.

What is differential privacy and when do I need it in a clean room?

Differential privacy is a mathematical framework that adds calibrated noise to every aggregate output so that the presence or absence of a single record cannot be inferred, with the strength of the guarantee parameterised by an epsilon budget (ε). Smaller epsilon = stronger privacy, more noise; ε=1.0 is a common per-query value; ε=10.0 lifetime lets you run ~10 queries under basic composition. You need DP whenever your clean-room outputs are numerical aggregates and you cannot tolerate a differencing attack — where an attacker issues two nearly-identical queries and subtracts the results to infer whether a single individual is present. BigQuery ships DP as first-party language construct (WITH DIFFERENTIAL_PRIVACY OPTIONS, ANON_COUNT, ANON_SUM, ANON_AVG); AWS Clean Rooms ships DP as an add-on (Clean Rooms Differential Privacy, 2024); Snowflake requires DP via UDFs. Combine DP with k-anonymity as defence in depth: k-anon drops small cells, DP protects against inference on remaining cells. Skip DP only when your outputs are categorical or already very coarse (state-level counts of millions).

Do I need a data clean room to comply with GDPR when sharing data?

Not always — but usually yes for external collaborations. GDPR classifies row-level data exchange between organisations as a transfer of personal data requiring a lawful basis (Article 6), a data-processing agreement (Article 28), and often data-subject notification. A clean room that returns only aggregate or DP-noised outputs — with k-anonymity floors and no possibility of raw-row exposure — sits outside the definition of "personal data" for the receiving party under Article 29 Working Party guidance and the ICO's clean-room guidance, which removes the transfer classification entirely. For strictly internal cross-account transfers within one legal entity, a data share (Snowflake Secure Data Share, BigQuery Analytics Hub in share mode) with proper role-based access is usually sufficient. For any external collaboration in a regulated vertical (retail-brand, ad-tech, healthcare, financial services), a clean room is the defensible default; a data share is an audit finding waiting to happen.

What is k-anonymity and how do I pick the right k for my clean room?

k-anonymity is the rule that every output aggregate must summarise at least k records — cells with fewer than k records are dropped (or the query is rejected). It defends against the small-cell disclosure risk where a bucket of "5 customers in Wyoming who bought product X" effectively identifies individuals. Typical k values by vertical: ad-tech k=50 (fast collaborations, coarse audience overlap); retail-brand k=100 (state or DMA-level aggregations); financial services k=100 to k=500 (regulator-comfort defaults); healthcare k=500 (Safe Harbor + expert determination); census-scale releases k=1000+. Enforce k-anon at both the query layer (SQL HAVING COUNT(DISTINCT ...) >= k) and the platform layer (Snowflake aggregation policy, BigQuery Analytics Hub min_cell_size, AWS Clean Rooms outputConstraints.minimum). Watch for quasi-identifier composition: a small (state, gender, age_band) bucket may still identify individuals even when the primary aggregation has k rows. The senior signal is picking k based on the smallest bucket you actually need to measure, not on a default number.

How much does a data clean room cost to set up and run?

Cloud-native clean rooms (Snowflake Native App, BigQuery Analytics Hub, AWS Clean Rooms) charge per-query compute on top of standard warehouse / slot / Athena pricing — typical single-collaboration costs are $500–$5000/month depending on query volume. Setup is 1–4 weeks of senior-engineering time per collaboration. Independent platforms (Habu, InfoSum, LiveRamp Safe Haven) are enterprise SaaS with contracts in the $150k–$500k/year range plus per-collaboration setup fees; they win when the collaboration is cross-cloud or when you need pre-built ad-tech identity resolution. DIY cross-cloud clean rooms take 6+ months of senior-engineering time (~$500k+ fully-loaded) plus ongoing maintenance — almost never the right answer if you have more than a handful of active collaborations. Governance cost (four-axes dashboard, epsilon budget accounting, salt-rotation runbook, audit-log retention) is a fixed one-time investment of ~30 senior-engineering-days plus ~1 day per quarter per collaboration. The cost of not doing this properly — a GDPR fine at up to 4% of global revenue — makes every clean-room programme pay for itself the first time it prevents a leak.

Practice on PipeCode

• Drill the SQL practice library → for the aggregation, hashing, and privacy-safe-join problems that senior clean-room interviewers gravitate towards.
• Rehearse on the ETL practice library → for the salt-rotation pipelines, classification-tag propagation, and DLP-scan workflows that surround every clean room.
• Sharpen the design axis with the optimization practice library → for the epsilon-budget tuning, k-anon floor selection, and cross-cloud architecture problems.
• Stack the prerequisites against PipeCode's 450+ data-engineering catalogue to anchor the four-axes intuition against real graded inputs.

Lock in clean-room design muscle memory

Platform docs explain the SDKs. PipeCode drills explain the decision — when Snowflake Native App beats BigQuery Analytics Hub, when Habu is the only right answer for cross-cloud, when a k-anon floor of 100 is enough and when you need DP on top. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →