PDE Designing Data Processing Systems Practice Test 11 — 15 Questions

Question 1

A healthcare company is designing a system to ingest HL7 messages from multiple hospitals into Google Cloud. The messages must be processed in near real-time to extract patient vitals and trigger alerts if thresholds are exceeded. The system must guarantee that no messages are lost and that processing is exactly-once. Which combination of Google Cloud services should they use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Cloud Pub/Sub with a pull subscription and Cloud Dataflow with exactly-once processing. Cloud Pub/Sub with a pull subscription ensures reliable message delivery, and Cloud Dataflow supports exactly-once processing for streaming pipelines. Together, they provide a managed solution that guarantees no message loss and exactly-once semantics. Dataflow can process HL7 messages in near real-time, extract vitals, and trigger alerts. This combination is the most robust and operationally efficient way to meet the strict processing guarantees required by the healthcare scenario.

Answer

Cloud Pub/Sub with a pull subscription and a custom application on Compute Engine that uses the Pub/Sub client library

Answer

Cloud Pub/Sub with a push subscription to a Cloud Function that writes to Cloud Firestore

Answer

Cloud Pub/Sub with a push subscription to Cloud Run, which then publishes to a second Pub/Sub topic for Dataflow

Question 2

You need to store petabytes of data in a data warehouse that supports ANSI SQL, automatic scaling, and real-time analytics. The data is primarily used for ad-hoc queries and business intelligence. Which Google Cloud service should you use?

Accepted Answer

BigQuery. BigQuery is the correct choice because it is a serverless, petabyte-scale data warehouse that supports ANSI SQL, automatically scales, and is optimized for ad-hoc queries and business intelligence. Its separation of storage and compute allows independent scaling and cost-effective analytics on large datasets.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 3

A media company is designing a data processing system on Google Cloud to analyze video streaming logs. The logs are generated continuously and stored in Cloud Storage. The company wants to use Cloud Dataflow to process these logs, but they need to ensure the pipeline can handle late-arriving data and provide accurate results for both real-time dashboards and historical analysis. Which two features of Cloud Dataflow should they use? (Choose two.)

Accepted Answer

Triggers to emit early results. To handle late-arriving data and provide accurate results for both real-time dashboards and historical analysis, the pipeline should use windowing with allowed lateness and triggers to emit early results. Windowing with allowed lateness ensures that late data is incorporated into the correct window, while triggers allow early results to be emitted for real-time dashboards and updated as more data arrives. Together, they balance latency and completeness.

Answer

Side inputs for enrichment

Answer

Exactly-once processing

Answer

Session windows

Question 4

A retail company runs a nightly batch pipeline that loads point-of-sale transactions into BigQuery. The pipeline uses a Cloud Composer (Apache Airflow) DAG with a BigQueryInsertJobOperator task that runs a SQL MERGE statement to upsert yesterday's sales into a large fact_sales table partitioned by transaction_date. The data engineering team notices that the MERGE task occasionally fails with a 'Resources exceeded during query execution' error when the source staging table contains more than 50 million rows. They need to redesign the DAG to reliably load large daily volumes without changing the final table schema or downstream dashboards. What should they do?

Accepted Answer

Replace the single MERGE statement with a multi-statement script that first deletes rows from the target partition for the affected dates and then inserts the staged rows using a BigQueryInsertJobOperator task with writeDisposition set to WRITE_APPEND.. A single large MERGE in BigQuery can hit internal resource limits because it must shuffle and join all source and target rows. Splitting the operation into a partition-scoped DELETE followed by an INSERT with WRITE_APPEND reduces the working set per statement and avoids the expensive shuffle. This pattern is a common, reliable way to upsert large daily batches while keeping the target schema and downstream queries unchanged.

Answer

Schedule the MERGE task to run during off-peak hours and increase the BigQuery slot reservation for the project so that the MERGE has more compute resources available.

Answer

Increase the number of Dataflow workers in the DAG by adding a DataflowOperator task that reads from the staging table and writes to the fact_sales table using a BigQueryIO write with STREAMING_INSERTS.

Answer

Convert the fact_sales table to a clustered table on transaction_date and re-run the original MERGE statement, relying on clustering to reduce the bytes scanned and avoid the resource error.

Question 5

Your company is migrating an on-premises Apache Hadoop cluster to Google Cloud. The cluster runs Hive for SQL-like queries and stores data in HDFS. You want a managed service that minimizes operational overhead while supporting existing Hive scripts. Which Google Cloud service should you choose?

Accepted Answer

Cloud Dataproc. Cloud Dataproc is the correct choice because it is a managed Hadoop and Spark service that supports Hive, HDFS, and other ecosystem tools, allowing you to run existing Hive scripts with minimal changes. It reduces operational overhead by handling cluster provisioning, configuration, and scaling.

Answer

Cloud Dataflow

Answer

Cloud Bigtable

Answer

BigQuery

Question 6

A logistics company wants to optimize their delivery routes using historical GPS data. The data is stored in BigQuery and is updated daily. They need to run a complex machine learning model that requires iterative processing over the entire dataset using Apache Spark. The model training takes several hours and must be run weekly. They want to minimize cost and operational overhead. Which approach should they take?

Accepted Answer

Use Dataproc Serverless for Spark with the BigQuery connector to read data directly from BigQuery.. Dataproc Serverless for Spark with the BigQuery connector allows the company to run Spark jobs directly on BigQuery data without exporting it. It is serverless, so it minimizes operational overhead and costs by charging only for job execution. It supports complex Spark MLlib models and iterative processing, making it the best fit for weekly training on historical GPS data. This approach aligns with the goals of minimizing cost and operational overhead while leveraging Spark's capabilities.

Answer

Run the Spark job on a long-running Dataproc cluster that is always available.

Answer

Use BigQuery ML to train the model directly in BigQuery without Spark.

Answer

Export the data to Cloud Storage and run a Dataproc cluster with autoscaling, then delete the cluster after training.

Question 7

A financial services firm is designing a Dataflow pipeline that reads from Pub/Sub and writes enriched transactions to BigQuery. The pipeline must guarantee exactly-once processing semantics for the BigQuery sink, even during pipeline updates and worker restarts. The team plans to use the Apache Beam Java SDK with the BigQueryIO connector. Which combination of configurations should they use?

Accepted Answer

Use BigQueryIO.write() with withMethod(STREAMING_INSERTS) and provide a deterministic insertId derived from the transaction ID. Set withFailedInsertRetryPolicy to RETRY_ALWAYS.. Exactly-once semantics for BigQuery writes in Dataflow require deduplication on the BigQuery side. With STREAMING_INSERTS, BigQuery can use the insertId to discard duplicate rows within a short time window. A deterministic insertId derived from the transaction ID ensures that retries after worker restarts or pipeline updates do not create duplicates. RETRY_ALWAYS preserves data by retrying transient errors, while deduplication prevents duplicates.

Answer

Use BigQueryIO.write() with withMethod(STREAMING_INSERTS) and set withFailedInsertRetryPolicy to RETRY_NEVER. This ensures that failed inserts are not retried, preventing duplicates.

Answer

Use BigQueryIO.write() with withMethod(FILE_LOADS), withTriggeringFrequency set to a fixed interval, and enable withAutoSharding. Rely on the default insertion semantics.

Answer

Use BigQueryIO.write() with withMethod(FILE_LOADS) and set withWriteDisposition(WRITE_TRUNCATE) for each window. This ensures that each window's data replaces the previous contents of the table.

Question 8

A financial services firm runs a batch risk calculation on Dataproc. The job reads from Cloud Storage, processes data in memory, and writes results to BigQuery. The job must complete within a 2-hour window each night, and the cluster must be shut down automatically after completion to minimize cost. You want to orchestrate this with minimal operational overhead. What should you do?

Accepted Answer

Create a Dataproc workflow template that runs the job and deletes the cluster upon completion, and trigger it with a Cloud Scheduler job that calls the Dataproc API.. Dataproc workflow templates natively support running a job on a cluster that is deleted automatically when the workflow completes, satisfying the auto-shutdown and cost requirements. Triggering the template with Cloud Scheduler provides a simple, serverless schedule. Persistent clusters, Composer, or fixed-delay deletion add cost or operational complexity without the same guarantee of completion-based cleanup.

Answer

Submit the job to an ephemeral Dataproc cluster created with the gcloud dataproc clusters create command, and use a Cloud Function triggered by Cloud Scheduler to delete the cluster after a fixed 2-hour delay.

Answer

Use a persistent Dataproc cluster and submit the job via a cron job on the master node, relying on the cluster's idle timeout to shut it down.

Answer

Use Cloud Composer to create a Dataproc cluster, submit the job, and delete the cluster in a DAG, scheduling it daily.

Question 9

A financial services firm runs a Dataflow batch pipeline that joins a 2 TB transaction dataset with a 40 GB customer reference dataset. The reference data changes only once per day and is currently read from a BigQuery table with a side-input transform on every element. Job cost is dominated by repeated BigQuery reads, and the pipeline occasionally hits quota errors. The team wants to minimize cost and quota pressure while keeping the daily refresh. What should the data engineer change?

Accepted Answer

Stage the reference dataset as an Avro file in Cloud Storage, load it into the pipeline with a file-based source, and pass it as a side input refreshed by a scheduled daily export from BigQuery.. Moving the slowly changing reference dataset out of BigQuery into a compact Cloud Storage format and refreshing it once daily eliminates read amplification. A single scheduled export replaces thousands of per-element reads, cutting both cost and quota pressure while preserving the daily update cadence. CoGroupByKey, partition pruning on a side input, and BI Engine all leave the repeated BigQuery access pattern intact.

Answer

Load the reference dataset into a BigQuery table partitioned by ingestion date and read only the latest partition as the side input.

Answer

Enable BigQuery BI Engine on the reference table so side-input queries are served from memory instead of disk.

Answer

Replace the side input with a CoGroupByKey transform that groups transactions and customer records by customer ID before the join.

Question 10

A healthcare analytics team needs to run a series of SQL transformations on data stored in BigQuery. The transformations must run on a schedule, and the team wants to minimize operational overhead by using a fully managed service that integrates with BigQuery and Cloud Logging. They also need to parameterize the SQL queries with runtime values such as the current date. Which Google Cloud service should they use?

Accepted Answer

BigQuery scheduled queries, using the @run_date parameter for runtime values.. BigQuery scheduled queries are a native, fully managed feature that executes SQL on a defined schedule. They support parameterization with system variables like @run_date, which allows dynamic date-based filtering. Integration with Cloud Logging provides visibility. This eliminates the need to manage infrastructure or write code, perfectly matching the requirement for a low-overhead, scheduled SQL transformation solution.

Answer

Cloud Composer with a DAG that uses BigQueryInsertJobOperator to run the SQL queries.

Answer

Dataflow with a pipeline that reads from BigQuery, applies SQL transformations using Beam SQL, and writes back to BigQuery.

Answer

Cloud Scheduler triggering a Cloud Function that calls the BigQuery API to execute the SQL.

Question 11

A startup is building a data lake on Google Cloud. They need to store raw JSON logs in a cost-effective manner and later query them using SQL with minimal transformation. The logs are infrequently accessed but must be retained for 7 years for compliance. Which storage solution should they use?

Accepted Answer

Cloud Storage with Archive storage class and BigQuery external tables.. Archive storage in Cloud Storage is the most cost-effective for long-term retention of infrequently accessed data. BigQuery external tables enable SQL querying of JSON logs directly from Cloud Storage without loading or transformation. This meets the cost, retention, and query requirements. The other options use higher-cost storage or databases not optimized for this use case.

Answer

Cloud Bigtable with a column family for JSON logs and a HBase client for querying.

Answer

Cloud SQL for PostgreSQL with JSONB columns and scheduled exports to Cloud Storage.

Answer

Cloud Storage with Nearline storage class and BigQuery external tables.

Question 12

A financial services company must analyze transaction data that includes customers' full names, account numbers, and home addresses. Regulations require that this personally identifiable information (PII) never be stored in raw form in their BigQuery analytics warehouse. The data engineering team plans to use Cloud Dataflow to read from a Pub/Sub topic and write to BigQuery. Which approach best satisfies the regulatory requirement while keeping the pipeline simple?

Accepted Answer

Apply a ParDo transform in Dataflow that uses the Cloud Data Loss Prevention (DLP) API to de-identify sensitive fields before writing to BigQuery.. De-identifying data in-flight with Cloud DLP inside a Dataflow pipeline prevents raw PII from ever reaching BigQuery storage. The DLP API can inspect and transform fields like names, account numbers, and addresses using techniques such as masking or tokenization. This meets the regulatory requirement at the earliest point, while keeping the pipeline serverless and managed.

Answer

Use BigQuery column-level security to restrict access to the PII columns, and grant permissions only to authorized analysts.

Answer

Enable BigQuery default encryption with a customer-managed encryption key (CMEK) so that the PII columns are encrypted at rest.

Answer

Load the raw data into a BigQuery table, then run a scheduled query that replaces PII columns with hashed values.

Question 13

A financial services firm is designing a data processing system on Google Cloud that must ingest change data capture (CDC) streams from an on-premises PostgreSQL database into BigQuery with sub-minute latency, preserve the ordering of changes per primary key, and apply updates and deletes so that BigQuery reflects the current state of each row. The source database cannot be modified to add triggers. Which two design elements should you include? (Choose two.)

Accepted Answer

Use Datastream to read the PostgreSQL write-ahead log and stream changes into Cloud Storage or BigQuery.. Log-based CDC through Datastream reads the PostgreSQL write-ahead log without touching the source schema, and the staging-plus-merge destination pattern keyed on the primary key is what turns a change stream into correct current-state rows in BigQuery, including deletes. Together they meet the latency, ordering, and mutation requirements.

Answer

Add database triggers to the PostgreSQL source that publish change events to a Pub/Sub topic.

Answer

Use a federated BigQuery external table that queries the on-premises PostgreSQL instance directly at read time.

Answer

Enable BigQuery streaming inserts with insertId deduplication to apply deletes directly to the target table.

Question 14

A logistics company ingests GPS telemetry from delivery vehicles into Pub/Sub. They need to process the stream in Dataflow to calculate real-time estimated arrival times (ETAs). The pipeline must handle late-arriving data up to 2 hours and must emit results every 5 minutes. The team wants to use Apache Beam's windowing and triggering. Which windowing strategy and trigger should they use to meet these requirements?

Accepted Answer

Fixed windows of 5 minutes with an AfterWatermark trigger that includes a late firing with a 2-hour allowed lateness.. The requirement is to emit results every 5 minutes and handle late data up to 2 hours. Fixed windows of 5 minutes provide the regular emission cadence. The AfterWatermark trigger with a late firing ensures that on-time results are emitted when the watermark passes, and late data triggers additional firings. Setting allowed lateness to 2 hours ensures that late data is not dropped prematurely. This combination satisfies both the timing and lateness requirements.

Answer

Sliding windows of 10 minutes every 5 minutes with an early trigger that fires when the watermark passes the end of the window.

Answer

Fixed windows of 5 minutes with a default trigger, allowing late data to be dropped after the watermark passes.

Answer

Session windows with a gap duration of 5 minutes and a repeating trigger every 5 minutes.

Question 15

A retail company runs a batch pipeline in Cloud Dataflow that reads from Cloud Storage and writes to BigQuery. The pipeline uses a GroupByKey operation on a key that is heavily skewed: one customer ID accounts for 40% of all transactions. This causes a single worker to process a massive amount of data, and the job takes hours longer than expected. The team wants to reduce the skew without changing the pipeline's logic or output. What should they do?

Accepted Answer

Apply a hot key fanout by adding a random secondary key, perform the GroupByKey, then remove the secondary key and re-group.. Hot key fanout is the correct technique for mitigating skew in a GroupByKey. By adding a random secondary key, the hot key's records are spread across multiple workers, and a subsequent grouping removes the secondary key to produce the final result. This preserves the pipeline's semantics while dramatically improving parallelism for the skewed key.

Answer

Increase the number of workers and use a larger machine type for the Dataflow job.

Answer

Replace GroupByKey with CombinePerKey using an associative and commutative CombineFn.

Answer

Increase the disk size of the worker VMs to allow more data to be spilled to disk.