PCDE Practice Test 3 — 25 Questions

Question 1

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.. Option B is correct because using a single schema with a tenant_id column and row-level security (RLS) in PostgreSQL allows tenant data isolation at the row level while maintaining a single database and schema. RLS policies automatically filter rows based on the current session's tenant context, ensuring performance is optimized through standard indexing on tenant_id and avoiding the overhead of multiple databases or schemas.

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 2

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 D is correct because Cloud Spanner uses a distributed architecture that splits data across splits based on the primary key range. If OrderId is sequentially generated (e.g., auto-increment), all new inserts will target the same split, creating a write hotspot that degrades throughput and latency. This is a well-known anti-pattern in Spanner; the recommended approach is to use a UUID or a monotonically increasing key with a hash prefix to distribute writes evenly.

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 3

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 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 4

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.. Option B is correct because a composite index on chat room ID and timestamp is essential for efficiently querying messages in order within a specific chat room. Firestore requires composite indexes for queries that combine equality filters on one field (chat room ID) with an order on another (timestamp). Option E is correct because storing messages as documents in a subcollection under each chat room document is the recommended pattern for Firestore, as it allows scalable, independent message collections per room without hitting the 1 MiB document size limit.

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.

Question 5

A company uses Cloud Spanner with a schema that includes a table 'Events' with primary key (EventId, Timestamp). They need to run range queries on Timestamp across all events. They notice slow queries. Which two actions can improve query performance? (Choose two.)

Accepted Answer

Create a secondary index on Timestamp.. Option A is correct because creating a secondary index on Timestamp allows Cloud Spanner to efficiently perform range queries on that column without scanning the entire table. Without this index, Spanner must perform a full table scan, which is slow for large datasets. The secondary index provides a sorted structure that directly supports the range scan operation.

Answer

Add a hash prefix to EventId to distribute writes.

Answer

Use interleaving with a parent table on EventId.

Answer

Change the primary key to (Timestamp, EventId).

Question 6

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 D is correct because BigQuery clustering only reduces the bytes scanned when the query filters on the clustering column (country). Without a WHERE clause on country, BigQuery must scan all rows in the selected partitions, even though partition pruning may reduce the date range. The high bytes processed indicates that clustering is not being leveraged, so the query performs a full scan of the chosen partitions.

Answer

The GROUP BY country requires sorting all rows

Answer

The table is not partitioned correctly

Answer

The date range is too wide

Question 7

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 8

A data engineer is designing a BigQuery schema for a dataset that will be used for both ad-hoc analysis and scheduled dashboards. They want to optimize costs and performance. Which three strategies should they consider? (Choose three.)

Accepted Answer

Use partitioning on a date column for time-based queries.. Partitioning on a date column (Option C) is correct because BigQuery uses the date column to prune partitions during query execution, significantly reducing the amount of data scanned and thus lowering costs and improving performance for time-based queries. This is a core optimization for both ad-hoc analysis and scheduled dashboards that frequently filter by date ranges.

Answer

Use wildcard tables with a suffix filter.

Answer

Store data in multiple tables per day.

Question 9

You are designing a BigQuery schema for IoT sensor data. The sensor readings have varying fields depending on the sensor type. You want to minimize storage costs and avoid schema maintenance when new sensor types are added. What is the best schema design?

Accepted Answer

Store the sensor data in a JSON column. Option B is correct because storing sensor data in a JSON column leverages BigQuery's native support for semi-structured data (the `JSON` data type), allowing you to ingest records with varying fields without schema changes. This minimizes storage costs by avoiding the overhead of many NULL columns and eliminates the need for schema maintenance when new sensor types are added, as BigQuery can query JSON fields directly using functions like `JSON_EXTRACT` or dot notation.

Answer

Use a separate table per sensor type

Answer

Use a schema with a STRUCT containing all possible fields as optional

Answer

Use a wide table with many nullable columns

Question 10

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 D is correct because the Firestore security rules allow any authenticated user to set the `visibility` field on a message document. This means a user could change the visibility to 'public', making private messages accessible to all users regardless of the intended audience. The rules do not validate that the user setting the visibility is the sender or an admin, exposing a data access control flaw.

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 11

A financial services company uses Cloud Spanner for transaction processing. They need to run analytical queries that scan large portions of the database without impacting OLTP performance. What schema design technique should they use?

Accepted Answer

Design the primary key so that analytical queries scan a small number of tablets by using interleaved tables.. Option C is correct because interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same tablet (split). This ensures that analytical queries scanning a large portion of the database can be served by a small number of tablets, minimizing cross-tablet reads and reducing contention with OLTP traffic. By designing the primary key to leverage interleaving, you keep analytical scans localized and avoid the performance penalty of scattering reads across many tablets.

Answer

Export data periodically to BigQuery and run queries there.

Answer

Create multiple secondary indexes on frequently scanned columns.

Answer

Use a read replica instance to offload analytical queries.

Question 12

A team executed the above DDL to create interleaved tables in Cloud Spanner. They need to query all orders for a specific customer. Which query will be most efficient?

Accepted Answer

SELECT * FROM Orders WHERE CustomerId = 1234;. Option C is correct because in Cloud Spanner, interleaved tables store child rows physically adjacent to their parent row within the same split. Querying Orders directly on the interleaved key (CustomerId) allows Spanner to perform a local index scan within the parent row's split, avoiding a distributed cross-table join. This leverages the interleaved table's physical clustering for the most efficient retrieval.

Answer

SELECT * FROM Orders WHERE CustomerId = 1234 AND OrderDate = '2023-01-01';

Answer

SELECT * FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId WHERE Customers.CustomerId = 1234;

Answer

SELECT * FROM Orders WHERE OrderId = 5678;

Question 13

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. The database is 2 TB in size and the network bandwidth to Google Cloud is limited to 500 Mbps. The migration window is 48 hours. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Export the database to flat files, compress, upload to Cloud Storage, then import to Cloud SQL.. Option C is correct because the 2 TB database size and 500 Mbps bandwidth yield a theoretical transfer time of approximately 9.5 hours (2 TB * 1024 GB/TB * 8 bits/byte / 500 Mbps / 3600 seconds/hour), which fits within the 48-hour window. However, pg_dump/pg_restore over a VPN (Option A) would be slower due to TCP overhead and latency, and Database Migration Service with continuous replication (Option B) requires ongoing connectivity and may not complete the initial load within the window. Exporting to flat files, compressing them (e.g., with gzip), uploading to Cloud Storage, and then importing to Cloud SQL leverages high-throughput parallel uploads and avoids network latency issues, making it the most reliable strategy for a one-time migration within the given constraints.

Answer

Create a VPN tunnel and use pg_dump/pg_restore over the network.

Answer

Use Database Migration Service with continuous replication.

Answer

Request a dedicated interconnect and then migrate.

Question 14

A company runs a production Cloud SQL for PostgreSQL instance used by a web application. The instance experiences intermittent latency spikes during peak hours. You need to diagnose the cause without downtime. Which tool should you use first?

Accepted Answer

Use Cloud SQL Insights to analyze query performance and identify slow queries.. Cloud SQL Insights provides built-in query performance monitoring and diagnostics without requiring any downtime. It surfaces slow queries, lock contention, and resource bottlenecks directly from the PostgreSQL engine, making it the ideal first step to identify the root cause of intermittent latency spikes during peak hours.

Answer

Use Database Migration Service to failover to a read replica.

Answer

Use gcloud sql instances describe to check instance configuration.

Answer

Use VPC Flow Logs to analyze network traffic.

Question 15

A mobile app backend uses Firestore for user profiles. The schema has a single collection 'users' where each document contains: user_id (used as document ID), name, email, and friends (an array of user IDs). The friends array can grow large (thousands of IDs). When a user adds a friend, the application updates the array, causing the document to grow and leading to write contention and size limit warnings. The team needs to redesign the schema to scale better. What is the best approach?

Accepted Answer

Create a new 'friendships' collection with documents containing user_id_1 and user_id_2 fields.. Option D is correct because it normalizes the friendship relationship into a separate 'friendships' collection, where each document represents a single bidirectional link between two users. This avoids unbounded document growth and write contention on user documents, as adding a friend only requires a small write to a new friendship document rather than updating a potentially large array. Firestore's 1 MiB document size limit and 1 write per second per document limit are no longer risk factors.

Answer

Move the friends list to a subcollection under each user document.

Answer

Migrate user profiles and friendships to Cloud SQL for relational capabilities.

Answer

Limit the maximum size of the friends array to 1000 at the application level.

Question 16

A financial services company uses Cloud Spanner for transaction processing. They need to ensure zero downtime during a schema change that adds a new column with a default value to a large table. Which approach should the Database Engineer take?

Accepted Answer

Use ALTER TABLE to add the column; Spanner handles schema changes online.. Option D is correct because Cloud Spanner supports online schema changes, including adding columns with default values, without requiring a maintenance window or causing downtime. Spanner applies schema updates asynchronously across all nodes while the database remains fully available for reads and writes, making it the ideal approach for zero-downtime requirements.

Answer

Create a new table with the new column, then use a fan-out pattern to write to both tables until the old table is deprecated.

Answer

Use an ALTER TABLE statement during a maintenance window.

Answer

Drop the table and recreate it with the new schema.

Question 17

A Cloud Firestore database stores documents for a mobile app. The app frequently queries for documents where a specific Boolean field is true. The field is not part of the collection group index. What should the developer do to improve query performance?

Accepted Answer

Create a composite index that includes the Boolean field and the query ordering field.. Option B is correct because Cloud Firestore requires a composite index to efficiently query on a Boolean field combined with an ordering field. Without this index, the query would perform a full collection scan, leading to poor performance. Creating a composite index that includes the Boolean field and the query ordering field allows Firestore to use the index to directly locate matching documents, avoiding expensive sequential scans.

Answer

Add a synthetic field that combines the Boolean with a timestamp for range queries.

Answer

Denormalize the Boolean field into separate subcollections.

Answer

Rely on the automatic single-field index already created.

Question 18

A company is setting up access control for a BigQuery dataset using the above IAM policy. An analyst who is a member of the group 'analysts@example.com' also has the user account 'analyst@example.com'. They need to create new tables in the dataset. What will be the outcome?

Accepted Answer

The analyst can create tables because they have dataOwner role on their user account.. The correct answer is C because IAM policies grant permissions based on the union of all roles assigned to the user, regardless of whether they come from group membership or direct user assignment. The analyst has the `dataOwner` role directly on their user account, which includes the `bigquery.tables.create` permission required to create new tables. Group membership with a lower-privilege role (e.g., `dataViewer`) does not override or conflict with the higher-privilege role on the user account.

Answer

The analyst will get an error because of conflicting roles.

Answer

The analyst cannot create tables because the group only has dataViewer.

Answer

The analyst can create tables if they also have jobUser role.

Question 19

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 B is correct because Firestore requires composite indexes to efficiently query documents across multiple fields (age, city, last_login) in a single collection. Without a composite index, Firestore would need to perform a full collection scan or merge results from separate index scans, which is inefficient and can lead to high latency or query failures. Creating a composite index on the three attributes allows Firestore to use a single index scan to satisfy queries filtering on any combination of these fields.

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 20

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. Option A is correct because 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