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

Question 1

A company uses Cloud SQL for SQL Server. They want to store JSON data in a column and query it efficiently. What should they do?

Accepted Answer

Store JSON in an nvarchar(max) column and use JSON_VALUE in queries.. Cloud SQL for SQL Server supports JSON functions like JSON_VALUE to extract and query data from JSON stored in nvarchar(max) columns. This allows efficient querying without schema changes, leveraging SQL Server's built-in JSON support. Option B is correct because it uses the recommended data type and function for JSON storage and querying in SQL Server.

Answer

Store each JSON field as a separate column.

Answer

Use a TEXT column with no indexing.

Answer

Store JSON as a binary column and parse in application.

Question 2

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.. The row key format [metric_id]#[timestamp] causes all rows for the same metric_id to share the same lexicographic prefix. Cloud Bigtable stores rows in sorted order by row key, so all rows for a given metric are co-located in a single tablet. When scanning a time range for that metric, all read requests hit the same tablet, creating a hotspot that limits throughput to the capacity of a single node.

Answer

Too many column families in the schema.

Answer

The number of nodes is insufficient.

Answer

Replication factor is set too low.

Question 3

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.. Using a hash prefix in the primary key distributes write operations uniformly across multiple splits (tablets). Cloud Spanner splits data based on key ranges; without a hash prefix, sequential writes (e.g., monotonically increasing keys) concentrate on a single split, causing hot spots and write contention. A hash prefix ensures that each new row lands on a different split, balancing the write load.

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 4

Which THREE considerations are important when designing a schema for Cloud Firestore to ensure scalability?

Accepted Answer

Design collections to avoid high read/write rates on a single document.. Cloud Firestore scales by distributing data across multiple documents. A single document with high read/write rates creates a hotspot, leading to contention and degraded performance. Designing collections to spread load evenly avoids this bottleneck and ensures linear scalability.

Answer

Nest subcollections up to 10 levels deep to model complex hierarchies.

Answer

Use collection group indexes for all queries to avoid manual index creation.

Question 5

Which TWO data types are supported in Cloud Spanner schemas?

Accepted Answer

ARRAY. ARRAY is a supported data type in Cloud Spanner, allowing you to store ordered lists of elements of the same primitive type (e.g., ARRAY, ARRAY). This is essential for modeling one-to-many relationships without requiring separate tables, and it integrates seamlessly with Cloud Spanner's query engine for array operations.

Answer

GEOMETRY

Answer

TEXT

Answer

TINYINT

Question 6

Refer to the exhibit. A developer creates these tables and notices that queries joining Users and Orders on UserId are slow. What is the most likely cause?

Accepted Answer

Tables are not interleaved, so parent and child rows may be in different splits.. In Google Cloud Spanner, interleaving tables physically co-locates parent and child rows in the same tablet split, reducing cross-node lookups. Without interleaving, rows from Users and Orders may reside on different servers, causing distributed queries that are slower due to network round trips. Option C correctly identifies this as the most likely cause of slow joins.

Answer

The primary key of Orders should include UserId as a prefix for co-location.

Answer

The foreign key constraint is missing, causing full table scans.

Answer

The foreign key reference should be on the parent table.

Question 7

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 8

Your company runs an e-commerce platform on Google Cloud. The platform uses Cloud SQL for MySQL to store product inventory. The inventory table has the following schema: CREATE TABLE inventory (product_id INT PRIMARY KEY, quantity INT, last_updated TIMESTAMP) ENGINE=InnoDB. The application performs frequent updates on quantity for a subset of popular products. Recently, you have noticed increased deadlock errors during peak hours. The application uses REPEATABLE READ isolation level. You suspect that the schema design is contributing to locking contention. After analyzing the workload, you find that the updates often involve incrementing or decrementing quantity by small amounts and are mostly on the same set of popular products. What would be the best course of action to reduce deadlocks without compromising data integrity?

Accepted Answer

Rewrite the update query to use atomic operations (e.g., UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?) without pre-fetching the current value.. The application's pattern of reading the current quantity before updating (e.g., SELECT quantity FROM inventory WHERE product_id = ?, then UPDATE inventory SET quantity = ? WHERE product_id = ?) causes gap locks and deadlocks under REPEATABLE READ. By using an atomic UPDATE (UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?), the database performs the update without a prior read, reducing lock contention. This maintains data integrity because the subtraction is atomic and accurate. Option B is wrong because MyISAM does not support transactions or row-level locking, which compromises data integrity. Option C is wrong because partitioning does not reduce locking contention on the same rows; it only improves data management. Option D is wrong because reducing isolation to READ COMMITTED may reduce some locking but introduces non-repeatable reads and does not address the fundamental read-before-write pattern that causes deadlocks.

Answer

Change the engine to MyISAM to avoid row-level locking.

Answer

Partition the inventory table by product_id range to spread the load.

Answer

Reduce the isolation level to READ COMMITTED to reduce locking.

Question 9

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?

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 10

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 storing relevant user data (e.g., username, avatar) directly in the posts table reduces the need for JOINs, significantly improving read performance. Option A (Migrate to NoSQL like Firestore) is a major architectural change that may not be worth the effort. Option B (Create a materialized view) could help but may introduce staleness and overhead. Option C (GIN indexes) are for full-text search, not join performance.

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 11

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 12

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 13

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 14

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 15

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.. Options B, D, and E are correct. Interleaved tables (B) model parent-child relationships and optimize joins and data locality. Secondary indexes (D) are essential for efficient queries on non-key columns. Cloud Spanner enforces foreign key constraints (E) to maintain referential integrity. Option A is incorrect because primary key columns cannot be NULL. Option C is incorrect because composite primary keys are commonly used in Spanner.

Answer

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

Answer

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