PCDE Practice Test 31 — 15 Questions

Question 1

A startup is building a BI system on Cloud SQL (PostgreSQL) for small-to-medium datasets. The data warehouse includes a fact table 'sales_fact' with millions of rows and dimension tables. The BI team reports that 'sales_fact' queries are slow despite proper indexing. What design change would most likely improve performance?

Accepted Answer

Denormalize frequently joined dimension columns into the fact table. Denormalizing frequently joined dimension columns into the fact table reduces the number of JOIN operations required for BI queries. In PostgreSQL on Cloud SQL, even with proper indexing, JOINs between a large fact table and multiple dimension tables can cause significant overhead due to tuple reconstruction and buffer pool churn. By storing commonly accessed dimension attributes directly in the fact table, queries become single-table scans or index lookups, dramatically reducing query latency for small-to-medium datasets.

Answer

Use a read replica to offload queries

Answer

Switch to Cloud Spanner for better scalability

Answer

Add more indexes on every column used in WHERE clauses

Question 2

A data team uses BigQuery and wants to ensure data freshness for BI reports with low latency. Which three techniques can help achieve near-real-time updates? (Select THREE).

Accepted Answer

Use a live view that queries the source table directly. Option B is correct because a live view (also known as a logical view) queries the source table directly each time it is accessed, ensuring that BI reports always see the most current data without any materialization delay. This provides near-real-time freshness by avoiding periodic refresh cycles.

Answer

Create a scheduled query that rewrites the entire table every hour

Answer

Use BigQuery's BI Engine for caching

Question 3

A startup is building a BI stack on Google Cloud. They have moderate data volumes and need to run ad-hoc analytical queries and real-time dashboards. Which Google Cloud database service is most appropriate for this workload?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse designed for analytical queries and real-time dashboards. It supports ad-hoc SQL queries on large datasets with fast execution via its columnar storage and distributed query engine, making it ideal for BI workloads with moderate data volumes.

Answer

Cloud Spanner

Answer

Firestore

Answer

Cloud SQL

Question 4

A company runs near-real-time dashboards on BigQuery that query a table partitioned by day and clustered by user_id. The most common query filters on user_id and then aggregates sales over the last 7 days. However, many queries still scan full partitions. What is the most likely cause?

Accepted Answer

The table is not clustered on user_id, or the clustering expression does not match the filter.. Option D is correct because the most common cause of full partition scans despite partitioning by day and clustering by user_id is that the clustering expression does not match the filter predicate. In BigQuery, clustering only prunes blocks within a partition when the filter column exactly matches the clustering key; if the filter uses a different expression (e.g., a cast or function) or if clustering is not properly defined, BigQuery falls back to scanning the entire partition. This results in the described behavior where queries still scan full partitions even though the table is partitioned and clustered.

Answer

The dashboard is configured to refresh every 5 minutes, causing too many queries.

Answer

The table uses a wide-column schema with many repeated fields.

Answer

The table is partitioned by hour, not by day.

Question 5

A company uses BigQuery for BI reporting. They have a large table 'events' with nested and repeated fields (ARRAY<STRUCT>). Analysts often query unnested data, which is slow. What is the best practice to improve query performance without changing the source schema?

Accepted Answer

Create a materialized view that flattens the nested data. Option D is correct because a materialized view in BigQuery can precompute and store the results of an UNNEST operation on nested fields, significantly reducing query time for repeated flattening queries. Unlike a regular view, a materialized view persists the flattened data and is automatically refreshed, so analysts query pre-joined, pre-flattened results without altering the source schema. This directly addresses the performance issue while preserving the original nested structure for other use cases.

Answer

Create a view that unnests the data

Answer

Redesign the table to be flat

Answer

Use a subquery with UNNEST and cache the results

Question 6

A BI analyst needs to calculate a running total of sales by region over time in BigQuery. Which SQL window function should be used?

Accepted Answer

SUM(sales) OVER (PARTITION BY region ORDER BY date). Option B is correct because the SUM() window function with an ORDER BY clause in the OVER() clause computes a running total (cumulative sum) over the specified partition. In BigQuery, when you include ORDER BY inside a window function's OVER() clause, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces the running total for each region ordered by date.

Answer

RANK() OVER (PARTITION BY region ORDER BY date)

Answer

ROW_NUMBER() OVER (PARTITION BY region ORDER BY date)

Answer

COUNT(sales) OVER (PARTITION BY region ORDER BY date)

Question 7

A data analyst reports that a BI dashboard query on BigQuery is taking over 30 seconds to execute. The table is partitioned by date and clustered by customer_id. The query filters on a specific date range and aggregates sales by customer. What is the most likely cause of the slow performance?

Accepted Answer

The query filters on a date column that is not the partition column, causing a full table scan.. Option C is correct because the query filters on a specific date range, but the table is partitioned by date, so BigQuery can prune partitions to scan only the relevant ones. If the filter were on a column that is not the partition column, a full table scan would occur, causing slow performance. Since the table is partitioned by date and the query filters on a date range, partition pruning should work efficiently, making C the most likely cause only if the filter column is misidentified. However, the question states the table is partitioned by date and the query filters on a specific date range, so partition pruning should apply; the correct answer is actually A, as clustering on customer_id provides no benefit without a filter on that column, leading to a full scan of the clustered data.

Answer

The query does not include a filter on the clustering column, so clustering provides no benefit.

Answer

The query uses a LEFT JOIN that requires a broadcast join, increasing network overhead.

Answer

The table does not have a primary key, so BigQuery cannot use index scans.

Question 8

A financial services company uses BigQuery for BI reporting. They need to design a data model that ensures data consistency and avoids duplicate records in the fact table. Which three practices should they follow? (Choose three.)

Accepted Answer

Use the OVERWRITE partition option for incremental loads.. Option A is correct because using the OVERWRITE partition option for incremental loads ensures that only the specific partition being loaded is replaced, preventing duplicate records within that partition while preserving data in other partitions. This approach maintains data consistency by avoiding full table overwrites and is efficient for incremental updates in BigQuery.

Answer

Apply a unique constraint on the fact table.

Answer

Use a daily load job that replaces the entire table with WRITE_TRUNCATE.

Question 9

A data engineer is building a BI reporting layer in BigQuery. The source data includes JSON logs with nested fields. Analysts need to query nested arrays efficiently. Which approach is best?

Accepted Answer

Use SQL and UNNEST to directly query nested arrays.. Option A is correct because BigQuery natively supports nested and repeated fields via the UNNEST operator, which flattens arrays into rows for SQL-based querying. This approach leverages BigQuery's columnar storage and efficient array handling, allowing analysts to query nested arrays directly without data duplication or additional ETL, which is optimal for BI reporting performance.

Answer

Load the data into separate tables for each array.

Answer

Flatten all nested fields into separate tables.

Answer

Create a view that flattens the data.

Question 10

A data team uses BigQuery for ad-hoc BI queries. They have a table with 100 columns. Analysts often select many columns. The table is partitioned by event_date. Queries are slow and expensive. What two-step optimization should they implement? (Note: This is a single correct answer among four options that combine two steps.)

Accepted Answer

Cluster the table by commonly used columns and limit the selected columns in queries.. Clustering by commonly used columns organizes data within partitions so that queries scanning only those columns read fewer blocks, reducing bytes processed. Limiting selected columns in queries further reduces the data scanned by avoiding unnecessary column reads. Together, these two steps directly address the high cost and slow performance caused by scanning many columns across a large partitioned table.

Answer

Convert the table to an Avro format and use partitioned tables.

Answer

Partition by event_date and use column-level security.

Answer

Cluster the table by event_date and use SELECT *.

Question 11

A BI developer needs to write a query that calculates total sales by month for the current year. They create a Common Table Expression (CTE) to define monthly aggregates, then reference it in a final SELECT. What is the main benefit of using a CTE over a subquery in this scenario?

Accepted Answer

CTEs enhance query readability and maintainability.. Option D is correct because CTEs improve query readability and maintainability by allowing you to define a named temporary result set once and reference it multiple times in the final SELECT. In this scenario, the CTE clearly separates the monthly aggregation logic from the final output, making the query easier to understand and modify compared to nesting subqueries.

Answer

CTEs are always faster than subqueries.

Answer

CTEs reduce the amount of memory used by the query.

Answer

CTEs automatically cache results for subsequent queries.

Question 12

A financial institution uses Cloud SQL for MySQL to handle transaction processing. They need to generate daily BI reports that aggregate millions of transactions per account. The BI queries are CPU-intensive and degrade OLTP performance. What is the most effective solution?

Accepted Answer

Create a Cloud SQL read replica and run reports against it. Creating a Cloud SQL read replica allows you to offload BI reporting queries to a separate instance that replicates data from the primary using MySQL's asynchronous replication. This isolates the CPU-intensive aggregation queries from the OLTP workload, preventing performance degradation on the primary instance while still providing near-real-time data for reports.

Answer

Schedule reports during off-peak hours only

Answer

Use Cloud SQL's high availability configuration

Answer

Upgrade the primary instance to a higher machine type

Question 13

Refer to the exhibit. A BI analyst runs a query to get total sales for the last 7 days. The query filters on sale_date BETWEEN '2023-01-01' AND '2023-01-07'. What is the primary benefit of the partitioning defined in the table?

Accepted Answer

It reduces the amount of data scanned by pruning partitions.. Partitioning in BigQuery (and similar data warehouses) physically divides the table into segments based on the partition column (sale_date). When the query filters on sale_date BETWEEN '2023-01-01' AND '2023-01-07', the query engine can perform partition pruning, scanning only the partitions that match the date range instead of the entire table. This dramatically reduces the amount of data read, lowering query cost and improving performance.

Answer

It automatically creates indexes on sale_date.

Answer

It allows the query to use clustering.

Answer

It enables streaming inserts.

Question 14

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.. Option A is correct because 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 15

A database engineer is designing a data model for a BI dashboard that tracks daily sales by product category. The data source is a transactional database with a normalized schema. Which BigQuery feature should they use to update the fact table incrementally each day?

Accepted Answer

Scheduled queries with MERGE statements. Scheduled queries with MERGE statements allow incremental updates by inserting new rows and updating existing ones based on a unique key, such as date and product category. This avoids full table reloads, making it efficient for daily fact table refreshes from a normalized transactional source.

Answer

Streaming inserts

Answer

BigQuery Data Transfer Service

Answer

Load jobs with WRITE_TRUNCATE