PDE Ingesting and Processing the Data Practice Test 5 — 15 Questions

Question 1

You need to ingest CSV files from Cloud Storage into BigQuery on a recurring schedule. The files arrive with a header row that must be skipped, and some columns contain quoted values with embedded commas. You want the load to be resilient to schema changes and to minimize cost. Which approach should you use?

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

Accepted Answer

Use a BigQuery load job with skipLeadingRows=1 and allowQuotedNewlines=false, and enable schema update options. A load job with skipLeadingRows=1 correctly ignores the header, and allowQuotedNewlines=false ensures quoted fields with embedded commas are parsed as a single field. Schema update options handle new columns automatically, and batch load jobs are more cost-effective than streaming for scheduled ingestion.

Answer

Use an external table over the Cloud Storage files and query with a header-skipping option

Answer

Use a BigQuery load job with skipLeadingRows=0 and allowQuotedNewlines=true

Answer

Use the Storage Write API with a custom parser that skips the header and splits on commas

Question 2

Your team runs a nightly Dataflow batch pipeline that reads approximately 4 TB of newline-delimited JSON from a Cloud Storage bucket, performs a small transformation, and writes the results to BigQuery. The transformation is stateless and CPU-light, and cost is the primary concern. The pipeline currently uses the default worker machine type. Which change should you make to reduce cost while maintaining acceptable performance?

Accepted Answer

Specify a smaller machine type such as n1-standard-2 and increase the number of workers with --numWorkers and --maxNumWorkers.. The workload is stateless and CPU-light, so the default n1-standard-4 workers are oversized. Choosing n1-standard-2 and raising the worker count keeps parallelism while lowering cost per vCPU. Dataflow Shuffle is already on by default, autoscaling only changes worker count, and Streaming Engine targets streaming jobs, so none of those address the real source of overspend.

Answer

Use the Dataflow Shuffle service by setting --experiments=use_dataflow_shuffle instead of relying on Persistent Disk-based shuffle.

Answer

Switch the pipeline to use Streaming Engine and set --enable_streaming_engine=true.

Answer

Enable autoscaling by setting --autoscalingAlgorithm=THROUGHPUT_BASED and leave the machine type unchanged.

Question 3

Your company needs to ingest data from a Kafka cluster into BigQuery. The data volume is high and you require minimal latency and exactly-once delivery. You decide to use the BigQuery Storage Write API with the Kafka Connect BigQuery connector. Which configuration should you use to achieve exactly-once semantics?

Accepted Answer

Use the Storage Write API in exactly-once mode and configure the connector to use a deterministic stream name per partition.. The Kafka Connect BigQuery connector achieves exactly-once semantics by leveraging the Storage Write API's exactly-once mode. It creates a stream per Kafka partition and commits offsets to BigQuery after successful writes. Using a deterministic stream name per partition ensures that after a restart, the connector can resume from the last committed offset, preventing duplicates. This configuration provides end-to-end exactly-once delivery from Kafka to BigQuery.

Answer

Use the Storage Write API in at-least-once mode and rely on BigQuery's MERGE statements to deduplicate.

Answer

Set the connector's write mode to exactly-once and enable the connector's internal deduplication.

Answer

Use the Storage Write API in exactly-once mode and set the connector's offset storage to Kafka's internal topic.

Question 4

A retail analytics team wants to ingest Avro-formatted files from Cloud Storage into BigQuery on a schedule, with automatic schema updates when new fields appear in the source files. They want to minimize custom code and operational overhead. Which TWO BigQuery features or services should they use to accomplish this? (Choose two.)

Accepted Answer

BigQuery schema autodetection during the load job.. BigQuery Data Transfer Service provides managed, scheduled ingestion from Cloud Storage, and schema autodetection allows the load to adapt to new fields in Avro files. Together they meet the requirements for low-code, scheduled ingestion with automatic schema updates, avoiding custom pipelines or manual table management.

Answer

A Cloud Function triggered by Cloud Storage object finalize events.

Answer

A Cloud Composer DAG that calls the BigQuery API to create tables.

Answer

The BigQuery Storage Write API with a streaming insert.

Question 5

A media analytics team ingests clickstream events into Pub/Sub and needs a Dataflow streaming pipeline that aggregates per-session metrics for exactly one hour after each session's first event. Events for a session can arrive up to 90 minutes late due to mobile client buffering, and the team wants late data included in the aggregate rather than dropped. Which windowing and trigger configuration should you use?

Accepted Answer

Session windows with a one-hour gap duration and allowed lateness of 90 minutes. Session windows with a one-hour gap correctly model a session that ends one hour after its last event and merges bursts that belong together. Allowed lateness of 90 minutes keeps the window state alive long enough for the buffered mobile events to be included, and each late arrival triggers an updated aggregate rather than being discarded.

Answer

Global window with a processing-time trigger that fires every hour

Answer

Sliding windows of one hour with a one-minute period and discarding mode

Answer

Fixed windows of one hour with the default trigger and allowed lateness set to zero

Question 6

A media analytics company ingests clickstream events into Pub/Sub at roughly 40,000 messages per second across 200 subscription backlogs. A Dataflow streaming pipeline reads from the subscription, performs sessionization with a 30-minute sliding window, and writes aggregated session records to BigQuery. The pipeline currently uses the default Pub/Sub subscription settings and the team observes steady, growing backlog with no data loss. They want the pipeline to keep up with peak load while minimizing duplicate processing after worker restarts. Which change should they make?

Accepted Answer

Configure the subscription's flow control and set the Dataflow pipeline's maxNumWorkers and autoscaling algorithm to scale out with the backlog.. Peak load at 40,000 messages per second requires the pipeline to add capacity as backlog grows, which Dataflow horizontal autoscaling provides when driven by backlog and CPU signals. Flow control on the subscription protects workers from being overwhelmed and keeps acknowledgement behavior predictable. Exactly-once delivery and longer retention address duplication and replay, not throughput, and batch mode cannot preserve sliding-window session state across continuous ingestion.

Answer

Increase the Pub/Sub subscription's message retention duration to 7 days so the pipeline can replay backlog during peak load.

Answer

Enable exactly-once delivery on the Pub/Sub subscription and rely on it to remove the need for Dataflow autoscaling.

Answer

Switch the Dataflow pipeline from streaming mode to batch mode and schedule it to run every 15 minutes against the subscription.

Question 7

A logistics company streams GPS pings from 40,000 delivery vehicles into a Pub/Sub topic. Individual pings are only useful when analyzed together with the full route for each vehicle, so the analytics team must guarantee that all pings for a given vehicle are processed by the same worker, in the order they were published. Which approach should you take in the Dataflow pipeline?

Accepted Answer

Publish each ping with an ordering key set to the vehicle ID and enable message ordering on the subscription.. Ordering keys on the publish side, combined with the ordering-enabled subscription, are the only mechanism here that binds messages of the same logical entity to a single ordered delivery stream. Because the ordering key is the vehicle ID, Dataflow receives each vehicle's pings sequentially, and the pipeline's keyed state keeps them on one worker. Retention, worker scaling, and file-based ingestion all change throughput or storage, not per-key ordering.

Answer

Increase the number of threads per worker and use a larger worker machine type.

Answer

Set the topic's message retention duration to 7 days and consume with a pull subscription.

Answer

Switch from Pub/Sub to a Cloud Storage bucket and have Dataflow poll the bucket with a TextIO watch transform.

Question 8

A startup needs to ingest application logs from hundreds of Compute Engine instances into BigQuery for ad hoc analysis. They want a fully managed, serverless pipeline that requires no code and can handle variable log volume. Which Google Cloud service should they use?

Accepted Answer

Cloud Logging with a BigQuery sink.. A Cloud Logging sink to BigQuery is the managed, serverless way to route Compute Engine logs into BigQuery without writing code. It scales with log volume and is the intended integration for log analytics, making it the correct choice for a no-code pipeline.

Answer

Cloud Dataproc with a Spark job that reads logs from Cloud Storage.

Answer

A Dataflow streaming pipeline that reads from Pub/Sub and writes to BigQuery.

Answer

BigQuery Data Transfer Service with a Cloud Storage transfer.

Question 9

Your team is building a streaming pipeline that ingests JSON events from Pub/Sub, performs a stateful per-session aggregation over a 10-minute window, and writes results to BigQuery. During load testing, you observe that the Dataflow job's throughput is lower than expected, and watermark lag keeps growing. You need to improve throughput while preserving correctness of the session aggregations. What should you do?

Accepted Answer

Increase the maximum number of workers and ensure the aggregation key has high cardinality so work is spread across workers.. A stateful session aggregation is only as parallel as its key space. When a small number of keys dominate traffic, Dataflow workers handling those keys become hot spots, state grows on a few workers, and watermarks stall because the runner cannot advance past unprocessed records. Raising the maximum worker count and spreading the aggregation across many keys lets the runner distribute state and processing, restoring throughput without altering the 10-minute session semantics or weakening correctness.

Answer

Enable the streaming engine and disable auto-scaling so the worker pool stays at a fixed size during the test.

Answer

Increase the number of Pub/Sub subscriptions attached to the topic so Dataflow can pull more messages in parallel.

Answer

Set the pipeline's windowing strategy to a shorter fixed window and rely on the default trigger to emit results earlier.

Question 10

You are building a Dataflow pipeline that reads from Cloud Storage and writes to BigQuery. The input files are large and the pipeline must handle malformed records without failing. You need to ensure that malformed records are logged and written to a dead-letter table for later analysis. Which TWO approaches should you use? (Choose two.)

Accepted Answer

Use a ParDo transform that writes malformed records directly to a BigQuery dead-letter table using the BigQueryIO write transform within the DoFn.. The correct approaches are to use a ParDo with a DoFn that catches exceptions and outputs malformed records to a side output, and to then write those side outputs to a dead-letter table using BigQueryIO. This pattern allows the pipeline to continue processing valid data while capturing errors. The other options either rely on non-existent features, drop malformed records, or are impractical. Using side outputs is the standard Apache Beam pattern for dead-letter queues.

Answer

Apply a Filter transform to remove malformed records before writing to BigQuery, and log them using a separate pipeline.

Answer

Use the Dataflow runner's automatic error handling feature to redirect failed records to a dead-letter queue.

Answer

Configure the pipeline to use the BigQueryIO write transform with a dead-letter table option.

Question 11

A retail chain loads point-of-sale CSV files into Cloud Storage and needs BigQuery tables refreshed every 15 minutes. The files are appended continuously, and duplicate rows appear when upstream retries occur. The team wants minimal operational overhead and automatic deduplication of retried files. Which loading approach should you choose?

Accepted Answer

BigQuery load jobs using the Storage Write API with exactly-once semantics keyed by file name. The Storage Write API's exactly-once semantics let BigQuery discard a replayed write when the same stream offset is reused, so retried files do not create duplicate rows. Pairing it with a stable offset derived from file identity keeps the pipeline idempotent and requires no cluster management, meeting the low-overhead and deduplication goals together.

Answer

Cloud Composer DAG that calls the bq load command every 15 minutes for each new file

Answer

A Dataproc job that runs every 15 minutes and loads the new files with the Spark BigQuery connector

Answer

BigQuery Data Transfer Service with a Cloud Storage transfer configured for every 15 minutes

Question 12

You are designing a Dataflow pipeline that reads from Cloud Pub/Sub and writes to BigQuery using the BigQueryIO write transform. The pipeline must guarantee exactly-once delivery to BigQuery. Which write method should you use?

Accepted Answer

BigQueryIO.Write.withMethod(Method.STORAGE_WRITE_API). The Storage Write API is designed to provide exactly-once semantics for Dataflow writes to BigQuery. It supports at-least-once and exactly-once modes, and when configured with the default stream, it ensures no duplicates. Other methods either provide at-least-once (STREAMING_INSERTS) or require additional configuration for exactly-once (FILE_LOADS), but the Storage Write API is the recommended and most straightforward approach for exactly-once delivery.

Answer

BigQueryIO.Write.withMethod(Method.FILE_LOADS)

Answer

BigQueryIO.Write.withMethod(Method.FILE_LOADS).withTriggeringFrequency(Duration.standardMinutes(1))

Answer

BigQueryIO.Write.withMethod(Method.STREAMING_INSERTS)

Question 13

A logistics company ingests GPS pings into Pub/Sub and processes them with a Dataflow streaming pipeline that writes to BigQuery. Downstream dashboards show occasional duplicate rows for the same ping ID. The pipeline uses a group-by-key with a fixed window and writes with BigQueryIO using default settings. Which change most directly eliminates the duplicates observed downstream?

Accepted Answer

Ensure the destination BigQuery table has a primary key defined and use BigQueryIO with the Storage Write API in exactly-once mode.. Duplicate rows in a streaming write typically come from at-least-once delivery and retries. The Storage Write API provides exactly-once semantics when the target table has a primary key, so defining ping ID as the primary key and using the exactly-once write mode lets BigQuery deduplicate and upsert. Streaming Engine, FILE_LOADS, and plain WRITE_APPEND do not provide that guarantee.

Answer

Set --enable_streaming_engine=true on the Dataflow pipeline and increase the number of workers.

Answer

Configure BigQueryIO with withMethod(BIGQUERY_STORAGE_API) and withWriteDisposition(WRITE_APPEND), using the Storage Write API.

Answer

Use BigQueryIO with withMethod(FILE_LOADS) and set withTriggeringFrequency to a one-minute interval.

Question 14

You are building a Dataflow pipeline that reads from Cloud Pub/Sub and writes to BigQuery. The pipeline must handle late-arriving data and emit results using event-time semantics. You need to ensure that the pipeline correctly processes data with timestamps that may arrive out of order. Which of the following should you implement?

Accepted Answer

Apply a windowing strategy that uses event-time timestamps, such as fixed windows, and set allowed lateness to accommodate late data.. The correct approach is to use event-time timestamps with a windowing strategy like fixed windows, combined with allowed lateness. This ensures that events are grouped by their actual occurrence time and that late data is still processed. Watermarks and triggers manage when results are emitted, balancing completeness and latency. Other options either ignore event time or use inappropriate windowing, leading to incorrect results.

Answer

Use a global window with a trigger that fires when the watermark passes the end of the window, and set allowed lateness to a reasonable duration.

Answer

Configure the pipeline to use processing-time timestamps and apply a fixed window of 1 minute.

Answer

Enable exactly-once processing and use a session window with a gap duration of 5 minutes.

Question 15

A healthcare analytics team must load a large set of CSV files from Cloud Storage into BigQuery every night. The files contain a header row, use comma delimiters, and the load must be resilient to a few malformed records without failing the entire job. They also want the load to be as efficient as possible for large files. (Choose two.)

Accepted Answer

Use the bq load command with --skip_leading_rows=1 and --max_bad_records set to a small value.. A BigQuery load job reads CSV files directly from Cloud Storage with high throughput and parallel parsing. Specifying skip_leading_rows handles the header, max_bad_records tolerates a few malformed rows without failing the job, and declaring CSV format with quoted-newline support ensures records with embedded line breaks parse correctly. Together these options satisfy efficiency and resilience.

Answer

Load the files with a Dataflow batch pipeline using TextIO and a BigQuery sink.

Answer

Use the BigQuery Storage Write API with the default stream to insert each row individually.

Answer

Create the table with a schema that uses the STRING type for every column to avoid parse errors.