Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Tink Envelope Encryption for Query-Time BigQuery 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 is designing a secure data ingestion and processing architecture to import sensitive financial records from an on-premises data center into Google Cloud. The design must fulfill the following security and cryptographic requirements:
- Sensitive fields must undergo client-side field-level encryption on-premises before being transmitted across a Dedicated Interconnect connection.
- The Data Encryption Keys (DEKs) used for field-level encryption must never be stored in plaintext and must be protected using envelope encryption with Key Encryption Keys (KEKs) hosted in Cloud KMS/Cloud HSM.
- A streaming Dataflow pipeline must ingest the encrypted records and store both the encrypted payload and the wrapped DEK into BigQuery.
- Authorized data analysts must be able to decrypt the granular fields on-demand directly in SQL queries, enforced by IAM permissions on the KEK.
Which cryptographic design and implementation meets these requirements?
Explanations
Encrypt sensitive fields on-premises using the Tink encryption library with local DEKs, wrap the DEKs using a Cloud KMS/Cloud HSM symmetric KEK, ingest the ciphertext and wrapped DEK via Dataflow into BigQuery, and use BigQuery AEAD functions for query-time column decryption.
Encrypt sensitive fields on-premises using the Tink encryption library with local DEKs, wrap the DEKs using a Cloud KMS/Cloud HSM symmetric KEK, ingest the ciphertext and wrapped DEK via Dataflow into BigQuery, and use BigQuery AEAD functions for query-time column decryption.
What the Tink and BigQuery AEAD Envelope Encryption Architecture Is
This architecture leverages the Tink cryptographic library on-premises alongside Cloud Key Management Service (Cloud KMS) or Cloud HSM to implement client-side envelope encryption and column-level access controls. Tink generates localized Data Encryption Keys (DEKs) to encrypt specific sensitive fields (such as credit card numbers or account identifiers) before data leaves the corporate perimeter, while the DEKs themselves are securely wrapped (encrypted) by a centralized Key Encryption Key (KEK) managed in Cloud KMS/Cloud HSM.
How it Meets the Stated Requirements
- Client-Side Field-Level Encryption: By applying Tink locally on-premises, sensitive data attributes are converted into ciphertext prior to transit over Dedicated Interconnect, providing end-to-end payload security.
- Envelope Encryption for DEKs: DEKs are generated per dataset/session and immediately wrapped by the Cloud KMS/Cloud HSM symmetric KEK. Plaintext DEKs are never written to disk or sent across the network.
- Ingestion via Dataflow to BigQuery: Dataflow processes the streaming records containing the ciphertext and wrapped DEKs, loading both into BigQuery storage tables.
- On-Demand SQL Decryption with IAM: BigQuery natively supports AEAD (Authenticated Encryption with Associated Data) decryption functions (such as
AEAD.DECRYPT_STRINGor keyset functions). Analysts running queries unwrap the DEK and decrypt values dynamically at runtime only if their identity has IAMroles/cloudkms.cryptoKeyDecrypterpermissions on the wrapping KEK.
Key Advantages
- Dual-Access Control: Decryption requires access permissions on both the BigQuery table and the Cloud KMS cryptographic key.
- Cryptographic Independence: Google Cloud infrastructure never sees plaintext data in transit or in the ingestion buffer unless authorized by IAM during query execution.
- Standardized Cryptography: Tink provides cross-platform, deterministic, and non-deterministic AEAD primitives that integrate directly with BigQuery standard SQL functions.
- Granular Key Rotation: KEKs can be rotated automatically in Cloud KMS every 30 days without needing to re-encrypt all historical stored data chunks.
Why This Is Optimal
This pattern provides defense-in-depth by combining transport encryption, customer-managed envelope encryption, and granular column-level access control without requiring custom decryption microservices in front of BigQuery.
Generate an asymmetric RSA key pair in Secret Manager, encrypt sensitive columns using the public key on-premises, load the data via Dataflow into BigQuery, and decrypt values during queries using a BigQuery remote User-Defined Function (UDF) that calls Secret Manager.
Generate an asymmetric RSA key pair in Secret Manager, encrypt sensitive columns using the public key on-premises, load the data via Dataflow into BigQuery, and decrypt values during queries using a BigQuery remote User-Defined Function (UDF) that calls Secret Manager.
What this Option Proposes
This pattern generates an asymmetric RSA key pair stored in Secret Manager, encrypts fields on-premises using the public key, loads ciphertexts into BigQuery, and invokes a remote User-Defined Function (UDF) backed by Cloud Functions to fetch the private key from Secret Manager and decrypt records on every query.
Why it Doesn't Meet the Requirement
- No Envelope Encryption with Cloud KMS/Cloud HSM: It bypasses Cloud KMS/Cloud HSM envelope encryption and does not utilize wrapped DEKs protected by a centralized KEK.
- Scalability and Latency Bottlenecks: Calling a remote UDF that repeatedly accesses Secret Manager to perform asymmetric decryption on large analytical datasets creates extreme latency and severe API quota limits.
- Incompatible with Native BigQuery AEAD: BigQuery natively supports optimized symmetric AEAD functions for wrapped keysets rather than external asymmetric decryption via Secret Manager.
Key Limitations
- Secret Manager is designed for configuration secrets and static credentials, not high-throughput cryptographic operations across millions of database rows.
- Asymmetric RSA operations are significantly slower and computationally more expensive than symmetric AES/AEAD algorithms.
This Answer Would Be Correct If
The workload required public-key cryptography for infrequent, single-record external submissions where clients cannot authenticate to Google Cloud APIs to wrap symmetric DEKs.
De-identify data on-premises using the Sensitive Data Protection API via public REST endpoints, write the pseudonymized records to Cloud Storage, and use Cloud Dataproc with Apache Spark to re-identify the data in memory before saving to BigQuery.
De-identify data on-premises using the Sensitive Data Protection API via public REST endpoints, write the pseudonymized records to Cloud Storage, and use Cloud Dataproc with Apache Spark to re-identify the data in memory before saving to BigQuery.
What this Option Proposes
This approach sends on-premises cleartext data to the Sensitive Data Protection (Cloud DLP) API over public internet endpoints for tokenization/masking, writes the output to Cloud Storage, and launches a Cloud Dataproc Spark job to perform bulk in-memory re-identification prior to loading into BigQuery.
Why it Doesn't Meet the Requirement
- Fails Client-Side Encryption Mandate: Sending unencrypted sensitive records over public API endpoints to Google Cloud does not fulfill the requirement for localized, client-side encryption prior to transit.
- No Envelope Encryption with Wrapped DEKs: It relies on tokenization transformations rather than local DEKs wrapped by Cloud KMS/Cloud HSM KEKs.
- Re-identification in Batch Processing: Re-identifying the data in batch inside Dataproc materializes cleartext data in BigQuery instead of enabling selective, on-demand query-time AEAD decryption for authorized analysts.
Key Limitations
- Cloud DLP API calls across external endpoints introduce network overhead and do not protect the data on-premises before departure.
- Storing re-identified data directly in BigQuery removes column-level cryptographic boundaries.
This Answer Would Be Correct If
The business required central automated masking and tokenization of unstructured text datasets where analysts must never access raw PII under any circumstance.
Encrypt full files on-premises using Customer-Supplied Encryption Keys (CSEK), store the plaintext DEKs inside Pub/Sub message attributes, decrypt the files inside Dataflow workers using custom transforms, and write the raw plaintext data into BigQuery tables protected with CMEK default dataset keys.
Encrypt full files on-premises using Customer-Supplied Encryption Keys (CSEK), store the plaintext DEKs inside Pub/Sub message attributes, decrypt the files inside Dataflow workers using custom transforms, and write the raw plaintext data into BigQuery tables protected with CMEK default dataset keys.
What this Option Proposes
This approach uses Customer-Supplied Encryption Keys (CSEK) at the file level on-premises, transmits the plaintext Data Encryption Key within Cloud Pub/Sub message attributes, performs full decryption inside the Dataflow worker pipeline, and writes the resulting cleartext into a BigQuery dataset configured with Customer-Managed Encryption Keys (CMEK).
Why it Doesn't Meet the Requirement
- Plaintext DEK Exposure: Passing the plaintext DEK within Pub/Sub message metadata violates the core requirement that DEKs must never exist or travel in plaintext.
- Lack of Field-Level and End-to-End Cryptography: Decrypting the payload inside Dataflow and writing plaintext columns to BigQuery exposes the sensitive fields to any user or service account with read access to the BigQuery table, eliminating column-level dual-access control.
- No Query-Time AEAD Decryption: Because the data lands in cleartext within BigQuery (relying solely on storage-level CMEK at rest), analysts do not decrypt fields on-demand with BigQuery SQL functions.
Key Limitations
- Storage-level CMEK encrypts underlying physical disk chunks at rest but presents cleartext directly to any user with table
SELECTprivileges. - Transmitting unencrypted cryptographic keys inside message queues creates severe compliance and data leakage risks.
This Answer Would Be Correct If
The objective was coarse-grained storage encryption where Dataflow is trusted to process sensitive records entirely in cleartext before persisting them to standard CMEK-encrypted persistent storage.
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
BigQuery Cloud Storage Default CMEK Encryption
Data security (encryption and key management)
AdvancedProfessional Data Engineer
Cloud KMS Imported Key Rotation
Data security (encryption and key management)
AdvancedProfessional Data Engineer
BigQuery GoogleSQL UDFs With MAX_BY
Processing logic
AdvancedProfessional Data Engineer
Dataflow Dead-Letter Queue Pattern
Integrating with new data sources
AdvancedProfessional Data Engineer
Cloud Composer Custom Lineage Inlets Outlets
Processing data
AdvancedProfessional Cloud Security Engineer
Active/Active Multi-Gateway HA VPN with ECMP
Designing and configuring private connectivity and encryption between data centers and VPC network (e.g., HA VPN, Cloud Interconnect).
Last updated Validated