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

Question 1

A logistics company runs its shipment-tracking platform on Cloud Spanner. The `Shipments` table has a primary key of `ShipmentId` (a monotonically increasing integer generated by the application) and stores a JSON column `TrackingEvents` that is updated continuously as the package moves. The write throughput is high and the operations team reports that all writes are landing on a single split, causing hotspots. Which schema change should the database engineer make to distribute writes?

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

Accepted Answer

Use a UUID4 string as the first key column instead of the monotonic integer.. Cloud Spanner distributes data by primary key ranges, and a monotonically increasing first key column concentrates all new inserts into the final split, creating a write hotspot. Replacing that key with a uniformly distributed value such as a UUID4 scatters inserts across many splits, allowing Spanner to spread write load. Alternative key prefixes like dates only shift the problem to a coarser granularity and do not resolve concurrent-write concentration.

Answer

Convert the `TrackingEvents` JSON column to a `STRING(MAX)` column and add a secondary index on it.

Answer

Add a `CreatedDate` column as the first key column and keep `ShipmentId` as the second.

Answer

Enable interleaving of a child `TrackingEvents` table under `Shipments` keyed by event timestamp.

Question 2

A retail company is designing a Firestore database for a product catalog. Each product document contains fields such as 'name', 'category', 'price', and 'inventoryCount'. The application needs to support queries that filter products by category and sort by price. The team wants to minimize the number of composite indexes required. What should they do?

Accepted Answer

Create a composite index on 'category' (ascending) and 'price' (ascending).. Firestore requires composite indexes for queries that combine a filter on one field with a sort on another. By creating a composite index on 'category' and 'price', the query filtering by category and sorting by price can be executed efficiently. This single index satisfies the query pattern, avoiding the need for multiple indexes or denormalization.

Answer

Use a collection group query to query across all product collections.

Answer

Create separate single-field indexes on 'category' and 'price'.

Answer

Denormalize the data by storing products under category subcollections and query each subcollection separately.

Question 3

A startup is building a mobile app using Firestore in Native mode. They need to store user profiles and allow users to search for other users by username. The username must be unique across all users. The team wants to minimize read costs and ensure that username lookups are fast. What should they do?

Accepted Answer

Create a 'users' collection where each document ID is the username, and store the profile fields in that document.. Using the username as the document ID in the users collection guarantees uniqueness and allows direct, single-read lookups. Firestore document IDs are unique within a collection, so no additional mapping or index is needed. This minimizes read costs and latency, and it simplifies the data model by keeping profile data in one place.

Answer

Store user profiles in a 'users' collection with auto-generated IDs, and create a separate 'usernames' collection that maps username to user ID.

Answer

Use the username as the document ID in a 'usernames' collection, and store the user profile in a separate 'users' collection keyed by the same username.

Answer

Store user profiles in a 'users' collection with auto-generated IDs, and rely on Firestore's automatic indexing to enforce uniqueness on the username field.

Question 4

A retail analytics team is building a BigQuery dataset to store point-of-sale transactions for ad-hoc analysis. Analysts frequently filter by `transaction_date` and `store_id`, and the data is append-only. The team wants to minimize query cost and maximize scan efficiency. Which design decision should the database engineer recommend?

Accepted Answer

Partition the table by `transaction_date` and cluster by `store_id`.. BigQuery charges by bytes scanned, so aligning the physical layout with query filters is the primary cost lever. Partitioning by `transaction_date` enables partition pruning for date-range filters, and clustering by `store_id` sorts data within partitions so store filters skip blocks. Together they minimize scanned bytes for the team's common query patterns on append-only transaction data.

Answer

Create the table with no partitioning and rely on BigQuery's automatic storage optimization.

Answer

Partition by `store_id` using integer range partitioning and cluster by `transaction_date`.

Answer

Create a materialized view over the base table and query the view instead.

Question 5

You are designing a Cloud Spanner schema for an IoT telemetry platform. The main table stores sensor readings with columns: device_id (STRING), event_timestamp (TIMESTAMP), reading_value (FLOAT64), and metadata (JSON). Queries almost always filter by device_id and a time range, and you want to minimize storage and improve scan efficiency. The primary key must support these queries while avoiding hotspots. What should you do?

Accepted Answer

Use a composite primary key with device_id as the first column and event_timestamp as the second column.. The correct design uses a composite primary key with device_id first and event_timestamp second. This ordering supports the dominant query pattern—filtering by device_id and a time range—by allowing efficient range scans. It also avoids hotspots because device_id is high-cardinality and not monotonically increasing, so writes spread across the key space. Secondary indexes or synthetic keys add overhead and are unnecessary here.

Answer

Use a synthetic primary key generated by a bit-reversed sequence, and create a secondary index on device_id and event_timestamp.

Answer

Use event_timestamp as the sole primary key and rely on Cloud Spanner's interleaving to group data by device_id.

Answer

Use a composite primary key with event_timestamp as the first column and device_id as the second column.

Question 6

A healthcare company is designing a Cloud SQL for PostgreSQL database to store patient records. The schema includes a 'patients' table and a 'visits' table. Visits are always associated with a patient, and when a patient is deleted, all their visits must be automatically removed. The team wants to ensure referential integrity and minimize application code. Which schema design should they use?

Accepted Answer

Define a foreign key on the visits table referencing the patients table with ON DELETE CASCADE.. A foreign key with ON DELETE CASCADE ensures that when a patient is deleted, all associated visits are automatically deleted by the database. This enforces referential integrity and eliminates the need for application-level deletion logic. Cloud SQL for PostgreSQL supports this standard SQL feature, making it the simplest and most reliable solution for the given requirement.

Answer

Use a database trigger on the patients table to delete related visits after a patient is deleted.

Answer

Define a foreign key on the visits table referencing the patients table with ON DELETE RESTRICT.

Answer

Store visits as a JSONB column within the patients table to avoid the need for a separate table and foreign key.

Question 7

A startup is building a new application on Firestore in Native mode. They need to store user profiles and frequently query users by their `email` address. They also want to minimize read costs and ensure low-latency lookups. What is the most efficient schema design for this requirement?

Accepted Answer

Store each user profile as a document in a `users` collection, using the `email` as the document ID.. The most efficient design is to use the email as the document ID in a `users` collection. This allows direct document reads by email, which are fast and cost-effective. It also ensures uniqueness of email addresses without needing additional indexes or queries. This approach aligns with Firestore best practices for natural keys.

Answer

Use a subcollection under each user document, with the email as the subcollection ID.

Answer

Store each user profile as a document in a `users` collection with an auto-generated document ID, and create a composite index on `email`.

Answer

Store all user profiles in a single document as a map, with email as the key.

Question 8

A financial analytics team uses BigQuery. They need to design a schema for a table that stores transaction records. The table will be queried with filters on customer_id, transaction_date, and amount. The team wants to minimize query costs and improve performance. They also need to enforce that customer_id is never null and that amount is always positive. Which schema design should they use?

Accepted Answer

Create a partitioned table on transaction_date, cluster by customer_id, and use NOT NULL constraints on customer_id and a CHECK constraint for amount > 0.. Partitioning by transaction_date enables partition pruning for date filters, and clustering by customer_id improves performance for customer-specific queries. Together, they reduce the amount of data scanned and lower query costs. BigQuery supports NOT NULL and CHECK constraints to enforce data integrity, ensuring customer_id is never null and amount is positive. This combination best meets the requirements.

Answer

Create a partitioned table on transaction_date, cluster by amount, and use NOT NULL constraints on customer_id and a CHECK constraint for amount > 0.

Answer

Create a partitioned table on customer_id, cluster by transaction_date, and use NOT NULL constraints on customer_id and a CHECK constraint for amount > 0.

Answer

Create a non-partitioned table with NOT NULL constraints on customer_id and a CHECK constraint for amount > 0, and rely on BigQuery's automatic optimization.

Question 9

A retail company uses Cloud Bigtable to store user session data. The row key is currently a reversed timestamp (e.g., Long.MAX_VALUE - timestamp) to allow efficient retrieval of the most recent sessions. The company now needs to query sessions by user_id and also retrieve the most recent session for each user. The table is expected to grow to tens of terabytes. They want to optimize for both write throughput and read performance for these queries. What schema design change should they make?

Accepted Answer

Change the row key to user_id followed by a reversed timestamp, so that all sessions for a user are stored contiguously and sorted by most recent first.. A composite row key of user_id followed by a reversed timestamp groups each user's sessions together and orders them most recent first. This enables efficient range scans for a user's sessions and a single seek for the latest session. It also distributes writes across many users, avoiding hotspotting. This is the recommended pattern for time-series data per entity in Cloud Bigtable.

Answer

Use user_id as the row key and store sessions as multiple versions in a column family, relying on cell versioning for timestamps.

Answer

Keep the reversed timestamp as the row key and add a column family for user_id, then use filters to query by user_id.

Answer

Create a separate table for each user, with row keys as timestamps, to isolate user data.

Question 10

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?

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 11

A startup is building a mobile app with Firestore in Native mode. They need to store user profiles and allow users to query by email address. The email field must be unique. What is the most appropriate schema design?

Accepted Answer

Store each user profile as a document in a 'users' collection, using the email address as the document ID.. In Firestore, document IDs are unique within a collection. Using the email address as the document ID naturally enforces uniqueness and enables direct document retrieval by email without needing a query. This approach is efficient, requires no additional indexes or collections, and aligns with Firestore's design for unique identifiers.

Answer

Store each user profile as a document in a 'users' collection, using a combination of email and a random suffix as the document ID to avoid collisions.

Answer

Store each user profile as a document with an auto-generated ID and create a single-field index on the email field.

Answer

Store each user profile as a document with an auto-generated ID and create a separate 'emails' collection mapping email to user ID.

Question 12

A media company uses Cloud Firestore in Native mode for a recommendations service. Each user document contains a `followedTopics` array of topic IDs, and the service must efficiently query all users who follow a given topic. The array can grow to several hundred entries per user. Which schema design should the database engineer use?

Accepted Answer

Create a top-level `follows` collection where each document has `userId` and `topicId` fields, and query by `topicId`.. Firestore is a document store without joins, so many-to-many relationships are best modeled with a junction collection. Storing one document per user-topic follow with indexed `userId` and `topicId` fields allows efficient equality queries in either direction. This avoids the array index and document size limits that would constrain a large embedded array and scales as follows accumulate.

Answer

Keep the array in the user document and query with an `array-contains` filter on `followedTopics`.

Answer

Create a subcollection under each user document containing one document per followed topic.

Answer

Store a denormalized `followerCount` and a `topFollowers` array on each topic document and query that document.

Question 13

A healthcare company stores patient records in Cloud SQL for PostgreSQL. The database schema includes a `patients` table with a `patient_id` primary key and a `visits` table that references `patients` via a foreign key. The application frequently runs a query that retrieves a patient's details along with all their visits. The team notices that these queries are slow and wants to optimize the schema. What should they do?

Accepted Answer

Add an index on the `patient_id` column in the `visits` table.. The most effective and standard optimization is to add an index on the foreign key column `patient_id` in the `visits` table. This allows PostgreSQL to efficiently retrieve all visits for a given patient, significantly speeding up the join query. Other options either introduce data integrity risks, add write overhead, or do not directly address the join performance issue.

Answer

Partition the `visits` table by `patient_id` to improve query performance.

Answer

Create a materialized view that joins `patients` and `visits` and refresh it on every write.

Answer

Denormalize the schema by storing patient details directly in the `visits` table.

Question 14

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 15

A retail company runs Cloud Spanner for its order management system. The `Orders` table has a primary key `OrderId` that is a monotonically increasing integer generated by the application. During peak shopping periods, the team observes that all writes are concentrated on a single Spanner server, causing high latency. They want to redesign the schema to distribute writes evenly while preserving fast lookups by `OrderId`. Which schema change should they implement?

Accepted Answer

Use a bit-reversed or hashed `OrderId` as the primary key and store the original `OrderId` as a non-key column.. A monotonically increasing primary key in Cloud Spanner creates a write hotspot because new rows are always inserted at the end of the key space, landing on the same split. Hashing or bit-reversing the key spreads inserts across many splits, eliminating the hotspot. Storing the original OrderId as a non-key column preserves the ability to retrieve orders by their natural identifier, and a secondary index can be added if point lookups by OrderId are required.

Answer

Add a secondary index on `CustomerId` and query orders by customer instead of by `OrderId`.

Answer

Enable `optimize_for_write` on the `Orders` table using an ALTER TABLE statement.

Answer

Change the primary key to `(OrderId, CustomerId)` and interleave `OrderItems` under `Orders`.