PCDE Practice Test 50 — 15 Questions

Question 1

A financial institution is designing a schema for Cloud BigQuery to store transactional data. They need to optimize for both storage cost and query performance. The data includes a `transaction_time` column that is frequently used in WHERE clauses and for partitioning. They also need to efficiently query by `account_id` and `transaction_type`. The table will be append-only and grow to petabytes. Which schema design should they implement?

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

Accepted Answer

Partition the table by `transaction_time` (day) and cluster by `account_id` and `transaction_type`.. Partitioning by `transaction_time` and clustering by `account_id` and `transaction_type` is the optimal design. Partitioning enables pruning based on time filters, reducing data scanned. Clustering sorts data within partitions, improving filter efficiency on the clustered columns. This design minimizes storage cost and maximizes query performance for the described workload.

Answer

Create a separate table for each `transaction_type` and use `transaction_time` as a partition.

Answer

Use a single table without partitioning or clustering, and rely on BigQuery's automatic optimization.

Answer

Partition the table by `account_id` and cluster by `transaction_time`.

Question 2

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 3

A team is designing a schema for a new Cloud SQL for PostgreSQL database that will store customer orders. They need to ensure that each order is associated with a valid customer and that deleting a customer is prevented if they have existing orders. Which schema design should they use?

Accepted Answer

Create a foreign key constraint on the orders table referencing the customers table with ON DELETE RESTRICT.. A foreign key constraint on the orders table referencing the customers table ensures referential integrity. Using ON DELETE RESTRICT prevents deletion of a customer who has orders, directly satisfying the requirement. This declarative approach is simpler and more reliable than triggers or check constraints, which cannot enforce cross-table references.

Answer

Create a trigger on the customers table that raises an exception if a delete is attempted when orders exist.

Answer

Create a foreign key constraint on the orders table referencing the customers table with ON DELETE CASCADE.

Answer

Create a check constraint on the orders table to validate that the customer_id exists in the customers table.

Question 4

A media company uses Cloud SQL for PostgreSQL to store metadata about videos. The `videos` table has columns `video_id` (integer, primary key), `title`, `upload_date`, and `tags` (an array of text). They frequently run queries that filter by specific tags, such as `SELECT * FROM videos WHERE 'action' = ANY(tags);`. These queries are slow. What schema change should they implement to improve performance?

Accepted Answer

Create a GIN index on the `tags` column.. Creating a GIN index on the `tags` column is the correct solution. GIN indexes are specifically designed to index composite values like arrays, enabling fast searches for elements within the array. This directly accelerates queries using `ANY(tags)` without requiring schema normalization or application changes, making it the most efficient and least disruptive fix.

Answer

Add a `tsvector` column and create a GIN index on it.

Answer

Normalize the schema by creating a separate `video_tags` table with `video_id` and `tag` columns, and create a composite index on `(tag, video_id)`.

Answer

Create a B-tree index on the `tags` column.

Question 5

A database engineer is designing a Cloud Spanner schema for a global inventory system. The `Inventory` table is keyed by `WarehouseId` (a string) followed by `ProductId` (a string). Most queries retrieve all products for a single warehouse, and some queries look up a product across all warehouses. The team wants to optimize read performance and avoid hotspots. Which two design choices should the engineer apply? (Choose two.)

Accepted Answer

Keep `WarehouseId` as the first key column so rows for a warehouse are stored contiguously and range scans by warehouse are efficient.. Cloud Spanner stores rows in primary key order, so leading with `WarehouseId` makes warehouse-centric range scans contiguous and fast, matching the dominant query pattern. The secondary access pattern, looking up a product across all warehouses, is served by a secondary index on `ProductId`, which provides a dedicated access path without scanning the base table. Together these choices optimize both query directions without introducing hotspots.

Answer

Interleave a `ProductDetails` child table under `Inventory` to improve join performance.

Answer

Reverse the key order to `ProductId`, `WarehouseId` to balance reads across splits.

Answer

Add a `CreatedTimestamp` column as the first key column to distribute writes uniformly.

Question 6

A Cloud Spanner database is used for a global inventory system. The main table 'Inventory' has a primary key of (WarehouseId, ProductId). The application frequently runs a query that retrieves all products for a given WarehouseId, ordered by ProductId. The database has a large number of warehouses, and each warehouse has a moderate number of products. To optimize this query, which schema design should you use?

Accepted Answer

Keep the primary key as (WarehouseId, ProductId) and rely on the natural ordering of the primary key for the query.. Cloud Spanner sorts data by primary key. A primary key of (WarehouseId, ProductId) ensures that all products for a warehouse are stored together and sorted by ProductId. The query can use a range scan on the primary key, which is efficient and requires no extra index. Other options either disrupt the ordering or add unnecessary overhead.

Answer

Create an interleaved table 'WarehouseProducts' with a foreign key to 'Inventory' and a primary key of (WarehouseId, ProductId).

Answer

Change the primary key to (ProductId, WarehouseId) to cluster products together.

Answer

Create a secondary index on (WarehouseId, ProductId) and force the query to use it.

Question 7

Order the steps to migrate an on-premises MySQL database to Cloud SQL using Database Migration Service (DMS).

Accepted Answer

Prepare source database, Create connection profile, Create migration job, Start migration, Promote. First prepare source, then create connection profile, create migration job, start migration, and finally promote.

Answer

Prepare source database, Create migration job, Create connection profile, Start migration, Promote

Answer

Create connection profile, Prepare source database, Create migration job, Start migration, Promote

Answer

Prepare source database, Create connection profile, Start migration, Create migration job, Promote

Question 8

A healthcare company is designing a Cloud Firestore schema for a patient management system. They need to store patient records and allow efficient querying by multiple fields such as `lastName`, `dateOfBirth`, and `city`. They also need to retrieve a patient's full record by a unique `patientId`. Which TWO schema design strategies should they use to optimize query performance and cost? (Choose two.)

Accepted Answer

Create composite indexes for combinations of `lastName`, `dateOfBirth`, and `city`.. The optimal strategies are to use `patientId` as the document ID for direct lookups and to create composite indexes for multi-field queries. Document ID lookups are fast and cheap, while composite indexes enable efficient querying by combinations of fields. These align with Firestore's strengths and avoid unnecessary complexity or limitations.

Answer

Denormalize all patient data into a single document to avoid joins.

Answer

Use a separate collection for each city to partition data.

Answer

Store each patient record as a subcollection under a `patients` collection.

Question 9

A startup is building a mobile app using Firestore in Native mode. They need to store user profiles with fields such as `userId`, `displayName`, `email`, and `lastLogin`. The app must support queries that filter users by `email` and sort by `lastLogin`. What is the most appropriate way to structure the Firestore data?

Accepted Answer

Create a collection `users` where each document ID is the `userId` and fields include `email` and `lastLogin`. Add a composite index on `email` and `lastLogin`.. Firestore's data model uses collections and documents, and queries that combine an equality filter with an order by on a different field require a composite index. Storing each user as a document with a unique ID and adding a composite index on the filtered and sorted fields is the idiomatic and scalable approach. Single-document designs hit size limits, and subcollection designs hinder cross-user queries.

Answer

Create a collection `users` and store each user as a subcollection under a document keyed by `email`.

Answer

Create a single document `users` that contains an array of user profile maps, and query using array-contains.

Answer

Create a collection `users` and rely on automatic single-field indexes for `email` and `lastLogin` without any composite index.

Question 10

A healthcare company is designing a Cloud BigQuery schema for patient records. The data includes a Patients table with a repeated field 'conditions' that stores an array of STRUCTs, each containing 'code' and 'diagnosis_date'. Analysts need to run queries that filter patients based on specific condition codes and also retrieve the diagnosis date for that condition. Which schema design approach is most efficient for these queries?

Accepted Answer

Keep the repeated field as is, and use UNNEST to flatten the array in queries.. BigQuery's nested and repeated fields are designed to efficiently store and query hierarchical data. Using UNNEST in queries allows analysts to flatten the array, filter by condition code, and retrieve the diagnosis date without joins. This leverages BigQuery's columnar storage and minimizes data scanned. Normalizing or converting to JSON would degrade performance and increase complexity. Thus, keeping the repeated field and using UNNEST is the most efficient design.

Answer

Normalize the schema by creating a separate Conditions table with a foreign key to Patients.

Answer

Convert the repeated field into a JSON string to allow flexible querying.

Answer

Create a separate column for each possible condition code to avoid arrays.

Question 11

A Cloud Bigtable cluster is experiencing high read latency. The workload consists of random point reads. You notice that the CPU utilization is high. Which action should you take to reduce read latency?

Accepted Answer

Increase the number of nodes in the cluster.. High CPU utilization in Bigtable indicates that the cluster's nodes are saturated. For random point reads, adding nodes increases the total CPU capacity and allows reads to be spread across more servers, directly reducing latency. This is the recommended scaling approach for CPU-bound read workloads.

Answer

Increase the size of the SSD storage per node.

Answer

Change the row key design to distribute reads more evenly.

Answer

Enable a replication policy for the cluster.

Question 12

A team executed the above DDL to create interleaved tables in Cloud Spanner. They need to query all orders for a specific customer. Which query will be most efficient?

Accepted Answer

SELECT * FROM Orders WHERE CustomerId = 1234;. In Cloud Spanner, interleaved tables store child rows physically adjacent to their parent row within the same split. Querying Orders directly on the interleaved key (CustomerId) allows Spanner to perform a local index scan within the parent row's split, avoiding a distributed cross-table join. This leverages the interleaved table's physical clustering for the most efficient retrieval.

Answer

SELECT * FROM Orders WHERE CustomerId = 1234 AND OrderDate = '2023-01-01';

Answer

SELECT * FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId WHERE Customers.CustomerId = 1234;

Answer

SELECT * FROM Orders WHERE OrderId = 5678;

Question 13

A retail company uses BigQuery to store sales transactions. The BI team needs to create a monthly customer lifetime value (CLV) report that aggregates purchase history across multiple tables. Which BigQuery feature should they use to define the data structure for this report?

Accepted Answer

Create a view that joins and aggregates the tables. A view in BigQuery allows the BI team to define a logical data structure that joins and aggregates multiple tables without storing the results. This ensures the monthly CLV report always reflects the latest data, as views are re-evaluated at query time, which is ideal for recurring reports that need up-to-date aggregations.

Answer

Create a materialized view with the aggregation query

Answer

Create an external table pointing to the raw data files

Answer

Create a new table to store the aggregated data using INSERT SELECT

Question 14

A company runs a Cloud SQL for MySQL instance in a project with a strict security policy. They need to encrypt the database with a customer-managed encryption key (CMEK) and rotate the key annually. Which action should they take?

Accepted Answer

Create a Cloud KMS key ring and key, grant the Cloud SQL service account the cloudkms.cryptoKeyEncrypterDecrypter role, and configure the instance to use the key.. To use CMEK with Cloud SQL, you create a Cloud KMS key, grant the Cloud SQL service account the cryptoKeyEncrypterDecrypter role, and configure the instance to use that key. This gives the instance permission to encrypt and decrypt data with the customer-managed key. Annual rotation is achieved by rotating the key in Cloud KMS, which creates a new version that Cloud SQL uses.

Answer

Create a Cloud KMS key and grant the project owner the cloudkms.cryptoKeyEncrypterDecrypter role, then enable CMEK on the instance.

Answer

Use a Cloud External Key Manager (EKM) key stored in a third-party HSM and reference it directly in the instance configuration.

Answer

Enable default encryption at rest, which automatically uses a customer-managed key from the project.

Question 15

You manage a Cloud Spanner instance with a regional configuration and a database that handles a global retail catalog. A reporting service issues a large number of single-row, strongly consistent reads against the primary key of the Products table, and p99 read latency has climbed from 8 ms to 45 ms over two weeks. Spanner's CPU utilization is at 55%, and no schema changes were made. You want to reduce read latency without weakening consistency for the catalog service. Which action should you take?

Accepted Answer

Investigate whether a small number of primary key values are receiving disproportionate read traffic, and if so, reshard the key space or add a key prefix to spread the load.. The latency pattern with moderate CPU and stable schema points to a hot spot: a narrow key range or a few popular keys concentrated on one split. Spanner scales by splitting key ranges, so redistributing the hot keys reduces per-split load and latency while keeping strong consistency. Adding nodes, indexes, or staleness does not fix a concentrated access pattern and can add cost or weaken guarantees.

Answer

Use a stale read with an exact staleness bound of 15 seconds for the reporting service while keeping strong reads for the catalog service.

Answer

Add a secondary index on the Products table primary key columns and force the reporting service to use it.

Answer

Increase the instance's node count to add more serving capacity for the read workload.