Import data from an external network into a secured BigQuery data warehouse

Last reviewed 2023-08-15 UTC

Many organizations deploy data warehouses that store confidential information so that they can analyze the data for various business purposes. This document is intended for data engineers and security administrators who deploy and secure data warehouses using BigQuery. It's part of a security blueprint that includes the following:

  • A GitHub repository that contains a set of Terraform configurations and scripts. The Terraform configuration sets up an environment in Google Cloud that supports a data warehouse that stores confidential data.
  • A guide to the architecture, design, and security controls that you use this blueprint to implement (this document).

This document discusses the following:

  • The architecture and Google Cloud services that you can use to help secure a data warehouse in a production environment.
  • Best practices for importing data into BigQuery from an external network such as an on-premises environment.
  • Best practices for data governance when creating, deploying, and operating a data warehouse in Google Cloud, including column-level encryption, differential handling of confidential data, and column-level access controls.

This document assumes that you have already configured a foundational set of security controls as described in the Google Cloud enterprise foundations blueprint. It helps you to layer additional controls onto your existing security controls to help protect confidential data in a data warehouse.

Data warehouse use cases

The blueprint supports the following use cases:

Overview

Data warehouses such as BigQuery let businesses analyze their business data for insights. Analysts access the business data that is stored in data warehouses to create insights. If your data warehouse includes data that you consider confidential, you must take measures to preserve the security, confidentiality, integrity, and availability of the business data while it's imported and stored, while it's in transit, or while it's being analyzed. In this blueprint, you do the following:

  • Encrypt your source data that's located outside of Google Cloud (for example, in an on-premises environment) and import it into BigQuery.
  • Configure controls that help secure access to confidential data.
  • Configure controls that help secure the data pipeline.
  • Configure an appropriate separation of duties for different personas.
  • Set up appropriate security controls and logging to help protect confidential data.
  • Use data classification, policy tags, dynamic data masking, and column-level encryption to restrict access to specific columns in the data warehouse.

Architecture

To create a confidential data warehouse, you need to import data securely and then store the data in a VPC Service Controls perimeter. The following image shows how data is ingested and stored.

The secured data warehouse architecture for external networks.

The architecture uses a combination of the following Google Cloud services and features:

  • Dedicated Interconnect lets you move data between your network and Google Cloud. You can use another connectivity option, as described in Choosing a Network Connectivity product.

  • Identity and Access Management (IAM) and Resource Manager restrict access and segment resources. The access controls and resource hierarchy follow the principle of least privilege.

  • VPC Service Controls creates security perimeters that isolate services and resources by setting up authorization, access controls, and secure data exchange. The perimeters are as follows:

    • A data ingestion perimeter that accepts incoming data (in batch or stream). A separate perimeter helps to protect the rest of your workloads from incoming data.
    • A data perimeter that isolates the encryption data from other workloads.
    • A governance perimeter that stores the encryption keys and defines what is considered confidential data.

    These perimeters are designed to protect incoming content, isolate confidential data by setting up additional access controls and monitoring, and separate your governance from the actual data in the warehouse. Your governance includes key management, data catalog management, and logging.

  • Cloud Storage and Pub/Sub receive data as follows:

  • Cloud Functions is triggered by Cloud Storage and writes the data that Cloud Storage uploads to the ingestion bucket into BigQuery.

  • A Dataflow pipeline writes streaming data into BigQuery. To protect data, Dataflow uses a unique service account and access controls. To help secure pipeline execution by moving it to the backend service, Dataflow uses Streaming Engine. For more information, see Dataflow security and permissions.

  • Cloud Data Loss Prevention (Cloud DLP) scans data that is stored in BigQuery to find any sensitive data that isn't protected. For more information, see Using Cloud DLP to scan BigQuery data.

  • Cloud HSM hosts the key encryption key (KEK). Cloud HSM is a cloud-based Hardware Security Module (HSM) service. You use Cloud HSM to generate the encryption key that you use to encrypt the data in your network before sending it to Google Cloud.

  • Data Catalog automatically categorizes confidential data with metadata, also known as policy tags, when it's discovered in BigQuery. Data Catalog also uses metadata to manage access to confidential data. For more information, see Data Catalog overview. To control access to data within the data warehouse, you apply policy tags to columns that include confidential data.

  • BigQuery stores the encrypted data and the wrapped encryption key in separate tables.

    BigQuery uses various security controls to help protect content, including access controls, column-level encryption, column-level security, and data encryption.

  • Security Command Center monitors and reviews security findings from across your Google Cloud environment in a central location.

  • Cloud Logging collects all the logs from Google Cloud services for storage and retrieval by your analysis and investigation tools.

  • Cloud Monitoring collects and stores performance information and metrics about Google Cloud services.

  • Data Profiler for BigQuery automatically scans for sensitive data in all BigQuery tables and columns across the entire organization, including all folders and projects.

Organization structure

You group your organization's resources so that you can manage them and separate your testing environments from your production environment. Resource Manager lets you logically group resources by project, folder, and organization.

The following diagram shows you a resource hierarchy with folders that represent different environments such as bootstrap, common, production, non-production (or staging), and development. This hierarchy aligns with the organization structure used by the enterprise foundations blueprint. You deploy most of the projects in the blueprint into the production folder, and the Data governance project in the common folder which is used for governance.

The resource hierarchy for a secured data warehouse for external networks.

For alternative resource hierarchies, see Decide a resource hierarchy for your Google Cloud landing zone.

Folders

You use folders to isolate your production environment and governance services from your non-production and testing environments. The following table describes the folders from the enterprise foundations blueprint that are used by this blueprint.

Folder Description
Bootstrap Contains resources required to deploy the enterprise foundations blueprint.
Common Contains centralized services for the organization, such as the Data governance project.
Production Contains projects that have cloud resources that have been tested and are ready to use. In this blueprint, the Production folder contains the Data ingestion project and Data project.
Non-production Contains projects that have cloud resources that are currently being tested and staged for release. In this blueprint, the Non-production folder contains the Data ingestion project and Data project.
Development Contains projects that have cloud resources that are currently being developed. In this blueprint, the Development folder contains the Data ingestion project and Data project.

You can change the names of these folders to align with your organization's folder structure, but we recommend that you maintain a similar structure. For more information, see the Google Cloud enterprise foundations blueprint.

Projects

You isolate parts of your environment using projects. The following table describes the projects that are needed within the organization. You create these projects when you run the Terraform code. You can change the names of these projects, but we recommend that you maintain a similar project structure.

Project Description
Data ingestion Contains services that are required to receive data and write it to BigQuery.
Data governance Contains services that provide key management, logging, and data cataloging capabilities.
Data Contains services that are required to store data.

In addition to these projects, your environment must also include a project that hosts a Dataflow Flex Template job. The Flex Template job is required for the streaming data pipeline.

Mapping roles and groups to projects

You must give different user groups in your organization access to the projects that make up the confidential data warehouse. The following sections describe the blueprint recommendations for user groups and role assignments in the projects that you create. You can customize the groups to match your organization's existing structure, but we recommend that you maintain a similar segregation of duties and role assignment.

Data analyst group

Data analysts view and analyze the data in the warehouse. This group can view data after it has been loaded into the data warehouse and perform the same operations as the Encrypted data viewer group. This group requires roles in different projects, as described in the following table.

Scope of assignment Roles
Data ingestion project
Data project
Data policy level Masked Reader (roles/bigquerydatapolicy.maskedReader)

Encrypted data viewer group

The Encrypted data viewer group can view encrypted data from BigQuery reporting tables through Cloud Looker Studio and other reporting tools, such as SAP Business Objects. The encrypted data viewer group can't view cleartext data from encrypted columns.

This group requires the BigQuery User (roles/bigquery.jobUser) role in the Data project. This group also requires the Masked Reader (roles/bigquerydatapolicy.maskedReader) at the data policy level.

Plaintext reader group

The Plaintext reader group has the required permission to call the decryption user-defined function (UDF) to view plaintext data and the additional permission to read unmasked data. This group requires roles in Data project, as described in the following table.

This group requires the following roles in the Data project:

In addition, this group requires the Fine-Grained Reader (roles/datacatalog.categoryFineGrainedReader) role at the Data Catalog level.

Data engineer group

Data engineers set up and maintain the data pipeline and warehouse. This group requires roles in different projects, as described in the following table.

Scope of assignment Roles
Data ingestion project
Data project
  • BigQuery Data Editor (roles/bigquery.dataeditor)
  • BigQuery Job User (roles/bigquery.jobUser)
  • Cloud Build Editor (roles/cloudbuild.builds.editor)
  • Cloud KMS Viewer (roles/cloudkms.viewer)
  • Compute Network User (roles/compute.networkuser)
  • Dataflow Admin (roles/dataflow.admin)
  • DLP Administrator (roles/dlp.admin)
  • Logs Viewer (roles/logging.viewer)

Network administrator group

Network administrators configure the network. Typically, they are members of the networking team.

Network administrators require the following roles at the organization level:

  • Compute Admin (roles/compute.networkAdmin)
  • Logs Viewer (roles/logging.viewer)

Security administrator group

Security administrators administer security controls such as access, keys, firewall rules, VPC Service Controls, and the Security Command Center.

Security administrators require the following roles at the organization level:

Security analyst group

Security analysts monitor and respond to security incidents and Cloud DLP findings.

Security analysts require the following roles at the organization level:

Example group access flows

The following sections describe access flows for two groups within the secured data warehouse solution.

Access flow for Encrypted data viewer group

The following diagram shows what occurs when a user from the Encrypted data viewer group tries to access encrypted data in BigQuery.

The flow for the encrypted data viewer group.

The steps to access data in BigQuery are as follows:

  1. The Encrypted data viewer executes the following query on BigQuery to access confidential data:

    SELECT ssn, pan FROM cc_card_table

  2. BigQuery verifies access as follows:

    • The user is authenticated using valid, unexpired Google Cloud credentials.
    • The user identity and the IP address that the request originated from are part of the allowlist in the Access Level/Ingress rule on the VPC Service Controls perimeter.
    • IAM verifies that the user has the appropriate roles and is authorized to access selected encrypted columns on the BigQuery table.

BigQuery returns the confidential data in encrypted format.

Access flow for Plaintext reader group

The following diagram shows what occurs when a user from the Plaintext reader group tries to access encrypted data in BigQuery.

The flow for the plaintext reader group.

The steps to access data in BigQuery are as follows:

  1. The Plaintext reader executes the following query on BigQuery to access confidential data in decrypted format:

    SELECT decrypt_ssn(ssn) FROM cc_card_table

  2. BigQuery calls the decrypt user-defined function (UDF) within the query to access protected columns.

  3. Access is verified as follows:

    • IAM verifies that the user has appropriate roles and is authorized to access the decrypt UDF on BigQuery.
    • The UDF retrieves the wrapped data encryption key (DEK) that was used to protect sensitive data columns.

    The decrypt UDF calls the key encryption key (KEK) in Cloud HSM to unwrap the DEK. The decrypt UDF uses the BigQuery AEAD decrypt function to decrypt the sensitive data columns.

  4. The user is granted access to the plaintext data in the sensitive data columns.

Understanding the security controls you need

This section discusses the security controls within Google Cloud that you use to help to secure your data warehouse. The key security principles to consider are as follows:

  • Secure access by adopting least privilege principles.
  • Secure network connections through segmentation design and policies.
  • Secure the configuration for each of the services.
  • Classify and protect data based on its risk level.
  • Understand the security requirements for the environment that hosts the data warehouse.
  • Configure sufficient monitoring and logging for detection, investigation, and response.

Security controls for data ingestion

To create your data warehouse, you must transfer data from another source in your on-premises environment, another cloud, or another Google Cloud source. This document focuses on transferring data from your on-premises environment or another cloud; if you're transferring data from another Google Cloud source, see Import data from Google Cloud into a secured BigQuery data warehouse.

You can use one of the following options to transfer your data into the data warehouse on BigQuery:

  • A batch job that loads data to a Cloud Storage bucket.
  • A streaming job that uses Pub/Sub.

To help protect data during ingestion, you can use client-side encryption, firewall rules, and access level policies. The ingestion process is sometimes referred to as an extract, transform, load (ETL) process.

Encrypted connection to Google Cloud

You can use Cloud VPN or Cloud Interconnect to protect all data that flows between Google Cloud and your environment. This blueprint recommends Dedicated Interconnect, because it provides a direct connection and high throughput, which are important if you're streaming a lot of data.

To permit access to Google Cloud from your environment, you must define allowlisted IP addresses in the access levels policy rules.

Network and firewall rules

Virtual Private Cloud (VPC) firewall rules control the flow of data into the perimeters. You create firewall rules that deny all egress, except for specific TCP port 443 connections from the restricted.googleapis.com special domain names. The restricted.googleapis.com domain has the following benefits:

  • It helps reduce your network attack surface by using Private Google Access when workloads communicate to Google APIs and services.
  • It ensures that you only use services that support VPC Service Controls.

For more information, see Configuring Private Google Access.

The data pipeline requires you to open TCP ports in the firewall, as defined in the dataflow_firewall.tf file in the harness-projects module repository. For more information, see Configuring internet access and firewall rules.

To deny resources the ability to use external IP addresses, the Define allowed external IPs for VM instances (compute.vmExternalIpAccess) organization policy is set to deny all.

Perimeter controls

As shown in the architecture diagram, you place the resources for the data warehouse into separate perimeters. To enable services in different perimeters to share data, you create perimeter bridges.

Perimeter bridges let protected services make requests for resources outside of their perimeter. These bridges make the following connections:

  • They connect the Data ingestion project to the Data project so that data can be ingested into BigQuery.
  • They connect the Data project to the Data governance project so that Cloud DLP can scan BigQuery for unprotected confidential data.
  • They connect the Data ingestion project to the Data governance project for access to logging, monitoring, and encryption keys.

In addition to perimeter bridges, you use egress rules to let resources that are protected by perimeters to access resources that are outside the perimeter. In this solution, you configure egress rules to obtain the external Dataflow Flex Template jobs that are located in Cloud Storage in an external project. For more information, see Access a Google Cloud resource outside the perimeter.

Access policy

To help ensure that only specific identities (user or service) can access resources and data, you enable IAM groups and roles.

To help ensure that only specific sources can access your projects, you enable an access policy for your Google organization. We recommend that you create an access policy that specifies the allowed IP address range for requests originating from your on-premises environment and only allows requests from specific users or service accounts. For more information, see Access level attributes.

Client-side encryption

Before you move your sensitive data into Google Cloud, encrypt your data locally to help protect it at rest and in transit. You can use the Tink encryption library, or you can use other encryption libraries. The Tink encryption library is compatible with BigQuery AEAD encryption, which the blueprint uses to decrypt column-level encrypted data after the data is imported.

The Tink encryption library uses DEKs that you can generate locally or from Cloud HSM. To wrap or protect the DEK, you can use a KEK that is generated in Cloud HSM. The KEK is a symmetric CMEK encryption keyset that is stored securely in Cloud HSM and managed using IAM roles and permissions.

During ingestion, both the wrapped DEK and the data are stored in BigQuery. BigQuery includes two tables: one for the data and the other for the wrapped DEK. When analysts need to view confidential data, BigQuery can use AEAD decryption to unwrap the DEK with the KEK and decrypt the protected column.

Also, client-side encryption using Tink further protects your data by encrypting sensitive data columns in BigQuery. The blueprint uses the following Cloud HSM encryption keys:

  • A CMEK key for the ingestion process that's also used by Pub/Sub, Dataflow pipeline for streaming, Cloud Storage batch upload, and Cloud Functions artifacts for subsequent batch uploads.
  • The cryptographic key wrapped by Cloud HSM for the data encrypted on your network using Tink.
  • CMEK key for the BigQuery warehouse in the Data project.

You specify the CMEK location, which determines the geographical location that the key is stored and is made available for access. You must ensure that your CMEK is in the same location as your resources. By default, the CMEK is rotated every 30 days.

If your organization's compliance obligations require that you manage your own keys externally from Google Cloud, you can enable Cloud External Key Manager. If you use external keys, you're responsible for key management activities, including key rotation.

Service accounts and access controls

Service accounts are identities that Google Cloud can use to run API requests on your behalf. Service accounts ensure that user identities do not have direct access to services. To permit separation of duties, you create service accounts with different roles for specific purposes. These service accounts are defined in the data-ingestion-sa module and the data-governance-sa module.

The service accounts are as follows:

  • Cloud Storage service account runs the automated batch data upload process to the ingestion storage bucket.
  • Pub/Sub service account enables streaming of data to Pub/Sub service.
  • Dataflow controller service account is used by the Dataflow pipeline to transform and write data from Pub/Sub to BigQuery.
  • Cloud Functions service account writes subsequent batch data uploaded from Cloud Storage to BigQuery.
  • Storage Upload service account allows the ETL pipeline to create objects.
  • Pub/Sub Write service Account lets the ETL pipeline write data to Pub/Sub.

The following table lists the roles that are assigned to each service account:

Name Roles Scope of Assignment
Dataflow controller service account Data ingestion project
Data project
Data governance
Cloud Functions service account Data ingestion project
  • BigQuery Data Editor (roles/bigquery.dataEditor)
  • BigQuery Metadata Viewer (roles/bigquery.metadataViewer)
 Data project
Storage Upload service account Data ingestion Project
Pub/Sub Write service account Data ingestion Project

Security controls for data storage

You configure the following security controls to help protect data in the BigQuery warehouse:

  • Column-level access controls
  • Service accounts with limited roles
  • Dynamic data masking of sensitive fields
  • Organization policies
  • Cloud DLP automatic scanning and data profiler
  • VPC Service Controls perimeters between the Data ingestion project and the Data project, with appropriate perimeter bridges
  • Encryption and key management, as follows:
    • Encryption at rest with CMEK keys that are stored in Cloud HSM
    • Column-level encryption using Tink and BigQuery AEAD Encryption

Dynamic data masking

To help with sharing and applying data access policies at scale, you can configure dynamic data masking. Dynamic data masking lets existing queries automatically mask column data using the following criteria:

  • The masking rules that are applied to the column at query runtime.
  • The roles that are assigned to the user who is running the query. To access unmasked column data, the data analyst must have the Fine-Grained Reader role.

To define access for columns in BigQuery, you create policy tags. For example, the taxonomy created in the standalone example creates the 1_Sensitive policy tag for columns that include data that cannot be made public, such as the credit limit. The default data masking rule is applied to these columns to hide the value of the column.

Anything that isn't tagged is available to all users who have access to the data warehouse. These access controls ensure that, even after the data is written to BigQuery, the data in sensitive fields still cannot be read until access is explicitly granted to the user.

Column-level encryption and decryption

Column-level encryption lets you encrypt data in BigQuery at a more granular level. Instead of encrypting an entire table, you select the columns that contain sensitive data within BigQuery, and only those columns are encrypted. BigQuery uses AEAD encryption and decryption functions that create the keysets that contain the keys for encryption and decryption. These keys are then used to encrypt and decrypt individual values in a table, and rotate keys within a keyset. Column-level encryption provides dual-access control on encrypted data in BigQuery, because the user must have permissions to both the table and the encryption key to read data in cleartext.

Data profiler for BigQuery with Cloud DLP

Data profiler lets you identify the locations of sensitive and high risk data in BigQuery tables. Data profiler automatically scans and analyzes all BigQuery tables and columns across the entire organization, including all folders and projects. Data profiler then outputs metrics such as the predicted infoTypes, the assessed data risk and sensitivity levels, and metadata about your tables. Using these insights, you can make informed decisions about how you protect, share, and use your data.

Service accounts with limited roles

You must limit access to the Data project so that only authorized users can view the sensitive data fields. To do so, you create a service account with the roles/iam.serviceAccountUser role that authorized users must impersonate. Service account impersonation helps users to use service accounts without downloading the service account keys, which improves the overall security of your project. Impersonation creates a short-term token that authorized users who have the roles/iam.serviceAccountTokenCreator role are allowed to download.

Organization policies

This blueprint includes the organization policy constraints that the enterprise foundations blueprint uses and adds additional constraints. For more information about the constraints that the enterprise foundations blueprint uses, see Organization policy constraints.

The following table describes the additional organizational policy constraints that are defined in the organization-policies module.

Policy Constraint Name Recommended Value
Restrict resource deployments to specific physical locations gcp.resourceLocations One of the following:
in:us-locations
in:eu-locations
in:asia-locations
Require CMEK protection gcp.restrictNonCmekServices bigquery.googleapis.com
Disable service account creation iam.disableServiceAccountCreation true
Disable Service account key creation disableServiceAccountKeyCreation true
Enable OS Login for VMs created in the project compute.requireOsLogin true
Disable automatic role grants to default service account automaticIamGrantsForDefaultServiceAccounts true
Allowed ingress settings (Cloud Functions) cloudfunctions.allowedIngressSettings ALLOW_INTERNAL_AND_GCLB
Restrict new forwarding rules to be internal only, based on IP address compute.restrictProtocolForwardingCreationForTypes INTERNAL
Disable serial port output logging to Cloud Logging compute.disableSerialPortLogging true
Define the set of shared VPC subnetworks that Compute Engine resources can use compute.restrictSharedVpcSubnetworks projects/PROJECT_ID/regions/REGION/subnetworks/SUBNETWORK-NAME

Replace SUBNETWORK-NAME with the resource ID of the private subnet that you want the blueprint to use.

Operational controls

You can enable logging and Security Command Center Premium tier features such as Security Health Analytics and Event Threat Detection. These controls help you to do the following:

  • Monitor who is accessing your data.
  • Ensure that proper auditing is put in place.
  • Generate findings for misconfigured cloud resources.
  • Support the ability of your incident management and operations teams to respond to issues that might occur.

Access Transparency

Access Transparency provides you with real-time notification in the event Google support personnel require access to your data. Access Transparency logs are generated whenever a human accesses content, and only Google personnel with valid business justifications (for example, a support case) can obtain access. We recommend that you enable Access Transparency.

Logging

To help you to meet auditing requirements and get insight into your projects, you configure the Google Cloud Observability with data logs for services you want to track. The harness-logging module configures the following best practices:

For all services within the projects, your logs must include information about data reads and writes, and information about what administrators read. For additional logging best practices, see Detective controls in the enterprise foundations blueprint.

Alerts and monitoring

After you deploy the blueprint, you can set up alerts to notify your security operations center (SOC) that a security incident might be occurring. For example, you can use alerts to let your security analyst know when an IAM permission has changed. For more information about configuring Security Command Center alerts, see Setting up finding notifications. For additional alerts that aren't published by Security Command Center, you can set up alerts with Cloud Monitoring.

Additional security considerations

In addition to the security controls described in this solution, you should review and manage the security and risk in key areas that overlap and interact with your use of this solution. These security considerations include the following:

  • The security of the code that you use to configure, deploy, and run Dataflow jobs and Cloud Functions.
  • The data classification taxonomy that you use with this solution.
  • Generation and management of encryption keys.
  • The content, quality, and security of the datasets that you store and analyze in the data warehouse.
  • The overall environment in which you deploy the solution, including the following:
    • The design, segmentation, and security of networks that you connect to this solution.
    • The security and governance of your organization's IAM controls.
    • The authentication and authorization settings for the actors to whom you grant access to the infrastructure that's part of this solution, and who have access to the data that's stored and managed in that infrastructure.

Bringing it all together

To implement the architecture described in this document, do the following:

  1. Determine whether you will deploy the blueprint with the enterprise foundations blueprint or on its own. If you choose not to deploy the enterprise foundations blueprint, ensure that your environment has a similar security baseline in place.
  2. Set up a Dedicated Interconnect connection with your network.
  3. Review the README for the blueprint and ensure that you meet all the prerequisites.
  4. Verify that your user identity has the iam.serviceAccountUser and iam.serviceAccountTokenCreator roles for your organization's development folder, as described in Organization structure. If you do not have a folder that you use for testing, create a folder and configure access.
  5. Record your billing account ID, organization's display name, folder ID for your test or demo folder, and the email addresses for the following user groups:
    • Data analysts
    • Encrypted data viewer
    • Plaintext reader
    • Data engineers
    • Network administrators
    • Security administrators
    • Security analysts
  6. Create the Data, Data governance, Data ingestion, and Flex template projects. For a list of APIs that you must enable, see the README.
  7. Create the service account for Terraform and assign the appropriate roles for all projects.
  8. Set up the Access Control Policy.

  9. In your testing environment, deploy the solution:

    1. Clone and run the Terraform scripts to set up an environment in Google Cloud.
    2. Install the Tink encryption library on your network.
    3. Set up Application Default Credentials so that you can run the Tink library on your network.
    4. Create encryption keys with Cloud KMS.
    5. Generate encrypted keysets with Tink.

    6. Encrypt data with Tink using one of the following methods:

    7. Upload encrypted data to BigQuery using streaming or batch uploads.

  10. Verify that authorized users can read unencrypted data from BigQuery using the BigQuery AEAD decrypt function. For example, run the following create decryption function:

    CREATE OR REPLACE FUNCTION `{project_id}.{bigquery_dataset}.decrypt`(encodedText STRING) RETURNS STRING AS
(
AEAD.DECRYPT_STRING(
KEYS.KEYSET_CHAIN('gcp-kms://projects/myProject/locations/us/keyRings/myKeyRing/cryptoKeys/myKeyName', b'\012\044\000\321\054\306\036\026…..'),
FROM_BASE64(encodedText), "")
);

    Run the create view query:

    CREATE OR REPLACE VIEW `{project_id}.{bigquery_dataset}.decryption_view` AS

SELECT
 Card_Type_Code,
 Issuing_Bank,
 Card_Number,
 `bigquery_dataset.decrypt`(Card_Number) AS Card_Number_Decrypted
FROM `project_id.dataset.table_name`

    Run the select query from view:

    SELECT
  Card_Type_Code,
  Issuing_Bank,
  Card_Number,
  Card_Number_Decrypted
FROM
`{project_id}.{bigquery_dataset}.decrypted_view`

    For additional queries and use cases, see Column-level encryption with Cloud KMS.

  11. Use Security Command Center to scan the newly created projects against your compliance requirements.

  12. Deploy the blueprint into your production environment.

What's next