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

Question 1

You need to design a Bigtable row key for a time-series application that records temperature readings from thousands of sensors. The most common query is 'get all readings for a specific sensor in the last hour'. Which row key design is optimal?

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

Accepted Answer

sensorID#reverse_timestamp. Optimal because it groups all readings for a sensor together (via sensorID as the row key prefix) while using reverse timestamps to ensure the most recent data appears first within each row. This design allows Bigtable to efficiently scan a single row for the last hour's readings using a prefix scan on sensorID with a timestamp range filter, minimizing the number of rows accessed.

Answer

timestamp#sensorID

Answer

sensorID#timestamp

Answer

hash(sensorID)#timestamp

Question 2

An e-commerce platform uses Cloud SQL for PostgreSQL with max_connections set to 500. They plan to increase the number of application instances requiring connections. The instance has 8 vCPUs and 32 GB RAM. What is the maximum number of connections Cloud SQL can support based on the default formula?

Accepted Answer

2048. Cloud SQL for PostgreSQL uses a default formula to calculate max_connections: max_connections = min(4 * vCPUs + 100, 2048) for PostgreSQL instances. With 8 vCPUs, the raw calculation gives 132, but the cap of 2048 governs the maximum allowed. Since the question asks for the maximum based on the default formula, the correct answer is the cap value of 2048. Candidates often mistakenly use the raw formula output (132) or confuse with MySQL's cap (4096).

Answer

4096

Answer

1024

Answer

500

Question 3

A company is evaluating Google Cloud databases for a new application that requires: (1) strong global consistency across multiple regions, (2) the ability to run complex analytical queries on the same data as the transactional workload, and (3) high write throughput. Which TWO databases should they consider?

Accepted Answer

Cloud Spanner. Cloud Spanner provides strong global consistency across multiple regions via multi-region configurations using synchronous replication, high write throughput (up to 20,000 writes per second per node, scalable), and support for transactional workloads. However, it does not natively handle complex analytical queries on the same data; for HTAP, a separate analytics engine like BigQuery or a dedicated HTAP database such as AlloyDB would be needed. AlloyDB offers HTAP and high write throughput, but its cross-region replication is eventually consistent, failing the strong global consistency requirement. BigQuery lacks transactional strong consistency and high write throughput. Bigtable provides eventual consistency and no SQL analytical queries. Firestore offers strong consistency only within a single region and no complex analytical queries. Thus, only Cloud Spanner fully meets the three requirements.

Answer

Bigtable

Answer

AlloyDB

Answer

BigQuery

Answer

Firestore

Question 4

You are migrating a 5 TB MySQL database to Cloud SQL. The migration must have minimal downtime. Which THREE steps should you include in your migration plan?

Accepted Answer

Configure continuous replication between on-premises and Cloud SQL.. Configuring continuous replication (e.g., using Database Migration Service with CDC) ensures that changes made on the on-premises MySQL database are continuously applied to Cloud SQL, keeping them in sync with minimal lag. This is essential for achieving minimal downtime, as it allows the final cutover to be nearly instantaneous.

Answer

Take a full backup and restore to Cloud SQL.

Answer

Use mysqldump to export the database.

Question 5

You are designing a Spanner schema for a financial application that stores transactions for user accounts. To avoid hotspots and optimize performance, which TWO practices should you follow?

Accepted Answer

Use the STORING clause in secondary indexes to include frequently accessed columns.. The STORING clause in a Spanner secondary index allows you to include non-key columns directly in the index, enabling index-only scans that avoid a back-join to the base table. This reduces read latency and resource consumption, which is critical for high-throughput financial transaction queries.

Answer

Store all data in a single table without interleaving.

Answer

Use a monotonically increasing integer as the primary key.

Answer

Create secondary indexes without the STORING clause.

Question 6

An engineer is designing a global inventory system that requires strong consistency across continents, with the ability to handle write conflicts and ensure ACID transactions. The system expects millions of reads and writes per second. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides ACID transactions and strong consistency across globally distributed regions, using TrueTime and synchronous replication to handle write conflicts at scale. It is designed for millions of reads and writes per second while maintaining external consistency, making it ideal for a global inventory system.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 7

A company uses Bigtable for time-series analytics and needs to query the most recent data points first. The row key currently consists of a user ID followed by a timestamp (e.g., user123#2024-01-15T10:30:00). However, frequent queries filter by time range across all users. Which row key design change would optimize query performance for this access pattern?

Accepted Answer

Reverse the timestamp and place it at the beginning of the row key (e.g., 2024-01-15T10:30:00_rev#user123).. Reversing the timestamp and placing it at the beginning of the row key ensures that the most recent data points are stored first in lexicographic order. Bigtable stores rows sorted by row key, so queries filtering by a time range across all users can now scan a contiguous range of rows without needing to skip over user ID prefixes. This design avoids the hotspotting and inefficient scans that occur when the timestamp is not the leading part of the key for time-range queries.

Answer

Use the user ID as the only row key and store timestamps as column qualifiers.

Answer

Use a monotonically increasing integer as the row key.

Answer

Use a hash of the user ID as a prefix (salting) to distribute writes evenly.

Question 8

A data engineer is designing a schema for Cloud Spanner to store a hierarchy of customers and their orders. Customers have many orders, and queries often retrieve orders for a specific customer. To optimize performance and reduce cross-node reads, which schema design pattern should the engineer use?

Accepted Answer

Use a parent-child interleaved table structure where Orders are interleaved in Customers.. Spanner's interleaved tables allow storing child rows (orders) physically co-located with their parent row (customer) using the same primary key prefix. This enables efficient joins and reduces cross-node reads. A secondary index with STORING is useful for other access patterns but not for hierarchical queries. Denormalization is not recommended in Spanner.

Answer

Denormalize by embedding order details as a repeated field within the customer row.

Answer

Normalize customers and orders into separate tables without any relationship.

Answer

Create a secondary index with STORING on the order ID.

Question 9

An engineer is migrating a workload from a relational database to Bigtable. The current schema has a Customers table (1M rows) and an Orders table (100M rows) with a foreign key. Queries often fetch all orders for a customer. What is the best row key design for the Bigtable orders table?

Accepted Answer

Use customer ID + order ID as the row key (e.g., cust123#ord456).. Using customer ID + order ID as the row key ensures that all orders for a single customer are stored in contiguous rows, enabling efficient range scans. Bigtable orders rows lexicographically by row key, so a prefix scan on the customer ID retrieves all related orders in a single read operation, avoiding expensive joins or scatter-gather patterns.

Answer

Use the order ID as the row key and store customer ID as a column.

Answer

Use a hash of the customer ID as the row key.

Answer

Use a random UUID as the row key.

Question 10

A company runs a MySQL database on Cloud SQL for an e-commerce platform. They need to add a new column to a table with millions of rows without causing downtime. What is the recommended approach?

Accepted Answer

Use a tool like pt-online-schema-change to perform the change with minimal impact.. Pt-online-schema-change (or gh-ost) creates a shadow table with the new schema, incrementally copies rows using triggers or binary log replay, and then atomically swaps the tables. This avoids holding any locks on the original table, preventing downtime for an e-commerce platform with millions of rows. Cloud SQL's InnoDB does not support true online DDL for all ALTER TABLE operations, especially on large tables, making a dedicated online schema change tool the safest approach.

Answer

Use 'ALTER TABLE ... ADD COLUMN ... ALGORITHM=INPLACE, LOCK=NONE'.

Answer

Create a new table with the column, copy data manually, then swap tables.

Answer

Use 'ALTER TABLE ... ADD COLUMN' directly; Cloud SQL handles it online.

Question 11

An engineer is designing a Cloud Spanner schema for a chat application where users send messages. Messages are ordered by timestamp per conversation. The primary key chosen is (ConversationId, MessageId) where MessageId is a monotonically increasing integer. What potential issue might arise with this key design?

Accepted Answer

Writes will be concentrated on a single split causing hotspots.. Using a monotonically increasing integer as the second part of the primary key (MessageId) in Cloud Spanner causes all new writes to be concentrated on a single split (tablet) that handles the highest key range. This creates a hotspot, degrading write throughput and latency, as Cloud Spanner splits data by key range and sequential inserts target the same node.

Answer

Queries by conversation will be slow.

Answer

The table cannot be interleaved with another table.

Answer

The table will not support secondary indexes.

Question 12

A company needs a fully managed, relational database with strong consistency and global distribution for a travel booking application that supports high concurrency and ACID transactions. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it is a fully managed, globally distributed relational database that provides strong consistency and ACID transactions across regions, making it ideal for high-concurrency travel booking applications that require global distribution and transactional integrity.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 13

A data engineer is migrating a legacy on-premises Oracle data warehouse to Google Cloud. The source schema uses star schemas and advanced Oracle features like materialized views. The target must support real-time data from streaming sources and run complex SQL joins over 50 TB of data with low latency. Which architecture is most appropriate?

Accepted Answer

Migrate to BigQuery and use streaming inserts for real-time data.. BigQuery is the most appropriate target because it supports real-time streaming inserts, can handle complex SQL joins over 50 TB of data with low latency via its columnar storage and distributed query engine, and can replace Oracle materialized views with logical views or scheduled queries. It also natively integrates with streaming sources like Pub/Sub, making it ideal for the real-time data requirement.

Answer

Migrate to AlloyDB and use columnar engine for analytics.

Answer

Migrate to Cloud Spanner and use its analytics interface.

Answer

Migrate to Cloud SQL for PostgreSQL and use read replicas for analytics.

Question 14

A company is migrating a monolithic application to a microservices architecture and plans to use multiple Google Cloud databases. The application has the following workloads: (1) user profiles with high read/write concurrency, (2) product catalog with complex queries, (3) session data that requires low-latency access. Which three Google Cloud databases should be used? (Choose three.)

Accepted Answer

Firestore. Cloud SQL is correct for the product catalog with complex queries because it is a fully managed relational database that supports standard SQL, enabling complex joins and aggregations. Firestore is correct for user profiles with high read/write concurrency because it is a NoSQL document database that scales horizontally and provides strong consistency. Memorystore (Redis) is correct for session data requiring low-latency access because it is an in-memory cache delivering sub-millisecond response times. Cloud Bigtable is designed for high-throughput time-series or key-value data, not complex queries, and Cloud Spanner is more suited for globally distributed transactional workloads.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 15

An engineer is designing a Bigtable schema for a weather data application. The data is written by thousands of sensors, each generating a reading every minute. Queries typically retrieve all readings for a sensor in a time range. The row key should be designed to avoid hotspots and support these queries. Which two row key components are recommended? (Choose two.)

Accepted Answer

Reversed timestamp. Using a reversed timestamp (e.g., Long.MAX_VALUE - timestamp) as part of the row key spreads writes across Bigtable tablets, avoiding hotspots that occur when sensors write sequentially in natural time order. This design also supports efficient range scans for a sensor's data over a time range when combined with a sensor ID prefix.

Answer

Sensor ID (raw) as the only key

Answer

Sensor location as a column

Answer

Timestamp in natural order