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

Question 1

A small team runs a nightly Dataflow batch job that reads a Cloud Storage bucket of CSV files and loads them into BigQuery. The bucket contains both the day's new files and all historical files, and the job currently reprocesses everything each night, which has become slow. The team wants each night's job to process only files that arrived since the previous run, without changing the file layout. Which approach should the data engineer use?

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

Accepted Answer

Pass a glob pattern to TextIO that includes only the current date, such as the day's folder, and derive the pattern from a runtime parameter.. Selecting only the files that arrived since the last run is most easily done by deriving a glob pattern from the run date, since TextIO expands globs and runtime parameters let the pattern change per execution. This avoids reprocessing history, requires no bucket reorganization, and does not depend on service features that Dataflow does not provide for batch sources.

Answer

Enable Dataflow's automatic incremental processing flag so the service tracks already-read files across job runs.

Answer

Use FileIO.match with a filter that compares each object's creation time against a timestamp stored from the previous run.

Answer

Write a custom source that lists the bucket and stores processed object names in a Cloud Storage manifest file.

Question 2

A data engineer needs to load a 2 TB CSV dataset from Cloud Storage into BigQuery once per day. The CSV files have a header row and contain a mix of string, integer, and timestamp columns. The engineer wants the simplest reliable load that requires no transformation and minimizes cost. What should the engineer do?

Accepted Answer

Use the bq load command with --source_format=CSV, --skip_leading_rows=1, and --autodetect, loading directly into a native BigQuery table.. A direct batch load with bq load is the canonical way to ingest CSV from Cloud Storage into a native BigQuery table. It is free, requires no pipeline infrastructure, and with skip_leading_rows and autodetect it handles the header and infers column types automatically. For a daily 2 TB file set with no transformation, this is simpler and cheaper than Dataflow, the Data Transfer Service, or an intermediate database.

Answer

Load the CSV files into a Cloud SQL PostgreSQL instance first, then use federated queries from BigQuery to read the data.

Answer

Use the BigQuery Data Transfer Service to schedule a Cloud Storage transfer with CSV as the source format and enable schema auto-detection.

Answer

Create a Dataflow pipeline that reads the CSV files, parses each row, and writes to BigQuery using the Storage Write API.

Question 3

You are building a Dataflow pipeline that reads from a Kafka topic hosted on a Compute Engine VM, processes the data, and writes to BigQuery. The pipeline must handle Kafka messages that contain a timestamp field indicating when the event occurred. You need to ensure that the pipeline's windowing and watermarking are based on this event time, not the processing time. Which approach should you use?

Accepted Answer

Use the KafkaIO source with withTimestampPolicyFactory() and provide a custom TimestampPolicy that extracts the timestamp from the Kafka message value.. KafkaIO provides a withTimestampPolicyFactory() method that allows you to define a custom TimestampPolicy. This policy is used by the source to assign event timestamps to each record, which then drive windowing and watermarking. By implementing a policy that extracts the timestamp from the message payload, you ensure that the pipeline correctly uses event time.

Answer

Use the KafkaIO source with withProcessingTime() to assign timestamps based on the system clock of the Dataflow worker.

Answer

Configure the Kafka consumer to use the LogAppendTime timestamp type, which sets the timestamp when the message is appended to the Kafka log.

Answer

After reading from Kafka, apply a ParDo transform that outputs the message with a timestamp extracted from the value using withTimestamps().

Question 4

A healthcare analytics team ingests HL7 messages into Pub/Sub and processes them with a Dataflow streaming pipeline that writes to BigQuery. Regulators require that each message be represented exactly once in BigQuery even if the pipeline restarts or workers fail, and messages carry a unique message control ID. The team wants the strongest guarantee available without writing custom deduplication logic in BigQuery. Which approach should the data engineer take?

Accepted Answer

Use BigQueryIO with the Storage Write API in exactly-once mode, keyed on the message control ID, with a matching table schema.. Exactly-once delivery into BigQuery is achieved with the Storage Write API's exactly-once mode, which commits each stream atomically and uses the stream's key to prevent duplicates across retries and restarts. Deriving the key from the HL7 message control ID makes the deduplication deterministic and requires no custom cleanup logic in BigQuery, satisfying the regulator's requirement more strongly than best-effort streaming insert deduplication or post-hoc MERGE jobs.

Answer

Use BigQueryIO in STREAMING_INSERTS mode with insertId derived from the message control ID and allowRetryPolicy enabled.

Answer

Write to a BigQuery table partitioned by ingestion time and run a scheduled MERGE statement that removes duplicate message control IDs each hour.

Answer

Write to BigQuery with FILE_LOADS and trigger a load every time the pipeline receives a message, using a unique temporary table per message.

Question 5

You need to ingest data from a Cloud Storage bucket into BigQuery. The data is in JSON format and new files are added every hour. You want to minimize the cost of data ingestion and ensure that the data is available for querying as soon as possible. Which approach should you use?

Accepted Answer

Use a Cloud Function triggered by Cloud Storage object finalize events that starts a BigQuery load job for each new file.. Using a Cloud Function triggered by Cloud Storage object finalize events allows you to start a BigQuery load job immediately when a new file arrives. This provides low latency and is cost-effective because you only pay for the function invocations and BigQuery load jobs (which are free). It also requires minimal maintenance.

Answer

Use the bq command-line tool to run a load job every hour using a cron job on a Compute Engine instance.

Answer

Use the BigQuery Data Transfer Service with a Cloud Storage transfer configuration that runs every hour.

Answer

Use a Cloud Dataflow pipeline that reads from Cloud Storage and writes to BigQuery using BigQueryIO with the Storage Write API.

Question 6

A gaming company uses Pub/Sub to collect player events. They need a Dataflow pipeline that writes to BigQuery and handles late-arriving data by updating previously written records. They want to minimize cost and complexity. Which approach should they use?

Accepted Answer

Write the events to a BigQuery table using streaming inserts, and then periodically run a MERGE statement to deduplicate and update records based on a unique event ID.. Streaming inserts append all events, and a periodic MERGE statement can upsert based on event ID, handling late data efficiently. This approach minimizes cost and complexity by avoiding custom exactly-once upsert logic and leveraging BigQuery's SQL capabilities.

Answer

Use BigQueryIO.write() with STREAMING_INSERTS and enable BigQuery's UPDATE and DELETE DML statements to modify existing rows.

Answer

Use BigQueryIO.write() with the Storage Write API in exactly-once mode and configure the pipeline to use a primary key for upserts.

Answer

Use Dataflow's built-in BigQueryIO with the FILE_LOADS method and set a triggering frequency of 1 minute to allow updates.

Question 7

You need to stream real-time user click events from your application into BigQuery for immediate analysis. The events must be available for query within seconds. Which approach is recommended?

Accepted Answer

Use Pub/Sub to Dataflow to BigQuery with the Storage Write API for high-throughput streaming.. The recommended approach is to use Pub/Sub to Dataflow to BigQuery with the Storage Write API. Dataflow provides a managed stream processing service that can handle high-throughput, low-latency ingestion, and the Storage Write API offers exactly-once semantics and is optimized for streaming inserts into BigQuery. This combination ensures events are available for query within seconds and scales well.

Answer

Use Cloud Data Fusion to ingest streaming data from Pub/Sub into BigQuery.

Answer

Use Cloud Functions to receive events from Pub/Sub and insert them into BigQuery using the legacy streaming API.

Answer

Use Pub/Sub with a BigQuery subscription to directly write events into BigQuery.

Question 8

Your company is migrating an on-premises Hadoop cluster to Google Cloud. You need to transform large datasets using Spark SQL. Which Google Cloud service should you use?

Accepted Answer

Dataproc. Dataproc is the managed Spark and Hadoop service on Google Cloud, purpose-built for running existing Spark SQL workloads with minimal changes. It allows you to spin up a cluster, run your Spark SQL transformations on large datasets stored in Cloud Storage or BigQuery, and then tear it down, making it the direct equivalent of an on-premises Hadoop cluster in the cloud.

Answer

Dataflow

Answer

BigQuery

Answer

Cloud Dataprep

Question 9

A data engineer needs to transfer 500 TB of on-premises data to Google Cloud Storage. The data is stored on NAS devices and the network bandwidth is limited to 100 Mbps. What is the most cost-effective and timely transfer method?

Accepted Answer

Use Transfer Appliance. Transfer Appliance is Google's physical data transfer service that ships a rackable appliance to the customer site, where data is copied locally and the appliance is shipped back to Google for upload to Cloud Storage. For 500 TB over a 100 Mbps link, the network transfer time would be roughly 1.5 years, making online transfer impractical. Transfer Appliance is the most cost-effective and timely option for this volume and bandwidth.

Answer

Use Storage Transfer Service over the internet

Answer

Use a VPN connection and rsync

Answer

Use gsutil cp in parallel

Question 10

You are building a Dataflow pipeline in Python that reads messages from Pub/Sub, enriches them with data from a BigQuery table, and writes the results to BigQuery. The enrichment lookup table is large and changes infrequently. Which approach minimizes cost and latency?

Accepted Answer

Use a side input that reads the BigQuery table periodically and caches it.. Using a side input that periodically reads the BigQuery table and caches it avoids querying BigQuery for every incoming message, which would be prohibitively expensive and high-latency. The side input is refreshed at a configurable interval (e.g., every 10 minutes) via a pipeline option, and the cached data is broadcast to all workers, enabling fast, in-memory lookups without per-element I/O. This approach minimizes cost by reducing BigQuery API calls and minimizes latency by avoiding synchronous queries for each message.

Answer

Use a CoGroupByKey transform to join the incoming stream with a stream from BigQuery.

Answer

Use BigQuery IO to query the table for every incoming message.

Answer

Use a stateful DoFn and store the lookup in state per key.

Question 11

You are designing a Dataflow pipeline to process streaming data. The pipeline may encounter malformed records. You need to handle these errors without failing the entire pipeline and store the bad records for later analysis. What is the best practice?

Accepted Answer

Use a dead letter sink to write malformed records to a separate Pub/Sub topic or GCS location.. A dead letter sink writes malformed records to a separate Pub/Sub topic or GCS location so they can be analyzed later without failing the pipeline, which is the recommended Apache Beam pattern for handling bad records. This preserves the main pipeline's throughput and keeps the bad data available for debugging or reprocessing.

Answer

Catch the exception and log it, then continue processing.

Answer

Write all records to BigQuery using the Storage Write API and handle errors in the write operation.

Answer

Raise an exception in the DoFn to stop the pipeline for manual intervention.

Question 12

You are migrating an on-premises PostgreSQL database to Cloud SQL. You need to continuously replicate changes to BigQuery for real-time analytics with minimal latency. Which service should you use?

Accepted Answer

Datastream. Datastream is Google Cloud's serverless change data capture (CDC) service that continuously replicates changes from sources like PostgreSQL, MySQL, Oracle, and SQL Server into BigQuery or Cloud Storage with low latency. It reads the source's replication log and streams changes without impacting production workloads significantly. This directly matches the requirement for continuous, near-real-time replication into BigQuery.

Answer

Dataflow with JDBC source

Answer

Pub/Sub with a Cloud Function that writes to BigQuery

Answer

Storage Transfer Service

Question 13

You are designing a Dataflow pipeline that needs to exactly-once process events from Pub/Sub and write to BigQuery using the Storage Write API. The pipeline may restart and could reprocess some messages. What setting ensures exactly-once semantics for the output?

Accepted Answer

Use the Storage Write API in committed mode and enable exactly-once semantic in Dataflow. The BigQuery Storage Write API in committed mode, combined with Dataflow's exactly-once processing, provides exactly-once semantics for writes to BigQuery. Committed mode uses a stream-based protocol where records are written and committed atomically, and Dataflow's exactly-once mode ensures that on pipeline restart, records are not duplicated. This is the recommended configuration for exactly-once streaming into BigQuery.

Answer

Use the legacy streaming inserts with insertId for deduplication

Answer

Use at-least-once delivery on Pub/Sub and idempotent writes to BigQuery

Answer

Use the Storage Write API in buffered mode with deduplication logic

Question 14

You need to process a large volume of event data from Cloud Storage, apply complex transformations using Apache Spark, and then load the results into BigQuery. The data arrives in batches every hour. You want to minimize costs by using preemptible VMs. Which service should you use?

Accepted Answer

Dataproc. Dataproc is a managed Apache Spark and Hadoop service that supports preemptible VMs, making it the ideal choice for batch processing with complex transformations using Spark while minimizing costs. It integrates natively with Cloud Storage for input and BigQuery for output, and preemptible VMs can reduce compute costs by up to 80%. The hourly batch pattern aligns with Dataproc's job-based execution model.

Answer

Cloud Composer

Answer

BigQuery

Answer

Dataflow

Question 15

Which TWO statements are true about BigQuery Data Transfer Service? (Choose 2)

Accepted Answer

It supports scheduled transfers from Amazon S3 and Redshift.. Option D is correct because BigQuery Data Transfer Service provides built-in connectors for Amazon S3 and Amazon Redshift, enabling recurring, scheduled (and on-demand) transfers of external data into BigQuery without custom code. Option E is correct because the service also offers first-party Google connectors, including Google Ads, YouTube (Channel/Content Owner reports), and Google Ad Manager, which load those marketing datasets into BigQuery on a schedule. Option A is not correct because the Data Transfer Service is designed for ingestion/movement, not in-flight transformation; transformations are typically done after loading using BigQuery SQL, views, or scheduled queries. Option B is not correct because Cloud SQL is not a native Data Transfer Service source; moving Cloud SQL data into BigQuery generally requires tools such as Datastream, the Cloud SQL federated query/EXTERNAL_QUERY, or export/load pipelines. Option C is not correct because BigQuery Data Transfer Service is available in many more locations than just US and EU, and its regional availability follows BigQuery dataset locations rather than being limited to those two regions.

Answer

It supports data transformation during transfer.

Answer

It can transfer data from Cloud SQL to BigQuery directly.

Answer

It is only available in the US and EU regions.