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

Question 1

A company wants to store and analyze time-series metrics from thousands of servers. The data is write-heavy with occasional reads of recent data. They need low-latency writes and the ability to scan large ranges later. Which Google Cloud database is MOST appropriate?

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

Accepted Answer

Cloud Bigtable. Bigtable is designed for time-series data, high write throughput, and efficient range scans. It can handle large volumes of time-series data with low latency.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud SQL

Question 2

A team is migrating a relational database to Cloud Bigtable. They need to design a row key that distributes write traffic evenly across nodes. The original table had a composite primary key of customer_id and order_date. Which row key design is BEST for high write throughput?

Accepted Answer

hash(customer_id) # customer_id # order_date. For Cloud Bigtable, row keys should be designed to distribute writes evenly across nodes. Using a hash of the customer_id as a prefix ensures that sequential customer IDs are spread across the keyspace, avoiding hotspots. Including the original customer_id and order_date after the hash maintains uniqueness and allows for efficient queries by customer and date. This design achieves high write throughput.

Answer

customer_id # order_date

Answer

order_date # customer_id

Answer

customer_id only

Question 3

An organization uses Cloud Spanner and needs to add a new column to an existing table without downtime. The table has billions of rows and is heavily used. What is the recommended approach to add the column?

Accepted Answer

Use ALTER TABLE ADD COLUMN statement. Cloud Spanner supports online schema changes, including ALTER TABLE ADD COLUMN, without downtime. Adding a nullable column is a metadata-only operation that completes quickly even on tables with billions of rows. Spanner's schema change mechanism is designed for zero-downtime DDL, so no table copy or backup/restore is needed.

Answer

Create a new table with the column, then copy data and rename

Answer

Add the column using gcloud command and set a downtime window

Answer

Backup the database, add column, then restore

Question 4

A small business runs a MySQL OLTP database for their inventory management system. They need high availability with automatic failover and regional disaster recovery. Which Google Cloud database service meets these requirements with minimal operational overhead?

Accepted Answer

Cloud SQL with HA and cross-region read replicas. Cloud SQL for MySQL with high availability (HA) configuration provides automatic failover within a region and can be configured with cross-region replicas for disaster recovery.

Answer

Cloud Spanner

Answer

Cloud Bigtable

Answer

Compute Engine with self-managed MySQL

Question 5

A data analyst needs to run a one-time SQL query on a dataset stored in CSV files in Cloud Storage. The query will scan the entire dataset, and the analyst wants to minimize cost. Which Google Cloud service should they use?

Accepted Answer

BigQuery. BigQuery is the most cost-effective choice for a one-time SQL query on CSV files in Cloud Storage because it supports querying external data directly from Cloud Storage using external tables, and you only pay for the query processing (on-demand pricing). This avoids the need to load data into a database or provision infrastructure.

Answer

Cloud SQL

Answer

Dataproc

Answer

Cloud Spanner

Question 6

A company is planning to use Cloud Spanner for a new global application. They estimate a peak write throughput of 10,000 mutations per second. What is the minimum number of processing units (PUs) required, given that each PU supports up to 2000 mutations/second?

Accepted Answer

5 processing units. Cloud Spanner's write throughput scales with the number of processing units (PUs), with each PU supporting approximately 2,000 mutations per second. To sustain a peak of 10,000 mutations per second, you divide 10,000 by 2,000, which equals 5 PUs. Therefore, 5 processing units is the minimum required to meet the stated write throughput.

Answer

1 processing unit

Answer

100 processing units

Answer

10 processing units

Question 7

A manufacturing company uses Cloud SQL for PostgreSQL for its inventory system. The database has grown and now experiences high read latency. The team wants to improve read performance without changing application code. Which THREE actions should they consider? (Choose 3)

Accepted Answer

Add read replicas. Option A (Add read replicas) is correct because Cloud SQL for PostgreSQL read replicas offload read-only traffic from the primary instance, reducing contention and latency for read-heavy inventory queries without requiring application code changes beyond pointing reads at the replica endpoint. Option B (Create appropriate indexes based on query patterns) is correct because missing or poorly designed indexes force sequential scans over large tables, and adding targeted B-tree indexes on frequently filtered or joined columns directly reduces read latency at the query level. Option E (Increase the instance's memory allocation) is correct because a larger Cloud SQL tier increases the shared_buffers and OS page cache, allowing more of the working set to be served from memory instead of disk, which lowers read latency. Option C (Migrate to Cloud Spanner) is not appropriate here because it is a major re-architecture requiring schema and application changes, contradicting the 'without changing application code' constraint. Option D (Implement database sharding) is also not suitable because sharding requires application-level routing logic and schema changes, adding significant complexity rather than a no-code performance improvement.

Answer

Migrate to Cloud Spanner

Answer

Implement database sharding

Question 8

A financial services company is migrating a legacy on-premises OLTP application to Google Cloud. The application requires high transaction rates (thousands per second), strict ACID compliance, and the ability to scale horizontally across multiple regions with strong consistency. Which Google Cloud database service should the company choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that combines horizontal scalability across regions with strong consistency and full ACID transaction support at global scale. It uses TrueTime and Paxos to provide external consistency, making it suitable for high-throughput OLTP workloads requiring thousands of transactions per second. Unlike other options, Spanner natively supports multi-region configurations with synchronous replication and strong consistency guarantees.

Answer

Bigtable

Answer

Cloud SQL for PostgreSQL

Answer

Firestore

Question 9

A startup needs a database for its new web application that will serve a global user base. The application requires low-latency reads and writes (single-digit milliseconds), strong consistency, and the ability to handle high concurrency (thousands of transactions per second). Which Google Cloud database service should the startup choose?

Accepted Answer

Cloud Spanner. Cloud Spanner offers global distribution, strong consistency, and low-latency transactions at high concurrency. It is designed for globally scaled OLTP workloads.

Answer

Cloud SQL for MySQL

Answer

Bigtable

Answer

Firestore

Question 10

A company is designing a schema for Cloud Bigtable to store user sessions. Access patterns: (1) read all sessions for a given user ID, and (2) read a specific session by session ID. The row key should support both patterns efficiently. Which row key design is MOST appropriate?

Accepted Answer

Use user_id#session_id as the row key. Using user_id#session_id as the row key allows efficient range scans for all sessions of a given user (prefix scan on user_id) and direct point reads for a specific session (exact row key lookup). This design leverages Bigtable's lexicographic sorting, where rows with the same user_id prefix are stored contiguously, enabling fast retrieval of all sessions for a user.

Answer

Use session_id as the row key and store user_id as a column

Answer

Use a hash of user_id as row key prefix and session_id as suffix

Answer

Use user_id as the row key and store multiple session columns

Question 11

A company is running analytical queries on large datasets (terabytes) that involve aggregations, joins, and window functions. The data is updated daily via batch loads. The queries must complete in seconds to minutes. Which Google Cloud database service is BEST suited for this workload?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable enterprise data warehouse designed for analytical workloads on massive datasets. It uses columnar storage and a distributed query engine (Dremel) to execute complex aggregations, joins, and window functions efficiently over terabytes of data, returning results in seconds to minutes. Daily batch loads are easily handled via native ingestion or the Storage Write API, and separation of storage and compute allows independent scaling.

Answer

Cloud SQL for MySQL

Answer

Cloud Spanner

Answer

Bigtable

Question 12

A team is designing a Cloud Spanner schema for an e-commerce platform. They have 'Customer' and 'Order' tables and want to ensure that queries for all orders of a specific customer are efficient. Which schema design approach should they use?

Accepted Answer

Interleave the 'Order' table under the 'Customer' table with CustomerID as the first part of Orders' primary key. Spanner supports interleaving tables, where child rows are stored physically near the parent row. Interleaving Orders under Customers using the CustomerID as the first part of the primary key in Orders makes queries for all orders of a customer efficient by colocating related data.

Answer

Use a single table with denormalized customer and order data

Answer

Use a secondary index on CustomerID in the Order table

Answer

Create a separate 'Order' table with a foreign key to Customer

Question 13

A company is using Cloud Spanner and needs to add a new column to an existing table that has billions of rows. The column must have a default value of 0. The team is concerned about downtime. Which approach should they take to add the column with zero downtime?

Accepted Answer

Use ALTER TABLE ADD COLUMN with DEFAULT 0; the operation is online and non-blocking. Cloud Spanner supports online schema changes that are non-blocking. Adding a column with a DEFAULT value is an online operation that does not block reads or writes, and does not require copying data. The new column is added with the default value for existing rows at read time, not by backfilling.

Answer

Take the application offline, run ALTER TABLE to add the column, then bring the application back online

Answer

Create a new table with the column, copy data using Dataflow, then rename the tables

Answer

Add the column without a default, then run a batch update to set the default value

Question 14

An organization needs to run both transactional (OLTP) and real-time analytical (OLAP) queries on the same dataset without data duplication. The dataset is moderately large (a few terabytes). Which Google Cloud database service is MOST appropriate for this HTAP workload?

Accepted Answer

AlloyDB. AlloyDB is Google Cloud's PostgreSQL-compatible, fully managed database designed for HTAP workloads. It uses a columnar engine that automatically accelerates analytical queries on the same data used for transactional processing, eliminating the need for ETL or data duplication. Its architecture separates compute and storage, allowing independent scaling for OLTP and OLAP, and it supports real-time analytics with minimal latency. For a few terabytes, AlloyDB provides the best balance of transactional throughput and analytical performance without duplicating data.

Answer

Bigtable

Answer

BigQuery

Answer

Cloud SQL for PostgreSQL

Question 15

A company is designing a Cloud Bigtable schema for time-series data. The data is written by millions of devices every second. The query patterns are: (1) retrieve the most recent reading for a specific device, (2) retrieve all readings for a device in a time range. Which TWO row key design techniques should the team use to optimize for these patterns? (Choose two.)

Accepted Answer

Use field promotion: put device_id as the first component of the row key. Option B is correct because field promotion places the high-cardinality device_id as the leading row key component, which co-locates all readings for a given device in a contiguous key range so that both the latest-reading lookup and the time-range scan for a specific device can be served by a single efficient range scan. Option C is correct because using a reverse timestamp (MAX_TIMESTAMP - timestamp) as the second component orders each device's readings so the most recent reading sorts first, letting the 'most recent reading' query be satisfied by reading just the first row of that device's prefix rather than scanning to the end of the range. Option A is not appropriate here because salting/hash prefixes would scatter a single device's readings across many tablets, breaking the contiguous range needed for the per-device time-range query. Option D is wrong because a monotonically increasing timestamp as the first component causes sequential write hotspots and does not group data by device. Option E is wrong because column family layout does not address row key ordering or query access patterns.

Answer

Use salting (hash prefix) to distribute writes

Answer

Use a monotonically increasing timestamp as the first part of the row key

Answer

Store the entire row as a single column family to reduce overhead