GCP Professional Data Engineer Practice Question 9: PII Column Masking
Google Cloud Professional Data Engineer · Test 1 · question 9 of 10
A customers table holds email and phone columns. Analysts must no longer see the raw values, but their existing scheduled queries use SELECT * and count distinct users by grouping on a hash of email, and those queries must keep running unchanged. The fraud team must still read the raw values. Compliance forbids creating a second copy of the table, and the team does not want to maintain a parallel set of views.
Which approach satisfies all of these constraints?
Show answer and explanation
Answer:
BigQuery dynamic data masking builds on column-level security: a taxonomy supplies the policy tags, a data policy binds a masking rule and a set of principals to a tag, and the masking is applied at query time. The documented advantage over plain column-level access control is that "you don't need to modify existing queries by excluding the columns that the user cannot access"; SELECT * keeps working and simply returns masked values. Choosing the SHA-256 hash rule preserves exactly the property the analysts depend on: equal inputs hash to equal outputs, so counting distinct users by grouping on the value still gives the right answer. Principals with Fine-Grained Reader on the policy tag bypass masking, which is how the fraud team keeps raw access, and nothing is copied.
Option A breaks the queries. A view without the columns makes SELECT * return a different shape and makes the hash-based distinct count impossible, and it is precisely the parallel view layer the team wants to avoid.
Option C also breaks the queries. Under column-level access control alone, a principal without access on a tagged column gets a permission error on any query that selects it, including SELECT *, which is why masking exists as a separate capability on top of it.
Option D creates the duplicate that compliance forbids, doubles storage, and introduces a lag between the source table and the de-identified copy.
Reference: Introduction to data masking