Hard PDE practice questions

A data scientist trains a BigQuery ML boosted tree model daily on an orders table that grows continuously. They want the model evaluation metrics to be comparable across daily training runs, and they need each row deterministically assigned to either training or evaluation so that a given order always lands in the same split even as new rows arrive. Which approach best meets these requirements?

A data engineer is building a BigQuery ML logistic regression model to predict customer churn. The raw table contains a transaction timestamp, several engineered numeric features, and the churn label. During evaluation, the model reports 98% accuracy on the test set, but performance drops sharply in production. On review, the engineer notices they computed the mean and standard deviation for feature normalization across the entire table before running CREATE MODEL, and BigQuery ML randomly split rows into training and evaluation sets. What is the most likely cause of the inflated evaluation metrics, and how should it be fixed?

Your company runs BigQuery in the Enterprise edition with a single reservation of 1,000 baseline slots assigned to a production project for scheduled ETL. During overnight batch windows the ETL reservation is fully utilized, but during the day it sits nearly idle while a separate ad-hoc analytics project (currently on-demand billing) generates unpredictable, spiky query costs. Leadership wants to reduce overall cost and improve slot utilization without degrading the ETL SLA. What should you do?

A retail company uses BigQuery with a flat-rate reservation of 1,000 slots. Their nightly ETL jobs and interactive BI dashboard queries share the same reservation. During the day, analysts complain that dashboards are slow whenever ad-hoc data science queries run large scans, and at night the ETL jobs sometimes run long because reporting refresh jobs compete for slots. The team wants to isolate these workloads so critical dashboards always get priority, while still allowing idle capacity to be reused. What should they do?

Your team runs a nightly BigQuery batch load pipeline plus dozens of interactive analyst queries during business hours, all on a single on-demand billing project. Finance reports monthly BigQuery costs are volatile and unpredictable, and they want a predictable maximum monthly spend without blocking analysts entirely when batch jobs are running. You must reorganize the workloads to give both predictable cost and workload isolation. Which approach best meets these requirements?

An industrial IoT company ingests sensor readings from 50,000 devices, each emitting one reading per second. The primary access pattern is retrieving the most recent readings for a single device over the last hour, and occasionally scanning a device's readings across a full day. The team chose Cloud Bigtable for its write throughput and low-latency reads. Which row key design best supports these access patterns while avoiding hotspotting?

A European financial services company must store and process customer transaction data in BigQuery. Regulatory requirements mandate that both the data and the encryption keys used to protect it remain physically within the EU, and the company must be able to permanently revoke access to the data across all copies at any time. Which approach best satisfies these requirements?

A retail analytics team runs a streaming Dataflow pipeline that must join two Pub/Sub streams: an 'orders' stream and a 'payments' stream. An order and its matching payment share an order_id but can arrive up to 10 minutes apart and out of order. The team needs to correlate each order with its payment to compute settled revenue, emitting a result once both events for an order_id are seen (or marking the order unsettled if no payment arrives within the window). Which pipeline design best meets these requirements?

A Dataflow streaming pipeline aggregates ad-impression counts per publisher using a GroupByKey followed by a Sum combiner. A handful of very large publishers generate the vast majority of events, and monitoring shows severe data skew: a few workers are overloaded processing these 'hot keys' while others sit idle, causing backlog growth. You must reduce processing latency without changing the final aggregation results. What is the most effective change?

A media company runs a streaming Dataflow pipeline that ingests events from Pub/Sub, enriches them, and writes results to BigQuery. Leadership requires that if the primary region (us-central1) suffers a full regional outage, the pipeline can resume processing with minimal data loss and an RTO of under one hour. Which design best satisfies this disaster recovery requirement?

You are building a Dataflow streaming pipeline that reads clickstream events from Pub/Sub and enriches each event with product metadata (name, category, price). The product metadata is stored in a BigQuery table that is updated roughly once per hour and contains about 50,000 rows. You want the enrichment to reflect metadata updates without redeploying the pipeline, while keeping per-event latency low and avoiding a lookup call for every event. What is the best approach?

Your team runs a Dataflow streaming pipeline that reads from Pub/Sub, performs enrichment lookups, and writes to BigQuery. During a marketing campaign, the Pub/Sub subscription backlog grows steadily and end-to-end latency climbs from seconds to several minutes. Monitoring shows worker CPU is only moderately utilized, but the number of workers has not increased. What is the most likely cause and appropriate fix?

A production Dataflow streaming pipeline reads from Pub/Sub, enriches events, and writes to BigQuery. You discover a bug in the enrichment transform that is silently dropping a subset of valid records. You have fixed the code, but the pipeline is processing millions of events per hour and business requires no gap in processing and no duplicate reprocessing of already-correct output. The pipeline's transform DAG shape is unchanged. What is the best way to deploy the fix while maintaining continuity?

Your team runs a production Dataflow streaming pipeline reading from Pub/Sub and writing to BigQuery. Over the past week, an on-call engineer notices that the end-to-end latency has crept from seconds to over 30 minutes, even though input message rate is roughly constant and the job has not been redeployed. Autoscaling is enabled with a generous maximum worker count, but the job is holding steady at only 3 workers. Which action should you take first to diagnose the root cause of the growing latency?

Your team ingests a clickstream from Pub/Sub into a streaming Dataflow pipeline that computes per-user session activity aggregated into 10-minute fixed windows. Mobile clients frequently buffer events and send them minutes after they occurred, sometimes well after the window has fired. Business analysts complain that late-arriving events are silently dropped, producing undercounted metrics. You must ensure late events still update the correct window's results while keeping the pipeline emitting timely initial results. What should you do?

A data engineering team runs a large nightly Spark ETL job on Dataproc. To reduce costs, they configured the cluster to use secondary workers that are Spot VMs. The job frequently fails during the shuffle-heavy stages because preempted workers lose intermediate shuffle data, forcing expensive recomputation and sometimes exceeding retry limits. The team wants to keep the cost savings of Spot VMs while making the job resilient to preemption. What should they do?

A Looker developer builds an Explore that joins an orders table (one row per order) to an order_items table (many rows per order) using a one-to-many relationship. Business users report that a measure summing order_amount (defined on the orders view) returns inflated totals whenever they also include fields from order_items in the same query. What is the correct way to define the model so this measure returns accurate results regardless of the join fan-out?

A retail company ingests order events into Pub/Sub, where an upstream publisher occasionally retries publishes, causing duplicate messages with the same application-level order_id but different Pub/Sub message IDs. A Dataflow streaming pipeline aggregates order totals in 10-minute fixed windows and writes to BigQuery. The team observes inflated revenue totals due to duplicate orders. They cannot change the publisher. Which approach most reliably eliminates the duplicates based on order_id within the pipeline?

A retail analytics team runs a Dataflow streaming pipeline that reads clickstream events from Pub/Sub and aggregates purchase counts into 1-hour fixed windows. Business users complain that they must wait up to an hour to see any partial results on the live dashboard. The team wants to emit approximate running totals every minute while the window is still open, then emit a final corrected result when the window closes and watermark passes. Which Apache Beam configuration best achieves this?

A financial services company ingests trade events through Cloud Pub/Sub into a Dataflow streaming pipeline. Auditors require that events for each trading account are processed in the exact order they were published, and that no event is dropped or duplicated in the final BigQuery ledger table. Traffic is bursty and can exceed 50,000 messages per second across thousands of accounts. Which design best meets these fidelity requirements?

A data science team at a lending company is building a churn model in Vertex AI. Their training labels come from an event table where each customer has a churn event with a specific timestamp. Their features (account balance, transaction counts, support tickets) are stored in a Vertex AI Feature Store and change frequently over time. When they built their first training set by joining the latest feature values to each label, the model performed well in offline evaluation but poorly in production. What is the most likely cause and the correct fix?

A retail company trains a churn prediction model in Vertex AI using features computed by a nightly BigQuery batch job (e.g., 30-day rolling purchase totals). In production, the online prediction service recomputes those same features with ad hoc SQL written by the application team, and the model's live accuracy is far lower than during offline evaluation. What is the MOST likely root cause and the best remedy?