PCDE Practice Exam 65 — 20 Questions

Question 1

A healthcare company uses Cloud SQL for PostgreSQL for patient records and Cloud Firestore for a mobile app's session state. They need a dashboard that shows patient counts by region alongside active session counts, updated every few minutes. They want to minimize custom code and operational effort. What should they do?

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

Accepted Answer

Use Datastream to replicate Cloud SQL to BigQuery and the Firestore to BigQuery extension to stream session changes, then build the dashboard in Looker Studio.. Datastream handles Cloud SQL to BigQuery replication with managed CDC, and the Firestore to BigQuery extension streams session changes into BigQuery. Both are low-code, managed services that fit the requirement to minimize custom code and operations. Looker Studio can then present the combined metrics with frequent refresh. Other options require custom API code, pipeline authoring, or scheduled exports that add overhead.

Answer

Set up Cloud SQL federated queries in BigQuery for patient records and use the Firestore REST API to pull session counts into a custom application.

Answer

Use Cloud Data Fusion to create a pipeline that reads from Cloud SQL and Firestore and writes to BigQuery every few minutes.

Answer

Export Firestore sessions to Cloud Storage every few minutes and load into BigQuery, and use BigQuery federated queries for Cloud SQL.

Question 2

A financial institution is designing a Cloud Spanner schema for a transaction ledger. The primary access pattern is to retrieve all transactions for a given account within a date range, and they need to optimize for both read and write performance. The table will have columns: account_id (string), transaction_date (date), transaction_id (string), amount (numeric), and details (string). Which schema design should they use?

Accepted Answer

Primary key: (account_id, transaction_date, transaction_id). No interleaving.. The primary key (account_id, transaction_date, transaction_id) ensures that all transactions for an account are stored together and sorted by date, enabling efficient range scans. This design avoids hotspots because account_id is not monotonically increasing. Interleaving is unnecessary because the access pattern is fully served by the primary key, and interleaving could introduce additional constraints without performance gains.

Answer

Primary key: (transaction_id, account_id, transaction_date). No interleaving.

Answer

Primary key: (transaction_date, account_id, transaction_id). No interleaving.

Answer

Primary key: (account_id, transaction_date, transaction_id). Interleave the table in a parent Accounts table.

Question 3

A logistics company runs an AlloyDB for PostgreSQL cluster in us-central1 with a primary instance and a read pool. They want to improve availability so that a zonal failure of the primary does not cause a prolonged outage, and they want failover to be as fast as possible with minimal data loss. Which configuration should they implement?

Accepted Answer

Configure the primary instance to use a regional availability configuration with a secondary instance in a different zone of the same region.. AlloyDB's regional availability configuration maintains a secondary instance in a different zone that can be automatically promoted if the primary's zone fails. This delivers zone-level redundancy, fast failover, and minimal data loss, matching the goal of avoiding prolonged outages. Read pool instances, cross-region replicas, and point-in-time recovery serve different purposes and do not provide the same automatic, low-RTO behavior.

Answer

Create a cross-region replica of the AlloyDB cluster and manually promote it when the primary zone fails.

Answer

Add more read pool instances in the same zone as the primary to absorb traffic during a failure.

Answer

Enable point-in-time recovery on the cluster so that the primary can be restored quickly after a zonal failure.

Question 4

A logistics company uses Cloud Bigtable for shipment tracking in a single region and Cloud SQL for PostgreSQL for billing. An analytics team needs to run complex joins between shipment events and billing records, and the results must be queryable within minutes of new events arriving. The company wants to avoid managing servers and wants a single SQL surface for both datasets. Which architecture should the database engineer implement?

Accepted Answer

Stream Cloud Bigtable changes into BigQuery using a Dataflow job and replicate Cloud SQL for PostgreSQL into BigQuery using Datastream, then run joins in BigQuery.. BigQuery is the serverless SQL surface that can join data from both sources. Dataflow can stream Cloud Bigtable rows into BigQuery for low-latency ingestion, and Datastream can replicate Cloud SQL for PostgreSQL changes into BigQuery, so the analytics team gets fresh, joinable data without provisioning or managing servers.

Answer

Create a Cloud SQL federated query that reads Cloud Bigtable through its JDBC driver and joins it with the billing tables in place.

Answer

Export Cloud Bigtable to Cloud Storage nightly and load it into Cloud SQL for PostgreSQL as external tables, then run joins in Cloud SQL.

Answer

Configure Cloud Spanner as a hub, using its PostgreSQL interface with foreign data wrappers to read both Cloud Bigtable and Cloud SQL for PostgreSQL.

Question 5

A logistics company is migrating an on-premises MySQL 5.7 database to Cloud SQL for MySQL 8.0. They want to use Database Migration Service and need the initial load to complete without errors. The source database uses the MyISAM storage engine for several tables. What must the engineer do before starting the DMS migration job?

Accepted Answer

Convert the MyISAM tables to InnoDB on the source before the migration begins.. Database Migration Service for MySQL depends on InnoDB for consistent snapshots and row-based change capture. MyISAM tables cannot provide the transactional guarantees the service needs, so the migration would fail during the initial load. Converting those tables to InnoDB on the source before starting the job is the required remediation.

Answer

Set the binlog_format to STATEMENT on the source so MyISAM changes are captured during CDC.

Answer

Enable the MyISAM storage engine on the destination Cloud SQL instance by setting a database flag.

Answer

Configure the DMS job to use the mysqldump utility with the --skip-lock-tables option for MyISAM tables.

Question 6

A company is migrating a 2 TB Oracle database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). The Oracle database uses numerous PL/SQL stored procedures and triggers. DMS successfully migrates the schema and data, but the stored procedures and triggers are not migrated. What should the team do to migrate these objects?

Accepted Answer

Use the Oracle SQL Developer tool to export the PL/SQL code and manually rewrite it in PL/pgSQL for PostgreSQL.. DMS migrates schema and data but does not convert PL/SQL stored procedures and triggers to PL/pgSQL. The team must manually rewrite these objects, often using Oracle SQL Developer to extract the code and then adapting it to PostgreSQL syntax. This ensures that business logic is correctly implemented in the target database. Automated conversion tools can assist but require careful validation.

Answer

Use the pgloader utility to migrate the stored procedures and triggers from Oracle to PostgreSQL.

Answer

Enable the 'Migrate code objects' setting in the Cloud SQL instance configuration before running DMS.

Answer

Configure DMS to enable the 'Convert stored procedures' option in the migration job settings.

Question 7

A logistics company runs its shipment-tracking platform on Cloud Spanner. The `Shipments` table has a primary key of `ShipmentId` (a monotonically increasing integer generated by the application) and stores a JSON column `TrackingEvents` that is updated continuously as the package moves. The write throughput is high and the operations team reports that all writes are landing on a single split, causing hotspots. Which schema change should the database engineer make to distribute writes?

Accepted Answer

Use a UUID4 string as the first key column instead of the monotonic integer.. Cloud Spanner distributes data by primary key ranges, and a monotonically increasing first key column concentrates all new inserts into the final split, creating a write hotspot. Replacing that key with a uniformly distributed value such as a UUID4 scatters inserts across many splits, allowing Spanner to spread write load. Alternative key prefixes like dates only shift the problem to a coarser granularity and do not resolve concurrent-write concentration.

Answer

Convert the `TrackingEvents` JSON column to a `STRING(MAX)` column and add a secondary index on it.

Answer

Add a `CreatedDate` column as the first key column and keep `ShipmentId` as the second.

Answer

Enable interleaving of a child `TrackingEvents` table under `Shipments` keyed by event timestamp.

Question 8

A healthcare company is designing a Cloud SQL for PostgreSQL database to store patient records. The schema includes a 'patients' table and a 'visits' table. Visits are always associated with a patient, and when a patient is deleted, all their visits must be automatically removed. The team wants to ensure referential integrity and minimize application code. Which schema design should they use?

Accepted Answer

Define a foreign key on the visits table referencing the patients table with ON DELETE CASCADE.. A foreign key with ON DELETE CASCADE ensures that when a patient is deleted, all associated visits are automatically deleted by the database. This enforces referential integrity and eliminates the need for application-level deletion logic. Cloud SQL for PostgreSQL supports this standard SQL feature, making it the simplest and most reliable solution for the given requirement.

Answer

Use a database trigger on the patients table to delete related visits after a patient is deleted.

Answer

Define a foreign key on the visits table referencing the patients table with ON DELETE RESTRICT.

Answer

Store visits as a JSONB column within the patients table to avoid the need for a separate table and foreign key.

Question 9

You are analyzing a Cloud BigQuery dataset that contains billions of rows. A daily report query scans the entire table, resulting in high cost and long execution time. You want to reduce the amount of data processed by the query. Which action should you take?

Accepted Answer

Partition the table by date and filter on the date column.. Partitioning the table by date and filtering on that column allows BigQuery to prune partitions, scanning only the necessary data. This reduces both cost and execution time, directly addressing the full-table scan problem.

Answer

Increase the number of slots in the project.

Answer

Create a clustered table on a high-cardinality column.

Answer

Use a materialized view to precompute the report.

Question 10

A startup runs a small PostgreSQL database for its internal HR tool on a single Compute Engine VM. The database is 20 GB, has low traffic, and the team wants to reduce operational overhead by moving to a managed service with minimal changes. They need automated backups and patching. Which Google Cloud service should they choose?

Accepted Answer

Cloud SQL for PostgreSQL. For a small, low-traffic PostgreSQL database that needs managed backups and patching with minimal application changes, Cloud SQL for PostgreSQL is the appropriate choice. It is compatible with standard PostgreSQL clients and handles operational tasks automatically, while the other services are either non-relational or designed for much larger scale.

Answer

Firestore

Answer

Bigtable

Answer

Cloud Spanner

Question 11

You are the database engineer for a Cloud Spanner instance that serves a global retail application. The instance uses regional configuration us-central1 with 3 nodes. During a flash sale, you observe that CPU utilization on the instance stays above 90% for 15 minutes and P99 read latency climbs from 8 ms to 250 ms. You want to reduce latency quickly while keeping the same schema and query patterns. What should you do?

Accepted Answer

Add more nodes to the instance to increase compute capacity.. Cloud Spanner scales horizontally by adding nodes, and each node provides a fixed amount of CPU and serving capacity. When CPU utilization stays very high and latency rises, the instance is compute-bound, so adding nodes is the direct fix. Changing region topology or adding indexes does not increase the serving capacity that the saturated instance needs.

Answer

Change the instance to a multi-region configuration such as nam3.

Answer

Enable the Spanner query optimizer's latest version and rerun the queries.

Answer

Create a secondary index on the columns used by the hottest read queries.

Question 12

A startup is deploying a new application on Google Cloud and needs a fully managed relational database that supports automatic failover and replication across zones within a region. They want to minimize operational overhead. Which Google Cloud service should they use?

Accepted Answer

Cloud SQL for PostgreSQL with high availability enabled.. Cloud SQL for PostgreSQL with high availability enabled provides a fully managed relational database with automatic failover and cross-zone replication. It requires minimal operational overhead, as Google manages backups, patches, and failover. Other options either are not relational, do not provide automatic failover, or are overkill for a startup.

Answer

Cloud Spanner with a regional instance configuration.

Answer

Cloud Bigtable with a single cluster.

Answer

AlloyDB for PostgreSQL with a read pool.

Question 13

A media analytics company stores time-series event data in Cloud Bigtable. They need to keep the dataset highly available within a single region while tolerating a single-zone failure, and they want the lowest possible write latency for their ingest pipeline. Which configuration should they choose?

Accepted Answer

A multi-cluster routing instance with clusters in two zones of the same region and multi-cluster routing enabled.. Cloud Bigtable achieves zone-level fault tolerance within a region by using a multi-cluster instance with clusters in separate zones and multi-cluster routing. Because replication stays within the region, write latency remains low compared with cross-region configurations. This meets the dual requirement of surviving a zone failure and keeping ingest latency minimal for the time-series pipeline.

Answer

A multi-cluster instance with clusters in two different regions and a replication profile of 100 percent.

Answer

A single-cluster instance with nodes spread across two zones using SSD storage and a replication profile of 0 percent.

Answer

A single-cluster instance with one node in a single zone, relying on Cloud Bigtable's automatic backups.

Question 14

You are managing a Cloud Bigtable instance with a single cluster in us-central1. The instance is used for a real-time analytics application that requires high availability across zones. You need to ensure that the cluster can survive a zonal failure without downtime. What should you do?

Accepted Answer

Enable replication by adding a second cluster in a different zone within the same region.. Adding a second cluster in a different zone within the same region enables Bigtable replication, which provides automatic failover if one zone fails. This ensures high availability without downtime and maintains low latency because the clusters are in the same region. Other options do not provide automatic failover or are designed for disaster recovery rather than zonal high availability.

Answer

Create a backup of the Bigtable instance and restore it to a new instance in a different zone if a failure occurs.

Answer

Configure the existing cluster to use SSD storage instead of HDD for faster failover.

Answer

Use a multi-region instance with clusters in different regions to ensure high availability.

Question 15

A logistics company runs Cloud SQL for PostgreSQL in a single region and wants to design for high availability and disaster recovery. They need automatic failover within the region for zone failures and a way to recover from a full regional outage. Which TWO configurations should they implement? (Choose two.)

Accepted Answer

Create a cross-region read replica and promote it during a regional outage.. High availability provides a synchronous standby in a different zone and automatic failover for zone failures within the region. A cross-region read replica extends protection beyond the region and can be promoted during a regional outage, covering the scenario that in-region HA cannot. Together they address both zone-level automatic failover and regional disaster recovery for the Cloud SQL for PostgreSQL deployment.

Answer

Use Cloud SQL backups stored in a multi-region bucket as the primary failover mechanism.

Answer

Enable point-in-time recovery and rely on it to fail over during a zone outage.

Answer

Configure the application to connect to the standby instance IP directly for reads.

Question 16

A company is planning to deploy a Cloud Spanner instance for a global application that requires strong consistency and high availability. The database engineer must choose a configuration that minimizes latency for users in North America, Europe, and Asia. The application has a read-heavy workload with some writes. Which two factors should the engineer consider when selecting the instance configuration? (Choose two.)

Accepted Answer

The number of nodes or processing units must be sufficient to handle the read and write throughput.. When selecting a Cloud Spanner instance configuration for a global application, the engineer must consider the number of nodes or processing units to handle the workload, and the instance configuration (regional or multi-region) to place replicas close to users. These factors directly impact performance, latency, and availability. The SQL dialect, node count for failover, and secondary indexes do not determine global latency or availability in the same way.

Answer

Enabling automatic failover requires a minimum of three nodes in each region.

Answer

The use of secondary indexes can reduce write latency by distributing writes across multiple replicas.

Answer

The choice of SQL dialect (GoogleSQL or PostgreSQL) affects the replication strategy and consistency guarantees.

Question 17

A financial services firm runs Cloud Spanner for a global ledger. Auditors require that the database can be restored to any point within the last 30 days, and the restore must not affect the running production instance. The team wants the lowest operational effort. What should they do?

Accepted Answer

Enable point-in-time recovery with a 30-day retention period and restore to a new Cloud Spanner instance when needed. Cloud Spanner point-in-time recovery keeps a versioned history of the database for a configurable retention window of up to 30 days and restores to a new instance at any timestamp inside that window. Because the restore target is a separate instance, production remains available, and the entire capability is managed by Spanner, minimizing operational effort.

Answer

Create a second Cloud Spanner instance and configure multi-region replication from production

Answer

Export the database to Cloud Storage every hour and import it into a new instance on demand

Answer

Schedule daily Cloud Spanner backups with a 30-day retention and restore the backup to a new instance

Question 18

A healthcare company is migrating a 10 TB on-premises Apache Cassandra cluster to Cloud Bigtable. The data model uses wide rows with millions of columns per partition key, and the application performs point reads by row key. They need to minimize read latency after migration. Which schema and migration design should the engineer choose?

Accepted Answer

Map each Cassandra row key to a Bigtable row key and each Cassandra column to a Bigtable column qualifier within a single column family, then load with the Dataflow Cassandra to Bigtable template.. Bigtable's wide-column model maps naturally onto Cassandra's partition-and-column layout, so using the Cassandra row key as the Bigtable row key and columns as qualifiers preserves the application's point-read pattern. Keeping all qualifiers in one column family avoids extra lookups, and the Dataflow template provides the parallel bulk load needed for 10 TB.

Answer

Store each Cassandra row as a serialized blob in a single Bigtable column, and decode it in the application after the read.

Answer

Use BigQuery as an intermediate store, export the Cassandra data to Avro, load into BigQuery, and then run scheduled queries to copy into Bigtable.

Answer

Map each Cassandra partition to a Bigtable column family and each Cassandra column to a separate Bigtable row, then query with a row-range scan.

Question 19

A healthcare company stores patient records in Cloud SQL for PostgreSQL. The database is in us-central1 and must be highly available across zones. They also need read replicas in europe-west1 for analytics and a disaster recovery strategy that can survive a regional outage with minimal data loss. They want to minimize operational overhead. What should they do?

Accepted Answer

Enable high availability on the primary instance, create cross-region read replicas for analytics, and promote a replica if the primary region fails.. The correct approach uses Cloud SQL high availability for automatic failover within the region, cross-region read replicas for analytics and disaster recovery, and replica promotion for regional failover. This meets the requirements with minimal operational overhead because it leverages managed features. Other options either lack cross-region DR, provide insufficient RPO, or introduce unnecessary migration complexity.

Answer

Migrate to Cloud Spanner with a multi-region configuration to achieve global strong consistency and automatic failover.

Answer

Set up a Cloud SQL instance with read replicas in multiple zones within the region and use Cloud Storage to export backups for disaster recovery.

Answer

Configure a Cloud SQL instance with a failover replica and use Cloud SQL federated queries to query data from BigQuery for analytics.

Question 20

A media company uses Cloud Bigtable to store user interaction events. The table has a row key that is a combination of a user ID and a timestamp, with the user ID as a prefix. The company needs to retrieve the most recent 100 events for a specific user. The data is stored in a single cluster in us-central1. The performance of these reads is critical and must be optimized. What should they do to ensure the lowest possible latency for these queries?

Accepted Answer

Use a row key that is a combination of user ID and reversed timestamp, and perform a scan with a limit of 100.. In Cloud Bigtable, data is sorted lexicographically by row key. To efficiently retrieve the most recent events for a user, the row key should be designed so that the latest events are stored together and can be read with a limited scan. Using a reversed timestamp as a suffix makes the most recent events appear first for that user, so a scan with a limit of 100 reads only the necessary rows. This minimizes latency and resource consumption.

Answer

Create a secondary index on the user ID and timestamp columns to speed up the lookups.

Answer

Use a row key that starts with the timestamp, followed by the user ID, and use a prefix scan.

Answer

Enable SSD storage on the Bigtable cluster to reduce read latency.