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

Question 1

A developer is designing a schema for Firestore to store user profiles. Each user has a unique ID and multiple addresses. Which data modeling approach is recommended for Firestore?

Accepted Answer

Store addresses as a nested map within the user document.. Firestore is a document-oriented NoSQL database, and the recommended pattern for storing related data that is always accessed together with the parent document is to embed it as a nested map (or array of maps). Since addresses belong to a single user and are typically read alongside the profile, embedding them as a nested map within the user document avoids extra reads and keeps the data denormalized for fast access.

Answer

Store addresses as a string array in the user document.

Answer

Use a relational join between users and addresses collection.

Answer

Create a separate collection for addresses with a reference to user ID.

Question 2

A team is designing a BigQuery schema for time-series analytics on IoT sensor data. They expect high write throughput and queries that aggregate data by hour. Which partitioning and clustering strategy is most cost-effective?

Accepted Answer

Partition by date and cluster by sensor_id with a timestamp column.. Partitioning by date (e.g., _PARTITIONDATE) allows BigQuery to prune partitions when querying hourly aggregates, drastically reducing scanned bytes. Clustering by sensor_id with a timestamp column further organizes data within each partition, enabling efficient filtering and sorting for sensor-specific queries. This combination optimizes both write throughput (partitioning avoids small, frequent partitions) and query performance (clustering reduces scan on sensor_id filters).

Answer

Partition by ingestion_time and cluster by sensor_id.

Answer

Use integer range partitioning on sensor_id.

Answer

Partition by sensor_id and cluster by timestamp.

Question 3

A company is designing a Firestore schema for a chat application with millions of messages. They need to support real-time updates and efficient querying of recent messages per conversation. Which schema and indexing strategy is optimal?

Accepted Answer

Store messages as a subcollection under each conversation document. Create a composite index on (conversationId, timestamp desc).. Storing messages as a subcollection under each conversation document allows for natural data locality and efficient queries. The composite index on (conversationId, timestamp desc) enables Firestore to quickly retrieve the most recent messages for a given conversation without scanning unrelated data, which is critical for real-time updates at scale.

Answer

Store all messages in a single top-level collection. Create an index on (conversationId, timestamp desc).

Answer

Store messages in a subcollection with a single-field index on timestamp.

Answer

Use a parent document with a nested array of recent messages, and a separate collection for older messages.

Question 4

A data warehouse in BigQuery stores daily snapshots of customer data. The schema uses a single table with a snapshot_date partition column. Over time, the table has grown to 10 TB and queries often scan entire partitions. Which schema redesign would improve query performance and reduce costs significantly?

Accepted Answer

Use a nested and repeated structure to store all snapshots per customer in a single row.. Storing all snapshots per customer in a nested and repeated structure (e.g., an array of structs) eliminates the need to scan multiple rows for the same customer across different snapshot dates. This reduces the table size by avoiding row duplication, and queries that filter on customer_id can leverage the nested structure to read only the relevant data, significantly cutting both query costs (less data scanned) and improving performance.

Answer

Create separate tables for each snapshot_date.

Answer

Use clustering on customer_id and snapshot_date.

Answer

Use a wildcard table with a _TABLE_SUFFIX filter.

Question 5

A company uses Cloud Spanner with a schema that includes a table 'Events' with primary key (EventId, Timestamp). They need to run range queries on Timestamp across all events. They notice slow queries. Which two actions can improve query performance? (Choose two.)

Accepted Answer

Create a secondary index on Timestamp.. Option A is correct because Cloud Spanner stores rows sorted by primary key, so a range scan on Timestamp alone cannot be served efficiently when Timestamp is only the second key column; creating a secondary index on Timestamp gives Spanner a sorted access path for those range predicates. Option B is correct because a covering (storing) index on Timestamp that includes all columns referenced by the query lets Spanner answer the query entirely from the index, avoiding an extra lookup into the base table for each row and thereby reducing latency and backfill cost. Option C is not appropriate here because hash prefixes on EventId address write hotspotting from monotonically increasing keys, not read performance for Timestamp range scans. Option D is not appropriate because interleaving on EventId co-locates child rows with a parent keyed by EventId, which does not help a query that ranges over Timestamp across all events. Option E is not appropriate because changing the primary key to (Timestamp, EventId) would make Timestamp the leading key and could help that query, but it is a destructive schema change that breaks the existing key design and uniqueness semantics for EventId, and it is not one of the marked correct answers.

Answer

Add a hash prefix to EventId to distribute writes.

Answer

Use interleaving with a parent table on EventId.

Answer

Change the primary key to (Timestamp, EventId).

Question 6

A data scientist runs a complex SQL query on a large BigQuery dataset and receives the above error. The query joins 10 tables and uses multiple window functions. Which action is most likely to resolve the issue?

Accepted Answer

Simplify the query by reducing the number of joins or using a temporary table.. The error is likely a resource or memory exhaustion error caused by the complexity of the query (10 joins and multiple window functions). Simplifying the query by reducing joins or using temporary tables reduces the amount of data shuffled and processed in a single stage, directly addressing the root cause. In BigQuery, complex queries with many joins and window functions can exceed slot memory limits, and breaking them into simpler steps avoids this.

Answer

Apply for a quota increase for concurrent queries.

Answer

Increase the number of slots allocated to the project.

Answer

Use the '--maximum_billing_tier' flag to increase the billing tier.

Question 7

You have a Cloud SQL for MySQL table that stores user logins with columns: user_id, login_time, ip_address. You frequently run queries to count logins by user for a specific date range. Which index would be most efficient?

Accepted Answer

A composite index on (login_time, user_id). A composite index on (login_time, user_id) is most efficient because queries filter by a date range on login_time and then group or filter by user_id. The index allows the database to quickly locate the range of login_time values and then use the user_id part for covering the query, minimizing I/O.

Answer

No index; rely on full table scan

Answer

Separate indexes on user_id and login_time

Answer

A composite index on (user_id, login_time)

Question 8

You have a BigQuery table with billions of rows partitioned by date and clustered on country. Users frequently query the table to compute total sales by product for a specific month. The product field has high cardinality (millions of distinct values). Which optimization would improve query performance the most?

Accepted Answer

Re-cluster the table with product as the first clustering column. B is correct because clustering on a high-cardinality column like product, especially as the first clustering column, allows BigQuery to prune blocks more effectively during queries that filter or group by product. Since the table is already partitioned by date, clustering on product reduces the amount of data scanned when computing total sales by product for a specific month, directly addressing the query pattern.

Answer

Use a wildcard table pattern to query across date partitions

Answer

Partition by product

Answer

Keep the current clustering on country

Question 9

You have a Cloud Spanner table 'Orders' with columns: OrderId, CustomerId, OrderDate, Status. You need to support a query that finds all orders for a customer in the last 30 days, sorted by OrderDate descending, with strong consistency. Using only indexes, what is the best approach?

Accepted Answer

Create a secondary index on (CustomerId, OrderDate DESC) with INCLUDE (OrderId, Status). It creates a covering index with the exact sort order needed (DESC on OrderDate) and includes all required columns (OrderId, Status) to avoid back-to-table lookups. This ensures strong consistency in Cloud Spanner by using a single index scan without needing to read the base table, while the composite key on (CustomerId, OrderDate) efficiently filters by customer and date range.

Answer

Create a secondary index on (OrderDate) only

Answer

Create a secondary index on (CustomerId, OrderDate)

Answer

Use a manual table scan with filter

Question 10

Which three of the following are valid considerations when designing secondary indexes in Cloud Spanner? (Choose three.)

Accepted Answer

Secondary indexes maintain strong consistency with the base table. Cloud Spanner secondary indexes are fully synchronous with the base table, meaning they are updated atomically in the same transaction as the table write. This ensures that reads using the secondary index always return strongly consistent data, without any eventual consistency window.

Answer

Secondary indexes are automatically used for queries that filter on primary key columns

Answer

Secondary indexes require a unique constraint

Question 11

A company uses Firestore to power a live sports score app. Scores are updated frequently, and many clients listen to real-time updates on specific games. Which two design decisions will minimize the number of reads and reduce costs? (Choose two.)

Accepted Answer

Store an aggregate score summary document per game and listen to it. Storing an aggregate score summary document per game and listening to it reduces reads by consolidating frequently updated fields into a single document, minimizing the number of document reads per update. Option C is correct because using a separate document per game with listeners filtered by game ID ensures each client only listens to the specific game they care about, avoiding unnecessary reads from irrelevant documents.

Answer

Use a collection group query to listen to all games at once

Answer

Use a single document for all games with nested fields

Answer

Use a subcollection of periods (quarters) to spread writes

Question 12

Refer to the exhibit. You are reviewing a Firestore security rules file. What is the main security flaw in the database schema design that these rules expose?

Accepted Answer

Users can set the visibility field, allowing them to make messages public. The Firestore security rules allow any authenticated user to set the `visibility` field on a message document. This means a user could change the visibility to 'public', making private messages accessible to all users regardless of the intended audience. The rules do not validate that the user setting the visibility is the sender or an admin, exposing a data access control flaw.

Answer

The rules do not protect against brute force attacks

Answer

The senderId field is not indexed

Answer

The delete rule allows admin to delete any message

Question 13

A Firestore application stores user profiles that must be queried by any of multiple attributes (age, city, last_login). What is the best schema design to support these queries efficiently?

Accepted Answer

Create a composite index on the attributes in a single collection. Firestore requires composite indexes to efficiently query documents across multiple fields (age, city, last_login) in a single collection. Without a composite index, Firestore would need to perform a full collection scan or merge results from separate index scans, which is inefficient and can lead to high latency or query failures. Creating a composite index on the three attributes allows Firestore to use a single index scan to satisfy queries filtering on any combination of these fields.

Answer

Store attributes in an array field and query with array-contains

Answer

Use subcollections per attribute value

Answer

Create separate documents for each attribute value

Question 14

In Cloud Spanner, a table 'Orders' has a primary key (OrderId INT64) and is frequently updated. The application often queries for orders placed in the last hour. To reduce read latency, you decide to add a column to store the commit timestamp. Which approach should you use?

Accepted Answer

Define the column with the `allow_commit_timestamp` option and set it to 'true'. Cloud Spanner's `allow_commit_timestamp` option, when set to 'true' on a column of type `TIMESTAMP`, automatically populates that column with the exact commit timestamp of the transaction. This enables efficient time-based queries (e.g., orders placed in the last hour) without requiring application-managed timestamps or additional writes, reducing read latency by leveraging Spanner's built-in commit-time visibility.

Answer

Create an interleaved table with the timestamp

Answer

Use a generated column with expression to get current_timestamp

Answer

Add a secondary index on a user-managed timestamp column

Question 15

A Cloud Spanner database has a parent table 'Customers' and a child table 'Orders' interleaved on CustomerId. The most common query retrieves the last 10 orders for a given customer. How should the primary key of Orders be defined for optimal performance?

Accepted Answer

(CustomerId, OrderDate DESC). Defining the primary key as (CustomerId, OrderDate DESC) leverages Cloud Spanner's interleaved table structure to physically co-locate rows for the same customer, and the descending order on OrderDate allows the most common query (retrieving the last 10 orders) to be served as a sequential scan of the most recent rows without sorting or a full table scan.

Answer

(CustomerId, OrderId)

Answer

Add a commit timestamp column as part of the primary key

Answer

No change; use a secondary index on OrderDate