Professional Data Engineer Free Practice Questions — Page 11
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.
Question 105
You need to modernize your existing on-premises data strategy. Your organization currently uses:
• Apache Hadoop clusters for processing multiple large data sets, including on-premises Hadoop Distributed File System (HDFS) for data replication.
• Apache Airflow to orchestrate hundreds of ETL pipelines with thousands of job steps.
You need to set up a new architecture in Google Cloud that can handle your Hadoop workloads and requires minimal changes to your existing orchestration processes. What should you do?
A. Use Bigtable for your large workloads, with connections to Cloud Storage to handle any HDFS use cases. Orchestrate your pipelines with Cloud Composer.
B. Use Dataproc to migrate Hadoop clusters to Google Cloud, and Cloud Storage to handle any HDFS use cases. Orchestrate your pipelines with Cloud Composer.
C. Use Dataproc to migrate Hadoop clusters to Google Cloud, and Cloud Storage to handle any HDFS use cases. Convert your ETL pipelines to Dataflow.
D. Use Dataproc to migrate your Hadoop clusters to Google Cloud, and Cloud Storage to handle any HDFS use cases. Use Cloud Data Fusion to visually design and deploy your ETL pipelines.
Show Answer
Correct Answer: B
Explanation: Dataproc is the managed Google Cloud service for running Apache Hadoop and Spark workloads with minimal migration effort. Cloud Storage is the recommended replacement for HDFS, providing durable shared storage. Cloud Composer is Google Cloud's managed Apache Airflow service, allowing existing Airflow DAGs and orchestration processes to be reused with minimal changes.
Question 106
You are using a Dataflow streaming job to read messages from a message bus that does not support exactly-once delivery. Your job then applies some transformations, and loads the result into BigQuery. You want to ensure that your data is being streamed into BigQuery with exactly-once delivery semantics. You expect your ingestion throughput into BigQuery to be about 1.5 GB per second. What should you do?
A. Use the BigQuery Storage Write API and ensure that your target BigQuery table is regional.
B. Use the BigQuery Storage Write API and ensure that your target BigQuery table is multiregional.
C. Use the BigQuery Streaming API and ensure that your target BigQuery table is regional.
D. Use the BigQuery Streaming API and ensure that your target BigQuery table is multiregional.
Show Answer
Correct Answer: B
Explanation: To achieve exactly-once delivery into BigQuery from Dataflow, use the BigQuery Storage Write API, which supports exactly-once semantics via stream offsets. The required throughput is about 1.5 GB/s, which exceeds the typical regional Storage Write API throughput quota (around 300 MB/s) but fits within multiregional throughput limits (around 3 GB/s). The legacy Streaming API does not provide exactly-once delivery semantics.
Question 107
Your organization uses a multi-cloud data storage strategy, storing data in Cloud Storage, and data in Amazon Web Services’ (AWS) S3 storage buckets. All data resides in US regions. You want to query up-to-date data by using BigQuery, regardless of which cloud the data is stored in. You need to allow users to query the tables from BigQuery without giving direct access to the data in the storage buckets. What should you do?
A. Setup a BigQuery Omni connection to the AWS S3 bucket data. Create BigLake tables over the Cloud Storage and S3 data and query the data using BigQuery directly.
B. Set up a BigQuery Omni connection to the AWS S3 bucket data. Create external tables over the Cloud Storage and S3 data and query the data using BigQuery directly.
C. Use the Storage Transfer Service to copy data from the AWS S3 buckets to Cloud Storage buckets. Create BigLake tables over the Cloud Storage data and query the data using BigQuery directly.
D. Use the Storage Transfer Service to copy data from the AWS S3 buckets to Cloud Storage buckets. Create external tables over the Cloud Storage data and query the data using BigQuery directly.
Show Answer
Correct Answer: A
Explanation: BigQuery Omni is designed to query data in AWS S3 without moving it into Google Cloud. For S3 data accessed through BigQuery Omni, you create BigLake tables that reference the external data. BigLake also provides fine-grained access control so users can query through BigQuery without requiring direct access to the underlying storage buckets. This satisfies the requirement to query up-to-date data across both Cloud Storage and S3 without copying data.
Question 108
You are architecting a data transformation solution for BigQuery. Your developers are proficient with SQL and want to use the ELT development technique. In addition, your developers need an intuitive coding environment and the ability to manage SQL as code. You need to identify a solution for your developers to build these pipelines. What should you do?
A. Use Dataform to build, manage, and schedule SQL pipelines.
B. Use Dataflow jobs to read data from Pub/Sub, transform the data, and load the data to BigQuery.
C. Use Data Fusion to build and execute ETL pipelines.
D. Use Cloud Composer to load data and run SQL pipelines by using the BigQuery job operators.
Show Answer
Correct Answer: A
Explanation: Dataform is purpose-built for SQL-based ELT development on BigQuery. It provides an intuitive SQL development environment, supports SQL as code with version control and dependency management, and can build, manage, test, and schedule BigQuery transformation pipelines. The other options are aimed at broader ETL/orchestration or streaming use cases rather than SQL-first ELT development in BigQuery.
Question 109
One of your encryption keys stored in Cloud Key Management Service (Cloud KMS) was exposed. You need to re- encrypt all of your CMEK-protected Cloud Storage data that used that key, and then delete the compromised key. You also want to reduce the risk of objects getting written without customer-managed encryption key (CMEK) protection in the future. What should you do?
A. Rotate the Cloud KMS key version. Continue to use the same Cloud Storage bucket.
B. Create a new Cloud KMS key. Set the default CMEK key on the existing Cloud Storage bucket to the new one.
C. Create a new Cloud KMS key. Create a new Cloud Storage bucket. Copy all objects from the old bucket to the new one bucket while specifying the new Cloud KMS key in the copy command.
D. Create a new Cloud KMS key. Create a new Cloud Storage bucket configured to use the new key as the default CMEK key. Copy all objects from the old bucket to the new bucket without specifying a key.
Show Answer
Correct Answer: D
Explanation: Create a new Cloud KMS key because rotating a key version does not re-encrypt existing Cloud Storage objects. Create a new bucket with the new key configured as the bucket's default CMEK, then copy the objects without specifying a key so the destination bucket's default CMEK is automatically applied. Using a default CMEK on the bucket also reduces the risk of future uploads being written without the intended customer-managed key protection.
$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.