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

Question 1

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?

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

Accepted Answer

BigQuery. BigQuery is a serverless data warehouse designed for large-scale analytics with fast SQL queries. It handles terabytes to petabytes, charges by query usage, and is ideal for OLAP workloads.

Answer

Cloud SQL for MySQL

Answer

Cloud Spanner

Answer

Bigtable

Question 2

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 3

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 4

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 a fully managed PostgreSQL-compatible database designed for hybrid transactional and analytical processing (HTAP). It includes a built-in columnar engine that accelerates analytical queries on transactional data without separate ETL or duplication.

Answer

Bigtable

Answer

BigQuery

Answer

Cloud SQL for PostgreSQL

Question 5

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. Using a reverse timestamp allows the most recent data to be at the beginning of the row key, making scans for the latest reading efficient. Field promotion ensures that device_id comes first in the row key, enabling efficient prefix scans for all readings of a device. Salting is not needed because device_id already distributes writes if there are many devices.

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

Question 6

An organization is designing a Cloud Spanner schema for a social media application. The application frequently queries for all posts by a specific user, and also updates the number of likes on a post. To ensure high performance and avoid hotspots, which TWO schema design principles should the team apply? (Choose two.)

Accepted Answer

Interleave the Post table under the User table using UserID as the first part of the primary key. Interleaving the Post table under the User table colocates posts with their user, making queries for a user's posts efficient by reducing distributed reads. Using a UUID for the post ID ensures writes are distributed across the cluster, avoiding hotspots from sequential keys like timestamps.

Answer

Use a secondary index on the Post table for UserID queries

Answer

Denormalize the like count into the User table to avoid joins

Answer

Use a monotonically increasing integer as the post ID to simplify indexing

Question 7

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 8

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 9

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 10

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 11

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 12

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 13

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 supports SQL queries on semi-structured JSON data and can handle high write rates via streaming inserts (up to 100,000 rows per second per project). Cloud Bigtable does not support SQL natively, Cloud SQL cannot handle the write throughput, and Firestore is limited in write capacity for this volume.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 14

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 15

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