PCDE Practice Test 28 — 15 Questions

Question 1

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.. Option B is correct because Firestore recommends using subcollections for arrays of objects to avoid document size limits and enable efficient querying. Option A (keeping nested arrays) can hit size limits and is not scalable; Option C (maps instead of arrays) still has size issues; Option D (stringified JSON) is not queryable.

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 2

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).. Option A is correct. 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 hotspot. Option B (changing primary key) would affect the base table distribution but not necessarily the index. Option C (separate table) adds complexity and still may have indexing issues. Option D (Bigtable) is a different database.

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 3

You are designing a Firestore database for a chat application. Documents will store messages with fields: senderId, messageText, timestamp, conversationId. To efficiently retrieve the most recent 50 messages in a conversation, which index should you create?

Accepted Answer

A composite index on (conversationId, timestamp, __name__) descending. Option A creates a composite index on (conversationId, timestamp, __name__) with descending order on timestamp, which efficiently supports queries that filter by conversationId and order by timestamp descending, limiting to 50 results. Option B only indexes timestamp, not filtering by conversation. Option C indexes senderId, which is not used in the query. Option D indexes conversationId only, but without timestamp order, it would require sorting in memory.

Answer

A single-field index on timestamp

Answer

An index on conversationId only

Answer

A composite index on (senderId, timestamp)

Question 4

Your team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The current schema uses table inheritance, which is not fully supported in Cloud SQL. What should you do to minimize application changes?

Accepted Answer

Redesign the schema using separate tables with joins. Option B is correct: redesign the schema using separate tables with joins, as this is the standard approach to replace inheritance and can be done with minimal application changes by creating views that emulate the inheritance hierarchy. Option A uses foreign data wrappers, which are not a direct replacement and add complexity. Option C uses materialized views, which don't support write operations. Option D is incorrect because Cloud SQL does not fully support table inheritance.

Answer

Continue using inheritance as Cloud SQL supports it fully

Answer

Use PostgreSQL foreign data wrappers to emulate inheritance

Answer

Use materialized views to combine data

Question 5

When designing a schema for a data warehouse in BigQuery, which table type is most cost-effective for storing raw event data that will be queried by date range filters?

Accepted Answer

A partitioned table partitioned by date column. Option C is correct: a partitioned table partitioned by date limits the data scanned to only the relevant partitions, reducing cost. Option A (regular table) scans all data. Option B (regular table clustered by timestamp) still scans all data. Option D (integer range partitioning on an ID) is not suitable for date queries and would not limit scans based on date.

Answer

A table with integer range partitioning on an ID column

Answer

A regular table with no partitioning

Answer

A regular table clustered on timestamp

Question 6

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 7

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 8

Refer to the exhibit. Which BigQuery SQL query correctly flattens the items into rows?

Accepted Answer

SELECT * FROM orders CROSS JOIN UNNEST(items) AS items. Option A is correct: using CROSS JOIN UNNEST(items) expands the repeated record into separate rows. Option B only filters null items. Option C is invalid syntax. Option D is missing CROSS JOIN.

Answer

SELECT * FROM orders WHERE items IS NOT NULL

Answer

SELECT * FROM orders, UNNEST(items) AS items

Answer

SELECT * FROM orders INNER JOIN items ON true

Question 9

A Cloud Bigtable instance stores time-series data with a row key format: [metric_id]#[timestamp]. The team notices read throughput is low when scanning a metric over a time range. What is the likely cause?

Accepted Answer

All rows for a given metric are stored in a single tablet causing a hotspot.. Option C is correct because bigtable rows are sorted lexicographically; with metric_id first, all rows for a metric are colocated in one tablet, causing hotspotting on reads. Option A is wrong because column families don't affect read distribution. Option B is wrong because replication factor doesn't affect per-tablet throughput. Option D is wrong because node count might be sufficient; the issue is row key design.

Answer

Too many column families in the schema.

Answer

The number of nodes is insufficient.

Answer

Replication factor is set too low.

Question 10

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'.. Option A is correct because by making the row key start with the event type, scans can efficiently filter by event type. Option B is incorrect because Bigtable does not support secondary indexes natively (you can use row key design or column families, but not indexes like relational databases). Option C (adding a column family) does not help with filtering. Option D (using a different database) is an architecture change.

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 11

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 12

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 13

Refer to the exhibit. What is the most likely performance issue with this schema?

Accepted Answer

Hotspotting on TransactionId due to monotonically increasing values. Option A is correct: TransactionId is monotonically increasing (likely auto-generated), causing write hotspotting on the last split. Option B is incorrect because UserId is not the primary key. Option C is incorrect: there are no secondary indexes shown. Option D is incorrect because hotspotting is a likely issue.

Answer

No performance issue; the schema is optimal

Answer

Hotspotting on UserId due to frequent queries

Answer

Too many secondary indexes causing write amplification

Question 14

Which TWO schema design practices help reduce write contention in Cloud Spanner?

Accepted Answer

Use a hash prefix in the primary key to distribute writes across splits.. Options A and D are correct. Option A: Using a monotonically increasing primary key causes hotspotting; instead, use a hash prefix. Option D: Splitting hot rows into multiple rows with different keys spreads writes. Option B is wrong because interleaving can increase contention if parent is hot. Option C is wrong because decreasing splits reduces parallelism. Option E is wrong because timestamp prefix causes hotspotting.

Answer

Use a timestamp prefix in the primary key to sort by time.

Answer

Use interleaved tables to keep related rows together.

Answer

Decrease the number of splits by using a less granular primary key.

Question 15

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.. Option B is correct because using a hash prefix (e.g., a hash of the transaction ID) as the first component of the primary key distributes writes across tablets, while the transaction ID as the second component still allows efficient lookups. Option A (UUID) helps distribution but has larger key size and may fragment reads; Option C (reverse timestamp) can also help but may cause hotspots if timestamps are sequential; Option D (composite with timestamp) still has potential for hotspots.

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.