PCDE Practice Exam 66 — 20 Questions

Question 1

A retail company runs its transactional workload on Cloud SQL for PostgreSQL and its product catalog on Firestore in Native mode. The catalog is frequently updated with new products and price changes. The company needs a near real-time, queryable view in BigQuery that joins order data from Cloud SQL with catalog data from Firestore for analytics. They want minimal operational overhead and no custom code. What should they do?

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

Accepted Answer

Create a Datastream stream from Cloud SQL to BigQuery and enable the Firestore to BigQuery extension, which writes catalog changes to BigQuery in near real time.. Datastream captures changes from Cloud SQL for PostgreSQL and streams them into BigQuery, while the Firestore to BigQuery extension streams document changes into BigQuery tables. Both are managed services that require little operational effort and no custom code, providing near real-time data for joins. This combination meets the latency and simplicity requirements better than batch exports or self-managed pipelines.

Answer

Configure the Cloud SQL federated query connector in BigQuery and use the Firestore BigQuery export to load catalog data, then schedule a recurring query to join the two datasets.

Answer

Export Firestore data to Cloud Storage nightly and load it into BigQuery, and use BigQuery federated queries against Cloud SQL for order data.

Answer

Use Cloud Data Fusion to build a pipeline that reads from Cloud SQL and Firestore and writes to BigQuery, deploying the pipeline on a schedule every five minutes.

Question 2

A company runs a Cloud SQL for MySQL instance with a read replica in a different region. They want to promote the read replica to a standalone primary instance to perform a regional failover. What should they do?

Accepted Answer

Use the gcloud sql instances promote-replica command on the read replica.. To promote a read replica to a standalone primary in Cloud SQL, the gcloud sql instances promote-replica command is used. This operation makes the replica writable and stops replication from the original primary, allowing it to serve as the new primary. Other commands like failover are for HA standby, and cloning or recreating does not promote the replica, so they are incorrect.

Answer

Use the gcloud sql instances clone command to create a new instance from the read replica.

Answer

Delete the primary instance and recreate it from the read replica's backup.

Answer

Use the gcloud sql instances failover command on the primary instance.

Question 3

A healthcare company runs AlloyDB for PostgreSQL and must deploy a configuration that keeps reads serving during a zonal failure while also offloading reporting queries from the primary instance. Which two components should they deploy? (Choose two.)

Accepted Answer

An AlloyDB high availability configuration with a standby instance in a second zone of the same region.. An AlloyDB high availability configuration places a standby in another zone so the primary survives a zonal failure, and a read pool of read replicas serves reporting traffic while keeping reads available. Together they satisfy both the availability and the offload requirements within the same region. Other services and cross-service replication do not fit the scenario.

Answer

A Cloud SQL for PostgreSQL replica that connects to the AlloyDB primary as a cascading replica.

Answer

A secondary AlloyDB cluster in a different region configured for cross-region replication.

Answer

A Cloud Bigtable cluster in the same region to store the reporting data and serve analytics queries.

Question 4

A financial services company runs a mission-critical Cloud SQL for PostgreSQL instance in a single zone. The database team must guarantee that the instance can automatically recover from a zone-level outage with no manual intervention and minimal data loss. The instance currently has no high availability configured. Which action should the team take?

Accepted Answer

Enable high availability on the instance by configuring a regional instance with a standby in a different zone.. High availability for Cloud SQL for PostgreSQL is achieved by configuring the instance as regional, which creates a synchronous standby in a different zone. Failover is automatic and data loss is minimal because replication is synchronous. Read replicas are asynchronous and require manual promotion, backups are for recovery not failover, and the Auth Proxy is only a connectivity tool.

Answer

Schedule automated backups with point-in-time recovery enabled and restore to a new instance during an outage.

Answer

Configure a Cloud SQL Auth Proxy with multiple connections to the instance to reroute traffic during an outage.

Answer

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

Question 5

A Cloud Spanner instance is used for a global gaming application. The database has a table named 'PlayerScores' with a monotonically increasing primary key 'ScoreID'. The application performs many inserts and updates. During peak hours, you observe high write latency and increased CPU utilization on the Spanner instance. You suspect that hotspotting is occurring. Which action should you take to mitigate the hotspotting?

Accepted Answer

Use a UUID or hash the primary key to distribute writes across the key space.. Hotspotting in Cloud Spanner occurs when writes are concentrated on a narrow key range, often due to monotonically increasing keys. Distributing the key space by using UUIDs or hash values spreads writes across multiple splits, allowing Spanner to scale horizontally and reduce latency. Increasing nodes, adding indexes, or interleaving do not fix the underlying key distribution problem.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Use interleaved tables to store scores for each player separately.

Answer

Enable a secondary index on the 'ScoreID' column.

Question 6

You are monitoring a Cloud SQL for MySQL instance that supports an online transaction processing application. Users report intermittent slowness, and you want to identify the root cause using available observability data. (Choose two.)

Accepted Answer

Review the instance's CPU utilization, memory, and disk I/O metrics in Cloud Monitoring.. Query Insights provides per-digest latency and frequency data that pinpoints which statements contribute to load, while Cloud Monitoring resource metrics reveal whether CPU, memory, or disk I/O is saturated. Together they let you correlate user-reported slowness with a specific resource or query pattern. Capturing all queries, resizing the buffer pool, or restarting the instance do not diagnose the problem.

Answer

Enable the slow query log and set long_query_time to 0 to capture every statement.

Answer

Restart the instance during off-peak hours to clear any accumulated locks.

Answer

Increase the innodb_buffer_pool_size to the maximum value supported by the instance.

Question 7

A financial services firm is migrating an on-premises Microsoft SQL Server 2019 database to Cloud SQL for SQL Server. The database is 1.5 TB, and the team wants a managed, low-downtime path with continuous replication and built-in validation. Which approach should they use?

Accepted Answer

Use Database Migration Service for SQL Server to perform a full backup plus transaction log shipping for continuous replication, then promote at cutover.. Database Migration Service for SQL Server is purpose-built for this migration, combining an initial full backup with continuous transaction log shipping so the destination stays current until a short promotion window. It also provides validation of the migrated data, matching the managed, low-downtime goal.

Answer

Configure transactional replication from the on-premises SQL Server to a Cloud SQL for SQL Server subscriber over a VPN.

Answer

Use SQL Server Always On availability groups with the Cloud SQL instance as an asynchronous secondary replica.

Answer

Back up the database to a BAK file, upload it to Cloud Storage, and restore it to Cloud SQL for SQL Server using the import feature.

Question 8

A retail company is designing a BigQuery schema for sales data. They need to store transaction records that include a nested array of line items. They want to query individual line items efficiently and also retrieve the full transaction. Which BigQuery schema design should they use?

Accepted Answer

Use a nested schema with a repeated RECORD field for line items within each transaction row.. BigQuery's nested and repeated RECORD fields allow storing a transaction with its line items in a single row, preserving relationships without joins. Queries can access line items using UNNEST or dot notation, and transaction-level fields are stored once. This design minimizes storage, maximizes query performance, and aligns with BigQuery's columnar architecture.

Answer

Use a JSON string column to store the line items as a serialized array.

Answer

Use separate tables for transactions and line items, and join them in queries.

Answer

Use a flat schema with one row per line item, repeating transaction-level fields for each line item.

Question 9

A retail company runs Cloud Spanner for its order management system. The `Orders` table has a primary key `OrderId` that is a monotonically increasing integer generated by the application. During peak shopping periods, the team observes that all writes are concentrated on a single Spanner server, causing high latency. They want to redesign the schema to distribute writes evenly while preserving fast lookups by `OrderId`. Which schema change should they implement?

Accepted Answer

Use a bit-reversed or hashed `OrderId` as the primary key and store the original `OrderId` as a non-key column.. A monotonically increasing primary key in Cloud Spanner creates a write hotspot because new rows are always inserted at the end of the key space, landing on the same split. Hashing or bit-reversing the key spreads inserts across many splits, eliminating the hotspot. Storing the original OrderId as a non-key column preserves the ability to retrieve orders by their natural identifier, and a secondary index can be added if point lookups by OrderId are required.

Answer

Add a secondary index on `CustomerId` and query orders by customer instead of by `OrderId`.

Answer

Enable `optimize_for_write` on the `Orders` table using an ALTER TABLE statement.

Answer

Change the primary key to `(OrderId, CustomerId)` and interleave `OrderItems` under `Orders`.

Question 10

A media company stores user-generated images in a Cloud Storage bucket and metadata in a Cloud SQL for MySQL instance. They need to run analytical queries that join the metadata with external data in BigQuery without moving the Cloud SQL data. Which approach should they use?

Accepted Answer

Create a federated query in BigQuery that connects to Cloud SQL using the Cloud SQL connector.. BigQuery federated queries allow querying Cloud SQL for MySQL directly using the Cloud SQL connector, so the metadata remains in Cloud SQL while being joined with BigQuery data. This avoids data movement and duplication, satisfying the requirement for live analytical queries.

Answer

Export the Cloud SQL data to a CSV file in Cloud Storage and load it into BigQuery.

Answer

Use Cloud SQL's built-in BigQuery export feature to schedule daily exports.

Answer

Set up a Datastream pipeline from Cloud SQL to BigQuery and query the replicated tables.

Question 11

A company is using Cloud Bigtable for a time-series application that stores sensor readings. The row key is currently structured as sensor_id#timestamp, where sensor_id is a string and timestamp is in reverse order (e.g., sensor_id#9999999999). They need to efficiently retrieve the latest reading for a specific sensor. What should they do to optimize the row key design?

Accepted Answer

Use a row key of sensor_id#reverse_timestamp, where reverse_timestamp is a fixed-width, zero-padded value representing the inverted timestamp.. For time-series data in Bigtable, storing the timestamp in reverse order (e.g., using Long.MAX_VALUE - timestamp) as part of the row key ensures that the most recent readings sort first. This allows efficient retrieval of the latest reading for a sensor using a simple prefix scan. Other options either scatter data, rely on unsupported features, or do not change the sort order.

Answer

Use a row key of sensor_id#timestamp and create a secondary index on timestamp.

Answer

Use a row key of sensor_id#timestamp and enable a column family with a TTL to keep only the latest reading.

Answer

Use a row key of timestamp#sensor_id, so that all readings are sorted by time globally.

Question 12

A company is designing a highly available architecture using Cloud SQL for MySQL. They need to ensure that the database remains available during a zonal failure and that read-heavy workloads can be offloaded from the primary instance. They also want to minimize data loss during failover. Which two configurations should they implement? (Choose two.)

Accepted Answer

Configure a high-availability (HA) configuration with a standby instance in a different zone.. High availability with a standby in a different zone ensures automatic failover and minimal data loss during zonal failures. Read replicas allow read-heavy workloads to be distributed, reducing load on the primary. Together, these configurations provide both availability and read scalability. Other options like PITR and cross-region replicas address different concerns and do not meet the specific needs.

Answer

Use Cloud SQL Auth Proxy to encrypt connections and improve security.

Answer

Set up a cross-region replica and use it as a hot standby for failover.

Answer

Enable binary logging and configure point-in-time recovery (PITR).

Question 13

A startup is developing a mobile app that requires a fully managed, serverless NoSQL database with automatic scaling and strong consistency for user profiles. They expect unpredictable traffic and want to minimize operational overhead. Which Google Cloud database should they choose?

Accepted Answer

Firestore in Native mode. Firestore in Native mode is a serverless NoSQL database that automatically scales and provides strong consistency for document reads. It is designed for mobile and web applications with unpredictable traffic, offering minimal operational overhead. Cloud SQL and Spanner require provisioning, and Bigtable is not serverless and is optimized for different workloads.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 14

A retail company runs an order-processing system on Cloud Spanner. The `Orders` table has a primary key `OrderId` (a UUID string). Query performance for fetching recent orders by `CustomerId` is poor because each query scans many rows. You need to improve read performance for queries that filter by `CustomerId` and sort by `OrderDate` descending. What should you do?

Accepted Answer

Create a secondary index on `CustomerId` and `OrderDate` (descending) and store `OrderId` as a stored column.. A secondary index on `CustomerId` and `OrderDate` directly supports the query pattern by allowing Cloud Spanner to seek to the relevant customer and read orders in the desired sort order. Storing `OrderId` in the index makes it covering, so the query can be satisfied entirely from the index, avoiding extra lookups to the base table and improving performance.

Answer

Add a foreign key constraint between `CustomerId` and a `Customers` table.

Answer

Change the primary key to `CustomerId` and make `OrderId` a non-key column.

Answer

Partition the `Orders` table by `OrderDate` using table partitioning.

Question 15

A healthcare company is designing a Cloud Spanner schema for a patient records system. The primary table 'Patients' has a primary key 'PatientId' (STRING). A related table 'Visits' records each visit with a unique 'VisitId' (STRING) and must be queried together with patient data. The system requires strong consistency and low latency for queries that retrieve a patient and all their visits. Additionally, the schema must support efficient updates to patient demographic information. Which two design choices should you make? (Choose two.)

Accepted Answer

Define the 'Visits' table with a composite primary key of 'PatientId' and 'VisitId'.. Interleaving the 'Visits' table in 'Patients' with 'PatientId' as the prefix co-locates visits with their patient, enabling low-latency, strongly consistent retrieval of a patient and all their visits in a single operation. To interleave, the child table must have a composite primary key that includes the parent's key, so defining 'Visits' with 'PatientId' and 'VisitId' is required. This design also keeps patient demographics in a separate row, allowing efficient updates without affecting visits. Secondary indexes or denormalization would not achieve the same performance and consistency benefits.

Answer

Denormalize patient demographic information into the 'Visits' table to avoid joins.

Answer

Use a foreign key constraint from 'Visits' to 'Patients' on 'PatientId' to enforce referential integrity.

Answer

Create a secondary index on 'Visits' for 'PatientId' to speed up queries that retrieve visits by patient.

Question 16

An online gaming company runs a player-session service on Cloud Bigtable and a leaderboard service on Cloud Spanner. They require a single application code path that can read both databases without maintaining separate client libraries per database. Which approach should they use?

Accepted Answer

Use the Google Cloud client libraries for each database and abstract them behind a shared data-access layer in the application.. Cloud Bigtable and Cloud Spanner are distinct managed services with their own client libraries and protocols. A shared data-access layer in the application lets each database be accessed with its native library while exposing one internal interface to the rest of the code. This satisfies the single-code-path requirement without relying on protocol translation that neither service supports.

Answer

Deploy a federated query layer using BigQuery external tables over both services.

Answer

Use the Cloud Bigtable HBase client for both databases by pointing it at Spanner endpoints.

Answer

Enable the Cloud Spanner PostgreSQL interface and connect Bigtable through the same PostgreSQL driver.

Question 17

A retail company is designing a Firestore database for a product catalog. Each product document contains fields such as 'name', 'category', 'price', and 'inventoryCount'. The application needs to support queries that filter products by category and sort by price. The team wants to minimize the number of composite indexes required. What should they do?

Accepted Answer

Create a composite index on 'category' (ascending) and 'price' (ascending).. Firestore requires composite indexes for queries that combine a filter on one field with a sort on another. By creating a composite index on 'category' and 'price', the query filtering by category and sorting by price can be executed efficiently. This single index satisfies the query pattern, avoiding the need for multiple indexes or denormalization.

Answer

Use a collection group query to query across all product collections.

Answer

Create separate single-field indexes on 'category' and 'price'.

Answer

Denormalize the data by storing products under category subcollections and query each subcollection separately.

Question 18

An IoT company ingests telemetry into Cloud Bigtable and needs to design the row key so that writes spread evenly across all tablet servers while still supporting efficient range scans by device. Which row key design should they use?

Accepted Answer

Use a salted prefix followed by the device identifier and a timestamp, so writes spread across key ranges while per-device scans remain range-addressable.. Bigtable sorts rows lexicographically, so a leading salt or hash spreads writes across tablet servers and avoids hotspots. Keeping the device identifier and timestamp after the salt preserves range-scan efficiency for per-device queries. This combination is the standard approach for high-volume time-series ingestion.

Answer

Use a reversed timestamp followed by the device identifier, so the newest data is always at the top of the table.

Answer

Use a hash of the device identifier as the row key, discarding the device identifier entirely so writes are perfectly random.

Answer

Use the device identifier as the row key with a timestamp suffix, so all rows for a device are contiguous and scans are sequential.

Question 19

A logistics company uses Cloud SQL for MySQL to track shipments and needs a disaster recovery strategy. The primary instance is in us-central1, and the business requires recovery in a different region with a recovery point objective of under one minute and a recovery time objective of under five minutes. The team wants the lowest operational overhead. Which approach should they implement?

Accepted Answer

Create a cross-region read replica in us-east1 and promote it if the primary region fails.. A cross-region read replica provides continuous asynchronous replication to a different region, keeping data loss under a minute, and promotion converts it to a writable primary in minutes. Because Cloud SQL manages replication and the promotion workflow, this meets both objectives with the least operational effort.

Answer

Schedule automated backups with point-in-time recovery enabled and restore to a new instance in us-east1 during a disaster.

Answer

Enable high availability on the primary instance so that a standby in a second zone takes over automatically.

Answer

Deploy a second standalone Cloud SQL instance in us-east1 and use mysqldump-based scripts to sync data on a schedule.

Question 20

A media company uses Cloud Firestore in Native mode for a recommendations service. Each user document contains a `followedTopics` array of topic IDs, and the service must efficiently query all users who follow a given topic. The array can grow to several hundred entries per user. Which schema design should the database engineer use?

Accepted Answer

Create a top-level `follows` collection where each document has `userId` and `topicId` fields, and query by `topicId`.. Firestore is a document store without joins, so many-to-many relationships are best modeled with a junction collection. Storing one document per user-topic follow with indexed `userId` and `topicId` fields allows efficient equality queries in either direction. This avoids the array index and document size limits that would constrain a large embedded array and scales as follows accumulate.

Answer

Keep the array in the user document and query with an `array-contains` filter on `followedTopics`.

Answer

Create a subcollection under each user document containing one document per followed topic.

Answer

Store a denormalized `followerCount` and a `topFollowers` array on each topic document and query that document.