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

Question 1

A global e-commerce company is designing a Cloud Spanner schema for order processing. They need strong consistency across regions and high write throughput. Orders are identified by a globally unique order ID (UUID). Currently, they use the UUID as the primary key, but they observe write hotspots during peak hours. What primary key design change should they make to distribute writes more evenly?

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

Accepted Answer

Use a composite primary key starting with a hash of the order ID, followed by the order ID.. Using a composite primary key starting with a hash of the order ID distributes writes evenly across all Cloud Spanner nodes, preventing hotspots. Cloud Spanner uses the first column of the primary key to determine row distribution; a monotonically increasing UUID as the first column causes all new writes to land on the same tablet, creating a hotspot. By hashing the UUID first, writes are spread uniformly across the key space, while the order ID ensures uniqueness.

Answer

Use the timestamp of order creation as the primary key.

Answer

Use a sequential integer primary key with auto-increment.

Answer

Keep UUID as primary key but add a secondary index on a hash of the UUID.

Question 2

A social media platform uses Cloud SQL for PostgreSQL for its user and post data. The schema has a normalized design with separate 'users' and 'posts' tables. Queries that fetch a user's timeline (joining users and posts) are slow due to heavy read volume. The team wants to optimize the schema for this read-heavy workload without changing the application logic significantly. What schema design change is most appropriate?

Accepted Answer

Denormalize by embedding commonly accessed user fields (e.g., username, avatar URL) into the posts table.. Denormalizing by embedding frequently accessed user fields (username, avatar URL) into the posts table eliminates the join on every timeline read, which is the dominant query pattern. This trades a small amount of storage and write-time complexity for a large read-latency improvement, and it can be done without changing application logic significantly because the posts table still returns the needed fields.

Answer

Migrate to a NoSQL database like Firestore for better read performance.

Answer

Create a materialized view that joins users and posts, refreshed periodically.

Answer

Add GIN indexes on the posts table for faster full-text search.

Question 3

An e-commerce platform uses Cloud Bigtable for real-time analytics on customer behavior. The table uses a row key of 'customer_id#timestamp' (customer ID followed by reverse timestamp). Queries for a specific customer's recent events are fast, but queries that filter by event type (e.g., 'purchase') across many customers are slow. What schema change can improve query performance for event-type filtering?

Accepted Answer

Change the row key to 'event_type#customer_id#timestamp'.. Cloud Bigtable's performance depends heavily on row key design for efficient scans. By changing the row key to 'event_type#customer_id#timestamp', queries filtering by event type can use a single row key prefix scan, which is fast and avoids full table scans. This leverages Bigtable's lexicographic ordering to group all events of the same type together, making event-type filtering a range scan rather than a filter across unrelated rows.

Answer

Create a separate column family for each event type.

Answer

Add a secondary index on the event_type column.

Answer

Use a separate Bigtable instance for each event type.

Question 4

Your team is designing a schema for Cloud SQL (MySQL) for a content management system. You need to implement full-text search on article content. Which TWO schema design choices are appropriate? (Choose two.)

Accepted Answer

Use Cloud SQL's built-in full-text search feature.. Cloud SQL for MySQL provides built-in full-text search capabilities that allow efficient searching of text data. Adding a FULLTEXT index on the content column enables the use of MATCH...AGAINST queries, which are optimized for natural language search and are far more performant than LIKE operations. This is the correct approach because it leverages the database engine's native indexing and search algorithms.

Answer

Use the LIKE operator with wildcards for pattern matching.

Answer

Store article content in a Cloud Storage bucket and query metadata.

Answer

Normalize content into a separate table and use joins.

Question 5

A retail company uses Cloud SQL for PostgreSQL for inventory management. The schema has a table 'inventory' with columns: product_id, warehouse_id, quantity, last_updated. The table contains over 100 million rows. The application frequently runs aggregate queries to compute total quantity of a product across all warehouses (e.g., SELECT SUM(quantity) FROM inventory WHERE product_id = ?). These queries are slow, taking tens of seconds. The team tries a covering index on (product_id, quantity) but sees little improvement because they still need to scan many rows. They need to redesign the schema to improve aggregation performance. What is the best approach?

Accepted Answer

Create a summary table 'product_totals' with columns product_id and total_quantity, and use triggers to keep it updated on INSERT/UPDATE/DELETE in inventory.. A summary table precomputes the SUM(quantity) per product_id, so the aggregate query becomes a single-row lookup on a small table instead of scanning millions of inventory rows. Triggers on INSERT/UPDATE/DELETE keep the total_quantity synchronized with the base table, giving O(1) read performance for the aggregation pattern. This is the classic materialized-aggregate pattern for read-heavy OLTP workloads where the aggregation is expensive but the underlying data changes at a manageable rate.

Answer

Add a covering index on (product_id, quantity).

Answer

Migrate the inventory table to Cloud Spanner and use interleaved indexes.

Answer

Use BigQuery as a read replica and query there.

Question 6

A mobile app backend uses Firestore for user profiles. The schema has a single collection 'users' where each document contains: user_id (used as document ID), name, email, and friends (an array of user IDs). The friends array can grow large (thousands of IDs). When a user adds a friend, the application updates the array, causing the document to grow and leading to write contention and size limit warnings. The team needs to redesign the schema to scale better. What is the best approach?

Accepted Answer

Create a new 'friendships' collection with documents containing user_id_1 and user_id_2 fields.. It normalizes the friendship relationship into a separate 'friendships' collection, where each document represents a single bidirectional link between two users. This avoids unbounded document growth and write contention on user documents, as adding a friend only requires a small write to a new friendship document rather than updating a potentially large array. Firestore's 1 MiB document size limit and 1 write per second per document limit are no longer risk factors.

Answer

Move the friends list to a subcollection under each user document.

Answer

Migrate user profiles and friendships to Cloud SQL for relational capabilities.

Answer

Limit the maximum size of the friends array to 1000 at the application level.

Question 7

A startup uses Cloud SQL (MySQL) for a blogging platform. The schema has a table 'posts' with columns: post_id (auto-increment PK), title, content, author_id, created_at. The application frequently runs a query to display the latest 10 posts from a specific author: SELECT * FROM posts WHERE author_id = ? ORDER BY created_at DESC LIMIT 10. This query is slow when an author has thousands of posts. The team wants to optimize this query without changing the application code. What schema change will be most effective?

Accepted Answer

Add a composite index on (author_id, created_at DESC).. A composite index on (author_id, created_at DESC) directly supports the query's WHERE clause on author_id and its ORDER BY created_at DESC, allowing MySQL to seek to the author's rows and read them in already-sorted order without a filesort. This is the most effective schema change because it requires no application code changes and addresses the exact access pattern. Cloud SQL for MySQL supports descending indexes, so the DESC direction is honored.

Answer

Partition the table by author_id using range partitioning.

Answer

Increase the query cache size in Cloud SQL.

Answer

Migrate the posts table to Cloud Spanner and use interleaved indexes.

Question 8

A financial services company is designing a Cloud Spanner schema for a trading system. They have two main entities: 'accounts' and 'transactions'. Each account has many transactions, and queries almost always retrieve transactions for a specific account. Which TWO schema design strategies should they employ?

Accepted Answer

Ensure the primary key of transactions includes the account_id as the first part.. Option B is correct because making account_id the first part of the transactions primary key co-locates and sorts all of an account's transactions together, so queries filtering by account_id become efficient primary-key range scans rather than full-table scans. Option E is correct because interleaving transactions as a child of accounts physically stores each account's transactions with the parent row, giving locality and enabling efficient joins/reads for account-specific transaction queries. Option A is not needed since a secondary index on transactions.account_id is redundant when account_id already leads the primary key, and it would add write amplification. Option C is wrong because foreign keys in Spanner enforce referential integrity but do not improve query performance or data locality. Option D is wrong because storing transactions as a JSON array inside the account row is an anti-pattern that limits transaction size, prevents efficient per-transaction updates, and breaks Spanner's relational model.

Answer

Use a secondary index on transactions.account_id.

Answer

Define a foreign key constraint from transactions to accounts.

Answer

Store transactions as a JSON array of repeating fields within the account record.

Question 9

A retail company runs an order-processing system on Cloud Spanner. The `Orders` table has a primary key `OrderId` (a UUID string). Query performance for fetching recent orders by `CustomerId` is poor because each query scans many rows. You need to improve read performance for queries that filter by `CustomerId` and sort by `OrderDate` descending. What should you do?

Accepted Answer

Create a secondary index on `CustomerId` and `OrderDate` (descending) and store `OrderId` as a stored column.. A secondary index on `CustomerId` and `OrderDate` directly supports the query pattern by allowing Cloud Spanner to seek to the relevant customer and read orders in the desired sort order. Storing `OrderId` in the index makes it covering, so the query can be satisfied entirely from the index, avoiding extra lookups to the base table and improving performance.

Answer

Add a foreign key constraint between `CustomerId` and a `Customers` table.

Answer

Change the primary key to `CustomerId` and make `OrderId` a non-key column.

Answer

Partition the `Orders` table by `OrderDate` using table partitioning.

Question 10

You are designing a Cloud Spanner schema for a social networking application. The main entity is a 'User' table. Each user has a unique username, and you need to efficiently retrieve a user by username. Additionally, you want to interleave a 'Post' table with 'User' to co-locate posts with their author for efficient joins. Which primary key design for the 'User' table best meets these requirements and optimizes for Cloud Spanner's architecture?

Accepted Answer

Use the username as the primary key.. The username as the primary key directly supports efficient retrieval by username and enables interleaving of 'Post' with 'User' using the same key, co-locating related data for efficient joins. While random UUIDs distribute writes, they require a secondary index for username lookups. Composite keys add unnecessary complexity. Monotonically increasing integers cause hotspots. Thus, using the username is the best balance for the given requirements.

Answer

Use a composite primary key of (username, user_id).

Answer

Use a monotonically increasing integer as the primary key.

Answer

Use a randomly generated UUID as the primary key.

Question 11

You are designing a schema for a Cloud SQL for PostgreSQL database that will store time-series data from IoT sensors. The data volume is high, and queries will frequently filter by sensor_id and a time range. You want to optimize query performance and minimize storage. Which schema design approach is most appropriate?

Accepted Answer

Use PostgreSQL declarative partitioning by range on timestamp, with subpartitions by hash on sensor_id, and create indexes on each partition.. Declarative partitioning by range on timestamp with subpartitions by hash on sensor_id and local indexes enables efficient partition pruning for time-range queries and even data distribution for sensor_id filters. It also simplifies data lifecycle management by allowing old partitions to be dropped. The other options either lack partitioning, leading to larger indexes and slower queries, or use separate tables, which is operationally complex. Thus, the partitioned design is most appropriate.

Answer

Create a single table with columns (sensor_id, timestamp, value) and a composite index on (sensor_id, timestamp).

Answer

Store each sensor's data in a separate table, with a table name like sensor_<id>.

Answer

Use a single table with a BRIN index on timestamp and a B-tree index on sensor_id.

Question 12

A media company ingests time-series telemetry from millions of IoT devices into Cloud BigQuery. Analysts frequently run ad-hoc queries that filter on device_id and a timestamp range, and they also aggregate data by hour. The table will grow to petabytes, and the company wants to minimize query cost and avoid scanning irrelevant data. The team must choose a partitioning strategy. Which approach should they use?

Accepted Answer

Partition the table by the ingestion timestamp using time-unit column partitioning on a TIMESTAMP column, and optionally cluster by device_id.. Partitioning by the ingestion timestamp enables partition pruning for time-range filters, which is the dominant query pattern. Clustering by device_id further reduces the data scanned when filtering on specific devices. Together, they minimize cost and improve performance for both ad-hoc and aggregation queries on a petabyte-scale time-series table.

Answer

Create a separate table for each day and use wildcard table queries.

Answer

Partition the table by an integer range on a synthetic device identifier column.

Answer

Use a single unpartitioned table and rely on BigQuery's automatic optimizations.

Question 13

A financial services company uses Cloud Spanner for a global ledger application. The 'Transactions' table has a primary key of 'TransactionId' (a UUID string) and includes columns 'AccountId', 'Amount', and 'Timestamp'. The application frequently runs queries that retrieve all transactions for a specific account within a date range, sorted by timestamp. These queries are currently slow because they require a full table scan. The team wants to optimize the schema for these queries without changing the application's write pattern, which inserts transactions with random UUIDs. What should they do?

Accepted Answer

Create a secondary index on (AccountId, Timestamp) and include the 'Amount' column as a stored column.. Creating a secondary index on (AccountId, Timestamp) allows Cloud Spanner to quickly find all transactions for a given account within a time range, and the index is sorted by timestamp, satisfying the sort order. Storing 'Amount' in the index makes it covering, so the query can be answered entirely from the index without fetching the base row. This approach optimizes read performance while leaving the primary key and write pattern unchanged.

Answer

Change the primary key to (AccountId, Timestamp, TransactionId) to cluster transactions by account and time.

Answer

Create a secondary index on (Timestamp, AccountId) and rely on the query optimizer to use it for account-specific queries.

Answer

Add a foreign key from 'Transactions' to an 'Accounts' table and interleave 'Transactions' in 'Accounts'.

Question 14

A company is migrating an on-premises MySQL database to Cloud SQL for MySQL. The existing schema uses the `ENUM` data type for a `status` column in the `orders` table. The application frequently filters and sorts by this column. What should the database engineer do to ensure optimal performance and compatibility in Cloud SQL?

Accepted Answer

Keep the `ENUM` data type as is; Cloud SQL for MySQL supports it natively.. Cloud SQL for MySQL supports the `ENUM` data type natively, so there is no compatibility issue. `ENUM` is stored efficiently as integers and provides fast filtering and sorting. Keeping it as is avoids unnecessary schema changes and preserves application performance and behavior.

Answer

Replace the `ENUM` with a foreign key to a lookup table.

Answer

Replace the `ENUM` with a `VARCHAR` column and create an index on it.

Answer

Convert the `ENUM` to a `SET` data type to allow multiple values.

Question 15

A healthcare company is designing a BigQuery schema for patient data. They need to store patient records and allow efficient querying by multiple attributes such as patient ID, date of birth, and zip code. The data volume is very large, and queries often filter on combinations of these attributes. The team wants to minimize query cost and maximize performance. Which schema design approach should they use?

Accepted Answer

Partition the table by a date column such as admission date and cluster by patient ID, date of birth, and zip code.. Partitioning by a date column such as admission date allows BigQuery to prune partitions when queries include a filter on that date. Clustering by patient ID, date of birth, and zip code sorts data within partitions, so queries filtering on these columns can skip irrelevant blocks. This combination reduces the amount of data scanned, lowering cost and improving performance for common query patterns.

Answer

Partition the table by date of birth and cluster by patient ID and zip code.

Answer

Create separate tables for each attribute and use joins to combine them when querying.

Answer

Create a single table with all patient attributes and rely on BigQuery's automatic clustering and partitioning.