Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Per-Tenant Datasets with Masked Payment Columns
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
A financial SaaS enterprise is designing a multi-tenant data access architecture in BigQuery to serve both internal analytics teams and external client organizations. The data architecture must satisfy the following technical and compliance requirements:
- Tenant Scaling & Regionalization: Support scaling from 500 to over 5,000 distinct tenant organizations located across multiple global Google Cloud regions.
- Performance Optimization: Enable each tenant to apply independent table partitioning and clustering schemes tailored to their specific querying workloads.
- Column Governance: Dynamically obscure sensitive payment card data for general data analysts while providing plaintext access to authorized compliance auditors with the Fine-Grained Reader role.
Which architecture should the data engineering team implement to satisfy these requirements?
Explanations
Create a centralized fact table in a primary dataset and expose authorized views filtered by tenant ID to each tenant. Enforce column-level security by assigning basic IAM Viewer permissions to compliance auditors and IAM Billing User permissions to standard analysts.
Create a centralized fact table in a primary dataset and expose authorized views filtered by tenant ID to each tenant. Enforce column-level security by assigning basic IAM Viewer permissions to compliance auditors and IAM Billing User permissions to standard analysts.
What the Authorized View Architecture Is
This approach uses a single base fact table and creates individual authorized views that filter rows using a WHERE tenant_id = '...' clause. It attempts to manage access and security purely through dataset authorization and basic IAM roles.
Why It Doesn't Meet the Requirements
- Tenant Scaling Limits: BigQuery enforces a hard limit of 2,500 authorized resources per dataset. Attempting to scale to 5,000+ tenants using authorized views against a base dataset will exceed this quota.
- Regionalization & Custom Optimization: Authorized views cannot cross regions and are strictly tied to the region of the base table. Furthermore, authorized views cannot have their own distinct clustering and partitioning schemes; they are forced to inherit the base table's physical structure.
- Column Governance: Basic IAM roles (such as Viewer or Billing User) operate at the project, dataset, or table level and cannot provide dynamic column masking or fine-grained column access control.
Key Limitations
- 2,500 authorized resources limit: Fails when scaling beyond 2,500 tenant views on a single dataset.
- Rigid partitioning/clustering: Tenants cannot customize indexing or data organization.
- Lack of column-level granularity: Basic IAM roles cannot selectively obscure individual fields.
This Answer Would Be Correct If
The organization had fewer than 2,500 tenants located entirely in a single geographic region, all querying using identical access patterns without requiring column-level dynamic data masking.
Additional Context
Authorized views are ideal for small-to-medium multi-tenant architectures or internal departmental sharing within the same region, but they do not scale to massive multi-region SaaS deployments.
Configure a data ingestion pipeline using Dataflow to write tenant-specific records into dedicated per-tenant BigQuery datasets in each tenant's local region, allowing tailored clustering and partitioning schemes. Apply Dataplex policy tags with dynamic data masking rules on sensitive columns, granting the Fine-Grained Reader role to compliance auditors.
Configure a data ingestion pipeline using Dataflow to write tenant-specific records into dedicated per-tenant BigQuery datasets in each tenant's local region, allowing tailored clustering and partitioning schemes. Apply Dataplex policy tags with dynamic data masking rules on sensitive columns, granting the Fine-Grained Reader role to compliance auditors.
What the Dedicated Dataset Architecture with Dynamic Data Masking Is
A dedicated dataset per tenant design segregates tenant data into discrete BigQuery datasets (or subset tables) written by an upstream orchestration or ETL/ELT service like Cloud Dataflow. This is combined with Dataplex policy tags and dynamic data masking to enforce granular, role-based column access control natively within BigQuery.
How It Meets the Requirements
- Tenant Scaling & Regionalization: A dataset-per-tenant pattern bypasses the hard limit of 2,500 authorized resources per dataset associated with authorized views. It also allows tenant datasets and tables to reside in different Google Cloud regions, accommodating regional residency and latency requirements for globally distributed clients.
- Performance Optimization: Because each tenant receives dedicated physical tables/datasets rather than shared logical views over a centralized monolithic table, each tenant can configure custom partitioning and clustering strategies optimized for their individual query patterns.
- Column Governance: Applying policy tags with dynamic data masking masks sensitive fields (such as credit card numbers or PII) at runtime for standard users while revealing plaintext values to auditors who hold the Fine-Grained Reader (
roles/datacatalog.categoryFineGrainedReader) role.
Key Advantages
- High horizontal scalability: Seamlessly scales past 5,000 tenants without colliding with authorized resource or view quotas.
- Granular lifecycle and regional management: Allows data lifecycle rules, geographic placement, and backup schedules to be customized per tenant.
- Centralized policy governance: Policy tag taxonomies govern column masking uniformly across all tenant tables.
- Predictable performance: Tenant workloads are decoupled, avoiding shared scan overheads and unoptimized cluster keys.
Why This Is Optimal
Using Dataflow to write dedicated per-tenant datasets eliminates the 2,500 resource ceiling and single-region constraint inherent to authorized views, while native dynamic data masking provides robust column-level security without maintaining duplicate, obfuscated views.
Consolidate all tenant records into a single multi-region fact table and create BigQuery row access policies for each tenant. Apply Customer-Managed Encryption Keys (CMEK) separately to sensitive table columns to mask payment data dynamically.
Consolidate all tenant records into a single multi-region fact table and create BigQuery row access policies for each tenant. Apply Customer-Managed Encryption Keys (CMEK) separately to sensitive table columns to mask payment data dynamically.
What Row Access Policies with CMEK Is
This architecture relies on BigQuery Row-Level Security (RLS) via CREATE ROW ACCESS POLICY statements on a single consolidated table to filter row data per tenant, while attempting to use Cloud KMS Customer-Managed Encryption Keys (CMEK) to enforce column-level data masking.
Why It Doesn't Meet the Requirements
- Performance Optimization: A single consolidated fact table forces all tenants to share one partitioning and clustering strategy (e.g., partitioning by ingestion date and clustering by tenant ID). Tenants cannot tailor their own partitioning schemes.
- Column Governance with CMEK: CMEK operates at the storage level (encrypting entire datasets, tables, or disk buckets at rest using Cloud KMS keys) rather than masking individual column values dynamically at query runtime based on IAM roles.
- Regional Placement: A single table cannot be placed in multiple distinct regional locations simultaneously to satisfy disparate data residency mandates across 5,000 global tenants.
Key Limitations
- No per-tenant physical schema tailoring: Clustering and partitioning are fixed globally across all tenants.
- Misuse of CMEK: CMEK does not replace column-level policy tags or dynamic data masking.
- Row filter pruning limitations: Row-level security filter conditions do not participate in query partition pruning.
This Answer Would Be Correct If
The goal was strictly row isolation within a single unified dataset where all tenants shared identical performance patterns and column masking was handled by external client-side AEAD libraries rather than native BigQuery masking.
Additional Context
Dynamic data masking in BigQuery is implemented through Data Catalog / Dataplex policy tags, not through CMEK key rotation or assignment.
Deploy authorized materialized views in each tenant's Google Cloud project referencing a central source table. Use Cloud Storage Bucket Lock retention policies on the BigQuery export buckets to prevent unmasked column exposure.
Deploy authorized materialized views in each tenant's Google Cloud project referencing a central source table. Use Cloud Storage Bucket Lock retention policies on the BigQuery export buckets to prevent unmasked column exposure.
What Authorized Materialized Views with Bucket Lock Is
This pattern provisions authorized materialized views across tenant projects to cache analytical query results, while attempting to manage column privacy and data security using Cloud Storage Bucket Lock retention controls on export buckets.
Why It Doesn't Meet the Requirements
- Cross-Region Incompatibility: Authorized views and materialized views must reside in the exact same Google Cloud region as the base source table; they cannot bridge cross-region data access for globally dispersed tenants.
- Authorized Resource Limits: It remains subject to the dataset limitation of 2,500 authorized resources.
- Irrelevant Security Controls: Cloud Storage Bucket Lock enforces WORM (Write Once, Read Many) immutability and compliance retention periods on Cloud Storage objects. It has no mechanism to enforce column-level dynamic data masking or control query execution in BigQuery.
Key Limitations
- Region locking: Views cannot be instantiated in different regions from the base dataset.
- Ineffective column security: Bucket Lock does not provide column masking or row-level access control.
- High maintenance: Managing 5,000+ individual materialized views introduces massive management overhead and exceeds dataset authorization quotas.
This Answer Would Be Correct If
You needed immutable audit archive storage in Cloud Storage to satisfy SEC 17a-4 or FINRA compliance rules where data cannot be overwritten or deleted for a set duration.
Additional Context
Materialized views are precomputed caches designed for computational query optimization, not primary multi-tenant isolation boundaries across thousands of tenants.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
BigQuery Materialized View Partition Alignment
Defining architecture to support data access patterns
AdvancedProfessional Data Engineer
Cloud Spanner Table Interleaving
Defining architecture to support data access patterns
AdvancedProfessional Data Engineer
Bigtable Time-Series Row Key Design
Choosing managed services (e.g., BigQuery, BigLake, AlloyDB, Bigtable, Spanner, Cloud SQL, Cloud Storage, Firestore, Memorystore)
AdvancedProfessional Data Engineer
Dataplex Raw and Curated Zones
Data staging, cataloging, profiling, and discovery (data governance)
AdvancedProfessional Data Engineer
BigQuery Storage Write API Exactly-Once Dead-Letter
Identifying the services (e.g., Dataflow, Apache Beam, Dataproc, Cloud Data Fusion, BigQuery, Pub/Sub, Apache Spark, Hadoop ecosystem, and Apache Kafka)
AdvancedProfessional Cloud DevOps Engineer
Google Cloud Custom Organization Policy Constraints
Managing configuration and policy
Last updated Validated