Google Professional Cloud Database Engineer (PCDE) — Questions 376–450

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

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

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

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

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

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

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

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

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

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

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

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because the question explicitly states that strong reads are used, which means the read timestamp is automatically set to the current timestamp, not a stale one. Option C is wrong because Cloud Spanner does not have read-only replicas; all replicas can serve reads, but strong reads are always served from the leader replica, so reading from a non-leader replica would not occur with strong reads. Option D is wrong because Cloud Spanner provides strong consistency for all reads by default; eventual consistency is not a mode that can be selected, and the issue is specific to secondary index staleness, not a general consistency model.

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing the instance size (vertical scaling) only masks the symptom of high CPU usage without fixing the inefficient query plan; the full table scans will continue to consume resources, and costs increase without performance guarantee. Option B is wrong because creating a read replica for reporting offloads the query to another instance but does not eliminate the full table scan on the replica; the same inefficient query will still cause high CPU on the replica. Option C is wrong because query rewriting with materialized views pre-computes and stores the result set, which can improve performance for repeated complex queries, but it does not address the immediate full table scan caused by missing indexes; materialized views also require maintenance and may become stale.

Why this answer

How to eliminate wrong answers

Option A is wrong because clustering by timestamp within a partition by ingestion_time is redundant—since the partition already organizes data by time, clustering by the same column adds no additional pruning benefit and wastes clustering resources. Option B is wrong because without clustering, queries filtering on device_id must scan all rows in the relevant partitions, leading to full partition scans and higher query costs. Option C is wrong because no partitioning means every query must scan the entire table, even when filtering on the last hour, resulting in maximum data read and cost.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because partitioning by date only limits the scan to the relevant date range, but the query still must aggregate 20 TB of data across all countries and categories, which cannot reduce latency to under 5 seconds. Option B is wrong because adding clustering by category improves the efficiency of the aggregation step by co-locating data, but it does not precompute the aggregation; the query still must scan and aggregate all rows in the filtered partition, which is too slow for a 20 TB table. Option C is wrong because increasing query priority does not change the amount of data scanned or the computational work required; it only affects scheduling and resource allocation, not the fundamental latency of scanning and aggregating 20 TB.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because reaching the maximum number of connections would produce a different error, such as 'Cannot open server connection' or 'Connection limit exceeded', not a login failure for a specific database. Option C is wrong because if the Cloud SQL proxy were not running, the application would not be able to reach the Cloud SQL instance at all, resulting in a network timeout or connection refused error, not a SQL Server login error. Option D is wrong because if the instance were not in the same VPC network, the application would experience a network connectivity failure (e.g., timeout or unreachable host), not a SQL Server authentication error that includes a database name reference.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because a materialized view stores pre-computed results, which can become stale and require manual or automatic refreshes, making it unsuitable for a report that must reflect the most recent purchase history without latency. Option C is wrong because an external table points to raw data files (e.g., in Cloud Storage) and does not support SQL joins or aggregations natively; it is designed for querying external data without loading it into BigQuery, not for defining a structured report. Option D is wrong because creating a new table with INSERT SELECT stores a static snapshot of the data, which would require manual re-execution to update the CLV report, defeating the purpose of a dynamic, recurring report.

Why this answer

How to eliminate wrong answers

Option B is wrong because APPROX_COUNT_DISTINCT reduces the accuracy of distinct counts but does not reduce the memory usage of a join operation; it only optimizes a specific aggregation function. Option C is wrong because increasing the slot reservation increases the available compute resources (slots) but does not reduce the memory usage per query; it may delay the error but does not fix the underlying memory bottleneck. Option D is wrong because using SELECT * in a subquery retrieves all columns, which increases the data volume and memory consumption, making the 'Resources exceeded' error worse.

Why this answer

How to eliminate wrong answers

Option B is wrong because CPU utilization above 70% is a symptom, not a root cause, and enabling fine-grained access control does not reduce write latency. Option C is wrong because interleaved indexes optimize read performance by colocating parent and child rows, but they do not speed up writes; in fact, they can add overhead to write operations. Option D is wrong because the instance has 5 nodes and no application changes were made, so under-provisioning is unlikely; the issue is a hot spot from key design, not insufficient capacity.

Why this order

Why this answer

How to eliminate wrong answers

Option A is wrong because column-level security hides entire columns (e.g., salary), not rows, so it cannot restrict which rows a salesperson sees based on region. Option B is wrong because BigQuery does not have a native 'row-level access policies' feature; the correct term is row-level security implemented via authorized views or row-level access policies (which are not a distinct BigQuery feature). Option D is wrong because IAM roles control access at the dataset or table level, not at the row level, and creating separate roles per region would require duplicating tables or complex, unscalable management.

Why this answer

How to eliminate wrong answers

Option B is wrong because 'Active connections' shows the total number of open connections to the database, not queries waiting for locks; a high active connection count does not necessarily indicate lock contention. Option C is wrong because 'CPU utilization' measures processor usage, which may be high due to many reasons (e.g., heavy queries, indexing issues) but does not specifically indicate queries waiting for locks. Option D is wrong because 'Queries' typically refers to the total number of queries executed per second, not the subset of queries that are blocked waiting for locks.

Why this answer

How to eliminate wrong answers

Option A is wrong because `instanceType` in Cloud Bigtable is either `PRODUCTION` or `DEVELOPMENT`, not `MULTI_CLUSTER`; the term 'MULTI_CLUSTER' is used for routing options, not instance type. Option B is wrong because there is no top-level `replication` field in the JSON output of `gcloud bigtable instances describe`; replication status is derived from the number of clusters in the `clusters` list. Option D is wrong because the `state` field in the instance description indicates the lifecycle state (e.g., `READY`, `CREATING`), not replication status; there is no `REPLICATED` state value.

Why this answer

How to eliminate wrong answers

Option A is wrong because Cloud SQL proxy is a client-side tool for encrypting connections and simplifying authentication, but it does not restrict access to authorized VMs only; it still requires a public IP or a private IP with appropriate network configuration, and it does not enforce VM-level authorization. Option B is wrong because while SSL/TLS encrypts connections, using an authorized network with a public IP still exposes the database to the internet, violating the requirement to restrict access to authorized VMs only; authorized networks are IP-based and do not enforce VM-level identity. Option D is wrong because Cloud Armor is a web application firewall for HTTP(S) traffic, not for database connections; it cannot restrict access to Cloud SQL PostgreSQL instances, and using a public IP with SSL/TLS still leaves the database internet-facing.

Why this answer

Why this answer

Why this order

Why this answer

How to eliminate wrong answers

Option A is wrong because cached results only affect query performance, not the freshness of streaming data; cached results are served from a temporary cache and do not delay the visibility of newly streamed data. Option B is wrong because partitioning by hour does not inherently introduce a delay; it organizes data into partitions but does not control when streaming data becomes available for queries. Option D is wrong because a clustering key improves query performance by sorting data within partitions, but it has no impact on the latency of streaming data appearing in query results.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because materialized views improve query performance for common aggregations but do not reduce storage costs for historical data; they actually incur additional storage costs for the precomputed results. Option B is wrong because clustered tables optimize query performance by sorting data within partitions but do not reduce storage costs or automatically expire old data. Option D is wrong because a view with a WHERE clause filtering recent data only limits the data scanned at query time, but the underlying historical data remains in storage and continues to incur costs.

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 selecting the same region as the instance does not provide disaster recovery isolation; a regional failure would affect both the instance and its backups. Option C is wrong because on-demand exports to Cloud Storage in the same region also lack cross-region redundancy; the backups remain vulnerable to the same regional outage. Option D is wrong because Cloud SQL automated backups cannot be pointed to a multi-region Cloud Storage bucket; automated backups are stored in Cloud SQL's internal backup storage, not in a user-managed bucket, and the backup location must be a single region.

Why this answer