PCDE Practice Test 30 — 15 Questions

Question 1

What is the most likely cause of the high execution time?

Accepted Answer

Missing index on status column. The execution plan shows a full table scan. Since the query filters on status, an index on the status column would avoid the scan and reduce execution time. Options A, B, D, E are not the primary cause.

Answer

The WHERE clause uses a string comparison

Answer

The query selects two columns causing inefficiency

Answer

The instance has insufficient nodes

Answer

The query does not filter on primary key

Question 2

An e-commerce site uses Cloud SQL for MySQL. They need read scalability for product catalog queries. What should they do?

Accepted Answer

Add read replicas. Adding read replicas in Cloud SQL for MySQL offloads read traffic from the primary instance, providing horizontal read scalability for product catalog queries. Replicas asynchronously replicate data using MySQL's native binary log replication, allowing the primary to focus on writes while replicas handle read-heavy workloads.

Answer

Partition tables

Answer

Use Cloud Spanner

Answer

Enable automatic storage increase

Question 3

Which TWO are best practices for optimizing write performance in Cloud Bigtable?

Accepted Answer

Group multiple mutations into a single request. Option B is correct because Bigtable batches mutations into a single RPC request, reducing network round trips and improving throughput. Sending individual mutations incurs per-request overhead, so grouping them into a single atomic or non-atomic batch (via `MutateRows` or client-side batching) significantly increases write throughput.

Answer

Use short row keys to reduce storage size

Answer

Use the Dataflow Bulk Import API for real-time writes

Answer

Increase replication lag to allow more time for writes

Question 4

A data analyst needs to create a reporting table that aggregates sales data by month. They want to ensure the table is optimized for querying by month and product category. Which table design best supports this?

Accepted Answer

Partition by month and cluster by product_category.. Option D is correct because partitioning by month physically separates data into monthly segments, allowing query pruning to skip irrelevant partitions when filtering by month. Clustering by product_category within each partition co-locates rows with the same category, reducing the amount of data scanned for queries that filter on both month and category. This design optimizes both I/O and scan efficiency for the described workload.

Answer

Use a table with clustering on product_category only.

Answer

Use a flat table with no partitioning.

Answer

Use a view that selects month and product_category.

Question 5

A company is using BigQuery for BI and needs to reduce costs for a large historical dataset that is infrequently queried. Which approach should they take?

Accepted Answer

Partition by ingestion time and set expiration on partitions older than 90 days.. Option C is correct because partitioning by ingestion time allows BigQuery to automatically manage data lifecycle by setting partition expiration. This reduces storage costs for historical data that is infrequently queried, as partitions older than 90 days are deleted without manual intervention. This approach directly addresses the need to reduce costs for a large historical dataset while maintaining query performance on recent data.

Answer

Use materialized views for common aggregations.

Answer

Use clustered tables.

Answer

Use a view with a WHERE clause filtering recent data.

Question 6

Your company runs a global e-commerce platform on Google Cloud Spanner. The database schema includes an 'Orders' table with primary key (OrderId, CustomerId) and an 'OrderItems' table with primary key (OrderId, CustomerId, ItemId), interleaved in parent Orders on delete cascade. During peak shopping hours, you notice that queries retrieving all items for a specific order are performing full table scans on the OrderItems table, leading to increased latency and higher CPU utilization. The queries use the OrderId as the filter condition. The database administrators have already checked that the query plans show table scans instead of using the interleaved index. You are tasked with resolving this performance issue. Which of the following actions should you take?

Accepted Answer

Remove CustomerId from the Orders primary key (making it just OrderId) and update OrderItems to have primary key (OrderId, ItemId), maintaining interleaving.. Option A is correct because the interleaved index in Cloud Spanner requires that the parent table's primary key columns be a prefix of the child table's primary key. With the original schema, queries filtering only on OrderId cannot use the interleaved index because CustomerId is missing from the filter, forcing a full table scan. By removing CustomerId from the primary key of Orders and OrderItems, OrderId becomes the leading column, allowing the interleaved index to be used for efficient point lookups.

Answer

Change the primary key of Orders to (OrderId, CustomerId) and update OrderItems accordingly.

Answer

Create a secondary index on OrderItems(OrderId).

Answer

Increase the number of Spanner nodes to improve throughput.

Question 7

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 8

The query returns results but takes a long time. The orders table has 500M rows with order_date as a timestamp and revenue as float. How can the query be optimized?

Accepted Answer

Partition the table by month on order_date.. Partitioning the table by month on order_date (Option B) is correct because it physically separates the data into monthly partitions, allowing the query engine to prune partitions that do not match the query's time range. This dramatically reduces the amount of data scanned, which is the primary cause of slow performance on a 500M-row table. In BigQuery, partitioning by a timestamp column like order_date is a native, cost-effective optimization that directly addresses the scan bottleneck.

Answer

Add a clustering key on order_date.

Answer

Use a wildcard table over multiple date-sharded tables.

Answer

Use a materialized view that caches the query result.

Question 9

Which TWO best practices should be followed when modeling data for a Looker BI dashboard to optimize query performance?

Accepted Answer

Use persistent derived tables (PDTs) to materialize intermediate results. Option B is correct because Persistent Derived Tables (PDTs) materialize intermediate query results into physical tables in the underlying database (e.g., BigQuery). This avoids re-executing complex logic on every user interaction, drastically reducing query latency and cost. PDTs are a core Looker optimization for repeated, heavy transformations.

Answer

Use derived tables for all complex logic

Answer

Use native derived tables to leverage BigQuery's UDFs

Answer

Use materialized views in the underlying database

Question 10

A data analyst runs a query that joins two large tables on a high-cardinality column with many NULL values. Which action is most likely to resolve the error?

Accepted Answer

Add a WHERE clause to filter out NULLs from the join key.. Option C is correct because filtering out NULLs from the join key with a WHERE clause prevents the database from attempting to match NULL values, which cannot be equated in a standard SQL join (since NULL != NULL). This reduces the cardinality of the join operation and avoids potential performance degradation or errors caused by the large number of NULLs being processed in a high-cardinality column.

Answer

Use a DISTINCT clause on the join key.

Answer

Increase the query timeout setting.

Answer

Use a UNION ALL to combine tables.

Question 11

A company is designing a BigQuery data warehouse for sales analytics. They want to minimize query costs when aggregating daily sales by region and product. Which two methods are effective? (Select TWO).

Accepted Answer

Creating a materialized view with GROUP BY region, product, day. Option A is correct because a materialized view in BigQuery pre-computes and stores the results of the GROUP BY query on region, product, and day. When the underlying data changes, the materialized view is incrementally refreshed, so queries that match the view's aggregation are served directly from the stored results, avoiding full table scans and reducing query costs (bytes processed). This is ideal for recurring aggregation patterns like daily sales summaries.

Answer

Using a view that queries the raw data with WHERE clause

Answer

Storing pre-aggregated results in a separate table and updating nightly

Answer

Creating indexes on the raw table

Question 12

A BigQuery table is partitioned by ingestion time (pseudo column _PARTITIONTIME) and uses the default partition expiration of 90 days. A data engineer runs a DELETE statement to remove rows older than 100 days. Why does this query process more bytes than expected?

Accepted Answer

The DELETE statement must scan all partitions because it uses a condition that does not prune partitions.. Option D is correct because the DELETE statement uses a condition that does not reference the partitioning column (_PARTITIONTIME) in a way that allows partition pruning. Since the table is partitioned by ingestion time, BigQuery must scan all partitions to evaluate the filter, even though the condition logically targets rows older than 100 days. This results in processing more bytes than expected, as the default partition expiration of 90 days does not reduce the scan scope when the WHERE clause does not leverage the partitioning column.

Answer

The table is not partitioned; it is clustered.

Answer

The DELETE statement does not use a WHERE clause on a clustering column.

Answer

The DELETE statement filters on a custom timestamp column instead of _PARTITIONTIME.

Question 13

A financial company uses Cloud SQL for PostgreSQL to store transaction data. They need to create a materialized view that aggregates daily sales for a BI dashboard. The underlying transaction table is updated continuously. Which approach ensures the materialized view remains up to date without manual intervention?

Accepted Answer

Schedule a Cloud Function via Cloud Scheduler to run REFRESH MATERIALIZED VIEW periodically. Option C is correct because Cloud SQL for PostgreSQL does not support automatic materialized view refresh. The only way to keep a materialized view up to date without manual intervention is to schedule a periodic refresh using Cloud Scheduler to invoke a Cloud Function that executes the REFRESH MATERIALIZED VIEW command. This approach balances freshness with resource cost, as refreshing on every transaction would be too expensive.

Answer

Use BigQuery federated query to directly query the Cloud SQL table

Answer

Use a Cloud SQL read replica and create the materialized view on the replica

Answer

Add a trigger on the base table to refresh the materialized view on each update

Question 14

Refer to the exhibit. What is the likely cause of this error?

Accepted Answer

The query does not include WHERE clause with partition column. The error occurs because the query attempts to access a partitioned table without specifying the partition column in the WHERE clause. In Snowflake (the platform implied by PCDE context), querying a large partitioned table without a partition filter forces a full scan of all partitions, which can exceed resource limits or time out. The correct approach is to include the partition column in the WHERE clause to enable partition pruning.

Answer

The table is a view

Answer

The table is not partitioned

Answer

The user does not have permission to query the table

Question 15

Refer to the exhibit. The query used DATE_TRUNC(order_date, MONTH) as month. order_date is a TIMESTAMP column. What is the data type of the month column in the result?

Accepted Answer

TIMESTAMP. In BigQuery (the SQL engine for the PCDE exam), DATE_TRUNC with a TIMESTAMP input and MONTH granularity returns a TIMESTAMP value, not a DATE or DATETIME. The function truncates the timestamp to the first day of the month at 00:00:00 UTC, preserving the TIMESTAMP data type. Therefore, the month column in the result is of type TIMESTAMP.

Answer

STRING

Answer

DATE

Answer

DATETIME

PCDE Practice Test 30 — 15 Questions

Exhibit

PCDE Practice Test 30 — 15 Questions

Exhibit