PDE Practice Test 25 — 15 Questions

Question 1

A company wants to use BigQuery's PIVOT operator to transform their sales data. They have a table with columns: 'year', 'quarter', 'revenue'. They want to create a report where each row is a year and each column is a quarter (Q1, Q2, Q3, Q4) showing revenue. Which SQL statement is correct?

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

Accepted Answer

SELECT * FROM sales PIVOT(SUM(revenue) FOR quarter IN ('Q1','Q2','Q3','Q4')). BigQuery's PIVOT operator requires an aggregate function applied to the value column, followed by FOR IN (). Option A correctly uses SUM(revenue) as the aggregate, FOR quarter to specify the pivot column, and IN ('Q1','Q2','Q3','Q4') with quoted string literals. The remaining columns (year) automatically become the grouping keys, producing one row per year with Q1–Q4 as columns.

Answer

SELECT * FROM sales PIVOT(revenue FOR quarter IN (Q1, Q2, Q3, Q4))

Answer

PIVOT sales ON quarter USING SUM(revenue)

Answer

SELECT * FROM (SELECT year, quarter, revenue FROM sales) PIVOT(SUM(revenue) FOR quarter IN (Q1, Q2, Q3, Q4))

Question 2

An online retailer uses BigQuery for analytics. They have a time-series table with 5 billion rows and new data arrives every day. They want to optimize query performance and reduce costs by ensuring that queries scan only the partitions they need. Which table design should they use?

Accepted Answer

Use a table partitioned on the timestamp column.. Partitioning on the timestamp column allows BigQuery to perform partition pruning, so queries with filters on that column only scan the relevant partitions. This directly reduces the amount of data read, lowering both query cost (pay-per-byte) and improving performance. For a 5-billion-row table with daily data arrival, time-unit partitioning is the standard design to meet the stated goals.

Answer

Use a table clustered on the timestamp column.

Answer

Use a table with no partitioning but use LIMIT in queries.

Answer

Use a table partitioned by ingestion time with a partition expiration.

Question 3

Which BigQuery SQL function can be used to get an approximate count of distinct values in a large column faster than COUNT(DISTINCT) with lower accuracy?

Accepted Answer

APPROX_COUNT_DISTINCT. APPROX_COUNT_DISTINCT is a BigQuery function that returns an approximate count of distinct values using a HyperLogLog++ algorithm. It is significantly faster and uses far fewer resources than COUNT(DISTINCT) on large datasets, at the cost of a small statistical error (typically under 1%). This matches the requirement for a faster, lower-accuracy distinct count.

Answer

APPROX_QUANTILES

Answer

COUNT(DISTINCT)

Answer

DISTINCT_COUNT

Question 4

A data engineer wants to automatically detect when the distribution of input features to a production model has shifted significantly. Which Vertex AI feature should they enable?

Accepted Answer

Vertex AI Model Monitoring. Vertex AI Model Monitoring is the correct service because it is specifically designed to continuously detect feature distribution drift and prediction skew in production models. It automatically compares the current input feature distribution against a baseline (e.g., training data) and triggers alerts when significant statistical shifts occur, enabling proactive retraining or investigation.

Answer

Vertex AI Vizier

Answer

Vertex AI Explainable AI

Answer

Vertex AI Feature Store

Question 5

You manage a team that deploys multiple versions of a computer vision model for A/B testing on Vertex AI Endpoints. You need to route a small percentage of traffic to a canary version while the rest goes to the stable version. You also need to gradually increase the canary traffic over time based on performance metrics. Which approach should you take?

Accepted Answer

Deploy both models to the same endpoint and configure traffic splitting percentages using the Vertex AI console or API.. Vertex AI Endpoints natively support traffic splitting between model versions deployed to the same endpoint. This allows you to assign a percentage of traffic (e.g., 5%) to a canary version and the remainder to the stable version, and then adjust the split over time via the console or API as performance metrics dictate. This approach avoids the complexity and latency of external load balancers or application-level routing.

Answer

Create two separate endpoints, one for each version, and use a separate load balancer to route a percentage of requests to the canary endpoint.

Answer

Use Cloud Armor with weighted backend services to route a portion of requests to the canary version.

Answer

Implement feature flags in the application code to randomly select the model version for each prediction request.

Question 6

A company needs a messaging service for event-driven applications that require low cost for high-throughput, but can tolerate occasional message loss. Which Pub/Sub product should they choose?

Accepted Answer

Pub/Sub Lite. Pub/Sub Lite is designed for cost-sensitive workloads with relaxed durability. Standard Pub/Sub offers at-least-once delivery and high durability. Push vs pull is irrelevant to cost.

Answer

Pub/Sub with pull subscriptions

Answer

Pub/Sub with dead letter topics

Answer

Pub/Sub with push subscriptions

Question 7

A retail company is building a recommendation engine that requires processing customer clickstream data in near real-time. The data is ingested via Pub/Sub, and must be joined with a lookup table of product details (updated daily) before being used for model inference. Which design pattern should they use?

Accepted Answer

Use a Dataflow pipeline that reads from Pub/Sub and uses a side input from a regularly refreshed PCollection loaded from Cloud Storage.. Dataflow can read streaming data from Pub/Sub and use a side input from a regularly refreshed PCollection loaded from Cloud Storage. This pattern allows the product lookup table (updated daily) to be periodically reloaded into the pipeline as a side input, enabling efficient, low-latency enrichment of each event without per-event external calls or batch delays.

Answer

Enrich the stream by querying BigQuery for each event using a Cloud Function.

Answer

Store product details in Cloud Memorystore (Redis) and have the streaming application look up each event.

Answer

Write events to BigQuery and use scheduled queries to join with the product table in batch.

Question 8

Which Google Cloud service is designed to replicate data from MySQL, PostgreSQL, and Oracle databases to BigQuery or Cloud Storage in near real-time?

Accepted Answer

Datastream. Datastream is a serverless CDC service that ingests change data from relational databases into GCS or BigQuery.

Answer

Cloud Data Fusion

Answer

Dataflow

Answer

Pub/Sub

Question 9

An organization needs to continuously replicate change data from a MySQL database to BigQuery with sub-minute latency. The database is running on-premises. Which Google Cloud service should they use?

Accepted Answer

Cloud Datastream. Cloud Datastream is a serverless change data capture (CDC) and replication service that supports continuous replication from MySQL (including on-premises) to BigQuery with sub-second latency. It handles schema conversion, and integrates with BigQuery for real-time analytics. This is the purpose-built service for the requirement.

Answer

Cloud Pub/Sub with a custom connector to MySQL

Answer

BigQuery Data Transfer Service for MySQL

Answer

Cloud Dataflow with a JDBC source

Question 10

A company deploys a machine learning model to Vertex AI for real-time predictions. After deployment, they notice that prediction latency spikes during peak traffic hours. Which approach should they take to reduce latency without sacrificing accuracy?

Accepted Answer

Configure auto-scaling with higher min and max instances. Configuring auto-scaling with higher min and max instances ensures that Vertex AI has sufficient pre-warmed replicas to handle traffic spikes without cold-start latency. This approach maintains model accuracy because it does not alter the model architecture or inference logic, only the infrastructure capacity.

Answer

Reduce the number of input features

Answer

Switch from online to batch prediction

Answer

Use a larger machine type for the model

Question 11

You need to analyse streaming data from thousands of IoT devices, each sending temperature readings every second. You want to calculate the average temperature per device over the last 5 minutes, updating every minute. Which windowing strategy should you use in Dataflow?

Accepted Answer

Sliding windows of length 5 minutes with a period of 1 minute. Sliding windows of length 5 minutes with a period of 1 minute produce a new window every minute, each covering the previous 5 minutes of data — exactly matching the requirement to compute a 5-minute average that updates every minute. This is the canonical use case for sliding windows in Dataflow/Beam.

Answer

Global windows with a trigger firing every minute

Answer

Fixed windows of 5 minutes

Answer

Session windows with a gap duration of 1 minute

Question 12

A company wants to implement a robust MLOps lifecycle on Google Cloud. Which THREE components are essential?

Accepted Answer

Vertex AI Model Registry for versioning. Vertex AI Model Registry (A) is essential because it provides centralized versioning, lineage, and lifecycle tracking of trained models, which is a core requirement for a robust MLOps lifecycle. Vertex AI Pipelines (B) is essential for orchestrating the end-to-end ML workflow (data prep, training, evaluation, deployment) as reproducible, automated pipeline steps. Cloud Build (D) is essential for CI/CD, enabling automated building, testing, and deployment of ML artifacts and pipeline components as part of the MLOps process. Pub/Sub (C) can support event-driven retraining but is an optional architectural pattern rather than a core MLOps component, and Cloud SQL (E) is a general relational database not purpose-built for model metadata management, so neither is essential here.

Answer

Pub/Sub for event-driven retraining

Answer

Cloud SQL for model metadata

Question 13

A data science team uses Cloud Build and Vertex AI to implement CI/CD for their machine learning models. Which THREE steps are essential for a production-ready operationalization pipeline? (Choose 3.)

Accepted Answer

Deploy the model to a staging endpoint for manual approval before promoting to production.. Option B is correct because a production-ready operationalization pipeline should gate promotion with a staging endpoint and manual approval, so a human can verify the model behaves correctly before it serves live traffic. Option D is correct because Vertex AI Model Evaluation compares the candidate model's metrics against the current production model, ensuring the new version actually improves or meets quality thresholds before promotion. Option E is correct because embedding unit and integration tests for the training code in the Cloud Build pipeline catches data, feature, and code regressions early in CI/CD, which is essential for reliable automated ML delivery. Option A is not correct because storing training artifacts in Cloud Storage without versioning breaks reproducibility and rollback, which are required for production ML pipelines. Option C is not correct because automatically deploying every new model version straight to production bypasses validation and approval, risking unvetted or degraded models serving users.

Answer

Store all training artifacts in Cloud Storage without versioning.

Answer

Automatically deploy every new model version directly to the production endpoint.

Question 14

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?

Accepted Answer

The Dataflow workers are CPU-bound; increase the number of workers or adjust autoscaling settings.. 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.

Answer

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

Answer

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

Answer

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

Question 15

A company uses Dataplex to manage data quality across multiple BigQuery datasets. They want to define a data quality rule that checks if a column 'email' contains a valid email format. Which Dataplex feature should they use?

Accepted Answer

Create a custom Data Quality rule using the 'regex' type.. Dataplex Data Quality tasks support a set of built-in rule types (range, non-null, uniqueness, set, regex, sql_assertion, row_condition), and email format validation is not one of the built-in types. The 'regex' rule type lets you supply a regular expression that each value in the 'email' column must match, so a pattern like ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$ enforces valid email formatting. This is the intended Dataplex mechanism for pattern-based validation.

Answer

Use Cloud DLP to classify and validate emails.

Answer

Use the built-in 'email' rule type in Dataplex.

Answer

Create a Dataflow pipeline to validate emails and write results to a separate table.