PCDOE Design and Plan Database Solutions Practice Test 2 — 15 Questions

Question 1

A company is migrating an on-premises OLTP application to Google Cloud. The application requires high concurrency (up to 5,000 simultaneous connections) and uses a relational schema with strong transactional integrity. Which database service is the MOST suitable?

Accepted Answer

Cloud SQL. Cloud SQL supports up to 4,096 connections by default (configurable) and provides full transactional integrity. For 5,000 connections, Cloud SQL can be configured with appropriate instance sizing. Spanner also supports transactions but is overkill for a single-region OLTP migration. BigQuery is for analytics, Firestore is NoSQL.

Answer

Firestore

Answer

BigQuery

Answer

Cloud Spanner

Question 2

You need to design a Spanner schema for a social media application that stores user posts. Each post has a unique ID, author ID, timestamp, and content. The primary access pattern is querying all posts for a given author in reverse chronological order. Which schema design minimizes the risk of hotspotting?

Accepted Answer

Primary key: (AuthorId, Timestamp) with Timestamp stored in descending order. Using a composite primary key with author ID as the first part distributes writes across splits. Adding timestamp as the second key orders rows for efficient range scans. Monotonically increasing keys (like sequential post IDs) cause hotspots. Interleaving with user table would be appropriate, but the question focuses on hotspotting.

Answer

Primary key: (AuthorId, PostId) where PostId is generated using a UUID

Answer

Primary key: (AuthorId, HashOfPostId)

Answer

Primary key: (PostId) with a secondary index on AuthorId

Question 3

A data engineer needs to run complex analytical queries on terabytes of data with sub-second query latency. The data is stored in Google Cloud Storage and updated daily. Which database service should they use?

Accepted Answer

BigQuery. BigQuery is a serverless data warehouse designed for complex SQL queries on large datasets with fast query execution. It can query data directly from Cloud Storage using external tables. Cloud SQL and Spanner are for OLTP, Bigtable for real-time low-latency lookups.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Cloud Spanner

Question 4

An e-commerce platform runs on Cloud SQL and expects heavy write traffic during a flash sale. The database instance currently has 8 vCPUs and 32 GB RAM. Based on the max_connections formula (max_connections = RAM_MB/16), what is the current maximum number of connections, and what change would increase it?

Accepted Answer

2048 connections; increase RAM to 64 GB. With 32 GB = 32768 MB, max_connections = 32768/16 = 2048. To increase connections, you need to increase RAM (e.g., 64 GB gives 4096). vCPU does not directly affect max_connections per the formula.

Answer

2048 connections; increase vCPU to 16

Answer

4096 connections; increase vCPU to 16

Answer

4096 connections; increase RAM to 64 GB

Question 5

You are designing a Bigtable schema for a time-series application that records sensor readings every second. Queries always filter by device ID and time range. To avoid hotspotting and ensure recent data is retrieved quickly, which row key design is MOST effective?

Accepted Answer

Row key: (hash(deviceID)#deviceID#reverseTimestamp). Salting (hash prefix) distributes writes across nodes, avoiding hotspots from sequential timestamps. Field promotion (device ID first) allows efficient prefix scans. Reverse timestamp ensures most recent data appears first when scanning.

Answer

Row key: deviceID#timestamp

Answer

Row key: (reverseTimestamp#deviceID)

Answer

Row key: timestamp#deviceID

Question 6

Which Google Cloud database service is designed for hybrid transactional and analytical processing (HTAP) with a built-in columnar engine?

Accepted Answer

AlloyDB for PostgreSQL. AlloyDB for PostgreSQL is a fully managed database with a columnar engine that accelerates analytical queries on transactional data. Cloud Spanner offers an analytics interface, but AlloyDB explicitly mentions a columnar engine for HTAP.

Answer

BigQuery

Answer

Cloud SQL

Answer

Cloud Spanner

Question 7

A company needs to migrate its on-premises Oracle database to a fully managed relational database on Google Cloud. The application uses stored procedures and requires high availability across multiple zones. Which migration path is MOST appropriate?

Accepted Answer

Migrate to Cloud SQL for PostgreSQL and configure cross-zone high availability. Cloud SQL for PostgreSQL supports high availability across zones and can use the PostgreSQL dialect with some migration effort. Bare Metal Solution is for lifting and shifting Oracle workloads but is not fully managed. AlloyDB is PostgreSQL-compatible and provides high availability. The best option is Cloud SQL for PostgreSQL due to its managed nature and cross-zone HA.

Answer

Use Database Migration Service to migrate to Cloud SQL for SQL Server

Answer

Use Bare Metal Solution to run Oracle Database on dedicated hardware

Answer

Migrate to AlloyDB for PostgreSQL with cross-region replication

Question 8

A team is designing a Spanner schema for an online gaming leaderboard. The leaderboard stores player scores and requires high write throughput. Which primary key design is BEST to avoid write hotspots?

Accepted Answer

Primary key: (HashOfPlayerId, Timestamp). Monotonically increasing keys (like score or timestamp) cause hotspotting. Using a random prefix (e.g., hash of player ID) ensures writes are distributed across splits. Player ID alone might also cause hotspotting if many players write simultaneously.

Answer

Primary key: (PlayerId, Timestamp)

Answer

Primary key: (Score, PlayerId)

Answer

Primary key: (Timestamp, PlayerId)

Question 9

A financial services firm needs a database for real-time fraud detection that requires single-digit millisecond latency for lookups on a precomputed feature set. The dataset is 5 TB and grows by 1 TB per month. Which database service BEST meets these requirements?

Accepted Answer

Cloud Bigtable. Bigtable is designed for high-throughput, low-latency access to large datasets. It can handle 5 TB (or more) with SSD nodes. Cloud SQL and Spanner have lower throughput limits. BigQuery is not real-time.

Answer

BigQuery

Answer

Cloud Spanner

Answer

Cloud SQL (MySQL)

Question 10

You need to estimate the number of Bigtable nodes required for a workload of 50,000 reads per second (QPS) and 20,000 writes per second. Each node can handle 10,000 QPS for reads or writes. Storage is not a constraint. What is the minimum number of nodes required?

Accepted Answer

5 nodes. Reads require 50,000/10,000 = 5 nodes. Writes require 20,000/10,000 = 2 nodes. The higher value is the bottleneck, so 5 nodes are needed.

Answer

2 nodes

Answer

7 nodes

Answer

10 nodes

Question 11

Which of the following is a benefit of using parent-child interleaved tables in Cloud Spanner?

Accepted Answer

Improved read performance for queries joining parent and child. Interleaving stores child rows physically close to their parent row, enabling fast joins and reducing read latency. It does not improve write throughput or eliminate the need for secondary indexes.

Answer

Increased write throughput by distributing writes

Answer

Automatic sharding across regions

Answer

Eliminates the need for secondary indexes

Question 12

A team is migrating a MongoDB application to Firestore. The data model includes embedded documents and references between collections. Which approach should they follow to maintain similar query performance?

Accepted Answer

Use subcollections for embedded data and references for relationships. Firestore supports subcollections (similar to embedded documents) and references. Denormalization can reduce the need for joins. Avoiding transactions for every write is not ideal for consistency. The recommended pattern is to use subcollections for embedded data and references for related entities.

Answer

Flatten all data into a single document per entity

Answer

Replicate data across multiple collections to avoid reads

Answer

Use a single collection for all documents and rely on client-side joins

Question 13

A company is designing a database solution for a global social media application that requires strong consistency, high write throughput, and complex relational queries. Which TWO Google Cloud databases should they consider? (Choose 2)

Accepted Answer

Cloud Spanner. Cloud Spanner provides global strong consistency and relational support. AlloyDB offers strong consistency and high performance for relational workloads. Bigtable is eventually consistent. BigQuery is analytical. Firestore is not globally consistent.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Firestore

Question 14

A gaming company is designing a leaderboard using Cloud Spanner. The leaderboard updates scores in real time and supports queries for top players. Which THREE strategies should they implement to achieve high performance and avoid hotspots? (Choose 3)

Accepted Answer

Use global secondary indexes with STORING clause to avoid back-to-base lookups. Salting distributes writes, storing top scores in a separate table reduces contention, and using a STORING clause in indexes prevents data lookup. Global secondary indexes without STORING cause extra round trips.

Answer

Use a monotonically increasing primary key for scores

Answer

Use a single table with all data and rely on Spanner auto-scaling

Question 15

A startup is building an IoT analytics platform that ingests sensor data at high velocity and needs to run real-time dashboards and ad-hoc queries on the data. Which TWO Google Cloud databases should they use together? (Choose 2)

Accepted Answer

Cloud Bigtable. Bigtable is ideal for real-time ingestion and retrieval of sensor data with low latency. BigQuery is used for analytical queries and dashboards. Cloud SQL and Spanner are not optimized for high-velocity IoT ingestion. Firestore is more suited for mobile apps.

Answer

Cloud Spanner

Answer

Firestore

Answer

Cloud SQL