Jump to Content
Security & Identity

Announcing BigQuery Encrypt and Decrypt function compatibility with Sensitive Data Protection

June 5, 2024
Lanre Ogunmola

Cloud Security Architect

Parth Desai

Customer Engineer, Data and AI

Try Gemini 1.5 models

Google's most advanced multimodal models in Vertex AI

Try it

Organizations collect vast amounts of data to create innovative solutions, perform ground breaking research, or optimize their designs. With this comes the responsibility to ensure data is adequately protected to meet regulatory, compliance, contractual or internal security requirements.

For organizations that want to move their data warehouses from on-premises to cloud-first systems, such as BigQuery, protecting sensitive data from unauthorized access or accidental exposure is crucial. Using encryption-based tokenization is a vital tool to create an additional layer of defense and fine-grained data control. 

In addition to storage-level encryption, whether using Google-managed or customer-managed keys, BigQuery now has seamless integration with Sensitive Data Protection supporting native SQL functions that allow interoperable deterministic encryption and decryption between BigQuery and Sensitive Data Protection. 

In short, this makes it easier to protect sensitive data across a variety of scenarios:

  • Protect sensitive data in BigQuery: Securely protect data containing personally identifiable information (PII), healthcare records, or financial data at query time while maintaining compliance with regulations.

  • Share sensitive data securely: Collaborate with  external parties (partners or consumers) while keeping sensitive information protected by sharing encrypted data (encrypted externally with Sensitive Data Protection), providing decryption keys separately and decrypting with function in BigQuery.

  • Compatible tokenization anywhere you need it:  Whether you create tokens with the Sensitive Data Protection APIs for workloads outside of BigQuery or create tokens natively in BigQuery, you can join, aggregate, and keep referential integrity where you need it. 

  • Improved performance:  Enhanced performance for example, like functions, leverages BigQuery distributed architecture to execute Sensitive Data Protection token-based encryption and decryption tasks natively in parallel across multiple BigQuery nodes to significantly accelerate the operations.


Using Sensitive Data Protection functions in BigQuery

Here are the steps to get you started:

1. Identify your sensitive data: Use the Sensitive Data Protection discovery service to pinpoint BigQuery tables and columns containing confidential information.

2. Generate your encryption keys: generate your data keys and use Cloud KMS to protect your data keys.

3. Apply encryption: Use DLP_DETERMINISTIC_ENCRYPT to encrypt the identified data fields.



4. Store and process securely: Continue working with your encrypted data within BigQuery, safe in the knowledge that it's protected.

Query from encrypted table


 Run aggregates on encrypted columns:


5. Decrypt when needed: Use DLP_DETERMINISTIC_DECRYPT to access the original data only when necessary at query time and for authorized users.


Query from decrypted records:



Next steps

Sensitive Data Protection and BigQuery data security functions are powerful tools for protecting sensitive data in the cloud. By understanding how they function, and how their capabilities can be best used, you can enhance your data security posture, reduce the risk of data breaches, and help with the confidentiality of sensitive information while safeguarding your privacy.

Ready to dive deeper? Check out the Sensitive Data Protection: DLP-compatible encrypt function documentation for detailed instructions.

Posted in