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

Question 1

A startup is building a new mobile application and needs a fully managed, serverless NoSQL database that automatically scales to handle unpredictable traffic spikes. The data model consists of user profiles and activity feeds, and the team wants to minimize operational overhead. Which Google Cloud database should they choose?

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

Accepted Answer

Cloud Firestore in Native mode. Cloud Firestore in Native mode is a serverless NoSQL document database that automatically scales and is designed for mobile and web applications. It provides real-time updates, offline support, and requires no provisioning, making it ideal for startups with unpredictable traffic. Other options either require provisioning, lack mobile-friendly features, or are not serverless.

Answer

Cloud Bigtable

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Question 2

A company is using Cloud Bigtable for a time-series application that stores sensor readings. The row key is currently structured as sensor_id#timestamp, where sensor_id is a string and timestamp is in reverse order (e.g., sensor_id#9999999999). They need to efficiently retrieve the latest reading for a specific sensor. What should they do to optimize the row key design?

Accepted Answer

Use a row key of sensor_id#reverse_timestamp, where reverse_timestamp is a fixed-width, zero-padded value representing the inverted timestamp.. For time-series data in Bigtable, storing the timestamp in reverse order (e.g., using Long.MAX_VALUE - timestamp) as part of the row key ensures that the most recent readings sort first. This allows efficient retrieval of the latest reading for a sensor using a simple prefix scan. Other options either scatter data, rely on unsupported features, or do not change the sort order.

Answer

Use a row key of sensor_id#timestamp and create a secondary index on timestamp.

Answer

Use a row key of sensor_id#timestamp and enable a column family with a TTL to keep only the latest reading.

Answer

Use a row key of timestamp#sensor_id, so that all readings are sorted by time globally.

Question 3

A financial institution uses Bigtable for storing real-time stock trades. They need to query trades by stock symbol and time range, and also by trade ID for individual trade lookups. The row key currently is a concatenation of stock symbol and reverse timestamp. They notice that queries by trade ID are slow because they require a full table scan. What should they do to improve performance for trade ID lookups without affecting the existing access pattern?

Accepted Answer

Maintain a separate table with trade ID as the row key and stock symbol + timestamp as the column qualifier.. Bigtable does not support secondary indexes, so to enable efficient lookups by trade ID, you must create a separate table with trade ID as the row key. This denormalization duplicates data but provides fast point reads for trade ID while preserving the original table's optimized access pattern for symbol and time range queries.

Answer

Create a secondary index on trade ID using Bigtable's built-in indexing feature.

Answer

Enable Bigtable's row key filter to scan by trade ID in the existing table.

Answer

Use a composite row key that includes both stock symbol and trade ID, and query using a prefix.

Question 4

A healthcare company is designing a database solution on Google Cloud to store patient records. The data must be strongly consistent, support ACID transactions, and be highly available across multiple regions. The records include structured data with complex relationships, and the company expects moderate write throughput with heavy read traffic. They also need to run SQL queries for reporting. Which database solution should they choose?

Accepted Answer

Cloud Spanner with a multi-region configuration. Cloud Spanner is the only Google Cloud database that combines strong consistency, ACID transactions, multi-region high availability, and SQL support. It is designed for relational data with complex relationships and can handle moderate write throughput with heavy read traffic. Other options either lack multi-region strong consistency, SQL support, or ACID transactions.

Answer

Cloud SQL for PostgreSQL with cross-region read replicas

Answer

Cloud Bigtable with a multi-region configuration

Answer

Cloud Firestore in Datastore mode

Question 5

A company is deploying a new application on Google Cloud that requires a relational database with automatic failover, point-in-time recovery, and the ability to scale reads. They want to minimize administrative overhead. They expect moderate write throughput and need strong consistency. Which database solution should they choose?

Accepted Answer

Cloud SQL for PostgreSQL with high availability configuration and read replicas.. Cloud SQL for PostgreSQL with high availability and read replicas provides a managed relational database with automatic failover, point-in-time recovery, and read scaling. It offers strong consistency and minimizes administrative overhead, making it the best fit for moderate write throughput and relational requirements.

Answer

Cloud Spanner with regional configuration.

Answer

Bigtable with a relational schema emulation layer.

Answer

Firestore in Datastore mode with multi-region replication.

Question 6

A retail company is deploying a new order-processing application on Google Cloud. The application requires a relational database that supports automatic, multi-region replication with strong consistency and 99.999% availability. The database will be accessed from users in North America, Europe, and Asia. The team wants minimal operational overhead and the ability to scale horizontally for writes. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that combines relational semantics, horizontal write scalability, strong consistency, and multi-region replication with a 99.999% availability SLA. The other options either lack global strong consistency, horizontal write scaling, or relational capabilities, making them unsuitable for this scenario.

Answer

Cloud Bigtable

Answer

Cloud SQL for PostgreSQL with cross-region replicas

Answer

Firestore in Datastore mode

Question 7

A logistics company uses Cloud Spanner for its shipment tracking system. The Shipments table has a primary key of (shipment_id, created_at). The company frequently runs queries to find all shipments with a specific status (e.g., 'IN_TRANSIT') that were created in the last 24 hours. These queries are currently slow because they scan the entire table. The team wants to improve query performance without changing the primary key. What should they do?

Accepted Answer

Create a secondary index on (status, created_at) and include shipment_id as a stored column.. Creating a secondary index on (status, created_at) with shipment_id stored is the optimal solution. It allows Spanner to quickly find shipments by status and time range without scanning the base table, and it respects the constraint of not altering the primary key. This is a standard indexing strategy in Spanner.

Answer

Add a new column for a hash of the status and use it as a prefix in the primary key.

Answer

Use a SQL query hint to force the use of a specific index.

Answer

Create a separate table that stores only shipments with status 'IN_TRANSIT' and query that table.

Question 8

A startup is deploying a new application on Google Cloud and needs a fully managed, globally distributed database that supports ACID transactions and SQL. They expect rapid growth and want to avoid operational overhead. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that combines global distribution, horizontal scalability, ACID transactions, and SQL support with a fully managed service. The other options lack either global distribution, SQL support, or ACID transactions at scale.

Answer

Firestore in Datastore mode

Answer

Cloud Bigtable

Answer

Cloud SQL for MySQL

Question 9

A small e-commerce company runs a MySQL database on Cloud SQL. They need to add a read replica to offload reporting queries from the primary instance. The reporting queries are read-only and can tolerate slight replication lag. Which Cloud SQL feature should they use?

Accepted Answer

Create a read replica in the same region.. Read replicas in Cloud SQL are designed to offload read-only workloads from the primary instance. They replicate asynchronously, so they can tolerate replication lag, which is acceptable for reporting queries. This is the simplest and most cost-effective solution for the given scenario.

Answer

Enable high availability (HA) configuration.

Answer

Migrate to Cloud Spanner for read scaling.

Answer

Use Cloud SQL federated queries to query an external replica.

Question 10

A healthcare company is designing a Cloud Spanner schema for patient records. The primary access pattern is to retrieve a patient's record by patient_id. They also need to query patients by last name and by date of birth for analytics. The patient_id is a random UUID. Which schema design should they use to optimize for these access patterns and avoid hotspots?

Accepted Answer

Use patient_id as the primary key and create secondary indexes on last_name and date_of_birth.. A random UUID primary key distributes writes evenly, avoiding hotspots. Secondary indexes on last_name and date_of_birth allow efficient queries for those columns. Spanner's secondary indexes are automatically maintained and can be used for lookups, making this the best design for the given access patterns.

Answer

Use a composite primary key of last_name and patient_id.

Answer

Use patient_id as the primary key and create a single interleaved table for analytics.

Answer

Use last_name as the primary key and create a secondary index on patient_id.

Question 11

A healthcare company stores patient records in Cloud SQL for PostgreSQL. The database is in us-central1 and must be highly available across zones. They also need read replicas in europe-west1 for analytics and a disaster recovery strategy that can survive a regional outage with minimal data loss. They want to minimize operational overhead. What should they do?

Accepted Answer

Enable high availability on the primary instance, create cross-region read replicas for analytics, and promote a replica if the primary region fails.. The correct approach uses Cloud SQL high availability for automatic failover within the region, cross-region read replicas for analytics and disaster recovery, and replica promotion for regional failover. This meets the requirements with minimal operational overhead because it leverages managed features. Other options either lack cross-region DR, provide insufficient RPO, or introduce unnecessary migration complexity.

Answer

Migrate to Cloud Spanner with a multi-region configuration to achieve global strong consistency and automatic failover.

Answer

Set up a Cloud SQL instance with read replicas in multiple zones within the region and use Cloud Storage to export backups for disaster recovery.

Answer

Configure a Cloud SQL instance with a failover replica and use Cloud SQL federated queries to query data from BigQuery for analytics.

Question 12

A startup is developing a mobile app that requires a fully managed, serverless NoSQL database with automatic scaling and strong consistency for user profiles. They expect unpredictable traffic and want to minimize operational overhead. Which Google Cloud database should they choose?

Accepted Answer

Firestore in Native mode. Firestore in Native mode is a serverless NoSQL database that automatically scales and provides strong consistency for document reads. It is designed for mobile and web applications with unpredictable traffic, offering minimal operational overhead. Cloud SQL and Spanner require provisioning, and Bigtable is not serverless and is optimized for different workloads.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 13

A retail company uses Firestore in Native mode for a product catalog. They need to support a query that finds all products with a price between $10 and $50, sorted by price ascending, and also filters by category. The catalog has millions of products. Which Firestore feature should they use to optimize this query?

Accepted Answer

Create a composite index on (category, price) and query with filters on category and price range, ordering by price.. Firestore requires composite indexes for queries that combine multiple filters with a sort order. A composite index on (category, price) allows the database to efficiently filter by category and a price range while returning results sorted by price. This is the standard way to optimize such queries and scale to millions of documents without performance degradation.

Answer

Enable Firestore in Datastore mode and use a composite index on (category, price).

Answer

Store the price as a string and use a range filter with lexicographic ordering.

Answer

Use a collection group query on a subcollection named 'prices' and filter by price range.

Question 14

A media analytics team stores 300 TB of clickstream events in Bigtable and needs to serve two access patterns: (1) fetch the latest events for a specific user in reverse chronological order, and (2) scan all events for a given day across all users for batch aggregation. The current row key is a random UUID, which makes both patterns slow. They want a design that keeps writes evenly distributed while supporting both patterns. Which row key design should they use?

Accepted Answer

Use a row key of salted hash bucket followed by reversed timestamp, and add a separate table keyed by date plus user ID for the daily scan.. Because Bigtable has no secondary indexes, a single table cannot efficiently serve both a per-user reverse-chronological lookup and a cross-user daily scan. Salting the row key with a hash bucket keeps writes spread across tablets, and a denormalized second table keyed by date plus user ID provides the batch aggregation path without full scans.

Answer

Use a row key of reversed timestamp followed by user ID, and rely on a column family per user for the daily scan.

Answer

Use a row key of date followed by user ID and timestamp, and query the per-user latest events with a filter on the user ID column.

Answer

Use a row key of user ID followed by reversed timestamp, and rely on Bigtable's built-in secondary indexes for the daily scan.

Question 15

A financial analytics firm uses Cloud Bigtable to store time-series trade data. The row key is designed as reversed timestamp (e.g., #<reverse_timestamp>) to enable quick retrieval of the latest trades. They now need to query trades by account ID and time range, and also by symbol and time range. The current row key does not support these queries efficiently. What should they do to optimize for these access patterns?

Accepted Answer

Create a new table with row key as account_id#reverse_timestamp and another table with symbol#reverse_timestamp, and write data to both tables using an application-level dual-write.. To support multiple access patterns in Bigtable, you must design row keys that match the query patterns and often duplicate data into separate tables. Using account_id#reverse_timestamp and symbol#reverse_timestamp as row keys in two tables allows efficient range scans for each pattern. Dual-writes keep the tables in sync. Other options rely on non-existent features or inefficient full scans.

Answer

Use Cloud Bigtable's built-in secondary index feature to create indexes on account_id and symbol.

Answer

Add a column family for account_id and another for symbol, and use filters to query by those columns.

Answer

Change the row key to account_id#symbol#reverse_timestamp to support both queries in a single table.