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

Question 1

A financial services company runs a high-frequency trading application that requires strong consistency, horizontal scalability, and low-latency transactions across multiple regions. Which Google Cloud database should they choose?

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

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, strongly consistent, horizontally scalable relational database service designed for mission-critical applications like trading. Cloud SQL is not multi-region, Bigtable does not support SQL/ACID, and Firestore is not relational.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 2

A startup needs a database for a global user base with low-latency reads and writes, strong consistency, and the ability to scale horizontally without downtime. They anticipate variable traffic. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that provides strong consistency (external consistency) at global scale with horizontal scaling and no downtime. It uses TrueTime and Paxos to synchronize across regions, offering 99.999% availability for multi-region configurations. Its ability to scale reads and writes linearly while maintaining ACID transactions makes it ideal for a global user base with variable traffic.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 3

You are running Cloud Bigtable for time-series analytics. Each row represents a metric and uses a row key of format 'metricID#timestamp' (e.g., 'cpu_usage#2023-08-01T00:00:00Z'). You notice that writes are concentrated on a small number of nodes. What is the most effective way to distribute writes more evenly?

Accepted Answer

Add a hash prefix of the metricID to the row key. Cloud Bigtable distributes data across nodes based on row key ranges, and rows with similar or sequential row keys land on the same tablet server, creating hotspots. Adding a hash prefix of the metricID to the row key scatters writes across many tablets, distributing load evenly across nodes. This is the canonical Bigtable row key design pattern for write-heavy time-series workloads.

Answer

Use a different column family for each metric

Answer

Increase the number of nodes

Answer

Reverse the timestamp in the row key

Question 4

A company is migrating a large on-premise Hadoop workload to Google Cloud. The data is stored in HBase and consists of time-series logs with 10 PB of data. They need a fully managed NoSQL solution with high throughput and low latency. Which migration path should they choose?

Accepted Answer

Use Cloud Bigtable and the HBase client. Cloud Bigtable is the fully managed, scalable NoSQL database that is compatible with HBase API, making migration straightforward. Cloud Spanner is relational, not the best for time-series logs. BigQuery is for analytics, not real-time. Firestore is document-based and not suited for petabyte-scale logs.

Answer

Migrate HBase tables to Cloud Spanner using Dataflow

Answer

Export data to BigQuery using Storage Transfer Service

Answer

Re-architect as Firestore collections

Question 5

Which database service should you use if your workload requires complex JOINs, ACID transactions, and you want to avoid operational overhead of managing patching and backups?

Accepted Answer

Cloud SQL. Cloud SQL is the correct choice because it is a fully managed relational database service that supports complex JOINs, ACID transactions, and handles patching, backups, and replication automatically. It is designed for traditional relational workloads that require strong consistency and SQL support without operational overhead.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud Storage

Question 6

A company runs an e-commerce website on Cloud SQL. They want to scale read traffic without impacting write performance and need high availability across zones. Which configuration should they use?

Accepted Answer

Use a regional Cloud SQL instance with automatic failover and add read replicas. Cloud SQL offers read replicas for scaling read traffic and regional (multi-zone) instances for high availability. Using a regional instance with automatic failover provides HA; adding read replicas offloads reads. A cross-region replica adds latency, and a single zone with increased tier does not provide HA.

Answer

Create a cross-region replica and use it for reads

Answer

Increase the machine tier of the existing instance

Answer

Migrate to Cloud Spanner for automatic read scaling

Question 7

A team is migrating a large on-premise Oracle database to Cloud SQL for PostgreSQL. They need to minimize downtime and ensure data consistency. Which migration approach is recommended?

Accepted Answer

Use Database Migration Service with continuous replication. Database Migration Service (DMS) with continuous replication performs an initial full load and then continuously replicates ongoing changes from the source Oracle database to Cloud SQL for PostgreSQL, allowing a short cutover with minimal downtime and consistent data. This is the recommended approach for large, live migrations where downtime must be minimized.

Answer

Use pg_dump and pg_restore

Answer

Export data as CSV, import to Cloud SQL

Answer

Create a Cloud SQL read replica from on-premise

Question 8

A data analyst needs to run ad-hoc SQL queries on a large dataset stored in Google Cloud Storage (CSV files). They do not want to manage any infrastructure. Which service should they use?

Accepted Answer

BigQuery. BigQuery is a fully managed, serverless data warehouse that can query external data directly from Google Cloud Storage using external tables or the BigQuery Storage API, with no infrastructure to manage. It supports standard SQL for ad-hoc analysis and scales automatically to handle large datasets. This makes it the correct choice for a data analyst who wants to query CSV files in GCS without provisioning or managing servers.

Answer

Dataproc

Answer

Cloud Spanner

Answer

Cloud SQL

Question 9

An engineer is designing a Cloud SQL database for an e-commerce platform. They need to store product inventory and order history. Which schema normalization level is recommended for OLTP to avoid data anomalies?

Accepted Answer

Third Normal Form (3NF). Third Normal Form (3NF) is the recommended normalization level for OLTP systems to avoid data anomalies while maintaining practical query performance. 3NF eliminates transitive dependencies, ensuring that non-key attributes depend only on the primary key, which reduces redundancy and update anomalies. While higher normal forms exist, 3NF is a standard target for transactional databases.

Answer

Second Normal Form (2NF)

Answer

First Normal Form (1NF)

Answer

Denormalized schema

Question 10

An organization needs a fully managed, globally distributed relational database with strong consistency and horizontal scaling for a multi-region application. Which service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is Google's fully managed, globally distributed relational database that provides strong consistency (external consistency) and horizontal scaling via TrueTime and Paxos-based replication. It is the only option that combines relational semantics, global distribution, strong consistency, and horizontal scale-out. Bigtable and Firestore are NoSQL, and Cloud SQL is regional and vertically scaled.

Answer

Bigtable

Answer

Firestore

Answer

Cloud SQL

Question 11

A company is designing a data warehouse for analytics. They need to store structured and semi-structured data, support SQL queries with sub-second performance on petabytes, and integrate with Data Studio. Which service should they consider?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse that supports SQL queries on petabyte-scale datasets with sub-second performance and integrates with Data Studio. Cloud Bigtable is a NoSQL database and does not support SQL. Cloud Spanner is designed for OLTP workloads, not analytics. Dataproc is a managed Hadoop/Spark service, not a data warehouse. Cloud SQL is for smaller relational datasets and cannot handle petabytes.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Answer

Dataproc

Answer

Cloud SQL

Question 12

An engineer needs to migrate a MySQL database to Cloud SQL with minimal downtime. Which TWO steps should be part of the migration plan? (Choose 2)

Accepted Answer

Verify that the source MySQL version is compatible with Cloud SQL. Option C is correct because Cloud SQL supports only specific MySQL major/minor versions, so verifying that the source MySQL version is compatible with the target Cloud SQL version is a mandatory prerequisite before any migration can succeed. Option D is correct because Database Migration Service (DMS) provides continuous replication from the source MySQL to Cloud SQL, allowing a cutover with minimal downtime rather than a one-time dump-and-load. Option A is not appropriate because a Cloud SQL read replica can only be created from another Cloud SQL instance, not directly from an external/on-premises MySQL source. Option B is not suitable for minimal downtime because mysqldump plus import requires taking the source offline or accepting data loss during the dump window. Option E is unnecessary because DMS handles connectivity (for example via IP allowlisting or a reverse SSH tunnel) and a Cloud VPN tunnel is not a required migration step.

Answer

Create a Cloud SQL read replica from the source

Answer

Perform a mysqldump and import the dump into Cloud SQL

Answer

Set up a Cloud VPN tunnel between on-premise and GCP

Question 13

A startup is building a ride-sharing application that requires globally distributed, strongly consistent transactions for ride matching and payments. The database must scale horizontally and provide low-latency reads and writes. Which Google Cloud database should they use?

Accepted Answer

Cloud Spanner. Cloud Spanner is Google's globally distributed, horizontally scalable relational database that provides external consistency (strong consistency) across regions using TrueTime and Paxos. It supports ANSI SQL, ACID transactions, and low-latency reads/writes at global scale, making it the only option that satisfies all three requirements: global distribution, strong consistency, and horizontal scaling.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 14

A data engineering team needs to run complex analytical queries on terabytes of data stored in Cloud Storage. The queries are ad-hoc and require scanning large portions of the dataset. The team needs a serverless solution that optimizes for cost by charging only for the data processed. Which Google Cloud service should they use?

Accepted Answer

BigQuery. BigQuery is a serverless data warehouse that uses columnar storage and charges for the data scanned by queries. It is ideal for ad-hoc analytical queries on large datasets.

Answer

Dataproc

Answer

Cloud SQL

Answer

Cloud Spanner

Question 15

An e-commerce platform uses Cloud Spanner as its database. The orders table has a monotonically increasing order_id as the primary key, and the team observes high write latency during peak hours. Which design change would BEST distribute write load across nodes?

Accepted Answer

Change the primary key to a UUID string. Cloud Spanner physically orders rows by primary key, so a monotonically increasing order_id concentrates all new inserts at the end of the key space, creating a hot spot on a single split (and thus a single node). Changing the primary key to a UUID string (or a hash-prefixed key) randomizes insert distribution across splits, spreading write load across many nodes and eliminating the hot spot.

Answer

Add a secondary index on order_id

Answer

Increase the number of nodes

Answer

Use interleaving with a parent table