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

Question 1

Refer to the exhibit. This DDL is used to create a table in Cloud Spanner. The table will be used for storing user data with high write throughput. What is one performance issue with this table design?

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

Accepted Answer

The primary key is a monotonically increasing integer. Using a monotonically increasing integer as the primary key in Cloud Spanner causes all writes to be directed to a single split (tablet), creating a hotspot that severely limits write throughput. Cloud Spanner distributes splits across nodes based on key ranges, and sequential keys prevent this distribution, leading to performance degradation under high write load.

Answer

The Name column is of type STRING(MAX)

Answer

There are no secondary indexes

Answer

The table is not interleaved with another table

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

Refer to the exhibit. A developer deployed these Firestore security rules. What is a security concern with this configuration?

Accepted Answer

All authenticated users can read all documents. The Firestore security rules shown grant read access to all authenticated users without any document-level or collection-level restrictions. The rule `match /databases/{database}/documents { allow read: if request.auth != null; }` applies to all documents in the database, meaning any authenticated user can read every document, including those they should not have access to. This violates the principle of least privilege and can lead to unauthorized data exposure.

Answer

Only the owner can write to documents

Answer

The rules do not explicitly allow delete operations

Answer

The wildcard path matches all databases

Question 4

A company is running a MySQL database on Cloud SQL and needs to optimize for high random read/write performance. Which storage type should they choose?

Accepted Answer

SSD persistent disk. SSD persistent disk provides consistent low-latency performance for random read/write operations, which is critical for MySQL databases on Cloud SQL. It offers higher IOPS and throughput compared to HDD or balanced persistent disks, making it the optimal choice for high random read/write workloads.

Answer

Local SSD

Answer

Balanced persistent disk

Answer

HDD persistent disk

Question 5

A global e-commerce platform needs a database that supports strong consistency across multiple continents and can handle high write throughput. Which database service should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides globally distributed, strongly consistent relational database service with synchronous replication across regions and continents, supporting external consistency and high write throughput via TrueTime and Paxos-based consensus. This meets the requirement for strong consistency across multiple continents and high write throughput, which is not achievable with traditional single-region or eventually consistent databases.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Firestore

Answer

Cloud Bigtable

Question 6

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 7

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 8

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 9

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 10

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 11

A company collects sensor data from millions of devices globally. Each write is a small record with a device ID and timestamp. They need low write latency and high availability. Which database should they choose?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is a fully managed, scalable NoSQL database designed for large analytical and operational workloads with high throughput and low latency. It supports millions of writes per second from globally distributed devices, provides high availability through replication, and is optimized for time-series data like sensor records with device IDs and timestamps.

Answer

Cloud Firestore

Answer

Cloud Memorystore

Answer

Cloud SQL for MySQL

Question 12

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.. A hash key prefix can distribute writes more evenly across nodes, preventing hot spots that cause high CPU on a single node. Adding nodes may not resolve a hot spot.

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 13

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 14

Refer to the exhibit. A company creates these Cloud Spanner tables. What happens when a customer record is deleted?

Accepted Answer

All orders for that customer are automatically deleted.. Cloud Spanner enforces referential integrity through interleaved tables. When a parent row in the Customers table is deleted, all child rows in the Orders table that are interleaved under that customer are automatically deleted via a cascading delete. This behavior is inherent to the interleaved table structure, not an explicit ON DELETE CASCADE clause.

Answer

The deletion fails if there are orders.

Answer

The orders are deleted only if the order date is older than 30 days.

Answer

The orders remain orphaned.

Question 15

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.