PDE Designing Data Processing Systems Practice Test 3 — 15 Questions

Question 1

A company uses BigQuery with partitioned tables by ingestion time. They notice that queries scanning recent partitions are fast but queries scanning older partitions are slow. What is the most likely cause?

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

Accepted Answer

The older partitions lack clustering metadata because clustering was enabled after data was ingested. Clustering can improve query performance by sorting data within partitions. If older partitions were created before clustering was enabled, they are not clustered, leading to slower scans. Re-clustering only applies to new data unless you manually rewrite older partitions.

Answer

Older partitions are stored on slower storage tiers

Answer

The table has too many partitions, causing high metadata overhead

Answer

Queries are using different SQL syntax for older partitions

Question 2

You need to run a one-time data transformation job on a small CSV file (100 MB) using a visual, code-free interface. Which Google Cloud service is designed for this?

Accepted Answer

Dataprep. Dataprep (Trifacta) is a visual data wrangling tool for exploring and transforming data without code. It's ideal for ad-hoc, small to medium datasets.

Answer

Dataflow

Answer

Cloud Data Fusion

Answer

Dataproc

Question 3

You are designing a Dataflow pipeline that joins two unbounded PCollections from different sources. Which transform should you use?

Accepted Answer

CoGroupByKey. CoGroupByKey performs a key-based join of multiple PCollections. It can handle unbounded streams with appropriate windowing.

Answer

ParDo

Answer

Flatten

Answer

GroupByKey

Question 4

A company wants to build a real-time dashboard for monitoring application logs. The logs are ingested via Pub/Sub and must be processed with low latency (sub-second). You need to enrich the logs with user metadata from Cloud SQL and store the results in BigQuery for analysis. Which TWO services should be used for the stream processing? (Choose two.)

Accepted Answer

Dataflow. Dataflow can read from Pub/Sub, enrich with side inputs from Cloud SQL, and write to BigQuery. Pub/Sub is the ingestion point. The pipeline uses Dataflow for stream processing.

Answer

Dataproc

Answer

Cloud Data Fusion

Answer

Cloud Functions

Question 5

A data pipeline processes sensitive customer data. You need to ensure that only authorised users can query the data in BigQuery, and that the data is encrypted at rest and in transit. Which THREE steps should you take? (Choose three.)

Accepted Answer

Create authorised views in BigQuery to restrict access to sensitive columns. Encryption at rest is enabled by default with CMEK or CSEK. In-transit encryption is default for BigQuery. Authorised views provide fine-grained access control. IAM roles control dataset access.

Answer

Use a Cloud VPN to encrypt data in transit between on-premises and Google Cloud

Answer

Grant the bigquery.dataViewer role at the dataset level to all users

Question 6

You are designing a Dataflow pipeline for processing real-time clickstream data. The pipeline must group events into 30-second windows and handle late data up to 5 minutes. You want to output partial results every 10 seconds for low-latency monitoring. Which THREE configurations should you use? (Choose three.)

Accepted Answer

Use fixed windows of 30 seconds. Fixed windows of 30 seconds create the required windowing. Allowed lateness of 5 minutes ensures late data within that timeframe is included in results. A trigger with early firings every 10 seconds produces the requested partial results for low-latency monitoring. All three configurations are necessary to meet all requirements.

Answer

Use sliding windows of 30 seconds with a 10-second period

Answer

Use a trigger that fires after the end of the window

Question 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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