PCDE Practice Exam 2 — 20 Questions

Question 1

A BI query uses COUNT(column) to count non-null values and COUNT(*) to count all rows. The analyst expects both counts to be equal, but COUNT(column) returns fewer rows. What is the most likely explanation?

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

Accepted Answer

The column contains NULL values, which are not counted by COUNT(column).. COUNT(column) ignores NULL values in the specified column, while COUNT(*) counts every row in the result set regardless of NULLs. If the column contains any NULLs, COUNT(column) will return a lower number. This is a fundamental SQL behavior defined in the ANSI SQL standard and is consistent across all major BI platforms (e.g., Tableau, Power BI, Looker) that generate SQL queries.

Answer

The query has a WHERE clause that filters some rows.

Answer

COUNT(*) is faster, so it's not accurate.

Answer

COUNT(*) counts duplicate rows, while COUNT(column) does not.

Question 2

A company is migrating a large on-premise Hadoop workload to Google Cloud. The data is stored in HBase and consists of time-series logs with 10 PB of data. They need a fully managed NoSQL solution with high throughput and low latency. Which migration path should they choose?

Accepted Answer

Use Cloud Bigtable and the HBase client. Cloud Bigtable is the fully managed, scalable NoSQL database that is compatible with HBase API, making migration straightforward. Cloud Spanner is relational, not the best for time-series logs. BigQuery is for analytics, not real-time. Firestore is document-based and not suited for petabyte-scale logs.

Answer

Migrate HBase tables to Cloud Spanner using Dataflow

Answer

Export data to BigQuery using Storage Transfer Service

Answer

Re-architect as Firestore collections

Question 3

A company uses BigQuery BI Engine for sub-second query performance. However, some queries are hitting the BI Engine memory limit. Which action should be taken?

Accepted Answer

Increase BI Engine capacity allocation.. BI Engine is an in-memory analysis service that accelerates queries by caching data in memory. When queries exceed the allocated memory, they spill to disk, causing performance degradation. Increasing the BI Engine capacity allocation directly addresses this by providing more memory for caching, enabling sub-second query performance for larger datasets.

Answer

Cluster the tables more granularly.

Answer

Use a reservation with a higher slot count.

Answer

Optimize the dimension tables by denormalizing.

Question 4

A company is using Cloud SQL for PostgreSQL and needs to perform point-in-time recovery (PITR) to recover from a logical error that occurred 30 minutes ago. They have already configured automated backups. What additional configuration is required?

Accepted Answer

Set the 'transaction log retention' to a value between 1 and 7 days.. Cloud SQL for PostgreSQL requires point-in-time recovery (PITR) to be enabled by setting the transaction log retention period to a value between 1 and 7 days. This retains WAL logs so the instance can be restored to any point within that window, such as 30 minutes ago. Automated backups alone do not enable PITR.

Answer

Create a cross-region backup replica to enable PITR.

Answer

Enable binary logging on the instance.

Answer

Increase the storage size to accommodate logs.

Question 5

A Cloud SQL for PostgreSQL instance is experiencing high read traffic. You need to offload read queries and ensure the solution can survive a regional outage. What should you do?

Accepted Answer

Create a cross-region read replica and direct read traffic to it.. Cross-region read replicas serve read traffic and provide disaster recovery if the primary fails. Creating a cross-region read replica reduces read load and can be promoted if the primary region fails. Option D is correct. Option A (Cloud Memorystore cache) reduces latency but does not survive a regional outage. Option B (increasing machine type) helps performance but not regional failover. Option C (same-region read replica) does not survive a regional outage.

Answer

Set up Cloud Memorystore as a cache in front of the database.

Answer

Increase the machine type of the primary instance to handle the load.

Answer

Create a read replica in the same region and use it for read queries.

Question 6

A company wants to implement a disaster recovery plan for their AlloyDB database. They need automatic failover with minimal data loss and RTO under 30 seconds. Which configuration should they use?

Accepted Answer

Enable high availability (HA) on the AlloyDB cluster, which provisions a standby in a different zone.. AlloyDB high availability (HA) provisions a standby instance in a different zone within the same region, with automatic failover that typically completes in under 30 seconds and uses synchronous replication to the standby, ensuring minimal data loss. This is the native, supported configuration for automatic failover with sub-30-second RTO and near-zero RPO.

Answer

Configure a cross-region read replica and promote it manually during a disaster.

Answer

Deploy AlloyDB in a single zone without HA.

Answer

Use AlloyDB with multiple read pools and a custom failover script.

Question 7

A company wants to store and analyze time-series metrics from thousands of servers. The data is write-heavy with occasional reads of recent data. They need low-latency writes and the ability to scan large ranges later. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Bigtable is designed for time-series data, high write throughput, and efficient range scans. It can handle large volumes of time-series data with low latency.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud SQL

Question 8

A Cloud Bigtable instance stores time-series data with a row key format: [metric_id]#[timestamp]. The team notices read throughput is low when scanning a metric over a time range. What is the likely cause?

Accepted Answer

All rows for a given metric are stored in a single tablet causing a hotspot.. The row key format [metric_id]#[timestamp] causes all rows for the same metric_id to share the same lexicographic prefix. Cloud Bigtable stores rows in sorted order by row key, so all rows for a given metric are co-located in a single tablet. When scanning a time range for that metric, all read requests hit the same tablet, creating a hotspot that limits throughput to the capacity of a single node.

Answer

Too many column families in the schema.

Answer

The number of nodes is insufficient.

Answer

Replication factor is set too low.

Question 9

A Cloud Spanner query is performing a join between two tables that are frequently accessed together. Which schema design can optimize this query?

Accepted Answer

Use interleaved tables. Interleaved tables store child rows physically with parent rows, improving locality for parent-child joins.

Answer

Use a foreign key constraint

Answer

Create a secondary index

Answer

Denormalize the data into a single table

Question 10

You want to ensure that a critical deployment on GKE has minimal downtime during rolling updates. You also want to ensure that at least 2 pods are always available. Which Kubernetes resource should you configure?

Accepted Answer

PodDisruptionBudget with minAvailable: 2. PodDisruptionBudget (PDB) specifies the minimum number or percentage of pods that must be available during voluntary disruptions like rolling updates. Setting minAvailable: 2 ensures at least 2 pods are running during updates. HPA, VPA, and cluster autoscaler do not control pod availability during updates.

Answer

Cluster autoscaler with minNodes: 2

Answer

HorizontalPodAutoscaler with minReplicas: 2

Answer

VerticalPodAutoscaler with updateMode: Auto

Question 11

A company runs a Cloud SQL PostgreSQL instance for a SaaS application. They notice that the database CPU is consistently above 90% during peak hours, and queries slow down. The application is read-heavy and can tolerate some replication lag. Which action would MOST effectively reduce CPU load on the primary?

Accepted Answer

Create read replicas and route read-only queries to them. Creating one or more read replicas and offloading SELECT queries to them reduces CPU load on the primary instance. Adding more vCPU to the primary (scaling up) increases capacity but is more expensive and may not be as cost-effective as read replicas. PgBouncer helps with connection overhead but not CPU load from queries. Query optimization helps but may not reduce load enough if the volume is high.

Answer

Use EXPLAIN ANALYZE to optimize the slowest queries

Answer

Increase the number of vCPUs on the primary instance

Answer

Enable connection pooling with PgBouncer via Cloud SQL Auth Proxy

Question 12

You need to set up disaster recovery for a Cloud SQL for PostgreSQL instance. The primary instance is in us-central1, and you want a standby in us-west1 that can be promoted to a standalone instance during a regional outage. The solution must minimize data loss and recovery time. Which approach should you take?

Accepted Answer

Create a cross-region read replica in us-west1. During a disaster, promote the replica to a standalone instance.. Creating a cross-region read replica in us-west1 and promoting it during a disaster is the correct approach for Cloud SQL for PostgreSQL. This provides a live standby with asynchronous replication, minimizing data loss (low RPO) and recovery time (low RTO) because promotion is quick and the replica is continuously updated.

Answer

Configure cross-region automated backups with a retention of 7 days. In the event of a disaster, restore the latest backup to a new instance in us-west1.

Answer

Set up an on-premise PostgreSQL instance and configure streaming replication from Cloud SQL to the on-premise instance.

Answer

Use gcloud sql instances create with a backup configuration pointing to us-west1, but this does not create a live standby.

Question 13

A company is designing a data model for a BI dashboard that requires real-time updates and historical analysis. Which THREE practices should be followed?

Accepted Answer

Use clustering on frequently filtered columns.. Clustering on frequently filtered columns in BigQuery organizes data into blocks based on the values of those columns, allowing queries with filters on those columns to skip irrelevant blocks entirely. This reduces the amount of data scanned, improving query performance and lowering costs, which is critical for a BI dashboard that needs real-time updates and fast historical analysis.

Answer

Create a separate table for each day's data.

Answer

Use the default BigQuery table expiration setting.

Question 14

A data engineer is designing a BI solution in BigQuery for a retail chain. They need to support queries that aggregate sales by store, product, and date across millions of transactions. The data is loaded in near real-time from Cloud Pub/Sub. Which table design provides the best balance of query performance and cost?

Accepted Answer

Partition by date, cluster by store_id and product_id. Partitioning by date enables BigQuery to prune entire partitions when querying by date range, which is the most common filter in sales aggregation queries. Clustering on store_id and product_id further reduces the data scanned within each partition by colocating rows with similar store and product values. This design minimizes both query cost (bytes billed) and latency, while supporting near-real-time ingestion from Pub/Sub without requiring table rewrites.

Answer

Partition by store_id, cluster by product_id

Answer

Unpartitioned table with clustering on store_id and product_id

Answer

Use materialized views with aggregation on store_id, product_id, and date

Question 15

An engineer needs to monitor the replication lag of a Cloud SQL read replica. Which metric should they use in Cloud Monitoring?

Accepted Answer

replication_lag. The `replication_lag` metric in Cloud Monitoring directly measures the time delay between a primary Cloud SQL instance and its read replica, reported in seconds. This is the standard metric for monitoring how far behind the replica is in applying changes from the primary, which is critical for ensuring read-after-write consistency and data freshness.

Answer

sent_bytes_count

Answer

disk_bytes_used

Answer

cpu_utilization

Question 16

A startup needs a database for its new web application that will serve a global user base. The application requires low-latency reads and writes (single-digit milliseconds), strong consistency, and the ability to handle high concurrency (thousands of transactions per second). Which Google Cloud database service should the startup choose?

Accepted Answer

Cloud Spanner. Cloud Spanner offers global distribution, strong consistency, and low-latency transactions at high concurrency. It is designed for globally scaled OLTP workloads.

Answer

Cloud SQL for MySQL

Answer

Bigtable

Answer

Firestore

Question 17

Match each Cloud SQL high-availability feature to its description.

Accepted Answer

Automatic failover: Automatically switches to a standby instance in a different zone if the primary instance fails, ensuring minimal downtime.. Automatic failover switches to a standby zone on failure, read replicas provide read-only copies for scaling, and point-in-time recovery allows restoring to any time within retention. Common confusions mix automatic failover with read replicas.

Answer

Automatic failover: Provides read-only copies of the primary instance to distribute read load.

Answer

Read replicas: Automatically switches to a standby instance in a different zone if the primary fails.

Question 18

A team has an SLO of 99.9% availability over a 30-day period. How many minutes of downtime does the error budget allow per month?

Accepted Answer

43 minutes. An SLO of 99.9% availability allows 0.1% unavailability. Over a 30-day month (30 × 24 × 60 = 43,200 minutes), 0.1% equals 43,200 × 0.001 = 43.2 minutes, commonly rounded to 43 minutes. This is the error budget — the maximum downtime permitted while still meeting the SLO.

Answer

4.32 minutes

Answer

432 minutes

Answer

4,320 minutes

Question 19

Refer to the exhibit. The query joins two large tables and aggregates results. Which optimization would most likely reduce the high shuffle bytes in Stage 3?

Accepted Answer

Add a WHERE clause to filter rows before the join.. Adding a WHERE clause before the join reduces the amount of data that needs to be shuffled across the network in Stage 3. In Spark SQL (the engine behind Databricks and many PCDE scenarios), predicate pushdown filters rows early, minimizing the input to the join and subsequent aggregation, which directly reduces shuffle bytes.

Answer

Ensure both tables are clustered on the join key.

Answer

Use a broadcast join hint to force one table to be broadcast.

Answer

Add an ORDER BY clause to sort the data before aggregation.

Question 20

Order the steps to perform a disaster recovery drill for a Cloud Spanner database using backups.

Accepted Answer

Create backup -> Restore to another region -> Verify data integrity -> Update application configuration -> Test application failover. The correct sequence for a Cloud Spanner disaster recovery drill using backups is: first create a backup, then restore it to another region. After restoration, verify data integrity, then update application configurations to point to the restored database, and finally test the application to ensure failover works. This order ensures data consistency and minimizes risk.

Answer

Restore to another region -> Create backup -> Verify data integrity -> Update application configuration -> Test application failover

Answer

Create backup -> Verify data integrity -> Restore to another region -> Update application configuration -> Test application failover

Answer

Create backup -> Restore to another region -> Update application configuration -> Verify data integrity -> Test application failover