PDE Designing Data Processing Systems Practice Test 3 — 15 Questions

Question 1

Your data engineering team needs to process a continuous stream of clickstream events from a website and update a real-time dashboard showing user activity over the last hour. The pipeline should have minimal operational overhead and support exactly-once processing semantics. Which Google Cloud service should you use?

Accepted Answer

Cloud Dataflow with Apache Beam. Dataflow with Apache Beam is the only service among the options that natively supports exactly-once processing and can handle both streaming and batch pipelines with minimal operational overhead. Dataflow is fully managed, handles autoscaling, and provides exactly-once guarantees for streaming data.

Answer

Cloud Dataproc with Apache Spark Streaming

Answer

Cloud Data Fusion with batch pipelines

Answer

Cloud Pub/Sub Lite with push subscriptions

Question 2

Your company ingests millions of events per second into a Pub/Sub topic. The downstream consumer must process events with minimal latency and high throughput. However, the consumer occasionally falls behind during traffic spikes, and you need to ensure no data loss while minimizing costs. Which subscription type and configuration should you choose?

Accepted Answer

Pull subscription with flow control settings. Pull subscriptions allow the subscriber to control the throughput by batching messages and setting flow control, which is ideal for high-throughput scenarios. Using a pull subscription with exactly-once delivery (if available) or at-least-once combined with idempotent processing ensures no data loss. Push subscriptions have limitations on throughput and are not suitable for millions of events per second.

Answer

Push subscription with a load balancer

Answer

Push subscription with endpoint on Cloud Run

Answer

Pull subscription with exactly-once delivery disabled

Question 3

You are designing a data pipeline that processes streaming events with late-arriving data (up to 2 hours late). The pipeline must compute hourly aggregations and emit results as soon as possible, but must also accurately update results when late data arrives. You want to minimize overall processing cost. Which Dataflow windowing and trigger configuration should you use?

Accepted Answer

Fixed windows of 1 hour with allowed lateness of 2 hours and trigger every 5 minutes (early) and on watermark (late) with accumulating fired panes. Session windows are ideal for capturing bursts of user activity but not for fixed hourly aggregations. The best approach is to use fixed windows with allowed lateness of 2 hours and triggering early every N minutes (e.g., 5 minutes) and also on watermark advancement. This provides early results while allowing late data to update the window. Using accumulating and discarding late panes (or just accumulating) depends on the use case; but here, accumulating fired panes is typical for correctness.

Answer

Global window with triggers every 5 minutes

Answer

Sliding windows of 1 hour with 30-minute offset

Answer

Session windows with 10-minute gap duration

Question 4

You are migrating on-premises Hadoop jobs to Google Cloud. The existing jobs use Spark for ETL and Hive for querying. You want to minimize changes to the existing code and maintain the ability to use Hive queries with the same metastore across multiple clusters. Which service combination should you use?

Accepted Answer

Cloud Dataproc with Cloud Storage and Dataproc Metastore. Dataproc is the managed Hadoop/Spark service that allows you to run existing Spark and Hive code without modification. Dataproc Metastore provides a fully managed Hive metastore that can be shared across multiple Dataproc clusters. Cloud Storage is used instead of HDFS for storing data, but the metastore is the key component.

Answer

Cloud Dataflow with Beam SQL

Answer

Cloud Dataproc with Dataproc on GKE

Answer

Cloud BigQuery with external tables on Cloud Storage

Question 5

You are designing a batch data pipeline that runs daily to ingest data from an on-premises database into BigQuery. The ingestion volume is approximately 50 GB per day. The data must be available in BigQuery by 6 AM each day. The on-premises database supports change data capture (CDC) via logs. Which approach minimizes operational cost and complexity?

Accepted Answer

Use Cloud Dataflow with a JDBC source in batch mode to read CDC logs and write to BigQuery. Using Dataflow with a JDBC source to read CDC logs in batch mode is straightforward and cost-effective for daily 50 GB loads. Dataproc could also work but requires cluster management. Pub/Sub with Dataflow would be more complex and costly for a daily batch. Data Fusion adds a visual layer but is overkill for this simple batch ingestion.

Answer

Use Cloud Dataproc with Spark Streaming to ingest CDC logs

Answer

Use Pub/Sub with a Dataflow streaming pipeline

Answer

Use Cloud Data Fusion with a batch pipeline

Question 6

You have a BigQuery table that is partitioned by ingestion time and clustered on user_id. The table stores event logs and is queried frequently by user_id to analyze user behavior over the last 30 days. Queries are still scanning too many partitions. Which optimization should you apply first?

Accepted Answer

Change the partition column to a DATE column based on event_timestamp and keep clustering on user_id. The query filter on user_id already uses clustering, which prunes blocks within a partition. But the query also filters on a date range, which should leverage partition pruning. If queries still scan many partitions, the most likely cause is that the partition filter is not applied effectively. Using a range filter on the partition column (_PARTITIONDATE) or a column used for partitioning will limit partitions scanned. But the question already says the table is partitioned by ingestion time. The best next step is to ensure the query uses a filter on the partition column. However, among the options, changing the partition type to a specific date column (e.g., event_timestamp) with clustering on user_id could improve if the ingestion time doesn't align with the query time range.

Answer

Create a materialized view that pre-aggregates data by user_id and date

Answer

Remove partitioning and rely solely on clustering

Answer

Add clustering on a second column like event_type

Question 7

Your company uses Cloud Data Fusion to build ETL pipelines. You have a pipeline that reads from Cloud Storage, transforms data using a custom Wrangler recipe, and writes to BigQuery. The pipeline is failing with an error indicating that the Wrangler directive is invalid. You have verified the recipe works in the Cloud Data Fusion Studio. What is the most likely cause of the failure?

Accepted Answer

The Wrangler plugin is not deployed in the Cloud Data Fusion instance. When a pipeline that works in the studio fails at runtime, common issues include differences in environment (e.g., runtime arguments, service account permissions, or plugin versions). But the most likely cause is that the pipeline configuration does not include the necessary plugins or the runtime environment is missing the required artifacts. In Cloud Data Fusion, the studio uses a local or preview environment, while the pipeline runs on a separate Cloud Data Fusion instance with its own set of plugins. If the Wrangler plugin is not deployed to the runtime environment, the directive will fail.

Answer

The pipeline is using a different version of the Wrangler plugin

Answer

The Cloud Storage bucket is in a different region than the Data Fusion instance

Answer

The service account used in the pipeline does not have permissions to write to BigQuery

Question 8

You need to allow a data analyst to run queries on a BigQuery dataset but prevent them from modifying the data or deleting the dataset. Which IAM role should you grant?

Accepted Answer

roles/bigquery.dataViewer. BigQuery Data Viewer grants read-only access to datasets and tables. roles/bigquery.dataViewer allows running queries and viewing metadata but not modifying or deleting data.

Answer

roles/bigquery.dataOwner

Answer

roles/bigquery.jobUser

Answer

roles/bigquery.dataEditor

Question 9

Your team is migrating a legacy batch processing system that uses Apache Spark on-premises. The migration must be completed with minimal code changes and support both batch and streaming in the future. You want to use a fully managed service. Which Google Cloud service is most appropriate?

Accepted Answer

Cloud Dataproc (standard cluster). Cloud Dataflow uses Apache Beam, which is a different programming model than Spark. Dataproc is the managed Spark service that allows you to run existing Spark code with minimal changes, and Dataproc Serverless eliminates cluster management. However, Dataproc Serverless currently only supports batch workloads, not streaming. The question asks for both batch and streaming future support. Dataproc (standard) supports both batch and streaming with Spark Structured Streaming. But it is not fully serverless. Dataproc Serverless is serverless but only batch. So the best answer is Dataproc (standard) with a cluster that can be used for both.

Answer

Cloud Data Fusion

Answer

Cloud Dataflow

Answer

Cloud Dataproc Serverless

Question 10

You are designing a Dataflow pipeline that reads from Pub/Sub, aggregates events into 10-minute windows, and writes the results to BigQuery. The pipeline must reliably handle late-arriving data (up to 1 hour) and prevent duplicate aggregations. Which combination of pipeline options should you use?

Accepted Answer

Use exactly-once processing by setting the pipeline's streaming engine to exactly-once and using a BigQuery sink with exactly-once semantics. To prevent duplicate aggregations, you need exactly-once processing. Dataflow supports exactly-once sinks (like BigQuery) when using the FILE_LOADS method or streaming inserts with exactly-once semantics. Using at-least-once with deduplication in BigQuery is not reliable. Exactly-once semantics require idempotent writes; the recommended approach is to use the BigQuery sink with exactly-once support (by setting the trigger frequency appropriately and using a dedup key). However, the simplest way is to use the Dataflow streaming engine and the BigQuery sink with exactly-once enabled. Among the options, using the BigQuery streaming inserts with exactly-once semantics (available in Dataflow) is correct.

Answer

Use at-least-once processing and rely on BigQuery's automatic deduplication

Answer

Use exactly-once processing and write results to a staging table, then use a scheduled merge query to combine with the main table

Answer

Use at-most-once processing to guarantee no duplicates, and accept data loss

Question 11

You need to create a BigQuery table that stores customer transaction data. The table will be queried frequently by a customer_id column to retrieve recent transactions (last 30 days). Which table design optimizes query performance and cost?

Accepted Answer

Partition by transaction_date and cluster by customer_id. Partitioning by transaction_date allows queries to scan only relevant partitions. Clustering by customer_id sorts data within each partition by customer_id, further reducing the amount of data scanned for queries filtering on customer_id. This combination is best for time-range queries with frequent customer_id filters.

Answer

Partition by customer_id and cluster by transaction_date

Answer

Partition by ingestion_time and cluster by customer_id

Answer

Cluster by transaction_date and customer_id without partitioning

Question 12

Your company is building a real-time anomaly detection system for financial transactions. The system must process streams of transactions and flag anomalies within seconds. The volume is moderate (5000 transactions per second). You want a fully managed solution that integrates with BigQuery for historical analysis. Which service should you use for stream processing?

Accepted Answer

Cloud Dataflow. Cloud Dataflow is a fully managed service ideal for real-time stream processing with low latency. It can read from Pub/Sub, perform transformations (e.g., anomaly detection), and write to BigQuery for historical analysis. Dataproc requires cluster management; Data Fusion is batch-oriented; Pub/Sub alone does not process data.

Answer

Cloud Pub/Sub with push subscriptions

Answer

Cloud Dataproc with Spark Streaming

Answer

Cloud Data Fusion

Question 13

Your organization is designing a data lake on Google Cloud using Cloud Storage. You need to choose a file format for storing raw data that supports schema evolution, is splittable for parallel processing, and is optimized for query performance in BigQuery. Which TWO formats meet these requirements? (Choose 2.)

Accepted Answer

Avro. Both Avro and Parquet support schema evolution (through schemas) and are splittable. Parquet is columnar and highly optimized for BigQuery performance. Avro is row-oriented but also splittable and supports schema evolution. CSV and JSON do not natively support schema evolution and are less performant for BigQuery. ORC is not natively supported by BigQuery.

Answer

CSV

Answer

JSON (newline-delimited)

Answer

ORC

Question 14

Your company runs a Dataflow streaming pipeline that processes user activity from Pub/Sub and writes aggregated results to BigQuery. Lately, the pipeline is experiencing high latency and backlog growth during peak hours. You need to troubleshoot and improve performance. Which THREE actions should you take? (Choose 3.)

Accepted Answer

Change the worker machine type to a higher CPU/memory configuration. Increasing the number of workers allows the pipeline to process more data in parallel. Using streaming engine can improve throughput and reduce latency by offloading state management. Adjusting the worker machine type to use more CPU/memory can help if the processing is compute-intensive. Adding more subscriptions would not help because the pipeline reads from a single subscription. Changing the window size affects business logic but not necessarily performance. Combining these three optimizations addresses common bottlenecks.

Answer

Decrease the window duration to reduce data per window

Answer

Add additional Pub/Sub subscriptions to the same topic

Question 15

Your team is using Cloud Dataprep to clean and transform a dataset. Which TWO features of Cloud Dataprep help you understand data quality issues before running the pipeline? (Choose 2.)

Accepted Answer

Column histograms. Data quality profiling provides statistics and distributions to identify anomalies. Column histograms visualize data distribution and outliers. Scheduling and recipe steps are execution features, not exploratory analysis. Joins are transformations, not profiling.

Answer

Scheduling data quality jobs

Answer

Joining datasets

Answer

Recipe steps