PCDE Plan and manage database infrastructure Practice Test 5 — 15 Questions

Question 1

A company is migrating a large Oracle database to Cloud Spanner. The source database uses sequences for primary key generation. The database engineer needs to design the Cloud Spanner schema to avoid hotspotting. What primary key design should they recommend?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use a hash of the original key as a prefix to the primary key.. Using a hash of the original key as a prefix to the primary key distributes writes evenly across Cloud Spanner's splits, preventing hotspotting. Cloud Spanner uses a distributed, append-only storage model where sequential keys (like from Oracle sequences) cause all new writes to land on the same split, creating a hotspot. A hash prefix ensures that related rows are still co-located for efficient queries while spreading write load across multiple nodes.

Answer

Keep the same sequence-based integer keys.

Answer

Use a composite primary key with a timestamp prefix.

Answer

Use UUIDs as the primary key without modification.

Question 2

An organization has a Cloud SQL for MySQL instance that stores sensitive data. They need to encrypt the data at rest using a customer-managed encryption key (CMEK). The database engineer creates a Cloud KMS key ring and key, and configures the Cloud SQL instance to use CMEK. However, after 30 days, the instance becomes inaccessible and the error message indicates the CMEK key is disabled. What is the most likely cause?

Accepted Answer

The CMEK key was disabled by the key administrator for security reasons.. The error message indicates the CMEK key is disabled, and the most common cause in a controlled environment is that the key administrator intentionally disabled the key. Cloud SQL for MySQL requires the CMEK key to be enabled to encrypt and decrypt data at rest; if the key is disabled, the instance cannot access its data and becomes inaccessible. This aligns with the scenario where no other configuration changes are mentioned, making administrative action the likely cause.

Answer

The Cloud SQL instance exceeded the number of times it can access the CMEK key.

Answer

The CMEK key was rotated, and the old key version was disabled.

Answer

The CMEK key expired, causing automatic disablement.

Question 3

A manufacturing company is deploying a time-series database for sensor data on Cloud Bigtable. They expect 100 TB of data per year and need low-latency reads on row keys within the last hour. Which TWO design choices should they make?

Accepted Answer

Use a reverse timestamp in the row key so that recent data is at the beginning of the table.. Option C is correct because Cloud Bigtable stores rows in lexicographic order by row key, so prefixing the key with a reverse timestamp (e.g., Long.MAX_VALUE minus the timestamp) places the most recent sensor data at the start of the table, enabling fast, low-latency scans of the last hour's data. Option D is correct because a salting prefix (a deterministic hash bucket prepended to the row key) spreads sequential time-series writes across multiple tablet nodes, preventing a single hot tablet from becoming a write bottleneck as 100 TB/year of sensor data is ingested. Option A is wrong because Bigtable does not have a user-set 'maximum QPS' parameter; performance is governed by node count and autoscaling, and capping QPS would throttle ingestion rather than optimize the design. Option B is wrong because splitting data into monthly tables is an anti-pattern that complicates queries and schema management; Bigtable handles large volumes within a single table using row-key design. Option E is wrong because long row keys increase storage and memory overhead and slow lookups; row keys should be short and designed to avoid hotspots, not lengthened to prevent collisions.

Answer

Set the maximum QPS to 1000 to avoid overloading the cluster.

Answer

Partition the data into separate tables for each month.

Answer

Design row keys to be as long as possible to avoid collisions.

Question 4

A global retail company uses Cloud Spanner to manage product inventory. They need to apply a schema change to add a new column to a table that has 10 billion rows. Which THREE strategies should they consider to minimize downtime?

Accepted Answer

Schedule the schema change during a period of low traffic.. Option A is correct because Cloud Spanner schema updates are online and non-blocking, but scheduling the ALTER TABLE during a low-traffic window still reduces contention and any transient latency impact on a 10-billion-row table. Option C is correct because adding a column with a NULL default (or no default) does not require Spanner to rewrite or backfill existing rows, so the change completes quickly without touching the 10 billion rows. Option D is correct because ALTER TABLE ... ADD COLUMN IF NOT EXISTS makes the DDL idempotent, preventing errors if the column already exists and avoiding failed schema-update retries that could prolong the operation. Option B is not appropriate because Cloud Spanner does not support disabling and re-enabling a table for schema changes, and doing so would cause downtime. Option E is unnecessary and costly because creating a new table and copying 10 billion rows in batches introduces significant complexity and data-movement overhead, whereas Spanner handles ADD COLUMN online.

Answer

Disable the table, add the column, then re-enable.

Answer

Create a new table with the column, then copy data in batches.

Question 5

A company is planning to migrate a self-managed MongoDB database to a fully managed Google Cloud service. They need to maintain high availability and support complex queries with aggregation pipelines. Which TWO services should they consider?

Accepted Answer

MongoDB Atlas on Google Cloud. MongoDB Atlas on Google Cloud (A) is correct because it is a fully managed MongoDB service available in Google Cloud regions, preserving MongoDB's native aggregation pipeline and replica-set high availability during migration. Cloud SQL for PostgreSQL with the JSON data type (E) is also correct because it is a fully managed Google Cloud database that supports JSON storage and complex SQL/JSON queries, offering HA configurations via regional instances. Cloud Spanner (B) is not appropriate because it is a relational, horizontally scalable database with its own SQL dialect and no MongoDB aggregation pipeline support. Cloud Firestore (C) is a NoSQL document store but lacks MongoDB aggregation pipelines and is not a drop-in MongoDB replacement. Cloud Bigtable (D) is a wide-column NoSQL store optimized for high-throughput analytics, not for MongoDB-style aggregation queries or HA migration of a MongoDB workload.

Answer

Cloud Spanner

Answer

Cloud Firestore

Answer

Cloud Bigtable

Question 6

A company uses Cloud Firestore in Datastore mode for a multi-tenant SaaS application. Each tenant has a separate namespace. The application has grown rapidly, and the database engineer notices that write throughput is degrading. Monitoring shows that the number of writes per second is high but within Firestore limits. However, the latency for writes is increasing linearly with the number of tenants. The engineer suspects that index management is causing the problem. The current schema uses automatic indexes for all properties. What is the best corrective action?

Accepted Answer

Disable automatic indexing and create custom composite indexes only for queries that require them.. Automatic indexing in Firestore in Datastore mode creates an index for every property, which causes write amplification as each write must update all relevant indexes. With many tenants in separate namespaces, the number of indexes grows linearly with tenants, increasing write latency. Disabling automatic indexing and creating custom composite indexes only for needed queries reduces the index write overhead, restoring write throughput.

Answer

Implement a data retention policy to delete old tenant data.

Answer

Request a throughput increase from Google Cloud support.

Answer

Shard tenants into separate Firestore databases to distribute the write load.

Question 7

A company has a Cloud SQL instance with a 500 GB database. They need to perform a major version upgrade from MySQL 5.7 to 8.0 with minimal downtime. Which strategy should they use?

Accepted Answer

Create a read replica with new version, then promote. Creating a read replica with the new MySQL version and then promoting it minimizes downtime because replication keeps the replica synchronized with the primary until promotion. This approach avoids the lengthy export/import process and reduces the risk of data loss or extended unavailability.

Answer

Export and import the database

Answer

Use in-place upgrade via gcloud command

Answer

Upgrade during maintenance window

Question 8

A developer needs a local development database that mirrors a Cloud SQL instance. What is the best practice?

Accepted Answer

Export data and import to local MySQL. Exporting the Cloud SQL instance data (e.g., using `gcloud sql export sql` or `mysqldump`) and importing it into a local MySQL database creates an exact, offline replica of the production schema and data. This allows the developer to work with a full, consistent dataset without network latency, connection overhead, or dependency on Cloud SQL availability, which is the standard best practice for local development mirrors.

Answer

Use Cloud SQL Proxy to connect locally

Answer

Use Cloud Functions

Answer

Use Cloud SQL's public IP

Question 9

Which TWO database services are fully managed and support global distribution of data for low-latency reads and writes?

Accepted Answer

Cloud Spanner. Cloud Spanner (B) is correct because it is a fully managed, horizontally scalable relational database that offers multi-region configurations with synchronous replication, enabling globally distributed, strongly consistent reads and writes with low latency. Firestore (C) is correct because it is a fully managed NoSQL document database whose multi-region and regional modes automatically replicate data across regions, providing low-latency reads and writes for globally distributed clients. Memorystore (A) is not correct because it is a fully managed in-memory Redis/Memcached service that is regional, not globally distributed. Bigtable (D) is not correct because, while fully managed and highly scalable, it is a single-region (or multi-cluster within a region) wide-column store and does not provide global multi-region distribution for low-latency writes. Cloud SQL (E) is not correct because it is a managed relational service for MySQL, PostgreSQL, and SQL Server that is regional and does not natively support global distribution.

Answer

Memorystore

Answer

Bigtable

Answer

Cloud SQL

Question 10

Which TWO actions can a team take immediately to resolve a Cloud SQL instance running out of storage?

Accepted Answer

Enable automatic storage increase. Option A (Enable automatic storage increase) is correct because Cloud SQL's automatic storage increase feature lets the instance expand its disk size on demand when it approaches the threshold, immediately relieving the out-of-storage condition without downtime. Option B (Increase storage capacity via gcloud) is correct because you can run a command such as gcloud sql instances patch INSTANCE_NAME --storage-size=SIZE to manually raise the instance's storage capacity right away, which resolves the shortage. Option C (Delete binary log files) is not a valid immediate fix because binary logs are needed for point-in-time recovery and replication, and deleting them is not a supported Cloud SQL action to free space. Option D (Migrate to Cloud Spanner) is not immediate and is a major architectural change, not a quick remediation for a full disk. Option E (Add read replicas) does not increase the primary instance's storage and would not resolve the out-of-storage condition.

Answer

Delete binary log files

Answer

Migrate to Cloud Spanner

Answer

Add read replicas

Question 11

A global gaming company uses Cloud Spanner for user profiles and game state. They have a single-region instance in us-central1. Recently, they launched in Europe and notice high latency for European users. They also need to ensure data locality compliance (GDPR). The database is heavily written with throughput spikes. They want to minimize latency without sacrificing write throughput. They consider adding a secondary index on region column. What is the best course of action?

Accepted Answer

Create a multi-region configuration spanning US and Europe, and modify application to read from closest region.. A multi-region Cloud Spanner configuration with regional endpoints allows the application to read from the closest region, reducing latency for European users while maintaining strong consistency and write throughput. Spanner's multi-region configurations use synchronous replication across all regions, ensuring GDPR compliance by keeping European user data within Europe for reads. Write throughput is not sacrificed because Spanner's architecture is designed for high scalability and uses synchronous replication efficiently.

Answer

Shard the database by region into separate Spanner instances.

Answer

Use Cloud CDN to cache read data.

Answer

Create a read-only replica in Europe using Cloud Spanner's read replica feature.

Question 12

A small business runs a Cloud SQL instance with 10 GB data. They want to automate daily backups with 7-day retention. They also need to restore quickly if needed. What is the simplest solution?

Accepted Answer

Enable automatic backups in Cloud SQL settings.. Cloud SQL provides a built-in automatic backup feature that can be configured with a 7-day retention period directly in the instance settings. This eliminates the need for custom scripts or external schedulers, and enables point-in-time recovery for fast restoration without manual export/import overhead.

Answer

Use Cloud Scheduler to trigger exports.

Answer

Export to Cloud Storage daily using a cron job.

Answer

Use gcloud to create a backup schedule with retention.

Question 13

An online retailer uses Cloud SQL for PostgreSQL. They need to scale for a seasonal peak. They expect 2x current traffic. Their current instance is 16 vCPU, 64 GB RAM, 1 TB storage. The peak lasts 4 hours. They want to handle it without downtime. What is the best approach?

Accepted Answer

Increase vCPU and memory to 32 vCPU/128 GB temporarily for the peak window.. Cloud SQL for PostgreSQL supports vertical scaling with minimal downtime, and temporarily increasing vCPU and memory to 32 vCPU/128 GB for the 4-hour peak window meets the 2x traffic demand without requiring application changes. This approach avoids permanent cost increases and leverages Cloud SQL's ability to scale up and down via the gcloud command or console, with only a brief failover (typically under 60 seconds) that can be scheduled during a maintenance window to achieve near-zero downtime.

Answer

Use Cloud Spanner to auto-scale.

Answer

Upgrade to a higher-tier machine type permanently.

Answer

Add read replicas and rewrite queries to use replicas for reads.

Question 14

A company is migrating their on-premises Oracle database to Cloud SQL for PostgreSQL. They want to minimize downtime during the cutover. Which two strategies should the database engineer recommend? (Choose 2.)

Accepted Answer

Use Database Migration Service with continuous replication.. Database Migration Service (DMS) with continuous replication is the only strategy that enables minimal downtime migration from Oracle to Cloud SQL for PostgreSQL. DMS handles full data load and ongoing change replication until cutover. Option C (pglogical) is invalid because pglogical only supports replication between PostgreSQL databases, not from Oracle. Option D (read replicas and promote) is not a migration strategy from an external Oracle database; it is for scaling within PostgreSQL. Options A and E involve significant downtime.

Answer

Use a Cloud VPN tunnel for data transfer.

Answer

Use a third-party tool like pglogical for replication.

Answer

Use Cloud SQL for PostgreSQL with read replicas and promote.

Answer

Perform an export using pg_dump and import using psql.

Question 15

A company runs a Spanner instance with a single region configuration. They are experiencing increased latency for writes when there is a network disruption between their application and the Spanner instance. The application is deployed in the same region. What should the database engineer do to minimize write latency during such disruptions?

Accepted Answer

Implement client-side retry logic.. Client-side retry logic is the correct approach because Spanner's built-in retry mechanisms (e.g., gRPC deadlines and backoff) can automatically re-send failed write requests when transient network disruptions occur. By implementing application-level retry with exponential backoff and jitter, the database engineer ensures that write operations are resilient to short-lived network blips without requiring infrastructure changes. This directly minimizes write latency during disruptions by avoiding manual intervention and leveraging Spanner's session-based retry capabilities.

Answer

Enable multi-region configuration.

Answer

Use a compute engine instance as a proxy.

Answer

Increase the number of nodes.