PCDE Design innovative, scalable, and highly available cloud database solutions Practice Test 5 — 15 Questions

Question 1

A company is evaluating Google Cloud databases for a new application that requires: (1) strong global consistency across multiple regions, (2) the ability to run complex analytical queries on the same data as the transactional workload, and (3) high write throughput. Which TWO databases should they consider?

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

Accepted Answer

Cloud Spanner. Cloud Spanner provides strong global consistency across multiple regions via multi-region configurations using synchronous replication, high write throughput (up to 20,000 writes per second per node, scalable), and support for transactional workloads. However, it does not natively handle complex analytical queries on the same data; for HTAP, a separate analytics engine like BigQuery or a dedicated HTAP database such as AlloyDB would be needed. AlloyDB offers HTAP and high write throughput, but its cross-region replication is eventually consistent, failing the strong global consistency requirement. BigQuery lacks transactional strong consistency and high write throughput. Bigtable provides eventual consistency and no SQL analytical queries. Firestore offers strong consistency only within a single region and no complex analytical queries. Thus, only Cloud Spanner fully meets the three requirements.

Answer

Bigtable

Answer

AlloyDB

Answer

BigQuery

Answer

Firestore

Question 2

You are migrating a 5 TB MySQL database to Cloud SQL. The migration must have minimal downtime. Which THREE steps should you include in your migration plan?

Accepted Answer

Configure continuous replication between on-premises and Cloud SQL.. Option A is correct because configuring continuous replication (for example, MySQL binary log replication or Cloud SQL's native replication) keeps the on-premises source and Cloud SQL target synchronized, so changes made during the migration are captured and downtime is limited to the final cutover. Option B is correct because Database Migration Service (DMS) is Google Cloud's managed service specifically designed to migrate MySQL databases with minimal downtime, handling initial snapshot plus ongoing replication and supporting a controlled promotion/cutover. Option D is correct because the migration must end with a controlled cutover: stopping writes to the source, ensuring the replica has caught up, and promoting the Cloud SQL instance to become the primary writable database, which is the step that actually completes the migration with minimal downtime. Option C is not the best fit because a full backup and restore alone is a one-time copy that does not keep the target current, so any changes after the backup would be lost and downtime would be significant. Option E is not appropriate because mysqldump is a logical export tool that is slow and impractical for a 5 TB database and, by itself, provides no continuous synchronization or minimal-downtime cutover.

Answer

Take a full backup and restore to Cloud SQL.

Answer

Use mysqldump to export the database.

Question 3

You are designing a Spanner schema for a financial application that stores transactions for user accounts. To avoid hotspots and optimize performance, which TWO practices should you follow?

Accepted Answer

Use the STORING clause in secondary indexes to include frequently accessed columns.. Option B is correct because using the STORING clause in secondary indexes lets Spanner store frequently accessed columns directly in the index, so queries that need those columns can be satisfied by an index-only scan and avoid extra lookups to the base table, improving performance. Option C is correct because a UUID primary key distributes inserts randomly across the key space, preventing the sequential write hotspot that occurs when new rows are appended to one end of a key range. Option A is not appropriate because a single non-interleaved table does not by itself address hotspotting and ignores Spanner's interleaving feature for co-locating related child rows. Option D is wrong because a monotonically increasing integer primary key concentrates new writes at the high end of the key range, creating a hotspot. Option E is wrong because omitting the STORING clause forgoes the index-only scan benefit and forces base-table lookups for covered columns.

Answer

Store all data in a single table without interleaving.

Answer

Use a monotonically increasing integer as the primary key.

Answer

Create secondary indexes without the STORING clause.

Question 4

A company is migrating an on-premises OLTP application to Cloud SQL. The current database runs on a server with 64 GB RAM and 16 vCPUs, and handles thousands of write-heavy transactions per second. The team wants to size the Cloud SQL instance appropriately. Which sizing approach is correct for estimating max_connections?

Accepted Answer

Set max_connections = RAM_MB / 16.. Cloud SQL for MySQL and PostgreSQL uses the formula `max_connections = RAM_MB / 16` as a default heuristic to prevent memory exhaustion from connection overhead. Each connection consumes approximately 16 MB of RAM for buffers and state, so dividing total RAM by 16 provides a safe upper bound. This approach directly ties connection limits to available memory, which is critical for write-heavy OLTP workloads that may open many concurrent connections.

Answer

Set max_connections = 8 * vCPU_count.

Answer

Set max_connections = 10 * IOPS.

Answer

Set max_connections = vCPU_count * 1000.

Question 5

A company uses Bigtable for time-series analytics and needs to query the most recent data points first. The row key currently consists of a user ID followed by a timestamp (e.g., user123#2024-01-15T10:30:00). However, frequent queries filter by time range across all users. Which row key design change would optimize query performance for this access pattern?

Accepted Answer

Reverse the timestamp and place it at the beginning of the row key (e.g., 2024-01-15T10:30:00_rev#user123).. Reversing the timestamp and placing it at the beginning of the row key ensures that the most recent data points are stored first in lexicographic order. Bigtable stores rows sorted by row key, so queries filtering by a time range across all users can now scan a contiguous range of rows without needing to skip over user ID prefixes. This design avoids the hotspotting and inefficient scans that occur when the timestamp is not the leading part of the key for time-range queries.

Answer

Use the user ID as the only row key and store timestamps as column qualifiers.

Answer

Use a monotonically increasing integer as the row key.

Answer

Use a hash of the user ID as a prefix (salting) to distribute writes evenly.

Question 6

A company runs a MySQL database on Cloud SQL for an e-commerce platform. They need to add a new column to a table with millions of rows without causing downtime. What is the recommended approach?

Accepted Answer

Use a tool like pt-online-schema-change to perform the change with minimal impact.. Pt-online-schema-change (or gh-ost) creates a shadow table with the new schema, incrementally copies rows using triggers or binary log replay, and then atomically swaps the tables. This avoids holding any locks on the original table, preventing downtime for an e-commerce platform with millions of rows. Cloud SQL's InnoDB does not support true online DDL for all ALTER TABLE operations, especially on large tables, making a dedicated online schema change tool the safest approach.

Answer

Use 'ALTER TABLE ... ADD COLUMN ... ALGORITHM=INPLACE, LOCK=NONE'.

Answer

Create a new table with the column, copy data manually, then swap tables.

Answer

Use 'ALTER TABLE ... ADD COLUMN' directly; Cloud SQL handles it online.

Question 7

A company runs a high-traffic event logging system on Cloud Bigtable. Each event has a timestamp, severity level, and a message. Queries often filter by severity and time range. To optimize for this access pattern, which field should be placed first in the row key?

Accepted Answer

Severity level. Severity level should be placed first in the row key because Cloud Bigtable stores rows in lexicographic order by row key. By placing severity first, all rows with the same severity are stored contiguously, making range scans over time within a severity highly efficient. This directly supports the query pattern of filtering by severity and time range without requiring a full table scan.

Answer

Random salt

Answer

Timestamp (reversed)

Answer

Message ID

Question 8

A company is migrating a monolithic application to a microservices architecture and plans to use multiple Google Cloud databases. The application has the following workloads: (1) user profiles with high read/write concurrency, (2) product catalog with complex queries, (3) session data that requires low-latency access. Which three Google Cloud databases should be used? (Choose three.)

Accepted Answer

Firestore. Firestore (A) is correct because it is a fully managed NoSQL document database that scales horizontally and handles high read/write concurrency well, making it a strong fit for user profiles with heavy concurrent access. Cloud SQL (B) is correct because it provides a fully managed relational database (MySQL, PostgreSQL, or SQL Server) that supports complex SQL queries and joins, which the product catalog workload requires. Memorystore (Redis) (D) is correct because it is an in-memory data store delivering sub-millisecond latency, ideal for session data that needs low-latency access. Cloud Bigtable (C) is not appropriate here because it is a wide-column NoSQL database optimized for large analytical and time-series workloads, not complex relational queries or session management. Cloud Spanner (E) is not selected because, while it is a globally distributed relational database, it is typically overkill and more costly for these specific workloads compared to the chosen options.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 9

An engineer is designing a Bigtable schema for a weather data application. The data is written by thousands of sensors, each generating a reading every minute. Queries typically retrieve all readings for a sensor in a time range. The row key should be designed to avoid hotspots and support these queries. Which two row key components are recommended? (Choose two.)

Accepted Answer

Reversed timestamp. Option E is correct because prefixing the row key with a hash of the sensor ID distributes writes across multiple tablets, preventing hotspots when thousands of sensors write concurrently. Option C is correct because appending a reversed timestamp (or using it within the key) allows the most recent readings to sort first while still enabling efficient range scans for a sensor's time-range queries. Together, a hashed prefix plus reversed timestamp balances write distribution and read locality. Option A is wrong because using the raw sensor ID alone concentrates all writes for a sensor on one tablet and can create hotspots. Option B is wrong because sensor location as a column does not affect row key distribution or query efficiency for time-range reads. Option D is wrong because a natural-order timestamp causes sequential writes to the same tablet region, producing hotspots.

Answer

Sensor ID (raw) as the only key

Answer

Sensor location as a column

Answer

Timestamp in natural order

Question 10

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is the correct choice because it is a fully managed, scalable NoSQL database designed for large analytical and operational workloads, such as time-series IoT sensor data. It supports petabyte-scale storage, single-digit millisecond latency for reads and writes, and millions of operations per second using a simple key-value model with timestamps, making it ideal for high-throughput, low-latency time-series data.

Answer

BigQuery

Answer

Firestore

Answer

Cloud Spanner

Question 11

A financial services company is migrating from an on-premises Oracle RAC database to Cloud Spanner. The current application uses sequences to generate globally unique IDs for transactions. To avoid creating hotspots in Spanner, the database architect recommends using a different primary key strategy. Which primary key design is most appropriate for Spanner to avoid hotspots?

Accepted Answer

Use a bit-reversed sequential key generated by the application.. Bit-reversed sequential keys distribute writes evenly across Cloud Spanner's split boundaries, preventing hotspots. Spanner uses key-range-based sharding, so monotonically increasing keys (like Oracle sequences) cause all new writes to hit a single split, leading to contention. Bit-reversal spreads sequential values across the key space, ensuring balanced write distribution.

Answer

Use a UUID string as the primary key.

Answer

Continue using sequential IDs from Oracle sequences to maintain consistency.

Answer

Use a composite key with a hash prefix derived from the transaction timestamp.

Question 12

A data pipeline writes 10 TB of streaming data daily into Bigtable. The row key is based on the device ID and timestamp in reverse order. Recent data is queried most frequently. Which three design choices optimize performance and cost? (Choose THREE.)

Accepted Answer

Use a single cluster with SSD storage.. Option B is correct because a single cluster with SSD storage provides the low-latency performance Bigtable needs for frequently queried recent data while avoiding the cost of multiple clusters; SSD is recommended for latency-sensitive workloads. Option D is correct because pre-splitting the table distributes the initial write load across multiple tablets, preventing hotspotting when 10 TB of streaming data begins loading before Bigtable's automatic splitting can keep up. Option E is correct because enabling compression on column families reduces the storage footprint of the 10 TB daily writes, directly lowering storage cost and often improving read efficiency. Option A is not appropriate because HDD storage is cheaper per GB but has higher latency and lower throughput, which conflicts with the requirement to query recent data most frequently. Option C is not a valid design choice here because the scenario already states the row key uses reverse timestamp, so it is a restatement of the existing design rather than an additional optimization.

Answer

Use HDD storage to reduce cost per GB.

Answer

Use reverse timestamp to make recent data first.

Question 13

A Cloud Spanner instance has a single node and is experiencing high write contention. The workload is 3000 writes per second with 2 KB mutations. Which two changes would improve write throughput? (Choose TWO.)

Accepted Answer

Redesign the primary key to distribute writes evenly across splits.. Option A is correct because Cloud Spanner write throughput is limited by the hottest split; a monotonically increasing or otherwise skewed primary key concentrates writes on the last split, causing contention, so redesigning the key (for example, using a hash prefix or bit-reversed sequence) distributes writes across many splits and allows parallelism. Option C is correct because a single-node Cloud Spanner instance provides roughly 2,000 writes per second (at 1 KB mutations), and adding a second node doubles the write capacity to about 4,000 writes per second, which covers the 3,000 writes/sec workload. Option B is wrong because increasing mutation size to 10 KB increases the write cost per mutation (Cloud Spanner counts mutations in 1 KB units), so it would reduce, not improve, throughput. Option D is wrong because a monotonically increasing primary key is exactly what causes hotspotting and write contention, not a remedy. Option E is wrong because splitting into smaller tables does not reduce contention on the hot key range and adds complexity without increasing node-level write capacity.

Answer

Increase the mutation size to 10 KB to reduce number of writes.

Answer

Use a monotonically increasing primary key to improve index performance.

Answer

Split the table into multiple smaller tables.

Question 14

An organization uses Cloud SQL for PostgreSQL with read replicas to offload reporting queries. During peak, the primary instance's CPU spikes to 90%. The team suspects the read replica is falling behind. Which two settings should they check to diagnose replication lag? (Choose TWO.)

Accepted Answer

pg_stat_replication view on the primary.. Option A is correct because querying the pg_stat_replication view on the primary instance exposes per-standby replication state, including sent_lsn, write_lsn, flush_lsn, replay_lsn, and the write/flush/replay lag intervals, which directly reveal how far a replica is behind. Option C is correct because Cloud SQL exports a built-in 'Replica lag' metric to Cloud Monitoring (based on the replica's replay position versus the primary's WAL position), giving a time-series view of lag during the peak window without needing to query the database. Option B is wrong because pg_replication_slots exists on the primary (or on the replica only if it has its own downstream subscribers) and tracks WAL retention for slots, not the replica's replay progress. Option D is wrong because max_wal_size controls WAL segment checkpointing behavior, not replication lag measurement. Option E is wrong because 'SHOW REPLICATION STATUS' is not a valid PostgreSQL command; replication status is obtained via pg_stat_replication, pg_stat_wal_receiver, or monitoring metrics.

Answer

pg_replication_slots view on the replica.

Answer

max_wal_size configuration parameter.

Answer

SHOW REPLICATION STATUS command.

Question 15

A company wants to migrate from a self-managed MySQL database to a fully managed GCP service. They need high availability with automatic failover, automated backups, and read replicas for scaling read traffic. Which two Google Cloud services meet these requirements? (Choose TWO.)

Accepted Answer

Cloud Spanner. Cloud SQL for MySQL (D) is correct because it is a fully managed MySQL service that supports high availability configurations with automatic failover to a standby instance, automated backups, and read replicas for scaling read traffic, making it a direct migration target for a self-managed MySQL database. Cloud Spanner (B) is also correct because it is a fully managed, horizontally scalable relational database that provides high availability with automatic failover across regions, automated backups, and read scaling through its distributed architecture, satisfying the stated requirements even though it is not MySQL-compatible. Firestore (A) is a NoSQL document database and does not provide MySQL compatibility, read replicas, or the relational features required. Cloud Bigtable (C) is a NoSQL wide-column store designed for high-throughput analytical and operational workloads, not for MySQL migration or read replicas. BigQuery (E) is a serverless data warehouse for analytics and does not support transactional MySQL workloads, automatic failover, or read replicas in the required sense.

Answer

Firestore

Answer

Cloud Bigtable

Answer

BigQuery