PCDE Practice Test 19 — 15 Questions

Question 1

A company runs a retail BI dashboard on BigQuery. The fact_sales table is partitioned by DAY and clustered by product_id. The table is 10 TB. Recently, analysts complain that queries filtering on a specific product_id and a month of data take over 10 minutes. The query uses a subquery to find top products. What should the engineer do?

Accepted Answer

Create a materialized view for the subquery.. Option A is correct because creating a materialized view for the subquery that identifies top products pre-computes and stores the results, which are incrementally refreshed by BigQuery. This avoids re-scanning the entire 10 TB fact_sales table each time the query runs, drastically reducing query time for the analysts' frequent filtering on product_id and a month of data.

Answer

Add an ORDER BY product_id to the subquery.

Answer

Change partition type to HOUR.

Answer

Re-cluster the table with product_id as the first clustering column and date as the second.

Question 2

You are a database engineer at a retail company. The company uses BigQuery for BI, with a fact table 'sales_fact' partitioned by order_date and containing 100 million rows. There is a dimension table 'products' with 10,000 rows. The BI team reports that the following query takes over 5 minutes to run: SELECT p.category, SUM(s.amount) FROM sales_fact s JOIN products p ON s.product_id = p.product_id WHERE s.order_date >= '2024-01-01' AND s.order_date < '2024-04-01' GROUP BY p.category. The table 'products' is not partitioned or clustered. 'sales_fact' is partitioned by order_date but not clustered. The query only scans 3 months of data (about 25 million rows). However, the join seems slow. What is the most likely cause and what single action would you take to improve performance?

Accepted Answer

Cluster the 'sales_fact' table on product_id. The query is slow because the join on `product_id` requires shuffling 25 million rows from `sales_fact` across nodes to match with `products`. Clustering `sales_fact` on `product_id` co-locates rows with the same `product_id` within each partition, reducing shuffle overhead and enabling more efficient broadcast or hash joins in BigQuery. This is the most impactful single action because it directly addresses the join performance bottleneck without changing the query logic.

Answer

Use a cross-join to avoid the join

Answer

Add an index on 'products.product_id'

Answer

Partition the 'products' table

Question 3

An e-commerce company uses BigQuery for BI. They have a large orders table with columns: order_id, customer_id, order_date, amount, status. Queries frequently aggregate total amount by customer and month. The current table is not partitioned. Users complain about high costs. The table is 2 TB and grows by 50 GB daily. Which action reduces query costs most?

Accepted Answer

Create a materialized view that aggregates by customer and month.. Option C is correct because a materialized view pre-aggregates the total amount by customer and month, eliminating the need to scan the full 2 TB table for every query. This drastically reduces the bytes processed per query, directly lowering BigQuery costs. Since the table grows by 50 GB daily, the materialized view incrementally updates, ensuring fresh results without reprocessing historical data.

Answer

Partition the table by order_date and cluster by customer_id.

Answer

Use a wildcard table with daily shards.

Answer

Set a maximum bytes billed limit on the project.

Question 4

You are responsible for a Cloud SQL for MySQL instance that supports a content management system (CMS). The application frequently performs SELECT queries with ORDER BY and LIMIT. Recently, the response time for these queries has increased. The database has 4 vCPUs and 15 GB memory. You check the slow query log and find many queries that are taking over 1 second. The 'rows_examined' is much higher than 'rows_sent'. The EXPLAIN plan shows 'Using filesort' and 'Using temporary'. There is currently an index on the column used in the WHERE clause but not on the ORDER BY columns. The table has 5 million rows. What should you do to improve query performance?

Accepted Answer

Add a composite index on the columns used in the WHERE clause and the ORDER BY clause.. Option D is correct because adding a composite index on the columns used in the WHERE clause and the ORDER BY clause allows MySQL to avoid the expensive 'Using filesort' and 'Using temporary' operations. With a covering index, the database can retrieve rows in the required order directly from the index, eliminating the need to sort the result set after filtering. This dramatically reduces 'rows_examined' and improves query response time for SELECT queries with ORDER BY and LIMIT.

Answer

Increase the buffer pool size to 80% of memory.

Answer

Disable the query cache to reduce overhead.

Answer

Remove the ORDER BY clause and sort the results in application code.

Question 5

A Cloud Spanner instance is experiencing high CPU utilization (above 80%) on multiple nodes. The database is used for an e-commerce application with a high volume of read-write transactions. The application uses the googlesql dialect and runs typical OLTP queries. You have already reviewed the query performance and found that most queries are efficient. Which initial step should you take to reduce CPU utilization?

Accepted Answer

Use the INFORMATION_SCHEMA.INDEXES view to identify and drop unused or redundant secondary indexes.. Option A is correct because high CPU utilization in Cloud Spanner often stems from excessive secondary index maintenance during write operations. Dropping unused or redundant indexes reduces the write amplification and CPU overhead per transaction, directly lowering CPU usage without compromising query performance, as the queries are already efficient.

Answer

Adjust the application to use staleness of 5 seconds for reads to reduce CPU for read-write transactions.

Answer

Increase the number of nodes in the Spanner instance to spread the CPU load.

Answer

Create a separate read-only replica pool to offload read traffic.

Question 6

You are managing a Spanner instance for a global financial application. The database has a table `transactions` with columns `transaction_id` (INT64), `user_id` (INT64), `amount` (FLOAT64), `timestamp` (TIMESTAMP), and `region` (STRING). The table is interleaved with a parent table `users`. Recently, you observed that point-read queries by `transaction_id` are taking over 100ms on average, whereas they used to take under 10ms. The instance CPU utilization is below 40%, and there are no contention issues. The `transactions` table has a primary key `(user_id, transaction_id)`. Queries filter on `transaction_id` only, without specifying `user_id`. Which optimization should you implement to improve point-read latency?

Accepted Answer

Create a secondary index on `transaction_id` to enable efficient key-based lookups.. Point-read queries by `transaction_id` are slow because the primary key is `(user_id, transaction_id)`, so without `user_id`, Spanner cannot directly locate the split (tablet) and must perform a full table scan or a less efficient lookup. Creating a secondary index on `transaction_id` allows Spanner to use that index for key-based lookups, reducing latency to under 10ms by enabling direct access to the specific split via the index's key.

Answer

Add a secondary index on `user_id` to help narrow down the search.

Answer

Use a Spanner query hint to force a specific index scan.

Answer

Change the primary key to `(transaction_id, user_id)` to enable direct access by transaction_id.

Question 7

A large e-commerce platform uses BigQuery for business intelligence. They have a fact table `orders` (10 TB, partitioned by order_date, clustered by customer_id) and a dimension table `customers` (2 TB, not partitioned, not clustered). The BI team runs a daily dashboard query that joins these tables on customer_id and filters on order_date = CURRENT_DATE() and customer_country = 'US'. The query currently scans the full `customers` table and 2 GB of the `orders` table, taking 30 seconds. The business wants to reduce cost and latency. The `customers` table has 500 million rows and is updated incrementally every hour. Which action will most effectively reduce the amount of data scanned and query time?

Accepted Answer

Cluster the `customers` table on customer_id.. Clustering the `customers` table on `customer_id` will physically co-locate rows with the same `customer_id`, allowing the query to use block-level pruning when joining with the filtered `orders` table. Since the query filters `orders` by `order_date = CURRENT_DATE()` (2 GB scanned) and then joins on `customer_id`, BigQuery can skip reading most of the `customers` table if it is clustered on the join key, drastically reducing the 2 TB full scan and lowering both cost and latency.

Answer

Denormalize customer country and other attributes into the `orders` table.

Answer

Create a materialized view that joins `orders` and `customers` on customer_id.

Answer

Partition the `customers` table by customer_id.

Question 8

A financial services company is designing a Cloud Spanner schema for a trading system. They have two main entities: 'accounts' and 'transactions'. Each account has many transactions, and queries almost always retrieve transactions for a specific account. Which TWO schema design strategies should they employ?

Accepted Answer

Ensure the primary key of transactions includes the account_id as the first part.. Option B is correct because Cloud Spanner distributes rows across splits based on the primary key prefix. By making `account_id` the first part of the transactions table primary key, all transactions for a given account are co-located, enabling efficient range scans and point lookups without cross-node shuffling.

Answer

Use a secondary index on transactions.account_id.

Answer

Define a foreign key constraint from transactions to accounts.

Answer

Store transactions as a JSON array of repeating fields within the account record.

Question 9

An analyst writes a SQL query that joins a fact table with multiple dimension tables. The query runs slowly due to shuffling. Which optimization technique should be applied?

Accepted Answer

Cluster the fact table on the dimension join keys.. Shuffling occurs when data must be redistributed across nodes during joins, often because the join keys are not co-located. Clustering the fact table on the dimension join keys physically co-locates rows with the same join key values, minimizing data movement during the join. This is a direct optimization for shuffle-heavy workloads in distributed SQL engines like Spark SQL or Hive.

Answer

Use a subquery in the FROM clause to pre-aggregate.

Answer

Use a LIMIT clause to restrict rows.

Answer

Use a window function to precompute values.

Question 10

A BI team is designing a BigQuery table for a sales dashboard that queries daily sales by product category and region. The dashboard often filters on a specific date range and a specific region. Which combination of partitioning and clustering should be used?

Accepted Answer

Partition by date, cluster by region. Partitioning by date (e.g., on a DATE or TIMESTAMP column) allows BigQuery to prune entire partitions when the dashboard filters on a specific date range, reducing the amount of data scanned. Clustering by region then sorts the data within each partition by region, enabling efficient block-level pruning when the dashboard filters on a specific region. This combination optimizes both the date range and region filters, which are the most common query patterns for this sales dashboard.

Answer

Partition by region, cluster by date

Answer

Use only clustering on date and region without partitioning

Answer

Partition by month, cluster by date

Question 11

A company uses BigQuery for BI. They need to create a table that stores daily sales data with millions of rows. The query pattern is to aggregate sales by month for specific product categories. Which table design is most cost-effective and performant?

Accepted Answer

Partitioned table by date with clustering on product_category. Partitioning by date allows BigQuery to prune entire partitions when querying monthly aggregates, drastically reducing the data scanned. Clustering on product_category further organizes data within each partition, enabling efficient block-level pruning for category filters. This combination minimizes both cost (bytes billed) and query latency for the described workload.

Answer

Non-partitioned table with clustering on product_category

Answer

Non-partitioned, non-clustered table with manual sharding by date

Answer

Partitioned table by product_category with clustering on date

Question 12

A data analyst runs a query joining several large tables and gets 'Resources exceeded' error. They need to reduce memory usage without changing the query logic. What should they do?

Accepted Answer

Use a subquery to pre-aggregate the largest table before joining. Option A is correct because pre-aggregating the largest table in a subquery reduces the amount of data that needs to be shuffled and joined in memory. In BigQuery, this minimizes the bytes processed and the memory footprint of the join operation, directly addressing the 'Resources exceeded' error without altering the overall query logic.

Answer

Use APPROX_COUNT_DISTINCT for counting distinct values

Answer

Increase the slot reservation

Answer

Use SELECT * in the subquery to ensure all columns are available

Question 13

A company tracks customer demographics that change over time (e.g., address). They need to maintain historical accuracy in BI reports. Which approach correctly implements a Type 2 slowly changing dimension?

Accepted Answer

Add effective start and end date columns for each dimension attribute. Option B is correct because Type 2 SCD uses start and end dates to track effective periods, allowing queries to join based on the snapshot date. Option A is wrong because overwriting loses history. Option C is wrong because an append-only log requires complex queries to get current snapshot. Option D is wrong because a single column storing only current value loses history.

Answer

Store only the current value and rely on the fact table's timestamp to infer history

Answer

Store only the current value in the dimension table and use an audit log for changes

Answer

Overwrite the old value with the new value

Question 14

A BI manager needs to restrict access to sensitive sales data so that salespeople can only see their own region's data. Which BigQuery feature should be used to implement row-level security without duplicating tables?

Accepted Answer

Create an authorized view that uses SESSION_USER() in a WHERE clause to filter rows. Option C is correct because an authorized view with SESSION_USER() in a WHERE clause dynamically filters rows based on the caller's identity, providing row-level security without duplicating tables. This approach leverages BigQuery's ability to share a single view with different users, each seeing only their authorized subset of data, which aligns with the requirement to restrict salespeople to their own region's data.

Answer

Use column-level security to hide sensitive columns

Answer

Use BigQuery row-level access policies

Answer

Create separate IAM roles for each region

Question 15

A company is designing a star schema for a BI dashboard that tracks sales performance. The dashboard needs to aggregate sales by product, store, and date. Which schema design is most appropriate?

Accepted Answer

Create a fact table with foreign keys to dimension tables for product, store, and date. Option C is correct because a star schema uses a central fact table with foreign keys to dimension tables, which is optimal for BI aggregation queries. Option A is wrong because a single wide table with all attributes leads to data redundancy and slower queries. Option B is wrong because a fully normalized schema (e.g., snowflake) introduces extra joins that can slow BI queries. Option D is wrong because storing data as JSON arrays in a single table is not suitable for efficient SQL aggregation.

Answer

Store all data in a single table using nested JSON arrays for product and store details

Answer

Create a single wide table with all attributes (product, store, date, sales)

Answer

Use a fully normalized snowflake schema with separate tables for each level of hierarchy