PCDE Design and implement database schemas Practice Test 4 — 15 Questions

Question 1

You are designing a Firestore database for a mobile app that stores user profiles. Each profile document contains fields such as userId, displayName, email, and lastLogin. The app needs to query users by email address. What is the most efficient schema design to support this query?

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

Accepted Answer

Store each user profile in a collection 'users' with an auto-generated document ID, and create a single-field index on the email field.. Firestore automatically indexes all fields, so a single-field index on email is created by default, enabling efficient queries by email. Using an auto-generated document ID provides a stable identifier independent of email changes. The other options either rely on email as document ID, which can be problematic if emails change or contain invalid characters, or add unnecessary composite indexes or data duplication. The auto-generated ID with default indexing is the most efficient and flexible design.

Answer

Store each user profile in a collection 'users' with document ID equal to the email address.

Answer

Store each user profile in a collection 'users' with document ID equal to email, and also store a copy of the profile in a collection 'emails' keyed by email.

Answer

Store each user profile in a collection 'users' with document ID equal to userId, and create a composite index on (email, userId).

Question 2

Your team is designing a schema for a new Cloud Spanner database that will store order line items. Each order can have many line items, and line items are always fetched together with their parent order. Queries will frequently retrieve all line items for a given order. The schema currently defines an 'Orders' table with a primary key of 'OrderId' and a separate 'LineItems' table with a primary key of 'LineItemId'. What schema change should you make to optimize data locality and query performance while preserving the ability to efficiently retrieve all line items for an order?

Accepted Answer

Change the 'LineItems' primary key to 'OrderId, LineItemId' and make 'OrderId' the first key column.. Interleaving child tables in Cloud Spanner physically co-locates rows with the same parent key prefix, which minimizes network overhead and improves performance for joins between parent and child. By making 'OrderId' the first column of the 'LineItems' primary key and interleaving the table in 'Orders', all line items for an order are stored together. This is the recommended schema design for one-to-many relationships in Cloud Spanner.

Answer

Change the 'LineItems' primary key to 'LineItemId, OrderId' and add a foreign key constraint to 'Orders'.

Answer

Add a secondary index on 'OrderId' in the 'LineItems' table.

Answer

Denormalize the schema by storing line items as a repeated field in the 'Orders' table.

Question 3

A team is designing a schema for a new Cloud Firestore application that stores user profiles. They need to support queries that filter on multiple fields: `age`, `city`, and `last_login`. They also want to minimize the number of composite indexes. Which two actions should they take? (Choose two.)

Accepted Answer

Create composite indexes for each combination of fields that will be queried together.. To support multi-field queries in Firestore, you must create composite indexes for the specific combinations used. Alternatively, denormalizing multiple fields into a single field reduces the number of composite indexes needed because queries then filter on one field. Both approaches are valid strategies to minimize index count while supporting the required query patterns.

Answer

Store each field in a separate subcollection to allow independent indexing.

Answer

Use collection group queries to query across multiple collections.

Answer

Use Firestore's built-in single-field indexes for all queries without additional configuration.

Question 4

A retail company uses Cloud Spanner for its order processing system. The `Orders` table has a monotonically increasing `order_id` as the primary key. During peak shopping seasons, the database experiences severe write hotspots on this table, causing performance degradation. The team wants to redesign the primary key to distribute writes evenly without changing the application's need to query orders by `order_id` and `customer_id`. What should they do?

Accepted Answer

Hash the `order_id` using a modulo function and prepend it as the first part of the primary key, while storing the original `order_id` as a separate column.. The correct solution is to shard the primary key by hashing the `order_id` and prepending the hash to the key. This eliminates the hotspot by distributing writes across multiple key ranges. The original `order_id` is still stored, allowing queries by that column via a secondary index. This maintains application functionality while improving write scalability.

Answer

Change the primary key to a randomly generated UUID and update the application to use the UUID for all lookups.

Answer

Use a composite primary key with `customer_id` as the first key column and `order_id` as the second.

Answer

Enable Cloud Spanner's automatic hotspot detection and let it rebalance the data without schema changes.

Question 5

You are designing a schema for a Cloud Bigtable table to store time-series data from millions of IoT devices. Each row should represent a device and a time bucket. You need to support queries that retrieve the latest readings for a specific device and also range scans over a time period for a device. Which row key design is most effective?

Accepted Answer

Use a row key that concatenates device_id and timestamp in reverse chronological order (e.g., device_id#reverse_timestamp).. A row key of device_id concatenated with a reverse timestamp stores all data for a device together, enabling efficient range scans by device. The reverse timestamp ensures the latest reading is at the top of the device's row range, so fetching the latest is fast. This design also avoids write hotspots because device IDs are diverse and the reverse timestamp prevents sequential writes from concentrating on one tablet. The other designs either cause hotspots or scatter device data, making range scans inefficient.

Answer

Use a row key that is a random UUID for each reading, with a separate index table mapping device_id to UUIDs.

Answer

Use a row key that is a hash of device_id concatenated with timestamp (e.g., hash(device_id)#timestamp).

Answer

Use a row key that concatenates timestamp and device_id (e.g., timestamp#device_id).

Question 6

A financial services firm is designing a Cloud Spanner schema for a global ledger. The main table 'Transactions' has a monotonically increasing primary key based on a timestamp, and the workload is write-heavy with high throughput. They observe hotspotting on the last few key ranges. They need to redesign the primary key to distribute writes evenly while still supporting efficient range scans by time. Which two actions should they take? (Choose two.)

Accepted Answer

Prefix the primary key with a hash of the timestamp, such as a modulo of the timestamp, to spread writes across multiple key ranges.. Hotspotting on a monotonically increasing primary key in Cloud Spanner is mitigated by distributing the leading part of the key. Hashing or prefixing with a modulo or random integer spreads writes across many key ranges. Both approaches allow time-range scans by including the prefix in queries, though they may require querying multiple ranges. This balances write throughput with the need for time-based range scans.

Answer

Shard the table manually by creating multiple tables for different time periods and use a UNION ALL query to scan across them.

Answer

Use a UUID or bit-reversed timestamp as the primary key to randomize insert order.

Answer

Add a secondary index on the timestamp column to support time-range queries.

Question 7

Your team is designing a Cloud Spanner schema for an order management system. The Orders table has a primary key defined as (CustomerId, OrderId). The application frequently runs a query that lists all orders for a given CustomerId, sorted by OrderId descending. To optimize this query, which schema design change should you make?

Accepted Answer

Add a descending secondary index on (CustomerId, OrderId DESC).. Cloud Spanner secondary indexes can specify sort order per column. For a query that filters on CustomerId and orders by OrderId descending, an index on (CustomerId, OrderId DESC) provides the exact ordering, eliminating the need for a sort and improving performance. Other options either misuse primary key constraints or add unnecessary complexity.

Answer

Add a new column OrderIdDesc that stores the negated OrderId and create an index on (CustomerId, OrderIdDesc).

Answer

Change the primary key to (CustomerId, OrderId DESC).

Answer

Create a secondary index on (CustomerId, OrderId) and rely on the query optimizer to reverse the scan.

Question 8

A healthcare company uses Cloud Spanner for a patient records system. The `Patients` table has a primary key `PatientId` (a random UUID). The table is expected to grow to billions of rows. Queries often retrieve patients by `PatientId` and also need to list all patients for a given `HospitalId`. The team wants to optimize for both point lookups and range scans by `HospitalId` while avoiding hotspots. What should they do?

Accepted Answer

Create a secondary index on `HospitalId` with `PatientId` as a stored column.. A secondary index on `HospitalId` with `PatientId` stored enables efficient range scans for patients by hospital while keeping the primary key on `PatientId` for fast point lookups. This design avoids hotspots because the primary key remains random, and the index provides the necessary access path for hospital-based queries.

Answer

Partition the `Patients` table by `HospitalId` using table partitioning.

Answer

Add a foreign key constraint from `HospitalId` to a `Hospitals` table.

Answer

Change the primary key to `HospitalId` and make `PatientId` a non-key column.

Question 9

A healthcare company is designing a schema for a Cloud Spanner database that stores patient records. The `Patients` table has a primary key `patient_id`. The application frequently queries patients by `last_name` and `date_of_birth` together. Additionally, the company must ensure that queries by `last_name` alone are efficient. They also need to enforce that each `patient_id` is unique and that `last_name` and `date_of_birth` are not null. Which two schema design choices should they implement? (Choose two.)

Accepted Answer

Declare `last_name` and `date_of_birth` as NOT NULL columns.. The two correct choices are to create a secondary index on `last_name` and `date_of_birth` and to declare those columns as NOT NULL. The composite index optimizes the frequent combined query, while the NOT NULL constraints enforce data integrity. The primary key already ensures uniqueness of `patient_id`, so no additional constraint is needed.

Answer

Create a secondary index on `last_name` only.

Answer

Define `patient_id` as the primary key and enforce uniqueness with a unique constraint.

Answer

Use a composite primary key of `patient_id` and `last_name`.

Question 10

You are designing a schema for a Cloud Spanner database that supports a global inventory management system. The main table 'Inventory' has a primary key of (WarehouseId, ProductId). You need to optimize for read-heavy workloads that frequently query inventory by ProductId across all warehouses, and also by WarehouseId. Which two additional schema design choices are most appropriate? (Choose two.)

Accepted Answer

Create a secondary index on ProductId.. A secondary index on ProductId enables efficient lookups by ProductId across all warehouses, which is a key requirement. A covering index on (ProductId, WarehouseId) including quantity further optimizes those queries by avoiding base table lookups. The primary key already supports queries by WarehouseId, so an index on WarehouseId is redundant. Interleaving a Product table is not necessary for the stated access patterns. Changing the primary key would harm WarehouseId queries and cause migration overhead.

Answer

Create a secondary index on WarehouseId.

Answer

Change the primary key to (ProductId, WarehouseId).

Answer

Interleave a 'Product' table with 'Inventory' to co-locate product data.

Question 11

A company is designing a Firestore schema for a mobile app that stores user profiles. Each profile document contains fields such as userId, name, email, and a list of friends (an array of user IDs). The app needs to query for all users who have a specific friend. Which Firestore feature should be used to efficiently support this query?

Accepted Answer

Create a single-field index on the friends array and use an array-contains query.. Firestore supports array-contains queries on array fields, and it automatically indexes array fields. For a user profile with a friends array, querying for a specific friend can be done efficiently using array-contains. This requires no additional schema design changes, making it the simplest and most effective solution.

Answer

Denormalize the data by creating a separate collection 'friend_relationships' with documents for each friendship.

Answer

Use a collection group query on a subcollection of friends.

Answer

Create a composite index on the friends array and userId.

Question 12

A retail company uses Cloud Spanner for its order management system. The `Orders` table has a monotonically increasing `OrderId` as the primary key, and the application inserts new orders at a very high rate. The team observes that all writes are hitting the same Spanner server, causing hotspots and high latency. They want to redesign the schema to distribute write load evenly without changing the application's ability to query orders by `OrderId`. What should they do?

Accepted Answer

Use a hash of the `OrderId` as the primary key and store the original `OrderId` as a separate column with a secondary index.. The correct approach is to hash the `OrderId` and use that as the primary key while storing the original `OrderId` as a separate column with a secondary index. This distributes writes evenly across Spanner servers, avoiding hotspots caused by monotonically increasing keys. The secondary index allows efficient lookups by the original `OrderId`, preserving application query patterns. Other options either do not solve the hotspot issue or break the query requirement.

Answer

Enable Spanner's automatic sharding feature by setting the `sharding_mode` to `AUTO` on the `Orders` table.

Answer

Change the primary key to a UUID generated by the application for each new order.

Answer

Add a `ShardId` column and make it part of a composite primary key with `OrderId`.

Question 13

A company is designing a Cloud Spanner schema for a global inventory system. The 'Products' table has a primary key of 'ProductId' (a random UUID). The 'Inventory' table tracks stock levels for each product at each warehouse. The 'Inventory' table currently has a primary key of 'InventoryId' (a random UUID) and columns 'ProductId', 'WarehouseId', and 'Quantity'. Queries frequently retrieve inventory for a specific product across all warehouses, and also for a specific warehouse across all products. The team wants to optimize the schema for these access patterns and reduce hotspotting. Which TWO changes should they make? (Choose two.)

Accepted Answer

Change the 'Inventory' primary key to (ProductId, WarehouseId) and interleave 'Inventory' in 'Products'.. Interleaving 'Inventory' in 'Products' with a primary key starting with 'ProductId' optimizes product-centric queries by co-locating data, while a secondary index on (WarehouseId, ProductId) supports warehouse-centric queries. Together, these changes address both access patterns and reduce hotspotting because the parent key is a random UUID, distributing writes across the key space.

Answer

Add a foreign key constraint from 'Inventory' to 'Products' without changing the primary key.

Answer

Change the 'Inventory' primary key to (WarehouseId, ProductId) and interleave 'Inventory' in a 'Warehouses' table.

Answer

Use a monotonically increasing timestamp as the first column of the 'Inventory' primary key to distribute writes.

Question 14

A retail company is designing a Cloud Spanner schema for an order management system. The Orders table will have a composite primary key (CustomerId, OrderId). Query patterns require retrieving all orders for a given customer, sorted by OrderId. The team wants the most efficient primary key structure that supports these queries and avoids hotspots. What should they do?

Accepted Answer

Use CustomerId as the first key column and OrderId as the second, with OrderId generated using a bit-reversed sequence.. In Cloud Spanner, the primary key order determines data locality and query efficiency. Placing CustomerId first allows efficient retrieval of all orders for a customer. Using a bit-reversed sequence for OrderId prevents hotspots by scattering sequential writes across the key space while preserving numeric ordering for range scans. This design directly supports the required access pattern and write scalability.

Answer

Use OrderId as the first key column and CustomerId as the second, with OrderId generated as a monotonically increasing integer.

Answer

Use CustomerId as the first key column and OrderId as the second, with OrderId generated as a descending sequence.

Answer

Use a randomly generated UUID as the first key column, followed by CustomerId and OrderId.

Question 15

A SaaS company stores customer records in Cloud Spanner. The `Customers` table has a `CustomerId` primary key and a `Region` column. Regulatory requirements demand that data for customers in the EU be stored in the `europe-west1` region, while US customers remain in `us-central1`. The application must query each customer by `CustomerId` without knowing the region. Which schema and deployment approach meets these requirements?

Accepted Answer

Deploy two regional Cloud Spanner instances and maintain a shared lookup table in a third, globally accessible store that maps `CustomerId` to region for routing.. Data residency requires that EU customer data never leave the EU, which rules out multi-region configurations that replicate everywhere. Two regional instances keep data physically confined. To preserve region-agnostic lookups by `CustomerId`, the application consults a lightweight shared mapping store that returns the owning region, then queries the correct instance. This separates residency enforcement from query routing.

Answer

Create two separate Cloud Spanner instances, one per region, and route queries in the application based on a customer-to-region mapping.

Answer

Deploy two regional Cloud Spanner instances and use a foreign key between them to enforce the region mapping.

Answer

Use a single multi-region Cloud Spanner instance with a `Region` column and add a secondary index on `Region`.