GCP Professional Data Engineer Practice Question

A multinational bank keeps all credit-card transactions in a single BigQuery table called finance_prod.payment_txn. The table contains the columns pan (primary account number), txn_amount, merchant_id, and region_code.

Members of the group [email protected] must be able to explore the data freely but must never see raw pan values.
Members of the group [email protected] need full access to pan, yet they should only view rows where region_code equals the geographic region (EU or US) assigned to them.
The security team requires that no data be copied or duplicated into additional tables or projects.
Which approach best meets these requirements while honoring the principle of least privilege?

Move payment_txn into a separate dataset; create a duplicate dataset without the pan column for data scientists and use dataset-level IAM to control access; no row-level filters are applied.
Enable Customer-Managed Encryption Keys (CMEK) on payment_txn, grant the Viewer role on the dataset to [email protected], and the BigQuery Data Viewer role to [email protected]; rely on CMEK to protect sensitive data.
Tag the pan column with a Data Catalog policy tag and grant only [email protected] the Fine-Grained Reader role on that tag; add a row-level access policy on payment_txn filtering by region_code for each audit region; publish an authorized view that omits pan and grant [email protected] access to the view.
Create two new tables, one per region, each containing all columns; mask pan with SHA256() in views for [email protected], and grant auditors direct access to both regional tables.

Report Issue

Answer Description

The requirements call for two independent layers of fine-grained control applied directly on the source table. Column-level security can hide the sensitive pan column unless a user or group has the Fine-Grained Reader permission on the Data Catalog policy tag that protects that column; granting this role only to [email protected] satisfies the need for auditors to see the field while blocking data scientists. A row-level access policy scoped to region_code limits auditors to the rows for their region. Because data scientists must query the rest of the columns without seeing pan, the most efficient solution that avoids data duplication is an authorized view which excludes pan; the view can be shared with [email protected], allowing arbitrary analysis on the remaining columns. Dataset-level IAM is kept minimal, and no extra tables are created.

Alternative designs that copy data into masked or regional tables violate the "no duplication" constraint, while relying only on table-level ACLs or CMEK does not provide column or row-level filtering.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.