PCD Design Scalable and Highly Available Cloud Database Solutions Practice Test 4 — 15 Questions

Question 1

A company is migrating an on-premises PostgreSQL OLTP application to Google Cloud. They need a managed database with high availability, automatic failover, and zero RPO. Which database service should they choose?

Accepted Answer

Cloud SQL for PostgreSQL with HA configuration. Cloud SQL for PostgreSQL supports HA instances with synchronous replication to a standby in the same region, automatic failover, and zero RPO. It is the best choice for a lift-and-shift of a PostgreSQL OLTP workload.

Answer

Firestore

Answer

Bigtable

Answer

Cloud Spanner

Question 2

A global e-commerce platform requires a database that can handle millions of concurrent users, provide strong transactional consistency across regions, and achieve 99.999% availability SLA. Which database is the best fit?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that offers global distribution, horizontal scaling, ACID transactions across regions, and a 99.999% SLA.

Answer

Cloud SQL with cross-region replication

Answer

Firestore in Native mode

Answer

Bigtable

Question 3

A financial trading application uses Cloud Spanner for order processing. To reduce latency for read-heavy operations, the team wants to allow stale reads with a bounded staleness of 10 seconds. Which Spanner API or method should they use to achieve this?

Accepted Answer

Use the `timestamp_bound` parameter with `max_staleness` set to 10 seconds. Spanner supports stale reads (bounded staleness) by setting a read timestamp. The read timestamp can be set to a past time within the maximum staleness. The `max_staleness` option in the client libraries or the `read_timestamp` parameter in gRPC API allow bounded staleness.

Answer

Set the session to read-only mode

Answer

Configure an index with the `STORING` clause to avoid index joins

Answer

Use mutations API instead of DML for writes

Question 4

A team is designing a Cloud Bigtable schema for a time-series application that records sensor readings every second. They need to avoid write hotspots. Which row key design is most appropriate?

Accepted Answer

Use a composite key with sensor ID followed by reversed timestamp. To avoid hotspots, row keys should be designed to distribute writes across tablets. Reversing the timestamp (e.g., `[timestamp reversed]#[sensor_id]`) spreads new writes across different tablets, rather than appending to the same tablet.

Answer

Use a salted timestamp where salt is the sensor ID modulo a small number

Answer

Use a UUID as the row key

Answer

Use a monotonically increasing timestamp as the row key

Question 5

A mobile app startup needs a serverless, scalable NoSQL database that supports offline sync and real-time updates. Which database should they choose?

Accepted Answer

Firestore in Native mode. Firestore (Native mode) provides serverless NoSQL document storage, offline sync for mobile clients, and real-time listeners for updates. It is the ideal choice for mobile and web apps.

Answer

Bigtable

Answer

Cloud SQL

Answer

Memorystore

Question 6

A company wants to run HTAP workloads on a PostgreSQL-compatible database with a built-in columnar engine for faster analytics on transactional data. Which Google Cloud database should they choose?

Accepted Answer

AlloyDB. AlloyDB is a PostgreSQL-compatible database that includes a columnar engine for analytics, providing up to 4x faster OLTP than Cloud SQL PostgreSQL and is designed for hybrid HTAP workloads.

Answer

Cloud Spanner

Answer

Cloud SQL for PostgreSQL

Answer

BigQuery

Question 7

A Cloud Spanner instance is experiencing high latency for global secondary index queries. The team notices that queries on the secondary index are performing full table scans instead of index scans. What is the most likely cause?

Accepted Answer

The index is a global index without the STORING clause, causing index join. In Cloud Spanner, secondary indexes can be local (interleaved with parent) or global (non-interleaved). If an index is defined as a global index, queries might still need to join with the base table unless the index includes all needed columns. Without the `STORING` clause, Spanner must fetch additional columns from the base table, causing the query to scan the base table.

Answer

The index is a local index without the STORING clause

Answer

The Spanner instance is under-provisioned

Answer

The index is defined on a composite key that is not selective

Question 8

Which Google Cloud database service is designed as a fully managed, in-memory data store for caching, session management, and pub/sub messaging?

Accepted Answer

Memorystore. Memorystore provides managed Redis and Memcached for caching, session storage, and pub/sub. It is the correct service for these use cases.

Answer

Cloud SQL

Answer

Firestore

Answer

Bigtable

Question 9

A team is designing a Cloud Spanner schema for a hierarchical product catalog with categories and products. They want to ensure that products are stored in the same split as their parent category for low-latency queries. Which feature should they use?

Accepted Answer

Use interleaved tables with the products table interleaved in the categories table. Cloud Spanner interleaved tables allow storing child rows (products) with their parent row (category) in the same split, providing low-latency joins and locality. The parent-child relationship is defined using the `INTERLEAVE IN PARENT` clause.

Answer

Use a composite primary key with category ID as the first part, but no interleaving

Answer

Use Cloud Spanner's commit timestamp feature

Answer

Define a global secondary index on the category ID

Question 10

A company is using Cloud Bigtable for an ad-tech application with billions of ad impressions per day. They need to perform point reads on individual rows as well as scans over time ranges. Which column family design is recommended?

Accepted Answer

Separate frequently accessed columns into one column family and infrequently accessed into another. To optimize performance, frequently accessed columns (e.g., for point reads) should be in a separate column family from infrequently accessed columns (e.g., audit data). This reduces the amount of data scanned during scans and improves cache efficiency.

Answer

Use multiple tables for different access patterns

Answer

Store each column as a separate column family

Answer

Store all columns in a single column family for simplicity

Question 11

A Cloud SQL for MySQL instance configured with HA is experiencing a failover event. The application team reports that the database became unavailable for about 60 seconds during the failover. They want to minimize future downtime. What should they do?

Accepted Answer

Use Cloud SQL Proxy or configure the application to retry with a static IP. Cloud SQL HA failover typically takes less than 60 seconds. The downtime could be due to DNS propagation or connection pooling issues. Using a proxy like Cloud SQL Auth proxy or a load balancer with a static IP can reduce failover time by avoiding DNS changes.

Answer

Enable automated backups and point-in-time recovery

Answer

Increase the instance size to reduce failover time

Answer

Switch to a cross-region replica

Question 12

A gaming company uses Cloud Spanner for its global leaderboard. The leaderboard is updated frequently by millions of users. They notice that write latency spikes during peak hours due to hotspotting on the leaderboard table. Which schema change would best mitigate this?

Accepted Answer

Prefix the primary key with a server ID or hash of the user ID. Using a composite primary key with a hash prefix (e.g., server_id) as the first part distributes writes across splits, reducing hotspots. A monotonically increasing key (like rank) causes hotspotting.

Answer

Use UUID as the primary key

Answer

Add a secondary index on the rank column

Answer

Enable leaderboard caching with Memorystore

Question 13

A social media company is building a real-time analytics platform to process user activity streams. They need to ingest millions of events per second, store them for 30 days, and query with sub-10ms latency. Which TWO Google Cloud services should they use together? (Choose 2)

Accepted Answer

Cloud Pub/Sub. Cloud Pub/Sub is ideal for ingesting high-throughput event streams, and Cloud Bigtable provides low-latency storage and querying. Together they meet the requirements.

Answer

Firestore

Answer

Cloud Spanner

Answer

BigQuery

Question 14

A company is designing a multi-region Cloud Spanner deployment for a global user base. They want to ensure the lowest possible read latency for users in North America and Europe while maintaining strong consistency. Which THREE configuration choices should they make? (Choose 3)

Accepted Answer

Use interleaved tables to localize related data. A multi-region configuration with primary regions in North America and Europe provides leader placement for writes and read replicas for low-latency reads. Adding read-only replicas in each region reduces read latency. Placing read-write replicas in both regions ensures strong consistency locally.

Answer

Configure the application to use stale reads with 1-second bounded staleness

Answer

Use a single-region configuration and rely on Cloud CDN to cache read results

Question 15

A company is migrating an on-premises Oracle database to Google Cloud. They need a managed relational database with high availability, but they are not ready to redesign their schema for sharding. They also want to reduce licensing costs. Which TWO Google Cloud database services should they consider? (Choose 2)

Accepted Answer

Cloud SQL for SQL Server. Cloud SQL supports managed MySQL, PostgreSQL, and SQL Server, suitable for lift-and-shift. AlloyDB is PostgreSQL-compatible and offers high performance with a columnar engine. Both are managed and reduce licensing costs compared to Oracle.

Answer

Firestore

Answer

Cloud Spanner

Answer

Bigtable