PCDE Practice Test 46 — 15 Questions

Question 1

A media company uses Cloud Spanner for a global user profile database. They need to ensure that queries for a user's profile by user_id are fast and that the database can scale horizontally. They also want to avoid hotspots during peak loads. The schema currently uses a monotonically increasing user_id as the primary key. What should they do?

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

Accepted Answer

Change the primary key to a UUID or a hash of user_id to distribute writes evenly across splits.. Cloud Spanner performance depends on primary key design. A monotonically increasing key creates a hotspot because all new writes go to the last split. Using a UUID or hash distributes writes across splits, improving scalability. Other options do not address the root cause or rely on non-existent features.

Answer

Enable Cloud Spanner's automatic sharding feature to redistribute data.

Answer

Create a secondary index on user_id and query using that index.

Answer

Add an interleaved table for user profiles and use user_id as the parent key.

Question 2

A healthcare company stores patient records in Cloud Firestore in Native mode. They need to design a data model to support queries that retrieve all patients in a specific city who have a particular diagnosis, sorted by last name. The application also requires strong consistency for these queries. Which data model should they use?

Accepted Answer

Create a collection 'patients' with documents keyed by patient ID, and add fields for city, diagnosis, and lastName. Create a composite index on city, diagnosis, and lastName.. A root collection with fields for city, diagnosis, and lastName, plus a composite index on city, diagnosis, and lastName, directly supports the query. Firestore in Native mode provides strong consistency for all reads, so the application gets consistent results. This model avoids data duplication and scales well with the number of patients.

Answer

Create a collection 'patients' with documents keyed by city, and store an array of patient records within each document.

Answer

Use a root collection 'cities' with documents for each city, and a subcollection 'patients' under each city, and query within a specific city's subcollection.

Answer

Create a collection 'patients' and a subcollection 'diagnoses' under each patient, and query across all subcollections using a collection group query.

Question 3

A healthcare company stores patient records in Cloud Spanner and must also keep a searchable index in Elasticsearch running on Compute Engine. Regulatory rules require that any change committed to Spanner be reflected in Elasticsearch within seconds, and that no change be lost if the indexing service restarts. The team wants minimal custom code. Which mechanism should the database engineer use to capture and forward the changes?

Accepted Answer

Enable Spanner change streams on the tables, then use a Dataflow job with the Spanner change streams connector to publish mutations to a Pub/Sub topic that the indexer consumes.. Spanner change streams provide a durable, ordered log of committed mutations that Dataflow can read with checkpointing and exactly-once delivery. Routing through Pub/Sub adds a buffered, replayable channel so the Elasticsearch indexer can restart without losing events, and the whole pipeline uses managed components rather than custom polling or application-side interception.

Answer

Schedule a Cloud Scheduler job every 10 seconds that runs a query against Spanner using the commit timestamp column and posts new rows to the indexer.

Answer

Configure Spanner to write to a Cloud Storage bucket on every commit using a database-level export, then have the indexer read the exported files.

Answer

Run a Change Data Capture agent on each application server that writes to Spanner, intercepting SQL statements before they are sent and forwarding them to the indexer.

Question 4

A retail platform runs Cloud SQL for PostgreSQL for order processing and Cloud Bigtable for real-time inventory counters. A new service must atomically decrement inventory in Bigtable and insert an order row in Cloud SQL, and the team wants a single programming model that coordinates both operations with retries and compensating actions. Which approach should they use?

Accepted Answer

Use Workflows to orchestrate a Cloud SQL insert and a Bigtable mutation as separate steps with retry and compensation logic.. Because Cloud SQL and Cloud Bigtable cannot share a distributed transaction, the practical pattern is orchestration with compensating actions. Workflows can call each database through connectors or HTTP endpoints, retry transient failures, and run a compensation step if the second write fails, delivering a single declarative programming model without a two-phase commit coordinator.

Answer

Use Database Migration Service to replicate Cloud SQL into Bigtable, then perform both writes against Bigtable only.

Answer

Enable two-phase commit between Cloud SQL and Cloud Bigtable by configuring a distributed transaction coordinator on a Compute Engine instance.

Answer

Write both operations into a Cloud Spanner database and use Spanner's external consistency to cover the inventory counters.

Question 5

A Site Reliability Engineer is tasked with reducing toil in their team. They identify that resetting expired database connections manually is a common task. What is the best way to automate this toil?

Accepted Answer

Use Cloud Scheduler to invoke a Cloud Function that resets connections. Cloud Scheduler is a fully managed cron service that can invoke a Cloud Function on a schedule, making it the ideal serverless pattern for automating a recurring operational task like resetting expired database connections. This eliminates manual toil without requiring any infrastructure to manage, aligning with SRE principles of reducing repetitive work through automation.

Answer

Create a Cloud Workflow that runs every hour

Answer

Train all team members to reset connections manually

Answer

Write a cron job on a Compute Engine instance

Question 6

A retail company runs a 3 TB on-premises PostgreSQL 14 database on a single server. They want to migrate to Cloud SQL for PostgreSQL with less than 15 minutes of downtime. The database has a high volume of write transactions and a continuous stream of updates. They have a 1 Gbps dedicated interconnect between on-premises and Google Cloud. Which migration approach should they use?

Accepted Answer

Use Database Migration Service (DMS) for PostgreSQL with continuous replication.. Database Migration Service (DMS) for PostgreSQL is purpose-built for minimal-downtime migrations to Cloud SQL. It performs an initial snapshot and then continuously replicates changes from the source's WAL, keeping the destination up-to-date. With a high-speed interconnect, replication lag can be minimized, allowing a cutover well within the 15-minute target. Other methods either require downtime or lack managed continuous replication.

Answer

Set up a primary-replica replication between on-premises PostgreSQL and Cloud SQL, then promote the replica.

Answer

Use pg_dump and pg_restore with the --jobs flag to perform a parallel dump and restore.

Answer

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

Question 7

A logistics company runs Cloud SQL for PostgreSQL for shipment records and Cloud Bigtable for live vehicle location pings. They want a single dashboard that shows shipment details alongside the latest vehicle position, with sub-second latency and minimal operational overhead. Which design should they choose?

Accepted Answer

Have the application query Cloud SQL for shipment details and Cloud Bigtable for the latest vehicle position, joining the results in application code.. The dashboard needs fast reads of relational shipment records and the current vehicle position. Querying Cloud SQL for the former and Cloud Bigtable for the latter, then joining in the application, uses each store for what it does best with no replication pipeline and no data duplication. Consolidating into Spanner, streaming to Cloud Storage, or nightly BigQuery exports all add overhead or latency.

Answer

Serve the dashboard from a Dataflow streaming pipeline that joins both sources and writes results to Cloud Storage.

Answer

Export both databases nightly into BigQuery and point the dashboard at a BigQuery view.

Answer

Replicate both Cloud SQL and Cloud Bigtable into Cloud Spanner and serve the dashboard from Spanner.

Question 8

A company is migrating a 5 TB on-premises SQL Server database to Cloud SQL for SQL Server. They want to minimize downtime and ensure data consistency. They plan to use Database Migration Service (DMS) for the migration. The database has a high volume of transactions, and the migration must be completed within a tight maintenance window. Which DMS migration type should they use to achieve minimal downtime?

Accepted Answer

Continuous migration. Continuous migration is designed for minimal downtime by performing an initial full dump followed by continuous change data capture. This keeps the destination synchronized with the source until cutover, allowing the source to remain active. For a high-transaction 5 TB database, continuous migration ensures that only a short final cutover is needed, fitting within a tight maintenance window.

Answer

One-time migration

Answer

Homogeneous migration

Answer

Heterogeneous migration

Question 9

A company is migrating a large on-premises Microsoft SQL Server database to Cloud SQL for SQL Server using Database Migration Service (DMS). The database uses SQL Server Agent jobs for nightly ETL processes. The migration must be continuous with minimal downtime. Which of the following should they do to ensure the SQL Server Agent jobs are migrated correctly?

Accepted Answer

Manually recreate the SQL Server Agent jobs on the Cloud SQL instance after the migration is complete.. DMS migrates user databases but not instance-level objects like SQL Server Agent jobs. These jobs are stored in msdb and must be manually recreated on the target Cloud SQL instance after cutover. This ensures that scheduled ETL processes continue to function. While DMS handles schema and data, it does not cover server-level automation, so manual intervention is required.

Answer

Configure DMS to include the msdb database in the migration scope so that jobs are transferred automatically.

Answer

Use the SQL Server Integration Services (SSIS) to export the jobs and import them into Cloud SQL after migration.

Answer

Enable the 'Migrate instance-level objects' option in the DMS migration job settings.

Question 10

A financial services firm is migrating an on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL using Database Migration Service (DMS) with continuous replication. During the CDC phase, they observe that changes to tables with large object (LOB) columns are not being replicated, while changes to other tables replicate normally. The source uses the default logical decoding plugin. Which action should they take to resolve this?

Accepted Answer

Exclude the LOB tables from CDC and migrate them separately using pg_dump and pg_restore during the cutover window.. PostgreSQL logical decoding does not capture changes to large objects, so DMS continuous replication silently omits LOB changes. The supported workaround is to exclude those tables from the CDC stream and migrate them separately with pg_dump and pg_restore during cutover. Adjusting replication slots, changing output plugins, or installing pglogical does not overcome this fundamental decoding limitation.

Answer

Switch the source logical decoding plugin to wal2json, which supports large object changes, and restart the migration job.

Answer

Increase the max_replication_slots parameter on the source and restart the DMS migration job.

Answer

Enable the pglogical extension on the source and configure DMS to use it as the replication method for LOB tables.

Question 11

A company has a parent-child relationship in Cloud Spanner. They want to minimize cross-table join latency. What should they use?

Accepted Answer

Interleaved tables. Interleaved tables in Cloud Spanner physically co-locate child rows with their parent rows, which minimizes latency for joins between parent and child tables. This is the recommended approach for parent-child relationships to optimize join performance.

Answer

Cloud Functions

Answer

Stored procedures

Answer

Secondary indexes

Question 12

An engineer is migrating an Oracle database to Cloud SQL for PostgreSQL. They use Ora2Pg to convert the schema. One source table uses a column with data type NUMBER(10,2). What is the appropriate PostgreSQL data type after conversion, and which tool should the engineer use to verify the correctness of converted stored procedures?

Accepted Answer

Use NUMERIC(10,2) and pgTAP to write unit tests for stored procedures.. NUMBER(10,2) maps to NUMERIC(10,2) as it is a fixed-point number. Stored procedures converted from PL/SQL to PL/pgSQL should be tested with unit tests, and pgTAP is a popular PostgreSQL testing framework for this purpose. PL/pgSQL is the procedural language, not a testing tool. Ora2Pg handles conversion but does not provide unit tests.

Answer

Use VARCHAR2 and test with pgTAP.

Answer

Use FLOAT and rely on Ora2Pg's built-in validation.

Answer

Use INTEGER for the data type and PL/pgSQL for testing stored procedures.

Question 13

You are managing a Memorystore for Redis instance that is part of a high-traffic e-commerce application. The instance uses the volatile-lru eviction policy and has persistence disabled. You need to improve data durability without losing the ability to evict keys with TTL. You also want to ensure that the instance can automatically recover from a zonal failure. Which TWO actions should you take? (Choose TWO.)

Accepted Answer

Enable RDB persistence by setting the persistence mode.. (Enable RDB persistence) improves data durability by taking periodic snapshots without interfering with the volatile-lru eviction policy, which only evicts keys with TTL. Option D (Standard Tier with replication) provides automatic failover across zones within a region, ensuring recovery from a zonal failure. Option A only reduces eviction frequency but does not address durability or zonal failover. Option C is incorrect because Memorystore does not support native cross-region replicas. Option E uses manual exports, which do not enable automatic recovery and are not equivalent to persistence.

Answer

Increase the maxmemory setting to reduce eviction frequency.

Answer

Configure a cross-region replica to provide failover in another region.

Answer

Set up a Cloud Scheduler job to export the instance to Cloud Storage every hour.

Question 14

A media company is migrating a 10 TB on-premises PostgreSQL 14 database to Cloud SQL for PostgreSQL using Database Migration Service (DMS) with continuous replication. The migration must minimize downtime, and the source database has a high write throughput. Which TWO actions should they take to ensure the CDC phase can keep up and the cutover is as short as possible? (Choose two.)

Accepted Answer

Increase the Cloud SQL destination instance's vCPU and memory before starting the migration to handle the initial load and CDC apply rate.. To keep CDC up with high write throughput and minimize cutover time, the source must have adequate wal_sender and max_replication_slots so the replication slot is not starved, and the destination Cloud SQL instance must be sized to apply changes quickly. Making the source read-only, tweaking dump parallelism, or disabling autovacuum either increases downtime or degrades performance without addressing the core bottlenecks.

Answer

Configure the DMS migration job to use a larger dump parallelism setting to speed up the initial load and reduce CDC catch-up time.

Answer

Disable autovacuum on the destination Cloud SQL instance during the migration to reduce I/O overhead and improve apply throughput.

Answer

Set the source database to read-only mode during the initial dump to prevent writes from accumulating in the WAL.

Question 15

A Cloud Memorystore for Redis instance is running out of memory. The team wants to automatically remove the least recently used keys when memory is full. Which eviction policy should they configure?

Accepted Answer

allkeys-lru. The `allkeys-lru` eviction policy is correct because it applies the LRU (Least Recently Used) algorithm to all keys in the Redis instance, not just those with an expiry set. This ensures that when memory is full, the least recently accessed keys are automatically removed, regardless of whether they have a TTL, which directly meets the requirement to free memory without manual intervention.

Answer

volatile-lru

Answer

volatile-ttl

Answer

noeviction