PDE Ingesting and Processing the Data — Timed 100 Questions

Question 1

A data engineer needs to load 10 TB of CSV files from Amazon S3 into Google BigQuery on a daily basis. Which service should they use to automate this transfer?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service is designed to automate data ingestion from external sources, including Amazon S3, into BigQuery on a scheduled basis. It supports CSV and other formats, handles large volumes (10 TB daily), and provides built-in scheduling, monitoring, and retry logic. This makes it the most efficient and managed solution for recurring S3-to-BigQuery loads.

Answer

Dataproc

Answer

Cloud Data Fusion

Answer

Storage Transfer Service

Question 2

Your company uses Kafka for event streaming. You want to run Kafka on Google Cloud with the ability to auto-scale clusters and use managed infrastructure. Which service should you choose?

Accepted Answer

Dataproc. Dataproc is the correct choice because it is a managed Spark and Hadoop service on Google Cloud that supports running Kafka clusters via initialization actions. It allows auto-scaling of worker nodes and integrates with GCP storage and networking, providing the managed infrastructure required for Kafka event streaming. Note that Confluent Cloud is a third-party managed Kafka service, not a GCP-native service, and Cloud Pub/Sub is a messaging service, not a Kafka replacement. Cloud Dataflow is for data processing pipelines, not for running Kafka itself.

Answer

Cloud Pub/Sub

Answer

Confluent Cloud on GCP

Answer

Cloud Dataflow

Question 3

You need to perform a one-time migration of historical data from an on-premises Teradata data warehouse to BigQuery. The data volume is 50 TB and you have a high-speed network connection (10 Gbps). What is the most efficient way to load the data?

Accepted Answer

Use BigQuery Data Transfer Service for Teradata. BigQuery Data Transfer Service for Teradata is designed for this purpose; it can directly connect to Teradata and transfer data to BigQuery. Exporting to CSV then loading via gsutil is possible but less efficient. Transfer Appliance is for offline transfer but you have high-speed network. Dataproc is not needed.

Answer

Export data from Teradata to CSV files, upload to GCS using gsutil, then load into BigQuery.

Answer

Use Dataproc to run a Spark job that reads from Teradata and writes to BigQuery.

Answer

Use Transfer Appliance to ship the data offline.

Question 4

You have a Dataflow pipeline that processes streaming data with high throughput. You notice that the pipeline is experiencing high latency and the workers are underutilized. Which Dataflow feature can automatically optimize resource allocation?

Accepted Answer

Dataflow Prime. Dataflow Prime is Google Cloud's next-generation Dataflow runner that automatically optimizes resource allocation, including right-sizing worker resources and using features like 'Vertical Autoscaling' to adjust memory per worker. It addresses exactly the scenario described — high latency with underutilized workers — by dynamically tuning resources rather than relying on static worker configurations.

Answer

Flex Templates

Answer

Horizontal autoscaling

Answer

Streaming Engine

Question 5

Your organization uses dbt (data build tool) for transformations on BigQuery. You need to run dbt models on a schedule and manage versions. Which Google Cloud service can execute dbt jobs in a serverless manner?

Accepted Answer

Cloud Composer. Cloud Composer is the correct choice because it is a fully managed, serverless (managed) Apache Airflow service on Google Cloud that can schedule and orchestrate dbt jobs, including version management through Airflow's DAGs and connections to BigQuery. It natively supports running dbt commands via BashOperator or KubernetesPodOperator, making it suitable for scheduled dbt transformations on BigQuery. Dataflow is a managed Apache Beam service for stream and batch data processing, not for orchestrating dbt jobs. Cloud Build is a CI/CD service for building and deploying artifacts, not a scheduler for recurring dbt runs. Cloud Run runs containerized applications serverlessly but lacks built-in scheduling and orchestration for dbt workflows.

Answer

Dataflow

Answer

Cloud Build

Answer

Cloud Run

Question 6

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 7

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. The correct option is B: switching to Dataflow Prime enables vertical autoscaling (right-fitting), which dynamically adjusts worker memory and CPU per step to handle skewed or heavy loads better than horizontal-only scaling, directly addressing the poor scaling under high load. Since the pipeline is already in batch mode reading from Pub/Sub, the backlog is caused by insufficient per-worker resources or inefficient scaling, which Prime's right-fitting resolves. Option A is wrong because the pipeline is already in batch mode, so changing to batch is a no-op or contradictory. Option C is wrong because using a larger machine type for all workers is a static, manual change that over-provisions and does not adapt to load. Option D is wrong because manually increasing workers is a manual, horizontal-only fix that does not address per-worker resource bottlenecks and is not automatic or optimal.

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 8

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 9

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 10

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 11

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. Option B is correct because deploying a Kafka Connect cluster on Google Cloud with the Pub/Sub sink connector lets the existing on-premises Kafka cluster stream data into Pub/Sub with minimal changes to Kafka itself, since Kafka Connect is a standard Kafka component and the sink connector handles the integration. Option E is correct because running Kafka on Dataproc and using MirrorMaker replicates topics from the on-premises Kafka cluster to a cloud Kafka cluster, which preserves the existing Kafka setup and enables real-time analytics in Google Cloud with relatively low operational effort. Option A is not appropriate because Storage Transfer Service copies files/objects such as Kafka logs to GCS, not live streaming records, so it does not provide real-time ingestion. Option C is incorrect because Datastream captures change data from databases such as MySQL, PostgreSQL, Oracle, and SQL Server, not from Kafka topics. Option D is incorrect because replacing the on-premises Kafka cluster with Pub/Sub requires significant changes to the existing Kafka setup, which the team wants to avoid.

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 12

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 (B) is correct because it provides a managed, serverless connector that automates both the one-time historical migration of the 100 TB warehouse and the scheduled daily incremental loads from Teradata into BigQuery without custom ETL code. dbt (D) is correct because it is the specified transformation tool that runs SQL-based models against BigQuery to build and test the transformed data marts. Cloud Composer (E) is correct because it is the managed Apache Airflow service used to orchestrate the end-to-end pipeline, scheduling and coordinating the BigQuery Data Transfer Service loads and the dbt transformation jobs. Datastream (A) is not appropriate because it is a change data capture service for databases like MySQL, PostgreSQL, SQL Server, and Oracle, not Teradata data warehouse migrations. Transfer Appliance (C) is not needed because it is a physical appliance for offline bulk transfer of data when network transfer is impractical, whereas the managed Teradata connector handles the 100 TB migration over the network.

Answer

Datastream

Answer

Transfer Appliance

Question 13

You are building a streaming pipeline to ingest real-time clickstream data from a website into BigQuery for immediate analysis. The data must be available in BigQuery within seconds and you need to handle late-arriving data (e.g., browser offline events) that may arrive hours later. Which approach should you use?

Accepted Answer

Use Pub/Sub with Dataflow, writing to BigQuery using the Storage Write API in committed mode.. Pub/Sub provides a scalable, durable ingestion layer for real-time clickstream data, and Dataflow can handle late-arriving data via its built-in watermark and trigger mechanisms. The Storage Write API in committed mode ensures exactly-once semantics and low-latency writes to BigQuery, meeting the sub-second availability requirement while preserving data consistency for delayed events.

Answer

Use Cloud Logging to capture logs and export to BigQuery via a sink.

Answer

Use Pub/Sub with Cloud Functions, writing each event directly via BigQuery legacy streaming inserts.

Answer

Use Datastream to stream clickstream data from Cloud SQL to BigQuery.

Question 14

A company wants to transfer 500 TB of data from an on-premises Hadoop cluster to Google Cloud Storage (GCS) for processing with Dataproc. The on-premises network has a 1 Gbps dedicated link to Google Cloud. The data must be transferred as quickly as possible, minimizing network usage. Which transfer method should they use?

Accepted Answer

Use Transfer Appliance to physically ship the data.. Transfer Appliance is the correct method because the dataset is 500 TB and the network link is only 1 Gbps. At 1 Gbps, the theoretical maximum transfer time is over 46 days, and real-world throughput (due to overhead, congestion, and Hadoop data characteristics) would be even longer. Transfer Appliance physically ships the data, bypassing the network bottleneck entirely and minimizing network usage, which is the stated requirement.

Answer

Use Storage Transfer Service over the 1 Gbps link.

Answer

Use gsutil cp in parallel with multiple threads.

Answer

Use BigQuery Data Transfer Service for Hadoop.

Question 15

You are designing a near-real-time CDC pipeline to replicate changes from an on-premises PostgreSQL database to BigQuery for analytics. The source database has high transaction volume and you must ensure minimal impact on the source. Which Google Cloud service should you use to ingest the change data?

Accepted Answer

Datastream to stream changes to GCS, then load into BigQuery.. Datastream is a serverless change data capture (CDC) and replication service that streams changes from databases like PostgreSQL to destinations such as Cloud Storage. It minimizes impact on the source by reading the database's transaction log (WAL) and can stream to GCS, which can then be loaded into BigQuery. This meets the near-real-time and low-impact requirements.

Answer

Pub/Sub with a custom connector that polls the database every minute.

Answer

Use BigQuery Data Transfer Service for PostgreSQL.

Answer

Use Dataflow with a JDBC IO connector to read from PostgreSQL.

Question 16

Your Dataflow pipeline reads from Pub/Sub, performs transformations, and writes to BigQuery. You notice that the pipeline's autoscaling is not keeping up with sudden spikes in traffic, causing increased lag. The pipeline uses Classic Templates. Which change would most effectively improve autoscaling responsiveness?

Accepted Answer

Enable Dataflow Streaming Engine on the pipeline.. Enabling Dataflow Streaming Engine reduces the overhead of checkpointing and state management by offloading them to the service side, which allows the pipeline to scale more quickly in response to sudden traffic spikes. This directly addresses the autoscaling lag because Streaming Engine decouples compute from state, enabling faster worker adjustments without the bottleneck of persistent disk-based shuffle.

Answer

Switch to Dataflow Prime with Vertical Autoscaling enabled.

Answer

Increase the initial number of workers to handle the spike.

Answer

Use Flex Templates instead of Classic Templates.

Question 17

You are migrating an existing Kafka cluster to Google Cloud using Dataproc. The cluster handles high-throughput streaming data with strict ordering requirements per partition. Which choice of Dataproc configuration is most appropriate?

Accepted Answer

Use Dataproc with non-preemptible workers and persistent SSD storage for brokers.. Kafka brokers in a Dataproc cluster require persistent, non-preemptible workers to maintain data durability and strict ordering per partition. Preemptible workers can be terminated at any time, causing data loss or rebalancing that violates ordering guarantees. Persistent SSD storage provides the low-latency I/O needed for high-throughput Kafka workloads, while non-preemptible instances ensure broker stability and consistent replication.

Answer

Use Dataflow with Kafka IO instead of Dataproc.

Answer

Use Dataproc with local SSDs for better performance, and enable autoscaling.

Answer

Use Dataproc with preemptible workers to reduce cost, and attach standard persistent disks.

Question 18

You are designing a streaming pipeline that ingests events from Pub/Sub, enriches them with a machine learning model, and writes the results to BigQuery. The ML model is deployed on Cloud Run and has a high latency (500ms per request). You need to minimize the impact of slow ML inference on the overall pipeline throughput. Which approach should you take?

Accepted Answer

Use Dataflow to write events to Pub/Sub, then use a separate Dataflow pipeline that batches calls to Cloud Run.. It uses Dataflow to batch events before sending them to Cloud Run, which amortizes the 500ms per-request latency over multiple events, significantly increasing throughput. By writing events to Pub/Sub and then processing them in a separate Dataflow pipeline with batched calls, you decouple the ingestion from the inference and avoid blocking on each individual request.

Answer

Increase the number of Dataflow workers to compensate for the latency.

Answer

Use Cloud Functions to call Cloud Run and write directly to BigQuery.

Answer

Use Dataflow's ParDo with synchronous calls to Cloud Run for each element.

Question 19

Your team has a Dataflow pipeline that reads from BigQuery, transforms data, and writes to GCS. The pipeline is failing with 'Out of Memory' errors on the worker nodes. The input data is large but fits within the total cluster memory. Which configuration change is most likely to resolve the issue without increasing costs significantly?

Accepted Answer

Use a worker machine type with more memory, such as n2-highmem.. Out of Memory errors on Dataflow workers typically occur when the worker's memory is insufficient for the pipeline's operations, such as large shuffles or stateful processing. Using a worker machine type with more memory (e.g., n2-highmem) directly addresses the memory bottleneck without significantly increasing costs if the total cluster memory is already sufficient but per-worker memory is not. This is the most straightforward fix when the input data fits within total cluster memory but individual workers run out.

Answer

Shard the input into smaller reads using a BigQuery query.

Answer

Increase the disk size per worker.

Answer

Enable Dataflow Prime with vertical autoscaling.

Question 20

A company wants to stream real-time user click events from their web application into BigQuery for immediate analysis. Which combination of services is the most scalable and cost-effective for this use case?

Accepted Answer

Pub/Sub to Dataflow to BigQuery. Pub/Sub is the recommended service for ingesting streaming events, and Dataflow can read from Pub/Sub and write to BigQuery using the Storage Write API for high-throughput, exactly-once semantics.

Answer

Cloud Dataproc Spark Streaming to BigQuery

Answer

App Engine pushing logs to BigQuery

Answer

Cloud Functions writing directly to BigQuery