Simulate the real exam experience with 50 questions and a 120-minute time limit. Practice with AI-verified answers and detailed explanations.
AI-Powered
Every answer is cross-verified by 3 leading AI models to ensure maximum accuracy. Get detailed per-option explanations and in-depth question analysis.
Your media-streaming platform runs an AlloyDB for PostgreSQL cluster in us-central1 that is accessible only via a private IP; compliance forbids opening a public endpoint or installing agents on database VMs, and you must continuously replicate a subset of 12 tables (about 1.8 TB total, up to 2,500 row changes per second) into BigQuery for analytics and ML with end-to-end latency under 10 seconds and 99.9% delivery reliability using Google-managed services that automatically handle schema changes and scale without downtime; what should you do?
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.
Datastream is the managed CDC service that captures changes from PostgreSQL-compatible sources like AlloyDB using log-based replication without installing VM agents. It supports private connectivity so the source can remain private-only. Pairing Datastream with the Google-provided Dataflow template to stream into BigQuery provides autoscaling, checkpointing, and robust retry semantics to achieve low-latency (<10s) ingestion and high delivery reliability, while also supporting schema evolution handling in a managed way.
Core concept: This question tests near-real-time change data capture (CDC) from a private AlloyDB for PostgreSQL source into BigQuery using fully managed Google services, with low latency, high reliability, automatic scaling, and resilience to schema evolution. Why the answer is correct: Datastream is Google’s managed CDC service for databases (including PostgreSQL-compatible sources such as AlloyDB). It can capture ongoing row-level changes and stream them with low latency. Because the AlloyDB cluster is private-only and you cannot install agents on database VMs, Datastream fits: it uses database-native replication/log-based mechanisms rather than host agents. Datastream supports private connectivity patterns (e.g., VPC connectivity/Private Service Connect depending on setup) so you can keep traffic on private IP paths. To land changes in BigQuery with <10s end-to-end latency and 99.9% delivery reliability, the recommended pattern is Datastream CDC into a streaming pipeline using the Google-provided Dataflow template for Datastream-to-BigQuery. Dataflow provides autoscaling, checkpointing, and exactly-once/at-least-once processing semantics (depending on sink behavior), enabling high delivery reliability and continuous operation without downtime. Key features / configurations / best practices: - Configure Datastream for PostgreSQL CDC with an include list for the 12 required tables (subset replication). - Use private connectivity (no public endpoints) and least-privilege database replication user. - Use the Datastream-to-BigQuery Dataflow template to handle continuous ingestion, scaling, and operational management. - Enable schema evolution handling: Datastream captures DDL changes and the template/pipeline can propagate schema updates to BigQuery (within supported change types), reducing manual intervention. - Design for reliability per the Google Cloud Architecture Framework: use regional resources appropriately, monitor lag/throughput, set alerts, and plan quotas (BigQuery streaming inserts, Dataflow worker limits, Datastream throughput) for 2,500 changes/sec and 1.8 TB initial state. Common misconceptions: - “Federated queries” feels simpler, but it does not replicate data nor meet sub-10-second analytics freshness at scale. - “DMS can replicate to BigQuery” is a common confusion: DMS is for database-to-database migrations/replication (e.g., to Cloud SQL/AlloyDB), not continuous CDC into BigQuery as the primary target. - “Custom polling service” seems flexible but violates managed-service/scaling/reliability requirements and typically cannot meet low-latency CDC without heavy engineering. Exam tips: When you see: private-only source, no agents, continuous CDC, BigQuery target, low latency, and managed schema change handling—think Datastream + Dataflow template to BigQuery. Reserve DMS for migrations between operational databases, not analytics ingestion into BigQuery.
You are designing a global order reconciliation service for a multinational retail platform. Applications in North America, Europe, and Asia must be able to read and write concurrently, with p95 cross-region transaction commit latency under 200 ms. The database must be fully managed, relational (ANSI SQL), provide global external consistency with RPO=0, support online schema changes, and meet a 99.99% availability target. Which Google Cloud service should you choose?
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.
Cloud Spanner is the only Google Cloud database that directly matches all requirements: fully managed, relational with ANSI SQL, global external consistency (true serializability), synchronous replication for RPO=0, and multi-region configurations designed for 99.99% availability. It supports concurrent reads/writes from multiple regions and provides online schema changes. It is purpose-built for global transactional systems like order processing and reconciliation.
Core Concept: This question tests selecting a fully managed, globally distributed relational database that supports concurrent multi-region reads/writes with strong consistency (external consistency), zero data loss (RPO=0), and very high availability. Why the Answer is Correct: Cloud Spanner is Google Cloud’s globally distributed, horizontally scalable, ANSI SQL relational database designed for multi-region active-active workloads. It provides external consistency (true serializability) for transactions across regions, which is the key requirement behind “global external consistency with RPO=0.” With Spanner, commits are replicated synchronously across replicas in a multi-region configuration, so acknowledged commits are durable even if a region fails (RPO=0). Spanner is fully managed and supports online schema changes, aligning with the operational requirements. Key Features / Configurations / Best Practices: - Multi-region instance configurations (e.g., nam3, eur3, asia1) provide high availability and synchronous replication across geographically separated regions. This supports a 99.99% availability target when configured appropriately. - External consistency is enabled by Spanner’s TrueTime-based concurrency control, ensuring globally consistent reads and writes. - ANSI SQL and relational modeling are first-class, including secondary indexes and transactions. - Online schema changes allow many schema updates without downtime (important for global retail platforms). - Latency: Cross-region commit latency depends on replica placement and network distance; Spanner is the intended service when you must do cross-region transactions with strong consistency. In practice, you design the instance configuration and application access patterns (e.g., locality, leader placement) to meet p95 targets. Common Misconceptions: Firestore is multi-region and strongly consistent, but it is a NoSQL document database and not ANSI SQL relational. Cloud SQL is relational but is not designed for global active-active writes with external consistency and RPO=0 across continents; cross-region replication is typically asynchronous and failover changes the primary. Bigtable is wide-column NoSQL and does not provide relational SQL transactions or external consistency across regions. Exam Tips: When you see “global,” “concurrent writes in multiple regions,” “ANSI SQL,” “external consistency,” and “RPO=0,” default to Cloud Spanner. If the question instead emphasizes PostgreSQL/MySQL compatibility with read replicas and regional HA, think Cloud SQL; if it emphasizes document data and offline sync, think Firestore; if it emphasizes massive throughput key-value/time-series, think Bigtable.
Your company needs to relocate a high-traffic payment ledger database from a co-location data center to Google Cloud. The source is MySQL 8.0.28 using the InnoDB engine with binary logging enabled in ROW format; the dataset is 1.6 TB, averaging 900 TPS with bursts up to 1,500 TPS. A dedicated Cloud VPN tunnel provides 1 Gbps bandwidth between on-premises and Google Cloud. You must preserve ACID transactions and keep the production cutover under 3 minutes at 02:00 UTC. Cloud SQL for MySQL supports the source version. What should you do?
Correct. Database Migration Service is the Google-recommended service for migrating supported MySQL databases into Cloud SQL with minimal downtime. It performs an initial full load of the 1.6 TB dataset and then uses MySQL binlog-based replication to continuously apply ongoing changes from the source, which is exactly what is needed for a database sustaining 900 TPS with bursts to 1,500 TPS. Because the source already has binary logging enabled in ROW format, the prerequisites align well with DMS continuous migration. This allows the team to keep production online during the bulk transfer and use the 3-minute cutover window only to stop writes, let replication catch up, finalize the migration, and redirect clients.
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.
Core concept: This question tests low-downtime migration of MySQL to Cloud SQL using Database Migration Service (DMS) with continuous replication (binlog-based CDC). It emphasizes meeting strict cutover RTO (under 3 minutes) while preserving ACID behavior at the database layer. Why the answer is correct: With a 1.6 TB dataset and sustained 900 TPS (bursts to 1,500), any “offline” export/import approach (mysqldump/CSV) will take far longer than 3 minutes and introduces extended downtime. DMS supports MySQL 8.0 to Cloud SQL for MySQL and can perform an initial load followed by continuous replication from MySQL binary logs in ROW format. By letting the initial load run ahead of time and keeping replication nearly caught up, the final cutover becomes a short, controlled operation: stop writes on the source, allow replication to drain to near-zero lag, promote the destination, and repoint applications. Key features / configurations / best practices: - Use DMS continuous migration with binlog-based replication (GTID preferred when available; otherwise file/position). Ensure source has ROW binlog format (given) and appropriate retention so logs aren’t purged during initial load. - Validate network throughput: 1 Gbps VPN (~125 MB/s theoretical, lower effective) is adequate for continuous replication and initial load over time; the key is that cutover only depends on draining remaining changes, not transferring 1.6 TB during the window. - Preserve transactional integrity: InnoDB + row-based binlogs allow deterministic replication of committed changes. Application cutover should include connection string updates and possibly DNS/connection pool refresh. - Follow Google Cloud Architecture Framework reliability guidance: perform rehearsals, monitor replication lag, and plan rollback (keep source read-only but available briefly). Common misconceptions: - “mysqldump is simplest”: true for small databases, but at 1.6 TB it creates long downtime and risks missing the 3-minute requirement. - “ETL tools can migrate data”: Data Fusion pipelines are for transformation/analytics-style movement, not for maintaining transactional consistency with near-zero downtime. - “CSV export is faster”: it loses schema fidelity (constraints, triggers, routines), requires index rebuilds, and still implies long downtime. Exam tips: When you see large datasets + strict cutover window + MySQL with binlogs enabled, default to DMS continuous migration (CDC). Reserve dump/CSV imports for small datasets or when downtime is acceptable. Also remember Cloud SQL supports read replicas, but cross-environment migration is best handled by DMS for managed, monitored cutovers.
Your team runs a meal-delivery ordering platform in us-central1. The API is served from Cloud Run, and transactional data is stored in a single Cloud SQL for PostgreSQL instance with automatic maintenance updates enabled. 92% of customers are in the America/Chicago time zone and expect the app to be available every day from 6:00 to 22:00 local time. Security policy requires that database maintenance patches be applied within 7 days of release. You need to apply regular Cloud SQL maintenance without creating downtime for users during operating hours. What should you do?
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.
Cloud SQL High Availability (regional) is designed to minimize downtime during both planned maintenance and unplanned failures. With a synchronous standby in another zone, Cloud SQL can fail over during maintenance so the service remains available with minimal disruption. This best aligns with the Architecture Framework’s reliability principles and meets the requirement to patch within 7 days without impacting users during operating hours.
Core concept: This question tests Cloud SQL maintenance behavior and how to design for availability during planned maintenance. In Cloud SQL, maintenance (OS/database patching) can require a restart and therefore downtime on a single-instance deployment. The key architectural principle from the Google Cloud Architecture Framework is to design for high availability and minimize single points of failure. Why the answer is correct: Enabling High Availability (regional) for Cloud SQL for PostgreSQL creates a primary instance with a synchronous standby in a different zone within the same region (us-central1). During maintenance, Cloud SQL can perform a controlled failover to the standby, apply updates, and then (optionally) fail back. This significantly reduces user-visible disruption compared to patching a single instance, helping meet the requirement of no downtime during operating hours (06:00–22:00 America/Chicago) while still complying with the policy to apply patches within 7 days. Key features / configurations: - Cloud SQL HA (regional) uses zonal redundancy with automatic failover and a regional IP option. - Maintenance can be scheduled, but even with a window, a single instance still experiences downtime during restart. - HA is the recommended approach for production workloads that require high availability and reduced planned/unplanned downtime. - Cloud Run is stateless and can tolerate brief connection blips; HA minimizes the database-side interruption window. Common misconceptions: - A maintenance window alone (Option A) does not eliminate downtime; it only controls when it happens. Also, Cloud SQL maintenance windows are configured in UTC, and “sequencing” across instances doesn’t help if production is a single instance. - Read replicas (Option B) are for scaling reads and disaster recovery patterns, but they are asynchronous and cannot automatically take over as a primary for writes during maintenance without promotion and application reconfiguration. - Notifying users (Option C) does not meet the requirement to avoid downtime during operating hours. Exam tips: When you see “avoid downtime during maintenance” for Cloud SQL, think HA (regional) first. Maintenance windows are about scheduling, not eliminating downtime. Read replicas do not provide seamless write availability. Also remember Cloud SQL maintenance windows are specified in UTC and security patch timelines may force maintenance within a limited period, making HA the safest compliance-friendly design.
You operate a Cloud SQL for PostgreSQL deployment in Google Cloud. The primary instance runs in zone europe-west1-b, and a read replica runs in zone europe-west1-c within the same region. An alert reports that the read replica in europe-west1-c was unreachable for 11 minutes due to a zonal network disruption. You must ensure that the read-only workload continues to function and that the replica remains available with minimal manual intervention. What should you do?
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 is the best choice because the read-only workload depends on a serving replica, and the only replica has been unreachable for an extended period. Creating a new read replica is the most direct way to restore read capacity while keeping the primary online for writes. Redirecting traffic to the new replica addresses the workload requirement, whereas waiting for automatic recovery does not guarantee service continuity. Although not ideal compared with having multiple replicas preconfigured, it is the strongest recovery action among the listed options.
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.
Core concept: This question tests Cloud SQL for PostgreSQL read replica availability and operational recovery during a zonal disruption. A Cloud SQL read replica is a separate asynchronous replica instance, and a single replica in one zone is still a single point of failure for read traffic in that zone. Cloud SQL does not provide automatic read workload failover for a lone read replica, so if continued read availability is required, you must restore replica capacity yourself or design multiple replicas in advance. Why correct: Because the only read replica was unreachable for 11 minutes and the requirement is to ensure the read-only workload continues to function with minimal manual intervention, the best available action is to create a new read replica from the latest available state and redirect read traffic to it. This restores a serving replica without disrupting the primary. It is the closest operational response among the options to re-establishing read availability after the zonal failure. Key features: - Cloud SQL read replicas are asynchronous and do not automatically provide read failover semantics by themselves. - A zonal outage affecting the replica zone can leave read traffic without a serving target unless another replica already exists. - Recreating a read replica is an administrative recovery action; for stronger resilience, deploy multiple replicas across zones and use application-side routing or connection management. Common misconceptions: A common mistake is assuming Cloud SQL will automatically recreate a read replica in another zone after a zonal disruption. Another misconception is that restarting the primary helps replica recovery; it usually only adds unnecessary write disruption. Clones are also not substitutes for read replicas because they are independent copies, not replication targets. Exam tips: - Distinguish primary HA/failover behavior from read replica behavior; they are not the same. - If the question asks about maintaining read workload continuity, think about replica redundancy and traffic redirection. - When only one replica exists and it becomes unavailable, manual recreation may be necessary unless the architecture already includes additional replicas.
Want to practice all questions on the go?
Download Cloud Pass — includes practice tests, progress tracking & more.
You manage a Cloud SQL for PostgreSQL instance and currently run a 120-line Python 3.11 script that executes SELECT statements to collect table bloat and index usage metrics, writes a CSV to a Cloud Storage bucket, and publishes a Pub/Sub message; the script is stateless and completes in under 90 seconds. You need to run this job automatically every Monday at 02:00 UTC while minimizing operational cost and operational overhead. What should you do?
A Compute Engine VM with cron can run the script, but it adds operational overhead: OS maintenance, patching, monitoring, and ensuring the VM is always available at 02:00 UTC. Cost is typically higher because the VM must remain provisioned (often running 24/7) even though the job runs for only ~90 seconds weekly. This conflicts with the requirement to minimize cost and ops.
Cloud Composer (managed Airflow) is designed for complex workflows, dependencies, and multi-step pipelines. For a single 120-line script that runs once a week, Composer is overkill and introduces significant cost (environment resources run continuously) and operational complexity (DAG management, Airflow concepts, environment upgrades). It does not align with “minimize operational cost and overhead.”
Cloud Functions is a strong fit for a short, stateless Python job, and Cloud Scheduler provides a managed cron trigger for weekly execution at a precise time. This approach is serverless, scales automatically, and charges per use, minimizing both cost and operational burden. The function can securely connect to Cloud SQL, write to Cloud Storage, and publish to Pub/Sub using a least-privilege service account.
Cloud Tasks is primarily for asynchronous task execution with queue-based dispatch, rate limiting, and retry policies. It does not natively provide cron scheduling; you would still need Cloud Scheduler (or another trigger) to enqueue tasks weekly, adding unnecessary components. For a simple weekly job, invoking the function directly from Cloud Scheduler is simpler, cheaper, and lower-ops.
Core Concept: This question tests choosing the lowest-ops, lowest-cost serverless scheduling pattern for a short, stateless administrative job that interacts with Cloud SQL, Cloud Storage, and Pub/Sub. The key services are Cloud Functions (or Cloud Run) for serverless execution and Cloud Scheduler for cron-like triggers. Why the Answer is Correct: A stateless Python script that finishes in <90 seconds is an ideal fit for Cloud Functions. You avoid managing servers, OS patching, and cron reliability. Cloud Scheduler provides a managed cron service with a specific schedule (every Monday 02:00 UTC) and can invoke an HTTP-triggered function (or publish to Pub/Sub). This combination minimizes operational overhead and cost because you pay only for invocations and execution time, not for an always-on VM or a continuously running orchestration environment. Key Features / Configurations / Best Practices: - Use a 2nd gen Cloud Function (recommended) with an HTTP trigger and restrict invoker permissions (least privilege) via IAM. - Connect to Cloud SQL for PostgreSQL using the Cloud SQL Python Connector or Cloud SQL Auth Proxy approach (2nd gen supports direct VPC connectivity options). Prefer IAM DB authentication where feasible and store secrets in Secret Manager. - Ensure the function’s service account has only required roles: Cloud SQL Client, Storage Object Creator (or specific bucket permissions), and Pub/Sub Publisher. - Set appropriate timeout (>= 90s), memory, and concurrency defaults; keep it single-purpose. - Cloud Scheduler uses a cron expression like "0 2 * * 1" with UTC timezone. Common Misconceptions: - Using Cloud Composer can feel “enterprise-grade,” but it’s heavy and costly for a single short job. - Cloud Tasks is often confused with scheduling; it’s for asynchronous task dispatch and retry control, not weekly cron scheduling. - A VM + cron is simple but increases ops burden (patching, monitoring, uptime) and typically costs more due to always-on resources. Exam Tips: When you see “stateless,” “short runtime,” and “minimize ops/cost,” default to serverless compute (Cloud Functions/Cloud Run) plus Cloud Scheduler for time-based triggers. Use Cloud Tasks for high-volume, retryable background work initiated by events or applications—not for weekly schedules.
A nightly containerized ETL job running as the service account sa-etl@project-id.iam.gserviceaccount.com must read rows from a Cloud SQL for MySQL instance in us-central1 via the Cloud SQL Auth Proxy using a standard MySQL user (not IAM DB Auth) and then upsert up to 50,000 records into a regional Cloud Spanner database in us-central1. You must grant only predefined IAM roles following the principle of least privilege so the job can connect to Cloud SQL to read data and modify a table in Cloud Spanner; which roles should you assign to the service account?
roles/cloudsql.viewer is insufficient for Cloud SQL Auth Proxy connectivity. Viewer allows listing and getting instance configuration/metadata, but it does not provide the permissions required to establish a connection via the Cloud SQL Admin API used by the proxy. roles/spanner.databaseUser is appropriate for Spanner DML, but the Cloud SQL side fails least-privilege functionality because the job still can’t connect.
roles/cloudsql.client is the correct minimal predefined role to allow a workload identity (service account) to connect to a Cloud SQL instance using the Cloud SQL Auth Proxy/Cloud SQL connectors. Because the job uses a standard MySQL user, IAM is only needed for the proxy connection, not for DB authentication. roles/spanner.databaseUser is the least-privilege role for reading/writing Spanner data (including upserts) without admin privileges.
roles/cloudsql.editor and roles/spanner.admin are both excessive. Cloud SQL Editor allows modifying instances (settings, flags, users in some contexts) which is not needed for a read-only ETL source connection. Spanner Admin allows creating/deleting instances and databases and changing IAM/policies—far beyond the requirement to upsert rows into an existing database. This violates least privilege and increases risk.
roles/cloudsql.instanceUser is intended for IAM database authentication to Cloud SQL, where the database login itself is tied to an IAM principal. In this scenario, the job uses a standard MySQL username and password, so that role is not the relevant least-privilege choice for connectivity through the Cloud SQL Auth Proxy. The proxy/connectors require Cloud SQL connection permissions provided by roles/cloudsql.client, while the MySQL account separately needs in-database SELECT privileges. Therefore, D is incorrect because it selects the wrong Cloud SQL role for the stated authentication model.
Core concept: This question tests least-privilege IAM for cross-database ETL using Cloud SQL Auth Proxy (Cloud SQL for MySQL) and Cloud Spanner. It specifically distinguishes Cloud SQL connectivity permissions (IAM) from database authentication (MySQL user/password) and Spanner data-plane permissions for DML. Why the answer is correct: To connect through the Cloud SQL Auth Proxy, the calling identity must be authorized at the Cloud SQL API layer to obtain instance connection metadata/certificates and establish a secure connection. The predefined role that grants this is roles/cloudsql.client. Using a standard MySQL user (not IAM DB Auth) means IAM is not used to authenticate to MySQL itself; IAM only authorizes the proxy connection to the instance. For Cloud Spanner, the job must upsert (insert/update) up to 50,000 records, which requires write permissions on the database. roles/spanner.databaseUser is the least-privilege predefined role intended for application-level access to a Spanner database, including reading and writing data (DML) without administrative capabilities like creating instances/databases or changing schemas. Key features / best practices: - Cloud SQL Auth Proxy requires IAM permission to connect to the instance; roles/cloudsql.client is the standard minimal role for this. - Database credentials remain separate: the MySQL user must have SELECT privileges in MySQL, but that is not granted via IAM. - Spanner access should follow separation of duties: application writers use roles/spanner.databaseUser; administrators use roles/spanner.admin. - Aligns with Google Cloud Architecture Framework security principle of least privilege and minimizing blast radius. Common misconceptions: - roles/cloudsql.viewer seems plausible because the job “reads,” but viewer is for viewing instance metadata and does not grant the connect capability needed by the Auth Proxy. - roles/cloudsql.editor or roles/spanner.admin are over-privileged and violate least privilege. - roles/cloudsql.instanceUser is commonly confused with Cloud SQL connectivity; it is primarily relevant to IAM database authentication and instance-level user concepts, not the standard Auth Proxy connection requirement with traditional MySQL users. Exam tips: - For Cloud SQL Auth Proxy/Cloud SQL connectors: think “roles/cloudsql.client” for connectivity. - For Spanner application read/write without admin: “roles/spanner.databaseUser.” - Always separate IAM authorization (connect to service) from in-database grants (MySQL GRANT/Spanner schema roles).
Your logistics company is launching a live fleet-tracking map powered by Cloud Bigtable; the service performs approximately 150,000 random point-read operations per second with a 60:1 read-to-write ratio, requires 95th percentile read latency under 10 ms in a single region, and stores about 25 TB of frequently accessed hot data; to meet these latency and throughput targets, which Bigtable storage type should you choose?
SSD is the right choice for a Bigtable workload dominated by high-volume random point reads with a strict p95 latency target (<10 ms). SSD provides far higher random IOPS and more consistent tail latency than HDD, which is essential at ~150,000 reads/sec on 25 TB of hot data. You still must size the cluster with enough nodes, but SSD is the correct storage medium to meet the latency SLO.
Splitting into two Bigtable instances can increase aggregate capacity, but it adds complexity (data partitioning, routing logic, operational overhead) and does not inherently solve latency constraints if the underlying storage is not suitable. Bigtable is designed to scale within an instance by adding nodes and using proper row-key distribution. The question asks specifically which storage type to choose to meet latency/throughput targets.
HDD reduces storage cost per TB, but it is not appropriate for a latency-sensitive, high-QPS random point-read workload. HDD-backed Bigtable is better for large datasets with less stringent latency requirements and more scan-oriented access patterns. At 150,000 random reads/sec with p95 under 10 ms, HDD is likely to miss tail-latency and IOPS requirements even if you add nodes.
Cloud Bigtable does not support mixing SSD and HDD within the same instance; storage type is selected at the instance level. Even conceptually, mixing tiers would not guarantee the required p95 latency unless all hot data is on SSD and access is correctly routed. For exam purposes, treat this as an invalid configuration and choose SSD for hot, latency-critical workloads.
Core Concept: This question tests Cloud Bigtable performance planning—specifically how storage type (SSD vs HDD) impacts random point-read latency and sustained throughput. Bigtable is optimized for low-latency key/value access, but the storage medium strongly affects tail latency (p95/p99) for random reads. Why the Answer is Correct: The workload is dominated by random point reads: 150,000 reads/sec with a 60:1 read-to-write ratio. The requirement is p95 read latency under 10 ms in a single region while serving ~25 TB of hot data. For random access patterns at high QPS, SSD-backed Bigtable provides substantially lower latency and higher IOPS than HDD. HDD is better suited to large sequential scans and cost-sensitive, colder datasets; it typically cannot sustain very high random read rates with tight p95 latency targets. Therefore, SSD is the correct storage type to meet both throughput and latency SLOs. Key Features / Best Practices: - Use SSD storage for latency-sensitive, high-QPS random reads. - Scale throughput by adding Bigtable nodes; storage type and node count together determine achievable QPS and tail latency. Ensure enough nodes to avoid CPU saturation and to keep per-node QPS within recommended ranges. - Keep the design single-region as required; use multiple clusters only if you need higher availability or multi-region reads. - Model row keys to distribute reads evenly (avoid hotspotting), which is critical at 150k QPS. Common Misconceptions: - “Split across two instances” sounds like it increases read capacity, but it complicates routing, consistency, and operational overhead, and it doesn’t address the fundamental latency/IOPS limitation of HDD. - “HDD is cheaper” is true per TB, but the question prioritizes p95 latency and high random read throughput on hot data. - “Mix SSD and HDD in one instance” is not a supported Bigtable configuration; storage type is chosen per instance. Exam Tips: When you see Bigtable + strict sub-10 ms p95 + high random point-read QPS, default to SSD. Use HDD primarily for cost-optimized, less latency-sensitive workloads (e.g., batch analytics scans) and remember that Bigtable performance is scaled primarily by node count, while storage type strongly influences random I/O latency and tail behavior.
You are designing the database layer for a global real-time loyalty points platform for a retail conglomerate. The system must support ACID transactions with strong consistency for balance updates and redemptions. It must store both relational data (customers, stores, balances) and semi-structured JSON attributes (dynamic campaign metadata, per-store rules). It must scale transparently with multi-region writes and low-latency access across at least 3 continents, with RPO=0 and RTO<60 seconds during regional failures. You need a Google Cloud database that satisfies all of these requirements. What should you choose?
Cloud SQL for PostgreSQL supports ACID and relational modeling, but cross-region read replicas are asynchronous and primarily for read scaling and disaster recovery. They do not provide active-active multi-region writes with strong consistency, and failover typically involves promotion with potential replication lag (RPO>0). Latency for global users is also limited because the primary writer is in one region.
Cloud Spanner multi-region is purpose-built for globally distributed OLTP with horizontal scaling, strong consistency, and ACID transactions. It supports synchronous replication across regions (enabling RPO=0), automatic failover (supporting tight RTO), and low-latency reads via replicas. It also supports relational schemas and semi-structured data using JSON (and related types), matching the mixed data requirements.
Firestore in Datastore mode offers high scalability and multi-region replication, but it is a NoSQL document database and does not provide the same relational capabilities (joins, relational constraints) expected for customers/stores/balances. While it supports transactions, the model and query patterns differ significantly, and it is not the best fit for complex relational integrity plus global strongly consistent balance updates at scale.
Bigtable is a wide-column NoSQL database optimized for very high throughput and large-scale time-series or key-value workloads. It does not provide relational modeling or SQL, and it does not support general-purpose ACID transactions across rows like an OLTP system needs for balance updates/redemptions. Multi-cluster replication improves availability, but it is not a strongly consistent, multi-region transactional database.
Core concept: This question tests selecting a globally distributed, strongly consistent, horizontally scalable OLTP database on Google Cloud that supports ACID transactions and mixed relational + semi-structured data. Why the answer is correct: Cloud Spanner in a multi-region configuration is designed for global, real-time transactional systems that require strong consistency and high availability across regions. Spanner provides external consistency (strong consistency) for reads/writes and full ACID transactions, which is essential for loyalty balance updates and redemptions (preventing double-spend and ensuring correct balances). It also scales transparently via horizontal sharding managed by Spanner, avoiding manual partitioning. Key features / configurations: 1) Multi-region instance configuration (e.g., nam3/eur3/asia3) provides low-latency access across continents and automatic failover. With multi-region, Spanner uses synchronous replication with a voting/leader model; commits are replicated across regions, enabling RPO=0. 2) High availability and fast recovery: regional failures are handled by automatic leader re-election and serving from remaining replicas; this aligns with stringent RTO targets (often seconds to under a minute depending on failure mode and client retry behavior). 3) Data model flexibility: Spanner supports relational schemas (tables, interleaving/foreign keys) and semi-structured attributes via JSON data type (and/or ARRAY/STRUCT patterns), fitting dynamic campaign metadata and per-store rules. 4) Operational best practices: use multi-region configs, design primary keys for locality and hotspot avoidance, use read-write transactions for balance changes, and consider commit latency tradeoffs (global synchronous replication increases write latency vs single-region). Common misconceptions: Cloud SQL with cross-region replicas can look attractive for PostgreSQL compatibility, but it cannot provide multi-region writes with strong consistency and RPO=0; replicas are asynchronous and typically read-only. Firestore and Bigtable scale globally, but they are not relational OLTP systems with the same transactional semantics and SQL relational modeling required here. Exam tips: When you see “global,” “ACID,” “strong consistency,” “multi-region writes,” and “RPO=0,” Spanner is the default answer. Validate that the workload is OLTP (balances/redemptions) rather than analytics, and remember that multi-region Spanner trades higher write latency for global consistency and availability—an explicit requirement in this scenario.
You operate a Cloud SQL for MySQL primary instance (sql-prod-eu) in europe-west1 with two cross-region read replicas (sql-dr-us in us-central1 and sql-dr-asia in asia-southeast1); during a 45-minute disaster recovery exercise, you stopped sql-prod-eu and promoted sql-dr-us to primary to handle up to 3,000 write QPS, and now that the exercise is complete you must restore the pre-exercise topology with sql-prod-eu as the primary in europe-west1 and cross-region replicas in us-central1 and asia-southeast1 while preserving all writes that occurred during the test—what should you do?
Incorrect. Bringing sql-prod-eu back online does not incorporate the writes that occurred on sql-dr-us after promotion, so making it primary again would lose data. Cloud SQL for MySQL does not automatically reconcile divergent histories between the old primary and the promoted replica. Repointing replicas to sql-prod-eu would therefore restore an outdated timeline rather than preserve all writes from the DR exercise.
Incorrect. Re-creating sql-prod-eu as a cross-region read replica of sql-dr-us is only an intermediate rebuild step and does not satisfy the requirement to restore sql-prod-eu as the primary. As written, this option leaves europe-west1 as a replica, not the primary, so the pre-exercise topology is not actually restored. The required action must include promotion of the new europe-west1 instance after it catches up. Because option D explicitly includes that promotion step, it is the better and complete answer.
Incorrect. Deleting sql-dr-us would simply remove the instance that contains the writes generated during the DR exercise. Cloud SQL does not automatically transfer those writes back to sql-prod-eu or revert the old topology when a promoted primary is deleted. This option would cause data loss and would not restore a valid primary/replica configuration.
Correct. After sql-dr-us was promoted and handled writes, it became the authoritative primary containing the only complete copy of the DR-window transactions. To restore europe-west1 as the primary region without losing those writes, you must create a new read replica in europe-west1 from sql-dr-us, let it catch up, and then promote it to primary. Once that new europe-west1 primary is active, you can recreate cross-region replicas in us-central1 and asia-southeast1 to re-establish the desired topology. This is the supported failback pattern because Cloud SQL does not support reversing replication back into the old primary and making it authoritative again.
Core concept: In Cloud SQL for MySQL, promoting a read replica breaks the original replication topology and creates a new standalone primary. Any writes that occur after promotion exist only on that promoted instance, so the old primary cannot be reinstated as primary without being rebuilt from the new source of truth. Why correct: To fail back while preserving all writes, you must seed a new instance in the target region from the currently active primary, allow replication to catch up, and then promote that new regional replica to become the primary. Key features: cross-region read replicas replicate asynchronously, promotion is a one-way topology change, and failback requires rebuilding from the promoted primary rather than reusing the old primary directly. Common misconceptions: many candidates assume the old primary can simply be restarted and reused, or that deleting the promoted primary will somehow revert roles automatically; neither is supported. Exam tips: when a promoted replica has accepted writes and you must restore the original primary region with no data loss, look for the option that creates a new replica from the promoted primary in the desired region and then promotes it.
Study period: 1 month
Cloud Pass helped me master through practical and realistic questions. The explanations were clear and helped me understand.
Study period: 1 month
문제 다 풀고 시험쳤는데 유사한 문제가 많았어요. 다른 분들도 화이팅입니다!
Study period: 2 weeks
I studied with Cloud Pass for two weeks, doing around 20 ~30 questions a day. The difficulty level was very similar to the real PCDBE exam. If you’re preparing for this certification, this app is a must have.
Study period: 1 month
Very close to the real exam. Thanks
Study period: 1 month
Being able to reset my progress and re-solve the hard questions helped me a lot. Passed!
Download Cloud Pass and start practicing all Google Professional Cloud Database Engineer exam questions.
Want to practice all questions on the go?
Get the app
Download Cloud Pass — includes practice tests, progress tracking & more.
