PCDE Timed Practice Test 3 — 10 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?

10 minute time limit — choose an answer to begin.

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