Google Professional Cloud Database Engineer (PCDE) — Questions 751–825

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because streaming inserts are designed for real-time, row-by-row data ingestion, not for batch updating a fact table incrementally from a transactional database. Option B is wrong because BigQuery Data Transfer Service is used for automated imports from external SaaS sources (e.g., Google Ads, Amazon S3), not for executing custom SQL logic like MERGE against existing tables. Option D is wrong because WRITE_TRUNCATE replaces the entire table each load, which is inefficient and loses historical data, whereas incremental updates require preserving existing rows.

Why this answer

How to eliminate wrong answers

Option A is wrong because a non-partitioned table forces BigQuery to scan all rows even for a single month, leading to higher costs and slower performance despite clustering on product_category. Option C is wrong because manual sharding (e.g., table names like sales_20250101) is a legacy pattern that requires complex query logic (UNION ALL) and loses automatic partition pruning, plus BigQuery discourages sharding in favor of native partitioning. Option D is wrong because partitioning by product_category would create many small partitions (one per category), which is inefficient for date-range queries; clustering on date cannot compensate for the lack of date-based partition pruning, so monthly aggregations would still scan all partitions.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because federated BigQuery queries against Spanner execute reads directly on the Spanner instance, which can consume CPU and impact transactional latency, especially under heavy BI query loads. Option B is wrong because manual exports to Cloud Storage and batch loads into BigQuery introduce significant latency and operational overhead, making it unsuitable for near-real-time BI requirements. Option D is wrong because even read-only transactions on Spanner consume instance resources and can contend with transactional writes, degrading OLTP performance under concurrent BI query loads.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because application-level dual writes require modifying application code to write to both databases simultaneously, which introduces complexity, potential data inconsistency, and does not guarantee minimal downtime during the actual cutover. Option C is wrong because creating a read replica in Cloud SQL and promoting it is not a supported migration path from an external PostgreSQL database; Cloud SQL read replicas can only be created from a Cloud SQL primary instance, not from an on-premises or external source. Option D is wrong because exporting with pg_dump and importing into Cloud SQL is a batch, offline process that requires the source database to be read-only or stopped during the export to ensure consistency, resulting in significant downtime.

Why this answer

How to eliminate wrong answers

Option A is wrong because GROUP BY with SUM(amount) aggregates sales per day or per product, but it cannot produce a running total across dates; it loses the row-level context needed for cumulative calculations. Option B is wrong because `ROWS BETWEEN 30 PRECEDING AND CURRENT ROW` sums exactly 31 rows (30 preceding + current), which is a fixed row count, not a time-based window of 30 days; if dates are missing or irregular, this will not correctly represent sales over the last 30 calendar days. Option D is wrong because a correlated subquery to sum over previous dates is inefficient and scales poorly; it requires a separate subquery execution for each row, leading to O(n²) performance, whereas a window function operates in a single pass over the data.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Build triggers are used for building and testing, not for managing deployment approvals within Cloud Deploy; manual approval in Cloud Build is a separate feature for build pipelines, not for release promotion. Option B is wrong because a preDeploy hook runs custom logic before deployment but does not pause for manual approval; it cannot block the deployment pending human sign-off. Option C is wrong because a postDeploy hook runs after deployment has already occurred, so it cannot prevent the release from being deployed to production.

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 Artifact Registry does not automatically sign images on push; signing is a separate, explicit step that must be performed by a trusted entity like Cloud Build. Option B is wrong because canary deployment and audit logging verify deployment behavior and provenance, but they do not enforce cryptographic attestation or prevent unsigned images from being deployed. Option C is wrong because while enabling Binary Authorization on the GKE cluster is necessary, simply configuring the admission controller to use Cloud KMS is insufficient; the policy must specifically require attestations from a known signer, and the images must be signed by that signer.

Why this answer

How to eliminate wrong answers

Option A (4) is wrong because 4 nodes would provide 16 TB of raw SSD storage, which is excessive for only 3 TB of data and would be an inefficient use of resources. Option B (1) is wrong because a single node cannot provide data redundancy or high availability; in a production database cluster, you need at least 2 nodes to support replication and failover. Option D (3) is wrong because 3 nodes would provide 12 TB of raw storage, but after accounting for replication (typically 2 copies), the effective storage is 6 TB, which is more than needed; 2 nodes are sufficient and more cost-effective.

Why this answer

How to eliminate wrong answers

Option A is wrong because Database Migration Service's continuous export is designed for ongoing replication to external targets, not for creating a point-in-time consistent snapshot from Cloud SQL's binary logs. Option C is wrong because mysqldump at audit time would lock tables and impact production performance, and it cannot guarantee a consistent snapshot that includes all changes within a specific time window without binary log replay. Option D is wrong because Cloud SQL's export with a specific backup-id only exports from a full backup, not from a point-in-time that includes all changes within the last 24 hours; backups are typically taken at scheduled intervals, not at the exact audit time.

Why this answer

How to eliminate wrong answers

Option A is wrong because a single top-level collection with millions of messages creates a massive index that degrades query performance and increases latency for real-time updates, as Firestore must scan across all conversations. Option B is wrong because a single-field index on timestamp alone cannot efficiently filter by conversationId, leading to full collection scans or requiring client-side filtering, which breaks real-time requirements. Option D is wrong because storing recent messages in a nested array within a parent document violates Firestore's 1 MiB document size limit and does not support scalable querying for millions of messages, while the separate collection for older messages introduces complexity without indexing benefits.

Why this answer

How to eliminate wrong answers

Option A is wrong because storing all state in a single file would cause conflicts and IAM does not provide fine-grained concurrency control; Terraform's locking mechanism is designed to handle this at the object level, not via user permissions. Option B is wrong because `terraform force_unlock` is a manual command to break a stuck lock, not a preventive measure; running it before each run would defeat the purpose of locking and could still allow concurrent operations. Option D is wrong because enabling object versioning on the GCS bucket provides history and rollback capabilities but does not prevent concurrent writes; without locking, two simultaneous `terraform apply` commands could still corrupt the state.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because mysqldump is a logical backup tool that exports data as SQL statements, which requires taking the source database offline or locking tables during the dump, and the import process can be slow and error-prone for a 100 GB database, leading to significant application downtime and potential data inconsistency. Option C is wrong because BigQuery Data Transfer Service is designed for loading data into BigQuery, a data warehouse, not for migrating operational databases to Cloud SQL, and it does not support MySQL as a source or Cloud SQL as a target. Option D is wrong because Storage Transfer Service is used for moving objects from on-premises or other cloud storage to Google Cloud Storage (GCS), not for migrating live databases to Cloud SQL, and it cannot handle the transactional consistency or schema requirements of a MySQL database.

Why this answer

How to eliminate wrong answers

Option A is wrong because monotonically increasing integers concentrate writes on the last tablet, causing severe hot-spotting. Option B is wrong because timestamps are inherently monotonically increasing, leading to the same hot-spotting issue as auto-increment keys. Option C is wrong because a composite key with user_id and order_date still has a time-ordered component (order_date) that causes sequential writes to cluster on the same tablet, especially for users placing orders in quick succession.

Why this answer

How to eliminate wrong answers

Option A is wrong because reducing the number of replicas does not reduce write latency; it may actually increase latency by reducing read availability and fault tolerance, and writes still require leader confirmation. Option B is wrong because directed reads are used to route read requests to the nearest replica for lower read latency, but they do not affect write latency, as writes must still go through the leader. Option D is wrong because follower reads are a read-only feature that allows reads from non-leader replicas; writes cannot be performed on followers, so enabling follower reads for writes is technically invalid.

Why this answer

How to eliminate wrong answers

Option B is wrong because it introduces an external Elasticsearch instance, which violates the schema design constraint for Cloud SQL for PostgreSQL and adds operational complexity; the question asks for a schema design within PostgreSQL. Option C is wrong because a LIKE '%term%' query cannot use a B-tree index efficiently—it requires a full table scan, which is impractical for millions of product descriptions. Option D is wrong because materialized views with trigram indexes (pg_trgm) support fuzzy matching but are not optimized for full-text search ranking and relevance scoring; they are better suited for pattern matching, not linguistic search.

Why this answer

How to eliminate wrong answers

Option B is wrong because adjusting staleness to 5 seconds for reads reduces read CPU by allowing stale reads, but the problem states high CPU utilization on multiple nodes with read-write transactions; stale reads do not reduce the CPU cost of write operations or index maintenance, which are the primary drivers. Option C is wrong because increasing nodes spreads the CPU load but does not address the root cause; it may mask the issue and increase costs without resolving the underlying index overhead. Option D is wrong because creating a separate read-only replica pool offloads read traffic, but the high CPU is from read-write transactions, and read-only replicas cannot handle writes; they do not reduce CPU for write-heavy workloads.

Why this answer

How to eliminate wrong answers

Option B is wrong because disabling automatic refresh and scheduling a manual refresh every minute would significantly increase costs due to repeated full recomputation of the materialized view, and it also introduces operational complexity without leveraging BigQuery's built-in incremental refresh mechanism. Option C is wrong because using a streaming buffer with the base table reduces latency for new data ingestion but does not affect the refresh interval of the materialized view itself; the view still relies on its own staleness setting. Option D is wrong because creating additional materialized views with overlapping time windows does not reduce the refresh interval for any single view; it increases storage and processing costs without improving freshness, as each view would still have its own staleness constraint.

Why this answer

How to eliminate wrong answers

Option A is wrong because storage cost is reduced by the amount of data in expired partitions, not by a fixed percentage like 365%; the percentage depends on the table's total size. Option B is wrong because queries referencing data older than 365 days will simply return no rows from those expired partitions, but the query itself will not fail—it will succeed with an empty result for the expired range. Option D is wrong because the option does not control the number of partitions; it controls the expiration age of partitions, while the number of partitions is determined by the partitioning column's granularity and the data's date range.

Why this answer

How to eliminate wrong answers

Option B is wrong because BigQuery's automatic schema detection only works during table creation from external data sources (e.g., Cloud Storage) and cannot retroactively infer or restructure an existing table with a single JSON column. Option C is wrong because a view using JSON_QUERY and JSON_VALUE still requires runtime parsing of the JSON string for every query, which degrades performance and prevents BI tools from leveraging column-level optimizations like partitioning or clustering. Option D is wrong because the EXTRACT function in BigQuery is designed for extracting date/time parts, not for parsing JSON fields; using it would be syntactically incorrect and non-functional.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing max_connections can actually worsen CPU usage by allowing more concurrent queries to compete for CPU resources, potentially increasing contention. Option B is wrong because read pool offloading is a feature for Cloud SQL for PostgreSQL, not MySQL, and MySQL instances use read replicas instead. Option C is wrong because scaling up to 8 vCPUs is a reactive measure that does not address the underlying query inefficiency; it increases cost without guaranteeing performance improvement if the queries are poorly optimized.

Why this answer

How to eliminate wrong answers

Option A is wrong because 90% is too high; at this level, Cloud Spanner nodes experience significant queuing delays and potential throttling, making it a reactive rather than proactive threshold. Option B is wrong because 40% is too conservative; it would trigger false alarms unnecessarily, as Cloud Spanner is designed to handle moderate CPU loads without performance issues. Option C is wrong because 80% is above the recommended threshold; while it may indicate high utilization, it risks performance degradation before the alert fires, as queuing effects become noticeable above 65%.

Why this answer

How to eliminate wrong answers

Option B is wrong because partitioning does not automatically create indexes; BigQuery uses a columnar storage format and does not rely on traditional indexes. Option C is wrong because clustering is a separate feature that co-locates data within partitions based on sort order, but the primary benefit described here is partition pruning, not clustering. Option D is wrong because streaming inserts are a method for ingesting real-time data and are unrelated to the query performance benefit of partition pruning.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because integer range partitioning on an ID column does not align with date-based queries; it would require scanning all partitions or using inefficient filters, and it does not leverage BigQuery's optimized date pruning. Option C is wrong because a regular table with no partitioning forces a full table scan on every query, even when filtering by date, leading to maximum cost and slower performance. Option D is wrong because clustering on timestamp alone, without partitioning, still requires scanning the entire table; clustering only sorts data within a table, but BigQuery cannot skip entire storage blocks without partitioning, so cost savings are minimal compared to partitioning.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because multi-region configurations introduce cross-region replication and quorum overhead, which increases read latency compared to a regional setup, even with leader preference set to the application's region. Option C is wrong because a single-region instance with leader preference set to a zone does not add read replicas; leader preference only affects write latency and transaction commit, not read latency. Option D is wrong because read-only replicas in multiple zones within a single-region instance do not reduce read latency for the application; they are used for failover and disaster recovery, not for serving reads with lower latency.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud SQL Proxy is a secure tunnel for connecting to a live Cloud SQL instance over the internet; it does not create a local copy of the database, so the developer remains dependent on network connectivity and the production instance, which defeats the purpose of a local development mirror. Option B is wrong because Cloud Functions are serverless compute units for event-driven code, not a database service or tool for replicating or mirroring database state; they cannot store or serve a local copy of a Cloud SQL database. Option D is wrong because using Cloud SQL's public IP exposes the instance directly to the internet, which is a security risk and still requires a live connection to the remote database, not a local development mirror.

Why this answer

How to eliminate wrong answers

Option A is wrong because scheduling reports during off-peak hours only reduces contention but does not eliminate the CPU load from the primary instance, which can still impact OLTP performance if reports run concurrently with any other workload. Option C is wrong because Cloud SQL's high availability configuration uses a standby instance in a different zone for failover, not for read scaling; it does not offload query processing and the standby cannot serve read traffic. Option D is wrong because upgrading the primary instance to a higher machine type increases capacity but does not isolate the BI workload, so CPU-intensive queries will still compete with OLTP transactions for resources on the same instance.

Why this answer

How to eliminate wrong answers

Option B is wrong because monitoring without action does not resolve the high eviction rate, which can degrade session store performance and cause data loss. Option C is wrong because adding a read replica does not increase the primary instance's memory capacity or reduce evictions; replicas are for read scaling and high availability, not for alleviating memory pressure on the primary. Option D is wrong because enabling persistence (AOF or RDB) does not reduce memory usage; it writes data to disk but the dataset still resides in memory, so evictions will continue at the same rate.

Why this answer

How to eliminate wrong answers

Option B is wrong because creating a separate table duplicates data, leading to storage overhead, synchronization issues, and potential data staleness; it violates normalization principles and increases maintenance complexity. Option C is wrong because a subquery in the FROM clause does not inherently reduce column reads; the outer query still processes all columns from the subquery unless explicitly pruned, and it may not optimize execution plans as effectively as a view. Option D is wrong because a LIMIT clause restricts rows, not columns; it does not reduce the amount of data read per row, so the query still scans all columns from the large table, failing to address the root cause of slow performance.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because read replica lag affects replicas, not the primary instance after a failover; the failover promotes a replica to primary, and lag would have been caught before promotion. Option C is wrong because binary log is used for replication and point-in-time recovery, not for query performance; disabling it would not cause post-failover slowdown. Option D is wrong because data corruption would cause errors or crashes, not a gradual performance degradation; Cloud SQL automatically checks for corruption during failover.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because WHERE events IS NOT NULL does not flatten nested repeated fields; it only filters rows where the entire 'events' array is non-null, leaving the nested structure intact and unusable for per-event analysis. Option C is wrong because ARRAY_AGG(events) does the opposite of flattening—it aggregates rows into an array, which would further nest the data and break the per-event requirement. Option D is wrong because SELECT events.* from table attempts to select all fields from the 'events' record, but without UNNEST, BigQuery treats 'events' as a single array column, causing a syntax error or returning the array as a whole, not individual event rows.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing instance memory to 30 GB does not address the lack of an appropriate index; it may reduce buffer pool misses but cannot eliminate the need for a full table scan on a large table. Option C is wrong because removing the WHERE clause and fetching all rows in the application would transfer massive amounts of data over the network and force client-side filtering, which is far less efficient than letting the database engine use an index. Option D is wrong because partitioning the table by month does not automatically create an index on `status` and `order_date`; while partition pruning might help, without a proper index the query would still scan all rows in the relevant partitions.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because Query 2 does not scan less data than Query 1; it scans more data within the same partition because it lacks a clustering filter. Option C is wrong because the two queries do not scan the same amount of data; Query 1 benefits from both partition pruning and clustering, reducing the scan further. Option D is wrong because both queries use partition pruning on `event_date`, so they do not perform a full table scan; they only scan the relevant partition(s).

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing the buffer pool size (InnoDB buffer pool) does not address the root cause of filesort and temporary table usage; it only caches more data in memory, which may reduce disk I/O but does not eliminate the sorting overhead. Option B is wrong because disabling the query cache (which is deprecated in MySQL 8.0 and removed in 8.0+) does not affect queries that perform sorting; the query cache is only useful for identical SELECT statements and does not help with ORDER BY performance. Option C is wrong because removing the ORDER BY clause and sorting in application code shifts the sorting burden to the application server, which may still be inefficient and does not reduce the number of rows examined by the database; it also breaks the semantics of the query if the application relies on database-side ordering for pagination or consistency.

Why this answer

Why this order

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud Bigtable does not enforce a hard IOPS limit; it scales with the number of nodes, and throttling would typically manifest as increased error rates or retries, not simply increased latency with low CPU. Option C is wrong because a hot node would cause high CPU utilization on that node, not low overall CPU, and throttling would be localized to that node's requests. Option D is wrong because underprovisioning would lead to high CPU utilization and resource contention across the cluster, not low CPU with a high tablet count.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because using a larger machine type increases CPU and memory but does not reduce the time spent downloading dependencies; the bottleneck is network I/O, not compute resources. Option B is wrong because increasing the build timeout only extends the maximum allowed duration for the build to complete, it does not optimize the build process or reduce download time. Option D is wrong because Cloud Build's local SSD provides ephemeral storage for build artifacts but does not cache Docker layers across builds; dependencies would still be downloaded fresh each time.

Why this answer

Why this answer

Why this answer

Why this answer