PCDE Practice Test 3 — 10 Questions

Question 1

A company is migrating a legacy on-premises MySQL database to Cloud SQL for PostgreSQL. The database uses composite primary keys on multiple tables and heavily relies on cross-table joins with foreign keys. The team wants to minimize application code changes during migration. Which schema design strategy should the Cloud Database Engineer recommend to ensure compatibility and performance?

Accepted Answer

Use the same composite primary keys and foreign key constraints in Cloud SQL for PostgreSQL, leveraging its full support for these features.. Option B is correct because Cloud SQL for PostgreSQL fully supports composite primary keys and foreign key constraints, which are standard SQL features. By maintaining the same schema, the team minimizes application code changes while preserving referential integrity and join performance, as PostgreSQL's query planner handles these constructs efficiently.

Answer

Maintain the same schema and rewrite joins as materialized views in PostgreSQL to optimize queries.

Answer

Migrate to Cloud Spanner instead, using interleaved tables to replace join-heavy operations.

Answer

Remove composite primary keys and replace them with surrogate keys; use look-up tables for foreign key relationships.

Question 2

Match each Cloud Spanner replication type to its purpose.

Question 3

Match each Google Cloud tool to its purpose in database management.

Question 4

Match each Cloud SQL backup type to its retention policy.

Question 5

Order the steps to set up a Cloud Spanner instance with a global database.

Question 6

Order the steps to troubleshoot a connection timeout from an application to Cloud SQL.

Question 7

You are designing a database architecture for a global e-commerce application. The application requires low-latency reads in multiple regions and must handle up to 100,000 writes per second globally. Which Google Cloud database solution should you use?

Accepted Answer

Cloud Spanner with multi-region configuration.. Cloud Spanner with a multi-region configuration is the correct choice because it provides globally distributed, strongly consistent relational database service with horizontal scalability, supporting over 100,000 writes per second across multiple regions while maintaining ACID transactions and low-latency reads via regional replicas. This meets the requirements of a global e-commerce application needing high write throughput and low read latency in multiple regions.

Answer

Firestore in multi-region mode.

Answer

Bigtable with multiple clusters.

Answer

Cloud SQL with cross-region replication.

Question 8

You run the above command to create a Spanner instance. Later, you need to increase the instance's compute capacity to handle higher traffic. What is the correct approach?

Accepted Answer

Run gcloud spanner instances update test-instance --nodes=5. Option D is correct because Cloud Spanner allows you to increase the compute capacity of an existing instance by updating the node count using the `gcloud spanner instances update` command. This operation is performed online without downtime, as Spanner supports live resizing of nodes to handle increased traffic.

Answer

Use the console to change the instance configuration to a larger one.

Answer

Delete the instance and recreate with --nodes=5.

Answer

Create a new instance with a larger config and migrate data.

Question 9

A gaming company uses Memorystore for Redis to cache player session data. They need to ensure high availability with automatic failover in case of a zone failure. Which configuration should the Database Engineer choose?

Accepted Answer

Deploy a Standard tier Redis instance with replication across two zones.. Memorystore for Redis Standard tier provides cross-zone replication with automatic failover, ensuring high availability during a zone failure. The Standard tier uses a primary and replica instance in different zones, and if the primary fails, the replica is automatically promoted. This meets the requirement for automatic failover without manual intervention.

Answer

Deploy a Basic tier Redis instance with multiple read replicas.

Answer

Deploy a Memcached cluster with multiple nodes.

Answer

Deploy a Basic tier Redis instance in a single zone.

Question 10

A Database Engineer is responsible for managing a Cloud SQL for MySQL instance. The engineer needs to ensure that automated backups are retained for 14 days and that point-in-time recovery (PITR) is enabled. Which configuration should the engineer set?

Accepted Answer

Enable automated backups, set backup retention to 14 days, and enable binary logging.. Option B is correct because enabling automated backups with a 14-day retention ensures backup files are kept for the required duration, and enabling binary logging (which is required for PITR) allows point-in-time recovery by replaying transaction logs against a base backup. In Cloud SQL for MySQL, PITR is not automatically enabled with automated backups; binary logging must be explicitly enabled.

Answer

Create scheduled manual backups each day and retain them for 14 days; PITR is not needed because backups are daily.

Answer

Enable automated backups with a retention of 14 days; PITR is automatically enabled.

Answer

Enable automated backups with 14-day retention and set up a cross-region replica for disaster recovery.