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

Question 1

A company needs a globally distributed relational database that provides strong ACID transactions across regions and a 99.999% availability SLA. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that offers globally distributed ACID transactions and a 99.999% SLA. Cloud SQL and AlloyDB are regional, and Bigtable is not relational.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

AlloyDB

Question 2

An e-commerce platform uses Cloud SQL for PostgreSQL for its inventory database. To meet a higher availability requirement, they decide to enable the HA configuration. What is the expected recovery point objective (RPO) with Cloud SQL HA?

Accepted Answer

0 (zero). Cloud SQL for PostgreSQL HA uses synchronous replication to a standby instance in a different zone within the same region. This ensures that every write transaction is committed on both the primary and standby before acknowledging the client, resulting in zero data loss upon failover. Therefore, the RPO is 0 (zero).

Answer

Depends on the replication lag

Answer

Up to 5 minutes

Answer

Up to 1 minute

Question 3

You are designing a Cloud Spanner schema for a global user profile table with primary key UserId (UUID). Users frequently access their own profiles. Which index design minimizes read latency and avoids hotspotting?

Accepted Answer

Use a composite primary key with a hash prefix (e.g., INT64 hash of UserId) and UserId, and create a global secondary index on UserId with STORING clause. Using UUIDs as primary keys can cause hotspotting on the leading key. A better approach is to use a composite key with a sharding prefix (e.g., hash prefix) to distribute writes, and local indexes for single-region queries are not applicable. Global indexes with STORING clause avoid index joins and reduce read latency.

Answer

Use UUID as primary key and create a global index on UserId

Answer

Use UUID as primary key and rely on interleaved tables

Answer

Use a monotonically increasing integer as primary key

Question 4

A company is migrating an on-premises MySQL OLTP application to Cloud SQL. The application requires 48 vCPU, 312 GB RAM, and 30 TB of storage. Which Cloud SQL edition and tier should they choose?

Accepted Answer

Cloud SQL Enterprise with db-custom-48-307200. Option B is correct because the application requires 48 vCPU and 312 GB RAM, and Cloud SQL Enterprise with db-custom-48-307200 provides exactly 48 vCPU and 307200 MB (300 GB) of RAM, which is the closest available tier that meets or exceeds the memory requirement. Cloud SQL Enterprise supports custom machine types with up to 48 vCPU and 307200 MB RAM, while Enterprise Plus offers only predefined 'perf-optimized' tiers that do not match the exact vCPU/RAM combination needed.

Answer

Cloud SQL Enterprise Plus with db-perf-optimized-48-300

Answer

Cloud SQL Enterprise with db-custom-32-204800

Answer

Cloud SQL Enterprise Plus with db-perf-optimized-64-400

Question 5

You are designing a Bigtable row key for an IoT telemetry application that writes one row per device per minute. Devices are identified by a 12-character device ID. To avoid write hotspots, which row key design is most appropriate?

Accepted Answer

Row key: reversed deviceID + timestamp (e.g., '321cba#2024-03-15T10:30:00'). To distribute writes evenly across Bigtable tablets, the row key should have a non-monotonic prefix. Reversing the device ID (or salting) helps. Field promotion is not needed here.

Answer

Row key: timestamp + deviceID (e.g., '2024-03-15T10:30:00#abc123')

Answer

Row key: deviceID + timestamp (e.g., 'abc123#2024-03-15T10:30:00')

Answer

Row key: hash(deviceID) + deviceID + timestamp

Question 6

A mobile app developer wants to store user preferences and provide offline sync capabilities. The database should be serverless and automatically scale with usage. Which Google Cloud database is best suited?

Accepted Answer

Cloud Firestore. Cloud Firestore is a serverless, NoSQL document database that automatically scales with usage, making it ideal for storing user preferences and providing offline sync capabilities via its built-in offline data persistence and real-time synchronization. It supports mobile and web clients with automatic multi-region replication and strong consistency, aligning perfectly with the requirements of a mobile app developer.

Answer

Cloud Spanner

Answer

Cloud SQL

Answer

Cloud Bigtable

Question 7

A company runs a global stock trading platform that requires strong consistency across regions. They need to read the latest account balance after each trade. Which Cloud Spanner read mode should they use?

Accepted Answer

Strong reads. Strong reads provide the latest committed data with global consistency. Stale reads (bounded staleness) may return older data. External consistency is a Spanner property but not a read mode.

Answer

Read-only transactions with external consistency

Answer

Stale reads with bounded staleness of 10 seconds

Answer

Bounded staleness reads with max staleness of 1 second

Question 8

You are using Cloud Bigtable to store time-series financial market data. To ensure high availability across zones, you configure cluster replication. What is the recommended replication topology for automatic failover?

Accepted Answer

Two clusters in different zones with replication enabled and multi-cluster routing. Bigtable supports replication across clusters in different zones within a region (or across regions). For automatic failover, you should use primary-secondary (also called single-cluster routing with failover) or multi-cluster routing. The recommended HA setup is to have two clusters in different zones with replication enabled.

Answer

Two clusters in the same zone

Answer

Single cluster with multi-node configuration

Answer

Three clusters in three regions

Question 9

A developer is designing a Cloud Spanner schema for a blog application with tables: Posts (PostId, AuthorId, Title, Content) and Comments (CommentId, PostId, Author, Text). They want to store comments near their parent post for efficient retrieval. Which schema design should they use?

Accepted Answer

Use interleaving: define Comments as an interleaved table with Posts, with Comments primary key (PostId, CommentId). Interleaved tables in Cloud Spanner allow storing child rows (Comments) physically near parent rows (Posts) if the child table's primary key includes the parent's primary key. This improves locality and reduces cross-split reads.

Answer

Create a separate Comments table without interleaving and use application-level caching

Answer

Store comments as a JSON array within the Posts table

Answer

Use a foreign key from Comments to Posts and create a global secondary index on PostId

Question 10

An organization wants to cache frequently accessed session data for a web application to reduce database load. They require sub-millisecond latency and support for pub/sub messaging. Which Google Cloud service should they use?

Accepted Answer

Memorystore for Redis. Memorystore for Redis is the correct choice because it provides an in-memory data store with sub-millisecond latency, ideal for caching frequently accessed session data. It also natively supports pub/sub messaging via Redis's built-in PUBLISH/SUBSCRIBE commands, meeting both requirements precisely.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 11

A company wants to run hybrid transactional/analytical processing (HTAP) workloads on a PostgreSQL-compatible database with built-in columnar engine for faster analytical queries. Which Google Cloud database should they choose?

Accepted Answer

AlloyDB. AlloyDB is a PostgreSQL-compatible database service with a built-in columnar engine that accelerates analytical queries by storing data in columns rather than rows, enabling HTAP workloads without needing to move data between separate transactional and analytical systems. Its architecture decouples compute from storage and uses a columnar cache to deliver up to 100x faster analytical queries than standard PostgreSQL, making it the correct choice for HTAP on Google Cloud.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Answer

BigQuery

Question 12

You are tuning a Cloud Bigtable table used for analytics. One column family, 'data', contains both frequently accessed columns (e.g., 'price', 'volume') and rarely accessed columns (e.g., 'raw_json'). To optimize performance and cost, what column family design is recommended?

Accepted Answer

Create two column families: 'core' for frequently accessed columns and 'extended' for rarely accessed columns. Separating columns into different column families based on access patterns is a best practice. Frequently accessed columns should be in one column family (e.g., 'core'), and rarely accessed columns in another (e.g., 'extended'). This allows Bigtable to optimize storage and read performance.

Answer

Use separate tables for each access pattern

Answer

Store rarely accessed columns as a JSON string in a single column

Answer

Place all columns in a single column family for simplicity

Question 13

A company is designing a global inventory system using Cloud Spanner. They need to ensure data consistency across regions while minimizing latency for read-heavy workloads. Which two strategies should they consider? (Choose two.)

Accepted Answer

Use a multi-region configuration with read-write and read replicas. Multi-region configurations with read replicas provide strong consistency for writes and allow stale reads from read replicas for low-latency reads. Using global secondary indexes improves query performance. Interleaved tables help locality but not cross-region.

Answer

Use Cloud SQL for reads and Spanner for writes

Answer

Create interleaved tables for all relationships

Answer

Configure regional configuration with asynchronous replication

Question 14

You need to design a Firestore database for a mobile chat application that supports real-time updates and offline access. The app requires that users can only read and write their own messages. Which three configurations should you implement? (Choose three.)

Accepted Answer

Enable offline persistence in the client SDK. Option A is correct because enabling offline persistence in the Firestore client SDK allows the mobile chat app to cache data locally, ensuring that users can read and write their own messages even when the device is temporarily offline. This is essential for a chat application that must support real-time updates and offline access, as Firestore automatically synchronizes local changes with the server when connectivity is restored.

Answer

Use Cloud SQL instead of Firestore for stronger consistency

Answer

Use Firestore Datastore mode

Question 15

A financial services company uses BigQuery for analytics but needs to store transactional data with strong consistency and sub-millisecond latency. They are considering Cloud SQL, Cloud Spanner, and Firestore. Which two services meet all requirements? (Choose two.)

Accepted Answer

Cloud Spanner. Cloud Spanner is correct because it provides strong consistency (external consistency with TrueTime) and sub-millisecond latency for transactional workloads, making it ideal for financial services that require ACID transactions across globally distributed data. It combines the benefits of relational database structure with horizontal scalability, meeting both consistency and latency requirements.

Answer

Cloud SQL

Answer

Bigtable

Answer

Memorystore

Answer

Firestore