Google Professional Data Engineer (PDE) — Questions 151–225

Why this answer

How to eliminate wrong answers

Option A is wrong because Firestore is a NoSQL document database that does not support SQL semantics; it offers strong consistency only within a single region and lacks the global consistency and 99.999% SLA required. Option B is wrong because Cloud SQL is a traditional relational database that supports SQL but cannot horizontally scale across regions; it is limited to a single region and provides up to 99.95% availability, not 99.999%. Option D is wrong because Cloud Bigtable is a NoSQL wide-column database that does not support SQL semantics and provides only eventual consistency, not the strong consistency required for transactional data.

Why this answer

How to eliminate wrong answers

Option A is wrong because a sliding window of 5 minutes with a 2-minute slide creates overlapping windows that emit results every 2 minutes, not a single 5-minute window with late data handling; it would double-count events and not solve the late-arrival problem. Option B is wrong because setting the window duration to 7 minutes does not account for lateness—it simply shifts the window boundaries, causing data to be assigned to a different time range, which is incorrect for the intended 5-minute aggregation. Option D is wrong because a global window and watermark would aggregate all data into a single unbounded window, losing the per-5-minute grouping required by the pipeline.

Why this answer

How to eliminate wrong answers

Option A is wrong because executing a BigQuery DELETE statement per request is not scalable for high-volume deletion requests; each DELETE incurs slot consumption and can be slow on large partitioned tables, potentially exceeding the 48-hour SLA. Option B is wrong because Cloud DLP is designed for data masking and redaction in Cloud Storage, not for deleting rows from BigQuery tables; it does not address the requirement to remove customer transactions from BigQuery. Option D is wrong because BigQuery table snapshots are read-only copies used for point-in-time recovery, not for deleting specific user data; restoring a snapshot would revert the table to a previous state, not selectively remove a user's records.

Why this answer

How to eliminate wrong answers

Option A is wrong because Storage Transfer Service for on-premises requires network connectivity and is designed for smaller, incremental transfers, not for 500 TB over a 100 Mbps link. Option B is wrong because gsutil rsync is a command-line tool that relies on network bandwidth and would take an impractical amount of time (over 500 days) to transfer 500 TB at 100 Mbps. Option D is wrong because a Dataflow pipeline reading from NAS would still need to stream data over the limited 100 Mbps network, resulting in the same bandwidth bottleneck and excessive transfer time.

Why this answer

How to eliminate wrong answers

Option B is wrong because switching from pull to push subscription does not inherently increase throughput; push subscriptions have their own limitations (e.g., endpoint capacity, HTTP timeouts) and the backlog growth is a processing capacity issue, not a delivery mechanism issue. Option C is wrong because decreasing the window duration to 10 minutes does not reduce the backlog; it changes the aggregation granularity but does not affect the rate at which messages are consumed from the subscription. Option D is wrong because enabling Pub/Sub topic retention controls how long unacknowledged messages are kept, not the rate of consumption; it would only extend the time messages remain available, not reduce the backlog.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because the delete rule does not need an `isLive: true` condition to apply to live objects; in fact, `isLive: true` is the default behavior for lifecycle rules, and omitting it does not prevent the rule from applying to live objects. Option B is wrong because the `age` condition in lifecycle rules is always calculated from the object's creation date, not from the transition date. Option C is wrong because object versioning being enabled would cause the delete rule to apply only to non-current versions only if the rule explicitly targets non-current versions; the scenario describes current versions still present, and versioning does not inherently prevent deletion of current versions.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud SQL is a fully managed relational database for OLTP workloads, not designed for petabyte-scale analytical queries on external Parquet files, and requires data to be loaded into its storage. Option B is wrong because AlloyDB is a PostgreSQL-compatible database optimized for transactional and hybrid workloads, not a serverless query engine for external data in Cloud Storage, and it requires data to be imported. Option C is wrong because Dataproc with Spark SQL requires cluster management (even if ephemeral) and is not serverless; it also involves provisioning and scaling clusters, contradicting the 'no cluster management' requirement.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing the drift threshold to 0.9 would suppress alerts without addressing the underlying data drift, allowing the model to continue making inaccurate predictions. Option C is wrong because deploying a new model version trained on the original dataset would not resolve the drift; it would reuse the same outdated training data that no longer represents the current production distribution. Option D is wrong because disabling monitoring for the 'age' feature would hide the drift issue rather than fixing it, leaving the model vulnerable to degraded performance due to a drifted feature.

Why this answer

How to eliminate wrong answers

Option B is wrong because Dataproc already bills per second after a 1-minute minimum, so switching to per-second billing is not a change that reduces costs further. Option C is wrong because increasing driver memory does not reduce costs; it may actually increase costs by requiring a larger, more expensive VM, and performance gains are unlikely if the bottleneck is not driver memory. Option D is wrong because upgrading from Standard to Nearline storage increases cost (Nearline has higher retrieval and minimum storage duration fees) and is intended for infrequently accessed data, not for nightly write workloads where data is read soon after writing.

Why this answer

How to eliminate wrong answers

Option A is wrong because returning HTTP 200, even with slow processing, is treated as a successful acknowledgment; Pub/Sub would not resend the message. Option C is wrong because returning HTTP 200 with 'ack' in the body is still a valid acknowledgment (the body content is irrelevant; only the status code matters). Option D is wrong because HTTP 400 indicates a client error, which Pub/Sub treats as a permanent failure and will not retry indefinitely with a 10-second interval.

Why this answer

How to eliminate wrong answers

Option A is wrong because running a separate compaction job introduces additional latency and resource overhead, which contradicts the requirement to maintain low latency; it also adds complexity and potential data consistency issues. Option C is wrong because reducing the number of workers decreases parallelism, which can increase processing latency and may not guarantee larger files if the data volume per task remains small due to Spark's default partitioning. Option D is wrong because switching to Dataflow does not inherently solve the small files problem; Dataflow's built-in file sharding optimization (e.g., via `FileIO.write()` with `withNumShards`) still requires explicit configuration, and the question specifically asks for a solution within the existing Dataproc/Spark context.

Why this answer

Why these pairings

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Jupyter notebooks on Vertex AI Workbench are interactive environments that can produce non-deterministic outputs due to manual cell execution order, hidden state, and lack of version pinning for dependencies, making them unsuitable for reproducible training. Option C is wrong because custom containers with fixed tags (e.g., 'latest') can still lead to reproducibility issues if the underlying image is overwritten or updated; a fixed tag does not guarantee an immutable image unless combined with a digest (SHA256). Option D is wrong because Cloud Build is a CI/CD service for building and testing code, not for training models; it lacks the managed ML infrastructure and environment versioning that Vertex AI Training provides for reproducible model training.

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing the number of workers addresses throughput and latency, not data quality or malformed data errors; it does not prevent parsing failures. Option B is wrong because monitoring the 'system_lag' metric tracks pipeline latency, not data quality issues; it would not prevent or handle malformed records. Option C is wrong because while strongly typed schemas can help catch type mismatches at compile time, they do not automatically reject malformed data at runtime in Beam; the pipeline would still fail if a record cannot be parsed into the schema, and Beam does not have built-in automatic rejection to a dead letter queue without explicit handling.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud DLP masking is a data loss prevention technique that obscures data at query time but does not encrypt the underlying stored data with a CMEK; it is a transformation applied on the fly, not persistent encryption. Option C is wrong because CMEK at the dataset level encrypts the entire dataset's underlying storage (e.g., table files), but it does not provide column-level granularity; all columns are encrypted uniformly, and you cannot selectively encrypt only PII columns. Option D is wrong because storing data encrypted in Cloud Storage and using external tables with a CMEK would require managing encryption outside BigQuery and does not leverage BigQuery's native column-level encryption capabilities; external tables also have performance and feature limitations compared to native BigQuery tables.

Why this answer

How to eliminate wrong answers

Option B is wrong because reducing the number of input features may degrade model accuracy, and the question explicitly requires not sacrificing accuracy. Option C is wrong because switching from online to batch prediction eliminates real-time capability, which contradicts the requirement for real-time predictions. Option D is wrong because using a larger machine type can reduce latency but often increases cost and may introduce cold-start delays if scaling is not addressed; it does not directly solve latency spikes during peak traffic, and the question asks for a solution that does not sacrifice accuracy, which a larger machine type does not affect but is not the most targeted fix for traffic-induced latency.

Why this answer

How to eliminate wrong answers

Option B is wrong because fixed windows with a 1-hour period and data discarding would drop any data arriving after the window's end, failing the late-data requirement. Option C is wrong because session windows with a 1-hour gap duration merge events into sessions based on inactivity gaps, not fixed lateness, and do not guarantee handling of data arriving up to 1 hour late for a specific event time. Option D is wrong because the watermark estimate is a system-managed heuristic, not a configurable feature; setting it to 1 hour is not a valid Dataflow configuration and would not correctly handle late data.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Functions is a serverless compute service for single-purpose, event-driven functions, not a workflow orchestrator; it lacks native support for conditional branching, retry policies, and multi-step coordination across services. Option C is wrong because Cloud Workflows is a low-code orchestration service that can handle branching and error handling, but it is designed for simpler, synchronous workflows and does not provide the same level of scheduling, retry, and monitoring capabilities as Airflow for complex ETL pipelines. Option D is wrong because Cloud Scheduler is a cron job service that triggers tasks on a schedule, but it cannot manage conditional branching, error handling, or multi-service coordination within a single workflow.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because exporting the entire database as CSV files every hour and using WRITE_TRUNCATE overwrites the entire BigQuery table, losing all historical data and failing to capture updates and deletes in near real-time; it also does not provide exactly-once delivery. Option B is wrong because JDBCIO reads snapshots of the table at each poll interval, not change data capture, so it cannot capture deletes and may miss updates between polls; it also does not guarantee exactly-once semantics for upserts in BigQuery. Option D is wrong because Cloud SQL does not have a built-in change data capture feature that writes directly to Pub/Sub; this option describes a non-existent capability, as Cloud SQL requires third-party tools like Debezium or Datastream to capture CDC.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because exporting the model to a container and deploying on Cloud Run requires manual containerization and does not natively integrate with Vertex AI's model registry, versioning, or monitoring, and Cloud Run's scaling is based on request concurrency rather than the model-specific metrics Vertex AI provides. Option B is wrong because AI Platform Prediction with batch prediction is designed for offline, asynchronous predictions on large datasets, not for real-time online serving with automatic scaling based on live traffic. Option C is wrong because deploying the model as an API on App Engine introduces unnecessary complexity and lacks the optimized serving infrastructure, model versioning, and traffic splitting capabilities that Vertex AI Endpoints offer for ML models.

Why this answer

How to eliminate wrong answers

Option B is wrong because computing overall fairness metrics (e.g., demographic parity) only compares aggregate prediction rates across groups, which can mask per-instance bias and does not reveal whether the model is using the demographic feature in a discriminatory way. Option C is wrong because collecting more data and retraining the model is a remediation step, not an analysis step; it does not help diagnose whether the current model exhibits bias. Option D is wrong because Vertex AI Model Monitoring checks for training-serving skew (distribution drift between training and serving data), not for bias or fairness in predictions against demographic groups.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because pre-compiling the model with TensorRT optimizes inference performance (e.g., reducing latency per request) but does not eliminate the cold start latency that occurs when new instances are spun up from zero. Option C is wrong because using a larger GPU instance reduces per-request compute time but does not prevent the provisioning and model-loading delay when scaling from zero replicas. Option D is wrong because switching to batch prediction is designed for asynchronous, non-real-time workloads and does not meet strict latency requirements; it also does not address cold start for online inference.

Why this answer

How to eliminate wrong answers

Option B is wrong because a daily batch load does not meet the 15-minute ingestion requirement; it would only load data once per day, causing up to 24 hours of latency. Option C is wrong because it introduces unnecessary complexity and operational overhead by adding Pub/Sub and Dataflow, which are not needed when files are already in Cloud Storage and can be loaded directly via a Cloud Function. Option D is wrong because BigQuery federated queries do not ingest data into BigQuery; they query the CSV files directly from Cloud Storage, which is slower and does not support the required reporting use case where data must be stored in BigQuery for efficient analysis.

Why this order

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because it bypasses Data Fusion entirely, requiring raw data to be loaded into BigQuery staging tables first, which adds latency and storage costs; transformations in BigQuery are better suited for analytics queries, not as a primary ETL step in a Data Fusion pipeline. Option C is wrong because Cloud Functions have a maximum timeout of 9 minutes (540 seconds) and limited memory (up to 8 GB), making them unsuitable for large-scale batch transformations like joins and aggregations on datasets that may be gigabytes or terabytes in size. Option D is wrong because it suggests manually coding Spark on Dataproc, which defeats the purpose of using Data Fusion's visual design and managed execution; while Spark can be used, Data Fusion already abstracts and optimizes the Spark execution, so manual coding adds unnecessary complexity and maintenance overhead.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing the number of workers and vertical scaling does not address the root cause: the per-element synchronous BigQuery lookup is the bottleneck, and simply adding more workers will not reduce the latency of each individual query. Option B is wrong because BigQuery's streaming read API is designed for high-throughput ingestion, not for low-latency point lookups; it still requires a query per event and does not eliminate the network round-trip overhead. Option C is wrong because pre-joining in a batch pipeline and loading into Bigtable would work only if the patient records are static and the data is not truly streaming; it sacrifices the real-time nature of the pipeline and cannot handle late-arriving or updated patient data without reprocessing.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Functions do not support GPU backends; they are serverless compute for lightweight, event-driven code and cannot accelerate PyTorch inference. Option B is wrong because Cloud Run does not currently support GPUs; it is a managed compute platform for containerized applications but lacks GPU attachment capabilities. Option C is wrong because AI Platform Training is designed for model training jobs, not for hosting a real-time prediction service; it lacks the endpoint management, autoscaling, and low-latency serving features required for production inference.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Firestore is a NoSQL document database that does not support ACID transactions across multiple documents in the same way as a relational database, and it lacks point-in-time recovery as a built-in feature. Option B is wrong because Cloud Bigtable is a wide-column NoSQL database optimized for high-throughput analytical workloads (millions of ops/sec), not for ACID-compliant transactional workloads with point-in-time recovery. Option D is wrong because Cloud Spanner is a globally distributed relational database that provides ACID compliance and strong consistency, but it is overkill for a few thousand transactions per second and introduces unnecessary complexity and cost compared to Cloud SQL for this scale.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because dbt tests are used for validating data quality (e.g., uniqueness, not null) and do not handle transformation logic or dependency management. Option B is wrong because dbt snapshots are designed to capture historical changes in slowly changing dimensions (Type 2 SCDs), not to modularize transformation logic or manage model dependencies. Option D is wrong because dbt seeds are used to load static CSV files directly into the warehouse as tables, not to transform data or manage dependencies between models.

Why this answer

How to eliminate wrong answers

Option A is wrong because upgrading to a larger GPU does not address the CPU bottleneck; GPU memory is sufficient and GPU utilization is low, so more GPU memory would not reduce latency. Option B is wrong because increasing batch size would increase latency per request (as requests wait to be batched) and does not solve CPU-bound preprocessing; it may even worsen CPU contention. Option D is wrong because offloading preprocessing to a Cloud Run service asynchronously would add network round-trip latency and complexity, and the preprocessing is likely synchronous and required per request; it would not reduce the CPU bottleneck on the serving path.

Why this answer

How to eliminate wrong answers

Option A is wrong because using a non-volatile state backend like Cloud Bigtable does not prevent reprocessing; it only ensures state survives restarts, but the pipeline still replays uncommitted events from the last checkpoint. Option B is wrong because increasing the checkpoint interval to 60 seconds would actually increase the window of potential reprocessing, making the problem worse, not better. Option D is wrong because decreasing the checkpoint interval to 1 second would increase overhead and still not prevent reprocessing; the pipeline will always replay events from the last successful checkpoint, regardless of frequency.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because `afterProcessingTime` fires based on wall-clock time, not on data arrival, and it does not inherently cause multiple firings for the same window unless combined with other triggers or accumulation; the issue here is specifically the switch to watermark-based triggering. Option C is wrong because firing early and on-time panes is a feature of `afterWatermark` with early firings, but the question states the trigger was changed to `afterWatermark` alone (without early firings), so this does not explain the duplicates. Option D is wrong because accumulation mode (e.g., `accumulatingFiredPanes`) determines whether results are accumulated across firings, but the core cause of additional panes is the watermark reacting to late data, not the accumulation mode itself.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because storing each model version in a separate Cloud Storage bucket does not provide a mechanism for quick rollback; you would still need to redeploy the model from that bucket, which is not instantaneous. Option B is wrong because keeping the previous model in a container image and redeploying via Cloud Run is not a rollback strategy—it requires a new deployment, which takes time and does not leverage Vertex AI's managed traffic splitting. Option C is wrong because Cloud Source Repositories is a source code version control service, not a model registry; tagging model versions there does not affect production endpoint traffic.

Why this answer

How to eliminate wrong answers

Option A is wrong because processing-time windows ignore event timestamps entirely, so late-arriving data would be assigned to the wrong window, producing inaccurate aggregations. Option C is wrong because global windows with a 24-hour hold would cause unbounded latency and memory pressure, violating the requirement to avoid waiting a long time before emitting results. Option D is wrong because discarding late data after the window ends would lose delayed events, failing the requirement for accurate aggregations even with late data.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because network latency would cause delays in data processing but would not prevent the job from scaling up; the log would show slow progress or timeouts, not a scaling block. Option B is wrong because excessive memory usage per worker would cause worker crashes or OOM errors, not a failure to scale; the job would still attempt to add workers. Option C is wrong because insufficient permissions to scale would result in an authorization error when trying to create new worker instances, not a quota-related log message; the error would reference IAM roles or service account permissions.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing min_replica_count only sets a baseline number of replicas; it does not fix the autoscaling mechanism that failed to add replicas when CPU hit 100%. Option B is wrong because switching to gRPC can reduce network overhead and latency, but it does not address the root cause of scaling failure—the autoscaler not triggering due to missing metrics. Option D is wrong because raising max_replica_count only increases the upper limit; if the autoscaler never triggers scaling (due to missing metrics), the replicas will remain at the initial count regardless of the max setting.

Why this answer

How to eliminate wrong answers

Option A is wrong because training with a different loss function (e.g., cross-entropy vs. mean squared error) does not directly cause the model to output logits instead of probabilities; the output layer's activation function (or lack thereof) determines whether outputs are logits or probabilities. Option B is wrong because incorrect input scaling would affect the prediction values (e.g., shifting or scaling them), but it would not change the fundamental nature of the output from logits to probabilities; the model would still output whatever its final layer produces. Option C is wrong because the endpoint configuration (e.g., machine type, traffic splitting, or model version) does not alter the model's output format; the endpoint simply serves the model's raw predictions as-is.

Why this answer

How to eliminate wrong answers

Option B is wrong because it requires manual intervention to stop the pipeline and update the BigQuery schema each time a new column appears, which is not a long-term solution and defeats the purpose of a streaming pipeline that needs to handle schema evolution automatically. Option C is wrong because switching to Dataproc with Spark and Avro format does not inherently solve the schema evolution problem; it adds unnecessary complexity and still requires handling schema changes in the Spark job or BigQuery sink. Option D is wrong because using a Cloud Function to transform data and add null columns for missing fields is a brittle workaround that requires maintaining a separate function and does not scale well with frequent schema changes; it also introduces additional latency and cost.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B (Cloud Dataproc) is wrong because it is a managed Hadoop/Spark service designed for batch and stream processing but requires manual cluster management and autoscaling configuration, making it less suitable for handling unpredictable traffic spikes with low latency compared to the serverless Pub/Sub + Dataflow pipeline. Option C (Cloud Functions) is wrong because it is a lightweight, event-driven compute service with a maximum timeout of 9 minutes and limited throughput, making it unsuitable for high-volume, real-time streaming ingestion and processing. Option D (Cloud IoT Core) is wrong because it is specifically designed for ingesting data from IoT devices using MQTT/HTTP protocols, not for general web clickstream data from an HTTP endpoint, and it lacks the native streaming analytics capabilities needed for low-latency insights.

Why this answer

How to eliminate wrong answers

Option B is wrong because Dataproc is a managed Spark/Hadoop service for batch processing, not a primary query interface for ad-hoc analytics; it would add unnecessary complexity and latency compared to BigQuery's direct Avro querying. Option C is wrong because Cloud SQL is a relational database for transactional workloads, not designed for large-scale analytics on Avro data in a data lake, and it cannot directly query Avro files. Option D is wrong because Firestore is a NoSQL document database for real-time applications, not suitable for analytical queries on large volumes of streaming data in Avro format.

Why this answer

How to eliminate wrong answers

Option B is wrong because exporting predictions to Cloud Logging and using Log Analytics for distribution comparison is a manual, ad-hoc approach that lacks native drift detection algorithms and automated retraining triggers, making it unsuitable for continuous monitoring. Option C is wrong because storing predictions in BigQuery and running scheduled SQL queries to detect drift requires custom statistical logic and does not leverage Vertex AI's built-in drift detection, alerting, or pipeline integration, leading to higher maintenance overhead. Option D is wrong because Cloud Monitoring tracks prediction latency and error rates, which are operational metrics, not feature distribution shifts; relying on error rates as a proxy for drift is indirect and unreliable, and manual retraining defeats the goal of continuous automation.

Why this answer