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

Question 1

An organization wants to ingest on-premises Oracle database changes into BigQuery for real-time analytics with minimal latency. The Oracle database is version 19c and has a high transactional volume. Which Google Cloud service should they use?

Accepted Answer

Datastream. Datastream is the correct service because it is purpose-built for real-time, serverless change data capture (CDC) from Oracle databases (including 19c) to BigQuery. It uses LogMiner to read redo logs with minimal overhead, supporting high transactional volumes and sub-second latency without requiring custom code or manual schema management.

Answer

Pub/Sub

Answer

Dataflow

Answer

Storage Transfer Service

Question 2

A data engineer needs to schedule recurring nightly loads from Amazon S3 to Google Cloud Storage. The data is in CSV format and the volume is approximately 500 GB per night. Which Google Cloud service should they use?

Accepted Answer

Storage Transfer Service. The Storage Transfer Service is designed for online data transfers from external cloud providers like Amazon S3 to Google Cloud Storage. It supports scheduling recurring nightly transfers, handles large volumes (500 GB/night), and automatically retries failed transfers, making it the correct choice for this use case.

Answer

Transfer Appliance

Answer

BigQuery Data Transfer Service

Answer

Datastream

Question 3

A company runs a Dataflow pipeline that reads from Pub/Sub, transforms data, and writes to BigQuery. The pipeline uses classic templates and is deployed in batch mode. They notice that the pipeline does not scale well under high load, causing a backlog in Pub/Sub. Which improvement would BEST address the scaling issue?

Accepted Answer

Switch to Dataflow Prime to enable vertical autoscaling and right-fitting. Dataflow Prime provides vertical autoscaling and right-fitting, automatically adjusting resources to handle variable loads, which is ideal for streaming pipelines with bursty traffic.

Answer

Change the pipeline to use batch mode instead of streaming

Answer

Use a larger machine type for all workers

Answer

Increase the number of workers manually in the pipeline configuration

Question 4

A company needs to load data from a MySQL database into BigQuery daily. The data volume is 10 GB per day and the schema changes occasionally. They want to minimize costs and operational overhead. What is the MOST appropriate approach?

Accepted Answer

Use Datastream to stream changes from MySQL to BigQuery. Datastream is the most appropriate approach because it provides a serverless, change data capture (CDC) solution that continuously replicates changes from MySQL to BigQuery with minimal latency. It handles schema evolution automatically, reducing operational overhead, and its pay-per-GB pricing model minimizes costs for the 10 GB daily volume. This eliminates the need for manual exports or batch loads while ensuring data freshness.

Answer

Use BigQuery Data Transfer Service for MySQL

Answer

Export MySQL data to CSV, upload to GCS, and use BigQuery load jobs

Answer

Use Cloud SQL federated query from BigQuery

Question 5

A media company streams real-time viewer data from Pub/Sub to BigQuery using a Dataflow pipeline. They need to handle occasional malformed messages without losing valid data. Which pattern should they implement?

Accepted Answer

Implement a dead letter sink to store malformed messages for later analysis. Option C is correct because a dead letter sink (e.g., a separate Pub/Sub topic or a BigQuery error table) allows the Dataflow pipeline to route malformed messages out of the main processing path while continuing to process valid data. This pattern ensures no valid data is lost and provides a durable location for later analysis or reprocessing of the malformed records, which is essential for streaming pipelines where data quality issues are intermittent.

Answer

Raise an exception in the pipeline and stop processing

Answer

Use retry logic in the pipeline to reprocess malformed messages indefinitely

Answer

Discard malformed messages and log an error

Question 6

An organization needs to transfer 50 TB of historical data from an on-premises Hadoop cluster to Google Cloud Storage. The network bandwidth is limited to 100 Mbps. Which transfer method is MOST cost-effective and time-efficient?

Accepted Answer

Transfer Appliance. Transfer Appliance is designed for petabyte-scale offline transfers, shipping physical devices to Google for upload, which is much faster than using limited network bandwidth for 50 TB.

Answer

Storage Transfer Service over the network

Answer

BigQuery Data Transfer Service for Hadoop

Answer

gsutil cp with parallel composite uploads

Question 7

A data engineer is designing a Dataflow pipeline in Python that reads from Pub/Sub, applies complex transformations using external libraries, and writes to BigQuery. The pipeline must be deployed as a reusable, version-controlled template that can be easily updated without re-uploading the pipeline code each time. Which approach should they use?

Accepted Answer

Use a Flex Template with a custom Docker image that contains the pipeline code and dependencies. A Flex Template allows you to package both the pipeline code and its dependencies (including external libraries) into a custom Docker image. This image is stored in Artifact Registry and can be version-controlled, enabling updates by simply rebuilding and pushing a new image tag without re-uploading the pipeline code each time. This meets the requirement for a reusable, version-controlled template that can be easily updated.

Answer

Use a Classic Template and store the pipeline code in a Cloud Storage bucket

Answer

Use Cloud Composer to trigger the pipeline each time with updated parameters

Answer

Use Dataflow Prime and deploy the pipeline directly from the Apache Beam SDK

Question 8

A financial services company needs to ingest real-time trade data from multiple sources into BigQuery for immediate fraud detection. The data volume is high (1 million messages per second) and each message must be available for queries within seconds. They are considering the Storage Write API. Which stream mode should they choose to balance data availability and cost?

Accepted Answer

Buffered mode. Buffered mode (option C) is correct because it provides the best balance between data availability and cost for high-volume, real-time fraud detection. In buffered mode, data is written to BigQuery's managed storage within seconds, making it available for queries almost immediately, while the cost is lower than committed mode because buffered mode does not require an additional commit step. This mode is ideal for streaming use cases where latency is critical but cost efficiency is also a priority.

Answer

Legacy streaming inserts

Answer

Pending mode

Answer

Committed mode

Question 9

A team uses dbt on BigQuery to transform data in their data warehouse. They have a large table with nested and repeated fields (arrays and structs). The transformation needs to normalize this data into a star schema. Which dbt feature and BigQuery SQL feature should they use together?

Accepted Answer

dbt models with BigQuery UNNEST and CROSS JOIN. To normalize nested and repeated fields (arrays and structs) into a star schema, you need to flatten the arrays into separate rows. BigQuery's UNNEST operator, when used with CROSS JOIN, expands each array element into its own row, effectively denormalizing the nested structure. dbt models (SQL SELECT statements) are the correct dbt feature to define these transformations as version-controlled, reusable SQL files. Together, they allow you to write a dbt model that uses CROSS JOIN UNNEST to produce dimension and fact tables from a single nested table.

Answer

dbt hooks with BigQuery STRUCT access

Answer

dbt snapshots with BigQuery JSON functions

Answer

dbt seeds with BigQuery ARRAY_AGG

Question 10

A company wants to trigger a Cloud Run service whenever a new file is uploaded to a specific Cloud Storage bucket. Which event-driven solution should they use?

Accepted Answer

Eventarc with Cloud Storage trigger and Cloud Run destination. Eventarc is the recommended service for routing events from Cloud Storage to Cloud Run because it provides a fully managed, event-driven architecture with built-in filtering and retry logic. When a new file is uploaded, Cloud Storage emits a notification that Eventarc captures and delivers directly to the Cloud Run service as an HTTP request, enabling serverless processing without polling or additional infrastructure.

Answer

Cloud Scheduler to periodically poll the bucket

Answer

Cloud Functions triggered by Cloud Storage

Answer

Pub/Sub with a push subscription to Cloud Run

Question 11

A data engineer is using Apache Spark on Dataproc to process a large dataset. They need to perform complex aggregation and transformation with high performance. The dataset has a known schema and they want to take advantage of Catalyst optimizer. Which Spark API should they use?

Accepted Answer

DataFrames. DataFrames provide high-level API with Catalyst optimizer for performance, making them ideal for complex aggregations and transformations on structured data.

Answer

Spark SQL only

Answer

Datasets

Answer

RDDs

Question 12

A company uses Workflows to orchestrate a series of Google Cloud services for data processing. They need to call an external HTTP API as part of the workflow and handle potential failures with retries. Which Workflows feature should they use?

Accepted Answer

Retry policy on the step. Option A is correct because Workflows provides a built-in retry policy that can be configured on individual steps to automatically retry an HTTP call upon transient failures (e.g., 5xx server errors or network timeouts). This allows the workflow to handle external API failures without custom code, using exponential backoff and a maximum retry count.

Answer

Subworkflows

Answer

Parallel steps

Answer

Conditional steps

Question 13

A data engineering team needs to ingest streaming data from an existing Kafka cluster (on-premises) into Google Cloud for real-time analytics. They want to minimize changes to the existing Kafka setup and avoid long-term operational overhead. Which TWO approaches should they consider?

Accepted Answer

Deploy a Kafka Connect cluster on Google Cloud with the Pub/Sub sink connector. Using Kafka Connect with the Pub/Sub connector or setting up a Dataproc cluster running Kafka with mirroring are two ways to bridge on-premises Kafka to GCP with minimal changes.

Answer

Use Storage Transfer Service to copy Kafka logs from on-premises to GCS

Answer

Use Datastream to capture changes from Kafka

Answer

Replace the on-premises Kafka cluster with Google Cloud Pub/Sub

Question 14

A large enterprise is migrating its data warehouse from Teradata to BigQuery. They need to transfer historical data (100 TB) and set up ongoing daily incremental loads. They also need to transform the data using dbt. Which THREE Google Cloud services should they use?

Accepted Answer

BigQuery Data Transfer Service for Teradata. BigQuery Data Transfer Service for Teradata handles both historical and incremental loads, dbt runs on BigQuery for transformations, and Cloud Composer can orchestrate the dbt runs on a schedule.

Answer

Datastream

Answer

Transfer Appliance

Question 15

A data engineer needs to load data from CSV files in Cloud Storage into BigQuery. The CSV files have a header row and some columns contain nested JSON strings. Which TWO methods can they use to load this data into BigQuery?

Accepted Answer

Use the Storage Write API to write rows from a custom application. Option B is correct because the Storage Write API allows a custom application to stream data row-by-row into BigQuery, which can handle CSV files with nested JSON strings by parsing them in the application code before writing. This method supports complex data transformations and is suitable for real-time or near-real-time ingestion.

Answer

Use Datastream to load CSV files

Answer

Create a federated query using an external table

Answer

Use gsutil to copy files into BigQuery