Professional Data Engineer Advanced Practice Exam: Hard Questions 2025

A retail company is designing a near-real-time analytics platform. Events arrive from 200k devices and must be queryable in BigQuery within 60 seconds. During traffic spikes, Pub/Sub backlog increases and downstream processing must not lose events or double-count them. The team currently uses a streaming Dataflow pipeline writing to BigQuery via streaming inserts and notices duplicate rows and occasional schema-related failures when new optional fields appear. What architecture change best satisfies low-latency, scalability, and exactly-once semantics in BigQuery while handling schema evolution safely?

A financial services firm needs to build a data platform for daily risk calculations. Source systems include an on-prem Oracle database (change data capture required), SaaS CRM exports, and streaming trade events. Data must land in a governed lake, be discoverable via a data catalog, and support both ad hoc SQL in BigQuery and Spark-based feature engineering. The firm requires fine-grained access controls (row/column where possible), lineage, and the ability to enforce data retention policies. Which design best meets these requirements with minimal custom governance code?

A media company runs a Dataflow streaming pipeline (Pub/Sub -> transforms -> BigQuery). After enabling a new enrichment step that calls an external HTTPS API, the pipeline intermittently stalls, worker CPU is low, and Pub/Sub backlog grows. The external API has a strict QPS limit and occasional 429 responses. The company needs to keep end-to-end latency under 2 minutes, avoid dropping messages, and prevent the API from being overwhelmed. What is the best approach?

Your organization uses Composer (Airflow) to orchestrate a daily pipeline: ingest files to Cloud Storage, load to BigQuery, run transformations, then publish curated tables. Twice a month, downstream consumers see partial data in curated tables because a task marked success even though upstream loads had silently loaded only a subset of files. The issue is due to late-arriving files and non-atomic publish steps. You must ensure curated tables are updated atomically and only when completeness criteria are met, while still allowing backfills for specific dates. What is the best solution?

A company runs BigQuery scheduled queries that generate several derived tables each morning. Occasionally, a derived table is created from a mix of yesterday’s and today’s upstream partitions because some upstream tables finish late. The company wants a robust dependency mechanism and observability across the DAG, but prefers not to build a custom orchestration service. Which approach best resolves the issue?

A team trains a classification model in Vertex AI. In production, the model’s overall accuracy is stable, but a critical subgroup (new customers) shows a sharp drop in precision. The team has limited labeled data for that subgroup and cannot wait for a full retraining cycle. They need to detect and mitigate this issue quickly while maintaining auditability. What is the best next step?

You have a Vertex AI endpoint serving a model with strict latency SLOs. After deploying a new model version, p95 latency increases and occasional timeouts occur, but only for requests with large payloads. Logs show the model container is hitting memory pressure and performing frequent garbage collection. You must reduce latency quickly without reducing prediction accuracy. What should you do first?

A data platform uses BigQuery for analytics with multiple downstream dashboards. You discover that a frequently joined dimension table has non-unique keys due to upstream ingestion issues, causing silent row multiplication and inflated metrics in reports. The company wants to prevent this class of data quality issue from reaching curated layers and to surface failures early with automated checks. What is the best approach?

A healthcare organization stores sensitive datasets in BigQuery. Different roles require different access: analysts can see aggregated results, a small compliance team can see raw identifiers, and data scientists can access de-identified features. The organization also needs to ensure that exported data cannot leak identifiers unintentionally. Which solution best enforces least privilege and reduces exfiltration risk while keeping workflows practical?

A global IoT platform writes time-series telemetry to BigQuery partitioned tables. Queries often filter by device_id and time range, but performance degrades as data grows. The team clustered by device_id, yet some queries still scan large volumes due to hot devices generating disproportionate data. You need to optimize query performance and cost while preserving flexibility for ad hoc analysis. Which approach is most effective?