PDE Ingesting and Processing the Data — 15 Questions

Question 1

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 2

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 a fully managed workflow orchestration service that can schedule and execute dbt jobs. It provides a serverless experience with automatic scaling and built-in monitoring, making it the best choice among the options for running dbt models on a schedule. Dataflow is for data processing pipelines, Cloud Build is for CI/CD, and Cloud Run requires additional components like Cloud Scheduler for job scheduling, whereas Cloud Composer natively supports scheduling and versioning.

Answer

Dataflow

Answer

Cloud Build

Answer

Cloud Run

Question 3

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

Accepted Answer

It supports scheduled transfers from Amazon S3 and Redshift.. BigQuery Data Transfer Service supports scheduled, fully managed transfers from Amazon S3 and Amazon Redshift, enabling automated data ingestion into BigQuery for cross-cloud analytics. Option E is correct because the service natively integrates with Google Ads, YouTube, and Google Ad Manager to pull advertising and performance data directly into BigQuery on a recurring schedule.

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.

Question 4

You are building a Dataflow pipeline that reads from Pub/Sub, applies transformations, and writes to BigQuery. The pipeline must handle late-arriving data and ensure that the windowing and triggering are correct. Which THREE configurations should you consider? (Choose 3)

Accepted Answer

Use the BigQuery Storage Write API with committed mode to ensure exactly-once writes.. The BigQuery Storage Write API with committed mode provides exactly-once write semantics, which is essential for ensuring that late-arriving data processed by the pipeline does not result in duplicate rows in BigQuery. This mode uses stream offsets to track writes, guaranteeing that each record is written exactly once even if the pipeline retries.

Answer

Enable Dataflow Streaming Engine for exactly-once processing.

Answer

Use side inputs to enrich streaming data with static data.

Question 5

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 6

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 scaling batch pipelines under high load.

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 7

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 8

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. 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 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 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 12

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. 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

Question 13

A data engineer needs to transfer 5 PB of historical data from an on-premises Hadoop cluster to Cloud Storage. The network bandwidth is limited to 1 Gbps, and the transfer must complete within 30 days. Which transfer method should they use?

Accepted Answer

Transfer Appliance. The Transfer Appliance is a physical device designed for large-scale data transfers when network bandwidth is insufficient. With 5 PB of data and a 1 Gbps link, the theoretical maximum transfer time is over 500 days (5 PB × 8 bits/byte / 1 Gbps / 86400 seconds/day), far exceeding the 30-day window. The Transfer Appliance bypasses network constraints by shipping data physically to Google Cloud. Option A (gsutil rsync over the internet) is incorrect because it relies on the same 1 Gbps bandwidth, making it impossible to transfer 5 PB within 30 days. Option B (BigQuery Data Transfer Service) is designed for transferring data into BigQuery from cloud sources, not for large on-premises data transfers. Option C (Storage Transfer Service for on-premises) is intended for smaller or incremental transfers over a network, not for a 5 PB initial load within 30 days.

Answer

gsutil rsync over the internet

Answer

BigQuery Data Transfer Service

Answer

Storage Transfer Service for on-premises

Question 14

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

Question 15

A data engineer needs to load 2 TB of Avro files stored in Cloud Storage into BigQuery on a daily schedule. The schema is static and the data should overwrite the existing table each day. What is the most efficient way to accomplish this?

Accepted Answer

Use bq load with --replace flag in a cron job. The bq load command with the --replace flag is the most efficient method because it directly loads Avro files from Cloud Storage into BigQuery in a single, serverless operation without requiring any intermediate processing. Since the schema is static and the data overwrites the existing table daily, a simple cron job invoking bq load --replace is the simplest, fastest, and most cost-effective solution, avoiding the overhead of additional services like Dataflow or Data Transfer Service.

Answer

Create a BigQuery Data Transfer Service from Cloud Storage

Answer

Create a Dataflow pipeline to read Avro files and stream to BigQuery

Answer

Mount Cloud Storage as a filesystem and use SELECT INTO