PCDE Define data structures and implement SQL for Business Intelligence Practice Test 10 — 15 Questions

Question 1

A BI team in a large enterprise uses Looker connected to BigQuery. The data model has a primary table 'sales_fact' with billions of rows and multiple dimensions. The team notices that Looker queries often time out. Which approach would most likely resolve this without changing the data model?

Accepted Answer

Create a materialized view in BigQuery for the most common aggregations. Using Looker's persistent derived tables (PDTs) can pre-aggregate data and speed up dashboard queries.

Answer

Request Google Support to increase BigQuery timeout

Answer

Increase BigQuery slot capacity

Answer

Switch Looker to use SQL Runner only

Question 2

Which TWO of the following are best practices when designing data structures for business intelligence in BigQuery?

Accepted Answer

Partition tables on a column that aligns with common filter criteria. Partitioning tables on a column that aligns with common filter criteria (e.g., a date or timestamp column) allows BigQuery to prune partitions during query execution, drastically reducing the amount of data scanned and improving query performance and cost efficiency. This is a core best practice for optimizing BI workloads in BigQuery.

Answer

Store raw logs directly in fact tables without any aggregation

Answer

Use NULLable columns extensively to save storage

Answer

Use a single wide table for all data to simplify schema

Question 3

Which THREE of the following SQL techniques are commonly used to improve BI query performance in BigQuery?

Accepted Answer

Avoid JOINs by storing all relevant data in a single table. Option B is correct because denormalizing data into a single table avoids expensive JOIN operations, which in BigQuery can cause significant performance degradation due to shuffling and data redistribution across slots. By storing all relevant data in one table, you reduce the need for large-scale data shuffling, leading to faster query execution and lower slot consumption.

Answer

Select all columns using SELECT * to avoid missing data

Answer

Use self-joins to compare rows within the same table

Question 4

Which TWO of the following are valid approaches when troubleshooting a slow BI query in BigQuery that includes a complex JOIN between a large fact table and multiple dimension tables?

Accepted Answer

Ensure the fact table is clustered on the join key. Option A is correct because clustering on the join key in BigQuery physically co-locates rows with the same key value within the same block, reducing the amount of data scanned during the JOIN. This is especially effective for large fact tables, as it minimizes the need to shuffle data across slots, directly improving query performance.

Answer

Split the fact table into multiple smaller tables by region

Answer

Move the data to Cloud SQL for faster joins

Answer

Add indexes on the join columns

Question 5

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 6

You are a cloud database engineer for a financial services firm. The firm uses Cloud SQL for PostgreSQL to support a BI reporting tool. The main table 'transactions' has 500 million rows and is growing daily. Reports often run aggregations over date ranges and group by account_id. The 'transactions' table has indexes on date and account_id separately. Despite these indexes, the reporting queries are slow, often taking over 30 minutes. The database is deployed on a high-memory machine with 32 vCPUs and 256 GB RAM. You notice that the queries perform sequential scans instead of using indexes. What is the most likely reason, and what single change would you make to improve performance?

Accepted Answer

Create a composite index on (date, account_id). The correct answer is B because the reporting queries filter by date ranges and group by account_id, but the existing separate indexes on date and account_id cannot be combined efficiently for both conditions. PostgreSQL's query planner often chooses a sequential scan over using two separate indexes because it estimates that reading the entire table is cheaper than the bitmap scan overhead of combining them. A composite index on (date, account_id) allows the database to directly locate rows matching the date range and then access them in account_id order, eliminating the need for a separate sort or join step.

Answer

Partition the table by date using PostgreSQL declarative partitioning

Answer

Increase the shared_buffers setting to 128 GB

Answer

Disable sequential scans by setting enable_seqscan = off

Question 7

You are a database engineer for an e-commerce company. The company uses BigQuery for its BI and analytics. The data pipeline stages raw event data into a table 'raw_events' with columns: event_id, user_id, event_time, event_type, and a JSON string 'event_data'. The BI team wants to query this data for user behavior analysis, but the JSON parsing makes queries slow. They need to perform frequent queries that extract specific fields from the JSON and filter by event_time. The table 'raw_events' is not partitioned and has 2 billion rows. What is the most effective single step to improve query performance and reduce cost?

Accepted Answer

Partition the table on event_time and cluster on event_type. Partitioning the table on event_time allows BigQuery to prune entire partitions when queries filter by event_time, drastically reducing the amount of data scanned. Clustering on event_type further organizes data within each partition, enabling block-level pruning for queries that filter or aggregate by event_type. This combination directly addresses the slow JSON parsing and high cost by minimizing scanned bytes, which is the most effective single step for a 2-billion-row table.

Answer

Create a view that extracts JSON fields into columns

Answer

Increase BigQuery slots to maximum

Answer

Use a materialized view to precompute common queries

Question 8

A company is designing a BigQuery data warehouse for BI dashboards. They have a fact table with billions of rows and need to optimize query performance for common filters on date and customer_id. Which table design strategy is most effective?

Accepted Answer

Use a partitioned table on date with clustering on customer_id.. Option D is correct because partitioning the table on `date` allows BigQuery to prune entire partitions when filtering by date, drastically reducing the data scanned. Clustering on `customer_id` then sorts data within each partition, enabling block-level pruning for queries that filter on `customer_id`. This combination minimizes both I/O and cost for the described BI workload.

Answer

Use a clustered table on date only.

Answer

Use a non-partitioned table with indexing on customer_id.

Answer

Use a materialized view that aggregates by date.

Question 9

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 10

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 11

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 12

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 13

A healthcare company needs to run BI queries on patient data. The table is in BigQuery and contains 5 billion rows. Queries often filter on patient_id and date. But the table is not partitioned or clustered. Analysts run queries that scan the entire table. The data is updated daily. What is the most cost-effective way to improve performance?

Accepted Answer

Partition by date and cluster by patient_id.. Partitioning by date (e.g., ingestion or event date) allows BigQuery to prune entire partitions when queries filter on date, drastically reducing the data scanned. Clustering by patient_id within each partition further organizes the data so that queries filtering on patient_id can skip irrelevant blocks via block-level metadata. Together, this minimizes bytes billed and improves query performance without requiring table redesign or additional storage costs.

Answer

Partition the table by patient_id.

Answer

Use a view that only selects recent data.

Answer

Cluster the table by date.

Question 14

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 15

A financial company runs BI queries on a BigQuery table that is partitioned by ingestion time. The table is 1 TB and receives streaming inserts every minute. Analysts query the last 24 hours of data. The queries are slow. The table is clustered by transaction_id. What is the likely cause?

Accepted Answer

The cluster column is not used in queries.. Option A is correct because queries likely filter on the time range (last 24 hours) but not on transaction_id, so the clustering key is not used; clustering only helps when the cluster column is in the filter. Option B (streaming buffer) is not a cause because data is committed quickly. Option C (partition expiration) is not mentioned. Option D (SELECT *) may affect but not primary cause.

Answer

Streaming buffer causes delays.

Answer

Queries use SELECT *.

Answer

Partition expiration is set too short.