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

Question 1

A company uses BigQuery to run business intelligence reports. The data engineer needs to implement a star schema for a sales data warehouse. Which THREE are best practices when designing the tables?

Accepted Answer

Use a primary key on fact tables to enforce uniqueness. Option B is correct because in BigQuery, fact tables should have a primary key to enforce uniqueness of each sales transaction, preventing duplicate rows that would skew aggregations like SUM or COUNT. BigQuery does not enforce primary keys natively, but defining them in the schema (e.g., using PRIMARY KEY constraint in DDL) allows the query engine to optimize joins and deduplication, especially when using MERGE statements. This ensures data integrity in the star schema.

Answer

Use natural keys in dimension tables for simplicity

Answer

Store pre-aggregated data in dimension tables

Question 2

A retail company stores sales transactions in BigQuery. They want to create a materialized view that aggregates daily sales by product category, but they need the view to refresh automatically within 5 minutes of new data being inserted. The source table is partitioned by transaction_date and has a streaming buffer. What should they do to ensure the materialized view refreshes quickly enough?

Accepted Answer

Set the materialized view's max_staleness interval to 5 minutes and allow relaxed consistency.. Option D is correct because setting the `max_staleness` interval on the materialized view to 5 minutes allows BigQuery to serve query results from the view even if the underlying base table's streaming buffer hasn't fully committed, as long as the data is within the staleness window. This enables the materialized view to reflect near-real-time data without waiting for the streaming buffer to fully materialize, meeting the 5-minute refresh requirement.

Answer

Set max_staleness on the base table to 5 minutes.

Answer

Disable streaming inserts and use batch loads only.

Answer

Increase the streaming buffer size on the base table.

Question 3

A financial services company uses BigQuery to run complex analytical queries on trading data. They notice that a particular query joining a large fact table (10 TB) with a small dimension table (100 MB) is slow. The fact table is partitioned by date and clustered by symbol. The dimension table is not partitioned. The query filters on a specific date range and a few symbols. Which optimization is MOST likely to improve query performance?

Accepted Answer

Enable automatic query rewriting to use clustering keys for pruning on the dimension table join.. Option B is correct because BigQuery's automatic query rewriting can leverage clustering keys from the fact table to prune the join, even though the dimension table is not clustered. When the query filters on a specific date range and symbols, BigQuery can use the fact table's clustering on symbol to skip irrelevant blocks during the join, reducing data scanned and improving performance. This optimization is automatic and does not require manual denormalization or repartitioning.

Answer

Denormalize the dimension table into the fact table.

Answer

Partition the dimension table by its primary key.

Answer

Cluster the dimension table on its primary key.

Question 4

A company is designing a BigQuery data model for a business intelligence dashboard that shows sales by region and product. The data is refreshed daily. Which schema design is MOST cost-effective and performant for this use case?

Accepted Answer

A star schema with a fact table for sales and separate dimension tables for region and product.. Option B is correct because a star schema with a fact table for sales and dimension tables for region and product is optimized for analytical queries in BigQuery. Option A is wrong because a flat table with all columns leads to higher storage costs and slower queries due to scanning unnecessary columns. Option C is wrong because a wide table with nested columns is better for hierarchical data, not for simple dimensional analysis. Option D is wrong because a normalized schema with many joins is not ideal for BI queries and increases complexity.

Answer

A table with nested repeated columns for regions and products within each sale.

Answer

A fully normalized schema with separate tables for each attribute.

Answer

A single flat table containing all sales, region, and product columns.

Question 5

A data engineer runs a BigQuery query that joins a large fact table with a small lookup table. The query processes 1 TB of data and takes 30 seconds. The engineer wants to reduce the amount of data processed. Which optimization technique is MOST effective?

Accepted Answer

Use a WITH clause to pre-filter the fact table before joining.. Option B is correct because pre-filtering the fact table with a WITH clause (CTE) reduces the amount of data scanned and processed before the join occurs. Since the fact table is large (1 TB), applying filters early minimizes the data shuffled and joined, directly reducing the bytes billed in BigQuery. This is a form of predicate pushdown that leverages BigQuery's columnar storage and dynamic query optimization.

Answer

Increase the number of slots available for the query.

Answer

Cluster the lookup table on the join key.

Answer

Materialize the lookup table as a separate table with the same data.

Question 6

A company uses Cloud SQL for PostgreSQL to store transactional data and BigQuery for analytics. They need to sync a subset of tables from Cloud SQL to BigQuery daily for BI reporting. The tables are updated incrementally (INSERT, UPDATE, DELETE). Which approach is MOST reliable and cost-effective?

Accepted Answer

Use Datastream to stream changes from Cloud SQL to BigQuery in near real-time.. Datastream is purpose-built for exactly this use case: it captures CDC (Change Data Capture) events from Cloud SQL for PostgreSQL (using the PostgreSQL logical replication slot and the pgoutput plugin) and streams them directly into BigQuery via a streaming ingestion pipeline. This approach handles INSERT, UPDATE, and DELETE operations reliably without custom code, and it is cost-effective because it avoids full table exports and leverages BigQuery's streaming buffer for near-real-time updates.

Answer

Write a custom cron job on App Engine to extract changes and load them into BigQuery.

Answer

Create BigQuery federated queries that directly read from Cloud SQL.

Answer

Export the Cloud SQL tables to Cloud Storage as CSV files daily, then load them into BigQuery.

Question 7

Which TWO of the following are valid ways to improve the performance of a BigQuery query that joins two large tables?

Accepted Answer

Apply WHERE clauses to filter each table before the join.. Option A is correct because applying WHERE clauses before the join (e.g., using subqueries or CTEs to pre-filter each table) reduces the amount of data shuffled and processed during the join phase. BigQuery's query engine can push down filters to the storage layer, minimizing the bytes read and improving performance significantly.

Answer

Use the 'JOIN EACH' clause.

Answer

Denormalize the tables into a single table.

Answer

Set the query option 'USE_CACHE=TRUE'.

Question 8

Which THREE of the following are best practices for designing BigQuery tables for business intelligence reporting?

Accepted Answer

Partition tables by a date or timestamp column used in WHERE clauses.. Partitioning tables by a date or timestamp column used in WHERE clauses allows BigQuery to prune partitions, scanning only the relevant data instead of the entire table. This reduces query costs and improves performance, making it a best practice for BI reporting where queries often filter by time ranges.

Answer

Store data in many small tables to reduce the amount of data scanned per query.

Answer

Normalize data to reduce data redundancy.

Question 9

A company uses BigQuery for BI reporting with a star schema. The fact table 'sales' is partitioned by date and clustered by 'product_id'. The dimensions 'product' and 'customer' are updated nightly via merge statements. Recently, a report that joins 'sales' with 'product' on 'product_id' and filters on sale_date for the last 7 days started timing out. The query plan shows a 'SCAN' of the entire 'product' table. Which optimization should be applied to improve performance?

Accepted Answer

Cluster the 'product' table on 'product_id'. Option D is correct because clustering the 'product' table on 'product_id' physically co-locates rows with the same product_id into the same blocks, drastically reducing the amount of data scanned when the report joins on that column. The query plan's full SCAN of the 'product' table indicates that BigQuery must read every row, even though only a subset of products are referenced by the last 7 days of sales. Clustering on product_id enables block-level pruning, so only the relevant blocks are read, eliminating the full table scan.

Answer

Partition the 'product' table by 'product_id'

Answer

Partition the 'sales' table by 'product_id' instead of date

Answer

Remove clustering from the 'sales' table

Question 10

A data engineer is designing a BI solution in BigQuery for a retail chain. They need to support queries that aggregate sales by store, product, and date across millions of transactions. The data is loaded in near real-time from Cloud Pub/Sub. Which table design provides the best balance of query performance and cost?

Accepted Answer

Partition by date, cluster by store_id and product_id. Option B is correct because partitioning by date enables BigQuery to prune entire partitions when querying by date range, which is the most common filter in sales aggregation queries. Clustering on store_id and product_id further reduces the data scanned within each partition by colocating rows with similar store and product values. This design minimizes both query cost (bytes billed) and latency, while supporting near-real-time ingestion from Pub/Sub without requiring table rewrites.

Answer

Partition by store_id, cluster by product_id

Answer

Unpartitioned table with clustering on store_id and product_id

Answer

Use materialized views with aggregation on store_id, product_id, and date

Question 11

A company uses BigQuery to generate daily sales reports. The query aggregates sales by product category and region. The table 'sales_raw' is 500 GB and is updated every hour with new transactions. The report runs slowly. What is the most cost-effective method to improve query performance without changing the existing table schema?

Accepted Answer

Create a materialized view that aggregates sales by product category and region. Option C is correct because a materialized view in BigQuery pre-computes and stores the aggregated results of the query, allowing subsequent queries to read the pre-aggregated data instead of scanning the entire 500 GB 'sales_raw' table. This reduces both the data scanned and the query execution time, and it is automatically refreshed when the base table is updated (every hour), making it cost-effective as you only pay for the bytes used by the materialized view and the incremental refreshes, not for full table scans.

Answer

Partition the table by product category

Answer

Create a separate summary table using scheduled queries

Answer

Cluster the table by region

Question 12

A financial institution uses BigQuery for BI reporting. They have a table 'transactions' (10 TB) partitioned by transaction_date and clustered by customer_id. A common report filters on customer_id and last 30 days. The report is slow. Which change would most improve query performance for this specific report?

Accepted Answer

Add clustering on transaction_date in addition to customer_id. Option C is correct because adding clustering on transaction_date alongside customer_id improves query performance for the specific report that filters on both customer_id and the last 30 days. BigQuery uses clustering to sort data within partitions, so clustering by transaction_date ensures that within each partition, the rows for the last 30 days are colocated, reducing the amount of data scanned. This complements the existing partition pruning by further narrowing the scan to relevant blocks.

Answer

Change partition column to customer_id

Answer

Remove clustering and rely only on partitioning

Answer

Manually recluster the table daily

Question 13

A retail company uses BigQuery to analyze sales data. They need to create a weekly report showing total sales per product category for the last 4 weeks, but the query is taking too long and exceeding slot resources. The sales table has over 2 billion rows and is partitioned by date. Which design change would most improve query performance and reduce slot consumption?

Accepted Answer

Cluster the table by product_category within the existing date partitions.. Option B is correct because clustering the table by product_category within the existing date partitions organizes the data physically so that queries filtering or grouping by product_category can skip irrelevant blocks. This reduces the amount of data scanned and the slot consumption, directly addressing the performance issue without requiring additional resources.

Answer

Increase the number of available slots in the reservation.

Answer

Create a materialized view that pre-aggregates sales by category and date.

Answer

Partition the table by product_category instead of date.

Question 14

A financial services company needs to design a BigQuery data model for real-time fraud detection. Data arrives from multiple streaming sources and must be joined with historical customer profiles (10 TB) and transaction lookup tables (500 GB). Which TWO design considerations are most important to minimize query latency and cost?

Accepted Answer

Use time-based partitioning on the historical customer table and cluster on customer_id.. Option A is correct because time-based partitioning on the historical customer table (10 TB) allows BigQuery to prune irrelevant partitions during queries, reducing the amount of data scanned and thus lowering cost and latency. Clustering on customer_id further optimizes joins with streaming data by colocating related rows, minimizing shuffle overhead.

Answer

Schedule a nightly script to recluster tables based on query patterns.

Answer

Use a single table for all streaming data without partitioning to avoid partition management overhead.

Answer

Denormalize all historical and lookup data into a single wide table.

Question 15

Refer to the exhibit. Given the table definition and two queries, which statement about query performance is correct?

Accepted Answer

Query 1 will scan less data than Query 2 because it uses both partition pruning and clustering.. Query 1 uses both partition pruning (filtering on the partition key `event_date`) and clustering (filtering on the clustering column `user_id`), allowing it to skip irrelevant partitions and scan only the specific rows within the target partition. Query 2 uses only partition pruning on `event_date` but lacks a clustering filter, so it must scan all rows in the partition. Therefore, Query 1 scans less data than Query 2.

Answer

Query 2 will scan less data than Query 1 because it only needs to read one partition.

Answer

Query 1 will scan the same amount of data as Query 2 because both use partition pruning.

Answer

Both queries will perform a full table scan because the table is partitioned.