PDE Practice Exam 51 — 20 Questions

Question 1

A financial services firm is designing a data processing system on Google Cloud that must ingest change data capture (CDC) streams from an on-premises PostgreSQL database into BigQuery with sub-minute latency, preserve the ordering of changes per primary key, and apply updates and deletes so that BigQuery reflects the current state of each row. The source database cannot be modified to add triggers. Which two design elements should you include? (Choose two.)

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

Accepted Answer

Use Datastream to read the PostgreSQL write-ahead log and stream changes into Cloud Storage or BigQuery.. Log-based CDC through Datastream reads the PostgreSQL write-ahead log without touching the source schema, and the staging-plus-merge destination pattern keyed on the primary key is what turns a change stream into correct current-state rows in BigQuery, including deletes. Together they meet the latency, ordering, and mutation requirements.

Answer

Add database triggers to the PostgreSQL source that publish change events to a Pub/Sub topic.

Answer

Use a federated BigQuery external table that queries the on-premises PostgreSQL instance directly at read time.

Answer

Enable BigQuery streaming inserts with insertId deduplication to apply deletes directly to the target table.

Question 2

Your company is migrating an on-premises Apache Hadoop cluster to Google Cloud. The cluster runs Hive for SQL-like queries and stores data in HDFS. You want a managed service that minimizes operational overhead while supporting existing Hive scripts. Which Google Cloud service should you choose?

Accepted Answer

Cloud Dataproc. Cloud Dataproc is the correct choice because it is a managed Hadoop and Spark service that supports Hive, HDFS, and other ecosystem tools, allowing you to run existing Hive scripts with minimal changes. It reduces operational overhead by handling cluster provisioning, configuration, and scaling.

Answer

Cloud Dataflow

Answer

Cloud Bigtable

Answer

BigQuery

Question 3

You need to schedule a BigQuery query to run every day at 6:00 AM and write the results to a new table. The query is simple and does not require complex dependencies. You want a low-maintenance, serverless solution with minimal configuration. What should you do?

Accepted Answer

Use BigQuery scheduled queries to run the query daily and write to a destination table.. BigQuery scheduled queries are a built-in, serverless feature that lets you schedule SQL queries to run at specified intervals. They require no infrastructure management and can write results to a destination table. For a simple daily query, this is the most straightforward and low-maintenance solution, avoiding the overhead of Cloud Composer, Cloud Functions, or Dataflow.

Answer

Deploy a Cloud Function triggered by Cloud Scheduler to execute the query via the BigQuery API.

Answer

Create a Dataflow pipeline with a scheduled trigger to run the query and write results.

Answer

Create a Cloud Composer DAG with a BigQueryInsertJobOperator scheduled with a cron expression.

Question 4

A retail company is designing a Dataflow pipeline to process point-of-sale transactions from Cloud Pub/Sub and write to BigQuery. The pipeline must handle late-arriving data up to 24 hours and ensure that all data is written to BigQuery exactly once, even in the event of worker failures. Which two features should the engineer implement to meet these requirements? (Choose two.)

Accepted Answer

Set the allowed lateness to 24 hours on the windowing strategy.. To handle late-arriving data up to 24 hours, the pipeline must set allowed lateness to 24 hours on the windowing strategy. To achieve exactly-once writes to BigQuery, the engineer should use the Storage Write API with a deterministic deduplication key and enable Dataflow's ExactlyOnce mode. Together, these features satisfy both requirements.

Answer

Use the Dataflow ExactlyOnce processing mode and enable the BigQueryIO.Write with FILE_LOADS and a trigger that fires after 24 hours.

Answer

Use BigQueryIO.Write with the UseBeamSchema option and set the write disposition to WRITE_APPEND.

Answer

Configure the pipeline to use the BigQueryIO.Write with STREAMING_INSERTS and set the insertId based on a unique transaction identifier.

Question 5

You need to copy a 3 TB dataset from an on-premises Hadoop Distributed File System (HDFS) cluster to Cloud Storage over a 1 Gbps dedicated interconnect. The data is a one-time historical backfill and must be transferred with integrity verification. You want to minimize operational overhead and avoid writing custom transfer code. Which service should you use?

Accepted Answer

Storage Transfer Service with an HDFS source. Storage Transfer Service is purpose-built for moving large datasets from on-premises sources including HDFS into Cloud Storage. It handles parallelism, retries, integrity verification, and monitoring without custom code, which matches the requirement to minimize operational overhead for a one-time backfill over a dedicated interconnect.

Answer

Cloud Data Fusion with an HDFS source plugin

Answer

gcloud storage cp with parallel composite uploads

Answer

DistCp job writing directly to a Cloud Storage bucket

Question 6

You are ingesting streaming data into BigQuery using the Storage Write API. The data arrives with occasional duplicate records due to at-least-once delivery semantics from the source system. You need to ensure that the final BigQuery table contains no duplicates based on a unique event_id column. Which approach should you use?

Accepted Answer

Use the Storage Write API with application-created streams and specify a unique offset for each record to enable exactly-once semantics.. To achieve exactly-once semantics with the Storage Write API, you must use application-created streams and assign a unique offset to each record. This allows BigQuery to deduplicate records on write, ensuring no duplicates even with retries. The default stream does not provide exactly-once semantics, and BigQuery lacks unique constraints. MERGE is a batch alternative but not ideal for streaming.

Answer

Enable the exactly-once delivery semantics in the Storage Write API by using the default stream and specifying a unique offset for each record.

Answer

Use BigQuery's MERGE statement to upsert records into the table based on event_id, running it periodically to deduplicate.

Answer

Create a unique constraint on the event_id column in the BigQuery table to automatically reject duplicates.

Question 7

A startup is building a data lake on Google Cloud. They need to store raw JSON logs in a cost-effective manner and later query them using SQL with minimal transformation. The logs are infrequently accessed but must be retained for 7 years for compliance. Which storage solution should they use?

Accepted Answer

Cloud Storage with Archive storage class and BigQuery external tables.. Archive storage in Cloud Storage is the most cost-effective for long-term retention of infrequently accessed data. BigQuery external tables enable SQL querying of JSON logs directly from Cloud Storage without loading or transformation. This meets the cost, retention, and query requirements. The other options use higher-cost storage or databases not optimized for this use case.

Answer

Cloud Bigtable with a column family for JSON logs and a HBase client for querying.

Answer

Cloud SQL for PostgreSQL with JSONB columns and scheduled exports to Cloud Storage.

Answer

Cloud Storage with Nearline storage class and BigQuery external tables.

Question 8

A financial services firm stores customer transaction data in a BigQuery table. The table contains a column `customer_id` that is frequently used in WHERE clauses, but the table is not partitioned. Queries filtering on a specific `customer_id` scan the entire table, which is large and costly. The data engineer wants to reduce the bytes scanned for these queries without changing the table's partitioning scheme. What should the engineer do?

Accepted Answer

Create a clustered table on `customer_id` by using a CREATE TABLE ... CLUSTER BY customer_id statement and loading the data into it.. Clustering on `customer_id` organizes the table data so that BigQuery can skip blocks that do not contain the requested customer ID, thereby reducing bytes scanned and improving query performance. Partitioning is not suitable for a high-cardinality identifier like `customer_id`, and materialized views or partition filters do not address the specific need. Clustering is the correct technique for optimizing filters on a non-partitioned table.

Answer

Enable the `require_partition_filter` option on the table and add a partition on a date column.

Answer

Create a materialized view that selects all columns and filters on `customer_id`.

Answer

Add a partition on `customer_id` using a CREATE TABLE ... PARTITION BY customer_id statement.

Question 9

A retail company runs a nightly batch pipeline that loads point-of-sale transactions into BigQuery. The pipeline uses a Cloud Composer (Apache Airflow) DAG with a BigQueryInsertJobOperator task that runs a SQL MERGE statement to upsert yesterday's sales into a large fact_sales table partitioned by transaction_date. The data engineering team notices that the MERGE task occasionally fails with a 'Resources exceeded during query execution' error when the source staging table contains more than 50 million rows. They need to redesign the DAG to reliably load large daily volumes without changing the final table schema or downstream dashboards. What should they do?

Accepted Answer

Replace the single MERGE statement with a multi-statement script that first deletes rows from the target partition for the affected dates and then inserts the staged rows using a BigQueryInsertJobOperator task with writeDisposition set to WRITE_APPEND.. A single large MERGE in BigQuery can hit internal resource limits because it must shuffle and join all source and target rows. Splitting the operation into a partition-scoped DELETE followed by an INSERT with WRITE_APPEND reduces the working set per statement and avoids the expensive shuffle. This pattern is a common, reliable way to upsert large daily batches while keeping the target schema and downstream queries unchanged.

Answer

Schedule the MERGE task to run during off-peak hours and increase the BigQuery slot reservation for the project so that the MERGE has more compute resources available.

Answer

Increase the number of Dataflow workers in the DAG by adding a DataflowOperator task that reads from the staging table and writes to the fact_sales table using a BigQueryIO write with STREAMING_INSERTS.

Answer

Convert the fact_sales table to a clustered table on transaction_date and re-run the original MERGE statement, relying on clustering to reduce the bytes scanned and avoid the resource error.

Question 10

A company stores sensitive data in Cloud Storage and must ensure that data is encrypted at rest with keys that they control and can rotate on demand. They also need to audit key usage and revoke access immediately if a key is compromised. Which Cloud Storage encryption option should they use?

Accepted Answer

Customer-managed encryption keys (CMEK) with Cloud KMS. Customer-managed encryption keys (CMEK) with Cloud KMS allow the company to control and rotate keys, audit key usage through Cloud KMS audit logs, and revoke access by disabling or destroying the key. This satisfies the requirements for customer-controlled encryption, on-demand rotation, and immediate revocation for sensitive Cloud Storage data.

Answer

Google-managed encryption keys

Answer

Customer-supplied encryption keys (CSEK)

Answer

Client-side encryption before uploading to Cloud Storage

Question 11

A financial services company needs to process credit card transactions in real time to detect fraudulent patterns. The pipeline must handle late-arriving data (up to 2 hours) and produce accurate results. They want to use a unified programming model that works for both batch and streaming. Which Google Cloud service should they use?

Accepted Answer

Cloud Dataflow with Apache Beam. Cloud Dataflow with Apache Beam is the correct choice because it offers a unified batch and streaming programming model, supports event-time processing with watermarks and triggers, and can handle late-arriving data accurately through configurable allowed lateness. This makes it well-suited for real-time fraud detection with data arriving up to 2 hours late.

Answer

Cloud Pub/Sub with Cloud Functions

Answer

BigQuery with scheduled queries

Answer

Cloud Dataproc with Spark Streaming

Question 12

A financial services firm needs to design a batch processing system on Google Cloud to analyze large volumes of historical transaction data stored in Cloud Storage. The data is in Parquet format and must be processed using Apache Spark. The firm wants to minimize operational overhead and only pay for the resources used during job execution. Which Google Cloud service should they use?

Accepted Answer

Cloud Dataproc Serverless for Spark. Cloud Dataproc Serverless for Spark provides a fully managed Spark environment without cluster provisioning. It charges only for the resources used during job execution, aligning with the cost and operational requirements. It supports reading Parquet from Cloud Storage and is designed for batch processing workloads, making it the best fit for analyzing historical transaction data with Spark while minimizing overhead.

Answer

Cloud Dataflow with a custom Apache Spark runner

Answer

BigQuery with external tables over Cloud Storage

Answer

Cloud Dataproc with a long-running cluster

Question 13

A data engineering team maintains several Cloud Composer DAGs that load files from Cloud Storage into BigQuery. They want the DAG to start only after a new file has landed in the source bucket, rather than polling on a fixed schedule and often finding nothing. Which Airflow construct should they use to trigger the DAG based on the arrival of the object?

Accepted Answer

A GCSObjectExistenceSensor with a poke interval, followed by the load task.. The requirement is to begin processing only when a specific object appears, which is precisely what a Cloud Storage sensor provides. Sensors defer downstream tasks until their condition is met, so pairing a GCSObjectExistenceSensor with the load task converts the DAG from a blind schedule into a data-arrival trigger while keeping the existing load logic intact.

Answer

A TimeDeltaSensor set to the expected upload window before the load task.

Answer

A BigQueryInsertJobOperator configured to load the file directly from the bucket path.

Answer

An ExternalTaskSensor pointed at a separate file-upload DAG.

Question 14

You manage a Dataflow streaming pipeline that reads from Pub/Sub and writes to BigQuery. The pipeline must be updated to add a new transformation that enriches each message with data from Cloud SQL. You need to minimize downtime and ensure the pipeline continues processing without data loss. What should you do?

Accepted Answer

Use the Dataflow update feature to replace the pipeline with the new code while preserving the pipeline state.. The Dataflow update feature is designed to replace a running pipeline with a new version while preserving state, enabling seamless updates with minimal downtime and no data loss. Stopping and restarting, creating new subscriptions, or using external triggers do not maintain pipeline state and risk data duplication or loss.

Answer

Use Cloud Scheduler to trigger a Cloud Function that updates the pipeline code in place.

Answer

Create a new Pub/Sub subscription, update the pipeline to read from it, and deploy a new pipeline.

Answer

Stop the existing pipeline, update the code, and start a new pipeline from the same subscription.

Question 15

A financial analytics team ingests trade records into BigQuery every minute. Queries filter almost exclusively on trade_date and account_id, and the table grows by roughly 400 GB per day. Analysts report that monthly reports scanning the last 30 days are slow and expensive. The team wants to reduce bytes scanned without changing the ingestion pipeline. What should they do?

Accepted Answer

Partition the table by trade_date and cluster it by account_id.. Partitioning by the trade_date column allows BigQuery to skip partitions outside the query's date range, and clustering by account_id further prunes blocks within each retained partition. This combination directly reduces bytes scanned for the described monthly reports, improving both latency and cost while leaving the ingestion pipeline untouched.

Answer

Enable BigQuery BI Engine reservation for the reporting dataset.

Answer

Convert the table to a BigQuery external table over Cloud Storage Parquet files.

Answer

Create a materialized view that pre-aggregates daily totals per account.

Question 16

A data engineer is designing a BigQuery table to store e-commerce order events. Each row contains an order_id (STRING, high cardinality), customer_id (STRING, high cardinality), order_timestamp (TIMESTAMP), and order_amount (NUMERIC). Queries frequently filter on order_id for lookups and also scan by order_timestamp ranges for daily reporting. The engineer wants to minimize bytes scanned by both query patterns. What should the engineer do?

Accepted Answer

Partition the table by order_timestamp and cluster by order_id.. Partitioning by order_timestamp restricts daily range scans to the relevant date partitions, while clustering by order_id sorts rows within each partition so that point lookups read only matching blocks. This design targets both query patterns and minimizes bytes scanned without adding a separate pipeline or storage layer.

Answer

Partition the table by order_id and cluster by order_timestamp.

Answer

Create a materialized view that pre-aggregates daily order totals and query the view instead.

Answer

Enable BigQuery BI Engine on the table and rely on in-memory caching for all queries.

Question 17

A data engineer is designing a Cloud Storage layout for a data lake that will be queried by BigQuery external tables and by Dataproc jobs. The engineer wants to minimize query cost and improve scan performance across both engines. Which two practices should the engineer follow? (Choose two.)

Accepted Answer

Partition the data in Cloud Storage using a Hive-style key prefix such as dt=YYYY-MM-DD.. Columnar formats let both engines read only referenced columns and compress data efficiently, and Hive-style partitioning lets them prune entire prefixes when filters match the partition key. Together these reduce bytes scanned and listing overhead, which lowers query cost and improves scan performance for BigQuery external tables and Dataproc alike.

Answer

Enable object versioning on the bucket to preserve historical query results.

Answer

Store each row as a separate object to maximize parallelism during scans.

Answer

Use the Standard storage class for all objects to ensure the lowest possible access latency.

Question 18

A data engineer needs to choose a storage service for a new application that requires a schemaless document store with automatic multi-region replication, strong consistency for reads, and native mobile SDK support. The application must scale to millions of users without manual sharding. Which Google Cloud service should the engineer select?

Accepted Answer

Cloud Firestore in Native mode. Firestore in Native mode provides a schemaless document model, automatic multi-region replication, strong consistency for reads, and native mobile SDKs, and it scales horizontally without manual sharding. These characteristics match the application's requirements more closely than the relational or wide-column alternatives.

Answer

Cloud Spanner

Answer

Cloud Bigtable

Answer

Cloud SQL for MySQL

Question 19

Your team is migrating an on-premises Apache Hadoop cluster to Google Cloud. The cluster runs MapReduce jobs that read and write data to HDFS. You want to minimize code changes and operational overhead. Which Google Cloud service should you use to run these jobs?

Accepted Answer

Cloud Dataproc. Cloud Dataproc is designed for running Hadoop and Spark workloads in Google Cloud. It provides a managed cluster with Hadoop, Spark, and Hive, and can use Cloud Storage as a replacement for HDFS via the Cloud Storage connector. Existing MapReduce jobs can often run with little modification, and Dataproc handles cluster management, making it the best choice for migrating on-premises Hadoop workloads with minimal code changes and operational overhead.

Answer

BigQuery

Answer

Cloud Composer

Answer

Cloud Dataflow

Question 20

Your team uses Cloud Dataflow to stream events from Pub/Sub into BigQuery. Some events arrive late, up to 10 minutes after their event timestamp. You need the pipeline to produce correct aggregations per 5-minute window, including late data, while keeping latency low for on-time events. What should you do?

Accepted Answer

Set the windowing strategy to fixed windows of 5 minutes and configure allowed lateness to 10 minutes with a trigger that emits early results and updates them on late arrivals.. To correctly aggregate late events in Dataflow, you must use event-time windows with allowed lateness so state is retained for late arrivals, and triggers to emit early and updated results. Sink configuration and subscription settings do not affect windowing semantics.

Answer

Set the pipeline's default expansion service to use the BigQuery Storage Write API and enable exactly-once semantics for the sink.

Answer

Configure the Pub/Sub subscription to have a 10-minute acknowledgment deadline and increase the Dataflow worker count to handle the backlog.

Answer

Use a global window with a repeating trigger every 5 minutes and set the accumulation mode to DISCARDING.