PCDE Practice Test 13 — 15 Questions

Question 1

A company uses BigQuery for real-time BI. They have a table with streaming inserts. Analysts run queries that need to see data within seconds. However, they notice that streaming data appears with a delay of up to 2 minutes. What is the most likely reason?

Accepted Answer

The streaming buffer's flush interval is set to 2 minutes.. Option C is correct because BigQuery's streaming buffer has a default flush interval of up to 90 seconds, but it can be configured. When the flush interval is set to 2 minutes, data written via streaming inserts remains in the buffer for that duration before being committed to the table, causing a delay of up to 2 minutes before it becomes visible to queries. This matches the symptom described in the question.

Answer

The query uses cached results.

Answer

The table is partitioned by hour.

Answer

The table has a clustering key.

Question 2

A BI team is troubleshooting a slow BigQuery query. Which TWO actions can help identify the bottleneck?

Accepted Answer

Review the query execution plan in the BigQuery UI.. Reviewing the query execution plan in the BigQuery UI (Option A) is correct because it provides a visual breakdown of query stages, including shuffle operations, data distribution, and stage-level timing. This allows the BI team to pinpoint which stage is consuming the most time or resources, such as a skewed join or a slow aggregation, directly identifying the bottleneck.

Answer

Increase the number of slots to maximum.

Answer

Remove all WHERE clauses to simplify.

Answer

Rewrite the query to use only CTEs.

Question 3

A team is designing a disaster recovery plan for a Cloud Spanner instance. They want an RPO of 10 minutes and RTO of 5 minutes. Which two features should they use?

Accepted Answer

Enable point-in-time recovery (PITR) with a 10-minute recovery period. Point-in-time recovery (PITR) with a 10-minute recovery period is correct because it allows restoring Cloud Spanner data to any point within the last 10 minutes, meeting the RPO of 10 minutes. PITR provides versioned data retention without requiring manual backups, enabling recovery within seconds to minutes, which satisfies the RTO of 5 minutes when combined with a multi-region configuration.

Answer

Deploy a cross-region read replica

Answer

Configure automated backups

Answer

Use database versioning to failover

Question 4

A company is designing a data warehouse for business intelligence reporting. They want to organize data into fact and dimension tables to support fast aggregations. Which schema design is most appropriate for this purpose?

Accepted Answer

Star schema. The star schema is most appropriate for business intelligence reporting because it denormalizes dimension tables around a central fact table, enabling fast aggregations and simple queries. This design minimizes the number of joins required for analytical queries, which is critical for performance in OLAP workloads. In contrast, normalized schemas like 3NF or snowflake increase join complexity and degrade query speed.

Answer

Third Normal Form (3NF) schema

Answer

Snowflake schema

Answer

Entity-relationship schema

Question 5

A data engineer is creating a reporting layer in BigQuery for BI tools. Which TWO practices improve query performance?

Accepted Answer

Use approximate aggregate functions when exact accuracy is not needed.. Option A is correct because BigQuery's approximate aggregate functions (e.g., APPROX_COUNT_DISTINCT, APPROX_QUANTILES) use HyperLogLog++ and other sketching algorithms to return results with a small, bounded error (typically <1%) while drastically reducing the amount of data scanned and shuffled. This trade-off is ideal for BI dashboards where exact counts are not critical, as it can cut query execution time by orders of magnitude.

Answer

Use SELECT * in queries.

Answer

Use ORDER BY in subqueries unnecessarily.

Answer

Store all data in a single table without partitioning.

Question 6

Refer to the exhibit. The query scans 500 GB even though it filters on the partitioning column event_date and only needs data from 30 days. What is the most likely reason?

Accepted Answer

COUNT(DISTINCT) often results in full table scan to ensure accuracy, even with partitions.. Option A is correct because COUNT(DISTINCT) in many SQL engines, including those used in data warehousing like Google BigQuery or Snowflake, often requires a full scan of all partitions to ensure global uniqueness. Even with a filter on the partitioning column, the engine cannot guarantee that distinct values are confined to the filtered partitions without scanning all data, especially if the distinct operation spans across partitions or if the engine's optimizer lacks partition pruning for distinct aggregations.

Answer

The query lacks a LIMIT clause.

Answer

The clustering on user_id is causing a full table scan.

Answer

The table is not actually partitioned by event_date; the filter is on a non-partitioned column.

Question 7

A company stores user events in BigQuery as nested repeated fields. They want to use Looker to build dashboards on individual events. Which SQL pattern should they use in a derived table to flatten the data?

Accepted Answer

SELECT fields FROM table, UNNEST(events) AS event. Option B is correct because UNNEST(events) in BigQuery SQL flattens the nested repeated field 'events' into individual rows, enabling Looker to treat each event as a separate record for dashboarding. This is the standard pattern for denormalizing arrays in BigQuery derived tables, as it converts each array element into its own row while preserving the parent record's fields.

Answer

SELECT fields FROM table WHERE events IS NOT NULL

Answer

SELECT ARRAY_AGG(events) FROM table

Answer

SELECT events.* FROM table

Question 8

A database administrator notices that a Cloud SQL for MySQL instance is experiencing high CPU usage during peak hours. The instance has 4 vCPUs and 15 GB of memory. The query patterns are mostly read-intensive with occasional writes. Which action should the DBA take first to address the high CPU usage?

Accepted Answer

Analyze slow query log and optimize queries. High CPU usage in a read-intensive Cloud SQL for MySQL instance is most often caused by inefficient queries that consume excessive CPU cycles. Analyzing the slow query log allows the DBA to identify and optimize these queries, addressing the root cause directly. Increasing resources or changing configuration without understanding the workload can mask the problem and lead to unnecessary costs.

Answer

Increase the max_connections flag to allow more concurrent connections

Answer

Enable read pool to offload read queries

Answer

Increase the machine type to 8 vCPUs

Question 9

Refer to the exhibit. A BI query is performing slowly. The query plan shows a large shuffle in the aggregate stage. The table is not partitioned or clustered. Which optimization would most directly reduce the shuffle size?

Accepted Answer

Clustering the table on the grouping columns.. Clustering the table on the grouping columns physically co-locates rows with the same group key values within the same storage units (e.g., files or partitions). This allows the query engine to perform partial aggregation locally before the shuffle, dramatically reducing the amount of data that must be moved across the network during the aggregate stage. In systems like BigQuery or Spark SQL, clustering on grouping columns directly minimizes shuffle size by enabling pre-aggregation at the storage layer.

Answer

Converting the query to use a window function.

Answer

Using a materialized view.

Answer

Adding a WHERE clause to filter recent data.

Question 10

A company runs a Bigtable instance for time-series data. They need to reduce storage costs without compromising query performance for the most recent 30 days. Which three strategies should they implement?

Accepted Answer

Use Cloud Storage as a cold storage tier for historical data. Option B is correct because moving historical data (older than 30 days) to Cloud Storage as a cold storage tier reduces Bigtable storage costs while keeping the most recent 30 days in Bigtable for fast queries. Option D is correct because setting garbage collection (GC) to delete data older than 30 days automatically removes stale data, reducing storage footprint without impacting query performance for recent data. Both strategies directly address cost reduction while preserving performance for the required time window.

Answer

Increase the number of cluster nodes to improve compaction

Answer

Enable Bigtable replication and delete data from one cluster

Answer

Reduce the number of cluster nodes to save costs

Question 11

A company uses Cloud Spanner with a multi-region configuration (nam7) to support a global user base. They notice increased read latency for users in Europe, while write latency is acceptable. The database engineer observes that most queries are single-row reads using the primary key. What is the best approach to reduce read latency for European users?

Accepted Answer

Use read-only transactions with stale reads (timestamp bound) to read from local replicas.. Option C is correct because Cloud Spanner's multi-region configuration (nam7) includes regional replicas in Europe. By using stale reads with a timestamp bound, read-only transactions can be served from a local replica without incurring cross-region latency, which directly reduces read latency for European users while maintaining acceptable consistency.

Answer

Create a secondary index on the primary key column.

Answer

Increase the number of Spanner nodes to distribute the load.

Answer

Partition the database by region and use directed reads.

Question 12

A Cloud SQL instance is running low on disk space. You want to increase disk size with zero downtime. Which action should you take?

Accepted Answer

Use gcloud sql instances patch to increase the storage size. Option D is correct because Cloud SQL supports online storage increases without downtime. The `gcloud sql instances patch` command (or the equivalent Console/API operation) dynamically adds more disk capacity to the running instance while it continues serving traffic. This is possible because Cloud SQL uses persistent disks that can be resized online, and the database engine (e.g., MySQL, PostgreSQL) does not need to restart to recognize the additional space.

Answer

Stop the instance, increase disk, and restart

Answer

Export data, create a new instance with larger disk, and import

Answer

Create a clone with larger disk and failover

Question 13

A team is reviewing IAM permissions on a Cloud Storage bucket. The exhibit shows the bucket's IAM policy. A developer is using the service account sa-1 and reports that they cannot delete objects in the bucket. What is the likely reason?

Accepted Answer

The service account sa-1 does not have the storage.objects.delete permission.. The IAM policy shown in the exhibit grants the `objectViewer` role to service account `sa-1`, which includes the `storage.objects.get` and `storage.objects.list` permissions but does not include `storage.objects.delete`. Without the `storage.objects.delete` permission, the developer cannot delete objects in the bucket, even if they can view them. The correct answer is B because the service account lacks the necessary delete permission.

Answer

The etag value must be updated before any delete operation.

Answer

A condition is attached to the objectViewer role that prevents deletion.

Answer

The policy only allows deletion by the service account sa-2.

Question 14

A company needs to store time-series sensor data with high write throughput (millions of writes per second) and low latency reads. Which database service should they choose?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is a fully managed, scalable NoSQL database designed for large analytical and operational workloads, handling millions of writes per second with consistent low-latency reads. It uses a distributed, replicated SSTable storage engine and is optimized for time-series data, making it ideal for high-throughput sensor ingestion.

Answer

Cloud SQL

Answer

Firestore

Answer

Cloud Spanner

Question 15

A company wants to migrate a 100 GB MySQL database to Cloud SQL with minimal application changes. Which migration tool should they use?

Accepted Answer

Database Migration Service. Database Migration Service (DMS) is the correct tool because it is designed specifically for migrating databases to Cloud SQL with minimal downtime and minimal application changes. DMS uses a combination of initial snapshot and continuous change data capture (CDC) to replicate the source MySQL database to Cloud SQL, allowing the application to point to the new database with only a connection string update.

Answer

mysqldump

Answer

BigQuery Data Transfer Service

Answer

Storage Transfer Service