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

Question 1

Which THREE are considerations when designing a schema for Cloud Firestore?

Accepted Answer

Use subcollections to organize related data. Subcollections in Cloud Firestore allow you to organize related data hierarchically within a document, enabling efficient queries and scalability without hitting the 1 MiB document size limit. This structure is ideal for data like user posts or product reviews, where each subcollection can grow independently.

Answer

Use nested maps for deeply structured data

Answer

Always use transactional writes to ensure consistency

Question 2

Which TWO techniques can help avoid hot spotting in a Cloud Spanner table?

Accepted Answer

Add a hash of the primary key as the first part of the key. Adding a hash of the primary key as the first part of the key distributes writes evenly across Cloud Spanner's split boundaries. This prevents hot spotting, which occurs when monotonically increasing keys cause all new writes to land on the same tablet server. By hashing the key, writes are spread across multiple nodes, avoiding contention.

Answer

Use a monotonically increasing integer as the key

Answer

Use interleaved tables to distribute writes

Answer

Create a secondary index on a high-cardinality column

Question 3

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. `CROSS JOIN UNNEST(items)` is the standard BigQuery syntax to flatten a repeated (array) column into individual rows. The `UNNEST` operator expands each array element into a separate row, and `CROSS JOIN` ensures that all non-array columns from the `orders` table are preserved alongside each element. This is the only option that correctly transforms the nested `items` array into a normalized row-per-item structure.

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 4

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

Accepted Answer

Hotspotting on TransactionId due to monotonically increasing values. The schema uses TransactionId as the partition key with monotonically increasing values (e.g., timestamps or auto-incrementing integers). In a distributed database like Cloud Spanner or Bigtable, this causes all writes to land on a single partition, creating a hotspot that throttles throughput and increases latency. The correct answer is C because this hotspotting is the most likely performance 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 5

A company is designing a Cloud Spanner database for a global user base. They need to support strong consistency and low-latency reads across multiple regions. Which schema design practice is most important?

Accepted Answer

Use interleaved tables to co-locate related rows that are queried together.. Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same split, enabling local joins with strong consistency and low latency across regions. This design minimizes cross-node communication, which is critical for global workloads that require both strong consistency and fast reads.

Answer

Denormalize data into wide tables to reduce the number of joins.

Answer

Use a single table with composite primary key to avoid joins.

Answer

Create secondary indexes on every column to optimize read queries.

Question 6

A Cloud SQL for PostgreSQL instance is used for an OLTP application. The database schema has many foreign key constraints. Which action improves write performance?

Accepted Answer

Create indexes on foreign key columns.. Foreign key constraints require the database to check referential integrity on write operations, which can cause full table scans on the referenced table if the foreign key columns are not indexed. Creating indexes on foreign key columns allows PostgreSQL to quickly verify that a referenced row exists, dramatically reducing the cost of INSERT, UPDATE, and DELETE operations. This is the standard performance optimization for schemas with many foreign keys.

Answer

Drop all foreign key constraints.

Answer

Add more triggers to enforce integrity.

Answer

Increase the instance storage size.

Question 7

A game company uses Cloud Bigtable to store player session data. Access patterns include looking up a player's most recent sessions and scanning sessions by time range. Which row key design is most appropriate?

Accepted Answer

Use a row key of player ID followed by reversed timestamp.. It leverages Cloud Bigtable's sorted row key structure to group all sessions for a given player ID together, while the reversed timestamp ensures that the most recent session appears first within that group. This design enables efficient point lookups for a player's latest sessions and supports range scans over time ranges by using the timestamp portion of the key.

Answer

Use only player ID as row key with column qualifiers for timestamps.

Answer

Prefix with timestamp and append player ID.

Answer

Use a hash of player ID as row key and store timestamps in cell versions.

Question 8

A team is migrating an on-premises MySQL database to Cloud SQL. The current schema usesMyISAM tables. What is the recommended approach?

Accepted Answer

Convert MyISAM tables to InnoDB before migration.. Cloud SQL for MySQL does not support MyISAM tables because MyISAM lacks transaction support, row-level locking, and crash recovery, which are essential for a managed database service. Converting MyISAM tables to InnoDB before migration ensures compatibility, data integrity, and performance. The recommended approach is to alter the table engine to InnoDB prior to export or use a migration tool that handles the conversion.

Answer

Keep the schema as is; Cloud SQL supports MyISAM.

Answer

Replicate the on-premises MySQL to Cloud SQL using Database Migration Service.

Answer

Export the database using mysqldump and import directly into Cloud SQL.

Question 9

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 10

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 11

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 12

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 13

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 14

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 15

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.