Professional Data Engineer Free Practice Questions — Page 16
Question 133
You have a data stored in BigQuery. The data in the BigQuery dataset must be highly available. You need to define a storage, backup, and recovery strategy of this data that minimizes cost. How should you configure the BigQuery table that have a recovery point objective (RPO) of 30 days?
A. Set the BigQuery dataset to be regional. In the event of an emergency, use a point-in-time snapshot to recover the data.
B. Set the BigQuery dataset to be regional. Create a scheduled query to make copies of the data to tables suffixed with the time of the backup. In the event of an emergency, use the backup copy of the table.
C. Set the BigQuery dataset to be multi-regional. In the event of an emergency, use a point-in-time snapshot to recover the data.
D. Set the BigQuery dataset to be multi-regional. Create a scheduled query to make copies of the data to tables suffixed with the time of the backup. In the event of an emergency, use the backup copy of the table.
Show Answer
Correct Answer: B
Explanation: BigQuery time travel (point-in-time restore) only supports recovery up to 7 days, which does not meet a 30‑day RPO. To retain recoverable data for 30 days, you must create explicit backups, such as time‑suffixed tables via scheduled queries. To minimize cost, the dataset should be regional rather than multi‑regional, since multi‑regional storage is more expensive and not required by the question. Therefore, a regional dataset with scheduled backup copies best meets the availability, RPO, and cost requirements.
Question 134
You're using Bigtable for a real-time application, and you have a heavy load that is a mix of read and writes. You've recently identified an additional use case and need to perform hourly an analytical job to calculate certain statistics across the whole database. You need to ensure both the reliability of your production application as well as the analytical workload.
What should you do?
A. Export Bigtable dump to GCS and run your analytical job on top of the exported files.
B. Add a second cluster to an existing instance with a multi-cluster routing, use live-traffic app profile for your regular workload and batch-analytics profile for the analytics workload.
C. Add a second cluster to an existing instance with a single-cluster routing, use live-traffic app profile for your regular workload and batch-analytics profile for the analytics workload.
D. Increase the size of your existing cluster twice and execute your analytics workload on your new resized cluster.
Show Answer
Correct Answer: C
Explanation: To protect a latency‑sensitive production workload from a heavy, periodic analytics job, Bigtable best practice is workload isolation using replication and app profiles. By adding a second cluster and using **single‑cluster routing** app profiles, you can route the live read/write traffic to one cluster and the hourly batch analytics job to the other cluster. This prevents the batch job from consuming CPU and I/O resources needed by the production application. This pattern is explicitly documented by Google as the recommended solution for the *batch vs serve* use case.
Question 135
Your team is responsible for developing and maintaining ETLs in your company. One of your Dataflow jobs is failing because of some errors in the input data, and you need to improve reliability of the pipeline (incl. being able to reprocess all failing data).
What should you do?
A. Add a filtering step to skip these types of errors in the future, extract erroneous rows from logs.
B. Add a tryג€¦ catch block to your DoFn that transforms the data, extract erroneous rows from logs.
C. Add a tryג€¦ catch block to your DoFn that transforms the data, write erroneous rows to Pub/Sub PubSub directly from the DoFn.
D. Add a tryג€¦ catch block to your DoFn that transforms the data, use a sideOutput to create a PCollection that can be stored to Pub/Sub later.
Show Answer
Correct Answer: D
Explanation: To improve reliability and enable reprocessing of failed records in a Dataflow/Apache Beam pipeline, errors should be handled inside the DoFn and routed separately without disrupting the main flow. Using try–catch in the DoFn allows catching bad records, and emitting them via a side output (tagged output) creates a separate PCollection of erroneous rows. That PCollection can then be written efficiently using standard IO connectors (e.g., PubSubIO, BigQueryIO) with proper batching and retries. Writing directly to Pub/Sub from inside a DoFn is discouraged due to performance and scalability issues, and filtering errors would lose data needed for reprocessing.
Question 136
An organization maintains a Google BigQuery dataset that contains tables with user-level data. They want to expose aggregates of this data to other Google
Cloud projects, while still controlling access to the user-level data. Additionally, they need to minimize their overall storage cost and ensure the analysis cost for other projects is assigned to those projects. What should they do?
A. Create and share an authorized view that provides the aggregate results.
B. Create and share a new dataset and view that provides the aggregate results.
C. Create and share a new dataset and table that contains the aggregate results.
D. Create dataViewer Identity and Access Management (IAM) roles on the dataset to enable sharing.
Show Answer
Correct Answer: A
Explanation: An authorized view lets the organization expose only aggregated results while keeping user-level tables inaccessible. It avoids duplicating data, minimizing storage costs, and BigQuery charges query (analysis) costs to the project that runs the query, so downstream projects pay for their own analysis. Other options either copy data (higher storage cost) or grant overly broad access.
Question 137
You are deploying a new storage system for your mobile application, which is a media streaming service. You decide the best fit is Google Cloud Datastore. You have entities with multiple properties, some of which can take on multiple values. For example, in the entity 'Movie' the property 'actors' and the property
'tags' have multiple values but the property 'date released' does not. A typical query would ask for all movies with actor=<actorname> ordered by date_released or all movies with tag=Comedy ordered by date_released. How should you avoid a combinatorial explosion in the number of indexes?
A. Manually configure the index in your index config as follows:
B. Manually configure the index in your index config as follows:
C. Set the following in your entity options: exclude_from_indexes = 'actors, tags'
D. Set the following in your entity options: exclude_from_indexes = 'date_published'
Show Answer
Correct Answer: A
Explanation: In Cloud Datastore, combinatorial index explosion occurs when multiple multi-valued properties are combined in composite indexes. The recommended way to avoid this is to manually define only the composite indexes that match your actual query patterns. In this case, queries filter on a single multi-valued property (actors or tags) and order by a single-valued property (date_released). By explicitly defining separate composite indexes (actors + date_released, tags + date_released), you avoid creating unnecessary combinations and prevent index explosion. Excluding date_released would break ordering queries, and excluding actors/tags would prevent filtering, so manual index configuration is the correct solution.
Question 138
You are migrating a table to BigQuery and are deciding on the data model. Your table stores information related to purchases made across several store locations and includes information like the time of the transaction, items purchased, the store ID, and the city and state in which the store is located. You frequently query this table to see how many of each item were sold over the past 30 days and to look at purchasing trends by state, city, and individual store. How would you model this table for the best query performance?
A. Partition by transaction time; cluster by state first, then city, then store ID.
B. Partition by transaction time; cluster by store ID first, then city, then state.
C. Top-level cluster by state first, then city, then store ID.
D. Top-level cluster by store ID first, then city, then state.
Show Answer
Correct Answer: A
Explanation: Queries frequently filter on a recent time window (past 30 days), so partitioning by transaction time allows BigQuery to scan only the relevant partitions and minimizes cost. Within each time partition, clustering should align with common grouping and filtering dimensions. Since analysis is done by state, then city, then individual store, clustering in that left-to-right hierarchical order (state → city → store ID) provides the best pruning and query performance. Options without time partitioning or with less optimal clustering order are less efficient.
Question 139
You are designing a basket abandonment system for an ecommerce company. The system will send a message to a user based on these rules:
✑ No interaction by the user on the site for 1 hour
Has added more than $30 worth of products to the basket
✑ Has not completed a transaction
You use Google Cloud Dataflow to process the data and decide if a message should be sent. How should you design the pipeline?
A. Use a fixed-time window with a duration of 60 minutes.
B. Use a sliding time window with a duration of 60 minutes.
C. Use a session window with a gap time duration of 60 minutes.
D. Use a global window with a time based trigger with a delay of 60 minutes.
Show Answer
Correct Answer: C
Explanation: A session window with a 60-minute gap is designed to model user activity separated by periods of inactivity. In this scenario, you need to detect when a user has stopped interacting with the site for 1 hour, then evaluate the session to see if more than $30 of items were added and no transaction was completed. Session windows naturally close after the defined inactivity gap, making them ideal for basket abandonment and user session–based logic. Fixed or sliding windows do not model inactivity gaps, and a global window with triggers would be unnecessarily complex.
Question 140
You work for an advertising company, and you've developed a Spark ML model to predict click-through rates at advertisement blocks. You've been developing everything at your on-premises data center, and now your company is migrating to Google Cloud. Your data center will be closing soon, so a rapid lift-and-shift migration is necessary. However, the data you've been using will be migrated to migrated to BigQuery. You periodically retrain your Spark ML models, so you need to migrate existing training pipelines to Google Cloud. What should you do?
A. Use Vertex AI for training existing Spark ML models
B. Rewrite your models on TensorFlow, and start using Vertex AI
C. Use Dataproc for training existing Spark ML models, but start reading data directly from BigQuery
D. Spin up a Spark cluster on Compute Engine, and train Spark ML models on the data exported from BigQuery
Show Answer
Correct Answer: C
Explanation: The requirement is a rapid lift-and-shift with minimal code changes while continuing to retrain existing Spark ML models, and the data will live in BigQuery. Cloud Dataproc is Google Cloud’s managed Spark service and supports Spark MLlib directly, allowing you to run the same Spark training pipelines with little or no rewrite. Dataproc integrates natively with BigQuery via the BigQuery connector, so you can read training data directly from BigQuery. Vertex AI would require rework or framework changes, and managing raw Spark clusters on Compute Engine adds unnecessary operational overhead.
Question 141
Your company is using WILDCARD tables to query data across multiple tables with similar names. The SQL statement is currently failing with the following error:
Which table name will make the SQL statement work correctly?
A. 'bigquery-public-data.noaa_gsod.gsod'
B. bigquery-public-data.noaa_gsod.gsod*
C. 'bigquery-public-data.noaa_gsod.gsod'*
D. 'bigquery-public-data.noaa_gsod.gsod*`
Show Answer
Correct Answer: D
Explanation: In BigQuery, wildcard table references (using *) must be enclosed in backticks (`). Without backticks, the parser raises a syntax error when it encounters *. Therefore the correct form is `bigquery-public-data.noaa_gsod.gsod*`. Option D represents this (despite a typographical issue in how it’s displayed), making it the only correct choice.
Question 142
You are operating a streaming Cloud Dataflow pipeline. Your engineers have a new version of the pipeline with a different windowing algorithm and triggering strategy. You want to update the running pipeline with the new version. You want to ensure that no data is lost during the update. What should you do?
A. Update the Cloud Dataflow pipeline inflight by passing the --update option with the --jobName set to the existing job name
B. Update the Cloud Dataflow pipeline inflight by passing the --update option with the --jobName set to a new unique job name
C. Stop the Cloud Dataflow pipeline with the Cancel option. Create a new Cloud Dataflow job with the updated code
D. Stop the Cloud Dataflow pipeline with the Drain option. Create a new Cloud Dataflow job with the updated code
Show Answer
Correct Answer: D
Explanation: The new pipeline introduces major changes to windowing and triggering. Cloud Dataflow in‑place updates (--update) only work when the compatibility check passes, and Google explicitly warns that changing the windowing algorithm or triggers is likely incompatible and can produce unpredictable results. To ensure no data is lost, the correct approach is to drain the existing streaming job so all in‑flight data is processed, then start a new Dataflow job with the updated code. Canceling would risk data loss, and in‑place updates are not recommended for this type of change.
$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.