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

Question 1

Based on the exhibit, what is the primary key of the Readings table?

Accepted Answer

(SensorId, Timestamp). The Readings table captures sensor measurements over time, so the natural primary key is the combination of SensorId and Timestamp, which uniquely identifies each reading. This is a classic example of a composite primary key in relational database design, ensuring that no two readings from the same sensor at the same time can exist.

Answer

(SensorId, SensorType)

Answer

(SensorId)

Answer

(ReadingsId)

Question 2

A financial services company uses Cloud Spanner for transaction processing. They need to run analytical queries that scan large portions of the database without impacting OLTP performance. What schema design technique should they use?

Accepted Answer

Design the primary key so that analytical queries scan a small number of tablets by using interleaved tables.. Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same tablet (split). This ensures that analytical queries scanning a large portion of the database can be served by a small number of tablets, minimizing cross-tablet reads and reducing contention with OLTP traffic. By designing the primary key to leverage interleaving, you keep analytical scans localized and avoid the performance penalty of scattering reads across many tablets.

Answer

Export data periodically to BigQuery and run queries there.

Answer

Create multiple secondary indexes on frequently scanned columns.

Answer

Use a read replica instance to offload analytical queries.

Question 3

A Cloud Firestore database stores documents for a mobile app. The app frequently queries for documents where a specific Boolean field is true. The field is not part of the collection group index. What should the developer do to improve query performance?

Accepted Answer

Create a composite index that includes the Boolean field and the query ordering field.. Cloud Firestore requires a composite index to efficiently query on a Boolean field combined with an ordering field. Without this index, the query would perform a full collection scan, leading to poor performance. Creating a composite index that includes the Boolean field and the query ordering field allows Firestore to use the index to directly locate matching documents, avoiding expensive sequential scans.

Answer

Add a synthetic field that combines the Boolean with a timestamp for range queries.

Answer

Denormalize the Boolean field into separate subcollections.

Answer

Rely on the automatic single-field index already created.

Question 4

A multinational corporation uses Cloud Spanner with a multi-region configuration. The schema includes a table that is updated frequently by users in two distant regions. They are experiencing high commit latencies due to distributed transactions. Which schema change would most reduce latency?

Accepted Answer

Use a table-level leader placement configuration to keep the table's splits in a single region.. Using a table-level leader placement configuration allows you to pin the leader replica for a table to a specific region, reducing cross-region latency for writes. This is particularly effective for frequently updated tables in multi-region Spanner configurations.

Answer

Reduce the number of replicas in the Spanner configuration.

Answer

Convert the table into an interleaved child of a parent table.

Answer

Increase the number of splits by using a more granular primary key.

Question 5

Refer to the exhibit. The team notices high write latency on the Events table. They are inserting 1,000 events per second. The EventId is generated by a sequence. What is the most likely issue?

Accepted Answer

The sequential primary key creates a hotspot on a single split.. The sequential primary key (EventId generated by a sequence) causes all new writes to be directed to the last tablet or split in the table, creating a hotspot. In Cloud Spanner, this leads to contention on a single split, increasing write latency despite adequate overall throughput capacity.

Answer

The allow_commit_timestamp option on CreatedAt column adds overhead.

Answer

The BYTES(MAX) data type causes excessive writing.

Answer

The node count is insufficient for the write throughput.

Question 6

A startup is migrating from MongoDB to Firestore in Datastore mode. Their existing documents contain nested arrays of sub-objects (e.g., tags, comments). They want to design a schema that scales well and supports efficient queries. What is the recommended approach for handling these nested arrays in Firestore?

Accepted Answer

Flatten the arrays into subcollections under each document.. Firestore in Datastore mode does not support querying or indexing nested array elements efficiently, which can lead to performance issues as the dataset grows. Flattening nested arrays into subcollections allows each sub-object to be a separate document, enabling scalable queries and proper indexing without the limitations of array-based storage.

Answer

Use maps instead of arrays to store the data.

Answer

Store the arrays as stringified JSON in a single field.

Answer

Keep the nested arrays as they are; Firestore supports arrays.

Question 7

A financial services company uses Cloud Spanner for a ledger application. The ledger table has a primary key of 'transaction_id' which is a monotonically increasing integer. During peak hours, they observe high write latencies due to hot spots on the last tablet. They need to redesign the schema to distribute writes evenly while still allowing efficient point lookups by transaction ID. What is the best approach?

Accepted Answer

Use a composite primary key with a hash prefix of the transaction ID as the first component, followed by the transaction ID.. Using a hash prefix of the transaction ID as the first component of a composite primary key distributes writes across multiple tablets in Cloud Spanner, avoiding hot spots on a single tablet. The transaction ID as the second component still enables efficient point lookups by transaction ID, as Spanner can use the hash prefix to locate the tablet and then scan within it for the exact row.

Answer

Reverse the timestamp and use it as the primary key.

Answer

Use a UUID as the primary key to ensure randomness.

Answer

Use a composite primary key with a timestamp and a random number.

Question 8

A company is migrating a large Oracle database to Cloud Spanner. They need to define the schema for relational tables with foreign keys. Which THREE considerations are important when designing the Spanner schema? (Choose three.)

Accepted Answer

Use INTERLEAVE tables to model parent-child relationships.. Option B is correct because Cloud Spanner uses table interleaving to physically co-locate child rows with their parent rows, which models parent-child relationships efficiently and improves join performance for hierarchical data. Option D is correct because Cloud Spanner requires secondary indexes to efficiently query columns that are not part of the primary key, since the base table is sorted only by primary key. Option E is correct because Cloud Spanner supports and automatically enforces foreign key constraints, ensuring referential integrity between tables without manual application-level checks. Option A is incorrect because primary key columns in Cloud Spanner cannot contain NULL values; keys must be NOT NULL. Option C is incorrect because composite primary keys are fully supported and often recommended in Spanner to distribute data evenly and avoid hotspots.

Answer

Use NULL values in primary key columns to allow optional fields.

Answer

Avoid using composite primary keys; use single-column keys instead.

Question 9

A startup is using Firestore in Native mode for a real-time chat application. They want to design the schema for chat rooms and messages. Which TWO design patterns are recommended? (Choose two.)

Accepted Answer

Use a composite index on chat room ID and timestamp.. A composite index on chat room ID and timestamp is essential for efficiently querying messages in order within a specific chat room. Firestore requires composite indexes for queries that combine equality filters on one field (chat room ID) with an order on another (timestamp). Option E is correct because storing messages as documents in a subcollection under each chat room document is the recommended pattern for Firestore, as it allows scalable, independent message collections per room without hitting the 1 MiB document size limit.

Answer

Use arrays in the chat room document to store message IDs.

Answer

Store all messages in a single top-level collection with a field for chat room ID.

Answer

Use a separate top-level collection for each chat room.

Question 10

A global gaming company uses Cloud Spanner for player profiles and game state. The schema includes a table 'PlayerStats' with a primary key (PlayerId, GameId, Timestamp). The table stores millions of rows per player. The application frequently runs a query to fetch the most recent stats for a given player across all games, using ORDER BY Timestamp DESC LIMIT 10. This query is slow, taking several seconds. The team adds a secondary index on (PlayerId, Timestamp) but still sees high CPU usage and latency. They need to redesign the schema to optimize this query without changing the application logic significantly. What should they do?

Accepted Answer

Change the primary key to (PlayerId, Timestamp, GameId) and drop the secondary index.. Changing the primary key to (PlayerId, Timestamp, GameId) aligns the physical row ordering with the query pattern: Cloud Spanner stores rows sorted by primary key, so rows for a given player are stored contiguously ordered by timestamp descending. This eliminates the need for a secondary index and allows the ORDER BY Timestamp DESC LIMIT 10 query to be satisfied by a direct range scan on the primary key, dramatically reducing CPU and latency.

Answer

Migrate the PlayerStats table to Cloud Bigtable for better time-series performance.

Answer

Create a stored procedure that aggregates data per player and caches results.

Answer

Add a materialized view that pre-computes the latest stats per player.

Question 11

A healthcare analytics company uses Cloud Bigtable to store time-series data from medical devices. The table has a row key of 'device_id#timestamp' where timestamp is stored in reverse order (max - timestamp) so that recent data is at the top. Queries that fetch data for a specific device over a date range are very fast. However, analysts also need to run queries that aggregate data across all devices for a specific hour (e.g., count of readings between 2023-01-01 10:00 and 11:00). These queries are extremely slow because they require scanning all rows. The team must redesign the schema to support both access patterns without duplicating data unnecessarily. What is the best approach?

Accepted Answer

Create a second table with row key 'timestamp#device_id' (with timestamp in natural order) to support time-range queries.. Creating a second table with row key 'timestamp#device_id' (with timestamp in natural order) allows efficient range scans for time-based queries across all devices. Bigtable sorts data lexicographically by row key, so a timestamp-first key groups data by time, enabling fast retrieval for a specific hour across all devices. This approach supports both access patterns without duplicating data unnecessarily, as the second table can be a materialized view or derived from the first.

Answer

Use BigQuery to query Bigtable via an external table and run the aggregation there.

Answer

Increase the number of Bigtable nodes to improve scan throughput.

Answer

Add a secondary index on the timestamp column.

Question 12

An online advertising platform uses Cloud Spanner for ad impression tracking. The table 'ad_impressions' has a primary key (ad_id, timestamp). The table receives millions of writes per minute. A secondary index on (campaign_id, timestamp) was created to support queries that sum impressions per campaign. During high traffic, the team notices increased write latency and hotspotting on the index (the campaign_id has low cardinality, causing all writes to a campaign to hit the same index split). They need to redesign the schema to avoid hotspotting on the index while still supporting the campaign aggregation queries. What is the best solution?

Accepted Answer

Modify the secondary index to include a hash prefix (e.g., use 'hash(campaign_id)' as the first column of the index).. Adding a hash prefix to the index key (e.g., using a hash of campaign_id as the leading column) distributes index writes across multiple splits, eliminating the hotspotting on the secondary index. Option B (migrating to Bigtable) introduces operational complexity and does not leverage Spanner's existing capabilities. Option C (changing the base table's primary key to start with campaign_id) would not fix the index hotspot because the secondary index on (campaign_id, timestamp) would still concentrate writes on a single split due to low cardinality of campaign_id. Option D (creating a separate aggregation table) adds complexity and would require additional mechanisms to keep it updated, which can be error-prone and may not solve the underlying index hotspot.

Answer

Migrate the ad_impressions table to Cloud Bigtable with row key 'campaign_id#timestamp'.

Answer

Change the primary key of the base table to include campaign_id as the first column.

Answer

Create a separate table that stores per-campaign aggregations, updated in real time.

Question 13

Refer to the exhibit.

Which of the following statements is true regarding this schema design?

Accepted Answer

Deleting a Singer row will automatically delete all associated Album rows.. The `ON DELETE CASCADE` clause on the foreign key from `Albums` to `Singer` ensures that when a row in the `Singer` table is deleted, all rows in the `Albums` table that reference that singer are automatically deleted. This is a standard referential integrity behavior in relational databases, and in Cloud Spanner (the technology context for PCDE), it is enforced at the database level to maintain consistency.

Answer

The Albums table cannot have any secondary indexes because of the INTERLEAVE clause.

Answer

The Albums table's rows are physically stored independent of the Singer table.

Answer

The Albums table's primary key must include the SingerId column only.

Answer

The ON DELETE CASCADE clause ensures that deleting an Album row will delete the corresponding Singer row.

Question 14

Your company runs a global e-commerce platform on Google Cloud Spanner. The database schema includes an 'Orders' table with primary key (OrderId, CustomerId) and an 'OrderItems' table with primary key (OrderId, CustomerId, ItemId), interleaved in parent Orders on delete cascade. During peak shopping hours, you notice that queries retrieving all items for a specific order are performing full table scans on the OrderItems table, leading to increased latency and higher CPU utilization. The queries use the OrderId as the filter condition. The database administrators have already checked that the query plans show table scans instead of using the interleaved index. You are tasked with resolving this performance issue. Which of the following actions should you take?

Accepted Answer

Remove CustomerId from the Orders primary key (making it just OrderId) and update OrderItems to have primary key (OrderId, ItemId), maintaining interleaving.. The interleaved index in Cloud Spanner requires that the parent table's primary key columns be a prefix of the child table's primary key. With the original schema, queries filtering only on OrderId cannot use the interleaved index because CustomerId is missing from the filter, forcing a full table scan. By removing CustomerId from the primary key of Orders and OrderItems, OrderId becomes the leading column, allowing the interleaved index to be used for efficient point lookups.

Answer

Change the primary key of Orders to (OrderId, CustomerId) and update OrderItems accordingly.

Answer

Create a secondary index on OrderItems(OrderId).

Answer

Increase the number of Spanner nodes to improve throughput.

Question 15

A financial analytics team uses BigQuery. They have a table `transactions` partitioned by `transaction_date` (DATE) and clustered by `customer_id`. They frequently run queries that filter on `customer_id` and a range of `transaction_date`, but they also need to retrieve only the latest transaction per customer. Which schema design change would best optimize these queries while minimizing cost?

Accepted Answer

Create a materialized view that pre-aggregates the latest transaction per customer.. A materialized view that pre-aggregates the latest transaction per customer is ideal because it stores the computed result and can be automatically maintained by BigQuery. Queries against the materialized view scan far fewer bytes than the base table, and the optimizer can rewrite queries to use it. This directly addresses the need for the latest transaction per customer while keeping costs low.

Answer

Use a wildcard table query to union all transactions and filter in the query.

Answer

Change the partitioning column to `customer_id` and cluster by `transaction_date`.

Answer

Add a clustering key on `transaction_amount` to improve filtering.