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

Question 1

A retail company is designing a schema for a new Cloud Spanner database to store order line items. The `Orders` table has a primary key of `OrderId` (STRING). The `OrderItems` table will store individual items for each order. The application frequently queries all items for a given order and also needs to efficiently retrieve a specific item by its line number within an order. What should be the primary key of the `OrderItems` table to optimize these access patterns?

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

Accepted Answer

`OrderId` (STRING), `LineNumber` (INT64). The correct primary key is a composite of `OrderId` and `LineNumber`. This design interleaves `OrderItems` rows within the parent `Orders` row, clustering all items for an order together. It also supports efficient point lookups by `OrderId` and `LineNumber`. This matches Cloud Spanner's recommendation for parent-child relationships and optimizes both range scans and point queries.

Answer

A randomly generated UUID (STRING)

Answer

`OrderId` (STRING), `ProductId` (STRING)

Answer

`LineNumber` (INT64), `OrderId` (STRING)

Question 2

Your team is designing a Cloud Spanner schema for an IoT fleet management system. The primary table, 'VehicleReadings', will store sensor data with columns: VehicleId (STRING), ReadingTimestamp (TIMESTAMP), SensorType (STRING), and ReadingValue (FLOAT64). Each vehicle emits multiple readings per second, and the table is expected to grow to petabytes. Queries will always filter by a specific VehicleId and a time range. You need to design the primary key to maximize write throughput and query performance. What should you do?

Accepted Answer

Use a composite primary key of (VehicleId, ReadingTimestamp) without any additional sharding.. The primary key (VehicleId, ReadingTimestamp) is optimal because VehicleId has high cardinality, spreading writes across many splits. It also supports efficient range scans for a specific vehicle and time range, which matches the query pattern. Adding a random shard or interleaving would either harm query performance or create hotspots, and reversing the key order would cause write contention on the timestamp prefix.

Answer

Use a composite primary key of (VehicleId, ReadingTimestamp) and add a sharding suffix like a random number to the first key part.

Answer

Use a primary key of (ReadingTimestamp, VehicleId) to enable time-based queries across all vehicles.

Answer

Use a composite primary key of (VehicleId, ReadingTimestamp) and interleave the table in 'Vehicles'.

Question 3

You are designing a Firestore database for a social media application. Users can post content, and other users can like and comment on posts. You need to support queries that retrieve the most recent posts for a user's feed, as well as retrieve all comments for a specific post. You want to minimize reads and avoid composite indexes where possible. Which two schema design choices should you make? (Choose two.)

Accepted Answer

Store posts in a top-level collection 'posts', with each post document containing a 'userId' field and a 'timestamp' field. Create a composite index on (userId, timestamp descending).. Using a top-level posts collection with a composite index on (userId, timestamp descending) efficiently supports retrieving a user's most recent posts. Storing comments as a subcollection under each post avoids an extra composite index and naturally scopes comments to their post. Together, these choices minimize reads and avoid unnecessary indexes, meeting the requirements.

Answer

Store posts in a top-level collection 'posts', with each post document containing a 'userId' field and a 'timestamp' field. Create separate single-field indexes on 'userId' and 'timestamp'.

Answer

Store posts as a subcollection under each user document, with each post document containing a 'timestamp' field. Query posts by ordering on timestamp.

Answer

Store comments in a top-level collection 'comments', with each comment document containing a 'postId' field and a 'timestamp' field. Create a composite index on (postId, timestamp).

Question 4

You are designing a schema for a Cloud SQL for PostgreSQL database that will store sensitive customer data. The application requires that certain columns, such as credit card numbers, be encrypted at rest with customer-managed encryption keys (CMEK). You also need to support efficient equality searches on the encrypted credit card numbers. Which approach should you take?

Accepted Answer

Use the pgcrypto extension to encrypt credit card numbers with a symmetric key, and create a hash column for equality searches.. To meet both encryption and search requirements, use pgcrypto to encrypt the credit card numbers and add a deterministic hash column for equality searches. This provides column-level encryption with CMEK (by using a key stored in Cloud KMS or managed by the application) and allows efficient indexed lookups on the hash. Other options either do not encrypt the data within the database or require inefficient full-table scans.

Answer

Store credit card numbers in a BYTEA column encrypted with a custom application-side encryption, and perform searches by decrypting all rows in the application.

Answer

Enable CMEK on the Cloud SQL instance and store credit card numbers in plaintext; rely on disk encryption.

Answer

Use Cloud SQL's built-in Transparent Data Encryption (TDE) with CMEK and create a regular index on the credit card number column.

Question 5

Your company is designing a Firestore database for a real-time collaborative document editing application. Each document has a unique documentId and can be edited by multiple users simultaneously. You need to store document content, a list of collaborators, and a revision history. The application must support queries to retrieve all documents a user has access to, sorted by last modified time. Which data model should you use?

Accepted Answer

Store each document as a top-level collection 'documents', with a subcollection 'revisions' for history. Store collaborators as a map field with userId as key. To query documents by user, use a collection group query on a 'collaborators' subcollection under each document.. The optimal design uses a subcollection for collaborators, enabling collection group queries to efficiently find all documents a user can access. This avoids data duplication, leverages Firestore's indexing, and supports sorting by last modified time when combined with appropriate indexes. Storing revisions in a subcollection keeps document size manageable and allows independent querying of history.

Answer

Store each document as a top-level collection 'documents', with a subcollection 'revisions' for history. Store collaborators as an array field. To query documents by user, create a separate top-level collection 'userDocuments' with documents keyed by userId and a subcollection 'documents' containing document references.

Answer

Store each document as a top-level collection 'documents', with a subcollection 'revisions' for history. Store collaborators as an array field. To query documents by user, use an array-contains query on the collaborators field and sort by lastModified.

Answer

Store each document as a top-level collection 'documents', with a subcollection 'revisions' for history. Store collaborators as an array field in the document. To query documents by user, create a separate collection 'userDocuments' with documents keyed by userId and an array of documentIds.

Question 6

A logistics company uses Cloud Spanner for its shipment tracking system. The `Shipments` table has a primary key of `ShipmentId` and includes a `Status` column. The application frequently queries for shipments with a specific `Status` and also needs to retrieve shipments by `ShipmentId`. The team wants to optimize read performance for status-based queries without affecting write throughput. What should they do?

Accepted Answer

Create a secondary index on the `Status` column.. Creating a secondary index on the `Status` column is the correct approach to optimize status-based queries. It allows Cloud Spanner to quickly locate shipments with a given status without scanning the full table. While the index adds some write overhead, it does not significantly impact write throughput unless status updates are extremely frequent. Other options either misuse Spanner features or break existing query patterns.

Answer

Interleave the `Shipments` table with a new `StatusIndex` table.

Answer

Change the primary key to `Status` and `ShipmentId` to cluster shipments by status.

Answer

Use a `STORING` clause to include all columns in a secondary index on `Status`.

Question 7

A company is designing a schema for a new application on Cloud Spanner. The application requires strong consistency and low-latency reads for user profiles. The profiles are accessed by user_id, and the application also needs to query users by email address. The team wants to minimize write latency and avoid hotspots. What should they do?

Accepted Answer

Use user_id as the primary key and create a secondary index on email.. Using user_id as the primary key aligns with the most frequent access pattern and avoids hotspots if user_id is high-cardinality and not monotonically increasing. A secondary index on email supports the less frequent email lookups. This design balances performance for both queries while minimizing write latency, as the secondary index adds some overhead but is necessary.

Answer

Use a composite primary key of user_id and email, and no secondary index.

Answer

Use email as the primary key and create a secondary index on user_id.

Answer

Use a randomly generated UUID as the primary key and create a secondary index on both user_id and email.

Question 8

A logistics company is using Cloud Spanner for a shipment tracking system. The `Shipments` table has a primary key of `ShipmentId` (STRING). The `Events` table records all tracking events for each shipment and has a primary key of `EventId` (STRING) and a foreign key `ShipmentId` referencing `Shipments`. Queries often retrieve all events for a shipment ordered by `EventTimestamp`. The database engineer wants to optimize query performance and reduce latency. What should they do to the `Events` table schema?

Accepted Answer

Interleave `Events` in `Shipments` and set the primary key to `ShipmentId`, `EventTimestamp`, `EventId`.. Interleaving `Events` in `Shipments` with a primary key of `ShipmentId`, `EventTimestamp`, `EventId` co-locates child rows with the parent and sorts them by timestamp. This enables efficient retrieval of all events for a shipment in chronological order, reducing latency and improving performance. It leverages Cloud Spanner's interleaving and key ordering features.

Answer

Change the primary key to `ShipmentId`, `EventId` and interleave in `Shipments`.

Answer

Add a `EventTimestamp` column and partition the `Events` table by it.

Answer

Create a secondary index on `ShipmentId` and `EventTimestamp`.

Question 9

A retail company is designing a Cloud Spanner schema for an order management system. The Orders table has a composite primary key (CustomerId, OrderId), and queries frequently retrieve all orders for a given customer, sorted by OrderId. To optimize these queries and ensure efficient data distribution, which schema design should you implement?

Accepted Answer

Use a secondary index on (CustomerId, OrderId) to allow efficient lookups and sorted retrieval.. The primary key (CustomerId, OrderId) in Cloud Spanner already stores rows sorted by CustomerId and then OrderId, so queries that retrieve all orders for a specific customer and need them sorted by OrderId can be served efficiently using the primary key. No additional secondary index is required, as it would duplicate storage and add write latency without improving read performance for this pattern. Thus, the optimal design is to rely on the existing primary key ordering.

Answer

Change the primary key to (OrderId, CustomerId) to distribute orders more evenly.

Answer

Add an interleaved index on Orders with CustomerId as the prefix and OrderId as the sort key.

Answer

Create a descending index on OrderId to speed up sorted retrieval.

Question 10

A Cloud SQL for PostgreSQL database is used for an online transaction processing (OLTP) application. The main table 'orders' has columns: order_id (SERIAL PRIMARY KEY), customer_id (INTEGER), order_date (DATE), status (VARCHAR), and total_amount (NUMERIC). The table currently has 500 million rows and is growing rapidly. Queries often filter by customer_id and order_date, and also by status for reporting. You need to design indexes to optimize query performance while minimizing write overhead. Which indexing strategy should you implement?

Accepted Answer

Create a composite index on (customer_id, order_date) and a composite index on (status, order_date).. The composite index on (customer_id, order_date) efficiently serves queries filtering by both customer and date. The composite index on (status, order_date) supports reporting queries that filter by status and date. This combination covers the primary query patterns while avoiding unnecessary indexes that would increase write overhead. It leverages PostgreSQL's ability to use composite indexes for multi-column filters.

Answer

Create separate single-column indexes on customer_id, order_date, and status.

Answer

Create a composite index on (customer_id, order_date) and a separate index on status.

Answer

Create a composite index on (customer_id, order_date) and a partial index on status WHERE status = 'pending'.

Question 11

A team is designing a schema for a Cloud Bigtable instance to store time-series data from IoT sensors. Each row represents a sensor reading and has a row key composed of `sensor_id` and a timestamp. The team wants to optimize for writes and reads of recent data, and also support occasional scans of historical data for a specific sensor. Which row key design should they use?

Accepted Answer

Use `sensor_id` followed by an inverted timestamp (e.g., `sensor_id#Long.MAX_VALUE - timestamp`).. For time-series data in Bigtable, the row key should distribute writes and support common read patterns. Grouping by sensor_id allows efficient scans for a specific sensor. Inverting the timestamp ensures that the most recent readings appear first in the sorted order for that sensor, so queries for recent data read a contiguous range at the start. This also avoids write hotspots because new data is inserted near the beginning of the sensor's key range rather than at the end, and high sensor cardinality spreads load across tablets.

Answer

Use a random UUID as the row key and store `sensor_id` and timestamp as columns.

Answer

Use `sensor_id` followed by a timestamp in ascending order (e.g., `sensor_id#timestamp`).

Answer

Use a timestamp followed by `sensor_id` (e.g., `timestamp#sensor_id`).

Question 12

A retail company is building a real-time inventory system on Cloud Spanner. The primary table 'Inventory' has a composite primary key: 'WarehouseId' (INT64) and 'ProductId' (INT64). Most queries filter by 'WarehouseId' and then join to a 'Products' table on 'ProductId'. The 'Products' table is relatively small (about 10,000 rows) and changes infrequently. To optimize query performance and minimize storage, which schema design should you use?

Accepted Answer

Create a secondary index on 'Products' for 'ProductId' and denormalize product details into 'Inventory' as a STRUCT column.. Denormalizing the small, slowly changing 'Products' table into the 'Inventory' table as a STRUCT column eliminates the join, reducing query latency and improving performance for read-heavy workloads. Since the product data is small and infrequently updated, the storage overhead is acceptable. This approach leverages Spanner's support for STRUCT types and avoids the complexity of interleaving or secondary indexes that do not address the join pattern. It is a best practice for optimizing joins with small dimension tables in distributed databases.

Answer

Use a foreign key from 'Inventory' to 'Products' on 'ProductId' and rely on Spanner's distributed joins to handle the join efficiently.

Answer

Interleave the 'Products' table in the 'Inventory' table using 'ProductId' as the interleave prefix.

Answer

Add a foreign key constraint from 'Inventory' to 'Products' on 'ProductId' and create a secondary index on 'Inventory' for 'ProductId'.

Question 13

A financial services company is designing a Cloud Firestore schema for a mobile banking application. They need to store user accounts and transactions. Queries will frequently retrieve a user's account details and the most recent transactions for that account. They also need to support queries that filter transactions by amount range and date. Which TWO schema design strategies should they implement to optimize query performance and cost? (Choose two.)

Accepted Answer

Create a composite index on the 'transactions' collection group for amount and date fields.. The optimal design uses a subcollection for transactions under each account document, which allows efficient retrieval of a user's account and recent transactions together. Additionally, a composite index on the transactions collection group for amount and date is necessary to support range and date filters across all transactions. This combination balances performance, scalability, and cost by minimizing reads and avoiding hot spots.

Answer

Store all transactions in a single root-level collection with an 'accountId' field.

Answer

Denormalize account details into each transaction document to avoid extra reads.

Answer

Use a map field within the account document to store an array of recent transactions.

Question 14

A company is designing a Cloud SQL for PostgreSQL database for a new application. They need to store JSON data that will be queried by specific keys and also require full-text search on a text column. Which two schema design choices should they make? (Choose two.)

Accepted Answer

Create a GIN index on the jsonb column to speed up key-based queries.. The jsonb data type allows indexing and efficient key-based queries, while a tsvector column with a GiST index enables full-text search. Together, these choices satisfy both requirements: storing and querying JSON by specific keys, and performing full-text search on a text column. Using jsonb with a GIN index is also valid for JSON, but the pair must cover both needs.

Answer

Create a separate table for each JSON key to normalize the data.

Answer

Use the tsvector data type and a GiST index for the full-text search column.

Answer

Use the json data type for the JSON column to preserve formatting and key order.

Question 15

A startup is using Firestore in Native mode for a mobile app that stores user profiles. Each user document is identified by the user's Firebase Authentication UID. The app needs to query users by their email address, which is unique, and also by their city. The startup wants to minimize read costs and ensure queries are fast. Which schema design should they implement?

Accepted Answer

Store each user profile as a document in a 'users' collection with the UID as the document ID, and create a separate 'emails' collection where the document ID is the email and the value is the UID.. Using a separate 'emails' collection keyed by email enables direct, low-cost lookups by email because Firestore document ID lookups are fast and incur only one read. The main 'users' collection keyed by UID supports authentication and can be queried by city using Firestore's automatic single-field indexes. This design minimizes read costs for the unique email lookup and avoids composite indexes that are not needed for individual field queries. It also keeps the schema simple and aligned with Firestore's strengths in direct document access.

Answer

Store each user profile as a document in a 'users' collection with the UID as the document ID, and create a composite index on 'email' and 'city'.

Answer

Store each user profile as a document in a 'users' collection with the UID as the document ID, and use the email address as the document ID instead.

Answer

Store each user profile as a document in a 'users' collection with the UID as the document ID, and create a single-field index on 'email' and another on 'city'.