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

Question 1

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). Option D is correct: creating a secondary index on (CustomerId, OrderDate DESC) with INCLUDE (OrderId, Status) allows the query to be served entirely from the index without accessing the base table, minimizing latency. Option A is good but missing INCLUDE forces access to the base table. Option B doesn't filter by customer. Option C is not an index-based solution.

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 2

You are designing a schema for a Cloud SQL for PostgreSQL database that supports full-text search across millions of product descriptions. The application requires fast search results ranked by relevance. Which schema design is most appropriate?

Accepted Answer

Use a tsvector column with a GIN index on that column. Option A is correct: use a tsvector column with a GIN index, which is PostgreSQL's built-in full-text search feature optimized for ranking and relevance. Option B uses LIKE with wildcards, which is slow and cannot rank. Option C relies on an external service, not a schema design within Cloud SQL. Option D uses trigram indexes, which support similarity search but not full-text search ranking.

Answer

Use a separate Elasticsearch instance

Answer

Use a LIKE '%term%' query with a B-tree index

Answer

Use materialized view with trigram indexes

Question 3

Which two of the following are best practices when designing BigQuery schemas? (Choose two.)

Accepted Answer

Use denormalization to reduce the number of joins. Options A and C are correct best practices: using repeated fields to avoid joins (common for nested data) and using denormalization to reduce joins. Option B (column-level security) is a security feature, not a schema design best practice. Option D is incorrect because BigQuery encourages logical data models, not a single table. Option E using RECORD is a way to implement nested structures but is not a general best practice by itself; repeated fields are more specific.

Answer

Use column-level security to restrict access

Answer

Use the type RECORD for structured data

Answer

Use a single table for all data to simplify queries

Question 4

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. Options A, D, and E are correct. A is true: secondary indexes can be created on interleaved tables (but note they are not interleaved themselves). D is true: secondary indexes in Spanner are strongly consistent with the base table. E is true: secondary indexes can be created on child tables without including the parent key, though they are not interleaved. B is false because secondary indexes are not automatically used for primary key filtering; the primary index is used. C is false because secondary indexes do not require a unique constraint; they can be non-unique.

Answer

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

Answer

Secondary indexes require a unique constraint

Question 5

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. Options B and C are correct. B: using a separate document per game and having clients listen only to the game they're interested in minimizes reads because each client only reads one document. C: storing an aggregate score summary document per game reduces the number of document updates and reads because changes are batched into a single document write, and listeners read that one document. Option A (collection group query) would listen to many documents, increasing reads. Option D (subcollection of periods) increases read complexity. Option E (single document for all games) would cause document contention and all clients reading the same large document.

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 6

Refer to the exhibit. You are reviewing the following Cloud Spanner DDL statement for a table storing customer orders. What potential performance issue will arise with this schema?

Accepted Answer

The OrderId is likely to be sequentially generated, causing write hotspots. Option B is correct: the primary key starts with OrderId, which is likely to be auto-generated and monotonically increasing. In Cloud Spanner, inserting rows with sequential primary keys causes write hotspots because all writes go to a single split, leading to performance degradation. Option A is incorrect because composite primary keys are fine. Option C is irrelevant. Option D is not a performance issue.

Answer

The primary key includes two columns which reduces insert performance

Answer

The TotalAmount column should be INTEGER for performance

Answer

The table lacks a foreign key constraint

Question 7

Refer to the exhibit. You receive the following query output showing bytes processed for a BigQuery query. The table is partitioned by date and clustered on country. What is the most likely reason for the high bytes processed?

Accepted Answer

The query does not filter on the clustering column, causing full scan of selected partitions. Option B is correct: the query does not filter on the clustering column (country), so BigQuery must scan all rows in the selected partitions. Clustering only reduces data scanned when there is a filter on the clustering key or when the query aggregates after filtering on it. Option A is incorrect because partitioning is working. Option C is incorrect because 31 days is a small range. Option D is incorrect because the GROUP BY does not cause full scan; the issue is lack of clustering filter.

Answer

The GROUP BY country requires sorting all rows

Answer

The table is not partitioned correctly

Answer

The date range is too wide

Question 8

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. Option B is correct: the schema includes a 'visibility' field that users can set when creating documents. Since the create rule only checks that the senderId matches the authenticated user, users can set visibility to 'public' for any message they create, potentially exposing private messages. Option A is not a security flaw. Option C is not a flaw. Option D is vague and not specifically about the schema.

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 9

A company is designing a schema for time-series sensor data in Cloud Spanner. They need to efficiently query the latest reading for each sensor. Which schema design is most appropriate?

Accepted Answer

Create a Sensors table and an interleaved Readings table with primary key (SensorId, Timestamp DESC). Option A is correct because interleaving the Readings table under Sensors allows efficient parent-child joins and retrieval of the latest reading per sensor using the primary key ordering. Option B (single wide table) leads to large rows and poor performance. Option C (Cloud SQL) is not optimized for time-series at scale. Option D (Bigtable) is better for time-series but the question specifies Spanner.

Answer

Use a single table with columns for each sensor and wide rows

Answer

Use Cloud SQL with a normalized schema

Answer

Use Cloud Bigtable with row keys (SensorId#Timestamp)

Question 10

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. Option A is correct: a composite index on the attributes allows Firestore to serve queries without collection scans. Option B (denormalized arrays) is inefficient for filtering. Option C (subcollections) adds complexity and may require more reads. Option D (separate documents per attribute) is not practical.

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 11

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'. Option D is correct: Spanner allows defining a column with commit timestamp option, which automatically records the commit time of the last mutation. This eliminates the need to read the column separately. Option A (interleaved tables) does not provide the timestamp. Option B (secondary index) adds write overhead. Option C (generated column) cannot be automatically populated with commit time.

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 12

A data warehouse in BigQuery stores event logs with nested and repeated fields (e.g., page views within a session). Which schema type is optimal for storing this data?

Accepted Answer

Use ARRAY> columns for nested repeated data. Option D is correct: ARRAY> allows storing nested repeated data natively in BigQuery, enabling efficient querying without joins. Option A (separate tables) requires costly joins. Option B (JSON strings) loses schema enforcement and performance. Option C (RECORD type) is a legacy term; the current best practice is arrays of structs.

Answer

Use RECORD type columns for each nested level

Answer

Normalize into separate tables and join

Answer

Store as JSON strings and parse at query time

Question 13

A Cloud Spanner application experiences high write latency on a table with a monotonically increasing primary key. Which schema change will most effectively reduce latency?

Accepted Answer

Modify the primary key to include a hash of the original key as a leading column. Option B is correct: adding a hash prefix to the primary key spreads writes across nodes, eliminating hotspotting. Option A is the current problem. Options C and D do not directly address the underlying hotspotting issue.

Answer

Convert the table to an interleaved table

Answer

Add a secondary index on the existing key

Answer

Increase the number of nodes in the instance

Question 14

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). Option B is correct: Using CustomerId (parent key) and OrderDate DESC ensures that the most recent orders are stored first within each interleaved row range, making queries for last N orders efficient. Option A (OrderId) is monotonically increasing but not sorted by date. Option C (secondary index) adds overhead. Option D (commit timestamp) is not a primary key.

Answer

(CustomerId, OrderId)

Answer

Add a commit timestamp column as part of the primary key

Answer

No change; use a secondary index on OrderDate

Question 15

A BigQuery table stores daily sales data. The team commonly queries data for a specific date range. Which schema optimization will reduce query cost and improve performance?

Accepted Answer

Partition the table by date column. Option A is correct: partitioning by date (e.g., ingestion-time or column) allows BigQuery to prune partitions and scan only relevant data. Option B (clustering) helps but partitioning is more impactful for range pruning. Option C (view) does not reduce storage. Option D (materialized view) is precomputed but still requires scanning partitions.

Answer

Create a view over the table

Answer

Create a materialized view with a filter on date

Answer

Cluster the table by date column