PII, retention, and cost split in a shared marketing dataset
Helios Health has a BigQuery dataset named marketing with 40 tables and 120 users across six teams. One table, customer_contacts, contains email, phone, and postal_address alongside customer_id and region. Today every user in the project has roles/bigquery.dataViewer at project level, and analysts query customer_contacts directly. An audit found this violates policy: only the CRM team of 12 people may see email and phone; the 60 regional analysts may see postal_address only for their own region; 5 auditors need read access to all columns but no export. Data must be retained for 400 days, then deleted; some tables currently have no expiration. Finance wants BigQuery cost split by team. The security team prefers least privilege and wants no disruption to existing SQL where possible. The company uses Cloud Identity groups and has not enabled column-level IAM beyond basic roles.
1. Design the access model for the three groups. What BigQuery features would you use, and what would you avoid?
Read the worked answer
Remove project-level bigquery.dataViewer from all 120 users. Use Cloud Identity groups: crm-team, regional-analysts, and auditors. Put policy tags on email and phone in Data Catalog and grant fine-grained reader only to crm-team. Regional analysts should query an authorized view that exposes customer_id, region, postal_address and applies a row access policy or a region-mapping join so each analyst sees only their region. Auditors get an authorized view with all columns and a custom role that includes read but excludes bigquery.tables.export, so they cannot export.
Avoid granting column-level IAM directly on the table as the primary control; policy tags are designed for this and scale across tables. Avoid breaking existing SQL by creating views with the same names in a separate dataset and granting access there. The judgement is to combine column-level policy tags, row-level controls, and authorized views rather than relying on one feature.
2. How would you implement the 400-day retention requirement across tables that may or may not be partitioned?
Read the worked answer
Set a dataset-level default table expiration of 400 days for new tables, but do not assume it applies retroactively. For existing partitioned tables, set partition expiration to 400 days. For tables without a date column, add ingestion-time partitioning where possible, or set an explicit table expiration. Inventory all 40 tables and record which mechanism applies. Exclude any table under legal hold and document the exception.
The judgement is that retention is not one setting. defaultTableExpirationMs affects new tables; partition expiration deletes old partitions; table expiration deletes the whole table. Using only one can leave old data behind or delete a table that should remain. Add a scheduled audit query against INFORMATION_SCHEMA.TABLE_OPTIONS and PARTITIONS to find tables missing expiration. Test on a copy first because expiration deletes data permanently.
3. Recommend a cost-attribution approach that Finance can use without breaking analyst workflows.
Read the worked answer
Use labels on datasets, scheduled queries, and jobs, and expose a cost dashboard from INFORMATION_SCHEMA.JOBS_BY_PROJECT grouped by team label. If labels are incomplete, they will undercount. For stronger attribution, move each team's workloads into separate projects or folders so billing export is unambiguous, while keeping shared data in an authorized dataset. Set custom quotas per team to cap runaway queries. Charge back reserved slots if the company uses commitments.
The judgement is that labels require discipline and can be omitted by ad hoc users. Project separation is more reliable but adds access-management overhead. A practical compromise is to start with labels and quotas, then split the heaviest teams into their own projects. Do not block analysts from querying; make the default path use governed views and scheduled queries that carry labels automatically.