Professional Data Engineer Free Practice Questions — Page 12
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 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.
Question 100
You are deploying a MySQL database workload onto Cloud SQL. The database must be able to scale up to support several readers from various geographic regions. The database must be highly available and meet low RTO and RPO requirements, even in the event of a regional outage. You need to ensure that interruptions to the readers are minimal during a database failover. What should you do?
A. Create a highly available Cloud SQL instance in region Create a highly available read replica in region B. Scale up read workloads by creating cascading read replicas in multiple regions. Backup the Cloud SQL instances to a multi-regional Cloud Storage bucket. Restore the Cloud SQL backup to a new instance in another region when Region A is down.
B. Create a highly available Cloud SQL instance in region A. Scale up read workloads by creating read replicas in multiple regions. Promote one of the read replicas when region A is down.
C. Create a highly available Cloud SQL instance in region A. Create a highly available read replica in region B. Scale up read workloads by creating cascading read replicas in multiple regions. Promote the read replica in region B when region A is down.
D. Create a highly available Cloud SQL instance in region A. Scale up read workloads by creating read replicas in the same region. Failover to the standby Cloud SQL instance when the primary instance fails.
Show Answer
Correct Answer: C
Explanation: Option C best satisfies all requirements: regional read scaling, high availability, low RTO/RPO during a regional outage, and minimal interruption for readers. A backup-and-restore approach has a much higher RTO. Option D only protects against zonal failures within a region, not regional outages. Option B uses cross-region read replicas, but the designated failover replica is not highly available itself; making the cross-region failover target a highly available instance improves resilience and availability during failover.
Question 101
You want to schedule a number of sequential load and transformation jobs. Data files will be added to a Cloud Storage bucket by an upstream process. There is no fixed schedule for when the new data arrives. Next, a Dataproc job is triggered to perform some transformations and write the data to BigQuery. You then need to run additional transformation jobs in BigQuery. The transformation jobs are different for every table. These jobs might take hours to complete. You need to determine the most efficient and maintainable workflow to process hundreds of tables and provide the freshest data to your end users. What should you do?
A. 1. Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Cloud Storage, Dataproc, and BigQuery operators. 2. Use a single shared DAG for all tables that need to go through the pipeline. 3. Schedule the DAG to run hourly.
B. 1. Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Cloud Storage, Dataproc, and BigQuery operators. 2. Create a separate DAG for each table that needs to go through the pipeline. 3. Schedule the DAGs to run hourly.
C. 1. Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Dataproc and BigQuery operators. 2. Use a single shared DAG for all tables that need to go through the pipeline. 3. Use a Cloud Storage object trigger to launch a Cloud Function that triggers the DAG.
D. 1. Create an Apache Airflow directed acyclic graph (DAG) in Cloud Composer with sequential tasks by using the Dataproc and BigQuery operators. 2. Create a separate DAG for each table that needs to go through the pipeline. 3. Use a Cloud Storage object trigger to launch a Cloud Function that triggers the DAG.
Show Answer
Correct Answer: C
Explanation: Because data arrival is event-driven rather than scheduled, the workflow should be triggered by Cloud Storage object creation instead of hourly scheduling. The DAG only needs Dataproc and BigQuery operators because Cloud Storage is the event source, not a processing step. For maintainability across hundreds of tables, a shared parameterized DAG is preferred over maintaining hundreds of separate DAGs, while table-specific logic can be driven by configuration or parameters.
Sources:
https://cloud.google.com/bigquery/docs/migration/pipelines
Question 102
You migrated a data backend for an application that serves 10 PB of historical product data for analytics. Only the last known state for a product, which is about 10 GB of data, needs to be served through an API to the other applications. You need to choose a cost-effective persistent storage solution that can accommodate the analytics requirements and the API performance of up to 1000 queries per second (QPS) with less than 1 second latency. What should you do?
A. 1. Store the historical data in BigQuery for analytics. 2. Use a materialized view to precompute the last state of a product. 3. Serve the last state data directly from BigQuery to the API.
B. 1. Store the products as a collection in Firestore with each product having a set of historical changes. 2. Use simple and compound queries for analytics. 3. Serve the last state data directly from Firestore to the API.
C. 1. Store the historical data in Cloud SQL for analytics. 2. In a separate table, store the last state of the product after every product change. 3. Serve the last state data directly from Cloud SQL to the API.
D. 1. Store the historical data in BigQuery for analytics. 2. In a Cloud SQL table, store the last state of the product after every product change. 3. Serve the last state data directly from Cloud SQL to the API.
Show Answer
Correct Answer: D
Explanation: BigQuery is the appropriate storage for 10 PB of historical analytical data. Serving a high-QPS, low-latency API is better suited to an operational database than BigQuery. Maintaining a separate table with only the latest product state (~10 GB) in Cloud SQL provides fast point lookups while keeping analytics and serving workloads separated. Firestore is not appropriate for 10 PB historical analytics, and Cloud SQL is not appropriate for storing 10 PB for analytics.
Question 103
You are a BigQuery admin supporting a team of data consumers who run ad hoc queries and downstream reporting in tools such as Looker. All data and users are combined under a single organizational project. You recently noticed some slowness in query results and want to troubleshoot where the slowdowns are occurring. You think that there might be some job queuing or slot contention occurring as users run jobs, which slows down access to results. You need to investigate the query job information and determine where performance is being affected. What should you do?
A. Use slot reservations for your project to ensure that you have enough query processing capacity and are able to allocate available slots to the slower queries.
B. Use Cloud Monitoring to view BigQuery metrics and set up alerts that let you know when a certain percentage of slots were used.
C. Use available administrative resource charts to determine how slots are being used and how jobs are performing over time. Run a query on the INFORMATION_SCHEMA to review query performance.
D. Use Cloud Logging to determine if any users or downstream consumers are changing or deleting access grants on tagged resources.
Show Answer
Correct Answer: C
Explanation: Administrative resource charts are designed to troubleshoot BigQuery slot utilization, job concurrency, and performance over time. INFORMATION_SCHEMA views such as JOBS and related views provide detailed job execution, wait time, and slot usage information to identify queuing or contention. Option A is a mitigation strategy rather than an investigation step, B provides high-level monitoring but not the detailed job analysis requested, and D is unrelated to query performance.
Question 104
You are on the data governance team and are implementing security requirements to deploy resources. You need to ensure that resources are limited to only the europe-west3 region. You want to follow Google-recommended practices.
What should you do?
A. Set the constraints/gcp.resourceLocations organization policy constraint to in:europe-west3-locations.
B. Deploy resources with Terraform and implement a variable validation rule to ensure that the region is set to the europe-west3 region for all resources.
C. Set the constraints/gcp.resourceLocations organization policy constraint to in:eu-locations.
D. Create a Cloud Function to monitor all resources created and automatically destroy the ones created outside the europe-west3 region.
Show Answer
Correct Answer: A
Explanation: The Google-recommended way to restrict where resources can be created is to use the Organization Policy constraint `constraints/gcp.resourceLocations`. Setting it to `in:europe-west3-locations` restricts resource creation to the europe-west3 region. Using `in:eu-locations` would allow resources across multiple EU regions, not just europe-west3. Terraform validation is not an enforcement mechanism outside Terraform, and deleting noncompliant resources after creation is not the recommended preventive control.
$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.