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

Question 1

A company uses Cloud SQL for MySQL and wants to run complex analytical queries on the same data without affecting OLTP performance. They need a solution with minimal data movement and low operational overhead. Which approach should they take?

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

Accepted Answer

Use AlloyDB for PostgreSQL with its columnar engine. AlloyDB is a PostgreSQL-compatible database that includes a columnar engine for analytical queries, providing HTAP capabilities with minimal performance impact on OLTP. BigQuery requires exporting data, which adds latency and overhead. Read replicas still run on MySQL engines optimized for OLTP. Spanner is overkill and requires migration. AlloyDB is the best fit for HTAP.

Answer

Migrate to Cloud Spanner

Answer

Export data to BigQuery periodically

Answer

Set up a Cloud SQL read replica and run analytical queries on it

Question 2

You are designing a Bigtable schema for a messaging application where users have conversations. Each row represents a message with row key 'userID#conversationID#timestamp'. The application queries the most recent messages for a given conversation. How should you modify the row key to optimize for this query pattern?

Accepted Answer

Reverse the timestamp. To get the most recent messages, you want to scan recent rows. If timestamp is increasing, the most recent messages have the highest timestamp but at the end of the scan range. By reversing the timestamp, you make recent messages appear first in a scan. Field promotion doesn't apply here. Salting would help writes but not the read pattern. The best approach is to reverse the timestamp so that most recent messages have lexicographically smaller keys.

Answer

Promote the conversationID before userID

Answer

Store timestamp as a column instead of part of row key

Answer

Use a hash prefix of the conversationID as the first part

Question 3

A company is migrating a large on-premise Hadoop workload to Google Cloud. The data is stored in HBase and consists of time-series logs with 10 PB of data. They need a fully managed NoSQL solution with high throughput and low latency. Which migration path should they choose?

Accepted Answer

Use Cloud Bigtable and the HBase client. Cloud Bigtable is the fully managed, scalable NoSQL database that is compatible with HBase API, making migration straightforward. Cloud Spanner is relational, not the best for time-series logs. BigQuery is for analytics, not real-time. Firestore is document-based and not suited for petabyte-scale logs.

Answer

Migrate HBase tables to Cloud Spanner using Dataflow

Answer

Export data to BigQuery using Storage Transfer Service

Answer

Re-architect as Firestore collections

Question 4

Which database service should you use if your workload requires complex JOINs, ACID transactions, and you want to avoid operational overhead of managing patching and backups?

Accepted Answer

Cloud SQL. Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server that handles patching, backups, and replication. AlloyDB also is managed, but Cloud SQL is more common for standard OLTP. Firestore and Bigtable are NoSQL.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud Storage

Question 5

You are designing a Spanner schema for a social media application. The table Posts has primary key (UserId, PostId) where PostId is a UUID. The application frequently queries all posts for a given user, ordered by timestamp descending. The current schema uses PostId as the second part of the key, which is random. How can you improve read performance for this query pattern?

Accepted Answer

Create a secondary index on (UserId, Timestamp DESC) with STORING clause. To efficiently query posts for a user in descending order of timestamp, you need the timestamp to be part of the primary key after UserId. However, using PostId (UUID) as the second part doesn't help ordering. You can add a timestamp column and create a secondary index with descending order, but that adds write overhead. Another approach is to change the primary key to (UserId, Timestamp, PostId) and use a separate mechanism to avoid hotspots (e.g., hash prefix on Timestamp). But the simplest improvement is to use a secondary index on (UserId, Timestamp DESC). The question asks to improve read performance; a secondary index with storing clause can provide good performance. The best answer is to create a secondary index on UserId and Timestamp with STORING to include other columns.

Answer

Use a hash prefix on UserId

Answer

Use a materialized view

Answer

Change the primary key to (UserId, Timestamp, PostId)

Question 6

A company runs an e-commerce website on Cloud SQL. They want to scale read traffic without impacting write performance and need high availability across zones. Which configuration should they use?

Accepted Answer

Use a regional Cloud SQL instance with automatic failover and add read replicas. Cloud SQL offers read replicas for scaling read traffic and regional (multi-zone) instances for high availability. Using a regional instance with automatic failover provides HA; adding read replicas offloads reads. A cross-region replica adds latency, and a single zone with increased tier does not provide HA.

Answer

Create a cross-region replica and use it for reads

Answer

Increase the machine tier of the existing instance

Answer

Migrate to Cloud Spanner for automatic read scaling

Question 7

A media streaming company is designing a database for user recommendations. They expect high write throughput for user interactions and need to run complex analytical queries on the same data for personalization. They want a fully managed solution with minimal latency for writes. Which TWO services can be combined to meet these requirements?

Accepted Answer

Cloud Bigtable. You can use Cloud Bigtable for high-throughput write ingestion of user interactions, and then export to BigQuery for analytics. Alternatively, Bigtable can be used with Cloud Dataflow for streaming analytics, but the question asks for databases. Another option is AlloyDB with its columnar engine for HTAP, but that may not handle the extreme write throughput of Bigtable. The best combination is Bigtable for writes and BigQuery for analytics. Firestore is not suitable for high write throughput. Spanner could be used but is more expensive and not as fast for writes as Bigtable for this use case.

Answer

Cloud Spanner

Answer

Cloud SQL

Answer

Firestore

Question 8

You are designing a Spanner schema for a global inventory system. The table Products has primary key (ProductId STRING). The table Inventory has primary key (ProductId STRING, WarehouseId INT64) and is interleaved in Products. You expect high write throughput on Inventory. Which TWO design choices will help avoid hotspots?

Accepted Answer

Use UUID for ProductId. To avoid hotspots in Spanner, avoid monotonically increasing keys. ProductId should be a UUID or hash. Also, using a hash prefix on ProductId as the first part of the key can help distribute writes. Interleaving is fine. Secondary indexes with STORING are for reads. Reverse timestamps are for Bigtable. The correct choices are using UUID and adding a hash prefix.

Answer

Reverse the timestamp in the row key

Answer

Use a monotonically increasing sequence for ProductId

Answer

Create a secondary index on WarehouseId with STORING

Question 9

A company wants to migrate a 5 TB MySQL database to Cloud Spanner with zero downtime. They need to validate schema and data consistency before switching traffic. Which THREE steps should they include in the migration plan?

Accepted Answer

Set up a Dataflow pipeline to replicate changes from MySQL to Spanner. Zero-downtime migration to Spanner typically involves using Strangler Fig pattern: replicate writes from MySQL to Spanner, validate data, then cutover. Key steps: 1. Export schema and convert to Spanner DDL (using HarbourBridge). 2. Set up live migration using Dataflow for continuous replication. 3. Validate data consistency (e.g., using checksums). Taking an export snapshot is fine but not for zero-downtime; using pgloader is for PostgreSQL; creating a Cloud SQL read replica is not directly relevant.

Answer

Use pgloader to migrate the data

Answer

Create a Cloud SQL read replica for fallback

Question 10

A startup is building a mobile app backend with high-concurrency user authentication and profile updates. They need strong consistency and ACID transactions. Which database service should they choose?

Accepted Answer

Cloud SQL. Cloud SQL provides fully managed relational databases with ACID compliance, suitable for high-concurrency OLTP workloads like user authentication and profile updates.

Answer

Firestore

Answer

BigQuery

Answer

Cloud Bigtable

Question 11

A company wants to run complex analytical queries on terabytes of sales data with sub-second query response times for dashboards. Data is updated frequently in near real-time. Which combination of services is most appropriate?

Accepted Answer

AlloyDB with columnar engine. AlloyDB with its columnar engine supports both high-speed transactions and fast analytical queries on the same data, fulfilling near-real-time analytics requirements.

Answer

Cloud Spanner with interleaved tables

Answer

Cloud SQL for MySQL with read replicas

Answer

Bigtable with aggregation queries

Question 12

An engineer is designing a Cloud Spanner table for a global user activity tracking system with high write throughput. Which primary key design is BEST to avoid hotspots?

Accepted Answer

UUID string (generated by application). Using a UUID or hash-prefixed key distributes writes evenly across nodes, preventing hotspots that occur with monotonically increasing keys.

Answer

Monotonically increasing integer (INT64) with auto-increment

Answer

Composite key with user_id as first part

Answer

Timestamp as primary key

Question 13

A team is migrating a large on-premise Oracle database to Cloud SQL for PostgreSQL. They need to minimize downtime and ensure data consistency. Which migration approach is recommended?

Accepted Answer

Use Database Migration Service with continuous replication. Using Database Migration Service (DMS) with continuous replication provides near-zero downtime and maintains consistency.

Answer

Use pg_dump and pg_restore

Answer

Export data as CSV, import to Cloud SQL

Answer

Create a Cloud SQL read replica from on-premise

Question 14

A data analyst needs to run ad-hoc SQL queries on a large dataset stored in Google Cloud Storage (CSV files). They do not want to manage any infrastructure. Which service should they use?

Accepted Answer

BigQuery. BigQuery is a serverless data warehouse that can query external data sources like GCS directly using federated queries.

Answer

Dataproc

Answer

Cloud Spanner

Answer

Cloud SQL

Question 15

A Cloud Bigtable instance stores high-volume time-series data. Write throughput is at node capacity, but read latency spikes occasionally. The row key pattern is 'timestamp#device_id'. Which optimization should be applied first?

Accepted Answer

Reverse the row key to device_id#timestamp. With timestamp first, writes are concentrated on the same tablet, causing hotspots and read latency spikes. Reversing the order or salting distributes writes.

Answer

Add more nodes to the cluster

Answer

Enable SSD storage

Answer

Use a single column family