PCD Design Scalable and Highly Available Cloud Database Solutions — Timed 15 Questions

Question 1

A company is migrating an on-premises MySQL 8.0 OLTP application to Google Cloud. The application requires high availability with automatic failover and zero RPO. Which Google Cloud database and configuration should they use?

Accepted Answer

Cloud SQL for MySQL with HA configuration. Cloud SQL for MySQL with HA configuration provides synchronous replication to a standby instance in the same region, automatic failover, and zero RPO.

Answer

Cloud Spanner with multi-region configuration

Answer

Cloud SQL for MySQL with read replicas

Answer

AlloyDB for PostgreSQL with HA

Question 2

An e-commerce company is designing a global product inventory system that requires strong consistency across regions, horizontal scaling for millions of users, and 99.999% availability. Which Google Cloud database meets all these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that provides globally distributed ACID transactions with strong consistency, horizontal scaling, and 99.999% SLA.

Answer

Firestore

Answer

Cloud Bigtable

Answer

Cloud SQL for PostgreSQL

Question 3

A company is designing a Bigtable schema for time-series data from millions of devices. Each device sends a reading every minute. The row key is currently 'device_id#timestamp'. The engineering team notices hot spots on a few popular devices. Which row key design change would BEST distribute writes across the cluster?

Accepted Answer

Append a random salt prefix to the row key. Salted keys (prepending a hash prefix) distribute writes across tablet servers. Reversing the timestamp or field promotion alone may not solve hotspots from popular device IDs.

Answer

Promote device ID to column family

Answer

Use reversed domain for device IDs

Answer

Use only timestamp as row key

Question 4

A mobile app developer wants a serverless NoSQL database that automatically syncs data offline and provides security rules for access control. Which Google Cloud database should they choose?

Accepted Answer

Firestore. Firestore is the correct choice: it is serverless, document-based, provides offline sync for mobile apps, and has Security Rules for access control.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Answer

Memorystore for Redis

Question 5

A financial services company requires a relational database that can handle hybrid transactional/analytical processing (HTAP) workloads with sub-millisecond transactional latency and real-time analytics without ETL. Which Google Cloud database is BEST suited?

Accepted Answer

AlloyDB for PostgreSQL. AlloyDB is PostgreSQL-compatible and includes a columnar engine for in-database ML inference and HTAP workloads. It is optimized for OLTP and real-time analytics without ETL.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Answer

BigQuery

Question 6

A company is using Cloud Spanner for a global user database. They need to read recent orders for a user with strong consistency. The application currently uses stale reads with a max staleness of 10 seconds. Some users see inconsistent data. What is the BEST approach to guarantee strong consistency without significantly impacting latency?

Accepted Answer

Use strong reads with a read timestamp. Strong reads with a read timestamp ensure the most recent data. Bounded staleness is a weaker consistency model. Using DML or mutations does not affect read consistency.

Answer

Use global secondary indexes with STORING clause

Answer

Use mutations API instead of DML

Answer

Reduce max staleness to 1 second

Question 7

An engineer is configuring Cloud SQL for PostgreSQL with HA. They notice that after a failover, the original primary instance does not automatically resume as a standby. What is the likely cause?

Accepted Answer

The original primary instance's disk is now in read-only mode. In Cloud SQL HA, after a failover, the original primary becomes a new standby using the same underlying disk. It does not create a new instance automatically. The disk is preserved, and the standby is recreated.

Answer

The standby instance was not configured in a different zone

Answer

The HA instance is using local SSD which is not durable

Answer

The original primary instance is deleted after failover

Question 8

Which Google Cloud database service should be used to implement a low-latency session store for a web application that requires pub/sub capabilities and rate limiting?

Accepted Answer

Memorystore for Redis. Memorystore for Redis provides low-latency in-memory data storage with support for pub/sub and rate limiting patterns. It is ideal for session stores.

Answer

Firestore

Answer

Cloud Spanner

Answer

Memorystore for Memcached

Question 9

A company is migrating a Cassandra-based time-series application to Google Cloud. They need to maintain single-digit millisecond write latency at petabyte scale. Which Google Cloud database is the BEST replacement?

Accepted Answer

Cloud Bigtable. Bigtable is the closest equivalent to Cassandra for time-series data, offering similar performance and scalability. Cloud Spanner is relational, and Firestore is for document data.

Answer

Cloud Spanner

Answer

Firestore

Answer

BigQuery

Question 10

An engineer is designing a Cloud Spanner schema for a social media application. The Users table and the Posts table have a parent-child relationship. To optimize read performance for fetching all posts of a user, which schema design approach should be used?

Accepted Answer

Use interleaved tables with Users as parent and Posts as child. Interleaved tables store child rows (Posts) with their parent row (User) in the same split, enabling locality and faster joins. Global or local indexes are separate structures.

Answer

Denormalize by storing post data as a repeated field in Users table

Answer

Use a local secondary index on user_id in the Posts table

Answer

Create a global secondary index on user_id in the Posts table

Question 11

A company is using Cloud Bigtable for ad tech data. They have a single table with a column family containing frequently accessed columns and another with rarely accessed columns. To optimize performance, what column family design change should they implement?

Accepted Answer

Move frequently accessed columns to a separate column family. Separating frequently accessed columns into their own column family reduces the amount of data read per request, improving performance.

Answer

Move rarely accessed columns to a different table

Answer

Combine all columns into one column family

Answer

Use a single column family and set garbage collection to delete rarely accessed columns

Question 12

Which Google Cloud database is a serverless data warehouse designed for analytical queries on petabyte-scale data, with SQL interface and built-in machine learning capabilities?

Accepted Answer

BigQuery. BigQuery is the serverless, highly scalable data warehouse for analytics. It supports SQL and BigQuery ML for in-database ML.

Answer

AlloyDB

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 13

A company is designing a disaster recovery strategy for a global e-commerce platform using Cloud Spanner. They need to ensure that the system can survive a regional outage with minimal data loss and automatic failover. Which TWO configurations should they choose? (Choose 2.)

Accepted Answer

Multi-region configuration with read-write replicas in at least two regions. A multi-region configuration with read-write replicas provides automatic failover and strong consistency across regions. Backup and restore is manual and not automatic.

Answer

Regional configuration with a single replica

Answer

Set up scheduled exports and imports using Cloud Storage

Answer

Use point-in-time recovery with daily backups

Question 14

A company is using Cloud Bigtable for IoT data ingestion. They are experiencing high latency and write throttling. The current row key format is 'device_id#timestamp'. Which THREE actions can help distribute the write load and reduce hotspots? (Choose 3.)

Accepted Answer

Reverse the order of row key components: timestamp#device_id. Salted keys, field promotion, and reversed domain are common patterns to distribute writes. Using a single node is not a solution; it worsens performance.

Answer

Increase the number of Bigtable nodes

Answer

Use a single column family to reduce data size

Question 15

A company is building a real-time leaderboard for an online game using Memorystore for Redis. They need to support millions of concurrent users, update scores frequently, and retrieve top 100 players. Which TWO features should they use? (Choose 2.)

Accepted Answer

Sorted sets with ZADD and ZREVRANGE commands. Sorted sets are ideal for leaderboards. Redis Cluster provides horizontal scaling for high throughput. Pub/sub is for messaging, not leaderboard. Replication provides HA but not scaling.

Answer

Pub/sub channels for score updates

Answer

Keyspace notifications to detect score changes

Answer

Redis Sentinel for automatic failover