PDE Storing the Data Practice Question
A healthcare organization stores patient data in BigQuery. They need to encrypt a specific column (e.g., SSN) using a key they manage, and decrypt it only for authorized queries via a user-defined function. Which approach should they use?
⚠ Common exam trap
A common mistake is to choose dataset-level encryption (CMEK) because it involves Cloud KMS, but CMEK does not allow per-column encryption or UDF-controlled decryption. The correct approach uses BigQuery AEAD encryption functions with a Cloud KMS key for column-level encryption and authorized decryption via a UDF.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use BigQuery AEAD encryption functions with a Cloud KMS key
BigQuery AEAD encryption functions (e.g., `AEAD.ENCRYPT` and `AEAD.DECRYPT`) allow you to encrypt a specific column using a customer-managed key stored in Cloud KMS, and then decrypt it only within a user-defined function (UDF) that enforces access controls. This meets the requirement of per-column encryption with key management and authorized decryption via a UDF.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use BigQuery AEAD encryption functions with a Cloud KMS key
Why this is correct
BigQuery AEAD functions encrypt and decrypt individual column values using a Cloud KMS key the organisation controls, and can be invoked inside a user-defined function. This satisfies both the customer-managed key and authorised-query decryption constraints.
- ✗
Use BigQuery column-level access controls
Why it's wrong here
Column-level access controls restrict who may read a column, but they do not encrypt values or provide a user-defined function for decryption. It is tempting because they target a specific column such as SSN, and would be correct for limiting analysts' visibility of sensitive fields while leaving data in plaintext.
- ✗
Use Cloud Key Management Service (Cloud KMS) with CMEK for the BigQuery dataset
Why it's wrong here
CMEK encrypts the entire dataset at rest with a Cloud KMS key, but decryption is transparent to every query, so no user-defined function decrypts a single column. It is tempting because CMEK does let you manage your own keys, and would be correct for meeting at-rest encryption or key-rotation compliance requirements.
- ✗
Use Cloud Data Loss Prevention (DLP) to de-identify the column
Why it's wrong here
DLP de-identifies data by masking, tokenising or redacting values, which is irreversible or format-preserving rather than reversible decryption via a user-defined function. It is tempting because DLP handles sensitive columns like SSNs, and would be correct for sanitising data before analytics or sharing.
About these practice questions
One of 747 original PDE practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PDE exam.