Google Professional Cloud Database Engineer (PCDE) — Questions 1426–1446

Creating read replicas in Cloud SQL offloads read traffic from the primary instance, allowing complex aggregate queries to run on replicas without impacting write performance. Since the reports can tolerate up to 5 minutes of staleness, the asynchronous replication lag inherent in Cloud SQL read replicas is acceptable, making this a cost-effective and scalable solution.

A blameless postmortem focuses on identifying contributing factors and systemic issues rather than assigning blame to individuals. The 5 Whys technique helps drill down to root causes.

The team already has existing Terraform modules, and using Terraform with a GCS backend allows them to continue leveraging their investment in Terraform without rewriting or bridging tools. Cloud Deployment Manager is a native Google Cloud IaC tool, but it does not natively import or execute Terraform configurations; forcing a migration or hybrid approach introduces unnecessary complexity and risk. The best practice is to standardize on one IaC tool, and since Terraform is already in use, using it with a GCS backend for state management is the most efficient and reliable path.

Cloud SQL for MySQL supports online storage increases. When you edit the instance and increase the storage allocation, the operation is applied without requiring a restart or causing downtime. The underlying infrastructure uses live migration and dynamic volume expansion to make the additional capacity available immediately while the instance continues serving traffic.

BigQuery clustered tables use block pruning to skip reading blocks that don't match the query's filter. Pruning is most effective when the filter includes the first clustering column (country). Without it, BigQuery must scan more blocks, leading to higher query costs and slower performance.

Cloud Functions is a serverless execution environment that can be triggered by events or schedules to automate tasks. It is well-suited for automating simple operational tasks.

Materialized views store the result set of a query physically, like a table. If the query includes non-deterministic functions (e.g., NOW(), RAND(), CURRENT_TIMESTAMP), the stored result would become stale immediately because the function's output changes each time it is evaluated. Most SQL databases (e.g., PostgreSQL, Oracle, Snowflake) explicitly forbid non-deterministic functions in materialized view definitions to prevent this logical inconsistency.

COUNT(column) ignores NULL values in the specified column, while COUNT(*) counts every row in the result set regardless of NULLs. If the column contains any NULLs, COUNT(column) will return a lower number. This is a fundamental SQL behavior defined in the ANSI SQL standard and is consistent across all major BI platforms (e.g., Tableau, Power BI, Looker) that generate SQL queries.

Canary deployment is the correct choice because it gradually shifts traffic from the current version to the new version while allowing monitoring of error rates. In Cloud Deploy, a canary strategy uses a sequence of phases (e.g., 10%, 50%, 100%) to incrementally route traffic, enabling real-time observability and automated rollback if error thresholds are breached.

A single cluster with SSD storage provides low-latency access for frequently queried recent data, which is critical for streaming workloads. SSDs offer consistent single-digit millisecond latency, while HDDs would introduce higher latency unsuitable for real-time queries. This balances performance and cost for the described use case.

Trunk-based development with short-lived feature branches and direct commits to main (or a single release branch) is the simplest and most aligned with GitOps principles where main branch represents the desired state.

Cloud Spanner secondary indexes are fully synchronous with the base table, meaning they are updated atomically in the same transaction as the table write. This ensures that reads using the secondary index always return strongly consistent data, without any eventual consistency window.

Clustering on frequently filtered columns in BigQuery organizes data into blocks based on the values of those columns, allowing queries with filters on those columns to skip irrelevant blocks entirely. This reduces the amount of data scanned, improving query performance and lowering costs, which is critical for a BI dashboard that needs real-time updates and fast historical analysis.

In Cloud SQL for MySQL, high write workloads cause frequent updates to tables, which invalidates the query cache entries for those tables. This means the cache must be repopulated often, reducing the hit rate and potentially adding overhead from cache maintenance, making it less effective for performance optimization.

These features optimize cost, performance, and management in BigQuery. Partitioning reduces scan costs, Clustering improves query performance, Materialized Views accelerate complex queries, and Slots provide resource control.

Upgrading to a larger instance (e.g., M5 with 60 GB) directly addresses both the high CPU utilization and memory pressure. The M2 instance is CPU-bound with sustained >90% usage, and the 85% cache hit ratio indicates that evictions are already occurring under the allkeys-lru policy, causing CPU spikes from eviction overhead and key lookups. A larger instance provides more CPU cores and memory, reducing eviction frequency and allowing the cache to serve more requests without thrashing.