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

Question 1

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?

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

Accepted Answer

Store orders as a nested array within the customer document. 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 2

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 3

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 4

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. 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 5

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. 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 6

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

Question 7

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 8

You are designing a Spanner schema for a global social media application that stores user posts. Each user can have millions of posts. The most common query is 'get the most recent 10 posts for a user'. Which table interleaving design minimizes latency?

Accepted Answer

Interleave the Posts table under the Users table, with user_id as parent key and post_timestamp as the child ordering key.. Interleaving the Posts table under the Users table in Spanner ensures that all posts for a given user are stored in the same split, co-located on the same tablet server. This allows the query for the most recent 10 posts to be served with a single, local range scan on the interleaved child table, using post_timestamp as the descending ordering key, minimizing cross-node communication and latency.

Answer

Use a secondary index on user_id in the Posts table.

Answer

Create a single table with user_id and post_timestamp as a composite primary key.

Answer

Store posts in a separate Cloud Bigtable table and use the user_id as part of the row key.

Question 9

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'. Hotspotting occurs because rows for a single high-volume device all share the same deviceID prefix, causing writes to be directed to a single tablet. To distribute writes evenly, the row key must include a non-deterministic or varying component. Option A adds a random salt per write, which scatters the keys for the same device across many tablets, effectively distributing the load. Option B still uses deviceID as the first part, so it doesn't spread writes. Option C creates monotonically increasing keys, leading to sequential writes and hotspotting. Option D does not change the row key, so it fails to address the prefix issue.

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 10

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 11

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 12

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 13

A global gaming company uses Spanner to store player profiles and scores. The most common query is 'Get the top 10 players by score' across all regions. The 'Players' table has millions of rows. Which schema design and query approach provides the best performance?

Accepted Answer

Add a secondary index on score column and query 'SELECT * FROM Players ORDER BY score DESC LIMIT 10'.. A secondary index on the `score` column allows Spanner to perform an index scan in descending order, retrieving only the top 10 rows without scanning the entire `Players` table. The `ORDER BY score DESC LIMIT 10` query leverages the index's sorted structure, making it the most efficient approach for this common query pattern in a globally distributed database.

Answer

Add a generated column storing the score as a string and index it.

Answer

Use a STORING clause to store additional columns in the index.

Answer

Use a parent-child interleaving between a 'Leaderboard' parent table and 'Players' child table.

Question 14

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) supports continuous replication and minimal downtime for large databases. It handles initial snapshot and ongoing CDC.

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 15

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