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

Question 1

A logistics company uploads nightly shipment manifests as newline-delimited JSON files into a Cloud Storage bucket. Analysts need to query these files with standard SQL immediately after upload, but the schema evolves frequently and the company wants to avoid managing a load job. Which approach should they use?

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

Accepted Answer

Create a BigQuery external table over the Cloud Storage bucket using the BigLake connection with JSON format and schema autodetect.. External tables let BigQuery read files directly from Cloud Storage, so analysts can run standard SQL over the manifests without a load job, and BigLake-backed external tables support schema autodetection for newline-delimited JSON. Because the schema changes often, autodetect combined with in-place querying avoids brittle load pipelines while still exposing the data through the BigQuery SQL surface.

Answer

Mount the bucket in Dataproc and run a Hive external table over the JSON files.

Answer

Use the Storage Transfer Service to copy the JSON into a BigQuery dataset, then query the dataset.

Answer

Load each file into a native BigQuery table with a scheduled query that runs bq load every night.

Question 2

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 3

A media analytics team runs a Dataflow streaming pipeline that reads click events from Pub/Sub and writes aggregates to BigQuery. During peak hours, the pipeline's BigQuery write throughput plateaus and Dataflow logs show repeated quota-related retries on the streaming insert API. The team wants to keep exactly-once semantics and increase sustained write throughput. What should they change?

Accepted Answer

Switch the BigQuery sink to the Storage Write API with exactly-once semantics enabled.. The Storage Write API with exactly-once mode is the intended high-throughput sink for Dataflow to BigQuery and removes the legacy streaming insert quota bottleneck while keeping exactly-once delivery. Increasing workers or autoscaling addresses compute, not the API quota, and batching to Cloud Storage trades away latency and complicates semantics. Replacing the sink is the targeted fix.

Answer

Enable autoscaling on the Dataflow job and raise the maximum worker count to the regional limit.

Answer

Write the aggregates to Cloud Storage as sharded files and schedule a load job every five minutes.

Answer

Increase the number of Dataflow workers to spread the streaming insert load across more threads.

Question 4

A healthcare company needs to ingest HL7 messages from an on-premises system into Google Cloud. The messages arrive continuously and must be processed in near-real-time, with transformations applied before loading into BigQuery. The company wants to use a fully managed service for message ingestion and a serverless data processing service. They also need to ensure that the pipeline can handle bursts of traffic and that the data is encrypted at rest. Which TWO Google Cloud services should they use? (Choose two.)

Accepted Answer

Cloud Dataflow. Pub/Sub provides scalable, managed ingestion of HL7 messages with encryption at rest, and Dataflow offers serverless stream processing with autoscaling and exactly-once semantics. Together, they form a fully managed pipeline that can handle bursts and transform data before loading into BigQuery. Other services are either not serverless or not designed for real-time processing.

Answer

Cloud Data Fusion

Answer

Cloud Dataproc

Answer

Cloud Composer

Question 5

A media company stores 400 TB of compressed JSON clickstream logs in a Cloud Storage bucket. Analysts need to run ad hoc SQL over this data several times a day, and the team wants to avoid the cost and delay of loading it into BigQuery. Which BigQuery capability should you use?

Accepted Answer

Create an external table over the Cloud Storage files using the BigLake connection, and query it directly.. External tables over Cloud Storage, accessed through a BigLake connection, keep the data in its original bucket while exposing it to BigQuery SQL. This removes both the load latency and the duplicate storage cost that the team is trying to avoid. Native loading, bucket-to-bucket copying, and streaming inserts all either duplicate the data or provide no query surface at all.

Answer

Load the JSON files into a BigQuery native table partitioned by ingestion time.

Answer

Use the Storage Transfer Service to copy the bucket into a second bucket in the same region as the dataset.

Answer

Create a BigQuery dataset and use the bq command-line tool to stream the JSON records with the insertAll API.

Question 6

A logistics company streams vehicle telemetry into a Pub/Sub topic. Each message contains a vehicle ID and a timestamp, and the Dataflow pipeline computes per-vehicle distance using a stateful DoFn with a ValueState timer set to fire after 5 minutes of event-time inactivity. During a regional network outage, some vehicles stop sending data for 20 minutes and then resume with correctly ordered timestamps. After the outage, operators notice that some late-arriving records are being dropped before the stateful computation. Which pipeline setting should be adjusted to retain those records for processing?

Accepted Answer

Set an allowed lateness on the windowing strategy that exceeds the 20-minute outage gap.. Late data handling is governed by allowed lateness on the window, not by Pub/Sub delivery settings or the timer domain. When the watermark advances past a window end after a 20-minute gap, records with earlier timestamps are dropped unless the window is kept alive. Setting allowed lateness beyond the outage gap lets the stateful computation include the resumed telemetry in the correct event-time window.

Answer

Switch the pipeline's windowing from event-time to processing-time windows.

Answer

Increase the Pub/Sub subscription acknowledgement deadline so messages remain outstanding longer.

Answer

Change the stateful DoFn timer from event-time to processing-time so it fires only when data resumes.

Question 7

A financial company uses a Dataflow streaming pipeline to read transactions from Pub/Sub and write to BigQuery. They need exactly-once processing semantics for the BigQuery writes and want to avoid duplicates during pipeline updates. Which approach should they use?

Accepted Answer

Use the BigQuery Storage Write API with exactly-once semantics in the Dataflow BigQueryIO connector.. The Storage Write API with exactly-once semantics in BigQueryIO provides deduplication through stream offsets and supports pipeline updates without duplicates. It is the designed solution for exactly-once streaming writes to BigQuery in Dataflow, unlike legacy streaming inserts or file loads.

Answer

Use BigQueryIO with STREAMING_INSERTS and enable insertId-based deduplication.

Answer

Use BigQueryIO with the FILE_LOADS method and trigger frequent load jobs.

Answer

Write to a temporary table and run a MERGE statement periodically to deduplicate.

Question 8

You need to ingest data from a Cloud Storage bucket into BigQuery. The data is in Avro format and you want to minimize the time to insight. Which method should you use?

Accepted Answer

Use the BigQuery Load job with the Avro source format and enable autodetect schema.. The BigQuery Load job natively supports Avro and can automatically detect the schema from the Avro file's embedded schema. This makes it the fastest and simplest way to ingest Avro data from Cloud Storage, minimizing time to insight. Other methods like Dataflow, Streaming API, or Cloud Data Fusion add unnecessary complexity, latency, or cost for a straightforward batch load.

Answer

Use Cloud Data Fusion to read the Avro files and load them into BigQuery.

Answer

Use the BigQuery Streaming API to stream the Avro records from Cloud Storage.

Answer

Use a Dataflow pipeline to read the Avro files and write to BigQuery.

Question 9

A retail analytics team loads point-of-sale transactions into BigQuery every 15 minutes using a load job that appends records. A nightly deduplication job currently removes duplicate transaction IDs, but the team wants duplicates eliminated at ingestion time without adding a separate job. The source system can emit a stable transaction ID for each record. What should you do?

Accepted Answer

Use a MERGE statement in the load pipeline that matches on transaction ID, or write through the Storage Write API with the transaction ID as the primary key.. Deduplication at write time requires the write path itself to enforce identity: either a MERGE keyed on transaction ID or the Storage Write API with a primary key. Physical layout choices such as partitioning and clustering improve query performance but never remove rows, and BigQuery does not enforce unique constraints. A staging-plus-MERGE design works but reintroduces the extra job the team is trying to eliminate.

Answer

Change the load job to write to a staging table, then run a MERGE statement to upsert into the final table.

Answer

Add a unique constraint to the destination table's transaction ID column so duplicate inserts are rejected.

Answer

Partition the table by transaction date and cluster it by transaction ID, then rely on the storage engine to drop duplicates.

Question 10

A data platform team uses Cloud Data Fusion to move data from an on-premises relational database into BigQuery. They need the pipeline to run on a fixed schedule, capture only rows changed since the last successful run, and avoid re-reading the entire source table each night. The source table has an updated_at column that is reliably populated. Which two approaches should they use? (Choose two.)

Accepted Answer

Configure the pipeline to run on a schedule using the Data Fusion scheduler or a Cloud Composer trigger. Scheduling the pipeline and filtering the source query on a persisted updated_at watermark together deliver a recurring job that reads only changed rows. The watermark must be written after a successful load so failures do not advance it prematurely, and the query-based Database source plugin is what makes the filter possible without custom code.

Answer

Use a Replicator pipeline with the default full-table snapshot mode for each run

Answer

Enable the Cloud Data Fusion lineage feature to track which rows were previously loaded

Answer

Use the BigQuery sink plugin with write disposition set to truncate before each nightly load

Question 11

You need to create a Dataflow pipeline that reads from Pub/Sub and writes to BigQuery. The pipeline must handle malformed messages by writing them to a dead-letter table in BigQuery. Which two Apache Beam transforms or patterns should you use to achieve this? (Choose two.)

Accepted Answer

Use a ParDo transform with a side output for failed messages.. To handle malformed messages, you need to detect them and route them separately. A ParDo transform with a side output allows you to process each message and emit malformed ones to a secondary output. Then, you can write that side output to a BigQuery dead-letter table. The combination of a ParDo with side output and a separate BigQueryIO.Write for that output achieves the required dead-letter pattern.

Answer

Write all messages to a BigQuery table and use a view to filter out malformed records.

Answer

Use a ParDo transform that throws an exception for malformed messages, and configure a dead-letter queue in Dataflow.

Answer

Use a Filter transform to separate valid and invalid messages.

Question 12

A media analytics team needs to copy 200 TB of historical log files from an on-premises NFS server into Cloud Storage, then transform them with a Dataflow batch job. The NFS server is reachable only from the corporate network, and the team wants to minimize transfer time and cost. Which approach should they use?

Accepted Answer

Use Storage Transfer Service with an agent pool deployed in the corporate network to transfer directly from the NFS server to Cloud Storage.. Storage Transfer Service with an agent pool is purpose-built for moving large datasets from on-premises sources like NFS to Cloud Storage. Agents run inside the corporate network, so no inbound firewall changes are needed, and the service handles parallelism, retries, and integrity checks. Manual rsync, Transfer Appliance, and single-machine VPN copies are slower or inappropriate at this scale.

Answer

Create a Cloud VPN tunnel and use gcloud storage cp from an on-premises machine to upload the files.

Answer

Mount the NFS share on a Compute Engine VM and run gsutil -m rsync to copy the data to Cloud Storage.

Answer

Use Transfer Appliance to ship the data to Google, then load it into Cloud Storage.

Question 13

A financial firm ingests trade confirmations from a partner's SFTP server into Cloud Storage, then loads them into BigQuery. The partner drops a variable number of files at unpredictable times, and the firm must detect each new file within seconds and trigger a downstream Cloud Run job. The files must not be processed twice if the trigger fires more than once. Which design should you implement?

Accepted Answer

Configure an Eventarc trigger on the bucket's object.finalize events to invoke Cloud Run, and have the function record the object generation in Firestore before processing.. Eventarc on object.finalize gives sub-minute, event-driven detection without polling, and the object generation number serves as a natural idempotency key because it uniquely identifies a version of the object. Writing that key to Firestore before doing the work makes duplicate deliveries harmless. Polling designs miss the latency target, and designs without a deduplication record allow the same file to be processed again on redelivery.

Answer

Schedule a Cloud Scheduler job every minute that lists the bucket and invokes Cloud Run for any object newer than the last run.

Answer

Use a Pub/Sub topic populated by Cloud Storage notifications and subscribe with a pull subscription that Cloud Run polls on a schedule.

Answer

Attach a Cloud Functions (1st gen) trigger to the bucket and let it call Cloud Run synchronously for each new object.

Question 14

A startup uploads JSON log files to a Cloud Storage bucket throughout the day and wants BigQuery to query them within minutes with no ETL code and no data duplication. The files use newline-delimited JSON and the schema is stable. The team wants the lowest-effort option that still lets queries see newly arrived files automatically. Which approach should they use?

Accepted Answer

Create a BigQuery external table over the Cloud Storage bucket using the newline-delimited JSON format. A BigQuery external table reads newline-delimited JSON directly from Cloud Storage, so no data is copied and no pipeline code is needed. Because the table definition is a URI pattern over the bucket, files added later are picked up by subsequent queries, which satisfies the fast-visibility and low-effort requirements.

Answer

Schedule a nightly bq load job to append the JSON files into a native BigQuery table

Answer

Build a Dataflow streaming job that parses the JSON and writes rows to BigQuery

Answer

Use the Storage Write API to stream each uploaded file's contents directly into a BigQuery table

Question 15

A data engineer needs to ingest data from a Cloud Storage bucket into BigQuery. The data is in CSV format and is updated daily with new files. The engineer wants to minimize manual intervention and ensure that new files are automatically loaded into BigQuery. Which Google Cloud service should be used to orchestrate this?

Accepted Answer

Cloud Functions. Cloud Functions provides an event-driven, serverless way to respond to new files in Cloud Storage. By configuring a Cloud Function to trigger on object finalize events, the function can execute a BigQuery load job for each new CSV file. This approach requires no polling, minimal code, and automatically scales. Other services like Cloud Scheduler or Cloud Composer could work but involve more overhead and are less direct for this specific requirement.

Answer

Cloud Scheduler

Answer

Cloud Data Fusion

Answer

Cloud Composer