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

Question 1

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?

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

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) performs an initial full dump-and-load followed by continuous change data capture (CDC) replication from the source MySQL to Cloud SQL, allowing the source to remain fully operational during migration. Because replication is continuous, the cutover can be scheduled when replication lag is near zero, minimizing downtime and avoiding the performance hit of a cold dump/restore. This is the recommended approach for minimizing downtime on large, actively used MySQL databases.

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 2

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 3

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 4

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. Cloud Spanner supports online schema changes via DDL statements executed with 'gcloud spanner databases ddl update', which apply the schema modification without taking the table offline. Adding a column is a metadata-only operation, and creating a secondary index can be done with the index backfill running in the background while production traffic continues. This approach avoids data migration, dual-writes, or table recreation, minimizing both downtime and performance impact.

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 5

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 daily offloads the full-table analytical scans to a dedicated OLAP engine, so the Cloud Spanner instance only serves OLTP transactions and is not burdened by reporting queries. Option C is correct because Cloud Spanner read-only replicas can serve analytical reads without competing with transactional reads/writes on the primary region, and they provide a dedicated, scalable read path for reporting. Option B is incorrect because strong reads do not separate workloads; they add consistency overhead and can increase latency and contention for both OLTP and reporting. Option D is incorrect because adding processing units increases capacity but does not isolate analytical scans from transactional traffic, so contention and latency spikes can still affect OLTP. Option E is incorrect because interleaved tables optimize parent-child data locality and joins, not workload isolation for full-table analytical scans.

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 6

A company wants to migrate their on-premises PostgreSQL database to Cloud SQL. The database currently runs mixed workloads: OLTP with heavy writes and occasional complex analytical queries. They want to avoid performance impact on the transactional workload. Which approach should they take?

Accepted Answer

Create a read replica of the Cloud SQL instance and run analytical queries against the replica.. To avoid performance impact on transactional workloads, a read replica of the Cloud SQL instance should be created and analytical queries directed to it. Read replicas in Cloud SQL are separate instances that replicate data from the primary asynchronously, providing isolation so that heavy analytical queries don't compete for resources with OLTP operations. This is a recommended best practice for workload separation in Cloud SQL for PostgreSQL.

Answer

Migrate to Cloud Spanner to handle both workloads using its analytics interface.

Answer

Use Cloud SQL with the 'analytics' tier enabled.

Answer

Use the same Cloud SQL instance but schedule analytical queries during off-peak hours.

Question 7

A company uses Bigtable for time-series data with a row key format: 'deviceID#timestamp'. They notice write hotspotting on a few devices that generate high volumes of data. How should they redesign the row key to distribute writes evenly?

Accepted Answer

Add a random salt prefix to the row key: 'random_number#deviceID#timestamp'. Bigtable row keys are sorted lexicographically, so a row key starting with 'deviceID' causes all writes for a hot device to land on the same tablet, creating a hotspot. Adding a random salt prefix (e.g., a random number) distributes those writes across multiple tablets, spreading the load. This is the canonical Bigtable recommendation for write-heavy time-series workloads with hot keys.

Answer

Reverse the timestamp: 'deviceID#reverse_timestamp'

Answer

Use a monotonically increasing integer for the row key.

Answer

Store all data in a single column family and use column qualifiers for timestamps.

Question 8

A team is migrating a relational database to Bigtable. The existing schema uses foreign keys to join orders, customers, and products. Which data model approach is most suitable for Bigtable?

Accepted Answer

Denormalize orders, customers, and products into a single table with a composite row key.. Bigtable is a wide-column NoSQL database optimized for high-throughput, low-latency access, and it does not support SQL-style joins or secondary indexes in the traditional relational sense. Denormalizing orders, customers, and products into a single table with a composite row key (e.g., customer_id#order_id#product_id) allows all related data to be co-located and retrieved with a single row scan, eliminating the need for joins and aligning with Bigtable's key-value access pattern.

Answer

Store each entity in a separate table and use secondary indexes.

Answer

Use Cloud SQL as a lookup table for joins.

Answer

Keep the normalized structure and use MapReduce to perform joins.

Question 9

A startup needs a fully managed relational database for their e-commerce platform with high availability, automatic failover, and read replicas. They expect moderate traffic and want to minimize operational overhead. Which Google Cloud service should they use?

Accepted Answer

Cloud SQL. Cloud SQL offers fully managed MySQL, PostgreSQL, and SQL Server with high availability and read replicas. It is the best fit for moderate-traffic OLTP workloads.

Answer

Cloud Spanner

Answer

Firestore

Answer

Bigtable

Question 10

You are designing a Cloud SQL for PostgreSQL database. The application has a table with 1 million rows that is frequently queried using equality on the 'email' column and range queries on the 'created_at' column. Which index strategy minimizes query latency?

Accepted Answer

Create a composite B-tree index on (email, created_at).. A composite B-tree index on (email, created_at) allows the database to satisfy both the equality condition on 'email' and the range condition on 'created_at' in a single index scan. PostgreSQL can use the leftmost column for equality filtering and then traverse the index tree to retrieve the range portion efficiently, minimizing random I/O and query latency.

Answer

Create a full-text index on email.

Answer

Create a B-tree index on email only.

Answer

Create separate B-tree indexes on email and created_at.

Question 11

A company runs a MySQL database on-premises and wants to migrate to Cloud SQL with minimal downtime. They have a multi-terabyte database. Which migration method is most appropriate?

Accepted Answer

Use Database Migration Service with continuous replication.. Database Migration Service (DMS) with continuous replication is designed for minimal downtime migrations. It performs an initial full dump and then continuously replicates ongoing changes from the source to Cloud SQL, allowing cutover with minimal downtime. This is ideal for multi-terabyte databases where a one-time export/import would cause significant downtime.

Answer

Use Cloud SQL's import feature from a CSV file.

Answer

Take a physical backup and restore to Cloud SQL.

Answer

Use mysqldump to export and then import into Cloud SQL.

Question 12

Which Spanner feature allows you to add a new column to an existing table without blocking writes or requiring a rebuild?

Accepted Answer

Online DDL. Online DDL (Data Definition Language) in Spanner allows schema changes such as adding a new column to an existing table without blocking writes or requiring a full table rebuild. This is achieved through a non-blocking, multi-phase schema update process that applies changes in the background while the table remains fully available for reads and writes.

Answer

Optimistic locking

Answer

Interleaved tables

Answer

Schema versioning

Question 13

An engineer is designing a global inventory system that requires strong consistency across continents, with the ability to handle write conflicts and ensure ACID transactions. The system expects millions of reads and writes per second. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides ACID transactions and strong consistency across globally distributed regions, using TrueTime and synchronous replication to handle write conflicts at scale. It is designed for millions of reads and writes per second while maintaining external consistency, making it ideal for a global inventory system.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 14

A data engineer is designing a schema for Cloud Spanner to store a hierarchy of customers and their orders. Customers have many orders, and queries often retrieve orders for a specific customer. To optimize performance and reduce cross-node reads, which schema design pattern should the engineer use?

Accepted Answer

Use a parent-child interleaved table structure where Orders are interleaved in Customers.. In Cloud Spanner, interleaved tables allow you to define a parent-child relationship where child rows are physically stored with the parent row. This co-locates related data on the same node, reducing cross-node reads and improving query performance for queries that retrieve orders for a specific customer. Interleaving is the recommended pattern for hierarchical data with frequent parent-child queries.

Answer

Denormalize by embedding order details as a repeated field within the customer row.

Answer

Normalize customers and orders into separate tables without any relationship.

Answer

Create a secondary index with STORING on the order ID.

Question 15

An engineer is migrating a workload from a relational database to Bigtable. The current schema has a Customers table (1M rows) and an Orders table (100M rows) with a foreign key. Queries often fetch all orders for a customer. What is the best row key design for the Bigtable orders table?

Accepted Answer

Use customer ID + order ID as the row key (e.g., cust123#ord456).. Using customer ID + order ID as the row key ensures that all orders for a single customer are stored in contiguous rows, enabling efficient range scans. Bigtable orders rows lexicographically by row key, so a prefix scan on the customer ID retrieves all related orders in a single read operation, avoiding expensive joins or scatter-gather patterns.

Answer

Use the order ID as the row key and store customer ID as a column.

Answer

Use a hash of the customer ID as the row key.

Answer

Use a random UUID as the row key.