PCDOE Design and Plan Database Solutions Practice Test 7 — 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?

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

A company is migrating a large on-premises MySQL database to Cloud SQL. The source database supports heavy reporting queries that scan millions of rows and join multiple tables. The team wants to minimize downtime and avoid performance degradation during migration. Which migration approach should they use?

Accepted Answer

Set up Database Migration Service (DMS) with continuous replication from the source to Cloud SQL, then cut over when replication lag is minimal. Database Migration Service (DMS) with continuous replication allows near-zero downtime migration by replicating changes from the source to Cloud SQL while the source remains operational. Export/import methods typically require downtime. Replicating to BigQuery is not relevant for Cloud SQL migration. Just changing instance size does not handle data migration.

Answer

Use gcloud sql import command to import a CSV export while the source database remains in use

Answer

Create a BigQuery transfer service to move data from MySQL to Cloud SQL

Answer

Use mysqldump to export the data and import it into Cloud SQL during a maintenance window

Question 3

A startup is building a real-time analytics dashboard that ingests 500,000 events per second and needs to query the last hour of data with sub-second latency. The data has a high write volume and the query pattern is time-range scans. Which Google Cloud database is most appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is the correct choice because it is a fully managed, scalable NoSQL database designed for high-throughput writes and low-latency time-series data access. It supports sub-second latency on time-range scans by storing data in sorted order by row key, making it ideal for ingesting 500,000 events per second and querying the last hour of data.

Answer

Cloud Spanner

Answer

Cloud SQL (PostgreSQL)

Answer

BigQuery

Question 4

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. The max_connections in MySQL is typically set based on memory: max_connections = RAM_MB/16. With 15GB RAM = 15360 MB, max_connections = 15360/16 = 960. The application pool of 500 should be fine, but if the error occurs, it might be due to other factors. However, the most common fix is to increase the max_connections parameter via a flag. The correct step is to increase the 'max_connections' flag in Cloud SQL. Reducing connections or using a connection pool won't help if the limit is reached. Increasing vCPUs does not directly change max_connections.

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 5

A company wants to run complex analytical queries on terabytes of data with sub-second response times. The data is structured and stored in Cloud Storage as Parquet files. They need a serverless solution that can query the data directly without loading it into a database. Which service should they use?

Accepted Answer

BigQuery. BigQuery is the correct choice because it is a serverless, fully managed data warehouse that supports querying structured data directly from Cloud Storage using external tables, without requiring data loading. It can handle terabytes of data with sub-second response times via its columnar storage, automatic scaling, and BI Engine for acceleration.

Answer

Cloud Dataproc

Answer

Cloud Bigtable

Answer

Cloud SQL

Question 6

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). Using a hash prefix on the first part of the primary key (e.g., hash of UserId) helps distribute writes across splits, avoiding hotspots. Using a UUID for PostId is good but UserId ordering still causes hotspots. Adding a timestamp as a second part doesn't help. Interleaving with User is fine but doesn't fix the hotspot issue.

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 7

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?

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Cloud SQL

Answer

Firestore

Question 8

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]. Option B is 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 9

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. Option A is correct because 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 10

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. Option C is correct because 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 11

You have a Cloud Spanner instance and need to add a new column and a secondary index to an existing table. The table is heavily used by production traffic. Which approach minimizes downtime and performance impact?

Accepted Answer

Use 'gcloud spanner databases ddl update' to add the column and create the index concurrently. Spanner supports online schema changes: you can add columns and indexes without downtime. The gcloud command 'gcloud spanner databases ddl update' applies DDL changes in the background without locking the table. Dropping and recreating the table causes downtime. Creating a new table and copying data requires application changes and downtime.

Answer

Export the table to Avro, modify the schema, import back into a new table, then rename

Answer

Create a new table with the new schema, use a temporary application to dual-write and backfill, then switch

Answer

Drop the table and recreate it with the new schema, then restore from backup

Question 12

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 13

A company is designing a Cloud Spanner database for a global inventory system. The application runs OLTP transactions on inventory levels and also needs to generate daily reports that scan the entire inventory table. Which two approaches will reduce the impact of analytical queries on transactional performance?

Accepted Answer

Export data to BigQuery daily for reporting. Option A is correct because exporting data to BigQuery offloads analytical workloads from Cloud Spanner entirely, preventing large scans from competing for Spanner's CPU and memory resources. BigQuery is purpose-built for analytical queries on large datasets, so daily exports ensure transactional performance remains unaffected by reporting queries.

Answer

Use strong reads for all queries to ensure consistency

Answer

Increase the number of processing units to handle both workloads

Answer

Use interleaved tables for inventory items

Question 14

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 Cloud Bigtable is a wide-column NoSQL database that does not support joins. To efficiently query all orders for a user, you must denormalize the one-to-many relationship by storing orders as multiple columns (e.g., order_1, order_2) or as a serialized JSON/protobuf column within the user's row. This avoids the need for a separate lookup or join, enabling single-row reads for the user's orders.

Answer

Maintain 3NF to avoid data duplication

Answer

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

Question 15

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. Creating read replicas in Cloud SQL offloads read traffic from the primary instance, allowing complex aggregate queries to run on replicas without impacting write performance. Since the reports can tolerate up to 5 minutes of staleness, the asynchronous replication lag inherent in Cloud SQL read replicas is acceptable, making this a cost-effective and scalable solution.

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