Professional Data Engineer Free Practice Questions — Page 10
Question 73
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: An RPO of 15 minutes requires that written data be replicated across regions within 15 minutes during a regional outage. Only dual-region Cloud Storage with turbo replication provides a documented 15‑minute RPO guarantee. A multi-region bucket does not provide an explicit 15‑minute RPO, and dual-region without turbo replication can take much longer to fully replicate. Monitoring Dataflow allows detection of the outage, and restarting the job in a secondary region combined with seeking the Pub/Sub subscription ensures reprocessing of recent messages within retention limits. Seeking back 60 minutes is conservative and still within the 1‑day retention, ensuring no data loss beyond the RPO.
Question 74
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: The requirement is resilience to a regional failure with the smallest possible recovery point objective (RPO) and no application impact. Dual‑region Cloud Storage buckets automatically replicate data across two specific regions, and enabling turbo replication guarantees that 100% of newly written objects are replicated to both regions within an RPO of about 15 minutes. This provides stronger, explicit RPO guarantees than default replication or multi‑regional buckets, while remaining transparent to applications. Other options either rely on manual copying, introduce higher RPO, or require application changes.
Question 75
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 is designed to simplify data access management across BigQuery and Cloud Storage by using Dataplex data roles instead of granting IAM on each underlying resource. Data engineers need full control over all assets in the lake, which is provided by the dataplex.dataOwner role at the lake level. Analytic users should only read curated data, which is correctly granted by assigning the dataplex.dataReader role at the curated zone level. This meets the access requirements while minimizing permission complexity.
Question 76
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 core problem is excessive query concurrency, not insufficient total slot capacity. BigQuery batch queries are queued and only start when resources are available, which naturally smooths concurrency for non-time-sensitive workloads. Converting the hundreds of non-urgent SQL pipelines to batch jobs prevents them from competing immediately for slots, while keeping ad-hoc queries interactive preserves responsiveness for analysts. Increasing slots (A or C) increases cost and does not directly address uncontrolled concurrency, and making everything interactive (D) would worsen contention.
Question 77
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 encrypting data at a finer granularity than table-level encryption so that deleting or revoking a specific user’s key renders only that user’s data unreadable. BigQuery AEAD functions provide native, column-level encryption and decryption within BigQuery, allowing you to use different keys per user and achieve crypto-deletion by removing the corresponding key. CMEK encrypts data at the table or dataset level and cannot support per-user crypto-deletion, and external encryption approaches introduce custom, non-native solutions.
Question 78
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 a fully programmatic solution that supports both streaming and batch ingestion, integrates data masking before load, and minimizes cost. Apache Beam on Dataflow provides code-level control, supports both streaming and batch in the same pipeline, integrates with Cloud DLP for de-identification, and writes directly to BigQuery while using a serverless, pay-per-use model. Cloud Data Fusion is low-code (not fully programmatic), BigQuery Data Transfer Service doesn’t handle on-prem streaming or pre-load masking, and Datastream is focused on database replication rather than general ETL with DLP.
Question 79
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: The requirement includes global read scaling, high availability, low RTO/RPO even for a regional outage, and minimal reader interruption during failover. Option C best meets all of these: a highly available primary in region A protects against zonal failures; a highly available read replica in region B provides a resilient cross‑region failover target; cascading read replicas in multiple regions support global read traffic with low latency. In a regional outage of A, promoting the HA replica in B avoids slow backup restores and provides the fastest recovery with minimal disruption. Option A relies on backup restore (high RTO/RPO), option B lacks HA on the failover replica, and option D does not address regional outages or global readers.
Question 80
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: The workflow must be event-driven because data arrival is unpredictable, so hourly schedules (A, B) are inefficient. Triggering the pipeline from a Cloud Storage object notification via Cloud Functions provides the freshest data. Using Dataproc and BigQuery operators is sufficient since transformations occur there. For hundreds of tables, a single shared DAG is more maintainable and scalable than managing hundreds of separate DAGs; table-specific logic can be handled via parameters or configuration. Therefore, option C best balances efficiency, maintainability, and freshness.
Question 81
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: The workload has two very different access patterns. The 10 PB of historical product data is purely analytical, which makes BigQuery the most cost‑effective and scalable choice for storage and analytics. The API, however, only needs the latest state (~10 GB total) and must handle ~1000 QPS with sub‑second latency, which is a classic low‑latency, high‑concurrency operational workload. Serving this directly from BigQuery, even via materialized views, is not ideal for consistent low‑latency API traffic. Storing the latest product state in Cloud SQL provides predictable performance and easily meets the QPS and latency requirements while keeping costs reasonable. Separating analytics (BigQuery) from serving traffic (Cloud SQL) is the most appropriate design.
Question 82
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: To investigate query slowdowns, job queuing, and slot contention, you need visibility into how BigQuery jobs are using slots over time. BigQuery’s administrative resource charts show slot utilization, concurrency, and job performance trends, which helps identify contention or queuing. Querying INFORMATION_SCHEMA views (such as JOBS or JOBS_BY_PROJECT) provides detailed per-query metadata, including execution time, slot usage, and queued time. Together, these directly address troubleshooting where performance is being affected.
$19
Get all 328 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.