PDE Practice Exam 52 — 20 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

A data platform team wants to grant a service account the ability to run BigQuery jobs and read data in a specific dataset, while ensuring it cannot create or delete datasets. Which IAM approach satisfies this with least privilege?

Accepted Answer

Grant bigquery.jobs.create at the project level and BigQuery Data Viewer on the specific dataset.. BigQuery separates the permission to run jobs from the permission to read data. Job creation is a project-level capability, so the service account needs bigquery.jobs.create at the project scope. Reading data can be scoped to the specific dataset with BigQuery Data Viewer, which confines access to that dataset and excludes dataset creation or deletion. Combining these two grants achieves the read-and-run requirement with least privilege.

Answer

Grant the BigQuery Admin role on the dataset.

Answer

Grant the BigQuery Data Editor role at the project level.

Answer

Grant the service account the BigQuery Data Viewer role at the project level.

Question 3

You have a BigQuery table 'logs' with a column 'timestamp' of type TIMESTAMP. You need to create a new table that contains only the logs from the last 7 days, partitioned by day on the 'timestamp' column. Which SQL statement should you use?

Accepted Answer

CREATE TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY). To create a partitioned table from a query, you must use the CREATE TABLE ... PARTITION BY clause followed by the partitioning expression and then AS SELECT. Partitioning by DATE(timestamp) is correct for daily partitions. The WHERE clause should filter on the timestamp column using TIMESTAMP_SUB to include all records from the last 7 days, not just those from the last 7 calendar days.

Answer

CREATE TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

Answer

CREATE TABLE new_logs PARTITION BY timestamp AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Answer

CREATE TABLE new_logs AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Question 4

A data engineer is designing a Cloud Storage bucket for a machine learning training pipeline. The pipeline writes many small Parquet files (about 1 MB each) from a Dataflow job, and a downstream training job reads them sequentially. The engineer wants to minimize the number of storage operations and improve read throughput. The bucket uses Standard storage class and has no lifecycle rules. What should the engineer do?

Accepted Answer

Configure the Dataflow job to write larger Parquet files, for example by using a longer window or by grouping writes into 128 MB to 512 MB files.. The core issue is many small files, which increases the number of Cloud Storage operations and reduces read throughput because each file requires separate open, metadata, and read operations. Writing larger Parquet files, such as 128 MB to 512 MB, reduces object count and allows the training job to read contiguous data more efficiently. This is a common Dataflow and Cloud Storage optimization: adjust the pipeline to produce fewer, larger shards. Other options address durability, cost class, or billing, not the operation-count and throughput problem.

Answer

Change the bucket storage class to Nearline and add a lifecycle rule to transition objects after 30 days.

Answer

Enable Requester Pays on the bucket so that the training job pays for egress and operations.

Answer

Enable Object Versioning on the bucket and set a lifecycle rule to delete noncurrent versions after 7 days.

Question 5

A healthcare company needs to process HL7 messages containing sensitive patient data. The messages arrive in Cloud Storage as JSON files. The pipeline must de-identify the data using the Cloud Healthcare API DLP de-identification, then load the results into BigQuery. The pipeline must ensure that no unredacted data is ever written to BigQuery, and that processing is fault-tolerant. The Dataflow pipeline reads from Cloud Storage, calls the DLP API for de-identification, and writes to BigQuery. Which additional configuration ensures that only de-identified data reaches BigQuery?

Accepted Answer

Use a DoFn that calls the DLP API and only emits the de-identified record; then use a BigQueryIO write with a dead-letter queue for failed DLP calls.. The pipeline must ensure that only de-identified data is written to BigQuery. Emitting only de-identified records from the DoFn guarantees that unredacted data never reaches the sink. A dead-letter queue for failed DLP calls maintains fault tolerance by isolating problematic records for later analysis or reprocessing, rather than blocking the pipeline or writing original data. This design meets both privacy and reliability requirements.

Answer

Use a ParDo that calls the DLP API and writes the de-identified data to BigQuery; if the DLP call fails, retry indefinitely until success.

Answer

Use a ParDo transform that calls the DLP API and then writes the original and de-identified data to separate BigQuery tables, with access controls on the original table.

Answer

Use a GroupByKey to batch records, then call the DLP API in a batch request; write both original and de-identified data to BigQuery with column-level encryption.

Question 6

You have a BigQuery table `orders` with columns `order_id` (STRING), `customer_id` (STRING), `order_date` (DATE), and `amount` (NUMERIC). You need to create a view that shows, for each order, the cumulative sum of `amount` for that customer, ordered by `order_date` ascending. Which SQL window function or clause should you use?

Accepted Answer

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). To compute a cumulative sum per customer ordered by date, you need a window function with PARTITION BY customer_id and ORDER BY order_date, and an explicit frame that includes all preceding rows up to the current row. The ROWS frame ensures that each row's sum includes only rows up to that point, avoiding grouping of same-date orders. This yields a true running total for each order.

Answer

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Answer

SUM(amount) OVER (ORDER BY order_date PARTITION BY customer_id)

Answer

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) WITH TIES

Question 7

A data engineer must store JSON documents for a product catalog in Cloud Firestore. Some documents contain nested arrays of product variants that can exceed 1 MiB when serialized. The engineer wants to keep the catalog queryable with strong consistency and low latency. What should the engineer do?

Accepted Answer

Store each variant as a separate document in a subcollection and keep only summary fields in the parent document.. Firestore imposes a hard 1 MiB limit per document, so large nested arrays must be split across documents. Moving variants into a subcollection keeps each document within the limit while preserving strong consistency and low-latency queries, since subcollection documents are first-class documents in the same database.

Answer

Move the entire catalog to Cloud Bigtable and use row keys to model variant relationships.

Answer

Store the variants as a base64-encoded string field inside the parent document.

Answer

Increase the Firestore document size limit by enabling the Enterprise edition of Firestore.

Question 8

A media company ingests clickstream events into Pub/Sub and processes them with a Dataflow streaming pipeline that writes to BigQuery. The pipeline uses a fixed window of five minutes and discards late data. A product manager reports that events arriving more than five minutes after their event timestamp never appear in reports. Which change should you make to capture those events?

Accepted Answer

Configure an allowed lateness on the window and adjust the trigger to emit updated results.. Discarding late data is a consequence of the window's allowed lateness and trigger configuration, not of window duration. Setting an allowed lateness period and a trigger that emits on late arrivals lets the pipeline accept events beyond the five-minute boundary and update the affected windows, so the stragglers appear in reports.

Answer

Switch the pipeline to use the Pub/Sub message publish time as the element timestamp.

Answer

Add a GroupByKey transform before the window to buffer all events.

Answer

Increase the fixed window size to thirty minutes.

Question 9

You are using Cloud Dataflow to stream data from Pub/Sub into BigQuery. The incoming messages are JSON strings with a field `event_time` in ISO 8601 format (e.g., "2024-03-15T14:30:00Z"). You need to write the data to a BigQuery table with a column `event_timestamp` of type TIMESTAMP. Which transformation should you apply in your Dataflow pipeline?

Accepted Answer

Use a ParDo to parse the JSON and convert `event_time` to a BigQuery TIMESTAMP using the appropriate library for your pipeline language (e.g., Instant.parse in Java or datetime.fromisoformat in Python).. The correct approach is to parse the JSON and convert the ISO 8601 string to a native timestamp type within the Dataflow pipeline. This ensures that the data written to BigQuery matches the TIMESTAMP column type. BigQueryIO expects the input PCollection to contain TableRow objects with fields matching the destination schema, so converting the string to a timestamp before writing is necessary.

Answer

Use a SQL query in BigQuery to convert the string to TIMESTAMP after the data is loaded into a staging table.

Answer

Configure BigQueryIO to write the `event_time` field as a STRING and rely on BigQuery's automatic schema detection to convert it to TIMESTAMP.

Answer

In the Dataflow pipeline, use a WithTimestamps transform to assign the event time as the element timestamp, and then write the original string to BigQuery.

Question 10

You need to design a data processing system that ingests streaming data from thousands of IoT devices. The data must be processed in real-time to calculate average temperature per device over 1-minute intervals, and the results should be stored in BigQuery for analysis. You want a serverless solution with minimal management. Which combination of Google Cloud services should you use?

Accepted Answer

Cloud Pub/Sub for ingestion, Cloud Dataflow for processing, and BigQuery for storage.. The scenario requires serverless ingestion, real-time processing with windowing, and analytical storage. Pub/Sub handles ingestion at scale, Dataflow provides serverless stream processing with windowing to compute 1-minute averages, and BigQuery stores results for analysis. This combination is fully managed and requires minimal operational effort.

Answer

Cloud Pub/Sub for ingestion, Cloud Functions for processing, and BigQuery for storage.

Answer

Cloud Pub/Sub for ingestion, Cloud Dataproc for processing, and Cloud Bigtable for storage.

Answer

Cloud IoT Core for ingestion, Cloud Dataproc for processing, and BigQuery for storage.

Question 11

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 12

A startup wants to analyze user clickstream data stored in Cloud Storage in Parquet format. They need to run ad-hoc SQL queries without managing any servers and want to pay only for the queries they run. Which Google Cloud service should they use?

Accepted Answer

BigQuery. BigQuery provides serverless SQL analytics with pay-per-query pricing and can query external Parquet data in Cloud Storage. It requires no infrastructure management, perfectly matching the startup's needs. Other services either require provisioning, are not designed for SQL analytics, or lack external data querying.

Answer

Dataproc

Answer

Cloud Bigtable

Answer

Cloud SQL

Question 13

A retail company stores 500 TB of JSON transaction logs in Cloud Storage. Analysts need to run ad hoc SQL over the data, and the company wants to avoid managing a separate cluster while keeping query cost predictable. The logs are already partitioned into date-based prefixes. What should the data engineer do?

Accepted Answer

Create a BigQuery external table over the Cloud Storage prefixes and use a hive-partitioned layout so queries use partition pruning.. An external table over Cloud Storage gives serverless SQL access to the JSON logs, and hive partitioning on the date prefixes enables pruning so only the relevant date partitions are read. This avoids loading 500 TB, removes cluster management, and keeps cost predictable because bytes billed track the partitions scanned.

Answer

Create a Dataproc cluster with Hive and query the JSON logs through Hive external tables on Cloud Storage.

Answer

Load all 500 TB into a BigQuery native table partitioned by ingestion time and query the native table.

Answer

Use Cloud SQL for PostgreSQL with the foreign data wrapper to read the JSON files from Cloud Storage.

Question 14

A logistics company uploads millions of small JSON files per day to a Cloud Storage bucket and needs to query them with standard SQL immediately, but the analytics team does not want to create BigQuery tables or manage schema changes. The files follow a consistent structure but new fields are added frequently. Which BigQuery capability should they use to query these objects directly from Cloud Storage with the least operational overhead?

Accepted Answer

Create an external table over the Cloud Storage bucket and query it with standard SQL.. An external table defined over Cloud Storage allows standard SQL queries against the JSON objects without loading them, and schema autodetection handles evolving fields. This minimizes operational overhead because there is no pipeline or table to maintain, at the cost of query performance and per-byte scanning charges on the external data.

Answer

Use the BigQuery Storage Write API to stream the JSON files into a table.

Answer

Mount the Cloud Storage bucket as a BigQuery dataset and query the files as tables.

Answer

Load the JSON files into a BigQuery table using a recurring Dataflow batch job.

Question 15

A retail company wants to analyze point-of-sale transaction data stored in Cloud SQL for PostgreSQL. They need to run complex analytical queries joining this data with data in BigQuery. The data changes frequently, and they want near-real-time access without impacting the production Cloud SQL instance. Which approach should they use?

Accepted Answer

Set up a Datastream stream from Cloud SQL to BigQuery. Datastream is a serverless change data capture service that replicates data from Cloud SQL for PostgreSQL to BigQuery in near-real-time without impacting the source database's performance. It reads the write-ahead log and streams changes, enabling up-to-date analytics. Other options either introduce latency, impact production, or require manual intervention.

Answer

Export Cloud SQL data to Cloud Storage daily and load into BigQuery

Answer

Use BigQuery federated queries to query Cloud SQL directly

Answer

Use Cloud Data Fusion to replicate Cloud SQL to BigQuery with a batch pipeline

Question 16

You are designing the deployment process for a Dataflow streaming pipeline that processes financial transactions. The pipeline must be updated without losing in-flight state, such as open windows and timers, and without downtime. Your team uses the Apache Beam Java SDK and deploys from a CI/CD pipeline. Which update strategy should you use?

Accepted Answer

Submit an update with the --update option and a compatible transform change to replace the pipeline in place.. Dataflow update replaces a running streaming pipeline in place while preserving state, provided the transform changes are compatible with the update compatibility rules. This avoids downtime and keeps windowed state intact. Draining, snapshot-based restart, and cancel-redeploy all interrupt processing or discard state, making them unsuitable for stateful, zero-downtime updates.

Answer

Cancel the pipeline and redeploy with a new job name.

Answer

Use a snapshot and then start a new job from the snapshot to preserve state.

Answer

Stop the pipeline with a drain, then start a new pipeline from the same template.

Question 17

A data engineer is designing a BigQuery table to store customer order records. Queries frequently filter by order_date and customer_id, and the dataset grows by about 5 TB per day. The engineer wants to minimize query cost and improve performance for these filtered queries. What should the engineer do?

Accepted Answer

Partition the table by order_date and cluster by customer_id.. Partitioning by order_date enables partition pruning, so queries with date filters scan only relevant partitions, cutting cost and improving speed. Clustering by customer_id sorts data within partitions, making customer-filtered queries more efficient. Together they match the described query patterns and scale well with daily 5 TB loads, unlike materialized views, wildcard tables, or BI Engine, which do not address base-table scan efficiency.

Answer

Create a materialized view that pre-aggregates orders by customer_id and order_date.

Answer

Enable BigQuery BI Engine and reserve memory for the dataset.

Answer

Use a wildcard table over daily sharded tables named orders_YYYYMMDD.

Question 18

A media company streams user interaction events into Pub/Sub and processes them with a Dataflow streaming pipeline that writes to BigQuery. During peak hours, the pipeline's watermark lags significantly behind real time, and late-arriving events are being dropped. The team wants late events to be included in windowed aggregations for up to 30 minutes after the window closes. Which Dataflow configuration should they apply?

Accepted Answer

Set the allowed lateness on the windowing transform to 30 minutes and update the aggregation to emit late panes.. Allowed lateness extends the period during which a window accepts late data after the watermark passes the window end. Setting it to 30 minutes and emitting late panes ensures events arriving within that window update the aggregation instead of being dropped, which is the correct way to handle late-arriving streaming data.

Answer

Switch the pipeline to use processing-time windows instead of event-time windows.

Answer

Increase the number of worker threads and enable autoscaling to reduce the watermark lag.

Answer

Configure the Pub/Sub subscription to retain messages for 30 minutes and replay them.

Question 19

You are designing a streaming pipeline that must handle late-arriving data with a maximum lateness of 10 minutes. You need to ensure that all data is processed exactly once and that results are emitted after the watermark passes the window. Which Apache Beam concept should you use to achieve this?

Accepted Answer

Use fixed windows with allowed lateness set to 10 minutes and a trigger that fires when the watermark passes the end of the window.. Fixed windows with allowed lateness of 10 minutes and a watermark-based trigger ensure that data arriving up to 10 minutes late is included, and results are emitted only after the watermark passes the window end, meaning all on-time data has arrived. This combination provides exactly-once processing semantics when used with a runner that supports it.

Answer

Use sliding windows with a period of 10 minutes and a trigger that fires when the watermark passes the end of the window.

Answer

Use a global window with a trigger that fires every 10 minutes.

Answer

Use session windows with a gap duration of 10 minutes and a trigger that fires on every element.

Question 20

A data engineer must give a Dataproc Serverless for Spark batch workload permission to read objects from a specific Cloud Storage bucket and write to a BigQuery dataset, following least privilege. The workload runs as a custom service account. Which approach should be used?

Accepted Answer

Grant the workload's service account `roles/storage.objectViewer` on the bucket and `roles/bigquery.dataEditor` on the dataset.. Least privilege in Google Cloud means binding predefined or custom roles to the narrowest resource scope that still satisfies the workload. Bucket-level object viewer and dataset-level data editor grant precisely the read and write operations the Spark job requires. Broad project roles, default service accounts, and perimeter controls either overshoot the needed permissions or fail to grant them at all.

Answer

Grant the workload's service account the basic `roles/editor` role on the project so both Cloud Storage and BigQuery calls succeed.

Answer

Enable the Cloud Storage and BigQuery APIs and rely on the default Compute Engine service account attached to the Dataproc Serverless workload.

Answer

Create a VPC Service Controls perimeter around the bucket and dataset and add the workload's service account to the access level.