professional-cloud-data-engineer
Prepare and test your skills
Prepare and test your skills
Dynamic data masking in BigQuery is a security mechanism that automatically obscures sensitive column values in query results based on the identity and permissions of the requesting user. The underlying table data remains unaltered in storage while BigQuery evaluates permissions at query runtime to determine whether to return raw, partially masked, or completely masked values. Dataplex policy tags, managed through Data Catalog, assign business classifications to table columns and establish fine-grained access control boundaries across datasets. This real-time obfuscation ensures that analytical workloads follow the principle of least privilege without creating duplicate, sanitized datasets.
Setting up dynamic data masking requires an ordered configuration workflow across classification and security services:
A four-step process flow showing dynamic data masking setup: Sensitive Data Protection discovers PII, Dataplex creates a policy tag taxonomy, Data Catalog attaches tags to BigQuery columns, and IAM assigns masking rules. At query runtime BigQuery returns plain text to Fine-Grained Readers and masked values to standard viewers, while stored data stays unaltered.
BigQuery enforces policy tag rules during query execution by comparing the user's IAM privileges against the column classification. Users granted privileged access, such as data owners or editors with fine-grained read permissions, receive plain text data, while standard viewers receive masked results such as formatted asterisks or redacted strings. This separation between data storage and presentation allows organizations to centralize security policy management across analytical teams.
Exam tip: Dynamic data masking obscures data dynamically at query execution time based on IAM permissions; it does not overwrite or permanently modify the original data stored in BigQuery.
Row- and column-level security in BigQuery limits data visibility to specific rows or columns based on user identity, group membership, or access permissions. These fine-grained access controls work directly with Identity and Access Management (IAM) to prevent unauthorized access across datasets, tables, and views without requiring separate physical tables for different user groups. By restricting data access at the storage layer, organizations can safely share unified analytical datasets with business intelligence tools and downstream consumers.
| Control Mechanism | Access Scope | Enforcement Mechanism | Primary Use Case |
|---|---|---|---|
| Authorized views | Table subsets (rows/columns) | SQL query execution without granting direct base table access | Sharing aggregated or pre-filtered views with external or restricted users |
| Row-level security | Specific table rows | Filter predicates based on user identity (such as session user or domain) | Restricting regional or departmental users to their relevant records |
| Column-level security | Specific table columns | Policy tags linked to the Fine-Grained Reader IAM role | Protecting sensitive fields like social security numbers across all queries |
In addition to access controls, data engineers can protect BigQuery data using column-level encryption and customer-managed encryption keys (CMEK) configured in Cloud Key Management Service (Cloud KMS). Column-level encryption establishes a dual-access security boundary where a user requires both BigQuery table read permissions and Cloud KMS cryptographic key access to decrypt plaintext values. All BigQuery data is encrypted at rest and in transit by default, but CMEK provides administrative ownership over key lifecycles and cryptographic boundaries.
Connecting visualization tools such as Looker or Looker Studio requires strict identity governance to avoid privilege escalation. Administrators should configure these tools to connect via dedicated service accounts with minimal IAM privileges and enforce service account impersonation rather than downloading long-lived service account keys. Queries initiated through business intelligence platforms inherit underlying row- and column-level security policies, ensuring consistent access restrictions regardless of the client interface.
Exam tip: Service account impersonation eliminates the need for long-lived service account keys while preserving all row-level and column-level security rules for connected visualization tools.
A de-identification pipeline uses Sensitive Data Protection (formerly Cloud DLP) to detect, mask, or tokenize sensitive data before storing it in analytical systems. Building a streaming de-identification pipeline with Cloud Dataflow allows organizations to transform incoming records from Cloud Storage and securely ingest the sanitized output into BigQuery. Processing data in transit minimizes exposure risks for personally identifiable information (PII) while preserving data structure and analytical utility.
The streaming ingestion flow begins when uncharacterized files land in Cloud Storage, triggering a Dataflow pipeline that executes inspection templates configured in Sensitive Data Protection. The service applies built-in classifiers to locate sensitive data elements and runs targeted de-identification transformations before loading the cleansed records into BigQuery. De-identification templates decouple security transformation rules from pipeline code, enabling centralized policy reuse across different streaming pipelines and storage buckets.
| De-identification Method | Reversibility | Structural Behavior |
|---|---|---|
| Format-Preserving Encryption (FPE) | Reversible | Retains the exact original data length and character set for legacy application compatibility |
| Deterministic Encryption (DE) | Reversible | Produces identical ciphertext for identical plaintext with strong cryptographic authentication |
| Cryptographic hashing | Non-reversible | Generates fixed-length unique hash values to verify data integrity without exposing raw text |
| Masking and redaction | Non-reversible | Replaces characters with static mask characters (such as #) or removes sensitive attributes entirely |
| Bucketing and date-shifting | Non-reversible | Groups individual numerical values into ranges or offsets timestamps to reduce record uniqueness |
Cryptographic de-identification techniques depend on a token encryption key that is protected as a wrapped key within Cloud Key Management Service (Cloud KMS). Rotating this token encryption key invalidates existing tokens, which requires a complete re-tokenization of the stored dataset to restore cryptographic integrity. Data pipelines must also be protected at the network boundary using VPC Service Controls to establish perimeters around Google Cloud managed services and prevent unauthorized data exfiltration.
Ingress traffic from external networks into Google Cloud should traverse dedicated hybrid connections using Cloud VPN or Cloud Interconnect. For pre-ingestion security on client machines, developers can use the Tink cryptography library to encrypt sensitive fields with data encryption keys (DEKs) before records leave the source environment. After records enter BigQuery, the data profiler continuously evaluates tables to detect high-risk assets and inform dynamic data masking or column-level access controls.
Exam tip: Rotating a token encryption key breaks the integrity of existing tokens and requires a full re-tokenization of the entire dataset to allow future de-tokenization.
Dynamic data masking in BigQuery automatically obscures sensitive column values in query results based on the user's Identity and Access Management (IAM) permissions at runtime, while the underlying stored data remains completely unaltered. It is a security mechanism that enforces the principle of least privilege without creating duplicate datasets.
Format-Preserving Encryption (FPE) and Deterministic Encryption (DE) are reversible de-identification methods that produce tokens that can be decrypted back to the original data. Cryptographic hashing, masking and redaction, and bucketing and date-shifting are non-reversible methods that permanently transform the data, preventing recovery of the original values.
Rotating a token encryption key managed in Cloud Key Management Service (Cloud KMS) invalidates the integrity of all existing tokens created with the old key. This break in integrity requires a complete re-tokenization of the entire affected dataset to allow for future de-tokenization operations.
Your organization manages a BigQuery dataset containing a customer transactions table with sensitive Personally Identifiable Information (PII), including columns for tax_id, email_address, and credit_card_number. You are preparing this data for enterprise-wide visualization and reporting.
You have the following security and access requirements:
Which solution should you implement?
Create a Cloud KMS customer-managed encryption key (CMEK) for each sensitive column. Grant Compliance Auditors the Cloud KMS CryptoKey Decrypter role and grant BI Analysts only BigQuery Job User permissions.
Create authorized views in BigQuery that use SQL hashing functions like SHA256() on sensitive columns. Grant BI Analysts access to the authorized views and grant Compliance Auditors direct access to the underlying base tables.
Configure an ongoing Cloud Data Loss Prevention (Cloud DLP) batch job to inspect the table and overwrite sensitive columns with pseudonymized tokens stored in an external Cloud KMS lookup table. Grant Auditors IAM access to the KMS decryption key.
Create a taxonomy with policy tags in Dataplex and associate dynamic data masking rules with the tags. Attach the policy tags to the sensitive columns in BigQuery. Grant Compliance Auditors the Fine-Grained Reader role on the policy tags, and grant BI Analysts the Masked Reader role on the policy tags.
StudyCerts
StudyCerts