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

Question 1

A global e-commerce platform expects 50,000 concurrent users during flash sales, each performing short transactions like adding to cart and checking out. The database must provide strong transactional consistency across regions. Which Google Cloud database is most appropriate?

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

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, strongly consistent relational database that can handle high-concurrency OLTP workloads with ACID transactions across regions. Cloud SQL is limited to single region and cannot meet global consistency requirements. Bigtable and Firestore are NoSQL and do not support strong ACID transactions natively.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud SQL (MySQL)

Question 2

An application uses Cloud SQL (MySQL) and experiences an increasing number of 'too many connections' errors. The current instance has 4 vCPUs and 15GB of RAM. The application's connection pool is configured for 500 connections. What should the engineer do to resolve this error?

Accepted Answer

Increase the max_connections flag in the Cloud SQL database flags settings. Cloud SQL for MySQL enforces a max_connections limit that is derived from the instance's memory tier, and the default for a 15GB RAM instance is well below the 500 connections the application pool requests. Raising the max_connections database flag directly increases the allowed concurrent connections, resolving the 'too many connections' error without changing the application architecture.

Answer

Configure a Cloud SQL connection pool with a maximum of 100 connections

Answer

Create a read replica to distribute read connections

Answer

Upgrade the instance to have 8 vCPUs to increase connection capacity

Question 3

A team is designing a Cloud Spanner schema for a global social media application. The table 'Posts' has a primary key of (UserId, PostId) where PostId is a UUID. They notice write hotspots on the server with monotonically increasing UserId values. What is the most effective schema design change to distribute writes evenly?

Accepted Answer

Add a hash prefix to the UserId to create a composite primary key like (HashUserId, UserId, PostId). Cloud Spanner uses range-based sharding on the primary key. Monotonically increasing UserId values cause all writes to go to the last range, creating a hotspot. Adding a hash prefix to UserId (e.g., HashUserId) distributes writes across multiple ranges, evenly spreading the load. This is the recommended approach for high-write tables with sequential keys.

Answer

Create a secondary index on PostId

Answer

Place PostId first in the primary key

Answer

Use a monotonically increasing integer for PostId instead of UUID

Question 4

A company needs to store and analyze semi-structured JSON logs from multiple microservices. The data is write-heavy with bursts of 100,000 writes/sec, and queries filter by service name and timestamp range. They require low operational overhead and the ability to query with SQL. Which Google Cloud database should they choose?

Accepted Answer

BigQuery. BigQuery is the correct choice because it is a serverless, highly scalable data warehouse that natively supports SQL and can ingest high-velocity streaming data (e.g., 100,000 writes/sec) via the Storage Write API or streaming inserts. It automatically handles semi-structured JSON with its native JSON data type and allows efficient filtering by service name and timestamp using SQL. Its columnar storage and partitioning/clustering capabilities make it ideal for analytical queries on time-series log data with low operational overhead.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 5

You are designing a Cloud Bigtable schema for a time-series application that stores temperature readings from sensors. Each reading has a sensor ID (string), a timestamp (microseconds), and a temperature value. Queries always filter by sensor ID and a time range. Which row key design is optimal?

Accepted Answer

[salted_hash]#[sensor_id]#[reversed_timestamp]. Optimal because it uses a salted hash to distribute writes across Bigtable tablets, avoiding hot-spotting on a single node for high-write sensors. The sensor_id ensures all data for a sensor is co-located for efficient range scans, and the reversed timestamp allows queries for the most recent data to be served from the start of the row range, leveraging Bigtable's lexicographic ordering.

Answer

[sensor_id]#[timestamp]

Answer

[timestamp]#[sensor_id]

Answer

[sensor_id]#[reversed_timestamp]

Question 6

A company is using Cloud SQL for MySQL for its OLTP workload. They want to run complex analytical queries on the same data without impacting transactional performance. The analytical queries involve large scans and joins. What is the recommended approach?

Accepted Answer

Export the data to BigQuery periodically and run analytical queries there. BigQuery is a serverless, highly scalable data warehouse designed for analytical queries on large datasets. By exporting data from Cloud SQL (OLTP) to BigQuery, you isolate the analytical workload from the transactional database, preventing resource contention and performance degradation on the primary instance. This separation of concerns is the recommended pattern for running complex scans and joins without impacting OLTP performance.

Answer

Enable MySQL Query Cache to speed up analytical queries on the primary instance

Answer

Use Cloud Spanner to handle both OLTP and analytics with interleaved tables

Answer

Create a Cloud SQL read replica and route analytical queries to it

Question 7

A team is migrating a legacy application from a relational database to Cloud Firestore. The existing schema has a Customers table and an Orders table with a foreign key. The application often shows orders for a customer. What is the recommended data modeling approach in Firestore?

Accepted Answer

Store orders as a nested array within the customer document. Cloud Firestore is optimized for denormalized, document-based data models. Storing orders as a nested array within the customer document allows the application to retrieve all orders for a customer with a single document read, which is efficient for the common query pattern of 'showing orders for a customer.' This approach avoids the need for joins or multiple queries, aligning with Firestore's strengths in read-heavy, hierarchical data access.

Answer

Use Cloud SQL instead of Firestore for this relationship

Answer

Create a top-level collection 'Orders' and use reference fields to link to customers

Answer

Create separate collections for customers and orders, and use composite indexes for queries

Question 8

A company is building a real-time leaderboard for a mobile game using Google Cloud. The data includes player scores that update frequently (thousands of writes per second) and queries for top 100 players. Which database is most suitable?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is a good fit for high write throughput and simple range scans (e.g., scanning top scores). Firestore has limited write capacity. Cloud SQL cannot handle thousands of writes per second. Spanner can but is overkill and more expensive.

Answer

BigQuery

Answer

Cloud Firestore

Answer

Cloud SQL (PostgreSQL)

Question 9

You are migrating a relational database to Cloud Bigtable. The source schema has a Users table and an Orders table with a one-to-many relationship. The application frequently queries all orders for a user. Which three Bigtable schema design practices should you apply?

Accepted Answer

Denormalize by storing orders as multiple columns or a serialized column in the Users row. Option A is correct because Bigtable has no joins, so the one-to-many Users-to-Orders relationship must be denormalized by embedding orders as multiple columns or a serialized column within the Users row, letting the application fetch all orders for a user with a single row read. Option C is correct because Bigtable sorts data lexicographically by row key and hotspots occur when sequential keys (like UserId) concentrate writes on one tablet; a hash prefix distributes writes evenly across tablets. Option E is correct because grouping user attributes and orders into separate column families lets you tune garbage collection and access patterns per family, and column families are the unit of locality and compression in Bigtable. Option B is incorrect because 3NF normalization relies on joins that Bigtable cannot perform, so it defeats the purpose of a wide-column NoSQL design. Option D is incorrect because keeping a foreign key in the Orders row key would require a separate lookup or scan to retrieve a user's orders, rather than storing them together in the Users row for fast single-row access.

Answer

Maintain 3NF to avoid data duplication

Answer

Keep the foreign key relationship by storing UserId in the Orders row key

Question 10

A company is using Cloud SQL for PostgreSQL and wants to improve read scalability for a reporting dashboard that executes complex aggregate queries. The reports can tolerate up to 5 minutes of data staleness. Which two actions should the team take?

Accepted Answer

Create read replicas in Cloud SQL and configure the reporting application to connect to the replicas. Option B is correct because Cloud SQL for PostgreSQL read replicas offload read-only reporting traffic from the primary instance, and since the reports tolerate up to 5 minutes of staleness, asynchronous replication lag is acceptable for the dashboard's aggregate queries. Option E is correct because Database Migration Service can continuously replicate Cloud SQL for PostgreSQL data into BigQuery, where complex aggregate queries run on a columnar analytics engine without impacting the transactional primary, and the 5-minute staleness tolerance fits the near-real-time CDC replication model. Option A is not correct because PostgreSQL has no general-purpose 'query cache' feature that can be enabled to accelerate aggregate queries; caching is handled at the plan/buffer level and does not provide the read scalability described. Option C is not correct because scaling up the primary's vCPUs increases capacity on the same instance rather than adding read scalability, and reporting queries would still compete with transactional workloads. Option D is not correct because connection pooling reduces connection establishment overhead but does not improve read scalability for complex aggregate queries.

Answer

Enable PostgreSQL query cache to speed up aggregate queries

Answer

Increase the number of vCPUs on the primary instance to handle more queries

Answer

Configure a connection pool to reduce connection overhead

Question 11

You are designing a Spanner schema for a global social media application that stores user posts. Each user can have millions of posts. The most common query is 'get the most recent 10 posts for a user'. Which table interleaving design minimizes latency?

Accepted Answer

Interleave the Posts table under the Users table, with user_id as parent key and post_timestamp as the child ordering key.. Interleaving the Posts table under the Users table in Spanner ensures that all posts for a given user are stored in the same split, co-located on the same tablet server. This allows the query for the most recent 10 posts to be served with a single, local range scan on the interleaved child table, using post_timestamp as the descending ordering key, minimizing cross-node communication and latency.

Answer

Use a secondary index on user_id in the Posts table.

Answer

Create a single table with user_id and post_timestamp as a composite primary key.

Answer

Store posts in a separate Cloud Bigtable table and use the user_id as part of the row key.

Question 12

You need to size a Bigtable cluster for a workload that requires 50,000 reads per second (QPS) and 20,000 writes per second. Each read is about 1 KB, each write is about 1 KB. The data volume is 5 TB and growing. You choose SSD storage. What is the minimum number of nodes?

Accepted Answer

5 nodes. Bigtable SSD nodes can handle approximately 10,000 read QPS per node for 1 KB reads and 10,000 write QPS per node for 1 KB writes. For 50,000 reads QPS, 5 nodes are needed (50,000 / 10,000 = 5). For 20,000 writes QPS, 2 nodes are sufficient, but the read requirement dominates. Each SSD node also provides up to 2.5 TB of storage, so 5 nodes provide 12.5 TB, more than the 5 TB data. With Bigtable’s minimum recommendation of 3 nodes for production, 5 nodes satisfy all requirements. Therefore, the minimum number of nodes is 5.

Answer

72 nodes

Answer

50 nodes

Answer

7 nodes

Question 13

A global gaming company uses Spanner to store player profiles and scores. The most common query is 'Get the top 10 players by score' across all regions. The 'Players' table has millions of rows. Which schema design and query approach provides the best performance?

Accepted Answer

Add a secondary index on score column and query 'SELECT * FROM Players ORDER BY score DESC LIMIT 10'.. A secondary index on the `score` column allows Spanner to perform an index scan in descending order, retrieving only the top 10 rows without scanning the entire `Players` table. The `ORDER BY score DESC LIMIT 10` query leverages the index's sorted structure, making it the most efficient approach for this common query pattern in a globally distributed database.

Answer

Add a generated column storing the score as a string and index it.

Answer

Use a STORING clause to store additional columns in the index.

Answer

Use a parent-child interleaving between a 'Leaderboard' parent table and 'Players' child table.

Question 14

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?

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 15

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