Google Professional Data Engineer PDE Questions 526–600 | Page 8/10

526

MCQmedium

A company uses Looker Studio to create dashboards from BigQuery data. They notice that dashboard queries take several seconds to load. They want to improve performance without changing the underlying data or creating materialized views. Which option should they use?

A.Enable BigQuery BI Engine for the project

B.Increase the number of BigQuery slots

C.Switch to Looker instead of Looker Studio

D.Replicate the data to Cloud SQL for faster queries

AnswerA

BI Engine is an in-memory analysis layer that caches BigQuery data and accelerates dashboard queries, requiring no changes to the underlying tables or materialised views. Enabling it for the project directly reduces the several-second load times observed in Looker Studio.

Why this answer

BigQuery BI Engine is an in-memory analysis service that accelerates queries from Looker Studio (and other BI tools) by caching data in memory, significantly reducing latency. Replicating data to Cloud SQL would add complexity and may not handle the volume. Using Looker instead of Looker Studio doesn't inherently speed up queries.

Increasing BigQuery slots would help but is more expensive and not as targeted for BI tools.

Full explanation →

527

MCQmedium

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?

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

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

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

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

AnswerB

Allowed lateness tells Dataflow to keep window state for 10 minutes after the watermark passes, so late events are included. Using early triggers provides low-latency partial results, and late triggers update them. This satisfies both correctness for late data and low latency for on-time events.

Why this answer

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.

Exam trap

The trap here is confusing Pub/Sub acknowledgment deadlines or sink exactly-once settings with Dataflow windowing semantics, which are separate concerns.

Full explanation →

528

MCQeasy

A startup is deploying a machine learning model for real-time fraud detection. They need low latency and automatic scaling during peak hours. Which Google Cloud service should they use?

A.Cloud Functions

B.Batch Prediction on Vertex AI

C.Cloud AI Platform Prediction with custom containers

D.Vertex AI Endpoints

AnswerD

Vertex AI Endpoints serves models behind a managed, autoscaling HTTP interface, so replicas scale with traffic to hold latency low during peaks. This directly satisfies the real-time fraud detection requirement for low latency plus automatic scaling, unlike batch prediction or custom serving.

Why this answer

Vertex AI Endpoints provide managed, autoscaling infrastructure designed for low-latency online predictions, making them ideal for real-time fraud detection. They automatically scale the number of compute nodes based on incoming traffic, ensuring peak-hour demand is met without manual intervention.

Exam trap

The trap here is that candidates confuse Cloud Functions or Batch Prediction for real-time serving, overlooking that Vertex AI Endpoints are the only option purpose-built for low-latency, autoscaling online predictions in the modern Vertex AI ecosystem.

How to eliminate wrong answers

Option A is wrong because Cloud Functions is a serverless compute service for event-driven, short-lived tasks, not designed for sustained, low-latency model serving with autoscaling for prediction traffic. Option B is wrong because Batch Prediction on Vertex AI is intended for asynchronous, offline predictions on large datasets, not for real-time, low-latency inference. Option C is wrong because Cloud AI Platform Prediction with custom containers is a legacy service that lacks the integrated autoscaling and endpoint management capabilities of Vertex AI Endpoints, which is the modern, recommended service for online predictions.

Full explanation →

529

MCQmedium

An organization needs to prevent data exfiltration from BigQuery by ensuring all traffic to BigQuery APIs goes through VPC boundaries and is restricted to a specific service perimeter. Which Google Cloud security control should they use?

A.Access Transparency

B.IAM conditions on BigQuery roles

C.Cloud Armor

D.VPC Service Controls

AnswerD

VPC Service Controls creates a service perimeter around BigQuery APIs, ensuring traffic stays within VPC boundaries and is restricted to the specified perimeter. This directly satisfies the stem's requirement to prevent exfiltration via API access controls.

Why this answer

VPC Service Controls (D) is the correct answer because it allows you to define a service perimeter around BigQuery APIs, ensuring that all traffic to BigQuery must originate from within the defined VPC boundaries. This prevents data exfiltration by blocking unauthorized access from outside the perimeter, even if valid credentials are used. It works by enforcing context-aware access policies at the Google Cloud network edge, not at the application layer.

Exam trap

The trap here is that candidates confuse IAM conditions (which control who can access data) with VPC Service Controls (which control where data can be accessed from), leading them to pick Option B instead of D.

How to eliminate wrong answers

Option A is wrong because Access Transparency provides logs of Google personnel access to your data, not network-level controls for data exfiltration. Option B is wrong because IAM conditions on BigQuery roles control authorization based on attributes like IP address or time, but they do not restrict traffic to VPC boundaries or create a service perimeter; they are identity-based, not network-based. Option C is wrong because Cloud Armor is a web application firewall (WAF) for HTTP(S) traffic to load balancers, not for BigQuery API traffic, and it cannot enforce VPC boundaries or service perimeters.

Full explanation →

530

MCQhard

A healthcare company is designing a system to ingest HL7 messages from multiple hospitals into Google Cloud. The messages must be processed in near real-time to extract patient vitals and trigger alerts if thresholds are exceeded. The system must guarantee that no messages are lost and that processing is exactly-once. Which combination of Google Cloud services should they use?

A.Cloud Pub/Sub with a pull subscription and a custom application on Compute Engine that uses the Pub/Sub client library

B.Cloud Pub/Sub with a pull subscription and Cloud Dataflow with exactly-once processing

C.Cloud Pub/Sub with a push subscription to a Cloud Function that writes to Cloud Firestore

D.Cloud Pub/Sub with a push subscription to Cloud Run, which then publishes to a second Pub/Sub topic for Dataflow

AnswerB

Cloud Pub/Sub provides reliable, scalable message ingestion with at-least-once delivery. When combined with Cloud Dataflow, which supports exactly-once processing semantics for streaming pipelines, this ensures that each message is processed exactly once. Dataflow can handle near real-time processing, extract vitals, and trigger alerts. This combination meets the requirements for no message loss and exactly-once processing in a near real-time system.

Why this answer

Cloud Pub/Sub with a pull subscription ensures reliable message delivery, and Cloud Dataflow supports exactly-once processing for streaming pipelines. Together, they provide a managed solution that guarantees no message loss and exactly-once semantics. Dataflow can process HL7 messages in near real-time, extract vitals, and trigger alerts.

This combination is the most robust and operationally efficient way to meet the strict processing guarantees required by the healthcare scenario.

Exam trap

The trap here is assuming that any message processing service can achieve exactly-once semantics without considering the specific guarantees of the processing engine.

Full explanation →

531

MCQeasy

A large retail company processes point-of-sale transactions from thousands of stores daily. The current batch pipeline runs on Cloud Dataproc using Spark and takes 3 hours to complete. The business wants to reduce processing time to under 30 minutes. The pipeline reads from Cloud Storage, joins with inventory data from BigQuery, performs aggregations, and writes to Cloud SQL for reporting. What is the most effective optimization?

A.Migrate the pipeline to Cloud Dataflow with Apache Beam for auto-scaling

B.Read inventory data from BigQuery and pre-join in BigQuery, then export to Cloud Storage as ORC files

C.Write intermediate results to Cloud SQL instead of BigQuery for faster access

D.Increase the number of worker nodes in the Dataproc cluster

AnswerB

Reduces data shuffle in Spark and speeds up processing.

Why this answer

It offloads the join operation to BigQuery, which is optimized for large-scale analytics and can process the join much faster than Spark. By pre-joining and exporting the result as ORC files (a columnar format optimized for Spark), the pipeline avoids the expensive shuffle and data transfer between Cloud Storage and BigQuery, significantly reducing the overall processing time to meet the 30-minute target.

Exam trap

The trap here is that candidates often assume that simply scaling up the existing infrastructure (more workers or auto-scaling) is the most effective optimization, but Cisco tests the understanding that architectural changes to reduce data movement and leverage service-specific strengths (like BigQuery for joins) are far more impactful than brute-force scaling.

How to eliminate wrong answers

Option A is wrong because migrating to Cloud Dataflow with Apache Beam introduces auto-scaling but does not address the fundamental bottleneck of joining large datasets across Cloud Storage and BigQuery; the join operation would still require significant data movement and processing, likely not achieving the required speedup. Option C is wrong because writing intermediate results to Cloud SQL instead of BigQuery would actually slow down the pipeline, as Cloud SQL is a transactional database not designed for high-throughput batch writes, and it would introduce additional latency and potential contention. Option D is wrong because simply increasing the number of worker nodes in the Dataproc cluster may improve parallelism but does not eliminate the costly shuffle and data transfer inherent in the join between Cloud Storage and BigQuery; it would also increase costs without guaranteeing the 6x performance improvement needed.

Full explanation →

532

MCQmedium

A company uses Cloud Pub/Sub for a real-time data pipeline. The subscription has a backlog of millions of messages that are not being processed quickly enough. In Cloud Monitoring, you observe that the 'subscription/num_undelivered_messages' metric is high and growing, while 'subscription/oldest_unacked_message_age' is also increasing. Which action is MOST likely to reduce the backlog?

A.Delete the subscription and recreate it with a larger message retention duration.

B.Reduce the acknowledgment deadline to force faster processing.

C.Change the subscription type from push to pull.

D.Increase the number of subscribers or the throughput capacity of the existing subscribers.

AnswerD

Scaling subscriber count or per-subscriber throughput directly raises the rate at which messages are pulled and acknowledged, so the undelivered-message count and oldest-unacked age both fall. The backlog stems from consumption capacity lagging behind publish rate, not from retention or delivery configuration, so adding consumer capacity addresses the actual constraint.

Why this answer

The backlog indicates that subscribers cannot keep up with the message flow. Increasing the number of subscribers or scaling their throughput capacity directly addresses the processing bottleneck, allowing messages to be pulled and acknowledged faster. Cloud Pub/Sub scales horizontally, so adding more pull subscribers or increasing the resources of existing ones (e.g., more worker threads, higher CPU/memory) reduces the backlog.

Exam trap

A common mistake is to think that reducing the acknowledgment deadline or changing subscription type will speed up processing, when in reality these actions can increase redeliveries or do not address the root cause of insufficient subscriber capacity.

How to eliminate wrong answers

Option A is wrong because deleting and recreating the subscription with a larger message retention duration does not increase processing speed; it only keeps messages longer, which does not reduce the existing backlog. Option B is wrong because reducing the acknowledgment deadline forces subscribers to acknowledge messages faster, but if they cannot process them in time, it leads to more redeliveries and can worsen the backlog. Option C is wrong because changing from push to pull does not inherently increase throughput; both modes can be scaled, and the bottleneck is subscriber capacity, not the delivery mechanism.

Full explanation →

533

MCQeasy

Your data engineering team needs to process a continuous stream of clickstream events from a website and update a real-time dashboard showing user activity over the last hour. The pipeline should have minimal operational overhead and support exactly-once processing semantics. Which Google Cloud service should you use?

A.Cloud Dataproc with Apache Spark Streaming

B.Cloud Data Fusion with batch pipelines

C.Cloud Dataflow with Apache Beam

D.Cloud Pub/Sub Lite with push subscriptions

AnswerC

Cloud Dataflow with Apache Beam provides serverless, autoscaling stream processing with exactly-once semantics via its streaming engine, satisfying the minimal operational overhead and exactly-once constraints. Beam's windowing and triggers handle the rolling one-hour dashboard aggregation over continuous clickstream events.

Why this answer

Cloud Dataflow with Apache Beam is Google's fully managed, serverless stream and batch processing service, and Beam provides built-in exactly-once semantics for streaming pipelines. It integrates natively with Pub/Sub for ingestion and supports windowing, triggers, and state for real-time aggregations like a rolling 1-hour user activity view. Because Dataflow is fully managed, it meets the 'minimal operational overhead' requirement without cluster management.

Exam trap

The trap is equating 'streaming' with any managed service — candidates pick Dataproc or Pub/Sub Lite because they sound real-time, but only Dataflow provides managed exactly-once stream processing with windowing.

How to eliminate wrong answers

Option A is wrong because Dataproc with Spark Streaming requires managing a cluster (sizing, scaling, patching) and does not provide exactly-once semantics out of the box — it typically offers at-least-once and needs idempotent sinks. Option B is wrong because Cloud Data Fusion batch pipelines are designed for batch ETL, not continuous real-time streaming dashboards. Option D is wrong because Pub/Sub Lite is a messaging service, not a processing engine — it can deliver messages but cannot compute windowed aggregations or maintain exactly-once processing state.

Full explanation →

534

MCQmedium

After migrating a production Cloud SQL for PostgreSQL database to a larger machine type, the team notices slower queries. What is the best step to identify the cause?

A.Reindex all tables to improve index efficiency.

B.Enable query caching through the database flags.

C.Enable pg_stat_statements and review query execution times.

D.Increase max_connections to handle more concurrent queries.

AnswerC

Enabling pg_stat_statements captures per-query execution statistics inside PostgreSQL itself, exposing which statements slowed after the resize. This satisfies the stem's need to identify the cause, since the extension aggregates total and mean execution times by normalised query, revealing plan regressions or resource contention that a machine-type change alone would not explain.

Why this answer

Pg_stat_statements is a PostgreSQL extension that provides detailed query execution statistics, including total execution time, number of calls, and I/O metrics. After migrating to a larger machine type, slower queries often stem from plan changes due to different hardware characteristics or configuration settings; reviewing pg_stat_statements output helps pinpoint which queries are underperforming and why.

Exam trap

Google Cloud often tests the misconception that performance issues after a migration are always due to indexing or connection limits, when in fact the most effective first step is to gather query-level metrics using built-in tools like pg_stat_statements.

How to eliminate wrong answers

Option A is wrong because reindexing all tables is a maintenance task that can improve index bloat but does not address the root cause of slower queries after a migration; it is a reactive measure without diagnostic value. Option B is wrong because Cloud SQL for PostgreSQL does not support a generic 'query caching' database flag; PostgreSQL relies on shared buffers and the buffer cache, and enabling any such flag would not provide diagnostic insight into query performance. Option D is wrong because increasing max_connections can actually degrade performance by increasing context switching and memory contention; it does not help identify why queries are slower and may worsen the issue.

Full explanation →

535

Multi-Selectmedium

Which TWO features of Cloud Pub/Sub guarantee at-least-once delivery and enable exactly-once processing downstream? (Choose two.)

Select 2 answers

A.Subscriber-retry policy with exponential backoff.

B.Exactly-once delivery subscription setting (optional, not enabled by default)

C.Message ordering by message key.

D.Cloud Dataproc integration for message replay.

E.Acknowledgment deadlines and message persistence.

AnswersA, E

Retries ensure messages are eventually delivered on failure.

Why this answer

Option A is correct because a subscriber-retry policy with exponential backoff ensures that when a message handler fails or does not acknowledge in time, Pub/Sub redelivers the message with increasing delays, which is a core mechanism supporting at-least-once delivery. Option E is correct because acknowledgment deadlines determine how long Pub/Sub waits for an ack before redelivering, and message persistence (retaining messages in storage until acknowledged or expired) guarantees that messages are not lost, thereby enforcing at-least-once delivery. Together, these features let downstream consumers deduplicate or use idempotent processing to achieve exactly-once processing semantics.

Option B is not marked correct because exactly-once delivery is an optional subscription setting, not enabled by default, and the question asks for features guaranteeing at-least-once delivery. Option C is not correct because message ordering by key only preserves order within an ordering key and does not itself guarantee delivery or exactly-once processing. Option D is not correct because Cloud Dataproc integration is unrelated to Pub/Sub delivery guarantees or replay semantics.

Exam trap

Google Cloud often tests the misconception that message ordering or replay features contribute to delivery guarantees, when in fact ordering is about sequence and replay is not a native Pub/Sub capability; the key trap is confusing 'exactly-once delivery' (which Pub/Sub does not offer) with 'exactly-once processing' (which requires subscriber-side idempotency).

Full explanation →

536

MCQeasy

A startup is using Cloud Build to automate the training and deployment of their machine learning models. The workflow is defined in cloudbuild.yaml and includes steps to: 1) Run a training job on AI Platform Training, 2) Build a custom prediction container, 3) Deploy the container to Cloud Run for serving. The deployment step fails intermittently with the error: 'Cloud Run service already exists and is not owned by the calling user.' You need to fix this so that deployments are reliable. What should you do?

A.Ensure the Cloud Build service account has the 'run.services.update' permission on the Cloud Run service.

B.Delete the existing Cloud Run service manually before each build.

C.Use 'gcloud run deploy --replace' in the build step to force replace the existing service.

D.Use Cloud Run for Anthos instead of fully managed Cloud Run to avoid ownership issues.

AnswerA

The error suggests a permissions issue; granting the correct role to the Cloud Build service account resolves it.

Why this answer

The error indicates that the Cloud Run service already exists and the Cloud Build service account does not own it. The Cloud Build service account needs the 'run.services.update' IAM permission on the specific Cloud Run service to modify it during deployment. Granting this permission allows the service account to update the existing service reliably, resolving the intermittent failure.

Exam trap

Google Cloud often tests the misconception that a command-line flag like '--replace' can bypass IAM permission errors, but the root cause is always IAM misconfiguration, not a missing flag.

How to eliminate wrong answers

Option B is wrong because manually deleting the service before each build is not a scalable or automated solution; it introduces manual steps and potential downtime, and does not address the underlying permission issue. Option C is wrong because 'gcloud run deploy --replace' does not exist; the correct flag is '--no-traffic' or '--async', and the error is about ownership, not about forcing replacement. Option D is wrong because switching to Cloud Run for Anthos does not resolve the ownership issue; the same IAM permission model applies, and the error would persist if the service account lacks the necessary permissions.

Full explanation →

537

MCQeasy

Your team uses Cloud Dataproc to run a Spark ML training job. The job is failing with an error: 'Container killed by YARN for exceeding memory limits.' What should you do to fix this?

A.Increase the spark.executor.memory property

B.Use preemptible VMs for faster execution

C.Increase the number of worker nodes

D.Enable the external shuffle service

AnswerA

Raising `spark.executor.memory` gives each YARN container a larger heap, so the executor stays within the memory ceiling YARN enforces. The failure stems from the container exceeding its allocated limit, and this property directly governs that allocation, letting the Spark ML training job complete without being killed.

Why this answer

The error 'Container killed by YARN for exceeding memory limits' indicates that the Spark executor process is using more memory than the YARN container allows. Increasing `spark.executor.memory` allocates a larger YARN container for each executor, providing the necessary headroom for the Spark application's memory demands, including overhead for off-heap memory and JVM internals.

Exam trap

The trap here is that candidates often confuse scaling horizontally (adding nodes) with scaling vertically (increasing per-node resources), and assume more nodes will fix memory limits when the issue is per-container allocation.

How to eliminate wrong answers

Option B is wrong because preemptible VMs are cheaper but can be terminated at any time, which does not address memory limits and can actually cause more failures due to preemption. Option C is wrong because increasing the number of worker nodes adds more executors but does not increase the memory per executor; the existing executors will still exceed their container limits. Option D is wrong because the external shuffle service helps with shuffle data persistence and reduces executor memory pressure during shuffle operations, but it does not increase the per-executor memory allocation; the root cause is insufficient container memory, not shuffle management.

Full explanation →

538

MCQmedium

A data platform team wants to grant a service account the ability to run BigQuery jobs and read data in a specific dataset, while ensuring it cannot create or delete datasets. Which IAM approach satisfies this with least privilege?

A.Grant the BigQuery Admin role on the dataset.

B.Grant the BigQuery Data Editor role at the project level.

C.Grant bigquery.jobs.create at the project level and BigQuery Data Viewer on the specific dataset.

D.Grant the service account the BigQuery Data Viewer role at the project level.

AnswerC

Job creation permission is granted at the project level because jobs are project-scoped resources, while data read access is granted on the dataset to limit exposure. This combination lets the service account run jobs that read the target dataset without granting rights over other datasets. It excludes dataset creation and deletion permissions, satisfying least privilege.

Why this answer

BigQuery separates the permission to run jobs from the permission to read data. Job creation is a project-level capability, so the service account needs bigquery.jobs.create at the project scope. Reading data can be scoped to the specific dataset with BigQuery Data Viewer, which confines access to that dataset and excludes dataset creation or deletion.

Combining these two grants achieves the read-and-run requirement with least privilege.

Exam trap

The trap here is assuming a single predefined role covers both running jobs and reading a dataset, when job creation and data access are granted at different scopes.

Full explanation →

539

Multi-Selecthard

A data platform team uses Cloud Data Fusion to move data from an on-premises relational database into BigQuery. They need the pipeline to run on a fixed schedule, capture only rows changed since the last successful run, and avoid re-reading the entire source table each night. The source table has an updated_at column that is reliably populated. Which two approaches should they use? (Choose two.)

Select 2 answers

A.Use a Replicator pipeline with the default full-table snapshot mode for each run

B.Enable the Cloud Data Fusion lineage feature to track which rows were previously loaded

C.Configure the pipeline to run on a schedule using the Data Fusion scheduler or a Cloud Composer trigger

D.Use the BigQuery sink plugin with write disposition set to truncate before each nightly load

E.Use the Database source plugin with a query that filters on updated_at greater than the last recorded watermark

AnswersC, E

Cloud Data Fusion pipelines can be scheduled directly through the built-in scheduler, or triggered externally by Cloud Composer, which provides the fixed nightly cadence the team requires. Without a schedule the pipeline would have to be started manually, so this is necessary to meet the recurring-run requirement while keeping the incremental logic in the pipeline itself.

Why this answer

Scheduling the pipeline and filtering the source query on a persisted updated_at watermark together deliver a recurring job that reads only changed rows. The watermark must be written after a successful load so failures do not advance it prematurely, and the query-based Database source plugin is what makes the filter possible without custom code.

Exam trap

The trap here is treating lineage or truncate-and-reload as change-capture mechanisms, when only a stored watermark plus a filtered source query reads just the changed rows.

Full explanation →

540

MCQmedium

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?

A.Raise an exception in the pipeline and stop processing

B.Use retry logic in the pipeline to reprocess malformed messages indefinitely

C.Implement a dead letter sink to store malformed messages for later analysis

D.Discard malformed messages and log an error

AnswerC

A dead letter sink diverts messages that fail parsing or validation into a separate store, so the pipeline continues processing valid records without data loss. This satisfies the requirement to handle occasional malformed messages while preserving valid viewer data for later analysis.

Why this answer

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.

Exam trap

Google Cloud often tests the dead letter pattern to see if candidates understand that streaming pipelines must handle bad data gracefully without stopping or losing valid records, and the trap is that many candidates choose retry logic (Option B) because they confuse transient errors with permanent data quality issues.

How to eliminate wrong answers

Option A is wrong because raising an exception and stopping the pipeline would cause all processing to halt, leading to data loss for valid messages and violating the requirement to handle malformed messages without losing valid data. Option B is wrong because retrying malformed messages indefinitely would cause the pipeline to stall on bad records, potentially blocking the processing of subsequent valid messages and increasing latency; Dataflow's retry mechanisms are intended for transient errors, not for permanently malformed data. Option D is wrong because discarding malformed messages and logging an error results in permanent data loss, which contradicts the requirement to preserve data for later analysis and violates best practices for data integrity in streaming pipelines.

Full explanation →

541

MCQeasy

Your team needs to store time-series data from millions of IoT devices. Each device sends a reading every 5 minutes, and the total data volume is about 2 TB per month. The most common query pattern is retrieving all readings for a specific device over a time range (e.g., last 24 hours). Which storage service should you choose?

A.Cloud Storage (objects per device per time interval)

B.BigQuery

C.Cloud Bigtable

D.Cloud Spanner

AnswerC

Bigtable's row-key design, keyed by device ID with time as a suffix, gives fast range scans for a single device over a time window, and scales horizontally for millions of devices writing every five minutes at 2 TB monthly.

Why this answer

Cloud Bigtable is a fully managed, scalable NoSQL database designed for high-throughput, low-latency time-series data. It supports single-row key lookups and range scans, making it ideal for retrieving all readings for a specific device over a time range (e.g., last 24 hours) from millions of IoT devices generating 2 TB/month. Its row key design (e.g., device_id + timestamp) enables efficient time-range queries without full table scans, unlike object storage or analytical warehouses.

Exam trap

Google Cloud often tests the misconception that BigQuery is suitable for operational, low-latency time-series queries, but the trap here is that BigQuery is an analytical warehouse optimized for large-scale batch queries, not for repeated, sub-second per-device range scans, which is a classic NoSQL (Bigtable) workload.

How to eliminate wrong answers

Option A is wrong because Cloud Storage (object storage) is optimized for immutable blob storage and lacks native indexing for time-range queries; retrieving all readings for a device over a time range would require listing and filtering millions of objects, which is slow and costly. Option B is wrong because BigQuery is a serverless data warehouse designed for analytical SQL queries on large datasets, not for real-time, high-throughput point lookups or range scans with sub-millisecond latency; it would incur high query costs and latency for repeated per-device time-range retrievals. Option D is wrong because Cloud Spanner is a globally distributed relational database with strong consistency and ACID transactions, which is overkill for time-series IoT data and would be prohibitively expensive and slower for high-volume, simple key-value range scans compared to Bigtable.

Full explanation →

542

MCQmedium

A user named Charlie needs to deploy a model to a Vertex AI Endpoint and also create training jobs. Which role should be assigned to Charlie?

A.roles/aiplatform.user

B.roles/owner

C.roles/aiplatform.modelUser

D.roles/editor

AnswerA

roles/aiplatform.user grants permissions to create and manage training jobs and deploy models to Vertex AI Endpoints, covering both tasks Charlie needs. It provides the necessary platform-level access without granting broader administrative rights, satisfying the deployment and training job requirement.

Why this answer

Charlie needs to deploy a model to a Vertex AI Endpoint and create training jobs. The `roles/aiplatform.user` role grants the necessary permissions to use all Vertex AI resources, including creating and managing endpoints, training jobs, models, and predictions. This role is the minimum required for a user to interact with Vertex AI services without granting broader project-level permissions.

Exam trap

The trap here is that candidates often confuse `roles/aiplatform.user` with `roles/aiplatform.modelUser`, mistakenly thinking the latter is sufficient for creating training jobs, when in fact it only allows prediction on existing models.

How to eliminate wrong answers

Option B is wrong because `roles/owner` grants full project-level access, including the ability to delete resources and manage IAM policies, which is excessive and violates the principle of least privilege. Option C is wrong because `roles/aiplatform.modelUser` only allows a user to deploy and predict from an existing model, but does not include permissions to create training jobs or manage endpoints. Option D is wrong because `roles/editor` grants broad project-level edit permissions across all Google Cloud services, not just Vertex AI, and is too permissive for the specific task of deploying models and creating training jobs.

Full explanation →

543

MCQmedium

A company has deployed a machine learning model on Vertex AI Prediction that serves real-time predictions for a customer-facing application. The model was trained using a custom container and is hosted on a single endpoint with a minimum number of nodes. Recently, the team noticed that during peak traffic, prediction latency increases significantly and some requests time out. The endpoint is configured with a baseline traffic split of 100% on the current model version. Which action should the team take to reduce latency and improve reliability?

A.Reduce the minimum number of nodes to zero to allow scale-to-zero during low traffic.

B.Place a Google Cloud Load Balancer in front of the Vertex AI endpoint to distribute requests across multiple endpoints.

C.Configure horizontal autoscaling with a higher maximum number of nodes and set a CPU utilization target.

D.Implement A/B testing by splitting traffic between two model versions to distribute load.

AnswerC

Autoscaling allows the endpoint to add nodes during high traffic, reducing latency and preventing timeouts.

Why this answer

Configuring horizontal autoscaling with a higher maximum number of nodes and a CPU utilization target allows Vertex AI Prediction to automatically add more nodes during peak traffic, distributing the inference load and reducing latency. This directly addresses the root cause—insufficient compute resources under high demand—without requiring architectural changes or sacrificing availability.

Exam trap

The trap is that candidates often confuse external load balancing (Option B) with autoscaling, assuming that distributing requests across multiple endpoints is equivalent to adding compute capacity. However, Vertex AI Prediction endpoints are single resources that cannot be scaled horizontally by fronting them with a load balancer—they require an internal autoscaling configuration, such as setting a higher maximum node count and a CPU utilization target, to dynamically add nodes during peak demand.

How to eliminate wrong answers

Option A is wrong because reducing the minimum number of nodes to zero would cause cold starts when traffic arrives, increasing latency rather than reducing it, and scale-to-zero is not suitable for a customer-facing application requiring real-time predictions. Option B is wrong because placing a Google Cloud Load Balancer in front of a single Vertex AI endpoint does not distribute requests across multiple endpoints—it would only add unnecessary network hops and complexity without solving the resource bottleneck. Option D is wrong because A/B testing splits traffic between model versions for evaluation purposes, not for load distribution; it does not increase the total compute capacity available to handle peak traffic.

Full explanation →

544

MCQmedium

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

A.Cloud Bigtable

B.BigQuery

C.Cloud Spanner

D.Firestore

AnswerA

Cloud Bigtable is a wide-column NoSQL store built on a sparse sorted map, delivering consistent single-digit millisecond latency at millions of reads per second. Its row-key design suits timestamped key-value IoT data at petabyte scale, matching every stated constraint.

Why this answer

Cloud Bigtable is a fully managed, scalable NoSQL database designed for large analytical and operational workloads, handling petabytes of data with consistent sub-10ms latency at millions of reads per second. Its key-value model with timestamps directly matches the time-series IoT sensor data structure, and it supports high-throughput, low-latency access via the HBase API or Bigtable client libraries.

Exam trap

A common trap in Google Cloud exams is confusing operational databases (Bigtable) with analytical warehouses (BigQuery). The petabyte-scale data might suggest BigQuery, but the single-digit millisecond latency and high throughput key-value access pattern require Bigtable's purpose-built design.

How to eliminate wrong answers

Option B (BigQuery) is wrong because it is a serverless data warehouse optimized for analytical SQL queries on large datasets, not for single-digit millisecond point reads at millions of operations per second; it incurs higher latency (typically hundreds of milliseconds) and is not designed for real-time key-value lookups. Option C (Cloud Spanner) is wrong because it is a globally distributed relational database with strong consistency and SQL support, but its latency and throughput for simple key-value reads are higher than Bigtable, and it is overkill for time-series data that does not require relational joins or transactions. Option D (Firestore) is wrong because it is a mobile and web document database optimized for real-time updates and moderate throughput, not for petabyte-scale time-series data with millions of reads per second; it has throughput limits (e.g., 10,000 writes/second per database) and higher latency for such high-volume workloads.

Full explanation →

545

MCQmedium

Your organization uses Vertex AI Feature Store to serve features for a real-time fraud detection model. The model is deployed on a Vertex AI endpoint. After a data pipeline update, the model's online predictions became inconsistent. What is the most likely cause?

A.The model's prediction server is running out of memory.

B.The feature store's online serving values are not synchronized with the batch feature values used during training.

C.The model was retrained with a different training dataset.

D.The online serving endpoint's model version was accidentally rolled back.

AnswerB

Online serving reads feature values from the online store, which must match the batch values used at training time. If the pipeline update writes stale or unsynchronised values, the endpoint receives features on a different distribution, producing inconsistent predictions. This is the classic training-serving skew caused by online and batch feature stores diverging.

Why this answer

In Vertex AI Feature Store, batch feature values used during model training and online serving values are stored separately. If a data pipeline update changes the batch feature values but the online serving values are not updated or synchronized, the model will receive different feature values at inference time than it was trained on, leading to inconsistent predictions. This is the most common cause of prediction drift after a pipeline change.

Exam trap

The trap here is that candidates may confuse a data pipeline update with a model retraining or version rollback, but the key is recognizing that feature store synchronization between batch and online stores is a distinct operational concern that directly causes prediction inconsistency.

How to eliminate wrong answers

Option A is wrong because running out of memory on the prediction server would cause errors or timeouts, not inconsistent predictions; the model would either fail or produce no output, not produce varying results. Option C is wrong because retraining with a different dataset would produce a new model version, but the question states predictions became inconsistent after a data pipeline update, not after a retraining event; a retrained model would be deployed as a new version, not cause inconsistency in the existing model's outputs. Option D is wrong because a rollback of the model version would revert to a previous consistent state, not introduce inconsistency; the predictions would be consistent with the older model version, not inconsistent.

Full explanation →

546

MCQeasy

A company runs a batch ETL pipeline on Cloud Dataproc. During peak hours, the job takes longer than expected. The pipeline reads from Cloud Storage, transforms data, and writes to BigQuery. What is the most cost-effective way to improve performance without redesigning the pipeline?

A.Add a secondary worker group using preemptible VMs and increase the number of workers.

B.Enable local SSDs on all worker nodes.

C.Increase the master node's machine type to n1-highmem-32.

D.Use Cloud Composer to schedule the job with a higher priority.

AnswerA

Preemptible secondary workers cost far less than standard instances, and adding workers increases parallel processing capacity for the Cloud Storage to BigQuery job. This improves throughput during peak hours without altering the pipeline design, satisfying the cost-effectiveness and no-redesign constraints.

Why this answer

Adding a secondary worker group with preemptible VMs is the most cost-effective way to improve performance because it allows you to scale out the cluster horizontally with compute instances that are significantly cheaper (up to 80% discount) than regular VMs. This directly addresses the bottleneck of processing capacity during peak hours without requiring any pipeline redesign, as Cloud Dataproc can automatically distribute work across additional workers.

Exam trap

The trap here is that candidates assume scaling up the master node or improving local storage will help, but the exam tests understanding that horizontal scaling with cheap, ephemeral workers is the most cost-effective approach for batch processing workloads that are CPU-bound and fault-tolerant.

How to eliminate wrong answers

Option B is wrong because enabling local SSDs on all worker nodes improves I/O performance for intermediate data, but the pipeline reads from Cloud Storage and writes to BigQuery, which are network-based operations; the bottleneck is CPU/memory for transformation, not local disk speed, making this an expensive upgrade with minimal impact. Option C is wrong because increasing the master node's machine type to n1-highmem-32 only improves the coordination and management of the cluster, not the actual data processing capacity; the master node does not perform data transformation work, so this does not address the performance bottleneck. Option D is wrong because Cloud Composer is a workflow orchestration tool that schedules and monitors jobs, but it does not directly improve the runtime performance of the ETL pipeline; setting a higher priority only affects scheduling order, not execution speed.

Full explanation →

547

MCQeasy

Which Google Cloud service provides a serverless Spark environment where you can run Spark jobs without provisioning or managing a cluster?

A.Dataflow

B.Dataproc Serverless

C.Dataprep

D.Cloud Data Fusion

AnswerB

Dataproc Serverless runs Spark workloads on managed, ephemeral infrastructure, so no cluster provisioning, sizing or teardown is required. This satisfies the serverless constraint directly, unlike Dataproc on Compute Engine, which requires you to create and manage clusters yourself.

Why this answer

Dataproc Serverless is a Google Cloud service that allows you to run Spark jobs without provisioning or managing a cluster. It automatically scales resources and charges only for the duration of the job, making it ideal for serverless Spark workloads. This matches the requirement exactly.

Exam trap

PDE often tests the distinction between serverless Spark (Dataproc Serverless) and other serverless data services like Dataflow, causing candidates to confuse the underlying processing engines.

How to eliminate wrong answers

Option A is wrong because Dataflow is a serverless service for Apache Beam, not Spark; it is used for stream and batch data processing but does not run Spark jobs. Option C is wrong because Dataprep is a data preparation tool for visual exploration and transformation, not a Spark execution environment. Option D is wrong because Cloud Data Fusion is a fully managed data integration service for building ETL pipelines, but it does not provide a serverless Spark environment for running arbitrary Spark jobs.

Full explanation →

548

MCQmedium

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?

A.Use a dead letter sink to write malformed records to a separate Pub/Sub topic or GCS location.

B.Catch the exception and log it, then continue processing.

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

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

AnswerA

A dead letter sink isolates malformed records by routing them to a separate Pub/Sub topic or GCS location, so a single bad record cannot fail the whole streaming pipeline. This satisfies the requirement to keep processing valid data while retaining failures for later analysis.

Why this answer

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.

Exam trap

The trap is choosing 'catch and log' as a simpler alternative, but logging does not provide a durable, queryable store for bad records, which the question explicitly requires.

How to eliminate wrong answers

Option B is wrong because catching and logging the exception loses the record content in a structured way and does not provide a durable store for later analysis; logs are not a substitute for a dead letter sink. Option C is wrong because writing all records to BigQuery and handling errors in the write operation does not isolate malformed records and may still fail the pipeline or lose data. Option D is wrong because raising an exception stops the pipeline, which directly violates the requirement to not fail the entire pipeline.

Full explanation →

549

MCQhard

A media analytics team runs a Dataflow streaming pipeline that reads click events from Pub/Sub and writes aggregates to BigQuery. During peak hours, the pipeline's BigQuery write throughput plateaus and Dataflow logs show repeated quota-related retries on the streaming insert API. The team wants to keep exactly-once semantics and increase sustained write throughput. What should they change?

A.Enable autoscaling on the Dataflow job and raise the maximum worker count to the regional limit.

B.Write the aggregates to Cloud Storage as sharded files and schedule a load job every five minutes.

C.Increase the number of Dataflow workers to spread the streaming insert load across more threads.

D.Switch the BigQuery sink to the Storage Write API with exactly-once semantics enabled.

AnswerD

The Storage Write API is designed for high-throughput streaming ingestion and, when the exactly-once stream mode is used, provides exactly-once delivery into BigQuery. It avoids the per-row streaming insert quota that causes the plateau while preserving the semantics the team requires. This is the recommended replacement for the legacy streaming insert path in Dataflow.

Why this answer

The Storage Write API with exactly-once mode is the intended high-throughput sink for Dataflow to BigQuery and removes the legacy streaming insert quota bottleneck while keeping exactly-once delivery. Increasing workers or autoscaling addresses compute, not the API quota, and batching to Cloud Storage trades away latency and complicates semantics. Replacing the sink is the targeted fix.

Exam trap

The trap here is treating a BigQuery API quota plateau as a Dataflow scaling problem and adding workers instead of changing the write path.

Full explanation →

550

MCQmedium

A Dataflow batch job fails consistently with the error shown. The job uses a custom container image and runs in a VPC with a private IP. What should the engineer do to resolve the issue?

A.Request a CPU quota increase in the region.

B.Verify that the VPC has Private Google Access enabled and that Cloud NAT is configured for outbound internet access if needed.

C.Rebuild the custom container image and upload it to Container Registry.

D.Check that the custom image is based on the latest Dataflow SDK version.

AnswerB

Private Google Access lets the private-IP subnet reach Google APIs and services without external addresses, satisfying the job's dependency on those endpoints. Cloud NAT supplies outbound internet access for the custom container image's external pulls, since instances lacking public IPs cannot otherwise reach the registry.

Why this answer

The error indicates that the Dataflow batch job cannot access required resources (e.g., container image, dependencies) because the VPC with private IPs lacks outbound internet connectivity. Option B is correct because enabling Private Google Access allows the VMs to reach Google APIs (like Container Registry) via the Google network, and Cloud NAT provides outbound internet access for non-Google APIs or external dependencies. Without these, the job fails to pull the custom container image or download necessary artifacts.

Exam trap

The trap here is that candidates often assume the error is due to the container image or SDK version, overlooking the VPC networking prerequisites (Private Google Access and Cloud NAT) that are required for Dataflow jobs using private IPs.

How to eliminate wrong answers

Option A is wrong because a CPU quota increase would not resolve connectivity issues; the error is about network access, not resource limits. Option C is wrong because rebuilding the container image does not fix the underlying network configuration problem; the image itself is not the cause of the failure. Option D is wrong because the Dataflow SDK version in the custom image is irrelevant to VPC networking; the job fails due to lack of outbound connectivity, not SDK compatibility.

Full explanation →

551

MCQhard

You manage a large-scale machine learning system that recommends products to users. The model is a deep neural network trained on TensorFlow and deployed on Vertex AI Endpoint with global load balancing. The model receives over 10,000 requests per second. Recently, the team added a new feature: the user's current geographic location (latitude/longitude). After deploying the updated model, you notice that the average prediction latency has doubled, and the error rate has increased, particularly for requests from regions far from the model's primary training data (North America). You suspect the location feature is causing issues. What should you do to diagnose and mitigate the problem?

A.Remove the location feature from the model and retrain without it to restore performance.

B.Increase the number of replicas for the endpoint to handle the increased latency.

C.Switch to a regional endpoint in North America to reduce latency for the majority of users.

D.Examine the latency breakdown using Cloud Monitoring to see if the location feature is causing computationally expensive operations, then consider feature engineering like bucketing coordinates.

AnswerD

Cloud Monitoring's latency breakdown isolates whether the latitude/longitude feature introduces expensive preprocessing or distance computations, satisfying the need to pinpoint the doubling's source. Bucketing coordinates into discrete geographic regions then reduces cardinality and sparsity, addressing the elevated error rates for requests far from North American training data.

Why this answer

The correct first step is to gather diagnostic data before making changes. Cloud Monitoring provides latency breakdowns per operation, letting you confirm whether the new location feature (e.g., raw latitude/longitude floats feeding into dense layers or distance computations) is the bottleneck. Once confirmed, feature engineering such as geohash bucketing or embedding coordinates reduces computational cost and improves generalization for out-of-region requests.

Exam trap

PDE often tests whether candidates jump to infrastructure scaling (adding replicas) or feature removal instead of first using observability tooling to isolate the root cause before mitigating.

How to eliminate wrong answers

Option A is wrong because removing the feature and retraining is a drastic, unverified action that discards potentially valuable signal without first confirming the feature is the root cause. Option B is wrong because adding replicas only scales capacity; it does not address the increased per-request compute cost or the model's poor generalization to distant regions, so latency per request and error rates remain elevated. Option C is wrong because switching to a regional North America endpoint would degrade latency for non-North American users and does nothing to fix the underlying feature-induced latency or accuracy problem.

Full explanation →

552

MCQeasy

A company needs to process streaming data from IoT devices with sub-second latency and exactly-once processing guarantees. Which Google Cloud service should they use?

A.BigQuery

B.Cloud Dataproc

C.Cloud Dataflow

D.Cloud Pub/Sub

AnswerC

Cloud Dataflow provides exactly-once processing through its Apache Beam runner, which tracks watermarks and uses checkpointing to deduplicate records during streaming execution. It satisfies the sub-second latency requirement via streaming mode rather than micro-batch windows, unlike Pub/Sub alone or Dataproc, which cannot guarantee exactly-once semantics natively.

Why this answer

Cloud Dataflow is the correct choice because it provides a unified stream and batch processing model with exactly-once processing guarantees and sub-second latency via its Apache Beam SDK. It supports event-time processing, watermarks, and triggers to handle out-of-order data from IoT devices while ensuring each record is processed exactly once, even in the case of failures.

Exam trap

Google Cloud often tests the distinction between data ingestion (Pub/Sub) and data processing (Dataflow), so the trap here is that candidates confuse Pub/Sub's streaming ingestion capability with the processing guarantees needed for exactly-once semantics.

How to eliminate wrong answers

Option A is wrong because BigQuery is a serverless data warehouse designed for analytical queries on large datasets, not for real-time stream processing with sub-second latency and exactly-once guarantees; it can ingest streaming data but does not provide the fine-grained per-record processing semantics required. Option B is wrong because Cloud Dataproc is a managed Hadoop/Spark service that can process streaming data via Spark Streaming, but it does not natively guarantee exactly-once processing out of the box and typically has higher latency due to micro-batching. Option D is wrong because Cloud Pub/Sub is a messaging and ingestion service that provides at-least-once delivery by default and does not perform data processing; it is a transport layer, not a processing engine.

Full explanation →

553

MCQhard

A company is building a continuous training pipeline that retrains a model daily using new data from a feature store. The training data must include features computed up to the timestamp of each training run. Which architecture should be used to ensure time-consistent feature values without label leakage?

A.Train on a fixed window of the most recent features without considering timestamps.

B.Use Vertex AI Feature Store with point-in-time lookup enabled to retrieve features as of the training timestamp.

C.Store all features in a Cloud SQL database and perform a join at training time.

D.Use Pub/Sub to stream new features into Cloud Storage and train on the latest snapshot.

AnswerB

Point-in-time lookup retrieves feature values as they existed at each training timestamp, preventing future data from leaking into training rows. This satisfies the time-consistent feature requirement, ensuring daily retraining uses only information available at that run's cutoff.

Why this answer

Vertex AI Feature Store's point-in-time lookup retrieves the exact feature values as they existed at the specified training timestamp, ensuring time-consistency and preventing label leakage. This mechanism avoids using future data that would not have been available at the time of prediction, which is critical for realistic model evaluation and production performance.

Exam trap

Google Cloud often tests the misconception that simply using the most recent data or a snapshot is sufficient for time-consistency, but the key requirement is to retrieve features as of the exact training timestamp to prevent label leakage, which only point-in-time lookup guarantees.

How to eliminate wrong answers

Option A is wrong because training on a fixed window of the most recent features without considering timestamps can introduce label leakage by including future feature values relative to the label timestamp, and it ignores the temporal ordering required for time-series data. Option C is wrong because storing all features in Cloud SQL and performing a join at training time lacks point-in-time semantics, meaning the join may inadvertently use features from after the label timestamp, causing leakage and inconsistent feature values. Option D is wrong because using Pub/Sub to stream new features into Cloud Storage and training on the latest snapshot does not guarantee that features are retrieved as of the exact training timestamp; the snapshot may include data that arrived after the label was generated, leading to leakage.

Full explanation →

554

MCQmedium

A data team needs to run complex analytical queries on a dataset that is frequently updated with new rows. They want to minimize query costs and avoid scanning old data that is rarely queried. Which BigQuery feature should they use?

A.Partitioned tables with partition expiration

B.BigQuery materialized views

C.Clustered tables

D.BigQuery BI Engine

AnswerA

Partitioning by date lets BigQuery prune irrelevant partitions, so queries scan only recent data, and partition expiration automatically deletes old partitions to cut storage and query costs. This suits frequently appended datasets where historical rows are rarely queried.

Why this answer

Partitioned tables with partition expiration allow you to divide a table into segments based on a date/timestamp column, and automatically delete partitions that are older than a specified duration. This minimizes query costs by only scanning relevant partitions and eliminates storage costs for old, rarely queried data without manual intervention.

Exam trap

A common mistake is to choose a performance optimization feature (clustering, materialized views, BI Engine) when the question explicitly asks about minimizing costs and avoiding scanning old data. The correct focus is on data lifecycle management with partition expiration.

How to eliminate wrong answers

Option B is wrong because BigQuery materialized views precompute and cache query results for faster reads, but they do not automatically expire old data or reduce storage costs for rarely queried rows. Option C is wrong because clustered tables sort data within partitions to improve query performance and reduce bytes scanned, but they do not provide automatic deletion of old data or partition expiration. Option D is wrong because BigQuery BI Engine is an in-memory analysis service that accelerates interactive queries but does not manage data lifecycle or expiration of old rows.

Full explanation →

555

MCQmedium

A data engineer needs to store quarterly financial data that must remain immutable for 7 years to meet regulatory compliance. The data is accessed infrequently after the first year. Which Cloud Storage feature should be used to enforce immutability?

A.Object Lifecycle Rules

B.Object Lock with Retention Policy

C.Object Versioning

D.IAM Conditions

AnswerB

Object Lock with a retention policy enforces write-once-read-many (WORM) protection at the object level, preventing deletion or modification for the specified retention period. This directly satisfies the seven-year regulatory immutability requirement, while the bucket's storage class can separately be set to a colder tier for the infrequent post-first-year access.

Why this answer

Object Lock with a Retention Policy enforces immutability by preventing object deletion or modification for a specified duration. For regulatory compliance requiring 7-year immutable storage, a retention policy configured with a retention period of 7 years ensures that objects cannot be overwritten or deleted, even by the root account. This directly meets the requirement for data that must remain unchanged for the full 7-year period.

Exam trap

Candidates often confuse Object Versioning with immutability, but versioning only protects against accidental deletion by preserving old versions—it does not prevent intentional deletion or overwriting of the current version, which is required for true immutability.

How to eliminate wrong answers

Option A is wrong because Object Lifecycle Rules manage transitions and deletions based on age or other criteria, but they do not enforce immutability—objects can still be modified or deleted by users with appropriate permissions. Option C is wrong because Object Versioning preserves previous versions of objects but does not prevent deletion or overwriting of the current version; it allows recovery but does not enforce a write-once, read-many (WORM) model. Option D is wrong because IAM Conditions control access based on attributes like IP address or time, but they do not prevent deletion or modification of objects; they only restrict who can perform actions, not enforce data immutability.

Full explanation →

556

MCQhard

You are optimizing a BigQuery query that scans 1 TB of data every day. The query joins a large fact table (partitioned by date) with a small dimension table. You notice that the query always scans the entire fact table, even though you only need the last 7 days of data. Which optimization will MOST reduce the bytes scanned?

A.Create a materialized view that pre-aggregates the data by day.

B.Add a WHERE clause that filters on the date column used for partitioning.

C.Cluster the fact table on the join key used in the query.

D.Change the table to use time-unit column partitioning with a 1-day partition interval.

AnswerB

Partition pruning only activates when the query filters on the partitioning column. Adding that WHERE predicate lets BigQuery eliminate all but the last seven date partitions, cutting bytes scanned from 1 TB to roughly 7 days' worth.

Why this answer

BigQuery partitioned tables allow partition pruning, where the query engine skips partitions that do not match the filter condition. Adding a WHERE clause on the partitioning column (date) restricts the scan to only the last 7 days' partitions, drastically reducing bytes scanned. This is the most direct and effective optimization for the described scenario.

Exam trap

PDE often tests whether candidates know that partition pruning requires a filter on the partitioning column; candidates may choose clustering or materialized views, but the most direct reduction in bytes scanned comes from adding the WHERE clause on the partition column.

How to eliminate wrong answers

Option A is wrong because a materialized view pre-aggregates data but does not reduce the bytes scanned for the underlying fact table unless the query is rewritten to use the view; it also adds storage and refresh costs. Option C is wrong because clustering on the join key improves filter and join performance but does not eliminate scanning of all partitions if the date filter is absent; clustering helps with selective filters on clustered columns, but partition pruning is more effective for date ranges. Option D is wrong because changing to time-unit column partitioning with a 1-day interval is already likely the case (partitioned by date), and it does not by itself reduce bytes scanned without a filter; the partitioning method is not the issue, the missing filter is.

Full explanation →

557

MCQmedium

An e-commerce application uses Cloud SQL (MySQL) for transaction processing. To improve read performance for reporting queries, the team wants to offload read traffic to a separate database instance that stays in sync with the primary. Which Cloud SQL feature should they use?

A.Configure a high availability (HA) replica

B.Use Cloud SQL's failover replica

C.Create a cross-region read replica

D.Enable automatic backups and point-in-time recovery

AnswerC

A cross-region read replica offloads reporting queries while asynchronously replicating from the primary, satisfying the requirement for a separate in-sync instance. However, cross-region placement adds replication lag and network cost; a same-region read replica would better suit latency-sensitive reporting, so this option is correct but not optimal.

Why this answer

Cloud SQL read replicas are designed to offload read traffic from the primary instance while staying in sync using asynchronous replication. Cross-region read replicas specifically allow you to place a replica in a different region, improving read performance for geographically distributed reporting queries without affecting the primary's transaction processing.

Exam trap

The pitfall is that candidates often assume a high availability (HA) failover replica can also serve read traffic, but in Cloud SQL, the HA standby is not directly accessible for reads; it is only used for automatic failover. Read replicas (option C) are the correct choice for offloading read queries.

How to eliminate wrong answers

Option A is wrong because a high availability (HA) replica is a synchronous standby that provides automatic failover for high availability, not a separate instance for offloading read traffic; it does not serve read queries independently. Option B is wrong because Cloud SQL does not have a separate 'failover replica' feature; failover is handled by the HA configuration, and the term is often confused with a read replica, but failover replicas are not used for read offloading. Option D is wrong because automatic backups and point-in-time recovery are disaster recovery features that protect data, not mechanisms to offload read traffic or improve query performance.

Full explanation →

558

Multi-Selectmedium

A company is evaluating BigQuery for a data warehouse migration. They have a mix of reporting queries and ad-hoc analytical queries. They want to control query costs and prevent runaway queries. Which THREE strategies should they implement?

Select 3 answers

A.Grant authorized view access to limit data visibility

B.Set a custom quota for concurrent queries

C.Partition and cluster tables to reduce bytes processed

D.Create materialized views for all reporting queries

E.Use BigQuery reservations (flex slots) for predictable workloads

AnswersB, C, E

A custom quota capping concurrent queries prevents runaway ad-hoc queries from monopolising slots and exhausting capacity. It satisfies the cost-control requirement by throttling query concurrency, so a single user or workload cannot overwhelm the project's shared BigQuery resources.

Why this answer

Option B is correct because setting a custom quota for concurrent queries (via BigQuery custom quotas in Cloud Console/IAM) caps the number of simultaneously running queries per project or user, preventing a flood of ad-hoc queries from exhausting slots and causing runaway costs. Option C is correct because partitioning (e.g., by ingestion/date column) and clustering (e.g., by frequently filtered columns) prune the data scanned, directly reducing bytes processed and therefore on-demand query cost. Option E is correct because BigQuery reservations with flex slots (or committed slots) let predictable reporting workloads run on dedicated capacity with fixed pricing, isolating them from unpredictable ad-hoc on-demand costs and giving cost predictability.

Option A is not correct because authorized views control data access/visibility, not query cost or runaway query prevention. Option D is not correct because materialized views can accelerate specific recurring queries but do not by themselves control costs or prevent runaway queries across a mixed workload.

Exam trap

PDE often tests the difference between cost-control mechanisms (quotas, partitioning, reservations) and access/performance mechanisms (authorized views, materialized views), causing candidates to pick visibility or caching features as cost controls.

Full explanation →

559

MCQeasy

A company wants to migrate 500 TB of on-premises archival data to Cloud Storage. The data is stored on a SAN and the network link is limited to 1 Gbps. The migration must complete within 10 days. What is the MOST cost-effective approach?

A.Set up a Cloud VPN and use rsync over the encrypted connection.

B.Use BigQuery Data Transfer Service to load the data directly into BigQuery.

C.Order a Transfer Appliance, copy data locally, and ship it to Google for ingestion.

D.Use Storage Transfer Service to copy data from on-premises to GCS over the existing network.

AnswerC

A 1 Gbps link transfers roughly 10 TB per day, so 500 TB needs about 50 days — far beyond the 10-day deadline. Transfer Appliance ships data physically, bypassing the bandwidth constraint, and is cheaper than upgrading the network link.

Why this answer

The Transfer Appliance is designed for large-scale data migrations where network bandwidth is insufficient. With 500 TB at 1 Gbps, the theoretical transfer time is over 46 days, far exceeding the 10-day window. The appliance allows you to physically ship the data, bypassing network constraints entirely, making it the most cost-effective and timely solution.

Exam trap

The trap here is that candidates underestimate the time required for network transfer at 1 Gbps and overestimate the practicality of compression or incremental sync, failing to recognize that physical shipping is the only viable option for multi-petabyte data within a tight deadline.

How to eliminate wrong answers

Option A is wrong because rsync over a 1 Gbps Cloud VPN would take approximately 46 days for 500 TB (assuming full utilization, which is unrealistic due to overhead and encryption), far exceeding the 10-day deadline. Option B is wrong because BigQuery Data Transfer Service is for loading data from SaaS applications (e.g., Google Ads, Amazon S3) or other cloud sources into BigQuery, not for ingesting on-premises archival data into Cloud Storage. Option D is wrong because Storage Transfer Service relies on the existing 1 Gbps network link, which would require over 46 days for 500 TB, violating the 10-day requirement.

Full explanation →

560

MCQhard

A data engineer is using Spark on Dataproc to process a large dataset. They notice the job is slow due to excessive shuffling. They want to optimize the job by using a more efficient data structure that reduces serialization overhead and provides better memory management. Which Spark API should they use?

A.Spark SQL

B.Spark Streaming

C.RDDs

D.DataFrames or Datasets

AnswerD

DataFrames and Datasets use Catalyst's optimised binary representation, bypassing Java serialisation and enabling off-heap Tungsten memory management. This directly reduces the serialisation overhead and excessive shuffling described in the stem, unlike RDDs, which serialise objects individually.

Why this answer

DataFrames and Datasets are built on Spark SQL's Catalyst optimizer and Tungsten execution engine, which provide schema-aware encoding and off-heap memory management. This dramatically reduces serialization overhead compared to RDDs and enables optimizations like predicate pushdown and shuffle partitioning improvements, directly addressing excessive shuffling.

Exam trap

PDE often tests the misconception that RDDs are always faster because they are 'lower level' — in fact, DataFrames/Datasets win on shuffle-heavy workloads due to Catalyst and Tungsten.

How to eliminate wrong answers

Option A is wrong because Spark SQL is the query interface/language layer, not the data structure — while DataFrames are accessed via Spark SQL, the question asks for the API/data structure that reduces serialization and improves memory management. Option B is wrong because Spark Streaming is for continuous data processing, not for optimizing shuffle-heavy batch jobs. Option C is wrong because RDDs are the low-level, untyped API that rely on Java/Kryo serialization for every shuffle and lack Catalyst/Tungsten optimizations — they are the source of the inefficiency, not the fix.

Full explanation →

561

MCQmedium

A company wants to use BigQuery materialized views to accelerate queries on a table that is updated every hour. Which statement about materialized views is true?

A.Materialized views cannot be clustered.

B.Materialized views must be manually refreshed by the user.

C.Materialized views can only be created on ingestion-time partitioned tables.

D.Materialized views are automatically updated when the base table changes.

AnswerD

BigQuery materialized views refresh automatically, but not instantly on every base-table change. They use a configurable refresh interval, with a default of 30 minutes, so hourly updates are covered. This satisfies the stem's hourly-update constraint without manual intervention, unlike logical views, which recompute on every query.

Why this answer

BigQuery materialized views are automatically refreshed when the base table changes, within a bounded staleness window, so queries against them return fresh results without manual intervention. This automatic refresh is a core property that makes them suitable for accelerating queries on hourly-updated tables.

Exam trap

PDE often tests materialized view refresh behavior and restrictions, so the trap is assuming manual refresh is required or that materialized views cannot be clustered or built on standard partitioned tables.

How to eliminate wrong answers

Option A is wrong because materialized views can be clustered, which further improves query performance and reduces bytes scanned. Option B is wrong because materialized views refresh automatically; manual refresh is not required (though you can trigger one). Option C is wrong because materialized views can be created on partitioned tables generally, not only ingestion-time partitioned tables, and can also be built on non-partitioned tables.

Full explanation →

562

MCQmedium

A healthcare company stores patient records in Cloud SQL for PostgreSQL and needs to run analytical queries on the same data without impacting the production database. The analytics team requires near-real-time replication and wants to use BigQuery for querying. The data must be kept in sync with minimal latency and without writing custom ETL code. Which solution should the data engineer implement?

A.Create a read replica of the Cloud SQL instance and point BigQuery federated queries at the replica

B.Use Datastream to replicate from Cloud SQL for PostgreSQL to BigQuery with a dataset-level replication

C.Use Cloud Data Fusion to build a pipeline that reads from Cloud SQL and writes to BigQuery on a schedule

D.Schedule a nightly export from Cloud SQL to Cloud Storage and load the files into BigQuery

AnswerB

Datastream is a serverless change data capture service that can replicate from Cloud SQL for PostgreSQL to BigQuery with low latency. It reads the write-ahead log and applies changes to BigQuery, keeping the data in sync without custom ETL. This meets the near-real-time and no-code requirements while offloading analytics from the production database.

Why this answer

Datastream provides serverless change data capture from Cloud SQL for PostgreSQL to BigQuery. It reads the database's write-ahead log and applies changes to BigQuery with low latency, keeping the analytical store in sync without custom code. This offloads analytics from the production database and meets the near-real-time and no-ETL requirements.

Other options either introduce batch latency, require pipeline development, or do not create a separate analytical store.

Exam trap

The trap here is assuming that BigQuery federated queries or read replicas provide a scalable analytical solution, when they actually query the transactional database and do not offload analytics.

Full explanation →

563

MCQeasy

A team is using Kubeflow Pipelines on Google Kubernetes Engine to orchestrate ML workflows. They need to track parameters, metrics, and artifacts for each run. Which tool should they integrate?

A.Cloud Monitoring

B.Cloud Logging

C.BigQuery

D.Vertex ML Metadata

AnswerD

Vertex ML Metadata provides a managed store for tracking parameters, metrics and artifacts per pipeline run, integrating with Kubeflow Pipelines on GKE. It satisfies the lineage requirement without self-hosting a metadata database, unlike plain GCS logging or TensorBoard alone.

Why this answer

Vertex ML Metadata is the correct choice because it is purpose-built for tracking parameters, metrics, and artifacts in ML workflows, and it integrates natively with Kubeflow Pipelines on Google Kubernetes Engine. It stores metadata for each pipeline run, enabling lineage tracking, comparison, and reproducibility of experiments.

Exam trap

Google Cloud often tests the distinction between general-purpose monitoring/logging tools and ML-specific metadata stores, so the trap here is that candidates may confuse Cloud Monitoring or Cloud Logging with a tool that can track ML metrics, when in fact they lack the structured schema and lineage capabilities required for ML workflow orchestration.

How to eliminate wrong answers

Option A is wrong because Cloud Monitoring is designed for infrastructure and application performance monitoring (e.g., CPU utilization, latency), not for tracking ML-specific parameters, metrics, and artifacts. Option B is wrong because Cloud Logging collects and stores log data (e.g., text logs from applications), not structured ML metadata like hyperparameters or model artifacts. Option C is wrong because BigQuery is a serverless data warehouse for analytical queries on large datasets, not a metadata store for ML pipeline runs.

Full explanation →

564

MCQhard

A company is using Pub/Sub to ingest clickstream events and Dataflow to write to BigQuery. They observe that some events are malformed and cause the pipeline to fail. They need a solution that captures malformed events without blocking the pipeline and allows reprocessing later. Which Dataflow pattern should they implement?

A.Use a side input to filter malformed events before the main pipeline

B.Use the Reshuffle transform to reattempt failures

C.Write malformed events to a dead letter sink (e.g., another Pub/Sub topic or GCS bucket) and continue processing healthy events

D.Use logging alerts to notify the team and stop the pipeline on error

AnswerC

Routing malformed records to a dead letter sink isolates them from the main pipeline, so healthy events continue flowing to BigQuery. The sink preserves the bad records for later inspection and reprocessing, satisfying the non-blocking capture requirement.

Why this answer

The dead letter sink pattern is the canonical Dataflow approach for handling malformed or unparseable records: instead of throwing an exception that fails the bundle (and potentially the whole pipeline), the transform routes the bad record to a secondary sink such as a Pub/Sub topic or GCS bucket while healthy events continue downstream. This preserves pipeline throughput and keeps the malformed payloads available for later inspection, correction, and reprocessing. It is the standard implementation of the dead-letter queue pattern in Apache Beam.

Exam trap

PDE often tests the misconception that side inputs or Reshuffle can handle bad records — candidates confuse data distribution/auxiliary data mechanisms with error-handling patterns, when only a dead-letter sink actually isolates failures.

How to eliminate wrong answers

Option A is wrong because a side input is a read-only auxiliary dataset broadcast to a transform (e.g., a lookup table), not a mechanism for isolating and persisting failed records. Option B is wrong because Reshuffle only redistributes elements across workers to break fusion or improve parallelism; it does not catch exceptions or retry failed elements. Option D is wrong because stopping the pipeline on error is the exact opposite of the requirement — it blocks processing of healthy events and provides no reprocessing path.

Full explanation →

565

MCQeasy

A team wants to store semi-structured user profile data for a web application. The data is accessed via a REST API and requires security rules to control read/write access. Which database fits best?

A.BigQuery

B.Firestore

C.Cloud SQL

D.Cloud Bigtable

AnswerB

Firestore stores semi-structured documents and enforces security rules directly at the database layer, satisfying the read/write access constraint without a separate authorisation tier. Its native REST API and real-time synchronisation suit web profile data, unlike relational engines requiring schema migrations or blob stores lacking granular rule enforcement.

Why this answer

Firestore is a NoSQL document database that natively stores semi-structured data (JSON-like documents) and integrates directly with Firebase Authentication and security rules to control read/write access per document or collection. Its REST API support and real-time capabilities make it ideal for web application user profiles that require flexible schemas and fine-grained access control.

Exam trap

Google often tests the distinction between NoSQL databases optimized for different workloads (document vs. wide-column vs. analytical), and the trap here is assuming any NoSQL database (like Cloud Bigtable) is suitable for semi-structured user profiles without considering the need for built-in security rules and REST API integration.

How to eliminate wrong answers

Option A is wrong because BigQuery is a serverless data warehouse designed for analytical queries on large datasets, not for transactional REST API access with per-document security rules. Option C is wrong because Cloud SQL is a relational database (MySQL, PostgreSQL, SQL Server) that requires a fixed schema and does not natively support document-level security rules or semi-structured data without additional abstraction. Option D is wrong because Cloud Bigtable is a wide-column NoSQL database optimized for high-throughput, low-latency time-series or analytical workloads, not for semi-structured user profiles with fine-grained access control via REST API.

Full explanation →

566

MCQeasy

A data engineer needs to process a large dataset (500 TB) stored in Cloud Storage using Dataproc. The processing job requires reading the entire dataset and writing results back to Cloud Storage. The job is expected to run for 6 hours. Which configuration minimizes cost?

A.Use a single-node cluster with standard VMs.

B.Use a cluster with local SSDs for faster I/O.

C.Use a cluster with a mix of standard and preemptible VMs.

D.Use a cluster with n1-highmem-32 instances and 1000 cores.

AnswerC

Preemptible VMs cost substantially less than standard VMs, and mixing them with standard workers keeps the six-hour job running while cutting compute spend. Since the workload is fault-tolerant batch processing, preemption risk is acceptable, minimising cost.

Why this answer

Preemptible VMs cost about 80% less than standard VMs, and mixing them with standard VMs provides fault tolerance for the job's 6-hour duration. Since the job reads and writes to Cloud Storage (not local HDFS), local SSDs are unnecessary, and a single-node cluster would lack the parallelism needed to process 500 TB efficiently within 6 hours. Using a mix of standard (for critical master/worker nodes) and preemptible VMs (for worker nodes) minimizes cost while ensuring job completion.

Exam trap

Google Cloud often tests the misconception that local SSDs always improve performance for data processing jobs, but in Dataproc, when data resides in Cloud Storage, the bottleneck is network throughput, not local disk speed, making SSDs an unnecessary cost.

How to eliminate wrong answers

Option A is wrong because a single-node cluster cannot process 500 TB in 6 hours due to limited CPU and memory resources, and it lacks fault tolerance if the node fails. Option B is wrong because local SSDs add cost without benefit when reading/writing from Cloud Storage, as the bottleneck is network I/O, not disk I/O; Dataproc uses Cloud Storage as the primary data source, not HDFS. Option D is wrong because using 1000 cores with n1-highmem-32 instances is over-provisioned and expensive, and the job's 6-hour runtime does not justify such a large cluster; it also ignores the cost savings of preemptible VMs.

Full explanation →

567

MCQmedium

You are running a streaming pipeline with Dataflow that reads from Pub/Sub and writes to BigQuery. You notice that the system lag metric is increasing over time, indicating that messages are taking longer to process. What is the most likely cause and how should you address it?

A.The source Pub/Sub topic has insufficient throughput; increase the number of partitions.

B.The Dataflow workers are CPU-bound; increase the number of workers or adjust autoscaling settings.

C.The BigQuery destination table has too many columns; reduce the number of columns.

D.The pipeline uses a batch transform that should be replaced with a streaming transform.

AnswerB

High system lag suggests worker resources are insufficient; adding workers reduces lag.

Why this answer

System lag in Dataflow measures the age of the oldest unprocessed message, and a steadily increasing value indicates the pipeline cannot keep up with input. The most common cause is CPU-bound workers, so scaling out workers or tuning autoscaling (e.g., max workers, worker type) restores throughput. This directly addresses the bottleneck rather than a downstream symptom.

Exam trap

The trap is picking a source-side fix (partitions) that applies to Kafka, not Pub/Sub, or blaming the sink; the exam expects you to recognize that increasing system lag in Dataflow is typically a worker capacity issue solved by autoscaling.

How to eliminate wrong answers

Option A is wrong because Pub/Sub does not use partitions; it uses subscriptions and scales via the number of messages and ack deadlines, so 'increase partitions' is a Kafka concept misapplied here. Option C is wrong because BigQuery column count does not cause processing lag; BigQuery handles wide tables efficiently and the bottleneck would be elsewhere. Option D is wrong because the pipeline is already streaming (Pub/Sub to BigQuery), so there is no batch transform to replace.

Full explanation →

568

MCQhard

A financial services company operates a real-time fraud detection pipeline using Apache Beam running on Google Cloud Dataflow. The pipeline reads transactions from Pub/Sub, enriches them with customer data from Bigtable, runs a machine learning model with side inputs from a Redis cluster, and writes results to BigQuery for downstream reporting. The data must be processed with exactly-once semantics to avoid duplicate fraud alerts or missing transactions. The pipeline currently uses a global window with 5-minute accumulation, but the team is experiencing high latency and occasional duplicates when the model side input is updated (triggered every 15 minutes via a WatchTransform). Additionally, the pipeline has a dead letter queue that outputs failed records to a separate Pub/Sub topic, but these records are never reprocessed. The team needs to ensure high reliability and data quality. Which course of action should the team take to improve solution quality?

A.Use fixed windows with a 10-minute duration and session gap of 2 minutes, disable side input caching, and log all dead letter records to Cloud Storage for manual inspection.

B.Switch to a batch processing approach that runs every minute using Cloud Composer, with data loaded from Pub/Sub into BigQuery and then processed with Dataproc to run the model.

C.Implement sliding windows of 5 minutes with a 2-minute allowed lateness, use side inputs with periodic refreshes using the .withUpdateFrequency transformation, and set up a Cloud Function to automatically replay dead letter records back to the main Pub/Sub topic after fixing the issue.

D.Keep the global window but use a custom trigger with early firings every 30 seconds and a late-firing threshold of 1 minute, and configure the side input to be broadcast every 5 minutes using a Read transform.

AnswerC

Sliding windows with allowed lateness capture late data, periodic side input refreshes reduce model update latency, and automatic reprocessing of dead letters ensures exactly-once semantics and data completeness.

Why this answer

Sliding windows with 2-minute allowed lateness handle late-arriving data without causing duplicates, and the .withUpdateFrequency transformation refreshes the side input every 15 minutes without triggering reprocessing, reducing latency. Replaying dead letter records via a Cloud Function ensures data completeness. Option A is incorrect because fixed windows with session gaps do not address side input latency and may lose late events.

Option B is incorrect because batch processing is unsuitable for real-time fraud detection and introduces significant latency. Option D is incorrect because custom triggers with early firings can cause duplicates due to side input updates, and the Read transform does not efficiently handle periodic model refreshes.

Full explanation →

569

MCQmedium

A retail company needs to generate product recommendations for millions of users every few hours. The model is a small scikit-learn model. Which prediction method should be used to minimize infrastructure cost while meeting the latency requirements?

A.Use Cloud Run to host the model and invoke it for each user request.

B.Export the model as a container and run on Google Kubernetes Engine with cluster autoscaling.

C.Deploy the model to a Vertex AI endpoint with a single replica for online predictions.

D.Use a Vertex AI batch prediction job that reads from BigQuery and writes results back to BigQuery or Cloud Storage.

AnswerD

Batch prediction processes the entire input dataset in a single managed job, so Vertex AI provisions resources only for the job's duration rather than continuously. This satisfies the stem's cost-minimisation constraint while handling millions of users every few hours.

Why this answer

Batch prediction is the most cost-effective approach for generating recommendations for millions of users every few hours. Vertex AI batch prediction jobs process large datasets in parallel without maintaining always-on infrastructure, and they can read from BigQuery and write results directly to BigQuery or Cloud Storage, minimizing compute costs while meeting the latency requirement of 'every few hours' (not real-time).

Exam trap

Google Cloud often tests the distinction between online (real-time) and batch (asynchronous) prediction patterns, and the trap here is that candidates assume 'predictions' always require a live endpoint, overlooking that batch jobs are the correct choice when latency requirements are in hours and the workload is massive and periodic.

How to eliminate wrong answers

Option A is wrong because Cloud Run invokes the model per user request, which would require millions of individual invocations every few hours, leading to high request-based costs and potential cold-start latency issues that are unnecessary for a batch workload. Option B is wrong because Google Kubernetes Engine with cluster autoscaling is overkill for a small scikit-learn model and introduces cluster management overhead and always-on node costs, even with autoscaling, making it more expensive than a serverless batch solution. Option C is wrong because a Vertex AI endpoint with a single replica is designed for online (real-time) predictions, which would be idle most of the time between the batch windows, incurring continuous compute costs for a single replica that is not needed for a scheduled batch job.

Full explanation →

570

MCQmedium

A data platform team uses Cloud Composer 2 to orchestrate a DAG that runs a Dataproc Serverless batch job producing a partitioned BigQuery table. The DAG passes the output location to a downstream task that runs a dbt model. The team wants failures in the dbt task to automatically trigger a retry of only that task, and they want the DAG to expose the Dataproc job ID in the Airflow UI for troubleshooting. Which approach BEST satisfies both requirements?

A.Use the DataprocSubmitJobOperator and configure the DAG's default_args with retries, which applies the retry policy to all tasks including the dbt task.

B.Use a single PythonOperator that submits the Dataproc job via the API, waits for completion, and then invokes dbt, with retries set on the PythonOperator.

C.Use the DataprocSubmitJobOperator and pass the returned job reference to XCom, then set retries on the dbt task and read the XCom value in its template fields.

D.Use the DataprocSubmitJobOperator with retries set on it, and add a separate downstream PythonOperator that runs dbt without its own retry configuration.

AnswerC

DataprocSubmitJobOperator returns the job reference, which Airflow automatically pushes to XCom, making the job ID visible and available downstream. Setting retries on the dbt task ensures only that task is retried on failure, and using XCom in template fields lets the dbt task consume the job ID or output path. This satisfies both the observability and granular retry requirements.

Why this answer

DataprocSubmitJobOperator returns a job reference that Airflow pushes to XCom, which makes the job ID available for downstream tasks and visible in the Airflow UI. Configuring retries specifically on the dbt task ensures that a transient failure there triggers only that task to rerun, leaving the already-successful Dataproc submission intact. This combination precisely meets both the observability and granular retry goals.

Exam trap

The trap here is setting retries on the upstream Dataproc operator or in default_args, when the requirement is to retry only the failing dbt task.

Full explanation →

571

MCQhard

You have a BigQuery table 'logs' with a column 'timestamp' of type TIMESTAMP. You need to create a new table that contains only the logs from the last 7 days, partitioned by day on the 'timestamp' column. Which SQL statement should you use?

A.CREATE TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

B.CREATE TABLE new_logs PARTITION BY timestamp AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

C.CREATE TABLE new_logs AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

D.CREATE TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

AnswerD

This statement creates a new table partitioned by day on the 'timestamp' column and populates it with data from the last 7 days. The PARTITION BY DATE(timestamp) clause ensures that the table is partitioned by day, which improves query performance and reduces cost. The WHERE clause filters the data appropriately. This is the correct syntax for creating a partitioned table from a query.

Why this answer

To create a partitioned table from a query, you must use the CREATE TABLE ... PARTITION BY clause followed by the partitioning expression and then AS SELECT. Partitioning by DATE(timestamp) is correct for daily partitions.

The WHERE clause should filter on the timestamp column using TIMESTAMP_SUB to include all records from the last 7 days, not just those from the last 7 calendar days.

Exam trap

The trap here is using DATE(timestamp) in the filter instead of TIMESTAMP_SUB, which can exclude events from the beginning of the 7-day window due to time-of-day truncation.

Full explanation →

572

MCQmedium

A data engineer needs to query data across BigQuery (in Google Cloud) and Snowflake (in AWS) without moving the data. Which service should they use?

A.Dataflow

B.Cloud SQL

C.Vertex AI Feature Store

D.BigQuery Omni

AnswerD

BigQuery Omni supports multi-cloud analytics without data movement.

Why this answer

BigQuery Omni allows querying data across multiple clouds using BigQuery's interface, with compute running in the respective cloud. Data stays in place.

Full explanation →

573

MCQhard

A streaming pipeline ingests events from Pub/Sub, enriches them via a slow REST API call, and writes the result to BigQuery. The API has a limit of 10 requests per second per client. The pipeline processes 1000 messages per second. Which approach minimizes latency while respecting API limits?

A.Use a global window with a trigger that fires every second, and inside the DoFn limit concurrent API calls to 10.

B.Fan out the stream to multiple REST API instances using Pub/Sub topic splitting.

C.Use a Dataflow Flex Template to run multiple pipelines, each processing a subset of messages.

D.Assign each message a random key and use a sliding window of 10 seconds; the API call will be distributed across workers.

AnswerA

Groups messages into batches per second, then controls concurrency to stay within the 10 req/s limit.

Why this answer

Using a global window with a trigger every second groups 1000 messages into a batch, and then throttling concurrent API calls to 10 within the DoFn (e.g., using a fixed-size thread pool) respects the API limit while minimizing latency by processing messages in parallel up to the limit. Option B is wrong because fanning out to multiple API instances doesn't help if the limit is per client; the total requests per second across all instances would still exceed the client limit. Option C is wrong because Dataflow Flex Templates are used to run parameterized pipelines, not to solve throttling issues.

Option D is wrong because assigning a random key and using a sliding window distributes messages across workers, but without explicit throttling, the API limit could still be exceeded.

Full explanation →

574

MCQeasy

You need to choose a messaging service for a real-time streaming application that requires low cost and can tolerate occasional message loss. Which service is MOST suitable?

A.Cloud Scheduler

B.Pub/Sub Lite

C.Pub/Sub

D.Cloud Tasks

AnswerB

Pub/Sub Lite provides zonal, low-cost messaging with no replication across zones, so it tolerates occasional message loss while meeting the low-cost constraint. Standard Pub/Sub replicates messages, incurring higher cost for durability the scenario does not require.

Why this answer

Pub/Sub Lite is designed for high-volume, low-cost streaming where occasional message loss is acceptable. Unlike standard Pub/Sub, it uses zonal or regional storage with pre-provisioned capacity, which dramatically lowers cost but does not guarantee the same durability or at-least-once delivery semantics under all failure conditions. This makes it the right fit when cost is prioritized over guaranteed delivery.

Exam trap

PDE often tests the distinction between Pub/Sub and Pub/Sub Lite by emphasizing cost tolerance for message loss — candidates incorrectly default to standard Pub/Sub for 'streaming' without weighing the durability/cost trade-off.

How to eliminate wrong answers

Option A is wrong because Cloud Scheduler is a cron-style job trigger, not a messaging/streaming service and cannot handle real-time message throughput. Option C is wrong because standard Pub/Sub provides strong durability and at-least-once delivery with replication, which is more expensive than needed when message loss is tolerable. Option D is wrong because Cloud Tasks is a task queue for asynchronous HTTP callbacks and single-consumer work items, not a high-throughput streaming messaging system.

Full explanation →

575

MCQeasy

A data engineer needs to store JSON documents in a Google Cloud database that must scale horizontally, support automatic multi-region replication, and provide strong consistency for reads. The application performs many small reads and writes keyed by a document ID, and the team wants to avoid managing servers. They also need the ability to run SQL-like queries on the documents. Which Google Cloud service should the engineer choose?

A.Cloud SQL for PostgreSQL with a read replica in each region.

B.Cloud Spanner with a multi-region configuration.

C.Bigtable with a single cluster in one region and a replication policy to a second region.

D.Firestore in Native mode with multi-region location.

AnswerD

Firestore in Native mode is a serverless, horizontally scalable document database with automatic multi-region replication and strong consistency for reads and writes. It supports document IDs as keys, handles many small reads and writes efficiently, and provides SQL-like queries through its query API. It requires no server management. This matches the requirements for scale, multi-region replication, strong consistency, and document storage. Firestore also supports real-time listeners and offline sync for mobile, but the core fit here is the document model and strong consistency.

Why this answer

Firestore in Native mode is the Google Cloud document database that provides automatic multi-region replication, strong consistency, horizontal scaling, and a SQL-like query API. It is serverless, so the team does not manage servers. The other options either lack strong consistency across regions, are relational rather than document-oriented, or are designed for different workloads.

Firestore's document ID key model and small read/write performance align with the application's access pattern.

Exam trap

The trap here is confusing Cloud Spanner's strong consistency and SQL support with document storage, when Spanner is a relational database with a fixed schema.

Full explanation →

576

MCQeasy

A company needs a fully managed, globally distributed relational database with strong consistency, external consistency, and 99.999% SLA for a financial transaction processing system. Which Google Cloud service should they use?

A.Firestore

B.Cloud Spanner

C.Bigtable

D.Cloud SQL

AnswerB

Cloud Spanner is the only Google Cloud relational database offering external consistency globally through TrueTime, satisfying the financial system's strict consistency requirement. Its multi-region configuration delivers the 99.999% SLA, and it is fully managed, unlike self-managed alternatives such as Cloud SQL or Bare Metal Solution.

Why this answer

Cloud Spanner is the correct choice because it is a fully managed, globally distributed relational database service that provides strong consistency, external consistency (true serializable transactions across regions), and a 99.999% SLA. These features are essential for a financial transaction processing system that requires ACID compliance and global scalability without sacrificing consistency.

Exam trap

The trap here is that candidates often confuse Cloud Spanner with Bigtable or Firestore because all three are globally distributed, but only Spanner offers the relational model, strong consistency, and the 99.999% SLA required for financial transactions.

How to eliminate wrong answers

Option A (Firestore) is wrong because it is a NoSQL document database that does not support relational queries or strong consistency across global distributions (it offers eventual consistency by default). Option C (Bigtable) is wrong because it is a wide-column NoSQL database designed for high-throughput analytical workloads, not relational transactions, and it does not provide SQL support or ACID transactions. Option D (Cloud SQL) is wrong because it is a regional relational database service that cannot provide global distribution or a 99.999% SLA; it supports only single-region deployments with limited failover.

Full explanation →

577

MCQmedium

Your team uses Cloud Dataproc for Spark ML training jobs. You want to reduce costs for non-critical, fault-tolerant training jobs. Which Dataproc feature should you use for worker nodes?

A.Use preemptible instances for worker nodes.

B.Use custom machine types with more memory.

C.Use SSDs instead of HDDs for persistent disks.

D.Use committed use discounts for 1-year or 3-year terms.

AnswerA

Preemptible instances suit fault-tolerant Spark training because Dataproc replaces them automatically when Compute Engine reclaims capacity, and they cost substantially less than standard VMs. The stem's non-critical, fault-tolerant constraint is satisfied: interrupted workers are re-added without failing the job, unlike sole-primary or non-retryable workloads.

Why this answer

Preemptible instances are short-lived, lower-cost VMs that Cloud Dataproc can use for worker nodes. Because the training jobs are non-critical and fault-tolerant (e.g., they can handle node failures via Spark's built-in resilience), preemptible instances significantly reduce costs while still completing the workload. This directly addresses the requirement to reduce costs for fault-tolerant jobs.

Exam trap

A common pitfall is confusing committed use discounts (which require a 1- or 3-year commitment) with preemptible VMs (which are interruptible but cost-effective). For non-critical, fault-tolerant workloads, preemptible VMs are the appropriate cost-saving feature, not committed use discounts.

How to eliminate wrong answers

Option B is wrong because custom machine types with more memory increase cost per node, which contradicts the goal of reducing costs. Option C is wrong because SSDs are more expensive than HDDs, and while they improve I/O performance, the question focuses on cost reduction, not performance. Option D is wrong because committed use discounts require a 1-year or 3-year commitment and are typically applied to all instances in a project, not specifically to worker nodes in a Dataproc cluster; they also do not leverage the fault-tolerant nature of the jobs to achieve the lowest possible cost.

Full explanation →

578

MCQhard

A financial services company uses Vertex AI to serve a fraud detection model. The model was trained on historical data that is updated daily. The team wants to automate retraining when data drift is detected. Which approach best operationalizes this requirement with minimal manual intervention?

A.Use Cloud Monitoring alerts on prediction latency to trigger a retraining pipeline.

B.Manually monitor model performance metrics in Vertex AI Experiments and retrain when accuracy drops.

C.Use scheduled Vertex AI Pipelines to retrain the model every night, then deploy automatically.

D.Enable Vertex AI Model Monitoring for feature drift and skew, then create a Cloud Function that triggers a Vertex AI Pipeline to retrain and deploy the model after validation.

AnswerD

Vertex AI Model Monitoring detects feature drift and skew, and a Cloud Function responds by triggering a Vertex AI Pipeline that retrains, validates, and redeploys the model. This closes the loop automatically, satisfying the minimal-manual-intervention constraint for daily-updated fraud data.

Why this answer

It uses Vertex AI Model Monitoring to automatically detect feature drift or skew, then triggers a Cloud Function that invokes a Vertex AI Pipeline to retrain and redeploy the model after validation. This approach minimizes manual intervention by automating both the detection of data drift and the subsequent retraining and deployment lifecycle.

Exam trap

Google Cloud often tests the distinction between scheduled retraining (Option C) and event-driven retraining triggered by actual drift detection (Option D), where candidates mistakenly choose the simpler scheduled approach without recognizing that it ignores the requirement to retrain only when drift is detected.

How to eliminate wrong answers

Option A is wrong because prediction latency is unrelated to data drift; monitoring latency only detects performance issues, not changes in data distribution. Option B is wrong because manually monitoring metrics in Vertex AI Experiments requires human intervention and does not automate retraining, contradicting the requirement for minimal manual intervention. Option C is wrong because scheduled nightly retraining ignores whether data drift has actually occurred, leading to unnecessary retraining and potential deployment of models that are not improved, and it does not use drift detection as the trigger.

Full explanation →

579

MCQhard

A logistics company ingests GPS pings from delivery vans into Pub/Sub, and a Dataflow streaming pipeline writes them to BigQuery. Latency requirements are lenient (about 5 minutes), but the finance team needs the pipeline's cost to be predictable and low, and the data volume fluctuates by a factor of ten between day and night. The team wants to minimize per-element cost without losing data. Which configuration should the data engineer choose?

A.Streaming Engine with autoscaling and a bounded maximum worker count, plus windowing with a 5-minute trigger so BigQuery writes are batched.

B.Batch mode with a 5-minute micro-batch schedule that reads Pub/Sub snapshots and writes to BigQuery.

C.Streaming Engine with autoscaling, a low --maxNumWorkers, and a streaming trigger that emits results at least every 5 minutes.

D.Streaming Engine with a fixed worker count sized for peak daytime load and --maxNumWorkers capped at that value.

AnswerA

Autoscaling lets Dataflow add workers during the daytime surge and shrink at night, matching cost to load while a bounded maximum keeps spend predictable. Windowing with a five-minute trigger batches BigQuery writes, which reduces per-row streaming insert overhead and cost. This satisfies the lenient latency target while keeping finance's cost model stable and avoiding data loss.

Why this answer

The scenario combines wildly variable throughput with a lenient latency target and a hard cost ceiling. Autoscaling with a bounded maximum worker count is the standard way to match capacity to demand while capping worst-case spend, and a five-minute windowing trigger amortizes BigQuery write overhead across many elements. Together these choices honor the latency allowance, keep costs predictable, and avoid the data-loss risk of an undersized fixed pool.

Exam trap

The trap here is treating low cost as always meaning fewer workers, when the real requirement is elasticity in both directions with a bounded maximum.

Full explanation →

580

MCQhard

A data engineer is designing a Cloud Storage bucket for a machine learning training pipeline. The pipeline writes many small Parquet files (about 1 MB each) from a Dataflow job, and a downstream training job reads them sequentially. The engineer wants to minimize the number of storage operations and improve read throughput. The bucket uses Standard storage class and has no lifecycle rules. What should the engineer do?

A.Change the bucket storage class to Nearline and add a lifecycle rule to transition objects after 30 days.

B.Enable Requester Pays on the bucket so that the training job pays for egress and operations.

C.Enable Object Versioning on the bucket and set a lifecycle rule to delete noncurrent versions after 7 days.

D.Configure the Dataflow job to write larger Parquet files, for example by using a longer window or by grouping writes into 128 MB to 512 MB files.

AnswerD

Writing larger Parquet files reduces the number of objects, which lowers the number of storage operations (PUT and GET) and improves sequential read throughput because the training job opens fewer files and can read larger contiguous ranges. Parquet is columnar; larger files also improve compression and encoding efficiency. The engineer can adjust the Dataflow pipeline to batch writes, use a larger shard size, or repartition before writing. This directly addresses both the operation count and read performance.

Why this answer

The core issue is many small files, which increases the number of Cloud Storage operations and reduces read throughput because each file requires separate open, metadata, and read operations. Writing larger Parquet files, such as 128 MB to 512 MB, reduces object count and allows the training job to read contiguous data more efficiently. This is a common Dataflow and Cloud Storage optimization: adjust the pipeline to produce fewer, larger shards.

Other options address durability, cost class, or billing, not the operation-count and throughput problem.

Exam trap

The trap here is focusing on storage class or versioning as a performance fix, when the real bottleneck is the number and size of objects being read.

Full explanation →

581

Multi-Selecthard

Your company has a Dataproc cluster that runs Spark jobs. You need to choose between RDDs, DataFrames, and Datasets for a new job that performs complex aggregations on structured data. Which TWO statements are correct regarding performance and ease of use?

Select 2 answers

A.DataFrames and Datasets are both available in PySpark.

B.DataFrames store data in a columnar format, allowing better compression.

C.RDDs are easier to use than DataFrames for complex aggregations.

D.DataFrames are optimized by Spark's Catalyst optimizer, leading to faster execution.

E.Datasets provide compile-time type safety and are always faster than DataFrames.

AnswersB, D

DataFrames use a columnar in-memory representation with Catalyst optimisation and Tungsten encoding, yielding better compression and scan efficiency than row-based RDDs. For complex aggregations on structured data, this columnar layout reduces I/O and memory footprint, improving performance.

Why this answer

Option B is correct because Spark DataFrames are built on Tungsten's columnar in-memory representation, which stores data by column and enables more efficient encoding, compression, and cache-friendly scans than RDDs' row-based Java/Python objects. Option D is correct because DataFrame operations are analyzed and rewritten by the Catalyst optimizer, which applies rule-based and cost-based optimizations such as predicate pushdown, column pruning, and join reordering, producing faster execution plans than hand-written RDD transformations. Option A is incorrect because Datasets are not available in PySpark; they exist only in the Scala and Java APIs, while PySpark offers DataFrames (and RDDs).

Option C is incorrect because RDDs are lower-level and require manual implementation of aggregation logic, making them harder, not easier, than DataFrames for complex aggregations. Option E is incorrect because, although Datasets add compile-time type safety in Scala/Java, they are not always faster than DataFrames and often incur serialization overhead; the claim of being 'always faster' is false.

Exam trap

PDE often tests the misconception that Datasets are always faster or that they exist in PySpark — candidates who assume 'typed = better performance' or 'Python has Datasets' pick the wrong answers.

Full explanation →

582

MCQhard

A Dataflow streaming pipeline reads from Pub/Sub, applies a ParDo that uses a side input from a BigQuery table (refreshed hourly), and writes to BigQuery. The side input is large and causes increased latency and worker OOM errors. Which design change solves this?

A.Use a stateful ParDo and store the lookup data in an external cache like Cloud Bigtable, performing lookups per element.

B.Increase the side input broadcast frequency to update more often.

C.Split the pipeline into two: one to load the side input, the other to process main input.

D.Use smaller worker machine types to distribute memory across more workers.

AnswerA

Moving the lookup data into Cloud Bigtable and querying it per element removes the large side input that Apache Beam materialises in worker memory, eliminating the OOM errors and latency. This satisfies the constraint that the hourly-refreshed BigQuery table is too large for side-input use.

Why this answer

Moving the large lookup data to an external cache like Cloud Bigtable offloads memory pressure from workers, eliminating OOM errors. The side input broadcast approach keeps the entire dataset in each worker's memory, which causes OOM when the data is large. Using an external cache allows per-element lookups without storing the entire dataset in memory, reducing latency by avoiding broadcast overhead.

Exam trap

Google Cloud often tests the misconception that increasing resources (like worker size or frequency) solves memory issues, when the real solution is to avoid storing large datasets in memory altogether by using an external lookup service.

How to eliminate wrong answers

Option B is wrong because increasing the broadcast frequency would make the OOM and latency problems worse, as it would reload the large dataset into memory more often without reducing memory footprint. Option C is wrong because splitting the pipeline into two pipelines does not solve the fundamental issue of storing the large side input in memory; the side input would still need to be broadcast or cached, and the two pipelines would require coordination, adding complexity without addressing memory pressure. Option D is wrong because using smaller worker machine types reduces available memory per worker, which would exacerbate OOM errors and increase latency due to more frequent garbage collection and slower processing.

Full explanation →

583

MCQhard

A data engineer must store JSON documents for a product catalog in Cloud Firestore. Some documents contain nested arrays of product variants that can exceed 1 MiB when serialized. The engineer wants to keep the catalog queryable with strong consistency and low latency. What should the engineer do?

A.Move the entire catalog to Cloud Bigtable and use row keys to model variant relationships.

B.Store each variant as a separate document in a subcollection and keep only summary fields in the parent document.

C.Store the variants as a base64-encoded string field inside the parent document.

D.Increase the Firestore document size limit by enabling the Enterprise edition of Firestore.

AnswerB

Firestore enforces a 1 MiB document size limit, so moving large nested arrays into a subcollection keeps each document within the limit. Queries remain strongly consistent and low latency because subcollection documents are still part of the same Firestore database and can be queried directly.

Why this answer

Firestore imposes a hard 1 MiB limit per document, so large nested arrays must be split across documents. Moving variants into a subcollection keeps each document within the limit while preserving strong consistency and low-latency queries, since subcollection documents are first-class documents in the same database.

Exam trap

The trap here is assuming a paid or enterprise tier removes the Firestore 1 MiB document size limit, when in fact that limit is fixed across editions.

Full explanation →

584

MCQmedium

A company wants to use AutoML Tables to build a classification model on a dataset with 100 features and 500,000 rows. They need to deploy the model for online predictions with low latency (<100 ms). Which deployment option should they choose?

A.Export the model as a TF SavedModel and deploy on Cloud Run

B.Deploy the model on AI Platform Prediction

C.Deploy the model to an endpoint in Vertex AI using the AutoML endpoint service

D.Use batch prediction in Vertex AI

AnswerC

Deploying to a Vertex AI endpoint serves AutoML Tables models for online prediction with the low-latency response the stem requires. Batch prediction cannot meet sub-100 ms per-request latency, and BigQuery ML export does not provide managed online serving.

Why this answer

AutoML Tables models are natively served through Vertex AI endpoints, which provide a managed, low-latency online prediction service. Deploying to a Vertex AI endpoint using the AutoML endpoint service preserves the AutoML model format and gives the sub-100 ms latency required for online predictions.

Exam trap

The trap is assuming AutoML models can be exported and self-hosted like custom-trained models — AutoML Tables models are only servable through Vertex AI endpoints, not Cloud Run or legacy AI Platform Prediction.

How to eliminate wrong answers

Option A is wrong because AutoML Tables models cannot be exported as TF SavedModels for arbitrary serving on Cloud Run — the export path is not supported for AutoML Tables. Option B is wrong because AI Platform Prediction is the legacy service and does not host AutoML Tables models in the current Vertex AI workflow. Option D is wrong because batch prediction is asynchronous and designed for bulk scoring, not sub-100 ms online requests.

Full explanation →

585

MCQhard

A healthcare company needs to process HL7 messages containing sensitive patient data. The messages arrive in Cloud Storage as JSON files. The pipeline must de-identify the data using the Cloud Healthcare API DLP de-identification, then load the results into BigQuery. The pipeline must ensure that no unredacted data is ever written to BigQuery, and that processing is fault-tolerant. The Dataflow pipeline reads from Cloud Storage, calls the DLP API for de-identification, and writes to BigQuery. Which additional configuration ensures that only de-identified data reaches BigQuery?

A.Use a ParDo that calls the DLP API and writes the de-identified data to BigQuery; if the DLP call fails, retry indefinitely until success.

B.Use a DoFn that calls the DLP API and only emits the de-identified record; then use a BigQueryIO write with a dead-letter queue for failed DLP calls.

C.Use a ParDo transform that calls the DLP API and then writes the original and de-identified data to separate BigQuery tables, with access controls on the original table.

D.Use a GroupByKey to batch records, then call the DLP API in a batch request; write both original and de-identified data to BigQuery with column-level encryption.

AnswerB

By only emitting de-identified records, the pipeline ensures that unredacted data never reaches BigQuery. Using a dead-letter queue for failed DLP calls prevents data loss and allows reprocessing. This design is fault-tolerant and meets the strict requirement that no original data is written to BigQuery. It also handles API errors gracefully without compromising data privacy.

Why this answer

The pipeline must ensure that only de-identified data is written to BigQuery. Emitting only de-identified records from the DoFn guarantees that unredacted data never reaches the sink. A dead-letter queue for failed DLP calls maintains fault tolerance by isolating problematic records for later analysis or reprocessing, rather than blocking the pipeline or writing original data.

This design meets both privacy and reliability requirements.

Exam trap

The trap here is assuming that encryption or access controls on original data in BigQuery are sufficient, but the requirement explicitly forbids any unredacted data in BigQuery.

Full explanation →

586

MCQmedium

You have a BigQuery table `orders` with columns `order_id` (STRING), `customer_id` (STRING), `order_date` (DATE), and `amount` (NUMERIC). You need to create a view that shows, for each order, the cumulative sum of `amount` for that customer, ordered by `order_date` ascending. Which SQL window function or clause should you use?

A.SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

B.SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

C.SUM(amount) OVER (ORDER BY order_date PARTITION BY customer_id)

D.SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) WITH TIES

AnswerA

This window function correctly partitions by customer_id, orders by order_date, and defines the frame from the start of the partition to the current row, producing a running total per customer. The ROWS clause ensures cumulative sum includes all previous rows and the current row, which is exactly what is needed for each order.

Why this answer

To compute a cumulative sum per customer ordered by date, you need a window function with PARTITION BY customer_id and ORDER BY order_date, and an explicit frame that includes all preceding rows up to the current row. The ROWS frame ensures that each row's sum includes only rows up to that point, avoiding grouping of same-date orders. This yields a true running total for each order.

Exam trap

The trap here is confusing ROWS and RANGE frames; RANGE includes peers with the same ORDER BY value, which can inflate the running total when multiple orders share the same date.

Full explanation →

587

MCQhard

Based on the exhibit, what is the most likely cause of duplicate rows despite using the same event_id as insertId?

A.BigQuery's streaming buffer deduplication is best-effort and may not catch duplicates within a short time window.

B.The Dataflow pipeline is retrying inserts due to network errors, and the same event_id is not being used in retries.

C.The pipeline is writing more than 100,000 rows per second, exceeding BigQuery's streaming quota.

D.The table is partitioned by timestamp, so BigQuery cannot deduplicate across partitions.

AnswerA

BigQuery's insertId deduplication operates only within the streaming buffer and is best-effort, not guaranteed. Duplicates arriving in separate buffer windows, or after buffer flush, bypass the check entirely, so identical event_id values still produce duplicate rows.

Why this answer

BigQuery's streaming buffer uses best-effort deduplication based on the `insertId` field. When multiple rows are inserted with the same `event_id` mapped to `insertId` within a short time window (typically up to a few minutes), the deduplication mechanism may fail to remove all duplicates, especially under high throughput or network retries. This is a documented limitation of BigQuery streaming, not a guarantee of exactly-once semantics.

Exam trap

Google Cloud often tests the misconception that BigQuery's streaming deduplication is a strong guarantee, when in fact it is best-effort and can fail under concurrent writes or short time windows.

How to eliminate wrong answers

Option B is wrong because if the same `event_id` is not used in retries, BigQuery would treat them as distinct rows and not deduplicate, but the question states the same `event_id` is used as `insertId`; the issue is that deduplication is best-effort, not that the ID is missing. Option C is wrong because exceeding the streaming quota (default 100,000 rows per second per table) would cause ingestion errors or throttling, not duplicate rows; duplicates arise from the buffer's deduplication behavior, not quota limits. Option D is wrong because BigQuery can deduplicate across partitions within the streaming buffer; partitioning does not disable deduplication, and duplicates can occur even in a single partition due to the buffer's best-effort nature.

Full explanation →

588

MCQmedium

A team wants to use Cloud Storage to build a data lake with separate zones for raw, curated, and processed data. They need to automatically move objects older than 30 days from the raw zone to a cheaper storage class. How can they achieve this?

A.Set a bucket retention policy that forces deletion after 30 days

B.Write a Cloud Function to delete objects older than 30 days

C.Use gsutil rsync to move objects between buckets

D.Configure a Cloud Storage object lifecycle rule with SetStorageClass action

AnswerD

Object lifecycle rules evaluate objects against age conditions and apply SetStorageClass to transition them automatically, satisfying the 30-day raw-zone requirement without manual intervention. This is the native Cloud Storage mechanism for cost-tiering, unlike bucket-level retention or transfer jobs.

Why this answer

Cloud Storage object lifecycle management rules can automatically transition objects from one storage class to a cheaper one (e.g., from Standard to Nearline or Coldline) based on age. By configuring a rule with the `SetStorageClass` action and a `Condition` of `age: 30`, objects in the raw zone bucket older than 30 days are moved to a lower-cost class without manual intervention or additional compute services.

Exam trap

Google often tests the distinction between lifecycle rules that change storage class versus retention policies that enforce immutability, and candidates may confuse 'move to cheaper storage' with 'delete' or 'retain'.

How to eliminate wrong answers

Option A is wrong because a retention policy prevents deletion or modification of objects until the retention period expires; it does not move objects to a cheaper storage class and would lock the data, not transition it. Option B is wrong because while a Cloud Function could delete objects, the requirement is to move them to a cheaper storage class, not delete them; using a function for this is also less efficient and more complex than a native lifecycle rule. Option C is wrong because `gsutil rsync` synchronizes content between buckets but does not automatically trigger based on age; it requires manual or scheduled execution and does not natively support storage class transitions based on object age.

Full explanation →

589

MCQmedium

An organization needs to restrict access to BigQuery and Cloud Storage so that data can only be accessed from within a specific VPC network and cannot be exfiltrated. Which Google Cloud feature should they use?

A.Private Service Access

B.VPC Service Controls

C.VPC firewall rules

D.IAM conditions

AnswerB

VPC Service Controls builds a service perimeter around BigQuery and Cloud Storage, permitting access only from within the specified VPC network and blocking data exfiltration to unauthorised projects. This directly satisfies the constraint that data remain accessible solely from inside the defined network boundary.

Why this answer

VPC Service Controls (option B) is the correct choice because it creates a security perimeter around Google Cloud services like BigQuery and Cloud Storage, preventing data exfiltration even from within a VPC. It enforces context-aware access based on the VPC network, ensuring data can only be accessed from authorized VPC sources and blocking unauthorized transfers outside the perimeter.

Exam trap

The trap here is that candidates confuse VPC firewall rules (which control network traffic) with VPC Service Controls (which control data access at the API layer), leading them to choose firewall rules because they think 'restricting access to a VPC' is purely a network-level concern.

How to eliminate wrong answers

Option A is wrong because Private Service Access is used to enable private connectivity from a VPC to Google-managed services (e.g., Cloud SQL, Memorystore) via internal IPs, but it does not provide exfiltration prevention or restrict data movement across services. Option C is wrong because VPC firewall rules control network traffic at the packet level (IP addresses, ports, protocols) but cannot prevent data exfiltration via API calls or service-to-service transfers, as they operate at layers 3/4, not at the application layer. Option D is wrong because IAM conditions allow fine-grained access control based on attributes like IP address or time, but they do not create a perimeter around services; they can restrict who can call an API but cannot block data movement between services or prevent exfiltration via authorized credentials.

Full explanation →

590

MCQmedium

A data pipeline uses Cloud Composer to orchestrate Dataflow and BigQuery jobs. The pipeline fails intermittently with dependency errors. Which design change can improve reliability?

A.Use retries with exponential backoff

B.Switch to Cloud Functions for orchestration

C.Increase worker count in Dataflow

D.Use a simpler DAG with fewer dependencies

AnswerA

Intermittent dependency errors arise when upstream Dataflow or BigQuery jobs finish late, so retries with exponential backoff let dependent tasks re-attempt after transient delays rather than failing outright. This directly addresses the unreliable cross-service dependencies named in the stem, raising pipeline reliability.

Why this answer

Cloud Composer (Apache Airflow) tasks can fail due to transient issues like API rate limits or resource contention. Implementing retries with exponential backoff allows the DAG to automatically re-attempt failed tasks with increasing delays, reducing the impact of intermittent failures without manual intervention. This is a standard Airflow pattern for improving reliability in orchestrated pipelines.

Exam trap

Google Cloud often tests the distinction between scaling compute resources (Dataflow workers) and improving orchestration reliability (retries), leading candidates to mistakenly choose option C when the problem is transient task failures, not resource bottlenecks.

How to eliminate wrong answers

Option B is wrong because Cloud Functions is a serverless compute service, not a workflow orchestrator; it lacks built-in support for managing task dependencies, retries, and scheduling across multiple services like Dataflow and BigQuery. Option C is wrong because increasing the Dataflow worker count addresses throughput and latency, not dependency errors in the orchestration layer; dependency errors stem from task sequencing or transient failures in Airflow, not from Dataflow parallelism. Option D is wrong because simplifying the DAG reduces complexity but does not handle intermittent failures; the core issue is transient errors, not the number of dependencies, and removing dependencies may break business logic.

Full explanation →

591

MCQeasy

A data engineer needs to orchestrate a complex data pipeline that involves multiple steps including data extraction from Cloud Storage, transformation using Dataflow, and loading into BigQuery. The pipeline has dependencies between tasks and requires monitoring and retries. Which Google Cloud service should be used for orchestration?

A.Workflows

B.Cloud Scheduler

C.Cloud Composer

D.Cloud Tasks

AnswerC

Cloud Composer provides managed Apache Airflow, whose directed acyclic graphs express task dependencies, scheduling, retries and monitoring across extraction, Dataflow transformation and BigQuery loading. This satisfies the stem's requirement for orchestration of a multi-step pipeline with dependencies and retry handling.

Why this answer

Cloud Composer is a fully managed workflow orchestration service built on Apache Airflow. It is designed to orchestrate complex data pipelines with dependencies, scheduling, monitoring, and retries, making it the correct choice for orchestrating extraction, transformation, and loading tasks across Google Cloud services.

Exam trap

PDE often tests the distinction between orchestration services; candidates may choose Workflows because it is also an orchestration tool, but Cloud Composer is specifically designed for data pipelines with Airflow.

How to eliminate wrong answers

Option A is wrong because Workflows is a serverless orchestration service for HTTP-based APIs and services, but it lacks the rich scheduling and dependency management of Airflow for data pipelines. Option B is wrong because Cloud Scheduler is a cron job service that triggers jobs but does not orchestrate multi-step pipelines with dependencies. Option D is wrong because Cloud Tasks is a task queue service for asynchronous task execution, not a pipeline orchestration tool.

Full explanation →

592

MCQhard

A Dataflow pipeline reads from Cloud Pub/Sub and writes to Cloud Storage. The pipeline needs to guarantee exactly-once processing despite worker failures. Which configuration ensures exactly-once semantics?

A.Use a side input from a deduplication dataset

B.Set the pipeline to use a global window with no early triggers

C.Insert a Reshuffle transform after reading

D.Enable exactly-once delivery on the Pub/Sub subscription and use an idempotent sink

AnswerD

Pub/Sub exactly-once delivery and an idempotent Storage write (e.g., using file naming) ensure no duplicates.

Why this answer

Pub/Sub subscriptions can be configured with exactly-once delivery (using the `enableExactlyOnceDelivery` flag), which ensures that each message is delivered to the subscriber exactly once. Combining this with an idempotent sink (e.g., Cloud Storage with unique filenames or deduplication logic) guarantees that even if a worker fails and the pipeline retries, the output will not contain duplicates. This is the only option that directly addresses both the source and sink to achieve end-to-end exactly-once semantics.

Exam trap

Google Cloud often tests the misconception that a single transform (like Reshuffle) or windowing strategy can guarantee exactly-once processing, when in reality it requires both source-level exactly-once delivery and an idempotent sink to handle retries from worker failures.

How to eliminate wrong answers

Option A is wrong because using a side input from a deduplication dataset does not prevent duplicate processing at the source; it only attempts to deduplicate after the fact, which is not a guarantee of exactly-once processing and adds complexity and latency. Option B is wrong because a global window with no early triggers controls when results are emitted, but it does not prevent duplicate messages from being processed due to worker failures or retries. Option C is wrong because a Reshuffle transform (which inserts a GroupByKey and an UngroupByKey) can help with fault tolerance by breaking fusion, but it does not provide exactly-once semantics; it only ensures that elements are redistributed, not that duplicates are eliminated.

Full explanation →

593

MCQeasy

A data engineer needs to design a data processing system that ingests large volumes of sensor data from IoT devices. The data should be stored in a schema-less format and allow for real-time analytics. Which Google Cloud service is most appropriate?

A.Cloud Spanner

B.Firestore

C.Cloud Bigtable

D.Cloud SQL

AnswerC

Bigtable is schema-less, highly scalable, and ideal for time-series sensor data.

Why this answer

Cloud Bigtable is the most appropriate choice because it is a fully managed, scalable NoSQL database designed for large-scale analytical and operational workloads. It supports schema-less storage of time-series sensor data and integrates with real-time analytics tools like BigQuery and Dataflow via the HBase API, meeting the requirements for high-throughput ingestion and low-latency queries.

Exam trap

The trap here is that candidates often confuse Cloud Bigtable with Firestore or Cloud SQL because they all offer NoSQL or relational storage, but fail to recognize that Bigtable is purpose-built for high-throughput, schema-less time-series data and real-time analytics, while the others are optimized for transactional or mobile workloads.

How to eliminate wrong answers

Option A is wrong because Cloud Spanner is a globally distributed, strongly consistent relational database that enforces a fixed schema, making it unsuitable for schema-less IoT data and overkill for real-time analytics at scale. Option B is wrong because Firestore is a document-oriented NoSQL database optimized for mobile and web app real-time synchronization, not for high-throughput ingestion of large volumes of sensor data or analytical workloads. Option D is wrong because Cloud SQL is a managed relational database service (MySQL, PostgreSQL, SQL Server) that requires a predefined schema and cannot handle the petabyte-scale, high-write throughput demands of IoT sensor data without significant performance degradation.

Full explanation →

594

MCQeasy

You are designing a streaming Dataflow pipeline that reads from Cloud Pub/Sub. Some data may arrive late due to network delays. You need to ensure that late-arriving data is still processed, but after a certain point, it should be discarded to avoid unbounded state. What is the best practice?

A.Switch to a batch pipeline

B.Use fixed windows without allowed lateness

C.Discard all late-arriving data

D.Set a watermark and allowed lateness

AnswerD

A watermark tracks event-time progress, while allowed lateness defines how long late records are still accepted before their window's state is discarded. This directly satisfies the constraint: late data is processed, yet state remains bounded rather than growing indefinitely.

Why this answer

In streaming Dataflow pipelines, setting a watermark and allowed lateness provides a mechanism to handle late-arriving data from Pub/Sub without unbounded state growth. The watermark defines the point after which data is considered late, and allowed lateness specifies how long to wait for late data before discarding it, balancing completeness and state management.

Exam trap

The trap here is that candidates often confuse 'allowed lateness' with simply discarding late data, failing to recognize that it provides a controlled buffer for late arrivals while still bounding state growth.

How to eliminate wrong answers

Option A is wrong because switching to a batch pipeline would lose the streaming, low-latency processing requirement and cannot handle late-arriving data in real time. Option B is wrong because fixed windows without allowed lateness would immediately discard any data arriving after the window end, even if it is only slightly delayed, leading to data loss. Option C is wrong because discarding all late-arriving data is too aggressive and ignores the need to process data that arrives within a reasonable delay, which is common in distributed systems like Pub/Sub.

Full explanation →

595

MCQhard

A media company ingests clickstream events into Pub/Sub and processes them with a Dataflow streaming pipeline that writes to BigQuery. The pipeline uses a fixed window of five minutes and discards late data. A product manager reports that events arriving more than five minutes after their event timestamp never appear in reports. Which change should you make to capture those events?

A.Switch the pipeline to use the Pub/Sub message publish time as the element timestamp.

B.Add a GroupByKey transform before the window to buffer all events.

C.Increase the fixed window size to thirty minutes.

D.Configure an allowed lateness on the window and adjust the trigger to emit updated results.

AnswerD

Allowed lateness extends how long a window accepts elements after its end, and a trigger that fires on late data emits revised results for those windows. Together they let events arriving beyond five minutes be incorporated into the aggregation instead of being discarded, which directly addresses the missing late arrivals.

Why this answer

Discarding late data is a consequence of the window's allowed lateness and trigger configuration, not of window duration. Setting an allowed lateness period and a trigger that emits on late arrivals lets the pipeline accept events beyond the five-minute boundary and update the affected windows, so the stragglers appear in reports.

Exam trap

The trap here is assuming that a larger window automatically captures late events, when lateness handling is governed by allowed lateness and triggers, not by window length.

Full explanation →

596

MCQeasy

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?

A.Use Pub/Sub to Dataflow to BigQuery with the Storage Write API for high-throughput streaming.

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

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

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

AnswerA

Pub/Sub with Dataflow and the Storage Write API delivers true streaming ingestion, satisfying the seconds-level latency constraint. Dataflow handles windowing and exactly-once processing, while the Storage Write API commits rows directly into BigQuery without the batching delays of load jobs or legacy streaming inserts.

Why this answer

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.

Exam trap

PDE often tests the difference between the legacy streaming API and the Storage Write API, and candidates may incorrectly choose the simpler Pub/Sub to BigQuery subscription without considering throughput and exactly-once requirements.

How to eliminate wrong answers

Option B is wrong because Cloud Data Fusion is a GUI-based data integration tool that is more suited for batch and ETL workloads, not for low-latency real-time streaming; it would introduce higher latency. Option C is wrong because using Cloud Functions to insert into BigQuery with the legacy streaming API is not recommended for high-throughput scenarios due to potential bottlenecks, lack of exactly-once semantics, and higher cost; the legacy API also has limitations. Option D is wrong because a BigQuery subscription to Pub/Sub directly writes to BigQuery but does not provide the same level of processing, transformation, and exactly-once guarantees as Dataflow with the Storage Write API; it is also limited in throughput and may not meet the 'within seconds' requirement for high volumes.

Full explanation →

597

MCQmedium

A Cloud SQL instance for PostgreSQL is experiencing heavy read traffic. The team wants to offload read queries while maintaining data consistency. Which solution meets their needs?

A.Create read replicas and direct read queries to them.

B.Increase the tier of the Cloud SQL instance.

C.Create a Cloud SQL failover replica.

D.Use Cloud Memorystore as a cache in front of Cloud SQL.

AnswerA

Read replicas handle read traffic, reducing load on the primary instance.

Why this answer

Read replicas in Cloud SQL for PostgreSQL allow you to offload read traffic from the primary instance by creating one or more asynchronous replicas that serve read queries. This maintains data consistency because replicas use PostgreSQL's native streaming replication, ensuring that all committed transactions on the primary are eventually reflected on the replica, providing a consistent snapshot for read operations.

Exam trap

The Google Professional Data Engineer exam often tests the distinction between read replicas (for offloading reads) and failover replicas (for high availability), so candidates may confuse the two and incorrectly select the failover replica option.

How to eliminate wrong answers

Option B is wrong because increasing the tier of the Cloud SQL instance only scales the primary instance vertically, which does not offload read traffic — it simply gives the same instance more resources, which may not be sufficient under heavy read loads and does not separate read and write workloads. Option C is wrong because a Cloud SQL failover replica is designed for high availability and automatic failover in case of primary instance failure, not for offloading read queries; it is a synchronous standby that does not serve read traffic. Option D is wrong because Cloud Memorystore as a cache can reduce read load but does not maintain strong data consistency with Cloud SQL; cached data may become stale, and the cache is not a direct offload of read queries from the database — it introduces eventual consistency and requires application-level cache invalidation logic.

Full explanation →

598

MCQhard

A company is migrating an on-premises PostgreSQL database to Google Cloud. The database runs complex analytical queries mixed with OLTP workloads. They need PostgreSQL compatibility and want to improve analytical query performance without changing the application. Which database should they choose?

A.BigQuery

B.Cloud Spanner

C.Cloud SQL for PostgreSQL

D.AlloyDB

AnswerD

AlloyDB provides full PostgreSQL wire compatibility, so the application connects unchanged, while its columnar engine accelerates analytical scans and aggregates alongside standard row-based OLTP processing. This directly satisfies the stem's requirement to improve analytical query performance on mixed workloads without application modification.

Why this answer

AlloyDB is the correct choice because it is a fully managed PostgreSQL-compatible database service specifically designed for demanding transactional and analytical workloads. It combines the PostgreSQL ecosystem with a columnar engine and adaptive caching to accelerate analytical queries by up to 100x over standard PostgreSQL, all without requiring application changes. This makes it ideal for mixed OLTP and complex analytical queries while maintaining PostgreSQL compatibility.

Exam trap

The trap here is that candidates often choose Cloud SQL for PostgreSQL because it is the most familiar PostgreSQL option within Google Cloud, overlooking that AlloyDB is Google's specialized service for mixed OLTP and analytical workloads, providing PostgreSQL compatibility with built-in analytical acceleration. Cloud SQL lacks these advanced analytical features.

How to eliminate wrong answers

Option A is wrong because BigQuery is a serverless data warehouse that is not PostgreSQL-compatible and requires application changes to use its SQL dialect; it is designed for large-scale analytics, not OLTP workloads. Option B is wrong because Cloud Spanner is a globally distributed, strongly consistent relational database that uses a proprietary SQL dialect, not PostgreSQL, and is optimized for horizontal scalability and high availability, not for improving analytical query performance on mixed workloads. Option C is wrong because Cloud SQL for PostgreSQL is a fully managed PostgreSQL service but lacks the built-in columnar engine and adaptive caching needed to significantly accelerate complex analytical queries; it is best suited for standard OLTP workloads, not mixed analytical and transactional demands.

Full explanation →

599

MCQmedium

You are building a multi-cloud analytics solution to join data from Google Cloud and AWS S3. You need to query the S3 data using BigQuery without moving it. Which Google Cloud service should you use?

A.Looker

B.Dataproc

C.BigQuery Data Transfer Service

D.BigQuery Omni

AnswerD

BigQuery Omni runs BigQuery queries directly against AWS S3 and Azure Blob Storage via Anthos-hosted compute, returning results without extracting or duplicating the data. This satisfies the stem's requirement to join Google Cloud and S3 data while querying S3 in place, avoiding egress and movement costs.

Why this answer

BigQuery Omni is the service that allows querying data in AWS S3 directly from BigQuery without moving it. It deploys BigQuery compute in AWS to process the data locally, returning results to Google Cloud.

Exam trap

The trap is confusing data transfer services with query-in-place services, leading to picking BigQuery Data Transfer Service.

How to eliminate wrong answers

Option A is wrong because Looker is a BI and analytics platform, not a query engine for external data. Option B is wrong because Dataproc is a managed Spark/Hadoop service, which would require moving or processing data, not direct querying. Option C is wrong because BigQuery Data Transfer Service moves data into BigQuery, not query in place.

Full explanation →

600

MCQeasy

You have a BigQuery table with sales data and want to pivot product categories into columns. Which SQL clause should you use?

A.UNPIVOT

B.PIVOT

C.ARRAY_AGG with CROSS JOIN

D.STRUCT

AnswerB

PIVOT rotates rows into columns, directly satisfying the requirement to turn product categories into separate columns. BigQuery supports the PIVOT operator, which aggregates values using an aggregate function alongside a FOR clause naming the category column and an IN list of the values to become column headers.

Why this answer

BigQuery supports the PIVOT operator, which rotates rows into columns by aggregating values for each distinct pivot key. It is used in the FROM clause after the source table or subquery, with an aggregate function and a list of pivot values. This is the standard SQL way to turn product categories into columns in a sales table.

Exam trap

PDE often tests the confusion between PIVOT and UNPIVOT — candidates must remember PIVOT turns rows into columns, while UNPIVOT turns columns into rows.

How to eliminate wrong answers

Option A is wrong because UNPIVOT does the opposite — it rotates columns into rows, not rows into columns. Option C is wrong because ARRAY_AGG with CROSS JOIN is a manual workaround that produces arrays rather than true pivoted columns and is far less readable than the native PIVOT operator. Option D is wrong because STRUCT is a data type for nested records, not a clause for reshaping rows into columns.

Full explanation →

Google Professional Data Engineer (PDE) — Questions 526–600