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

Question 1

A startup needs to run complex analytical queries on large datasets (10+ TB) with sub-second to a few seconds latency. The data is structured and updated daily in batch. Which Google Cloud service is best suited for this use case?

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

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse designed for petabyte-scale analytics with fast SQL queries using columnar storage and a distributed query engine. It supports sub-second to few-second latency on structured data via features like clustering, partitioning, and BI Engine acceleration, and it handles daily batch updates efficiently through batch loading or scheduled queries.

Answer

Cloud Bigtable

Answer

AlloyDB

Answer

Cloud SQL

Question 2

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 3

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 4

A company is using Cloud Bigtable to store user events for real-time analytics. The current row key is userId_timestamp (e.g., user123_20240115103000). However, writes to the table are unevenly distributed, causing hotspotting on a few nodes. Which row key modification can best distribute writes evenly?

Accepted Answer

Add a hash prefix (e.g., 2-byte hash of userId) at the beginning.. Adding a hash prefix (e.g., a 2-byte hash of the userId) at the beginning of the row key ensures that writes are distributed across all Bigtable nodes by randomizing the initial portion of the key. This prevents hotspotting because sequential or clustered userId values no longer concentrate writes on a single tablet server, as Bigtable splits and distributes rows based on the row key prefix.

Answer

Increase the number of nodes to handle the load.

Answer

Reverse the timestamp and prepend it to the key.

Answer

Move userId to the end of the row key.

Question 5

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 6

An engineer is designing a Cloud Spanner schema for a social media application. The database will have a User table and a Post table. Users have many posts, and the application frequently queries all posts for a user, ordered by timestamp. Which two schema design choices will improve performance? (Choose two.)

Accepted Answer

Interleave the Post table within the User table.. Option A is correct because interleaving Post within User physically co-locates child rows with their parent User row, so fetching all posts for a given user reads contiguous storage on the same split rather than scattering lookups across the keyspace, which is exactly the parent-child access pattern this application uses. Option E is correct because making (UserId, PostTimestamp) the Post primary key prefixes rows by UserId and orders them by timestamp within each user, so the frequent query for a user's posts ordered by timestamp is served by a single ordered range scan with no extra sort or index. Option B is not needed because a secondary index on PostId does not support the query pattern of retrieving a user's posts by timestamp, and Spanner primary keys are already indexed. Option C is wrong because a monotonically increasing integer PostId creates a hotspot where all new writes land on the last split, hurting write scalability. Option D is wrong because a random UUID PostId scatters writes but does not by itself optimize the read pattern of retrieving a user's posts in timestamp order.

Answer

Create a secondary index on PostId.

Answer

Use a monotonically increasing integer for PostId.

Answer

Use a UUID for PostId.

Question 7

An e-commerce platform uses Cloud SQL for PostgreSQL to manage orders. The application team reports that the database experiences performance degradation during peak hours due to high connection churn. They want to maintain a pool of established connections. Which configuration change addresses this without application code changes?

Accepted Answer

Enable the pgBouncer flag to use transaction pooling.. Enabling the pgBouncer flag in Cloud SQL for PostgreSQL provides a built-in connection pooler that maintains persistent connections to the database, reducing the overhead of frequent connection establishment. pgBouncer operates in transaction pooling mode, which allows multiple client connections to share a smaller pool of backend connections, directly addressing high connection churn without requiring any application code changes.

Answer

Reduce the max_connections flag to force the application to reuse connections.

Answer

Increase the max_connections flag to a higher value.

Answer

Switch to Cloud SQL for MySQL, which has built-in connection pooling.

Question 8

A gaming company uses Cloud Spanner to store player profiles and game state. The database has a table 'Players' with a monotonically increasing integer primary key. During a global launch event, write latency spikes and throughput drops. The issue is traced to hotspotting. Which schema change should the team implement to mitigate this?

Accepted Answer

Add a hash prefix to the primary key by salting the player ID.. Adding a hash prefix to the monotonically increasing integer primary key distributes writes across multiple Cloud Spanner splits, preventing hotspotting. Without this, sequential player IDs cause all new inserts to target the same split, leading to write contention and throughput drops during high-volume events like a global launch.

Answer

Change primary key to use a combination of timestamp and player ID.

Answer

Convert the primary key to a UUID stored as bytes.

Answer

Create a parent-child interleaved table structure.

Question 9

A retail company is designing a new inventory management system on Cloud Spanner. They need to ensure high write throughput for order processing. Which two schema design practices help avoid write hotspots? (Choose TWO.)

Accepted Answer

Avoid using a monotonically increasing primary key.. Option B is correct because Cloud Spanner splits data by primary key ranges, and a monotonically increasing key (such as a timestamp or auto-incrementing integer) causes all new writes to land on the last split, creating a hotspot; avoiding such keys spreads inserts across splits. Option E is correct because adding a hash prefix to the primary key randomizes key distribution, so consecutive inserts are scattered across many splits and write throughput scales horizontally. Option A is not correct here because secondary indexes add write amplification and index hotspots rather than preventing hotspots on the base table. Option C is not correct because storing everything in one non-interleaved table does nothing to distribute key ranges and can worsen contention. Option D is not correct because foreign keys enforce referential integrity and do not affect how rows are distributed across splits.

Answer

Create secondary indexes on frequently queried columns.

Answer

Store all data in a single table with no interleaving.

Answer

Use foreign keys to enforce referential integrity.

Question 10

A company is migrating a MySQL OLTP database to Bigtable for a time-series application. The current schema uses a relational model with normalized tables. Which two actions should the team take when designing the Bigtable schema? (Choose TWO.)

Accepted Answer

Denormalize the data into a single wide-column table.. Option A is correct because Bigtable is a wide-column NoSQL store with no joins, so the normalized relational model must be denormalized into a single wide-column table where related data is stored together for efficient reads. Option C is correct because Bigtable row keys are sorted lexicographically and writes to sequential keys (such as timestamps) create hotspots; salting the row key (e.g., prepending a hash or bucket prefix) distributes writes evenly across nodes. Option B is incorrect because Bigtable only supports single-row atomic operations, not multi-row ACID transactions like a relational database. Option D is incorrect because Bigtable does not support secondary indexes; query patterns must be served by row key design or by maintaining separate index tables. Option E is incorrect because normalizing to reduce duplication is a relational design practice that would require joins, which Bigtable cannot perform.

Answer

Maintain transactional integrity using Bigtable transactions.

Answer

Create secondary indexes on timestamp columns.

Answer

Normalize the schema to reduce data duplication.

Question 11

An online payment processing system uses Cloud SQL for MySQL with a 1 TB database. The system experiences high write throughput (~5000 writes/sec) and needs sub-10ms latency. The current instance has 8 vCPUs and 32 GB RAM. Which two metrics would indicate that the instance needs a larger tier? (Choose TWO.)

Accepted Answer

CPU utilization consistently above 80%.. Option B is correct because sustained CPU utilization above 80% on an 8-vCPU Cloud SQL instance indicates the vCPUs are saturated by the ~5000 writes/sec workload, so the instance cannot keep up and a larger tier with more vCPUs is needed to restore sub-10ms latency. Option C is correct because disk IOPS consistently hitting the instance's I/O limit means the storage throughput ceiling is the bottleneck; on Cloud SQL for MySQL, IOPS capacity scales with instance size and disk type, so moving to a larger tier raises the IOPS limit and relieves the write bottleneck. Option A is not a scaling signal because 50% memory usage leaves ample buffer pool headroom, well below the threshold where RAM pressure would force disk reads. Option D is not a problem indicator since 5 ms average query latency is already within the required sub-10ms target. Option E is not a bottleneck because 100 Mbps is trivial relative to the multi-Gbps network bandwidth available to an 8-vCPU Cloud SQL instance.

Answer

Memory usage is 50% of 32 GB.

Answer

Average query latency of 5 ms.

Answer

Network traffic is 100 Mbps.

Question 12

A startup is building a mobile app with a relational database backend. They expect moderate traffic and need strong consistency, automatic backups, and point-in-time recovery. Which two Google Cloud database services meet these requirements? (Choose TWO.)

Accepted Answer

Cloud Spanner. Cloud Spanner (B) is correct because it is a fully managed relational database that provides strong consistency (external consistency) via TrueTime, and it supports automatic backups and point-in-time recovery (PITR) with configurable retention windows, making it suitable for a relational backend with moderate-to-high traffic. Cloud SQL (C) is correct because it is a managed relational database (MySQL, PostgreSQL, SQL Server) offering strong consistency, automated backups, and point-in-time recovery through binary logging/transaction logs, which fits a startup's moderate-traffic mobile app backend. Firestore (A) is a NoSQL document database, so it does not meet the relational requirement. Cloud Bigtable (D) is a NoSQL wide-column store optimized for high-throughput analytics and does not provide relational semantics or the required PITR/backup model for this use case. BigQuery (E) is a serverless analytics data warehouse for OLAP workloads, not a transactional relational backend.

Answer

Firestore

Answer

Cloud Bigtable

Answer

BigQuery

Question 13

A company runs a financial analytics platform on BigQuery. They need to reduce query costs for frequent, predictable queries. Which three strategies can help? (Choose THREE.)

Accepted Answer

Use BI Engine to cache results of frequent queries.. Option A is correct because BI Engine is an in-memory analysis service that accelerates and caches frequently accessed data, reducing bytes processed and thus query costs for predictable, repeated queries. Option B is correct because materialized views pre-compute and store common aggregations, so recurring queries read the smaller pre-aggregated result instead of scanning the full base tables, lowering cost. Option D is correct because clustering tables on frequently filtered columns co-locates related data in storage blocks, so queries scan fewer bytes when filtering on those columns, reducing on-demand costs. Option C is not among the marked answers: partitioning by ingestion time prunes scans only when queries filter on the ingestion-time column, which is not stated as the frequent filter pattern here. Option E is not marked correct because DML statements (INSERT/UPDATE/DELETE/MERGE) are for modifying data, not a cost-reduction mechanism, and manually pre-aggregating with DML adds processing cost rather than reducing it.

Answer

Partition tables by ingestion time.

Answer

Use DML statements to pre-aggregate data.

Question 14

A team is designing a Bigtable schema for a real-time fraud detection system. The row key includes device ID and timestamp. They need to avoid hotspotting during high write periods. Which two row key design patterns help achieve this? (Choose TWO.)

Accepted Answer

Add a hash prefix or salt to the beginning of the row key.. Option A is correct because adding a hash prefix or salt to the beginning of the row key distributes writes across multiple tablets, preventing sequential device IDs or timestamps from concentrating load on a single tablet. Option E is correct because hashing the device ID randomizes the row key prefix, which spreads write traffic uniformly across the keyspace and avoids hotspotting during high write periods. Option B is not correct because placing frequently filtered fields first optimizes read queries, not write distribution, and can actually worsen hotspotting if those fields have low cardinality. Option C is not correct because reverse timestamp only changes the ordering of time-series data for efficient recent-data reads; it does not randomize the leading key component and can still cause hotspots. Option D is not correct because using device ID as the sole prefix groups all writes for a device together, and if device IDs are sequential or skewed, this creates hotspots rather than avoiding them.

Answer

Put the most frequently filtered fields first in the row key.

Answer

Use reverse timestamp to keep recent data first.

Answer

Use device ID as the sole row key prefix to group data by device.

Question 15

A multinational retailer is designing a Google Cloud database tier for a new order-capture service. The service must survive the loss of an entire Google Cloud region with no data loss and automatic failover, while still enforcing ACID transactions across order and inventory tables. The team wants to minimize operational overhead and avoid manual database administration. Which database service should they choose?

Accepted Answer

Cloud Spanner with a multi-region instance configuration. The requirement for surviving a full region loss with zero data loss and automatic failover, combined with ACID transactions across relational tables, points to a globally distributed, strongly consistent, fully managed relational database. Cloud Spanner multi-region configurations provide synchronous cross-region replication, automatic failover, and external consistency, which together satisfy all constraints without manual database administration.

Answer

Bigtable with a multi-cluster routing policy across regions

Answer

Firestore in Native mode with multi-region location

Answer

Cloud SQL for PostgreSQL with a cross-region read replica promoted manually during an outage