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

Question 1

A company is planning to migrate their on-premises Oracle database to Cloud SQL. Which THREE prerequisites must be satisfied?

Accepted Answer

Convert Oracle-specific syntax to PostgreSQL or MySQL. Option A is correct because Cloud SQL does not run Oracle; it supports MySQL, PostgreSQL, and SQL Server, so Oracle-specific PL/SQL, data types, and syntax must be converted to a supported engine such as PostgreSQL or MySQL before migration. Option C is correct because Database Migration Service (DMS) only supports specific source/target combinations and versions, so the on-premises Oracle source must be verified as compatible with DMS for the migration to proceed. Option D is correct because DMS requires network connectivity between the on-premises source and Google Cloud, established via VPC peering, VPN, or Interconnect, so the source can reach the destination instance. Option B is not required because DMS and Cloud SQL migrations work with standard editions, not only Enterprise Plus. Option E is not required because DMS performs a direct database-to-database migration and does not mandate a Cloud Storage staging bucket.

Answer

Purchase Cloud SQL Enterprise Plus edition for the target instance

Answer

Create a Cloud Storage bucket for staging migration data

Question 2

Refer to the exhibit. After creating this Bigtable instance, the administrator noticed high read latency during peak hours. Which configuration change would most likely help?

Accepted Answer

Increase cluster-num-nodes to at least 5. Increasing the number of nodes in the Bigtable cluster (option B) directly improves read throughput by distributing the read load across more tablet servers. High read latency during peak hours typically indicates that the existing nodes are saturated, and adding nodes reduces per-node load, lowering overall latency. This is the most direct and effective configuration change for addressing read latency in a single-cluster setup.

Answer

Change cluster-storage-type to HDD

Answer

Add more clusters in different zones

Answer

Increase cluster-autoscaling-max-nodes to 20

Question 3

A developer reports that their application cannot connect to a Cloud SQL instance using private IP, but public IP works. The Cloud SQL instance is in VPC peering with the application's VPC. The application is in the same region. What is the most likely cause?

Accepted Answer

The private IP range of the Cloud SQL instance conflicts with the application's VPC.. When a Cloud SQL instance is configured with a private IP address that overlaps with the application's VPC CIDR range, the VPC peering connection cannot route traffic correctly. This is due to the fact that VPC peering requires non-overlapping IP ranges to establish proper routing tables; overlapping ranges cause route conflicts and connectivity failures. Since public IP works, the issue is isolated to private IP routing, making IP range conflict the most likely cause.

Answer

The VPC peering connection is not established.

Answer

The Cloud SQL proxy is not running.

Answer

The Cloud SQL instance has 'require SSL' enabled.

Question 4

A company wants to ensure point-in-time recovery for their PostgreSQL database on Cloud SQL. What must they enable?

Accepted Answer

Automatic backups. Automatic backups in Cloud SQL enable point-in-time recovery (PITR) by maintaining transaction logs that allow you to restore the database to any specific time within the backup retention period. Without automatic backups enabled, Cloud SQL only supports restoring from a full backup snapshot, which does not provide the granularity needed for PITR.

Answer

Query insights

Answer

Write-ahead logging (WAL) archiving

Answer

Binary logging

Question 5

A company is migrating an Oracle database to Cloud Spanner. The Oracle database has complex stored procedures and triggers. What is the best approach?

Accepted Answer

Rewrite the stored procedures and triggers to Spanner-compatible SQL and use a heterogeneous migration tool.. Oracle stored procedures and triggers are written in PL/SQL, which is not compatible with Cloud Spanner's SQL dialect. A heterogeneous migration tool (e.g., Striim or Datastream with custom transforms) can handle schema and data conversion, but the application logic must be rewritten to Spanner-compatible SQL (e.g., using Cloud Spanner's stored procedures in GoogleSQL). This ensures the business logic is preserved and optimized for Spanner's distributed architecture.

Answer

Use Dataflow to stream data from Oracle to Spanner.

Answer

Use BigQuery to load data then export to Spanner.

Answer

Use Database Migration Service for homogenous migration.

Question 6

A company's application using Cloud Bigtable is experiencing high read latency. The row keys are based on a timestamp prefix. Which design change is most likely to improve performance?

Accepted Answer

Reverse the timestamp in row keys to distribute writes.. In Cloud Bigtable, monotonically increasing row keys (like timestamps) cause all writes to hit a single tablet server, creating a hotspot that degrades read and write performance. Reversing the timestamp (e.g., using `Long.MAX_VALUE - timestamp`) distributes writes across the key space, preventing hotspots and reducing read latency by balancing load across nodes.

Answer

Increase the number of column families.

Answer

Use a single node cluster.

Answer

Use monotonically increasing row keys.

Question 7

A company needs a cross-region disaster recovery solution for their Cloud SQL MySQL database. Which feature should they use?

Accepted Answer

Cross-region replication using external replicas.. Cloud SQL for MySQL does not natively support cross-region replication, but you can achieve it by configuring an external replica (a MySQL instance running outside Cloud SQL, such as on Compute Engine) that uses MySQL's native binary log (binlog) replication from the primary Cloud SQL instance. This setup allows you to maintain a standby database in a different region for disaster recovery, with the external replica continuously applying changes from the primary.

Answer

Read replicas in the same region.

Answer

Cloud SQL for MySQL does not support cross-region replication.

Answer

Use Database Migration Service to continuously copy data.

Question 8

A Cloud Spanner database is experiencing high CPU utilization on one node. Users report slow queries. The table uses a UUID primary key. What is the most effective action?

Accepted Answer

Use a hash key prefix to distribute writes.. Cloud Spanner splits data into splits by primary key range, and a random UUID primary key causes hotspots because inserts are scattered but reads and writes concentrate on the same key ranges. Adding a hash prefix to the UUID distributes the key space evenly across splits, eliminating the hotspot and reducing CPU pressure. This is the canonical Spanner anti-pattern fix for UUID keys.

Answer

Add a database index on frequently queried columns.

Answer

Convert the UUID to a monotonically increasing integer.

Answer

Increase the number of nodes in the instance.

Question 9

Refer to the exhibit. A developer tries to connect to a Cloud SQL instance from a VM using the public IP. The connection fails with this error. What should the developer do to fix the connection?

Accepted Answer

Connect using the Cloud SQL Proxy.. The error indicates that the Cloud SQL instance does not have an authorized network allowing the VM's public IP. However, the correct fix is to use the Cloud SQL Proxy, which establishes a secure, authenticated tunnel to the instance without needing to authorize the VM's IP. The proxy handles IAM-based authentication and encryption, bypassing the public IP network authorization requirement entirely.

Answer

Enable SSL-only mode on the Cloud SQL instance.

Answer

Add the client IP to the authorized networks.

Answer

Change the instance to require SSL for private connections.

Question 10

Refer to the exhibit. A company wants to perform point-in-time recovery (PITR) for their Cloud SQL MySQL instance. Is PITR enabled?

Accepted Answer

Yes, because binaryLogEnabled is true.. Cloud SQL MySQL uses binary logging to enable point-in-time recovery (PITR). When `binaryLogEnabled` is set to `true`, the instance logs all changes, allowing restoration to any specific point within the configured transaction log retention period. The `pointInTimeRecoveryEnabled` field is not a valid Cloud SQL configuration parameter; instead, PITR is implicitly enabled when binary logging is active.

Answer

No, because PITR requires the backupConfiguration to have 'pointInTimeRecoveryEnabled: true'.

Answer

No, because transactionLogRetentionDays is set to 7.

Answer

Yes, because enabled is true.

Question 11

A Cloud Spanner database is experiencing high CPU utilization and latency. The workload is read-heavy with occasional writes. Which TWO actions would most effectively improve performance?

Accepted Answer

Create a secondary index on frequently queried columns.. Option A is correct because a secondary index on frequently queried columns lets read-heavy queries use an index scan instead of a full table scan, reducing the number of rows and CPU cycles Spanner must process, which directly lowers CPU utilization and latency. Option C is correct because adding more nodes to the Spanner instance increases the available compute and storage capacity, allowing the read-heavy workload to be distributed across more resources and improving throughput and latency. Option B is wrong because a smaller instance configuration reduces capacity, which would worsen CPU saturation and latency. Option D is wrong because split points are used to distribute hot rows for write-heavy hotspots, not to relieve a read-heavy workload's CPU pressure. Option E is wrong because interleaved tables reduce joins for parent-child data, but the scenario does not indicate join overhead is the bottleneck.

Answer

Use a smaller instance configuration to reduce cost.

Answer

Use a split point to distribute hot rows.

Answer

Enable interleaved tables to reduce joins.

Question 12

A company is building a microservices architecture and needs to choose a database per service. Which THREE factors should they consider?

Accepted Answer

Consistency requirements. Option A (Consistency requirements) is correct because each microservice's data store must match the consistency model the service needs — for example, strong ACID consistency for financial transactions versus eventual consistency for a social feed — and this directly drives the choice between relational databases and NoSQL stores like Cassandra or DynamoDB. Option C (Expected query patterns) is correct because how data will be accessed (simple key-value lookups, complex joins, full-text search, or aggregations) determines whether a relational engine, document store, graph database, or search index such as Elasticsearch is appropriate. Option D (Data size and growth rate) is correct because the volume of data and its projected growth affect scalability, sharding, and storage decisions, pushing teams toward horizontally scalable systems like MongoDB or Cassandra when data grows rapidly. Option B (Preferred programming language) does not belong because virtually all modern databases offer drivers for common languages, so language preference is not a primary architectural driver. Option E (Number of developers) does not belong because team size is an organizational concern, not a technical factor that determines the right database per service.

Answer

Preferred programming language

Answer

Number of developers

Question 13

A company plans to migrate an on-premises MySQL database to Cloud SQL. Which THREE steps should they include in their migration plan?

Accepted Answer

Test application compatibility with Cloud SQL.. Option A is correct because testing application compatibility with Cloud SQL ensures the application's drivers, connection strings, and queries work against the managed MySQL-compatible instance before cutover. Option B is correct because Database Migration Service (DMS) is the recommended Google Cloud tool for connecting to and continuously replicating from an on-premises MySQL source into Cloud SQL during migration. Option D is correct because the migration plan must decide whether Cloud SQL will use a private IP (via VPC/Private Service Connect) or a public IP with authorized networks, which affects connectivity from the on-premises source and the application. Option C is not correct because Cloud SQL supports MySQL, so converting stored procedures to PostgreSQL dialect is unnecessary unless migrating to PostgreSQL, which is not the stated target. Option E is not correct because point-in-time recovery is a backup/restore feature that should be configured on the target instance, but it is not a required migration step and does not by itself move data from on-premises.

Answer

Convert all stored procedures to PostgreSQL dialect.

Answer

Enable point-in-time recovery before migration.

Question 14

A financial services company runs a critical application on Cloud SQL for PostgreSQL. They require point-in-time recovery (PITR) with the ability to recover to any second within the past 7 days. However, their current backup configuration only allows recovery to the previous 7 days, but not within seconds. What should they do to enable PITR?

Accepted Answer

Enable point-in-time recovery and set the transaction log retention to 7 days.. Enabling point-in-time recovery (PITR) on Cloud SQL for PostgreSQL automatically uses write-ahead log (WAL) archiving to allow recovery to any second within a specified retention period. Setting the transaction log retention to 7 days ensures that the archived WAL segments are kept for exactly 7 days, enabling recovery to any point within that window. This directly satisfies the requirement for second-granularity recovery over the past 7 days.

Answer

Use the Cloud SQL query insight feature to replay queries.

Answer

Enable binary logging and set the binary log retention period to 7 days.

Answer

Increase the number of automated backups to 7 per day.

Question 15

A company uses Cloud Spanner with a multi-region configuration (nam7) to support a global user base. They notice increased read latency for users in Europe, while write latency is acceptable. The database engineer observes that most queries are single-row reads using the primary key. What is the best approach to reduce read latency for European users?

Accepted Answer

Use read-only transactions with stale reads (timestamp bound) to read from local replicas.. Cloud Spanner's multi-region configuration (nam7) includes regional replicas in Europe. By using stale reads with a timestamp bound, read-only transactions can be served from a local replica without incurring cross-region latency, which directly reduces read latency for European users while maintaining acceptable consistency.

Answer

Create a secondary index on the primary key column.

Answer

Increase the number of Spanner nodes to distribute the load.

Answer

Partition the database by region and use directed reads.