Fixing BigQuery PII Overexposure While Preserving EU Residency
FinData Analytics stores 35 TB of transaction data in BigQuery in europe-west1, including account numbers, SSNs, and birth dates. Two hundred analysts in the EU and 30 in the US need access. The current design is a single dataset with a view that joins tables and is authorized on the dataset; analysts are granted BigQuery Data Viewer on the dataset. A penetration test found that an analyst could query the base tables directly and see SSNs because the authorized view did not restrict the underlying table grant. The US team needs only aggregated, de-identified metrics. Regulators require that data remains in the EU and that access to raw PII is logged and reviewed. The company uses CMEK with a Cloud KMS key ring in europe-west1. A proposal to copy data to us-central1 for US analysts is under discussion.
1. Explain why the authorized view failed and design a column-level access model that prevents direct base-table exposure while still letting EU analysts query raw PII when justified.
Read the worked answer
The authorized view failed because authorization on a view does not revoke direct grants on the underlying tables. The analysts held BigQuery Data Viewer on the dataset, which includes the base tables, so they could bypass the view entirely. An authorized view grants access to the view's definer, but it does not subtract permissions from the caller.
Design a policy-tag-based model. Classify columns with policy tags: SSN and account number as restricted, birth date as sensitive, and non-identifying fields as public. Grant the EU analyst group BigQuery Data Viewer on the dataset but bind the restricted policy tag to a separate group, or use column-level access controls so that the broad group sees only non-tagged columns. Analysts who need raw SSN must be in a smaller group with fine-grained reader on the restricted tag. Raw access should be time-bound through Privileged Access Manager or IAM Conditions and should require a ticket. Remove any direct table grants from the broad analyst group; audit with INFORMATION_SCHEMA.COLUMN_FIELD_PATHS and effective IAM policies to prove the base tables are not directly readable.
Use row-level security where the analyst should see only their region's rows. Do not rely on a single view as the only control; views are useful for presenting curated data, but policy tags enforce at the table level even when someone queries the base table. Test by impersonating an EU analyst and attempting a direct SELECT on the restricted column; it should fail with a permission error.
2. The US team needs only aggregated de-identified metrics. How would you provide that without copying raw data to us-central1, and what residency risks remain?
Read the worked answer
Keep the raw data in europe-west1. Create a separate dataset or view in europe-west1 that returns only aggregated, de-identified results, for example counts and sums grouped by region and month with small-cell suppression. Grant the US analysts BigQuery Data Viewer only on that view or dataset, and use policy tags to block direct access to raw columns. If the US team needs a local copy for latency, export only the aggregated result set to a us-central1 dataset; that copy contains no PII, so it does not violate the raw-data residency requirement. The export job itself must be reviewed to ensure it cannot select raw columns.
Residency risks remain. First, a view definer with broad access could be modified to expose raw data; protect view definitions with IAM and change review. Second, query results may be stored temporarily in the query location; set the job location to europe-west1 and confirm that the US analysts' queries run there. Third, logging and support access can cross regions; restrict Cloud Logging buckets and customer support access to EU personnel or EU locations where required. Fourth, CMEK keys are in europe-west1, which is good, but if you copy aggregated data to us-central1, that dataset cannot use the same key ring unless you create a key there. Decide explicitly whether the aggregated copy needs CMEK; it likely does, but it is a separate data-residency decision.
Do not copy raw data to us-central1 as a shortcut. That would create a second regulated data store and expand the compliance scope.
3. How would you configure and monitor CMEK to satisfy the regulator, and what operational failure would you test first?
Read the worked answer
Use a Cloud KMS key ring in europe-west1 and a key with a defined rotation period, for example 90 days. BigQuery CMEK protects data at rest, but key rotation does not automatically re-encrypt existing data; BigQuery handles key versions transparently for new writes, and old data remains decryptable with old versions as long as they are enabled. Document that distinction because auditors often assume rotation rewrites all data. Restrict Cloud KMS admin and encrypt/decrypt roles so that no single person can both alter key IAM and export data. Enable Cloud KMS audit logs for encrypt, decrypt, and key version operations.
Monitor for key disablement or destruction, because either can make the dataset unreadable. Create an alert on Cloud KMS key state changes and on BigQuery jobs failing with KMS permission errors. Grant the BigQuery service agent the CryptoKey Encrypt/Decrypt role on the key, and test that revocation breaks access as expected.
The operational failure to test first is loss of the CMEK key or its IAM binding. Simulate a revoked service-agent binding in a non-production dataset and verify that queries fail cleanly, alerts fire, and the restore procedure re-grants the binding without data loss. Also test what happens if the key is disabled during a restore. This is more important than rotation testing because a broken key IAM binding causes an outage, while rotation is routine.