A security engineer runs the above query in BigQuery and finds a column containing Social Security Numbers (SSNs). They must ensure that all SSNs in the dataset are automatically encrypted at rest using customer-managed keys (CMEK) and also that future queries do not expose SSNs to users without decryption access. Which approach should they take?
CMEK secures data at rest, and authorized views limit exposure.
Why this answer
Enabling CMEK on the dataset ensures all data, including SSNs, is encrypted at rest with customer-managed keys. Creating an authorized view that excludes the sensitive column and granting view access to users prevents exposure of SSNs in query results while still allowing access to non-sensitive data. This approach meets both the encryption and access control requirements without requiring manual encryption or de-identification.
Exam trap
Google Cloud often tests the misconception that manual encryption (e.g., Cloud KMS or DLP) is required for column-level protection, when in fact BigQuery's native CMEK combined with authorized views provides automatic encryption at rest and access control without custom encryption logic.
How to eliminate wrong answers
Option A is wrong because creating a separate table with encrypted columns does not automatically encrypt existing SSNs at rest; CMEK on the dataset encrypts all data at rest, but the separate table approach adds complexity and does not prevent future queries from exposing SSNs unless access is restricted. Option B is wrong because Cloud DLP de-identifies data (e.g., masking or tokenization) but does not encrypt at rest with CMEK; it also does not prevent users with direct table access from querying the original SSNs if the de-identified column is separate. Option C is wrong because using Cloud KMS to encrypt column values and storing encrypted data in BigQuery requires manual encryption/decryption logic in queries, which is error-prone and does not leverage BigQuery's native CMEK for automatic encryption at rest; it also fails to prevent exposure if users have access to the raw encrypted data.