PCDE Practice Test 25 — 15 Questions

Question 1

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.. Using nested and repeated fields (Option D) to store all snapshots per customer in a single row drastically reduces data scanned when querying by customer. Option A (clustering) still scans full partitions if querying across dates. Options B and C (separate tables/wildcards) still require scanning multiple partitions.

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 2

A startup is using Cloud Spanner for a global user base. They need to design a schema that minimizes interleaved table joins for common access patterns. Which schema design principle should they prioritize?

Accepted Answer

Use composite primary keys to colocate related data.. Interleaved tables in Spanner allow colocation of parent-child rows, reducing cross-node joins. Option A uses composite primary keys to colocate related data, which is the core principle of interleaving. Option B (normalization) increases joins. Option C (secondary indexes) helps but is not as fundamental. Option D (separate databases) increases complexity.

Answer

Normalize all tables to reduce data redundancy.

Answer

Store data in separate databases per region.

Answer

Use secondary indexes on all foreign key columns.

Question 3

A company is migrating an on-premises MySQL database to Cloud SQL for MySQL. The current schema uses InnoDB with foreign keys. What is a key consideration for maintaining referential integrity in Cloud SQL?

Accepted Answer

Use Cloud SQL's built-in foreign key enforcement which is identical to on-premises.. Cloud SQL for MySQL fully supports InnoDB and foreign keys, identical to on-premises. Option D is correct because the same foreign key enforcement applies. Option A is false. Option B is about a migration flag, not ongoing integrity. Option C is unnecessary.

Answer

Enable the foreign_key_checks flag during migration.

Answer

Convert foreign keys to application-level checks.

Answer

Foreign keys are not supported in Cloud SQL MySQL.

Question 4

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 encourages denormalization for one-to-few relationships. Storing addresses as a nested map within the user document (Option A) is efficient for small, fixed sets of addresses. Option B (separate collection) is for large or dynamic lists. Option C is not possible in Firestore. Option D (string array) loses structure.

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 5

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., ingestion time or event date) is standard for time-series. Clustering by sensor_id helps queries that filter on specific sensors. Option C (partition by date, cluster by sensor_id) is best. Option A uses ingestion time, which may not align with event time. Option B partitions by sensor_id, creating many partitions. Option D (integer range) is not suitable for dates.

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 6

A company uses Cloud Spanner with a schema that has a table 'Orders' with primary key (CustomerId, OrderDate, OrderId). They notice hotspots on a specific customer. Which schema change would best distribute load?

Accepted Answer

Add a hash of CustomerId as a prefix to the primary key.. Hotspots occur due to monotonically increasing or high-traffic keys. Adding a hash prefix to the primary key (Option A) distributes writes across nodes. Option B (OrderId only) loses ordering and may cause hotspots elsewhere. Option C (secondary index) helps reads but not writes. Option D (split by region) is overly complex.

Answer

Use a secondary index on CustomerId.

Answer

Split the table into multiple tables per region.

Answer

Change primary key to OrderId only.

Question 7

An e-commerce platform uses Cloud SQL for PostgreSQL. They need to run complex reporting queries that join several tables. These queries are slowing down the transactional workload. What should they do?

Accepted Answer

Use read replicas to offload reporting queries.. Read replicas can offload read-only reporting queries, protecting the primary's performance. Option B is correct. Option A (materialized views) still run on primary. Option C (subqueries) may not reduce load. Option D (scaling up) is more expensive and doesn't isolate workloads.

Answer

Create materialized views for common reports.

Answer

Change all joins to use subqueries.

Answer

Increase the number of vCPUs on the primary instance.

Question 8

A financial services company uses Cloud Spanner with a database that has multiple tables with interleaved relationships. They need to enforce a strict consistency requirement across two related tables that are not interleaved. Which method ensures global strong consistency?

Accepted Answer

Use Spanner's built-in atomicity by executing the updates in a single read-write transaction.. Spanner provides global ACID transactions across all tables within a database. Option B (single read-write transaction) is the correct approach. Option A (client-side) is unnecessary and less reliable. Option C (commit timestamps) does not provide atomicity. Option D (Pub/Sub) is eventual.

Answer

Use Cloud Pub/Sub to eventually synchronize the tables.

Answer

Use a commit timestamp-based approach to synchronize writes.

Answer

Use a client-side distributed transaction across the two tables.

Question 9

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 (Option A) is scalable and allows efficient queries with a composite index on (conversationId, timestamp desc). Option B (single collection) lacks natural grouping and may hit limits. Option C (subcollection without conversationId index) cannot filter by conversation efficiently. Option D (nested array) is limited to 1 MiB per document.

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 10

A team is designing a Cloud SQL for PostgreSQL schema for a multi-tenant SaaS application. They need to isolate tenant data while maintaining query performance and manageability. Which two approaches are appropriate? (Choose two.)

Accepted Answer

Use a single schema with a tenant_id column on every table and row-level security.. Separate schemas per tenant (B) provides logical isolation and easy backup/restore. Single schema with tenant_id and row-level security (C) is a standard multi-tenancy pattern. Options A and D are too costly. Option E offers no isolation.

Answer

Use separate databases per tenant.

Answer

Use a single table for all tenants with no tenant identifier.

Answer

Use a separate Cloud SQL instance per tenant.

Question 11

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 12

A Cloud SQL for PostgreSQL database experiences lock contention during heavy concurrent writes on a single table. Which schema design change can most effectively reduce contention?

Accepted Answer

Partition the table by a key that spreads write load. Option C is correct: table partitioning splits data into smaller physical pieces, reducing lock conflicts because writes target different partitions. Option A (read replicas) does not reduce write contention. Option B (connection pooling) improves connection management but not locking. Option D (materialized views) does not affect write locking.

Answer

Deploy read replicas to offload reads

Answer

Use a connection pooler like PgBouncer

Answer

Create materialized views for read queries

Question 13

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.. Option B is correct because using reversed timestamp as part of the row key helps distribute writes and allows efficient range scans over recent data. Option A is wrong because timestamp first can cause hotspotting. Option C is wrong because sequential player IDs cause hotspotting on a single tablet. Option D is wrong because hashing alone makes range scans impossible.

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 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.. Option C is correct because without interleaving, parent and child rows may be stored on different splits, causing distributed joins. Option A is wrong because there is a foreign key. Option B is wrong because the primary key is on OrderId, not a composite key. Option D is wrong because the foreign key is defined correctly.

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

A startup is using Firestore in Native mode for a real-time chat application. They want to design the schema for chat rooms and messages. Which TWO design patterns are recommended? (Choose two.)

Accepted Answer

Use a composite index on chat room ID and timestamp.. Options A and E are correct. Storing messages in a subcollection under each chat room (A) is scalable and follows Firestore best practices. A composite index on chat room ID and timestamp (E) is needed for efficient queries. Option B (single collection) is less scalable; Option C (separate collection per chat room) leads to many collections, which is not recommended; Option D (arrays) has size limits and is not scalable for many messages.

Answer

Use arrays in the chat room document to store message IDs.

Answer

Store all messages in a single top-level collection with a field for chat room ID.

Answer

Use a separate top-level collection for each chat room.