PCDE Practice Exam 1 — 20 Questions

Question 1

A company is designing a database solution for a global social media application that requires strong consistency, high write throughput, and complex relational queries. Which TWO Google Cloud databases should they consider? (Choose 2)

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

Accepted Answer

Cloud Spanner. Cloud Spanner provides global strong consistency and relational support. AlloyDB offers strong consistency and high performance for relational workloads. Bigtable is eventually consistent. BigQuery is analytical. Firestore is not globally consistent.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Firestore

Question 2

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. The database is 2 TB in size and the network bandwidth to Google Cloud is limited to 500 Mbps. The migration window is 48 hours. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Export the database to flat files, compress, upload to Cloud Storage, then import to Cloud SQL.. The 2 TB database size and 500 Mbps bandwidth yield a theoretical transfer time of approximately 9.5 hours (2 TB * 1024 GB/TB * 8 bits/byte / 500 Mbps / 3600 seconds/hour), which fits within the 48-hour window. However, pg_dump/pg_restore over a VPN (Option A) would be slower due to TCP overhead and latency, and Database Migration Service with continuous replication (Option B) requires ongoing connectivity and may not complete the initial load within the window. Exporting to flat files, compressing them (e.g., with gzip), uploading to Cloud Storage, and then importing to Cloud SQL leverages high-throughput parallel uploads and avoids network latency issues, making it the most reliable strategy for a one-time migration within the given constraints.

Answer

Create a VPN tunnel and use pg_dump/pg_restore over the network.

Answer

Use Database Migration Service with continuous replication.

Answer

Request a dedicated interconnect and then migrate.

Question 3

A company needs to migrate its on-premises Oracle database to a fully managed relational database on Google Cloud. The application uses stored procedures and requires high availability across multiple zones. Which migration path is MOST appropriate?

Accepted Answer

Migrate to Cloud SQL for PostgreSQL and configure cross-zone high availability. Cloud SQL for PostgreSQL supports high availability across zones and can use the PostgreSQL dialect with some migration effort. Bare Metal Solution is for lifting and shifting Oracle workloads but is not fully managed. AlloyDB is PostgreSQL-compatible and provides high availability. The best option is Cloud SQL for PostgreSQL due to its managed nature and cross-zone HA.

Answer

Use Database Migration Service to migrate to Cloud SQL for SQL Server

Answer

Use Bare Metal Solution to run Oracle Database on dedicated hardware

Answer

Migrate to AlloyDB for PostgreSQL with cross-region replication

Question 4

An engineer is designing a Cloud Spanner table for a global user activity tracking system with high write throughput. Which primary key design is BEST to avoid hotspots?

Accepted Answer

UUID string (generated by application). Using a UUID or hash-prefixed key distributes writes evenly across nodes, preventing hotspots that occur with monotonically increasing keys.

Answer

Monotonically increasing integer (INT64) with auto-increment

Answer

Composite key with user_id as first part

Answer

Timestamp as primary key

Question 5

Which database service should you use if your workload requires complex JOINs, ACID transactions, and you want to avoid operational overhead of managing patching and backups?

Accepted Answer

Cloud SQL. Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server that handles patching, backups, and replication. AlloyDB also is managed, but Cloud SQL is more common for standard OLTP. Firestore and Bigtable are NoSQL.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud Storage

Question 6

An organization uses Cloud Spanner and needs to add a new column to an existing table without downtime. The table has billions of rows and is heavily used. What is the recommended approach to add the column?

Accepted Answer

Use ALTER TABLE ADD COLUMN statement. Cloud Spanner schema changes are online and non-blocking, so you can simply use ALTER TABLE to add the column; it will not cause downtime.

Answer

Create a new table with the column, then copy data and rename

Answer

Add the column using gcloud command and set a downtime window

Answer

Backup the database, add column, then restore

Question 7

A team is migrating a MongoDB application to Firestore. The data model includes embedded documents and references between collections. Which approach should they follow to maintain similar query performance?

Accepted Answer

Use subcollections for embedded data and references for relationships. Firestore supports subcollections (similar to embedded documents) and references. Denormalization can reduce the need for joins. Avoiding transactions for every write is not ideal for consistency. The recommended pattern is to use subcollections for embedded data and references for related entities.

Answer

Flatten all data into a single document per entity

Answer

Replicate data across multiple collections to avoid reads

Answer

Use a single collection for all documents and rely on client-side joins

Question 8

A company uses Cloud SQL for MySQL and wants to run complex analytical queries on the same data without affecting OLTP performance. They need a solution with minimal data movement and low operational overhead. Which approach should they take?

Accepted Answer

Use AlloyDB for PostgreSQL with its columnar engine. AlloyDB is a PostgreSQL-compatible database that includes a columnar engine for analytical queries, providing HTAP capabilities with minimal performance impact on OLTP. BigQuery requires exporting data, which adds latency and overhead. Read replicas still run on MySQL engines optimized for OLTP. Spanner is overkill and requires migration. AlloyDB is the best fit for HTAP.

Answer

Migrate to Cloud Spanner

Answer

Export data to BigQuery periodically

Answer

Set up a Cloud SQL read replica and run analytical queries on it

Question 9

An organization needs a fully managed, globally distributed relational database with strong consistency and horizontal scaling for a multi-region application. Which service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner provides global distribution, strong consistency, horizontal scaling, and relational features.

Answer

Bigtable

Answer

Firestore

Answer

Cloud SQL

Question 10

A company is planning to use Cloud Spanner for a new global application. They estimate a peak write throughput of 10,000 mutations per second. What is the minimum number of processing units (PUs) required, given that each PU supports up to 2000 mutations/second?

Accepted Answer

5 processing units. Each PU supports 2000 mutations/second. To get 10,000 mutations/second, you need 10,000 / 2000 = 5 PUs. However, note that Spanner also requires at least 1 node (1000 PUs) for production. But the question asks for PUs based on write throughput alone.

Answer

1 processing unit

Answer

100 processing units

Answer

10 processing units

Question 11

A financial services company is migrating a legacy on-premises OLTP application to Google Cloud. The application requires high transaction rates (thousands per second), strict ACID compliance, and the ability to scale horizontally across multiple regions with strong consistency. Which Google Cloud database service should the company choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, horizontally scalable relational database service that provides ACID transactions and strong consistency across regions. It is ideal for OLTP workloads that require high throughput, consistency, and global scalability.

Answer

Bigtable

Answer

Cloud SQL for PostgreSQL

Answer

Firestore

Question 12

A data analyst needs to run a one-time SQL query on a dataset stored in CSV files in Cloud Storage. The query will scan the entire dataset, and the analyst wants to minimize cost. Which Google Cloud service should they use?

Accepted Answer

BigQuery. BigQuery can query external data directly from Cloud Storage; using query-on-demand billing, they only pay for the data scanned. It is serverless and requires no infrastructure.

Answer

Cloud SQL

Answer

Dataproc

Answer

Cloud Spanner

Question 13

A company runs a MySQL database on-premises and wants to migrate to Cloud SQL with minimal downtime. They have a multi-terabyte database. Which migration method is most appropriate?

Accepted Answer

Use Database Migration Service with continuous replication.. Database Migration Service (DMS) supports continuous replication and minimal downtime for large databases. It handles initial snapshot and ongoing CDC.

Answer

Use Cloud SQL's import feature from a CSV file.

Answer

Take a physical backup and restore to Cloud SQL.

Answer

Use mysqldump to export and then import into Cloud SQL.

Question 14

An e-commerce platform uses Cloud Spanner as its database. The orders table has a monotonically increasing order_id as the primary key, and the team observes high write latency during peak hours. Which design change would BEST distribute write load across nodes?

Accepted Answer

Change the primary key to a UUID string. Using a UUID (or a hash prefix) as the primary key or as a leading part of a composite key prevents hotspotting on a single tablet server by distributing writes across the cluster.

Answer

Add a secondary index on order_id

Answer

Increase the number of nodes

Answer

Use interleaving with a parent table

Question 15

Refer to the exhibit. This DDL is used to create a table in Cloud Spanner. The table will be used for storing user data with high write throughput. What is one performance issue with this table design?

Accepted Answer

The primary key is a monotonically increasing integer. Using a monotonically increasing integer as the primary key in Cloud Spanner causes all writes to be directed to a single split (tablet), creating a hotspot that severely limits write throughput. Cloud Spanner distributes splits across nodes based on key ranges, and sequential keys prevent this distribution, leading to performance degradation under high write load.

Answer

The Name column is of type STRING(MAX)

Answer

There are no secondary indexes

Answer

The table is not interleaved with another table

Question 16

A database administrator needs to restore a Cloud SQL for PostgreSQL instance to a specific point in time within the last 3 hours. Which configurations must be enabled to perform a point-in-time recovery (PITR)?

Accepted Answer

Automated backups and WAL archiving with a retention period. PITR in Cloud SQL for PostgreSQL requires automated backups enabled and write-ahead log (WAL) archiving with a specified transaction log retention period (1-7 days). Binary logging is for MySQL, not PostgreSQL.

Answer

Automated backups and binary logging

Answer

On-demand backups and binary logging

Answer

Automated backups and cross-region replicas

Question 17

A team wants to create a Cloud Spanner database backup and store it in a Cloud Storage bucket for long-term archival. Which method should they use?

Accepted Answer

Use the gcloud spanner databases backup command to create a backup that resides in Cloud Storage.. Cloud Spanner provides database-level backups directly to Cloud Storage in Avro + Protobuf format. Export/import is for migration, not archival backups.

Answer

Use gcloud spanner instances create-backup to create a backup of the instance.

Answer

Take a snapshot of the Spanner instance using Cloud Storage snapshots.

Answer

Use the gcloud spanner databases export command to export to Cloud Storage.

Question 18

A Cloud Spanner database is experiencing increased read traffic. You need to add an index to improve query performance without downtime. What is the correct approach?

Accepted Answer

Use the CREATE INDEX statement. Spanner creates indexes online without blocking writes or reads.

Answer

Export the database, create the index in a new instance, then import.

Answer

Use the ALTER TABLE statement to add the index as a constraint.

Answer

Create a secondary index using the 'CREATE INDEX WITH OFFLINE' option to avoid blocking.

Question 19

A Memorystore for Redis instance is running out of memory. The application uses Redis as a cache with key expiration. You want to prevent data loss for keys that have not reached their TTL. Which eviction policy should you configure?

Accepted Answer

volatile-lru

Answer

noeviction

Answer

volatile-ttl

Answer

allkeys-lru

Question 20

Which TWO statements about Cloud Spanner schema changes are correct? (Choose 2)

Accepted Answer

Schema changes are applied asynchronously and may take minutes to hours to complete.

Answer

Schema changes require the instance to be restarted.

Answer

You can only add columns, not drop them.

Answer

Creating a secondary index requires taking the table offline.