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

Question 1

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 designed for petabyte-scale, low-latency, high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is for analytics (seconds-to-minutes latency), Cloud SQL for OLTP (limited throughput), Firestore for document data.

Answer

Firestore

Answer

BigQuery

Answer

Cloud Spanner

Question 2

You are designing a global user-facing application that requires strong consistency, horizontal scalability, and 99.999% availability across multiple continents. Which database service should you choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database service that provides strong consistency, horizontal scalability, and 99.999% availability across multiple continents. It uses synchronous replication and the TrueTime API to deliver external consistency across globally distributed nodes, making it ideal for global user-facing applications that require ACID transactions at scale.

Answer

Cloud SQL

Answer

Firestore

Answer

Bigtable

Question 3

A Cloud Spanner table uses a UUID as the primary key. The application is experiencing high write latency and hotspotting. Which design change would BEST resolve the issue?

Accepted Answer

Use a composite primary key with a shard prefix (e.g., hash of UUID). Option B is correct because adding a shard prefix (e.g., a hash of the UUID) as the first part of a composite primary key distributes writes evenly across Cloud Spanner's splits, eliminating hotspotting. UUIDs alone cause hotspotting because they are randomly distributed but still lead to a single split being overwhelmed if the table is small or the UUIDs are inserted in order; a hash prefix ensures that consecutive writes target different splits, reducing write latency.

Answer

Switch to a monotonically increasing integer key

Answer

Add a secondary index on the UUID column

Answer

Enable interleaved tables

Question 4

Which Google Cloud database is serverless, supports offline data synchronization for mobile applications, and provides real-time updates via listeners?

Accepted Answer

Firestore. Firestore is a serverless NoSQL document database designed for mobile and web apps. It provides offline persistence, real-time listeners, and Security Rules for access control. Cloud SQL and Spanner are not serverless; Bigtable requires cluster management.

Answer

Cloud SQL

Answer

Cloud Spanner

Answer

Bigtable

Question 5

A financial application requires ACID transactions across multiple rows and tables in a single region with less than 10ms latency and strong consistency. The application must handle up to 10,000 transactions per second. Which database should you use?

Accepted Answer

Cloud Spanner. Cloud Spanner provides ACID transactions, strong consistency, and horizontal scalability, with latency in the single-digit milliseconds for regional instances. It can handle 10K TPS easily. Cloud SQL is ACID but limited in scaling (read replicas, but not horizontal write scaling). Firestore is NoSQL with limited transaction scope. Bigtable is not ACID.

Answer

Cloud SQL

Answer

Firestore

Answer

Bigtable

Question 6

You are migrating an on-premises PostgreSQL database (OLTP, 200 GB) to Google Cloud. The database requires high availability with automatic failover and zero RPO. Which configuration should you use?

Accepted Answer

Cloud SQL HA instance. Cloud SQL HA instance provides synchronous replication to a standby in a different zone within the same region, ensuring automatic failover with zero RPO because no committed transaction is lost. This meets the requirement for high availability and zero data loss for an OLTP PostgreSQL database of this size.

Answer

AlloyDB with HA configuration

Answer

Cloud SQL single zone instance with an external replica

Answer

Cloud SQL instance with cross-region read replica

Question 7

Your Bigtable instance is experiencing high latency on read queries that scan a large range of rows. The row keys are timestamps in descending order (e.g., '2024-01-01#user123'). What is the most likely cause?

Accepted Answer

Row keys cause hotspotting because of descending timestamps. Descending timestamps as row keys cause hotspotting because new writes (which are always the most recent timestamp) are concentrated on a single tablet server node, creating a 'hot node' that also serves read queries for that range. Bigtable splits and load-balances by row key prefix, so sequential descending keys like '2024-01-01#...', '2024-01-02#...' are written to the same tablet, leading to uneven load and high read latency for scans over large ranges.

Answer

Row keys are too short

Answer

There are too many column families

Answer

Column family design is incorrect

Question 8

Which Google Cloud service would you use to cache session data for a web application to reduce database load? The cache must support pub/sub messaging and have sub-millisecond latency.

Accepted Answer

Memorystore for Redis. Memorystore for Redis is ideal for caching, session storage, and pub/sub messaging with sub-millisecond latency. Memorystore for Memcached is also for caching but does not support pub/sub. Firestore and Bigtable are not caching solutions.

Answer

Memorystore for Memcached

Answer

Bigtable

Answer

Firestore

Question 9

You need to design a database for a global inventory management system that requires strong consistency across regions and can handle millions of updates per day. The system must maintain ACID transactions across multiple warehouses. Which database service and configuration would you choose?

Accepted Answer

Cloud Spanner multi-region instance. Cloud Spanner is the only Google Cloud database service that provides ACID transactions across regions with strong external consistency, making it ideal for a global inventory system requiring millions of updates per day. Its multi-region instance configuration uses synchronous replication and the TrueTime API to ensure linearizable transactions, meeting the need for strong consistency across warehouses.

Answer

Bigtable with cluster replication

Answer

Firestore in multi-region mode

Answer

Cloud SQL with cross-region replicas

Question 10

A company runs an OLTP workload on Cloud SQL for PostgreSQL. They need to run complex analytical queries on the same data without impacting transaction performance. Which service should they add?

Accepted Answer

Migrate to AlloyDB. AlloyDB is a PostgreSQL-compatible database designed for high-performance transactional and analytical workloads. It separates compute from storage and uses a columnar engine for analytics, allowing complex queries to run without degrading OLTP performance. This makes it the correct choice for running analytical queries on the same data without impacting transaction latency.

Answer

Set up a Cloud SQL read replica for analytics

Answer

Export data to BigQuery periodically

Answer

Use Cloud Spanner

Question 11

You are using Cloud Spanner and need to read data that is consistent as of a specific point in time, but you can tolerate staleness up to 10 seconds. Which read type should you use to achieve the best performance?

Accepted Answer

Stale read with a staleness bound of 10 seconds. Option D is correct because a stale read with a staleness bound of 10 seconds allows Cloud Spanner to serve the read from any replica that has data within that staleness window, avoiding the latency of a strong read that must contact the leader. This provides the best performance while still guaranteeing consistency as of a point in time up to 10 seconds ago, meeting the requirement of tolerating staleness up to 10 seconds.

Answer

Partitioned read

Answer

Read with a timestamp bound exactly 10 seconds in the past

Answer

Strong read

Question 12

Your mobile app uses Firestore to store user profiles. You need to restrict access so that users can only read/write their own data. Which Firestore feature should you use?

Accepted Answer

Security Rules. Firestore Security Rules are the correct choice because they allow you to define granular access controls based on user identity. By using the `request.auth.uid` variable in your rules, you can restrict read and write operations to documents where the user's UID matches a field in the document (e.g., `resource.data.user_id == request.auth.uid`), ensuring users can only access their own data.

Answer

Data Bundles

Answer

Composite indexes

Answer

IAM roles

Question 13

You are designing a Bigtable data model for an ad-tech platform that tracks user impressions. Which TWO row key design practices should you implement to avoid hotspots and ensure even distribution?

Accepted Answer

Use field promotion to place high-cardinality fields first. Option B is correct because field promotion places the highest-cardinality field first in the row key, which spreads writes across many tablet servers. In Bigtable, rows are sorted lexicographically by row key, so a high-cardinality prefix ensures that successive writes do not concentrate on a single tablet, avoiding hotspots.

Answer

Use monotonically increasing integers as the row key

Answer

Use a reversed timestamp as the first component

Answer

Use a low-cardinality field as the row key prefix

Question 14

You need to select a Google Cloud database for a global e-commerce platform that requires strong consistency for inventory updates, but can tolerate eventual consistency for product reviews. Which THREE services would you consider appropriate for the different parts of the platform?

Accepted Answer

Bigtable for real-time user behavior tracking. Bigtable is correct for real-time user behavior tracking because it is a fully managed, scalable NoSQL database designed for high-throughput, low-latency workloads like time-series data, user analytics, and event logging. It supports strong consistency for single-row operations, which is sufficient for tracking individual user events in real time, and can handle millions of writes per second across a global footprint.

Answer

BigQuery for storing product reviews

Answer

Cloud SQL for inventory

Question 15

You are configuring a Cloud Spanner database for a financial application. You need to ensure that queries on the 'Orders' table by 'customer_id' are efficient without performing a full table scan. You also want to avoid index joins when possible. Which TWO actions should you take?

Accepted Answer

Use the STORING clause to include frequently queried columns in the index. Option A is correct because the STORING clause in a Cloud Spanner secondary index allows you to include additional columns (e.g., frequently queried columns) directly in the index entries. This enables index-only scans, avoiding the need for an index join (back join) to fetch data from the base table, thus improving query efficiency. Option E is correct because creating a secondary index on 'customer_id' directly supports efficient point lookups and range scans on that column, preventing full table scans.

Answer

Create a global secondary index on 'order_id'

Answer

Partition the table by customer_id

Answer

Use the INTERLEAVE IN clause to create a local index