PCDE Practice Exam 67 — 20 Questions

Question 1

A retail company uses Cloud Bigtable to store user session data. The row key is currently a reversed timestamp (e.g., Long.MAX_VALUE - timestamp) to allow efficient retrieval of the most recent sessions. The company now needs to query sessions by user_id and also retrieve the most recent session for each user. The table is expected to grow to tens of terabytes. They want to optimize for both write throughput and read performance for these queries. What schema design change should they make?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Change the row key to user_id followed by a reversed timestamp, so that all sessions for a user are stored contiguously and sorted by most recent first.. A composite row key of user_id followed by a reversed timestamp groups each user's sessions together and orders them most recent first. This enables efficient range scans for a user's sessions and a single seek for the latest session. It also distributes writes across many users, avoiding hotspotting. This is the recommended pattern for time-series data per entity in Cloud Bigtable.

Answer

Use user_id as the row key and store sessions as multiple versions in a column family, relying on cell versioning for timestamps.

Answer

Keep the reversed timestamp as the row key and add a column family for user_id, then use filters to query by user_id.

Answer

Create a separate table for each user, with row keys as timestamps, to isolate user data.

Question 2

A financial analytics team uses BigQuery to store transactional data in a table `transactions` with columns `transaction_id`, `customer_id`, `amount`, and `timestamp`. The table is partitioned by `DATE(timestamp)` and clustered by `customer_id`. Analysts frequently run queries that filter on `customer_id` and a specific date range. They report that some queries are slow and scan more data than expected. Which action should the database engineer take to improve query performance and reduce cost?

Accepted Answer

Ensure queries include a filter on the partitioning column and use the clustering column in the WHERE clause or JOIN conditions.. BigQuery's cost and performance depend on how much data a query scans. Partitioning by date allows the query engine to skip partitions that don't match the filter, and clustering by customer_id sorts data within each partition so that filters on that column can skip blocks. If analysts write queries without a date filter or with expressions that prevent pruning, the table is fully scanned. Educating them to use the partitioning column in filters and the clustering column in conditions maximizes efficiency.

Answer

Add a secondary index on `customer_id` using BigQuery's `CREATE INDEX` statement.

Answer

Convert the table to a materialized view that pre-aggregates data by `customer_id` and date.

Answer

Change the partitioning column to `customer_id` and cluster by `DATE(timestamp)`.

Question 3

A company is migrating a large on-premises MySQL database to Cloud SQL for MySQL using Database Migration Service (DMS). They want to perform a one-time migration with minimal administrative effort. The source database is MySQL 5.7 and uses InnoDB. They have already created the source and destination connection profiles. What is the simplest way to perform the migration?

Accepted Answer

Create a migration job with 'One-time' migration type and start it.. For a one-time migration with minimal effort, DMS one-time job is ideal. It automates the dump and load process without requiring ongoing replication setup. After connection profiles are created, creating a one-time migration job and starting it performs the migration. This requires less administrative effort than manual methods or continuous replication, which are better suited for different use cases.

Answer

Configure Cloud SQL to replicate from the on-premises MySQL using native replication, then promote the replica.

Answer

Create a migration job with 'Continuous' migration type and start it.

Answer

Use mysqldump to export the database, then import it into Cloud SQL using the console.

Question 4

A startup is migrating a small 50 GB MySQL 8.0 database from an on-premises server to Cloud SQL for MySQL. They can afford a few hours of downtime and want the simplest migration method with minimal configuration. Which approach should they use?

Accepted Answer

Use mysqldump to export the database and import it into Cloud SQL.. mysqldump is the simplest and most appropriate method for migrating a small MySQL database when some downtime is acceptable. It captures the entire database schema and data in a single logical backup that can be easily imported into Cloud SQL. DMS and binary log replication are designed for minimal downtime but require more setup and are unnecessary here.

Answer

Use Database Migration Service (DMS) for MySQL with continuous replication.

Answer

Set up MySQL binary log replication from on-premises to Cloud SQL.

Answer

Use the Cloud SQL import feature with a CSV file exported from the source database.

Question 5

A Cloud Spanner instance with regional configuration us-central1 is serving a read-heavy workload. The application issues many small, single-row reads via the Spanner API. You observe high CPU utilization and increased read latency. Query plans show that these reads are using a secondary index. What should you do to improve performance?

Accepted Answer

Create a covering index that includes all columns needed by the query.. A covering index allows the query to be satisfied entirely from the index, eliminating the extra base-table lookup that occurs when the index does not include all selected columns. This reduces both latency and CPU for single-row reads. Increasing nodes, forcing a table scan, or using Data Boost do not fix the inefficient index access pattern.

Answer

Enable Data Boost for the Spanner instance.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Change the query to use a full table scan instead of the index.

Question 6

A financial services firm has an on-premises Oracle database that must be migrated to Google Cloud. The application requires a relational database with strong ACID guarantees and automatic, synchronous replication across regions for disaster recovery. The team wants to minimize operational overhead and avoid managing replication manually. Which Google Cloud database technology should they choose?

Accepted Answer

Cloud Spanner with multi-region configuration. Cloud Spanner is the only Google Cloud database that natively provides synchronous, strongly consistent replication across regions while offering relational semantics and ACID transactions. Its multi-region configuration automatically handles replication and failover, reducing operational burden. The other options either use asynchronous replication or lack relational ACID guarantees, making them unsuitable for this financial application with strict disaster recovery needs.

Answer

Cloud Bigtable with multi-cluster routing

Answer

Cloud SQL for MySQL with binary log replication to a cross-region instance

Answer

Cloud SQL for PostgreSQL with cross-region read replicas

Question 7

A retail company runs AlloyDB for PostgreSQL for its order service. The service experiences read-heavy spikes during flash sales, and the team wants to scale reads horizontally while keeping a single writer and minimizing replication lag to the read endpoints. Which AlloyDB capability should the database engineer use?

Accepted Answer

Add a read pool of secondary instances in the same region and direct read traffic to them.. AlloyDB separates the single writer from read scaling through read pools. Secondary instances in a read pool replicate the primary within the same region, so read traffic can be distributed across them with low lag while the primary remains the sole writer. This is the intended architecture for read-heavy spikes such as flash sales.

Answer

Create multiple secondary instances in different regions and route all reads to them.

Answer

Enable columnar engine on the primary and serve all reads from it.

Answer

Convert the primary instance into a multi-writer cluster with three writers.

Question 8

A media company stores user-generated video metadata in Cloud Spanner. The table uses a monotonically increasing integer as the primary key, and writes arrive at a sustained rate of 50,000 rows per second. Performance degrades as the table grows, with hot spots on a single split. The team needs to eliminate the hot spot without changing the application's write pattern. What should they do?

Accepted Answer

Hash the primary key or prepend a randomly distributed value to the key.. Cloud Spanner divides data into splits by key range. A monotonically increasing key sends every new insert to the same trailing split, so one server absorbs the write burst while others idle. Hashing the key or prefixing a random value scatters inserts across the key space, letting many splits and servers share the load and removing the hot spot.

Answer

Create a secondary index on the primary key column to improve write throughput.

Answer

Enable optimistic concurrency control and retry failed writes with exponential backoff.

Answer

Increase the number of nodes in the instance to distribute the write load.

Question 9

A startup is designing a schema for a new Cloud SQL for PostgreSQL database. They need to store user information and ensure that each user has a unique email address. They also want to enforce that the email is always provided. Which schema constraint should they apply to the `email` column?

Accepted Answer

NOT NULL and UNIQUE. The email column should have both NOT NULL and UNIQUE constraints. NOT NULL ensures the email is always provided, and UNIQUE prevents duplicate emails. This combination is the correct way to enforce the business rules without conflicting with the primary key, which might be a surrogate key like `user_id`.

Answer

PRIMARY KEY

Answer

CHECK (email IS NOT NULL)

Answer

FOREIGN KEY

Question 10

A startup is migrating a small 50 GB MySQL 8.0 database from a single on-premises server to Cloud SQL for MySQL. They want to use Database Migration Service (DMS) for a one-time migration with minimal configuration and no continuous replication. Which source connection profile configuration should they use?

Accepted Answer

Configure the source connection profile with the host IP, port 3306, a user with SELECT, RELOAD, and LOCK TABLES privileges, and no binary logging requirement.. For a one-time MySQL migration with DMS, the source user requires SELECT, RELOAD, and LOCK TABLES privileges to read data and obtain a consistent snapshot. Binary logging and replication privileges are only needed for continuous replication. Granting ALL PRIVILEGES or omitting RELOAD and LOCK TABLES would either violate least privilege or cause the migration to fail.

Answer

Configure the source connection profile with the host IP, port 3306, a user with ALL PRIVILEGES, and enable GTID mode on the source.

Answer

Configure the source connection profile with the host IP, port 3306, a user with SELECT only, and rely on DMS to handle table locking automatically without additional privileges.

Answer

Configure the source connection profile with the host IP, port 3306, a user with REPLICATION SLAVE and REPLICATION CLIENT privileges, and binary logging enabled.

Question 11

A Cloud Spanner instance is experiencing high write latency. The workload consists of many small transactions that insert rows into a table with a monotonically increasing primary key. You want to reduce write latency. What should you do?

Accepted Answer

Change the primary key to a UUID or a hash of the original key.. Monotonically increasing primary keys create hotspots because Spanner splits data by key range. All new inserts target the last split, causing high write latency. By hashing the key or using a UUID, writes are distributed across multiple splits, leveraging the parallel write capacity of the instance and reducing latency.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Add a secondary index on the primary key column.

Answer

Enable optimistic concurrency control for the transactions.

Question 12

A financial institution uses Bigtable for storing real-time stock trades. They need to query trades by stock symbol and time range, and also by trade ID for individual trade lookups. The row key currently is a concatenation of stock symbol and reverse timestamp. They notice that queries by trade ID are slow because they require a full table scan. What should they do to improve performance for trade ID lookups without affecting the existing access pattern?

Accepted Answer

Maintain a separate table with trade ID as the row key and stock symbol + timestamp as the column qualifier.. Bigtable does not support secondary indexes, so to enable efficient lookups by trade ID, you must create a separate table with trade ID as the row key. This denormalization duplicates data but provides fast point reads for trade ID while preserving the original table's optimized access pattern for symbol and time range queries.

Answer

Create a secondary index on trade ID using Bigtable's built-in indexing feature.

Answer

Enable Bigtable's row key filter to scan by trade ID in the existing table.

Answer

Use a composite row key that includes both stock symbol and trade ID, and query using a prefix.

Question 13

A startup runs a small Cloud SQL for MySQL instance for its staging environment. The instance is idle most of the day, and the team wants to reduce cost while keeping the instance available for occasional tests. They do not want to lose data or reconfigure connection strings. Which action should they take?

Accepted Answer

Stop the instance and start it when needed.. Stopping a Cloud SQL instance halts compute billing while retaining data, configuration, and the IP address, so the team can start it again for tests without reconfiguring applications. Deleting and restoring changes the IP and introduces risk, shrinking disks is unsupported, and converting to a read replica does not fit a standalone staging instance.

Answer

Reduce the instance's disk size to lower storage costs.

Answer

Convert the instance to a read replica and promote it when tests are needed.

Answer

Delete the instance and restore it from a backup when tests are needed.

Question 14

A retail company runs Cloud SQL for MySQL in a single zone. The operations team requires automatic failover to a standby in a different zone within the same region if the primary zone becomes unavailable. What should you configure?

Accepted Answer

Enable high availability by configuring a regional instance with a primary and standby in separate zones.. Cloud SQL high availability creates a regional instance with a primary and a standby in separate zones, using synchronous replication so the standby is current. On a zone failure, the instance fails over automatically and the connection endpoint is updated. Read replicas, network changes, and backup restores are manual or lossy and do not provide the required automatic, low-data-loss failover.

Answer

Set the instance to use a shared VPC and add a second subnet in another zone.

Answer

Create a read replica in another zone and promote it manually during an outage.

Answer

Schedule automated backups more frequently and restore the instance in another zone after failure.

Question 15

A startup is deploying a new web application on Google Cloud. They need a fully managed relational database for their MySQL-compatible application. The database will store user profiles and session data, with moderate traffic and a single region. They want minimal operational overhead and automatic backups. Which Google Cloud service should they use?

Accepted Answer

Cloud SQL for MySQL. Cloud SQL for MySQL is a fully managed service that supports MySQL, making it the best fit for a MySQL-compatible application with moderate traffic. It provides automated backups, replication, and patching, minimizing operational overhead. Other options are either non-relational, lack MySQL compatibility, or are overkill for the stated scale and region.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 16

A startup is deploying a new application on Google Cloud. They need a fully managed, relational database that supports MySQL and requires automatic backups, high availability, and the ability to scale read traffic. They want to minimize operational overhead and have predictable pricing. Which Google Cloud service should they choose?

Accepted Answer

Cloud SQL for MySQL. Cloud SQL for MySQL is a fully managed service that supports MySQL, provides automated backups, high availability via regional instances, and read replicas for scaling reads. It minimizes operational overhead and offers predictable pricing based on instance size. Other options like Cloud Spanner, Bigtable, and Firestore are not MySQL-compatible and would require application changes or are not relational.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 17

You are optimizing a Cloud SQL for MySQL instance that supports an online transaction processing workload. The instance has 16 vCPUs and 64 GB of RAM. During peak hours, you observe high CPU utilization and many queries waiting on the InnoDB buffer pool. You want to reduce the number of disk reads caused by buffer pool misses. Which action should you take?

Accepted Answer

Increase the innodb_buffer_pool_size to a larger value within the available memory.. Buffer pool misses force InnoDB to read pages from disk, which increases latency and CPU wait. The buffer pool size determines how much of the working set can be cached. Increasing it within available memory reduces disk reads. The query cache is deprecated and does not help non-repeating queries, more vCPUs do not add memory, and changing log flush behavior affects writes rather than read caching.

Answer

Enable the query cache to cache the results of frequent SELECT statements.

Answer

Set the innodb_flush_log_at_trx_commit parameter to 2 to reduce disk I/O.

Answer

Increase the number of vCPUs on the instance to handle more concurrent queries.

Question 18

A Cloud Bigtable cluster is used to store IoT sensor readings keyed by device ID with a timestamp suffix. The table uses a row key of the form deviceID#timestamp, and the workload is a mix of writes from devices and reads of the most recent reading for a given device. The database engineer observes that CPU utilization is high and that reads are slow, even though the cluster has plenty of nodes. What is the most likely cause?

Accepted Answer

The row key design causes hotspotting because device IDs are sequential.. Bigtable distributes rows across tablets by lexicographic row key order. Sequential device IDs cause consecutive rows to concentrate on a few tablets, so a small number of nodes handle most of the traffic while others sit idle. This produces high CPU and slow reads even with many nodes. Salting or hashing the row key prefix spreads the load.

Answer

The table has too many column families, which increases read amplification.

Answer

The cluster's replication configuration is set to a single cluster.

Answer

The cluster is using SSD storage instead of HDD storage.

Question 19

A company is planning to deploy a Cloud SQL for MySQL instance that will handle a high-traffic web application. They need to ensure high availability and the ability to scale read operations. Which two configurations should they implement? (Choose two.)

Accepted Answer

Enable high availability by creating a regional instance with a standby replica in a different zone.. High availability with a regional instance and standby replica ensures automatic failover during zone outages, while read replicas distribute read traffic to improve performance. Together, these configurations provide both uptime and scalability for a high-traffic web application. Other options address security, backup, or maintenance but do not fulfill the core requirements.

Answer

Configure the instance with a public IP address and restrict access using authorized networks.

Answer

Set the instance's maintenance window to occur during off-peak hours.

Answer

Enable point-in-time recovery (PITR) to allow restoration to any moment within the retention period.

Question 20

A retail company runs Cloud SQL for PostgreSQL for its order management system. The orders table contains a JSONB column called shipping_address that stores address components as nested JSON, including a street field. The application needs to frequently filter orders by the street name. To optimize read performance, what should you do?

Accepted Answer

Create a B-tree index on the expression (shipping_address->>'street').. Indexing the exact expression used in the WHERE clause, such as (shipping_address->>'street'), creates a B-tree index that PostgreSQL can use for equality and range filters on that nested field. This is the most direct and efficient optimization for frequent lookups by street name, requiring no application changes and minimal schema impact.

Answer

Convert the JSONB column to a separate relational table with a foreign key.

Answer

Add a CHECK constraint to validate that shipping_address contains a street key.

Answer

Create a GIN index on the JSONB column using jsonb_path_ops.