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

Question 1

A company uses BigQuery for BI reporting. They have a table 'orders' with columns: order_id, customer_id, order_date, amount, status. The BI team frequently runs queries that filter on order_date and group by customer_id to compute total sales per customer. Which partitioning and clustering strategy optimizes query performance and cost?

Accepted Answer

Partition by order_date, cluster by customer_id. Option D is correct because partitioning by order_date allows BigQuery to prune partitions for queries filtering on order_date, reducing the amount of data scanned. Clustering by customer_id organizes data within each partition so that GROUP BY customer_id queries can efficiently read only relevant blocks, minimizing shuffle and cost. This combination directly aligns with the BI team's query pattern of filtering by date and aggregating by customer.

Answer

Partition by order_date, cluster by status

Answer

Do not partition, cluster by customer_id

Answer

Partition by customer_id, cluster by order_date

Question 2

A retail company uses BigQuery to store sales data. The 'sales' table has 10 billion rows and is partitioned by transaction_date (daily). The BI dashboard runs a query that aggregates sales by product_category for the last 30 days. The query is slow and expensive. Which improvement is most effective?

Accepted Answer

Cluster the table on product_category. Option A is correct because clustering the table on product_category organizes the data within each daily partition by that column, allowing BigQuery to use block-level pruning to skip irrelevant blocks when filtering or aggregating by product_category. This directly reduces the amount of data scanned for the 30-day aggregation query, improving both performance and cost.

Answer

Change partitioning to monthly

Answer

Denormalize the product_category into the sales table

Answer

Use a materialized view with aggregation on product_category

Question 3

A company is designing a data warehouse for BI. They need to support both detailed transaction analysis and high-level aggregated reports. Which schema design best balances storage and query performance?

Accepted Answer

Star schema with fact and dimension tables. The star schema is the optimal design for balancing storage and query performance in a BI data warehouse because it separates transactional data into fact tables (for detailed analysis) and dimension tables (for context), enabling fast aggregations via star joins while avoiding the storage overhead of full denormalization. This structure directly supports both granular transaction queries and high-level rollups without the complexity or performance penalty of snowflake schemas or the redundancy of fully denormalized tables.

Answer

Fully denormalized single table

Answer

Wide column store with no schema

Answer

Snowflake schema with normalized dimensions

Question 4

A BI team runs a daily query on a BigQuery table 'events' partitioned by event_date. The query filters on event_date = CURRENT_DATE() and counts rows by event_type. The query is slow. Upon review, the table has 500 partitions but clustering is not set. Which action reduces query cost and latency?

Accepted Answer

Add clustering on event_type. Adding clustering on `event_type` physically co-locates rows with the same event type within each partition. This allows BigQuery to use block-level pruning when reading data, drastically reducing the number of bytes scanned for the COUNT(*) GROUP BY query. Since the query already filters on a single partition (`event_date = CURRENT_DATE()`), the performance bottleneck is scanning all rows in that partition; clustering eliminates that overhead without changing the table's structure or retention.

Answer

Recreate the table with only the last 30 days of data

Answer

Use a wildcard table for daily ingestion

Answer

Increase the partition expiration to 365 days

Question 5

A company stores sensor data in BigQuery. They have a table 'sensor_readings' with columns: sensor_id, reading_time, value. The table is partitioned by reading_time (hourly) and clustered by sensor_id. A BI query aggregates average value per sensor for the last week. The query still scans many bytes. What is the most likely cause?

Accepted Answer

Partition granularity is too fine for the query range. Option D is correct because the query scans a full week of data (168 hourly partitions), and each partition must be read entirely even though only a subset of sensors may be active. Hourly partitioning over a 7-day range means the query engine must scan all 168 partitions, which can result in a large number of bytes being processed. Clustering on sensor_id helps within each partition but does not reduce the number of partitions scanned; the fine granularity of hourly partitioning is the primary cause of excessive bytes scanned.

Answer

The query uses SELECT * instead of specific columns

Answer

Clustering on sensor_id is ineffective

Answer

The table is not using columnar storage

Question 6

Which TWO actions improve query performance and reduce cost in BigQuery for BI workloads?

Accepted Answer

Cluster tables on columns used in GROUP BY. Clustering tables on columns used in GROUP BY improves query performance by physically co-locating rows with similar values, reducing the amount of data scanned during aggregation. Partitioning on columns frequently used in WHERE clauses allows BigQuery to prune entire partitions from the scan, directly reducing both cost (bytes billed) and query execution time. These two optimizations are specifically recommended for BI workloads where repeated, selective queries are common.

Answer

Load data using batch loads instead of streaming

Answer

Store data in CSV format

Answer

Use SELECT * in all queries

Question 7

Which THREE are valid considerations when designing BigQuery tables for BI reporting?

Accepted Answer

Use partitioning on date columns to reduce query cost. Option C is correct because partitioning BigQuery tables by date columns (e.g., using _PARTITIONTIME or a DATE/TIMESTAMP column) allows the query engine to prune entire partitions during query execution. This significantly reduces the amount of data scanned, directly lowering query costs (since BigQuery charges per byte processed) and improving performance for time-range filters.

Answer

Use nested and repeated fields to avoid JOINs

Answer

Create indexes on frequently queried columns

Question 8

The exhibit shows query metadata for a query that scans 10 GB. Given the table is 100 GB and partitioned by hire_date, why did the query scan 10 GB and not less?

Accepted Answer

The filter on hire_date is not selective enough to prune most partitions. Option A is correct because partition pruning in Databricks (and Spark SQL) depends on the selectivity of the filter predicate. If the filter on `hire_date` matches a large number of partitions (e.g., filtering on a range that covers 10 GB out of 100 GB), the query scans exactly those partitions. The question states the table is 100 GB and partitioned by `hire_date`, so a 10 GB scan implies the filter pruned 90 GB of partitions but was not selective enough to reduce the scan further—e.g., the predicate may be a broad range or lack a precise equality condition.

Answer

Clustering on department is not being used because the query has ORDER BY

Answer

The query uses GROUP BY, which forces a full table scan

Answer

The table is not clustered properly

Question 9

The exhibit shows IAM policy for a BigQuery dataset. The BI team reports they can query tables but cannot create views. What is the missing role?

Accepted Answer

roles/bigquery.dataEditor. The BI team can query tables but cannot create views, which requires write access to the dataset. The `roles/bigquery.dataEditor` role grants permissions to read, create, update, and delete datasets, tables, and views, including the `bigquery.tables.create` and `bigquery.tables.update` permissions necessary for view creation. The existing query capability indicates they have at least `roles/bigquery.dataViewer`, but view creation demands the additional write permissions provided by `dataEditor`.

Answer

roles/bigquery.admin

Answer

roles/bigquery.metadataViewer

Answer

roles/bigquery.user

Question 10

A retail company uses BigQuery to store sales transactions. The BI team needs to create a monthly customer lifetime value (CLV) report that aggregates purchase history across multiple tables. Which BigQuery feature should they use to define the data structure for this report?

Accepted Answer

Create a view that joins and aggregates the tables. Option B is correct because a view in BigQuery allows the BI team to define a logical data structure that joins and aggregates multiple tables without storing the results. This ensures the monthly CLV report always reflects the latest data, as views are re-evaluated at query time, which is ideal for recurring reports that need up-to-date aggregations.

Answer

Create a materialized view with the aggregation query

Answer

Create an external table pointing to the raw data files

Answer

Create a new table to store the aggregated data using INSERT SELECT