Google Professional Data Engineer

Cloud Monitoring alerts can detect anomalies, but it is not an orchestration engine. Triggering jobs only when metrics indicate they “haven’t run” is reactive, brittle, and does not provide consistent dependency management, retries, or a unified execution history. It also complicates ad-hoc runs and does not inherently centralize logs and task-level status across Dataproc, STS, and Dataflow in a single operational interface.

A custom orchestrator on Cloud Run plus Firestore state and Cloud Scheduler can work, but it violates the requirement to avoid building custom infrastructure. You would need to implement scheduling logic, idempotency, retries, concurrency controls, a UI or tooling for ad-hoc runs, and robust monitoring/alerting. This increases operational overhead and risk compared to using a managed orchestrator designed for these patterns (Composer/Airflow).

A single VM with cron is the least reliable and least maintainable approach. It introduces a single point of failure, requires OS patching and uptime management, and forces you to build custom log parsing and notification logic. It also lacks a standardized workflow UI for manual runs and consistent task monitoring. This option conflicts with Google Cloud best practices for operational excellence and the requirement to avoid custom infrastructure.

BigQuery can store time-series data and supports SQL range queries, but it commonly incurs per-query costs (on-demand) and is not primarily a low-latency operational store. With 12M sensors at 1 Hz, ingestion is massive; while BigQuery can handle high volumes, achieving consistent sub-second ad hoc query latency on the most recent data is not its typical strength. Avoiding per-query charges would require flat-rate reservations, which the option does not specify.

A wide BigQuery table with one column per second and updating the same row every second is an anti-pattern. BigQuery is optimized for append-only analytics, not frequent row updates. This design increases complexity, risks contention, and makes schema evolution painful (adding metrics or changing granularity). It also does not naturally align with partitioning/clustering for efficient range queries and can lead to higher costs and operational overhead.

A wide Bigtable row per sensor per minute with 60 columns (one per second) can reduce row count, but it introduces frequent mutations to the same row (updates every second), which can be less efficient and may increase contention/hotspot risk. It also makes adding new metrics more complex (multiplying qualifiers per second) and can create very wide rows over time. Narrow, append-only time-series rows are generally preferred for scalability and simplicity.

Incorrect. A stall would not cause subscription/num_undelivered_messages to decrease; it would typically increase because messages are not being acknowledged fast enough. The second part (increase in rate of change of storage used bytes) also indicates higher write throughput, which is the opposite of a downstream slowdown. This option describes a healthy or improving pipeline, not a stalled one.

Incorrect. instance/storage/used_bytes is not an appropriate upstream metric for Pub/Sub; it’s also not a typical metric for Cloud Storage buckets in this context. Additionally, it reverses the logical placement: backlog belongs to the subscription (source), and bytes written belongs to the bucket (destination). This option mixes metrics and directions, making it unreliable for detecting pipeline stalls.

Incorrect. It suggests the source is storage used bytes increasing and the destination is Pub/Sub undelivered messages decreasing, which is backwards for this architecture. A decrease in undelivered messages generally indicates the subscriber is keeping up (or catching up), not stalling. Also, storage used bytes increasing does not indicate an upstream backlog; it indicates downstream accumulation.

Using Cloud KMS (CMEK) and encrypting locally with gcloud kms encrypt still relies on a key that exists and is controlled within Google Cloud KMS. While you can keep AAD outside Google Cloud, the KMS key material and decrypt capability remain in Google Cloud’s boundary. This generally does not meet a strict TNO mandate that explicitly requires even provider personnel cannot decrypt.

Destroying or rotating a Cloud KMS key after encrypting data makes the data effectively unrecoverable (and rotation does not help if the key is destroyed). It also does not satisfy the requirement that only the internal security team holds encryption material, because the key was in Cloud KMS. This option confuses key lifecycle/rotation with a TNO model and creates an availability/compliance risk.

CSEK with gsutil is the right mechanism, but storing the raw CSEK in Secret Manager or Memorystore violates the requirement that encryption material be held only by the internal security team and kept outside Google Cloud. Secret Manager is a Google-managed service; placing the key there breaks the “even cloud provider personnel cannot decrypt” intent of TNO.

Migrating to Dataflow could be cost-effective for some pipelines, but it generally requires rewriting the job (PySpark on Dataproc is not directly portable to Dataflow without changes). The question explicitly forbids rewriting or changing the schedule. Also, Dataflow is a different execution model (Beam) and operational approach, so it’s not the best answer for “configure the cluster” cost optimization.

Higher-memory machine types may reduce runtime, but they increase per-hour VM cost and don’t guarantee lower total cost for a job that already completes in 35 minutes. This option optimizes performance rather than cost. Without evidence that the job is memory-bound and that fewer nodes could be used, switching to larger machines is a risky and often more expensive change.

Local SSDs can improve I/O performance for shuffle-heavy Spark workloads, but they add cost and are not necessary when reading from Cloud Storage and writing to BigQuery for a short weekly batch. Dataproc jobs often benefit more from compute pricing optimizations than from adding premium storage. This is a performance tuning option, not the most direct cost reduction lever.

Incorrect. The Pub/Sub Kafka connector is implemented as Kafka Connect connectors (source/sink) and requires Kafka Connect runtime and connector deployment; this does not align with the stated preference for mirroring and introduces operational complexity. Also, even if deployed in cloud, this option still includes an unnecessary Kafka Connect layer and then proposes reading from Kafka to write to Cloud Storage, making Pub/Sub connector usage redundant and confusing for the stated goal.

Incorrect. It explicitly requires installing the Pub/Sub Kafka connector on the on-prem Kafka cluster, which violates the strict change control requirement to avoid deploying Kafka Connect plugins on-premises. Additionally, configuring Pub/Sub as a Source connector is the wrong direction for moving data from Kafka to Pub/Sub; you would typically use a Sink connector to publish Kafka records into Pub/Sub.

Incorrect. While Pub/Sub as a Sink connector is the correct direction for Kafka-to-Pub/Sub, it still requires installing and operating the Pub/Sub Kafka connector (Kafka Connect plugin) on-premises, which is prohibited by the requirement. It also changes the replication approach away from mirroring-based Kafka topic replication and introduces Pub/Sub semantics and quotas that are not necessary when the target is explicitly Cloud Storage for raw landing.

Network tags and firewall rules can allow or deny traffic, but they do not provide a path to reach Google APIs when nodes have no external IP and no NAT/PGA. Tags are also not a mechanism to selectively enable Google API access. This option confuses authorization (firewall) with connectivity (routing/egress).

Creating egress firewall rules to “Cloud Storage and BigQuery IP ranges” is not the right solution. Google APIs commonly use anycast VIPs and IPs can change; maintaining IP allowlists is brittle. More importantly, even with permissive egress rules, internal-only nodes still need a valid egress mechanism (Private Google Access or Cloud NAT) to reach those endpoints.

VPC Service Controls perimeters help reduce data exfiltration risk by restricting access to supported Google services from outside a perimeter. They do not solve basic network reachability from private nodes to Google APIs. You could still have connection failures without Private Google Access (or NAT/PSC). This is a security boundary feature, not an egress connectivity feature.

Incorrect. Although a sandbox project still protects production data, force-data-loss intentionally increases the chance of losing recent writes during failover. That makes the drill less representative of how you would operate in production and conflicts with the “no data loss” intent (even if only sandbox data is affected). Limited-data-loss is the appropriate mode for realistic, safer failover testing.

Incorrect. This performs a disruptive operation on production, violating the requirement to guarantee zero impact on production data. Adding a replica does not eliminate the possibility of data loss because replication is asynchronous; force-data-loss further increases risk. This option also adds operational complexity and cost without meeting the strict “no data loss” and “no production impact” constraints.

Incorrect. Even with limited-data-loss mode, initiating manual failover on production can still cause some data loss (asynchronous replication) and can cause transient client impact (connection resets, brief unavailability). The question requires guaranteeing zero impact on production data, which cannot be assured by any production failover action in Standard Tier Redis.

Incorrect. Compute Engine default encryption at rest uses Google-managed keys, not keys your team creates and controls. A dedicated service account does not change the encryption key ownership model. This fails the compliance requirements for customer-controlled key creation, rotation policy enforcement, and on-demand destruction. It also confuses identity/access control with cryptographic key management.

Incorrect. Generating keys locally and “uploading them to KMS” is not the standard approach for encrypting Persistent Disks. While Cloud KMS supports importing key material, you still use CMEK integration rather than manually encrypting data on instances. Manually encrypting at the OS/app layer would add operational complexity and likely require changes to how RabbitMQ/TimescaleDB read/write data.

Incorrect. Referencing KMS keys directly in application API calls describes application-level encryption (envelope encryption), which would require code changes and careful handling of encryption/decryption logic, performance, and key access patterns. The requirement explicitly says “without requiring changes to the application code,” making this unsuitable. For VM disks, CMEK is configured at the infrastructure resource level, not in app calls.

Draining a Dataflow job stops pulling new messages and attempts to finish processing in-flight elements before shutting down. However, this does not solve the requirement that the new pipeline is incompatible with the old one (keying/windowing/state changes). In-place updates are not always possible for incompatible streaming graph/state changes, and draining alone does not provide a parallel, zero-loss cutover path.

Transform mapping JSON is associated with certain Dataflow update scenarios, but it does not generally enable arbitrary incompatible semantic changes like different keying/windowing logic that affects state and aggregation correctness. Even if an update were accepted, it risks state incompatibility and incorrect results. This option also doesn’t address the operational need for a safe blue/green deployment with no interruption.

Running two Dataflow jobs against the same Pub/Sub subscription causes them to compete for messages from a shared backlog. Pub/Sub will distribute messages across subscribers, so the old job will no longer see all messages and the new job will not see all messages either. During cutover/cancellation, messages can be left unprocessed or processed inconsistently, violating the zero data loss requirement.

Changing the zone is not the primary control for autoscaling limits. While zone choice can matter if a specific zone has insufficient capacity or if you are constrained by zonal resource availability, Dataflow worker scaling is typically bounded by configuration (max workers) and project quotas. The scenario describes scaling consistently stopping at 40, which strongly indicates a configured cap rather than a transient zonal capacity issue.

The number of workers (initial workers / numWorkers) sets the starting size of the Dataflow job (and sometimes a fixed size if autoscaling is disabled). With autoscaling enabled, increasing the initial workers can reduce backlog at startup, but it does not allow the job to scale beyond its configured maximum. Since the job already scales up to 40 and then stops, the issue is not the initial worker count.

Disk size per worker can help if workers are running out of disk due to shuffle spill, large state, or temporary files, which might cause performance degradation or failures. However, it does not control how far autoscaling can scale out. The symptom here is a hard stop in scaling at 40 workers with growing backlog, which points to an autoscaling upper bound rather than per-worker disk constraints.

Cloud Spanner is a globally distributed, horizontally scalable relational database with strong consistency and SQL. It supports ACID transactions and high availability, but it is typically chosen for global distribution, massive scale, or multi-region resilience. For a single-region 2.3 TB PostgreSQL lift-and-shift with minimal changes and cost minimization, Spanner is usually overkill and may require schema/SQL adjustments, increasing effort and cost.

Cloud Bigtable is a wide-column NoSQL database optimized for very high throughput and low-latency key/value or time-series access patterns. It does not provide PostgreSQL-compatible standard SQL querying, relational joins, or typical OLTP transactional semantics across arbitrary rows like a relational database. Migrating a billing API from PostgreSQL to Bigtable would require significant schema and application redesign, violating the “cannot redesign” constraint.

Cloud Firestore is a document database (NoSQL) designed for mobile/web app backends with flexible documents, real-time sync, and simple querying. While it supports transactions, it is not a relational database and does not provide PostgreSQL-compatible SQL, joins, or the same schema/constraint model. Moving a PostgreSQL billing system to Firestore would require rethinking data modeling and application logic, which is not allowed in the next quarter.

Authorized views can securely expose a subset of data (row/column filtering) to other projects, but they are not the most self-service discovery mechanism. At this scale (5 departments across 10 projects), you typically need to create/manage views and IAM bindings per consumer context, which increases administrative overhead. It’s best when you must enforce data minimization, not when broad sharing and discovery are the primary goals.

Sharing views directly with individual users does not scale and is operationally brittle. Access management becomes user-centric rather than project/department-centric, increasing the risk of misconfiguration and ongoing maintenance (onboarding/offboarding, role changes). It also doesn’t provide a structured discovery/subscription experience across multiple consumer projects, making it less aligned with enterprise governance and least-privilege best practices.

BigQuery Data Transfer Service would copy the telemetry dataset into each department’s project on a schedule. This violates the requirement to avoid creating copies and increases costs due to duplicated storage and potentially duplicated processing. It also adds operational overhead (monitoring transfers, handling failures, schema changes) and can introduce data freshness/consistency issues across copies.

Writing to a shared network file and running Hadoop is a batch pattern with high latency (minutes+), not suitable for <2 seconds end-to-end. It also introduces contention and operational complexity around shared storage across regions. Hadoop jobs are not designed for continuous, low-latency, per-event reconciliation, and handling out-of-order events in near real time would be cumbersome and expensive.

Pub/Sub ingestion is good, but a push subscription to a custom HTTPS endpoint that writes to Cloud SQL is a poor fit for 3,333 events/sec sustained. Cloud SQL is an OLTP database with connection and write throughput limits; you’d likely hit bottlenecks, hot rows (rideId), and scaling challenges. Push delivery retries can cause duplicates, requiring careful idempotency, and the custom endpoint becomes an availability and latency risk.

Per-region MySQL databases with periodic queries is fundamentally a polling/batch merge approach, which cannot meet <2 seconds latency and complicates global consistency. Cross-region reconciliation adds replication lag and operational overhead, and you still must handle out-of-order events and conflicts. This design also increases failure modes (multiple databases) and does not provide a scalable streaming aggregation mechanism.

Incorrect. A row key of service_id#timestamp groups each service’s logs together, but with a normal ascending timestamp the newest row is at the end of that service’s key range rather than the beginning. To get the latest entry, the application would need a range scan and reverse-read behavior or logic to find the last row in the prefix, which is not the simplest possible query. This design is workable, but it does not optimize as directly for the exact access pattern as a reverse timestamp per service table.

Incorrect. A reverse timestamp alone in a single shared table makes the newest rows overall appear first, but it does not encode the service identity in the row key. As a result, finding the latest row for a specific microservice would require scanning and filtering rows until a matching service is found, which is inefficient and not deterministic at scale. This schema optimizes for global recency, not per-service recency.

Incorrect. Separate tables per service do isolate each microservice’s data, but a normal timestamp row key sorts older rows first and newer rows last. That means the latest log entry is not immediately accessible with a simple first-row read and instead requires reading from the end of the keyspace or using more complex scan logic. It also retains the drawbacks of monotonically increasing keys for write concentration within each table.

Incorrect. on_retry_callback is invoked when a task attempt fails and is scheduled to retry (e.g., state transitions to UP_FOR_RETRY). With retries=3, this would generate notifications on retry events, which the requirement explicitly forbids. This option is a common trap because it sounds related to retries, but it alerts too early and too often, increasing noise.

Incorrect. Alerting on the sla_missed metric targets SLA misses (timing violations), not terminal task failure. A task can miss an SLA yet still succeed later, and SLA misses can be influenced by scheduler delays or upstream dependencies. The question explicitly says not to notify on SLA misses, so this does not meet the requirement.

Incorrect. sla_miss_callback is triggered when the task (or DAG) exceeds the configured SLA time, regardless of whether it ultimately succeeds or fails. Since the requirement is to avoid notifications on SLA misses and focus only on final failure after retries, this callback is the wrong trigger condition.

Incorrect. Even if Dataflow is configured to use CMEK for its own resources (e.g., temp storage), the existing Pub/Sub topic still stores messages at rest using Google-managed encryption, and the existing BigQuery table remains encrypted with Google-managed keys. Writing to the existing table does not change its encryption. This fails the requirement that all at-rest data for the pipeline use the centralized CMEK.

Partially addresses BigQuery but still incorrect. Creating a new CMEK BigQuery table and copying historical data would satisfy CMEK for BigQuery storage, but the existing Pub/Sub topic would continue to store messages at rest with Google-managed encryption. Because the policy mandates CMEK for all at-rest data in the pipeline, leaving Pub/Sub unchanged is noncompliant.

Incorrect. This changes Pub/Sub to CMEK but leaves the BigQuery table encrypted with Google-managed keys, which violates the requirement. Additionally, a Pub/Sub BigQuery subscription writes into a specific table; if the table must be CMEK, you need a new CMEK table (and typically a new subscription targeting it).

Kafka Connect -> Pub/Sub -> Dataflow template -> BigQuery adds multiple components and an extra hop (Pub/Sub) that is not required to meet the stated goals. It also requires operating Kafka Connect (and potentially Connect workers) and managing topic-to-subscription mappings. While it can work, it is not the minimal-architecture approach and may add latency/operational overhead versus direct KafkaIO ingestion.

A single proxy VM to relay Kafka traffic creates a throughput bottleneck and a single point of failure, violating the requirement for horizontal scalability and minimal risk. It also adds an unnecessary network hop and operational burden (VM lifecycle, patching, scaling, HA). Dataflow can already reach on-prem Kafka over Interconnect privately without inserting a proxy layer.

Compared to A, a custom Dataflow pipeline provides more control, but it still requires Kafka Connect plus Pub/Sub, increasing components and hops beyond what the question asks for. Pub/Sub can be valuable for decoupling and buffering, but the prompt prioritizes minimal added components and minimal hops while meeting latency; direct KafkaIO ingestion is simpler and typically lower-latency.

Increasing the test split can slightly reduce the variance of the evaluation metric, but it does not improve the model itself. With 120M rows, even a 15% test set (~18M) is already extremely large and provides a stable estimate. Also, making the test set larger reduces training data, which can further hurt training and does not address why training RMSE is unusually high.

More data can help when the model is overfitting (high variance) or when the training set is too small. Here, 120M labeled examples is already massive; the symptom is not variance but inability to fit the training set (high training RMSE). Collecting 80M more examples is expensive and slow, and it does not directly address underfitting or overly constrained training dynamics.

Stronger regularization is used to combat overfitting, which typically appears as very low training error and higher test error. In this scenario, training RMSE is worse than test RMSE, suggesting the model is not fitting the training data well (high bias) or training is being stopped too early. Adding dropout/L2 would usually increase training error further and likely degrade overall performance.