Google Professional Cloud Database Engineer

Nightly automated backups are a disaster recovery mechanism, not high availability. Restoring from backup during an outage typically takes far longer than 3 minutes (instance provisioning, restore time, DNS/app reconfiguration), so it fails the RTO requirement. It also cannot guarantee RPO = 0 because transactions after the last backup would be lost. Backups remain important, but they do not meet the stated objectives.

A CDC/logical replication pipeline to a secondary instance is complex to operate and is not the Google-recommended HA approach for Cloud SQL. Logical replication is typically asynchronous, so it generally cannot guarantee RPO = 0. Manual failover also risks exceeding the 3-minute RTO due to detection, decision, and cutover steps. This option is more error-prone than managed HA.

A cross-region read replica is primarily a disaster recovery strategy for regional outages, not a zonal outage within the same region. Read replicas for Cloud SQL are asynchronous, so they cannot guarantee RPO = 0. Promotion is also a manual/operational action and may not meet the 3-minute RTO consistently. It also adds cross-region cost and complexity beyond the requirement.

Enabling serializable isolation (or stronger consistency semantics) targets correctness under concurrency, not cost or latency. Firestore already provides strong consistency for document reads and queries in native mode. Adding stricter transactional patterns would not reduce the number of reads; it may increase latency and contention. This option misunderstands the problem: the bottleneck is repeated global reads of identical content, not inconsistent results.

Moving to a US multi-region can improve availability and latency for North America, but it can worsen or not materially improve p95 latency for Europe/APAC compared to serving from CDN edges. More importantly, it does not reduce Firestore read costs: each user still triggers reads for 20 documents daily. Multi-region placement is not a substitute for caching when the same payload is requested millions of times.

A composite index on publish_date can improve query execution efficiency and avoid query failures for certain filter/order combinations, but it does not change Firestore billing for document reads returned, and it does not address global latency for 5 million users. The query returns the same 20 documents regardless; indexing won’t eliminate the repeated reads or provide edge caching benefits.

Incorrect. Auto-incrementing (monotonically increasing) primary keys are a known Cloud Spanner anti-pattern for high write rates because new rows land in the same “end” of the keyspace. This concentrates writes on a small number of ranges and their leaders, producing CPU hotspots and latency spikes. Spanner can split ranges, but the newest keys still target the hottest range, so the bottleneck persists.

Incorrect. Normalization changes how data is modeled across tables, which can help with data integrity and reduce duplication, but it does not directly address hot ranges caused by poor primary key distribution. If the primary keys remain sequential or low-cardinality-leading, the same leaders and ranges will still receive the majority of writes. Hotspot mitigation in Spanner is primarily about key design and write distribution.

Incorrect. Promoting low-cardinality attributes (like class with only 3 values) earlier in a composite primary key tends to cluster rows into a few large contiguous key ranges (e.g., all “economy” together). Under heavy writes, this increases the likelihood that a small number of ranges/leaders become hot. In Spanner, the leading key columns determine locality and are critical for distributing write load.

Incorrect. Using an external (public) IP violates the requirement of “no public internet exposure,” even if firewall rules restrict source ranges. Also, Cloud Functions does not have fixed egress IPs by default, making firewall allowlisting brittle. The Cloud SQL Auth Proxy helps with IAM-based auth and TLS, but it does not inherently provide stable connection management for 200 concurrent requests.

Incorrect. A public IP still exposes the database to the internet, conflicting with the security policy. Additionally, relying on firewall rules to allow only Cloud Functions traffic is problematic because serverless egress IPs are not stable unless you add additional NAT/egress controls. Connection pooling is good for performance, but the networking model fails the “inside prod-vpc only” requirement.

Partially correct on networking: private IP + private services access + Serverless VPC Access is the right private connectivity pattern. However, choosing Cloud SQL Auth Proxy as the primary answer does not address the stated need for “stable connection management” under 200 concurrent requests. The proxy is not a substitute for pooling; without pooling you can still exhaust PostgreSQL connections and degrade performance.

Cloud Spanner is a cloud-native, horizontally scalable relational database with strong consistency and multi-region HA. However, migrating from Oracle RAC OLTP to Spanner requires significant schema and application changes (SQL dialect differences, data model changes, and transaction semantics considerations). It also does not preserve Oracle RAC services, ASM, SCAN, or FAN/TAF behavior. This violates the minimal/no code change requirement and is not a “preserve RAC architecture” landing zone.

Compute Engine VMs with persistent disks and instance groups are not the right fit for a supported Oracle RAC deployment. RAC depends on tightly controlled cluster networking and shared storage behavior; building RAC on generic VMs can run into supportability constraints and may not meet the required low storage latency and high TPS consistently. Instance groups address VM availability, not Oracle RAC’s cluster semantics (SCAN, ASM, FAN/TAF) and shared-disk requirements.

Cloud SQL for PostgreSQL (even with Database Migration Service) implies a cross-engine migration from Oracle to PostgreSQL. That requires application and SQL changes, revalidation of stored procedures, data types, and performance tuning, and it will not preserve RAC-specific client failover mechanisms (FAN/TAF) or Oracle features. It also conflicts with the requirement to keep existing Oracle licensing and to preserve the RAC architecture with minimal rework.

A custom GKE microservice that polls AlloyDB is not CDC and will struggle to meet <10s latency and 99.9% delivery reliability at 2,500 changes/sec without significant custom engineering (deduplication, ordering, retries, backpressure, schema drift handling). Polling also increases load on the source and is operationally complex. It violates the requirement to use Google-managed services that automatically scale and handle schema changes without downtime.

BigQuery federated queries (via external connections) are for querying data in place, not continuously replicating it into BigQuery. They typically won’t meet the requirement to deliver changes with under-10-second end-to-end latency into BigQuery tables for downstream analytics/ML, and performance/cost can be unpredictable at high change rates. It also doesn’t provide a durable delivery pipeline with 99.9% delivery reliability guarantees for replicated data.

Database Migration Service is designed for migrations and continuous replication between databases (e.g., on-prem/MySQL/PostgreSQL to Cloud SQL/AlloyDB). It is not the standard managed service for streaming CDC directly into BigQuery as a target. Even if you could stage data elsewhere, it would add components and latency and would not match the requirement for automatic schema change handling and a direct, scalable, managed path into BigQuery.

Cloud SQL (MySQL/PostgreSQL/SQL Server) provides strong consistency and ACID transactions, but it primarily scales vertically and has practical limits for sustained 120,000 writes/sec with p95 <15 ms without significant sharding and operational complexity. High availability is available, but meeting 99.99% with elastic spike handling at this write rate is not its typical sweet spot. It’s better for regional OLTP workloads with moderate scaling needs.

Memorystore (Redis/Memcached) is an in-memory cache/data store optimized for low latency, not a primary durable database of record. While Redis can support atomic operations, it does not provide the same durability, multi-region relational transactional guarantees, or 99.99% SLA characteristics expected for a mission-critical reservation ledger. It is commonly used to cache seat maps, sessions, or rate limits, but not as the authoritative booking store.

Cloud Bigtable delivers very high throughput and low latency for wide-column/key-value workloads and can scale massively. However, it is not a relational database and does not provide full ACID transactions across rows in the way needed for strict seat reservation semantics (preventing double-booking with multi-entity constraints). Bigtable is excellent for event streams, time-series, and large-scale analytics serving, but not ideal as the primary transactional reservation database.

Incorrect. Migrating to Cloud Spanner is a platform change and a significant migration effort (schema changes, application changes, data migration, cost model differences). The question explicitly forbids migrating platforms or adding new components. Also, even fully managed services still have operational events; the key is meeting the stated requirement with the simplest allowed change.

Incorrect. Opening a Support case may help explain why downtime exceeded expectations, but it does not implement a preventative control to ensure maintenance occurs outside 09:00–22:00 JST. The requirement is to make an immediate configuration change to prevent future peak-hour maintenance. Support is reactive and not a configuration mechanism for scheduling maintenance windows.

Incorrect. Cloud Scheduler cannot “enforce” Cloud SQL maintenance windows or limit Google-managed maintenance to 5 minutes. Maintenance duration is not something you can cap with an external scheduler, and Cloud SQL maintenance is controlled through Cloud SQL settings (maintenance window/track), not by triggering jobs. This option misunderstands the managed nature of Cloud SQL maintenance.

Bigtable is a fully managed wide-column NoSQL database optimized for very high throughput and low-latency key/value access (time-series, IoT, analytics serving). It is not ANSI SQL relational and does not provide globally externally consistent, multi-region transactional semantics like Spanner. While it can replicate for availability, it is not the right fit for cross-region ACID transactions with strict consistency and online relational schema evolution requirements.

Firestore is a fully managed document database with strong consistency and multi-region replication options, and it can support global applications. However, it is not a relational ANSI SQL database and does not provide the same relational schema, joins, and SQL transaction model expected for an order reconciliation system described as relational. It also does not match the classic “external consistency + SQL + global transactions” requirement that points to Spanner.

Cloud SQL for MySQL is a managed relational database, but it is primarily regional. Cross-region designs typically rely on read replicas and asynchronous replication, which cannot guarantee RPO=0. Achieving concurrent multi-region writes with global external consistency is not a Cloud SQL capability; failover changes the primary and introduces operational complexity and potential data loss depending on replication mode. Meeting 99.99% globally with active-active writes is not its target use case.

Cross-region read replicas use asynchronous replication, which cannot guarantee zero data loss during a primary failure (RPO could be > 0 depending on lag). Also, having only cross-region replicas does not address the requirement to tolerate a single-zone outage with zero data loss and fast automatic failover; you still need HA within the primary region (multi-zone synchronous standby). This option focuses on DR but misses HA and the zero-data-loss zonal requirement.

A synchronous failover replica (HA) must be in a different zone, but keeping everything only within one region does not satisfy the requirement to recover from a regional outage within minutes. Same-region HA protects against zonal failures, not regional disasters. Even if you add multiple replicas, a region-wide outage would take out the primary and all same-region replicas, violating the DR requirement.

Cloud SQL does not provide synchronous replication for cross-region replicas; cross-region replication is asynchronous. Additionally, using asynchronous replicas across zones in the same region would not guarantee zero data loss for a zonal outage, violating the requirement. This option reverses the correct pattern: you want synchronous within a region (multi-zone HA) and asynchronous across regions (DR).

Changing automated backups to every 48 hours would reduce the number of backups created, but it conflicts with the requirement of a 24-hour RPO. If the last backup is up to 48 hours old, you could lose up to 2 days of data, exceeding the acceptable data loss window. Also, Cloud SQL automated backups are typically configured on a daily schedule window rather than an every-N-hours cadence.

Cloud SQL does not provide a supported option to choose a different storage tier (SSD vs HDD) specifically for automated backup storage. The instance’s disk type affects primary storage performance/cost, but backup artifacts are managed by the service and billed as backup storage without a user-selectable HDD/SSD tier. Therefore, this is not a valid or applicable cost-reduction lever for automated backups.

Cloud SQL automated backups are managed by the service and are not configurable to be stored in an arbitrary “lower-cost region within the same continent.” While some services support multi-region or cross-region storage classes, Cloud SQL automated backups don’t offer a setting to relocate them for cost optimization. Additionally, moving backups across regions can introduce compliance and restore-time considerations.

Oracle RAC on Bare Metal Solution can deliver high performance and compatibility for Oracle workloads, but it is typically high cost and operationally heavier than cloud-native managed databases. It also doesn’t naturally meet the requirement to scale capacity up/down frequently to control spend; RAC capacity is largely fixed to provisioned hardware. This option is more appropriate for lift-and-shift of existing Oracle RAC with strict compatibility needs.

Sharded Cloud SQL instances can reduce per-instance size and isolate depots, but it introduces significant operational complexity (many instances, backups, patching, connection routing, shard rebalancing). Cross-depot analytics and centralized scheduling queries become harder. Cloud SQL scaling is primarily vertical and can be disruptive; scaling down is limited and not designed for frequent weekday/holiday elasticity. This conflicts with minimizing disruption and enabling elastic cost control.

Cloud Bigtable supports autoscaling and massive throughput, but it is a wide-column NoSQL database, not an SQL relational database. The requirement explicitly states the solution must use an SQL database, which rules Bigtable out. Bigtable also requires different data modeling (row keys, column families) and does not provide relational joins/constraints expected in a scheduling system without additional layers.

Incorrect. Cloud Data Fusion is primarily an ETL and data integration platform, not a purpose-built transactional database migration service for low-downtime cutovers. A table-by-table pipeline approach does not naturally preserve commit ordering, transactional consistency, or continuous synchronization for a high-traffic payment ledger. It would also require substantial downtime or custom reconciliation logic to handle changes occurring during the migration. That makes it a poor fit for a 1.6 TB OLTP database with a strict sub-3-minute cutover requirement.

Incorrect. mysqldump is a logical export tool and is generally too slow for a 1.6 TB production database when downtime must remain under 3 minutes. Even if the export were compressed, the combined time for dumping, transferring, importing, and validating the data would be far longer than the allowed outage window. This method also places heavy load on the source and target during export and import, which increases operational risk. It is acceptable for smaller databases or maintenance windows with long downtime, but not for this scenario.

Incorrect. Exporting tables to CSV and importing them into Cloud SQL is a manual bulk-load pattern that is unsuitable for a transactional MySQL migration of this size and criticality. CSV workflows do not preserve full database semantics such as triggers, routines, foreign keys, and other schema-level objects without significant extra work. Rebuilding indexes and constraints after import would add even more time and complexity, making the outage far exceed 3 minutes. This approach is better suited to analytical data movement than to a payment ledger requiring low-downtime migration.

Creating a new HA instance and using export/import is a classic migration pattern, but it is high risk here. Export/import for 800 GB can take hours and requires a cutover window; it also risks data loss unless you freeze writes or implement additional replication. It violates the constraint of not accepting multi-hour downtime and is not the least operational risk compared to in-place HA enablement.

Cloud Data Fusion is an ETL/integration tool, not the standard or lowest-risk method to migrate a production MySQL database for HA. Using it for full database migration adds complexity, operational overhead, and potential data consistency issues. It also does not inherently solve the cutover/RPO problem without additional replication and careful orchestration, making it unsuitable under tight downtime constraints.

Manually shutting down the instance and “toggling HA” is not the recommended or necessary procedure. HA enablement is done through an instance update (console/API/gcloud patch), and introducing manual stop/start steps increases the chance of errors and extended downtime. It also doesn’t add value beyond what option C already provides in a controlled, supported way.

Setting a maintenance window helps control when maintenance occurs, but it does not prevent downtime on a single Cloud SQL instance because patching often requires a restart. Additionally, Cloud SQL maintenance windows are configured in UTC (not a local time zone), so “02:00–03:00 America/Chicago” is not how the setting is applied. Sequencing non-prod first is good practice, but it doesn’t solve production availability.

A read replica can offload read traffic, but it does not provide automatic, seamless continuity for a transactional (write-heavy) system during primary maintenance. Cloud SQL replicas are asynchronous; promoting a replica to primary is a disruptive operational event and typically requires connection endpoint changes and careful failover planning. This does not meet the requirement of avoiding downtime for users during operating hours.

Maintenance notifications and rescheduling are operationally helpful, but they don’t eliminate downtime. The requirement is to apply patches within 7 days and avoid downtime during 06:00–22:00 local time. Notifying users implies accepting downtime, which conflicts with the stated goal. Also, maintenance timing can be constrained by Google’s maintenance policies and the patch compliance window.

Incorrect. JDBC authentication to PostgreSQL uses database users (created in PostgreSQL/Cloud SQL), not Google Workspace usernames/passwords. Even if the public IP is reachable, Workspace credentials are not valid database credentials. Additionally, connecting directly to the public IP without the Cloud SQL Auth Proxy typically requires configuring authorized networks or other instance-side controls, which the question forbids changing.

Incorrect. The private IP (10.20.30.40) is only reachable from within a connected VPC network or from on-prem via Cloud VPN/Interconnect. The scenario explicitly states there is no VPN or Interconnect, so there is no route from the on-prem VM to the private IP. A direct JDBC connection to the private address will fail regardless of correct database credentials.

Incorrect. While using the Cloud SQL Auth Proxy with a service account is a best practice, configuring it to connect to the instance’s private IP still requires network connectivity to that private IP. Without VPN/Interconnect (or being in the same VPC), the on-prem VM cannot reach 10.20.30.40. The proxy does not magically provide private IP routing; it provides authenticated tunneling to Cloud SQL endpoints.

Disabling all backups and turning off transaction log retention is the absolute lowest cost, but it eliminates recovery capability. Even for non-critical systems, the question explicitly requires “still retaining basic recovery capability.” Without backups or PITR logs, you cannot restore after accidental deletion, corruption, or instance failure beyond what high availability might cover. This fails the stated business requirement.

One manual backup per day with transaction log retention off can meet a 72-hour RPO (daily backups are more frequent than required). However, it increases operational overhead and risk: someone must ensure backups are created, retained, and not accidentally deleted. Manual backups also don’t inherently optimize retention unless you manage lifecycle yourself. For “minimize ongoing operational cost,” automated backups are preferred.

Automated backups with transaction log retention on enables PITR, allowing restores to a specific timestamp between backups. This is valuable for tighter RPO/RTO targets and protection from logical errors discovered quickly. But it increases ongoing storage consumption (transaction logs) and can raise costs without providing meaningful benefit when the business accepts up to 72 hours of data loss. It is over-engineered for staging QA.

A clone of the primary is not the same as a read replica. Clones are independent copies and do not continue asynchronous replication from the primary after creation. Using a clone for read traffic would create data divergence over time and require more manual management. Therefore, it does not properly restore a managed read replica topology.

This option is incorrect because it relies on an assumed automatic recreation of the read replica in a healthy zone. Cloud SQL does not provide a documented guarantee that a single read replica will be automatically recreated elsewhere to preserve read availability after a zonal disruption. Verifying status is a reasonable operational check, but it does not itself ensure the read-only workload continues to function. The question asks what you should do to ensure availability, not merely what you should observe.

Restarting the primary is unnecessary and potentially harmful. The problem is with the replica being unreachable due to a zonal network disruption, not with the primary database process. Restarting the primary can interrupt write traffic and does not guarantee restoration of the replica or read service. It adds risk without addressing the actual failure domain.

This option is too small on both compute and storage for the stated workload. With only 8 vCPUs, 32 GB of RAM, and 600 GB of SSD, it is the least capable configuration and is unlikely to sustain the peak read demand during quarterly reporting. The storage allocation is especially problematic because SSD-backed IOPS capacity in Cloud SQL scales with disk size, and 600 GB does not provide enough headroom for a 27,000 IOPS target. Even though it is cheaper, it does not meet the performance requirement.

This option would likely provide sufficient performance, but it is not the most cost-efficient choice. The 1 TB SSD gives more storage performance headroom than option B, yet the question asks you to maximize read performance while keeping licensing costs minimal, which implies avoiding unnecessary overprovisioning. Since both B and C use SQL Server Standard and the same compute shape, the extra storage in C adds cost without being the minimal sizing choice. Therefore, C is not the best answer when cost optimization is part of the requirement.

This option uses SQL Server Enterprise, which significantly increases licensing cost and directly conflicts with the requirement to keep licensing costs minimal. Although it has the same 16 vCPU / 104 GB high-memory shape as B and C, its 500 GB SSD is also smaller than the Standard options that are better suited for high read IOPS. Enterprise edition does not inherently remove Cloud SQL storage performance limits, so paying more for Enterprise does not solve the core sizing issue. It is therefore both more expensive and less appropriate than the Standard alternatives.