PDE Designing Data Processing Systems — Timed 15 Questions

Question 1

A data engineer wants to create a BigQuery table that is partitioned by day and clustered by user_id and product_id. Which SQL statement should they use?

Accepted Answer

CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) PARTITION BY event_date CLUSTER BY user_id, product_id;. The correct syntax for creating a partitioned and clustered table in BigQuery requires PARTITION BY followed by CLUSTER BY in that order. Since `event_date` is of type DATE, `PARTITION BY event_date` correctly partitions by day. Option A unnecessarily wraps `event_date` in `DATE()`, which is only needed for TIMESTAMP columns. Option C is a duplicate of B and cannot be correct in a single-select question. Option D incorrectly places `CLUSTER BY` before `PARTITION BY`, which is invalid.

Answer

CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) PARTITION BY DATE(event_date) CLUSTER BY user_id, product_id;

Answer

CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) PARTITION BY event_date CLUSTER BY user_id, product_id;

Answer

CREATE TABLE mydataset.table (user_id INT64, product_id INT64, event_date DATE) CLUSTER BY user_id, product_id PARTITION BY event_date;

Question 2

A Dataflow pipeline processes a high-volume stream of JSON events. The pipeline has a bottleneck where a ParDo transformation performs an external API call for each element, causing high latency. Which strategy would BEST improve throughput without sacrificing correctness?

Accepted Answer

Use a GroupByKey to group elements with the same key and then make one API call per group.. Grouping elements by key with GroupByKey allows batching multiple elements that require the same API call, reducing the number of external calls and improving throughput. Side inputs are not designed for dynamic batching; they provide static data to all elements. Increasing workers or switching to MapElements does not address the core issue of per-element API calls.

Answer

Increase the number of workers in the pipeline.

Answer

Switch from ParDo to MapElements.

Answer

Use a side input to batch elements and make fewer API calls.

Question 3

A company is designing a data pipeline using the lambda architecture. They need to process both real-time streams and batch historical data. Which THREE components are essential for a lambda architecture on Google Cloud?

Accepted Answer

Dataflow for batch processing. The three essential components for a lambda architecture on Google Cloud are: Dataflow for batch processing (batch layer), Cloud Pub/Sub for real-time ingestion (used by both batch and speed layers), and Dataflow for stream processing (speed layer). These form the batch, ingestion, and speed layers. The serving layer (e.g., BigQuery) is also important but not among the three essential components listed here.

Answer

Cloud SQL as the serving database

Answer

Cloud Bigtable for serving

Question 4

A media company processes video metadata using a Dataflow pipeline. They need to join two streaming sources: user activity (Pub/Sub) and video catalog updates (Pub/Sub). Which THREE transforms should be used in the pipeline?

Accepted Answer

ParDo to process each element individually. To join two streaming Pub/Sub sources in Dataflow, you need to: (1) Use ParDo to extract the key from each element (e.g., video_id), (2) Window both PCollections into a common window (e.g., fixed 1-minute) to align the data, and (3) Use CoGroupByKey to join on the common key. GroupByKey separately is not required because CoGroupByKey internally groups by key. Flatten is used to combine PCollections of the same type, which is not applicable here.

Answer

Flatten to combine the two PCollections

Answer

GroupByKey on each PCollection separately before joining

Question 5

A data pipeline ingests streaming events into Pub/Sub and needs to join them with a slowly updating reference table (few thousand rows) from a Cloud Storage CSV file. The pipeline runs on Dataflow with Apache Beam. Which approach is most cost-effective and operationally simple?

Accepted Answer

Use a side input that reads the CSV once and broadcasts it to all workers. Side inputs in Apache Beam allow you to read a bounded dataset (the CSV) once and broadcast it as a read-only map to all workers processing the unbounded stream. For a small reference table (few thousand rows), this is both cost-effective (no external database calls) and operationally simple. Option A would incur high latency and cost by making a BigQuery query per event. Option C introduces Cloud SQL as an external dependency, increasing complexity and latency. Option D is incorrect because CoGroupByKey is used for joining two unbounded PCollections or a bounded and an unbounded PCollection, but reading the CSV into a batch PCollection each window would cause repeated reads and is inefficient.

Answer

Read the CSV in a DoFn and perform a BigQuery query each time an event is processed

Answer

Implement a custom sink that writes events to Cloud SQL and performs a SQL JOIN there

Answer

Use CoGroupByKey to join the stream and batch PCollections by a common key after reading the CSV into a batch PCollection each window

Question 6

A data engineering team is designing a streaming pipeline using Dataflow to process real-time clickstream data from a website. They need to aggregate user session metrics (e.g., number of sessions, average duration) every 5 minutes. The pipeline must handle late-arriving events (up to 2 minutes late) and ensure exactly-once processing semantics. Which TWO of the following should they configure? (Choose two.)

Accepted Answer

Fixed windows of 5 minutes with allowed lateness of 2 minutes.. The correct answers are B and D. For aggregating session metrics every 5 minutes, Fixed windows of 5 minutes with allowed lateness of 2 minutes (option B) align with the desired interval and handle late data. To guarantee exactly-once processing semantics, the Dataflow pipeline must be configured with exactly-once processing mode (option D). Option E (Pub/Sub exactly-once delivery) is a source-level feature that can help prevent duplicates, but the question asks which two configurations to set for the pipeline itself. Option A (Session windows with a 10-minute gap) would merge sessions up to 10 minutes apart, not producing fixed 5-minute aggregates. Option C (Sliding windows with 5-minute size and 1-minute period) would emit results every minute, which is not required.

Answer

Session windows with a 10-minute gap and allowed lateness of 2 minutes.

Answer

Sliding windows of 5 minutes with a 1-minute period and allowed lateness of 2 minutes.

Answer

Pub/Sub subscription with exactly-once delivery.

Question 7

A data engineer needs to design a stream processing pipeline that reads events from Pub/Sub, enriches them with data from a Cloud Storage file, and writes aggregated results to BigQuery. The pipeline must handle late-arriving events up to 1 hour. Which Dataflow feature should be used to manage late data?

Accepted Answer

Watermarks. Watermarks track event time progress and allow specifying allowed lateness. Triggers control when results are emitted, but watermarks handle late data.

Answer

Triggers

Answer

Side inputs

Answer

Windowing

Question 8

A company uses Dataproc to run daily Spark ML jobs. The jobs run for 2 hours each day. The team wants to reduce costs without changing job characteristics. Which strategy is MOST cost-effective?

Accepted Answer

Use preemptible instances for worker nodes. Preemptible VMs are up to 80% cheaper and can handle job interruptions as Spark is fault-tolerant. Single-node is for testing, not production. High-availability is for long-running clusters with HA requirements. Standard nodes are more expensive.

Answer

Use a single-node cluster to eliminate overhead

Answer

Enable high-availability mode to avoid restarts

Answer

Increase the number of standard workers to finish faster

Question 9

A financial services company stream trades into Pub/Sub and processes with Dataflow. The pipeline must ensure exactly-once processing of each trade for regulatory compliance. However, Pub/Sub guarantees at-least-once delivery. Which combination of features should the Dataflow pipeline use to achieve exactly-once semantics?

Accepted Answer

Use Dataflow's exactly-once processing mode and implement idempotent writes in the sink. Dataflow's exactly-once sink combined with idempotent writes ensures exactly-once output. Pub/Sub cannot guarantee exactly-once delivery, but Dataflow can deduplicate using unique IDs. Idempotent writes prevent duplicates even if Dataflow retries.

Answer

Enable Pub/Sub message deduplication and use at-most-once delivery

Answer

Use global windowing and discard late data

Answer

Use Pub/Sub Lite with exactly-once delivery guarantee

Question 10

A data engineer needs to create a BigQuery table that is partitioned by ingestion time and clustered by customer_id and transaction_date. They also want to limit access so that only users from a specific domain can query the table. Which approach should they use?

Accepted Answer

Create the table with partitioning and clustering, then create an authorized view on the table and grant the view access to the domain users. Authorized views allow sharing query results with specific users/groups without giving direct table access. Clustering and partitioning are defined at table creation. IAM roles at dataset level are too broad. Row-level security filters rows but doesn't restrict domain.

Answer

Create the table with partitioning only, then use a materialized view to restrict access

Answer

Create the table without clustering, use row-level security to filter by domain, and grant access to the table

Answer

Create the table with partitioning and clustering, then grant bigquery.dataViewer to the domain via IAM at the dataset level

Question 11

A startup needs a fully managed, serverless Spark service to run occasional data processing jobs without managing clusters. They want to pay only for the resources used during job execution. Which Google Cloud service should they use?

Accepted Answer

Dataproc Serverless. Dataproc Serverless provides a serverless Spark environment where you pay per job execution. Cloud Data Fusion is for visual ETL. Dataproc is managed but not serverless. Dataflow is serverless for Beam, not Spark.

Answer

Dataflow

Answer

Cloud Data Fusion

Answer

Dataproc

Question 12

A company wants to use Cloud Data Fusion to build ETL pipelines. They need to connect to a legacy on-premises database using JDBC and also want to use prebuilt transforms from the Hub. Which two features should they use?

Accepted Answer

CDAP JDBC plugin and the Hub. Cloud Data Fusion uses CDAP plugins for JDBC connections and the Hub provides prebuilt transforms. Plugins are the mechanism; Hub is where they are sourced. Wrangler is for data preparation, not sink. Dataproc is not needed as Data Fusion runs on its own infrastructure.

Answer

Cloud SQL JDBC driver and Cloud Functions

Answer

Dataproc Metastore and Cloud Storage sink

Answer

Wrangler and Dataproc

Question 13

A company uses Pub/Sub with push subscriptions to deliver events to a Cloud Run service. Recently, the service has been returning HTTP 429 (Too Many Requests), causing messages to be retried and eventually sent to the dead letter topic. What is the MOST likely cause?

Accepted Answer

The Cloud Run service needs more instances to handle the incoming request rate. Push subscriptions can be rate limited by the receiving service. Increasing ackDeadline gives more time but doesn't reduce rate. Using pull subscriptions shifts the rate control to the subscriber. Adjusting max delivery attempts only affects how many retries before dead letter, not rate limiting.

Answer

The subscription ackDeadline is set too low, causing messages to be redelivered

Answer

The push endpoint is not acknowledging messages quickly enough, causing a backlog

Answer

The dead letter topic is misconfigured, causing messages to be sent to it prematurely

Question 14

A data engineer needs to process data in a Dataflow pipeline that reads from a Pub/Sub topic. The pipeline must group events into 5-minute windows and compute the average value per key. Which Beam transform should they use after windowing?

Accepted Answer

Combine.perKey. Combine.perKey with an averaging function computes per-key average. GroupByKey groups by key but requires manual combination. ParDo is for element-wise processing. CoGroupByKey joins multiple PCollections.

Answer

ParDo

Answer

GroupByKey

Answer

CoGroupByKey

Question 15

A company uses BigQuery for analytics. They have a table that is queried frequently by date range. To reduce costs, they want to ensure queries only scan the relevant partitions. They also want to improve performance for queries filtering on a specific customer_id. Which table design should they use?

Accepted Answer

Partition by date column and cluster by customer_id. Partitioning by date allows pruning irrelevant partitions; clustering on customer_id orders data within partitions for efficient filtering. Clustering alone doesn't prune partitions. Ingestion-time partitioning is based on arrival time, not logical date.

Answer

Partition by ingestion time and cluster by customer_id

Answer

Use a materialized view that filters by date and customer_id

Answer

Cluster by date column and partition by customer_id