Professional Data EngineerManaging and securing dataMedium
A pharmaceutical company stores sensitive clinical trial data in BigQuery. The data must be accessible to researchers for analysis, but direct access to the raw tables is prohibited to prevent accidental exposure of PII. Researchers should only be able to query a subset of the data (e.g., de-identified patient outcomes) that is pre-filtered and aggregated. All access to this derived data must also be auditable. Which BigQuery feature, combined with appropriate logging, should be used to achieve this?
- ABigQuery Column-level Security with Cloud Audit Logs
- BBigQuery Authorized Views with Cloud Audit Logs
- CBigQuery Row-level Security with Cloud Audit Logs
- DBigQuery Data Masking with Cloud Audit Logs
Show answer & explanationAnswer & explanation
Correct answer: B. BigQuery Authorized Views with Cloud Audit Logs
BigQuery Authorized Views allow creating views that expose only a filtered, aggregated, or de-identified subset of data from underlying tables without granting direct access to those tables. When combined with Cloud Audit Logs, all queries against these views are recorded, providing the necessary audit trail.
Why the other options are wrong
- A. Column-level Security restricts access to entire columns, not a pre-filtered or aggregated subset of data.
- C. Row-level Security restricts access to specific rows, but the requirement is for a pre-filtered/aggregated subset, which views are better suited for.
- D. Data Masking obfuscates data within columns but doesn't inherently filter or aggregate data into a subset for access.
BigQuery Authorized Views
A BigQuery feature that allows users to query data through a view without having direct access to the underlying tables, enabling data sharing while maintaining strict access control and data privacy.
- The view owner grants access to the view, not the underlying tables.
- Can be used to filter, project, or aggregate data.
- Queries against views are subject to the view's definition.
- All access is auditable via Cloud Audit Logs.
Memory trick: View the data, but don't touch the raw, audit every glance, to follow the law.