Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
BigQuery Column-Level Security with AEAD Decryption
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
An enterprise financial organization is designing an ingestion pipeline to integrate continuous, high-volume transactional logs from an on-premises database into a secured BigQuery data warehouse.
The technical, analytical, and compliance requirements are as follows:
- Throughput & Network: High-bandwidth, low-latency private connectivity to transfer high-velocity streaming records into Google Cloud.
- Client-Side Data Protection: Highly sensitive columns (such as credit card numbers) must be encrypted on-premises prior to ingestion into the cloud.
- Granular Decryption & Masking: In BigQuery, authorized fraud investigators must be able to run SQL queries to decrypt the protected data on-demand, whereas general analysts querying the same table must only see dynamically masked values.
- Key Governance: Encryption keys must be hardware-backed with Cloud HSM and centrally managed via customer-managed encryption keys (CMEK).
Which ingestion and security design should the data engineering team implement?
Explanations
Establish an IPsec Cloud VPN tunnel; ingest raw plaintext transaction records into Cloud Storage; schedule a Sensitive Data Protection (DLP) de-identification inspection job to rewrite objects using customer-supplied encryption keys (CSEK); load the output into BigQuery tables with row-level security policies.
Establish an IPsec Cloud VPN tunnel; ingest raw plaintext transaction records into Cloud Storage; schedule a Sensitive Data Protection (DLP) de-identification inspection job to rewrite objects using customer-supplied encryption keys (CSEK); load the output into BigQuery tables with row-level security policies.
What This Option Proposes
This option relies on transmitting plaintext data over an IPsec Cloud VPN tunnel into a staging Cloud Storage bucket, executing batch Sensitive Data Protection (Cloud DLP) inspection jobs after ingestion, and managing disk encryption via customer-supplied encryption keys (CSEK).
Why It Doesn't Meet the Requirements
- Violates Client-Side Encryption: Transmitting plaintext records and staging them in Cloud Storage before de-identification fails the strict compliance rule requiring data to be encrypted on-premises prior to cloud ingestion.
- Network Limitations: Cloud VPN relies on public internet routing and is constrained by bandwidth limits compared to Dedicated Interconnect, making it less suited for continuous, massive streaming volume.
- Lacks Native AEAD SQL Decryption: Rewriting files with CSEK protects storage blocks but does not enable granular, column-level on-demand AEAD decryption inside BigQuery queries.
- Incorrect Access Control Level: Row-level security filters entire rows based on predicate conditions, rather than masking specific sensitive columns while preserving table row visibility for general reporting.
Key Limitations
- Post-ingestion inspection creates a security gap where cleartext sensitive data resides in cloud storage prior to batch execution.
- CSEK requires the client to supply raw cryptographic keys with every API call and does not integrate with BigQuery SQL AEAD functions or Cloud HSM managed rotation.
This Answer Would Be Correct If
The pipeline handled low-volume batch files where sensitive data discovery had to be automated across unclassified incoming files, and filtering entire records by user tenant was sufficient.
Stream records over public HTTPS endpoints to Cloud Run functions; tokenize sensitive identifiers irreversibly using cryptographic hashing; write files into Cloud Storage using BigLake tables; apply BigQuery Storage Read API ACLs to govern field access.
Stream records over public HTTPS endpoints to Cloud Run functions; tokenize sensitive identifiers irreversibly using cryptographic hashing; write files into Cloud Storage using BigLake tables; apply BigQuery Storage Read API ACLs to govern field access.
What This Option Proposes
This approach ingests events via Cloud Run functions over public HTTPS, executes one-way cryptographic hashing on credit card fields, writes files to Cloud Storage, and exposes them as BigLake tables managed by Cloud Storage access control lists (ACLs).
Why It Doesn't Meet the Requirements
- Irreversible Hashing Prevents Decryption: One-way tokenization (hashing) permanently destroys the underlying plaintext, making it impossible for authorized fraud investigators to decrypt and inspect the original card numbers in BigQuery.
- Insecure Ingress Architecture: Streaming high-volume enterprise financial logs over public HTTPS to Cloud Run lacks the high-throughput, private dedicated routing provided by Dedicated Interconnect.
- Improper Access Governance: BigQuery does not use Cloud Storage ACLs for fine-grained column masking. BigLake tables require BigQuery IAM and policy tags for column-level security.
- Fails Client-Side Cloud HSM Standard: On-premises encryption using Tink and Cloud HSM KEK envelope encryption is bypassed entirely.
Key Limitations
- Cryptographic hashing eliminates analytical utility for fraud investigation where exact values must be verified.
- Cloud Storage ACLs are not recommended over IAM and cannot deliver dynamic query-time column masking in SQL engines.
This Answer Would Be Correct If
The system required irreversible pseudonymization of user identifiers for machine learning training datasets where original values should never be recovered.
Establish a Dedicated Interconnect connection; encrypt sensitive fields on-premises using the Tink library with Cloud HSM-wrapped keys; ingest the encrypted columns and wrapped keys into BigQuery; configure policy tags with dynamic data masking and grant the Fine-Grained Reader role to authorized investigators to use BigQuery AEAD decryption SQL functions.
Establish a Dedicated Interconnect connection; encrypt sensitive fields on-premises using the Tink library with Cloud HSM-wrapped keys; ingest the encrypted columns and wrapped keys into BigQuery; configure policy tags with dynamic data masking and grant the Fine-Grained Reader role to authorized investigators to use BigQuery AEAD decryption SQL functions.
What the Recommended Architecture Is
This architecture combines high-throughput private network connectivity with client-side envelope encryption and native BigQuery column-level security to establish an end-to-end secured data ingestion and analytics pipeline.
How It Meets the Requirements
- High-Bandwidth, Low-Latency Networking: Dedicated Interconnect provides a direct physical link between the on-premises network and Google Cloud, providing the high throughput and predictable latency necessary for high-velocity transactional data.
- Client-Side Encryption: Utilizing the Tink encryption library on-premises allows the organization to encrypt sensitive fields (like credit card numbers) before data departs the private data center. Tink utilizes Data Encryption Keys (DEKs) that are wrapped by a Key Encryption Key (KEK) managed in Cloud HSM as a symmetric CMEK.
- Granular Decryption and Dynamic Masking: Ingesting both the ciphertext and the wrapped DEK into BigQuery enables authorized users to perform AEAD decryption (
AEAD.DECRYPT_STRING) at query runtime. Applying policy tags with dynamic data masking ensures unauthorized general analysts only see masked values, while fraud analysts with the Fine-Grained Reader (roles/datacatalog.categoryFineGrainedReader) role can access unmasked data and invoke AEAD decryption functions. - Hardware-Backed Key Governance: The KEKs are hosted in Cloud HSM, satisfying FIPS-compliant hardware security standards with automated 30-day rotation.
Key Advantages
- Dual-Access Control: Decryption requires permissions to both the BigQuery table and the underlying Cloud KMS/HSM cryptographic key.
- Zero Plaintext Exposure: Sensitive columns are never exposed in cleartext across the network perimeter or to unprivileged warehouse users.
- Standard SQL Usability: Analysts leverage native GoogleSQL AEAD functions directly inside BigQuery without custom external services.
Why This Is Optimal
This pattern aligns with the Google Cloud Secured Data Warehouse blueprint for external data sources, addressing throughput, client-side compliance, and granular role-based SQL access controls.
Deploy an Apache Sqoop cluster on Cloud Dataproc over Dedicated Interconnect; batch load relational data directly into BigQuery; apply column-level encryption using Cloud KMS software keys; configure IAM table-level permissions to restrict unauthorized analyst access.
Deploy an Apache Sqoop cluster on Cloud Dataproc over Dedicated Interconnect; batch load relational data directly into BigQuery; apply column-level encryption using Cloud KMS software keys; configure IAM table-level permissions to restrict unauthorized analyst access.
What This Option Proposes
This approach uses Apache Sqoop hosted on a Cloud Dataproc cluster to extract data from the on-premises database via JDBC in batch mode and load it directly into BigQuery, applying software-based Cloud KMS column encryption.
Why It Doesn't Meet the Requirements
- No Client-Side Envelope Encryption: Sqoop reads plaintext via JDBC and moves data across network boundaries before software keys can be applied, failing the requirement for local on-premises client-side encryption.
- Batch vs. Continuous Streaming: Apache Sqoop is a legacy batch extraction utility designed for Hadoop environments, making it unsuitable for continuous high-velocity streaming ingestion.
- Non-Compliant Key Management: Software-based Cloud KMS keys do not meet the explicit requirement for hardware-backed Cloud HSM key governance.
- Coarse Access Control: Relying exclusively on IAM table-level permissions forces binary access (either a user can query the entire table or none of it), failing the requirement for shared tables where general analysts see masked columns and fraud investigators see decrypted columns.
Key Limitations
- Sqoop requires managing cluster compute infrastructure on Dataproc.
- Table-level IAM cannot dynamically mask fields per user role within a single dataset.
This Answer Would Be Correct If
The workload was a scheduled periodic bulk migration of legacy relational tables where field-level obfuscation was not required and Dataproc was the primary compute framework.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
Dataflow Dead-Letter Queue Pattern
Integrating with new data sources
AdvancedProfessional Data Engineer
Dataproc Ephemeral Clusters with Secondary Workers
Deciding between persistent or job-based data clusters (e.g., Dataproc)
AdvancedProfessional Data Engineer
Cloud Storage Lifecycle and BigQuery Partitioning
Managing the lake (configuring data discovery, access, and cost controls)
AdvancedProfessional Data Engineer
Dataflow Regional Execution Without Zone Pinning
Running jobs in multiple regions or zones
AdvancedProfessional Data Engineer
BigQuery Facade Views
Mapping current and future business requirements to the architecture
AdvancedProfessional Cloud DevOps Engineer
Compute Engine MIG Canary Deployment
Safe and secure patching and upgrading practices
Last updated Validated