Professional Data Engineer Free Practice Questions — Page 10
Question 90
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?
A. 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. 2. Load the booking and user profile data into a BigQuery table.
B. 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. 2. Load the booking and user profile data into a BigQuery table.
C. 1. Load the CSV files from Cloud Storage into a BigQuery table, and enable dynamic data masking. 2. Create a policy tag with the email mask as the data masking rule. 3. Assign the policy to the email field in both tables. A 4. Assign the Identity and Access Management bigquerydatapolicy.maskedReader role for the BigQuery tables to the analysts.
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
Show Answer
Correct Answer: B
Explanation: The requirement is to de-identify email addresses before loading the data into BigQuery while still allowing analysts to join the two datasets on the email field. Simple masking destroys uniqueness, preventing reliable joins. BigQuery dynamic data masking occurs after the data is loaded and therefore does not satisfy the requirement to de-identify before loading. Cloud DLP format-preserving encryption (FFX) provides deterministic pseudonymization so the same email is transformed into the same encrypted value across both datasets, enabling joins without exposing the original PII.
Question 91
dataset.inventory_vm sample records:
You have an inventory of VM data stored in the BigQuery table. You want to prepare the data for regular reporting in the most cost-effective way. You need to exclude VM rows with fewer than 8 vCPU in your report. What should you do?
A. Create a view with a filter to drop rows with fewer than 8 vCPU, and use the UNNEST operator.
B. Create a materialized view with a filter to drop rows with fewer than 8 vCPU, and use the WITH common table expression.
C. Create a view with a filter to drop rows with fewer than 8 vCPU, and use the WITH common table expression.
D. Use Dataflow to batch process and write the result to another BigQuery table.
Show Answer
Correct Answer: A
Explanation: The correct choice is A. The vCPU value is stored in a nested/repeated field, so UNNEST is required to access and filter it. A standard view provides a reusable query without duplicating data and is the most cost-effective option for preparing reporting data. A materialized view incurs additional storage/maintenance considerations and WITH is only a query structuring construct, not a solution for nested fields. Dataflow is unnecessary overhead for a simple filter in BigQuery.
Question 92
Your company operates in three domains: airlines, hotels, and ride-hailing services. Each domain has two teams: analytics and data science, which create data assets in BigQuery with the help of a central data platform team. However, as each domain is evolving rapidly, the central data platform team is becoming a bottleneck. This is causing delays in deriving insights from data, and resulting in stale data when pipelines are not kept up to date. You need to design a data mesh architecture by using Dataplex to eliminate the bottleneck. What should you do?
A. 1. Create one lake for each team. Inside each lake, create one zone for each domain. 2. Attach each of the BigQuery datasets created by the individual teams as assets to the respective zone. 3. Have the central data platform team manage all zones’ data assets.
B. 1. Create one lake for each team. Inside each lake, create one zone for each domain. 2. Attach each of the BigQuery datasets created by the individual teams as assets to the respective zone. 3. Direct each domain to manage their own zone’s data assets.
C. 1. Create one lake for each domain. Inside each lake, create one zone for each team. 2. Attach each of the BigQuery datasets created by the individual teams as assets to the respective zone. 3. Direct each domain to manage their own lake’s data assets.
D. 1. Create one lake for each domain. Inside each lake, create one zone for each team. 2. Attach each of the BigQuery datasets created by the individual teams as assets to the respective zone. 3. Have the central data platform team manage all lakes’ data assets.
Show Answer
Correct Answer: C
Explanation: A domain-oriented data mesh in Dataplex is organized with one lake per business domain, allowing each domain to own and govern its data independently. Within each domain lake, separate zones can represent the analytics and data science teams. BigQuery datasets are attached as assets to the appropriate zones, and ownership remains with the domain rather than a centralized platform team, eliminating the bottleneck.
Question 93
You have one BigQuery dataset which includes customers’ street addresses. You want to retrieve all occurrences of street addresses from the dataset. What should you do?
A. Write a SQL query in BigQuery by using REGEXP_CONTAINS on all tables in your dataset to find rows where the word “street” appears.
B. Create a deep inspection job on each table in your dataset with Cloud Data Loss Prevention and create an inspection template that includes the STREET_ADDRESS infoType.
C. Create a discovery scan configuration on your organization with Cloud Data Loss Prevention and create an inspection template that includes the STREET_ADDRESS infoType.
D. Create a de-identification job in Cloud Data Loss Prevention and use the masking transformation.
Show Answer
Correct Answer: B
Explanation: Cloud Data Loss Prevention (Sensitive Data Protection) includes a built-in STREET_ADDRESS infoType that is designed to inspect structured data such as BigQuery tables for street addresses. A deep inspection job targeted at the dataset's tables will identify all occurrences. REGEXP_CONTAINS for the literal word 'street' would miss many valid addresses, discovery scan configurations are intended for broad organizational data profiling rather than a targeted dataset retrieval task, and de-identification jobs are for masking/transformation rather than finding occurrences.
Question 94
You have designed an Apache Beam processing pipeline that reads from a Pub/Sub topic. The topic has a message retention duration of one day, and writes to a Cloud Storage bucket. You need to select a bucket location and processing strategy to prevent data loss in case of a regional outage with an RPO of 15 minutes. What should you do?
A. 1. Use a dual-region Cloud Storage bucket. 2. Monitor Dataflow metrics with Cloud Monitoring to determine when an outage occurs. 3. Seek the subscription back in time by 15 minutes to recover the acknowledged messages. 4. Start the Dataflow job in a secondary region.
B. 1. Use a multi-regional Cloud Storage bucket. 2. Monitor Dataflow metrics with Cloud Monitoring to determine when an outage occurs. 3. Seek the subscription back in time by 60 minutes to recover the acknowledged messages. 4. Start the Dataflow job in a secondary region.
C. 1. Use a regional Cloud Storage bucket. 2. Monitor Dataflow metrics with Cloud Monitoring to determine when an outage occurs. 3. Seek the subscription back in time by one day to recover the acknowledged messages. 4. Start the Dataflow job in a secondary region and write in a bucket in the same region.
D. 1. Use a dual-region Cloud Storage bucket with turbo replication enabled. 2. Monitor Dataflow metrics with Cloud Monitoring to determine when an outage occurs. 3. Seek the subscription back in time by 60 minutes to recover the acknowledged messages. 4. Start the Dataflow job in a secondary region.
Show Answer
Correct Answer: D
Explanation: A 15-minute RPO for stored output requires a dual-region Cloud Storage bucket with Turbo Replication, which is the feature that provides a 15-minute replication RPO. After a regional outage, detect the failure, restart the Dataflow job in a secondary region, and use Pub/Sub seek to replay retained messages. Among the choices, only D includes Turbo Replication. Although the 60-minute seek value is conservative rather than minimal, it still satisfies the recovery objective, whereas A lacks the required Turbo Replication guarantee for a 15-minute RPO.
Question 95
You are designing the architecture of your application to store data in Cloud Storage. Your application consists of pipelines that read data from a Cloud Storage bucket that contains raw data, and write the data to a second bucket after processing. You want to design an architecture with Cloud Storage resources that are capable of being resilient if a Google Cloud regional failure occurs. You want to minimize the recovery point objective (RPO) if a failure occurs, with no impact on applications that use the stored data. What should you do?
A. Adopt multi-regional Cloud Storage buckets in your architecture.
B. Adopt two regional Cloud Storage buckets, and update your application to write the output on both buckets.
C. Adopt a dual-region Cloud Storage bucket, and enable turbo replication in your architecture.
D. Adopt two regional Cloud Storage buckets, and create a daily task to copy from one bucket to the other.
Show Answer
Correct Answer: C
Explanation: A dual-region Cloud Storage bucket provides automatic replication across two regions with a single bucket endpoint, so applications are unaffected by a regional failure. Enabling turbo replication minimizes the recovery point objective by providing a 15-minute replication RPO for newly written objects, making it the best fit for the requirement to minimize RPO while remaining resilient to a regional outage.
Question 96
You are designing a data mesh on Google Cloud by using Dataplex to manage data in BigQuery and Cloud Storage. You want to simplify data asset permissions. You are creating a customer virtual lake with two user groups:
• Data engineers, which require full data lake access
• Analytic users, which require access to curated data
You need to assign access rights to these two groups. What should you do?
A. 1. Grant the dataplex.dataOwner role to the data engineer group on the customer data lake. 2. Grant the dataplex.dataReader role to the analytic user group on the customer curated zone.
B. 1. Grant the dataplex.dataReader role to the data engineer group on the customer data lake. 2. Grant the dataplex.dataOwner to the analytic user group on the customer curated zone.
C. 1. Grant the bigquery.dataOwner role on BigQuery datasets and the storage.objectCreator role on Cloud Storage buckets to data engineers. 2. Grant the bigquery.dataViewer role on BigQuery datasets and the storage.objectViewer role on Cloud Storage buckets to analytic users.
D. 1. Grant the bigquery.dataViewer role on BigQuery datasets and the storage.objectViewer role on Cloud Storage buckets to data engineers. 2. Grant the bigquery.dataOwner role on BigQuery datasets and the storage.objectEditor role on Cloud Storage buckets to analytic users.
Show Answer
Correct Answer: A
Explanation: Dataplex data roles are designed to simplify permissions across underlying BigQuery and Cloud Storage assets. Granting dataplex.dataOwner at the lake level gives data engineers full access to the lake's managed data assets. Granting dataplex.dataReader on the curated zone restricts analytic users to read access only for curated data. This matches the requirement to simplify data asset permissions using Dataplex rather than assigning separate IAM roles on each underlying service.
Question 97
The data analyst team at your company uses BigQuery for ad-hoc queries and scheduled SQL pipelines in a Google Cloud project with a slot reservation of 2000 slots. However, with the recent introduction of hundreds of new non time-sensitive SQL pipelines, the team is encountering frequent quota errors. You examine the logs and notice that approximately 1500 queries are being triggered concurrently during peak time. You need to resolve the concurrency issue. What should you do?
A. Increase the slot capacity of the project with baseline as 0 and maximum reservation size as 3000.
B. Update SQL pipelines to run as a batch query, and run ad-hoc queries as interactive query jobs.
C. Increase the slot capacity of the project with baseline as 2000 and maximum reservation size as 3000.
D. Update SQL pipelines and ad-hoc queries to run as interactive query jobs.
Show Answer
Correct Answer: B
Explanation: The key detail is that the new SQL pipelines are explicitly non-time-sensitive. BigQuery batch queries are designed for this scenario: they are queued when compute resources are busy, reducing contention and avoiding concurrency quota issues, while interactive queries remain responsive for ad-hoc analysis. Increasing slots can add capacity, but the question asks how to resolve concurrency caused by many non-urgent jobs, and using batch priority is the intended workload management approach. Option D would make contention worse by making everything interactive. Option A is also inconsistent because a baseline of 0 would not preserve the existing guaranteed capacity of 2000 slots.
Question 98
You want to encrypt the customer data stored in BigQuery. You need to implement per-user crypto-deletion on data stored in your tables. You want to adopt native features in Google Cloud to avoid custom solutions. What should you do?
A. Implement Authenticated Encryption with Associated Data (AEAD) BigQuery functions while storing your data in BigQuery.
B. Create a customer-managed encryption key (CMEK) in Cloud KMS. Associate the key to the table while creating the table.
C. Create a customer-managed encryption key (CMEK) in Cloud KMS. Use the key to encrypt data before storing in BigQuery.
D. Encrypt your data during ingestion by using a cryptographic library supported by your ETL pipeline.
Show Answer
Correct Answer: A
Explanation: Per-user crypto-deletion requires application- or user-level encryption so that individual users' data can be rendered unreadable by destroying or revoking the specific encryption key. BigQuery's AEAD functions natively support column-level encryption/decryption and integrate with Cloud KMS keysets, enabling fine-grained encryption. CMEK for BigQuery encrypts data at rest but applies at the table/dataset level and cannot selectively crypto-delete a single user's data. Client-side encryption options are custom solutions, which the requirement seeks to avoid.
Question 99
You are planning to load some of your existing on-premises data into BigQuery on Google Cloud. You want to either stream or batch-load data, depending on your use case. Additionally, you want to mask some sensitive data before loading into BigQuery. You need to do this in a programmatic way while keeping costs to a minimum. What should you do?
A. Use Cloud Data Fusion to design your pipeline, use the Cloud DLP plug-in to de-identify data within your pipeline, and then move the data into BigQuery.
B. Use the BigQuery Data Transfer Service to schedule your migration. After the data is populated in BigQuery, use the connection to the Cloud Data Loss Prevention (Cloud DLP) API to de-identify the necessary data.
C. Create your pipeline with Dataflow through the Apache Beam SDK for Python, customizing separate options within your code for streaming, batch processing, and Cloud DLP. Select BigQuery as your data sink.
D. Set up Datastream to replicate your on-premise data on BigQuery.
Show Answer
Correct Answer: C
Explanation: The requirement is for a programmatic solution that supports both streaming and batch ingestion, masks sensitive data before loading into BigQuery, and minimizes operational overhead. Apache Beam with Dataflow supports both batch and streaming pipelines in code, can integrate Cloud DLP for de-identification before writing to BigQuery, and is serverless. Cloud Data Fusion is primarily a low-code tool rather than a programmatic approach. BigQuery Data Transfer Service does not fit on-premises custom streaming/batch ingestion with pre-load masking, and Datastream is for replication rather than DLP-based transformation.
$19
Get all 332 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.