Practice Test #2

Denormalizing by embedding dimension attributes can reduce join overhead and sometimes improve dashboard latency, but it does not solve the main problem: the query scans nearly the entire fact table because it is unpartitioned. You would still read most of the 4.5 TB to find the last 30 days. It can also increase storage due to repeated dimension attributes, violating the “without increasing storage costs” constraint.

Sharding into multiple tables by scooter_id is generally a BigQuery anti-pattern. It increases operational complexity (many tables, harder permissions, harder lifecycle management) and does not align with the access pattern (time-based filtering). It can also hurt performance because queries may need to union many shards. BigQuery recommends partitioning/clustering rather than manual sharding for large tables.

Materializing dimension data in views (or even materialized views) does not reduce the bytes scanned from the large fact table when filtering by date. The bottleneck is fact table I/O, not dimension lookup. Materialized views are useful when they pre-aggregate or pre-filter large datasets, but “materializing dimension data” alone won’t prevent scanning most of the unpartitioned fact table.

Running Spark on GKE is possible (e.g., Spark on Kubernetes), but it requires creating and operating a Kubernetes cluster, configuring Spark images, service accounts, networking, and often managing connectors and dependencies. This violates the requirement to minimize installation and infrastructure management. It’s better when you already standardize on Kubernetes and need container-based portability, not for a simple lift-and-shift nightly Spark ETL with minimal ops.

A single Compute Engine VM (or even a small set of VMs) would require you to install and manage Spark, configure scaling, handle job scheduling, and set up the GCS and BigQuery connectors and authentication. It also introduces capacity planning and reliability concerns for a 2 TB nightly workload. This option has the highest operational burden and is not aligned with the requirement to avoid infrastructure and connector setup.

Dataproc clusters are managed Hadoop/Spark clusters and can run Spark 3 jobs with GCS and BigQuery connectors available. However, you still manage cluster lifecycle (create/delete or keep it running), sizing, autoscaling policies, initialization actions, and image/version management. For a nightly batch job, you’d either pay for an always-on cluster or automate ephemeral cluster creation, both adding operational overhead compared to serverless.

Incorrect. Converting scheduled batch queries to interactive increases competition for slots during peak hours because interactive jobs try to run immediately. Batch priority is specifically designed to be opportunistic and run when resources are available, helping smooth utilization. This option would likely worsen queuing/cancellations and does not implement any cross-team priority scheme or guaranteed capacity.

Incorrect. Creating additional projects to multiply on-demand slot concurrency is explicitly disallowed by the requirement (“avoid creating additional projects”). Even if allowed, it fragments governance, complicates IAM, data sharing, and cost controls, and still doesn’t provide a clean, enforceable priority model across teams. It’s also an anti-pattern compared to using Reservations for capacity planning.

Incorrect. Requesting a quota increase might raise the on-demand concurrency cap, but it does not guarantee predictable performance or enforce a Finance > Operations > Marketing priority scheme. On-demand remains a shared, best-effort model subject to variability, and quota increases may be limited or slow to approve. Reservations are the intended solution for guaranteed capacity and workload management.

Dataproc with HDFS on persistent disks is a straightforward lift-and-shift and can meet the 60-day deadline. However, it preserves the most expensive and operationally heavy part of Hadoop: HDFS capacity on PD that you pay for continuously. It also tends to encourage long-running clusters, which is misaligned with the agency’s low midday utilization and cost-savings goals.

Running Spark on Dataproc with HDFS while simultaneously converting all Hive tables to BigQuery adds major scope and risk. Converting 900 TB of Parquet/ORC plus validating semantics, partitions, and downstream dependencies is time-consuming and can jeopardize the 60-day deadline and the 5:00 a.m. SLA. BigQuery migration is valuable, but better as a later modernization phase after stabilizing in cloud.

Rewriting Spark pipelines to Dataflow and migrating Hive fully to BigQuery is the most serverless approach, but it is not realistic within 60 days for 250 jobs without significant engineering effort, testing, and operational change. The risk of missing the nightly SLA is high. This option prioritizes long-term architecture over immediate, low-risk migration and near-term savings.

Cloud SQL is a fully managed relational database (MySQL/PostgreSQL/SQL Server) with strong ACID transactions and familiar SQL. However, you still provision instances, manage sizing, and scale via vertical scaling, read replicas, and potentially sharding. Handling sudden growth to very high write QPS globally can be complex and may require significant operational planning, which conflicts with the requirement to avoid managing database servers.

BigQuery is a serverless, highly scalable analytics data warehouse (OLAP). It excels at large-scale analytical queries and batch/stream ingestion for reporting, not low-latency transactional reads/writes for a microservice. BigQuery does not provide the operational transaction semantics needed for reservation updates and is not intended to serve as the primary OLTP database for high-QPS application traffic.

Cloud Bigtable is a highly scalable, low-latency wide-column NoSQL database suited for time-series, IoT, and large key-value workloads. While it can handle very high throughput, it is not serverless: you must provision and scale clusters/nodes and manage capacity to meet peak demand. It also lacks the simple entity-group transactional model described; it is not the best fit for small-group ACID transactions.

Incorrect. While complex workflows may consume more agent time per case, they are harder to automate successfully: many turns, more ambiguity, more edge cases, and more integrations (insurance, authorizations, policy rules). Early failures can increase transfers, frustrate patients, and reduce trust in the assistant. This approach is higher delivery risk and less likely to hit a near-term KPI like a 40% reduction in agent volume.

Incorrect. A representative mix may be useful for long-term product balance, but it dilutes focus and slows time-to-impact. Including long, complex intents early increases design, testing, and operational overhead, raising the chance of poor experiences and more handoffs. For a first-quarter target, prioritization should be driven by ROI and feasibility (volume and simplicity), not mirroring the distribution.

Incorrect. Keyword frequency is not a sound criterion for intent automation priority. NLU confusion is addressed through training data quality, intent design, entity modeling, and disambiguation strategies—not by selecting intents where a keyword appears once. Also, “insurance” is often a high-stakes, complex domain; avoiding it based on keyword heuristics does not align with the stated KPI or best practices for conversational design and rollout.

An App Engine cron job introduces custom orchestration and still doesn’t guarantee the BigQuery load has completed unless you add additional logic (polling job status, retries, backoff). It increases operational burden and failure modes compared to Dataprep’s native scheduling. App Engine cron is generally not the first choice when the service (Dataprep) already provides built-in scheduling for recurring batch runs.

Cloud Scheduler can trigger HTTP endpoints or Pub/Sub, but “exporting a recipe as a Dataprep template” and scheduling it externally is not the standard, simplest Dataprep automation pattern for this use case. Even if you could trigger runs via APIs, you would still need authentication, error handling, and potentially polling for upstream completion. Native Dataprep scheduling is more appropriate and exam-aligned.

Dataprep jobs do not become Dataflow templates in a direct, standard way. Dataflow templates are for Apache Beam pipelines, not Dataprep recipes. Cloud Composer is powerful for complex DAG orchestration and dependency management, but it is overkill here and adds cost/ops overhead. Unless the question requires multi-step workflows, cross-system dependencies, or event-driven triggers, Composer is not the best fit.

Checking the dashboard layer can be useful, but it’s not the best next step because you haven’t proven whether the pipeline output is actually missing. In streaming systems, apparent “loss” is often due to windowing/late data or aggregation timing rather than rendering. First isolate the discrepancy by validating counts through the pipeline; then, if outputs match expectations, investigate caching/refresh logic in the visualization tier.

Cloud Monitoring can show publish/ack rates, backlog, and latency, but it cannot pinpoint specific “missing messages” for recovery when publish succeeded. Pub/Sub is at-least-once and doesn’t provide a built-in mechanism to enumerate which individual messages were not processed end-to-end. If messages were dropped downstream (e.g., late data/windowing), Pub/Sub metrics won’t directly reveal which ones to recover.

Switching to a push subscription is not an appropriate fix or isolation step. Dataflow’s Pub/Sub source connector is designed for pull subscriptions; push delivery targets an HTTPS endpoint and doesn’t inherently improve reliability for Dataflow. The observed symptom (3–5% fewer scans) is more consistent with event-time/windowing/triggering or downstream aggregation/display behavior than with the pull vs push delivery model.

Incorrect. BigQuery CMEK encrypts the entire dataset/table at rest with a single customer-managed key (or small set of keys). If you disable/destroy the CMEK, you lose access to all data in the protected resource, not just one patient’s columns. CMEK does not provide per-row/per-patient cryptographic deletion and does not support selective undecipherability at the column level.

Incorrect. While Cloud KMS can be used for envelope encryption, “use CMEK to encrypt records before loading” is not a native BigQuery feature for per-record encryption. CMEK is applied to BigQuery storage resources, not used as a record-by-record encryption mechanism. Also, the requirement includes decrypting at query time using SQL, which aligns with AEAD functions rather than a generic pre-load encryption approach.

Incorrect. This relies on a custom cryptographic library in the ETL pipeline (client-side encryption), which the prompt explicitly forbids. Even if it could meet crypto-deletion conceptually, it would not be “native Google Cloud capabilities,” and it complicates key management, access control, and SQL-based decryption inside BigQuery without using BigQuery’s built-in AEAD functions.