PCDE Practice Test 17 — 15 Questions

Question 1

A BI team uses BigQuery to report on customer orders. The 'customers' dimension table is updated nightly with Type 2 Slowly Changing Dimensions (SCD). However, some reports show incorrect historical aggregates because the fact table references only the current customer key. Which approach resolves this issue?

Accepted Answer

Store the surrogate customer key from the dimension table in the fact table at transaction time. Option B is correct because with Type 2 SCD, each customer row has a unique surrogate key that represents a specific version of the customer's attributes over time. Storing that surrogate key in the fact table at transaction time ensures that historical facts are permanently linked to the correct customer attributes as they existed at the time of the order. This prevents incorrect aggregates when the dimension table is updated, as the fact table will always join to the precise version of the customer record that was active when the transaction occurred.

Answer

Update the fact table nightly to replace old customer keys with the current key

Answer

Denormalize customer attributes into the fact table

Answer

Use the natural customer ID in the fact table and join with the dimension using a BETWEEN condition on effective dates

Question 2

A database administrator is planning to migrate an on-premises MySQL database to Cloud SQL. Which two steps are required to ensure a secure migration?

Accepted Answer

Configure Cloud SQL to use a private IP address. Option A is correct because using a private IP address for Cloud SQL ensures that the database instance is not exposed to the public internet, reducing the attack surface. This is a fundamental security best practice for database migrations, as it restricts network access to within a Virtual Private Cloud (VPC) and requires traffic to traverse Google's internal network, which is more secure than public IP routing.

Answer

Add authorized networks for all client IPs

Answer

Ensure the database is encrypted at rest using CMEK

Answer

Enable SSL/TLS for all connections

Question 3

A global retail company uses Cloud Spanner to manage product inventory. They need to apply a schema change to add a new column to a table that has 10 billion rows. Which THREE strategies should they consider to minimize downtime?

Accepted Answer

Schedule the schema change during a period of low traffic.. Option A is correct because scheduling schema changes during low traffic reduces the risk of contention and performance impact on the live database. Cloud Spanner applies schema changes online without locking the entire table, but heavy write traffic can still cause transaction conflicts or increased latency; performing the change during a quiet period minimizes these risks.

Answer

Disable the table, add the column, then re-enable.

Answer

Create a new table with the column, then copy data in batches.

Question 4

A company wants to store and analyze data with BigQuery. They have customers in Europe and need to comply with GDPR data residency requirements. What should they do to ensure data stays within the European Union?

Accepted Answer

Create the dataset with the location set to 'EU'.. Option B is correct because BigQuery enforces data residency at the dataset level. When you create a dataset with the location set to 'EU', all tables, views, and data within that dataset are physically stored in a Google Cloud region within the European Union. This ensures compliance with GDPR data residency requirements, as data will not be moved or replicated outside the EU without explicit user action.

Answer

Use the default dataset location and rely on BigQuery's automatic compliance.

Answer

Set the query job location to 'EU' for all queries.

Answer

Choose a dataset location of 'us-central1' for performance.

Question 5

A team is designing a disaster recovery plan for a Cloud SQL for PostgreSQL instance. The RPO is 5 minutes and RTO is 1 hour. Which configuration meets these requirements?

Accepted Answer

Configure a cross-region read replica with point-in-time recovery (PITR) to enable failover. Option D meets the RPO of 5 minutes and RTO of 1 hour because a cross-region read replica with point-in-time recovery (PITR) allows you to failover to a replica in another region, minimizing data loss to within seconds (PITR can recover to any point in time within the retention window) and promoting the replica typically completes within minutes, well under the 1-hour RTO. This configuration provides both disaster recovery across regions and granular recovery to meet the strict RPO.

Answer

Schedule daily exports to Cloud Storage and import in another region

Answer

Enable automatic failover within the same region using HA configuration

Answer

Create a read replica in the same region and promote it during disaster

Question 6

A company is migrating a large Oracle database to Cloud Spanner. The source database uses sequences for primary key generation. The database engineer needs to design the Cloud Spanner schema to avoid hotspotting. What primary key design should they recommend?

Accepted Answer

Use a hash of the original key as a prefix to the primary key.. Option C is correct because using a hash of the original key as a prefix to the primary key distributes writes evenly across Cloud Spanner's splits, preventing hotspotting. Cloud Spanner uses a distributed, append-only storage model where sequential keys (like from Oracle sequences) cause all new writes to land on the same split, creating a hotspot. A hash prefix ensures that related rows are still co-located for efficient queries while spreading write load across multiple nodes.

Answer

Keep the same sequence-based integer keys.

Answer

Use a composite primary key with a timestamp prefix.

Answer

Use UUIDs as the primary key without modification.

Question 7

Refer to the exhibit. The company plans to store 3 TB of data in this instance. What is the minimum number of nodes required? (Assume 2 TB per node for HDD and 4 TB per node for SSD; this instance uses SSD.)

Accepted Answer

2. The instance uses SSD storage, which provides 4 TB per node. To store 3 TB of data, a single node with 4 TB SSD would suffice, but the question asks for the minimum number of nodes required. However, the correct answer is 2 because in a distributed database like Couchbase (commonly tested in PCDE), data is replicated across nodes for high availability and durability. With 1 node, there is no redundancy; with 2 nodes, you can store 3 TB of data while maintaining a replica, as each node contributes 4 TB of usable storage, and the effective storage after replication is 4 TB (2 nodes × 4 TB / 2 replicas = 4 TB), which is sufficient for 3 TB.

Answer

4

Answer

1

Answer

3

Question 8

A database engineer is configuring a Memorystore for Redis instance for a session store application. The application requires persistent storage to survive node failures. Which tier and configuration should be used?

Accepted Answer

Standard tier with AOF persistence enabled. The Standard tier in Memorystore for Redis provides a replicated architecture with a primary and read replica, ensuring high availability and automatic failover. Enabling AOF (Append-Only File) persistence writes every write operation to an AOF file, which is stored on persistent disk and can be replayed to restore data after a node failure. This combination meets the session store requirement for data durability across failures, as the Basic tier lacks replication and cannot guarantee data survival.

Answer

Standard tier with no persistence

Answer

Basic tier with RDB persistence enabled

Answer

Basic tier with AOF persistence enabled

Question 9

A company notices that their Cloud SQL for PostgreSQL instance, as shown in the exhibit, frequently runs out of storage, causing downtime. They have set up automated backups with point-in-time recovery. What is the most likely cause of the storage issue?

Accepted Answer

Transaction logs for point-in-time recovery are consuming disk space.. The correct answer is A because Cloud SQL for PostgreSQL uses transaction logs (WAL files) to enable point-in-time recovery (PITR). These logs accumulate on the disk until they are automatically removed, but if the rate of log generation exceeds the cleanup rate or if the backup retention period is long, the logs can fill the disk, causing storage exhaustion and downtime.

Answer

The activation policy is set to ALWAYS, causing continuous writes.

Answer

The instance tier (db-custom-4-15360) is too low for the workload.

Answer

The data disk type (PD_SSD) is not suitable for PostgreSQL.

Question 10

A company runs a production Cloud SQL for PostgreSQL instance. They need to ensure high availability with automatic failover in case of a zone failure. Which configuration should they use?

Accepted Answer

Enable high availability (HA) configuration during instance creation.. Option D is correct because enabling the high availability (HA) configuration during Cloud SQL for PostgreSQL instance creation automatically provisions a standby instance in a different zone within the same region. This synchronous replication setup ensures automatic failover with minimal data loss (typically under 1 second RPO) in the event of a zone failure, meeting the requirement for high availability.

Answer

Create a cross-region read replica and set it as failover target.

Answer

Use a zonal HA configuration by selecting a regional location.

Answer

Deploy a single Cloud SQL instance with multiple CPUs and high memory.

Question 11

An e-commerce application uses Cloud Spanner for its global inventory database. The application experiences high write latency during peak hours. After reviewing the schema, the database engineer notices that the primary key is an auto-incrementing integer. What is the most likely cause of the high write latency?

Accepted Answer

The monotonically increasing primary key creates a hotspot.. Cloud Spanner distributes data across splits based on the primary key range. A monotonically increasing integer primary key, such as an auto-incrementing ID, causes all new writes to target the same split (the highest key range), creating a hotspot. This single split becomes a bottleneck, leading to high write latency during peak hours, as Spanner cannot parallelize the writes across multiple nodes.

Answer

The application is using read-only transactions instead of read-write transactions.

Answer

The database is configured with a backup retention period of 2 seconds.

Answer

The application is using read replicas that are out of date.

Question 12

A company is planning to migrate their on-premises Oracle database to Cloud SQL. Which THREE prerequisites must be satisfied?

Accepted Answer

Convert Oracle-specific syntax to PostgreSQL or MySQL. Option A is correct because Cloud SQL does not support Oracle's proprietary PL/SQL syntax. When migrating from Oracle to Cloud SQL, you must convert Oracle-specific syntax (e.g., sequences, packages, hierarchical queries) to the target dialect—either PostgreSQL or MySQL—since Cloud SQL offers only these two engines. This conversion is a prerequisite to ensure the migrated database functions correctly after the move.

Answer

Purchase Cloud SQL Enterprise Plus edition for the target instance

Answer

Create a Cloud Storage bucket for staging migration data

Question 13

Refer to the exhibit. A developer deployed these Firestore security rules. What is a security concern with this configuration?

Accepted Answer

All authenticated users can read all documents. Option C is correct because the Firestore security rules shown grant read access to all authenticated users without any document-level or collection-level restrictions. The rule `match /databases/{database}/documents { allow read: if request.auth != null; }` applies to all documents in the database, meaning any authenticated user can read every document, including those they should not have access to. This violates the principle of least privilege and can lead to unauthorized data exposure.

Answer

Only the owner can write to documents

Answer

The rules do not explicitly allow delete operations

Answer

The wildcard path matches all databases

Question 14

Which TWO factors are important when selecting the location for a Cloud Spanner instance?

Accepted Answer

Data residency compliance. Data residency compliance (B) is critical because Cloud Spanner instances are regional resources; data is stored and processed within the chosen Google Cloud region, and many regulations (e.g., GDPR, HIPAA) mandate that data remain within specific geographic boundaries. Proximity to users (C) is important because Spanner's read and write latencies are directly affected by network distance; choosing a region close to your user base minimizes latency and improves application performance.

Answer

Number of other Spanner instances in the region

Answer

Cost of Cloud Spanner nodes in the region

Answer

Availability of Cloud VPN

Question 15

Refer to the exhibit. The application requires low-latency reads for users in Europe. The current cluster is in us-central1. What should they do?

Accepted Answer

Add a new cluster in a European region (e.g., europe-west1).. Adding a new cluster in a European region (e.g., europe-west1) is correct because it places data physically closer to users, reducing network latency for read operations. In a multi-region deployment, the application can read from the nearest cluster, achieving low-latency reads without changing the existing cluster's configuration. This approach leverages geographic proximity to minimize round-trip time (RTT) for European users.

Answer

Increase the number of nodes in the existing cluster.

Answer

Use a multi-cluster instance with existing cluster.

Answer

Change the storage type to SSD in the existing cluster.