How should you de-identify email fields before BigQuery joins?

Your company's data platform ingests CSV file dumps of booking and user profile data from upstream sources into Cloud Storage. The data analyst team wants to join these datasets on the email field available in both the datasets to perform analysis. However, personally identifiable information (PII) should not be accessible to the analysts. You need to de-identify the email field in both the datasets before loading them into BigQuery for analysts. What should you do?

1. Create a pipeline to de-identify the email field by using recordTransformations in Cloud Data Loss Prevention (Cloud DLP) with masking as the de-identification transformations type.
1. Create a pipeline to de-identify the email field by using recordTransformations in Cloud DLP with format-preserving encryption with FFX as the de-identification transformation type. Source Reference Answer
1. Load the CSV files from Cloud Storage into a BigQuery table, and enable dynamic data masking.
1. Load the CSV files from Cloud Storage into a BigQuery table, and enable dynamic data masking.

Community Votes

100%

100% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The exam tests the difference between Cloud DLP de-identification transformations and BigQuery dynamic masking; the trap is picking a masking method that loses join consistency or deferring de-identification until after data is loaded.

To join datasets on de-identified email fields in BigQuery, use Cloud DLP format-preserving encryption (FFX) for deterministic, format-preserving pseudonymization. The community consensus favors option B, noting that masking and dynamic data masking either break joinability or leave raw PII in BigQuery.

A common mistake is choosing Cloud DLP masking or BigQuery dynamic data masking. Masking can produce non-unique values that prevent accurate joins, while dynamic masking stores raw PII in BigQuery and does not de-identify before loading as required.

Community Discussion (13 comments)

lipa31 👍 15 Selected: B

Format-preserving encryption (FPE) with FFX in Cloud DLP is a strong choice for de-identifying PII like email addresses. FPE maintains the format of the data and ensures that the same input results in the same encrypted output consistently. This means the email fields in both datasets can be encrypted to the same value, allowing for accurate joins in BigQuery while keeping the actual email addresses hidden.

Smakyel79 👍 5

As it states "You need to de-identify the email field in both the datasets before loading them into BigQuery for analysts" data masking should not be an option as the data would stored unmasked in BigQuery?

Anudeep58 👍 4 Selected: B

Option A: Masking: Simple masking might not preserve the uniqueness and joinability of the email field, making it difficult to perform accurate joins between datasets. Option C and D: Dynamic Data Masking: These options involve masking the email field dynamically within BigQuery, which does not address the requirement to de-identify data before loading into BigQuery. Additionally, dynamic masking does not prevent access to the actual email data before it is loaded into BigQuery, potentially exposing PII during the data ingestion process.

chrissamharris 👍 2 Selected: B

format-preserving encryption with FFX is required as the analysts want to perform JOINs

JyoGCP 👍 3 Selected: B

Option B https://cloud.google.com/sensitive-data-protection/docs/pseudonymization

ML6 👍 4 Selected: B

A) masking = replace with a surrogate character like # or * = output not unique, so cannot apply joins C and D) question specifies to de-identify BEFORE loading into BQ, whereas these options perform dynamic masking IN BigQuery. Therefore, only valid option is B.

Matt_108 👍 1 Selected: C

Option C. The need is to just mask the data to Analyst, without modifying the underlying data. Moreover, it's stored on 2 separate tables and the analysts need to be able to perform joins based on the masked data. Dynamic masking is the right module and the right masking rule is email mask (https://cloud.google.com/bigquery/docs/column-data-masking-intro#masking_options) which guarantees the join capabilities join

task_7 👍 5 Selected: B

A wouldn't preserve the email format C&D maskedReader roles still grant access to the underlying values. the only option is B

Sofiia98 👍 1 Selected: C

I will go for C, because there is a separate type of masking for emails, so whe to use the dafault? https://cloud.google.com/bigquery/docs/column-data-masking-intro#masking_options

GCP001 👍 1 Selected: C

data masking with BQ is correct with email masking rule. Ref - https://cloud.google.com/bigquery/docs/column-data-masking-intro

Jordan18 👍 2

why not B?

raaad 👍 2 Selected: C

The reason option C works well is that dynamic data masking in BigQuery allows the underlying data to remain unaltered (thus preserving the ability to join on this field), while also preventing analysts from viewing the actual PII. - The analysts can query and join the data as needed for their analysis, but when they access the data, the email field will be masked according to the policy tag, and they will only see the masked version.

scaenruy 👍 1 Selected: D

D. 1. Load the CSV files from Cloud Storage into a BigQuery table, and enable dynamic data masking. 2. Create a policy tag with the default masking value as the data masking rule. 3. Assign the policy to the email field in both tables. 4. Assign the Identity and Access Management bigquerydatapolicy.maskedReader role for the BigQuery tables to the analysts

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Option B uses Cloud DLP format-preserving encryption (FPE) with FFX, which encrypts the email field while preserving its format and ensuring the same input always produces the same encrypted output. This deterministic property allows the two datasets to be joined on the encrypted email values in BigQuery. Because the data is de-identified before loading, analysts never see raw PII.

Why the Other Options Are Wrong

Option A uses masking transformation, which typically replaces email characters with placeholder symbols like '#' or '*'. This does not guarantee uniqueness, so joining on the masked field would fail or produce incorrect results. Options C and D rely on BigQuery dynamic data masking, which masks data only at query time for users with restricted roles, leaving the raw email stored in BigQuery and violating the requirement to de-identify before loading.

Community Comment Notes

Several comments correctly highlight that masking "might not preserve the uniqueness and joinability" (comment 4) and that dynamic masking "does not address the requirement to de-identify data before loading" (comment 4). Comment 5 poignantly explains that C/D perform masking inside BigQuery, not before loading. Comment 6 links to Google's pseudonymization documentation, supporting FPE/FFX as the right approach. Even the minority comment 8 favoring dynamic masking concedes it leaves underlying data unaltered, which contradicts the stated "before loading" requirement.

Official Reference

Exam Strategy

When a question mentions de-identifying before loading and needing to join later, look for deterministic, format-preserving methods like Cloud DLP FPE with FFX. Eliminate any option that only masks in BigQuery or that does not preserve a one-to-one join key.

Related Analysis

← Back to PDE Study Guide