Google Professional Cloud Database Engineer (PCDE) — Questions 226–300

Why this answer

Why this answer

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 a Cloud Build step runs during the build phase, not after deployment, and cannot conditionally target only staging environments; it would run for all deployments. Option B is wrong because manual flag enabling defeats the purpose of automated CI/CD and introduces human error risk, especially in staging where repeatable automation is expected. Option D is wrong because a Kubernetes ConfigMap is a static configuration mechanism that cannot dynamically toggle feature flags at runtime via LaunchDarkly, and it bypasses the flag management system entirely.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because BigQuery does not support traditional indexes; it uses columnar storage and clustering/partitioning for performance, so adding indexes is not applicable. Option C is wrong because replacing LEFT JOINs with INNER JOINs changes the business logic by excluding rows that do not have matching records in the joined table, which may alter the BI report results. Option D is wrong because increasing the number of BigQuery slots only addresses resource contention, not the root cause of slow JOINs; it is a costly workaround that does not optimize the query structure.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because materialized views are stored on the same primary instance and do not offload query processing; they still consume the same CPU and I/O resources, and refreshing them can add further load. Option B is wrong because changing joins to subqueries does not reduce resource consumption—subqueries often perform worse than joins in PostgreSQL due to lack of optimization for correlated subqueries. Option C is wrong because increasing vCPUs on the primary instance only scales vertically, which may help but does not isolate reporting workloads; the transactional queries still compete for the same resources, and scaling is limited by instance tier constraints.

Why this answer

How to eliminate wrong answers

Option B is wrong because enabling automatic storage increase only triggers when the instance reaches a predefined threshold (typically 90% of current capacity), and it increases storage in small increments (e.g., 10 GB), which may not be sufficient to immediately address a near-limit situation and could still risk downtime if the threshold is crossed unexpectedly. Option C is wrong because deleting and recreating the instance would cause complete downtime and data loss unless a backup is restored, which is not a zero-downtime solution. Option D is wrong because shutting down the instance to increase disk size introduces downtime, which violates the requirement of no downtime; Cloud SQL does not require a shutdown for storage increases.

Why this answer

How to eliminate wrong answers

Option B is wrong because configuring ingress and a VPC connector addresses network access control and private connectivity, not cold starts or CPU burst handling. Option C is wrong because setting --max-instances and --concurrency controls scaling limits and request concurrency, but does not prevent cold starts or manage CPU throttling. Option D is wrong because setting --memory and --cpu defines resource allocation, but does not keep instances warm or disable CPU throttling to handle sudden spikes without cold starts.

Why this answer

How to eliminate wrong answers

Option A is wrong because Database Migration Service is used for migrating databases to Cloud SQL, not for failover; failing over to a read replica would cause downtime during the promotion process and does not diagnose the latency issue. Option C is wrong because gcloud sql instances describe only returns static configuration metadata (e.g., machine type, region, maintenance window) and does not provide real-time query performance or latency diagnostics. Option D is wrong because VPC Flow Logs capture network-level metadata (source/destination IP, ports, packet count) and cannot reveal slow SQL queries, lock waits, or database engine internals.

Why this answer

How to eliminate wrong answers

Option B is wrong because BI Engine works with both tables and SQL views, not exclusively with views. Option C is wrong because BI Engine fully supports clustered tables and can leverage clustering metadata for efficient pruning. Option D is wrong because BI Engine supports partitioned tables and can use partition pruning to reduce the data scanned.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Spanner does not support an 'ONLINE' clause in ALTER TABLE; that syntax is specific to Oracle and other databases, not Spanner. Option B is wrong because the --async flag in gcloud spanner databases ddl update merely returns immediately without waiting for the DDL operation to complete, but the underlying CREATE INDEX operation is still the same non-blocking mechanism; the flag does not change the blocking behavior—it only affects the client-side wait. Option C is wrong because taking a backup and creating an index on a new instance is unnecessary overhead; Spanner's native non-blocking index creation eliminates the need for such a cumbersome migration, which would also introduce potential data inconsistency and extended downtime during traffic switch.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because applying for a quota increase for concurrent queries addresses the number of queries running simultaneously, not the resource consumption of a single complex query. Option B is wrong because increasing the number of slots allocated to the project provides more parallel processing capacity but does not fix the underlying issue of a query that exceeds per-stage memory or shuffle limits; it may only delay the failure. Option C is wrong because the '--maximum_billing_tier' flag is a legacy BigQuery feature that caps query cost, not a way to increase resources; it cannot resolve memory or complexity errors.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because changing the partition column to customer_id would prevent partition pruning for the date filter (last 30 days), forcing a full table scan of 10 TB and degrading performance. Option B is wrong because removing clustering entirely would eliminate the benefit of sorted blocks within partitions, increasing the amount of data scanned even with partition pruning. Option D is wrong because manually reclustering the table daily is unnecessary and inefficient; BigQuery automatically manages clustering metadata during write operations, and manual reclustering does not provide additional performance gains for this query pattern.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because timestamp first scatters readings for the same sensor across the entire table, making queries for a specific sensor's time range require a full table scan or multiple lookups. Option B is wrong because hashing the sensor_id destroys the natural sort order, so even though the sensor_id is first, the hash distributes rows randomly, preventing efficient range scans over time. Option D is wrong because a random UUID provides no ordering or grouping, forcing full table scans for any sensor-specific time-range query.

Why this answer

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 increasing the machine type adds cost and masks the underlying issue without diagnosing why CPU spikes occur; it treats a symptom, not the cause. Option B is wrong because enabling connection pooling reduces connection overhead but does not address CPU spikes or query performance; it may even hide connection-related issues without solving the root cause. Option D is wrong because setting a maximum connection limit or reducing connection lifetime can cause application errors or dropped connections without identifying why connections are increasing or why CPU is spiking.

Why this answer

How to eliminate wrong answers

Option A is wrong because `--num-nodes=2` is the correct flag, but the statement says 'instead of --nodes' — there is no `--nodes` flag in the gcloud spanner instances create command; the correct flag is `--num-nodes`. Option B is wrong because setting `--processing-units=2000` would create a 2-node equivalent in processing units (1000 per node), but the question explicitly asks for '2 nodes', not a processing-units-based instance, and removing `--nodes` is not a valid approach. Option D is wrong because `--processing-units=0` is invalid (minimum is 100) and combining `--nodes=2` with `--processing-units` is not allowed; the flags are mutually exclusive.

Why this answer

How to eliminate wrong answers

Option A is wrong because exporting and importing a SQL file is a manual, time-consuming process that does not provide near-real-time replication, leading to significant data loss and longer recovery time objectives (RTO). Option C is wrong because a same-region read replica does not protect against a regional outage; it only provides read scalability and high availability within the same region. Option D is wrong because Cloud SQL does not support 'cross-region backup replicas'; backups are regional and must be manually copied or restored to another region, which is slower and less automated than a cross-region read replica promotion.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Scheduler triggers exports to Cloud Storage, which are manual snapshot-like backups that do not support point-in-time recovery and require additional steps to restore, adding complexity. Option B is wrong because exporting to Cloud Storage via a cron job is a manual, scripted approach that lacks the automated retention management and integrated restore capabilities of Cloud SQL's native backup feature. Option D is wrong because gcloud commands can create backup schedules, but this still requires manual setup and does not leverage the fully managed automatic backup feature with built-in retention and point-in-time recovery that is simpler to enable via the Cloud Console or API.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud VPN provides encrypted connectivity but does not optimize bulk data transfer; rsync over VPN lacks built-in retry logic and parallelization for 10 TB, leading to slow and unreliable transfers. Option C is wrong because gsutil cp in parallel can transfer data but requires manual management of concurrency, retries, and consistency checks, and is less reliable than a managed service for 10 TB. Option D is wrong because Dataflow is a stream and batch processing framework, not a data transfer tool; using it to read and write data adds unnecessary complexity and cost compared to a purpose-built transfer service.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because a single-field index on timestamp alone cannot filter by conversationId, so Firestore would have to scan all messages across all conversations to find the most recent 50 for a specific conversation, which is inefficient and expensive. Option C is wrong because an index on conversationId only allows filtering by conversation but does not support ordering by timestamp, so Firestore would need to sort the results in memory, which fails for large datasets and cannot guarantee efficient retrieval of the most recent 50. Option D is wrong because a composite index on (senderId, timestamp) filters by sender, not by conversation, so it cannot efficiently retrieve messages for a given conversation; it would require a full collection scan or an additional filter step.

Why this answer

How to eliminate wrong answers

Option A is wrong because adding indexes on base tables does not address the core issue of repeated CTE evaluation; indexes can help but are not a targeted fix for the redundant execution of CTE logic. Option C is wrong because reusing the same CTE name multiple times does not change execution behavior; each reference still triggers a separate evaluation of the CTE definition. Option D is wrong because replacing CTEs with derived tables in the FROM clause does not change the execution plan; derived tables are also non-materialized and will be re-evaluated on each reference, offering no performance benefit.

Why this answer

How to eliminate wrong answers

Option A is wrong because BigQuery does not support partitioning by store_id (partitioning requires a DATE, TIMESTAMP, or INTEGER column with a specified range), and clustering alone cannot provide the same level of cost reduction as date-based partitioning for time-range queries. Option C is wrong because an unpartitioned table with clustering only still requires scanning the entire table for queries that filter by date, leading to higher costs and slower performance compared to a partitioned design. Option D is wrong because materialized views are automatically refreshed and incur additional storage costs; they do not replace the need for an efficient base table design, and they cannot be used as the primary ingestion target for near-real-time data from Pub/Sub.

Why this answer

How to eliminate wrong answers

Option A is wrong because storing repeated fields (e.g., ARRAY<STRUCT>) for line items within a single row violates Cloud Spanner's 10 MB row size limit and prevents efficient indexing or atomic updates of individual line items, degrading throughput for large orders. Option C is wrong because separate tables with a foreign key and index on ORDER_ID require a two-step lookup (index scan then table access) and may involve distributed reads if the index and data are on different splits, increasing latency compared to interleaving. Option D is wrong because denormalizing product information into LineItems does not address the core read pattern (retrieving all line items for an order) and introduces data redundancy and update anomalies without improving colocation; it still requires a separate table or repeated fields, neither of which matches the interleaved design's performance benefit.

Why this answer

Why this answer

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 increasing the buffer pool size only caches more data in memory, which may reduce physical I/O but does not eliminate the logical I/O caused by key lookups; the query still performs the same number of page accesses. Option B is wrong because rebuilding the index with FILLFACTOR=80 reduces page splits and fragmentation but does not change the index structure to include additional columns, so key lookups still occur. Option D is wrong because using a FORCESEEK query hint forces the optimizer to use an index seek, but it does not prevent key lookups if the index does not cover all required columns; it may even degrade performance by forcing a suboptimal plan.

Why this answer

How to eliminate wrong answers

Option B is wrong because exporting and re-importing a table with billions of rows would cause significant downtime and performance degradation, defeating the requirement for zero downtime. Option C is wrong because `CREATE INDEX` does not add a column; it creates an index on existing columns, and adding a column requires `ALTER TABLE`. Option D is wrong because creating a new table, backfilling data, and renaming tables would require complex coordination and likely cause downtime or data inconsistency, whereas Spanner's native `ALTER TABLE` handles the change online without such manual steps.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because the slow query log in Cloud Logging requires manual filtering and may not be enabled by default, whereas Query Insights provides immediate, structured visibility into high-latency queries. Option C is wrong because increasing the instance tier only masks the symptom by adding more resources, without identifying or fixing the underlying problematic queries. Option D is wrong because enabling the general query log generates excessive volume and performance overhead, and parsing it manually is inefficient compared to using Query Insights' built-in analysis.

Why this answer

How to eliminate wrong answers

Option A is wrong because the `skaffold dev` loop is designed for continuous local development with file watching and automatic redeployment, not for a single CI/CD pipeline execution on Cloud Build. Option B is wrong because `skaffold init` is used to generate an initial `skaffold.yaml` configuration file, not to run a deployment. Option C is wrong because `skaffold delete` is used to clean up deployed resources (e.g., deleting Kubernetes manifests), not to build and deploy.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because manually refreshing materialized views outside peak hours does not address the root cause of slow refreshes; it merely shifts the timing, and the underlying full-table scan cost remains unchanged. Option B is wrong because increasing the refresh interval reduces the frequency of refreshes but does not optimize the refresh operation itself; the refresh will still be slow when it runs, and downstream reports may become even more stale. Option C is wrong because disabling automatic refresh and using scheduled queries to rebuild the materialized view replaces an optimized incremental refresh with a full rebuild, which is typically slower and more expensive, and it loses BigQuery's automatic incremental refresh capabilities.

Why these pairings

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Anthos Service Mesh is a service mesh platform for traffic management and observability, not a configuration distribution mechanism; it does not replace or enhance Config Sync's sync trigger. Option B is wrong because `syncWait` is not a valid parameter in Config Sync; the correct parameter for controlling sync frequency is `SYNC_REPO_SECS` or the `--sync-period` flag, and setting it to 1 second would be impractical and unsupported. Option D is wrong because `gcloud container clusters update` is used to modify cluster-level settings (e.g., add-ons, maintenance windows), not to apply Kubernetes resource configurations from a Git repository; it does not integrate with Config Sync's reconciliation loop.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because interleaved tables optimize joins and hierarchical data access, not point reads by a non-primary-key column. Option C is wrong because reordering the primary key to (OrderDate, CustomerID) would break existing data distribution and still not optimize reads by CustomerID alone, as OrderDate would still be required for efficient lookups. Option D is wrong because increasing nodes improves throughput and capacity, not the fundamental latency of point reads caused by missing index support.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because Cloud SQL for MySQL is a relational database, not an in-memory cache, and migrating to it would fundamentally change the architecture and performance characteristics of the application. Option C is wrong because Cloud Memorystore for Redis does not support AOF persistence; enabling it is not possible in the managed service. Option D is wrong because increasing maxmemory and relying on the allkeys-lru eviction policy only manages memory usage under pressure, it does not provide any durability or persistence to prevent data loss during maintenance events.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Functions writing directly to BigQuery cannot guarantee exactly-once semantics; a function may be retried on failure, leading to duplicate rows, and it lacks built-in deduplication or checkpointing for streaming data. Option C is wrong because Cloud Pub/Sub subscriptions do not support writing directly to BigQuery; Pub/Sub is a messaging service and requires a subscriber (like Dataflow) to process and write data, so this option is not technically feasible. Option D is wrong because Cloud Dataproc running Spark streaming jobs introduces higher operational overhead and latency compared to Dataflow, and while Spark can achieve exactly-once semantics, it requires more manual configuration and does not integrate as seamlessly with BigQuery's streaming buffer as Dataflow does.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because adding nodes increases throughput and storage capacity but does not diagnose the root cause of a hot spot; it may mask the symptom without fixing the key design issue. Option C is wrong because switching to Cloud SQL is a migration that abandons Spanner's horizontal scaling and global consistency, and does not help diagnose the existing hot spot in Spanner. Option D is wrong because CPU utilization metrics indicate overall resource pressure but cannot pinpoint which specific keys or access patterns are causing contention, so they are insufficient for diagnosing hot spots.

Why this answer

How to eliminate wrong answers

Option A is wrong because Query insights is a performance monitoring and troubleshooting feature that provides query-level metrics and execution plans, not a mechanism for backup or recovery. Option C is wrong because write-ahead logging (WAL) archiving is a PostgreSQL internal mechanism for replication and crash recovery, but in Cloud SQL, PITR is enabled via automatic backups, not by directly configuring WAL archiving. Option D is wrong because binary logging is a MySQL/MariaDB feature used for replication and PITR in those databases, not applicable to PostgreSQL, which uses WAL instead.

Why this answer

How to eliminate wrong answers

Option A is wrong because creating a secondary index on the primary key column is redundant—the primary key is already indexed by the table's primary index, and this does not address the cross-region latency issue. Option B is wrong because increasing the number of Spanner nodes distributes load and improves throughput, but does not reduce the physical distance or network round-trip time between European users and the regional leader, so read latency remains high. Option D is wrong because Cloud Spanner does not support partitioning a database by region with directed reads; directed reads can be used to route reads to specific zones, but the database is a single global resource, and partitioning would break global consistency and transaction semantics.