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

Question 1

A company is migrating their on-premises data warehouse to BigQuery for BI. They have a fact table with billions of rows and many dimension tables. The current queries perform well in the on-prem system but are slow in BigQuery. The queries contain multiple JOINs and subqueries. Which optimization should they implement first?

Accepted Answer

Denormalize the dimension tables into the fact table.. Denormalizing dimension tables into the fact table is the most impactful first optimization because it eliminates the need for expensive JOIN operations across billions of rows. In BigQuery, JOINs on large fact tables with multiple dimension tables can cause significant data shuffling and increased slot consumption, whereas denormalization reduces query complexity and leverages BigQuery's columnar storage and compression more efficiently. This directly addresses the root cause of slow performance in a BI workload where subqueries and JOINs are prevalent.

Answer

Use clustering on all join keys.

Answer

Use BigQuery's automatic query rewriting.

Answer

Convert subqueries to CTEs.

Question 2

A company uses BigQuery for BI analytics. They want to improve query performance for a table with 10 TB of data. Which two actions should they take? (Choose two.)

Accepted Answer

Limit the number of columns queried using SELECT * with EXCEPT.. Option A is correct because using SELECT * with EXCEPT limits the number of columns scanned, reducing I/O and improving query performance in BigQuery. BigQuery charges by the amount of data processed, so reading fewer columns directly lowers both cost and query execution time.

Answer

Use a wildcard table to combine multiple tables.

Answer

Partition by a column with a high granularity.

Answer

Use a clustered column as the partition key.

Question 3

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 4

A company wants to create a BI dashboard that shows daily active users. The data is stored in a BigQuery table with columns: user_id, activity_date, and event_type. Which two optimizations would help reduce query costs? (Choose two.)

Accepted Answer

Use a materialized view with COUNT(DISTINCT user_id) grouped by activity_date.. Option C is correct because a materialized view precomputes the COUNT(DISTINCT user_id) grouped by activity_date, so queries against it read only the pre-aggregated results rather than scanning the entire base table. This drastically reduces the amount of data processed, lowering query costs in BigQuery's on-demand pricing model where cost is proportional to bytes processed.

Answer

Cluster the table by event_type.

Answer

Use SELECT * and filter in the BI tool.

Answer

Avoid using the LIMIT clause.

Question 5

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 6

Refer to the exhibit. A BI query is performing slowly. The query plan shows a large shuffle in the aggregate stage. The table is not partitioned or clustered. Which optimization would most directly reduce the shuffle size?

Accepted Answer

Clustering the table on the grouping columns.. Clustering the table on the grouping columns physically co-locates rows with the same group key values within the same storage units (e.g., files or partitions). This allows the query engine to perform partial aggregation locally before the shuffle, dramatically reducing the amount of data that must be moved across the network during the aggregate stage. In systems like BigQuery or Spark SQL, clustering on grouping columns directly minimizes shuffle size by enabling pre-aggregation at the storage layer.

Answer

Converting the query to use a window function.

Answer

Using a materialized view.

Answer

Adding a WHERE clause to filter recent data.

Question 7

Refer to the exhibit. A data engineer created a materialized view on a table that receives streaming inserts. When they query the materialized view, they get this error. What is the most likely cause?

Accepted Answer

The materialized view cannot read data from the streaming buffer.. The error occurs because materialized views in BigQuery cannot directly read data from the streaming buffer. When a base table receives streaming inserts, the data resides in the streaming buffer for up to 90 minutes before being committed to storage. Materialized views only reflect committed data, so querying them during this window returns an error indicating that the view cannot access the streaming buffer.

Answer

The materialized view definition includes a JOIN that is not supported.

Answer

The materialized view has reached its maximum size limit.

Answer

The base table has a schema change that the materialized view cannot adapt to.

Question 8

A company is building a business intelligence dashboard on BigQuery to analyze daily sales data. The table contains a TIMESTAMP column 'order_ts' and a string column 'region'. The BI team frequently filters by month and region. Which table design best optimizes query performance and cost?

Accepted Answer

Partition the table by date (month) and cluster by region. Partitioning by month and clustering by region reduces the data scanned for common filters, improving performance and cost.

Answer

Use a separate table for each region

Answer

Clustering by order_ts and region without partitioning

Answer

Partition the table by region and cluster by order_ts

Question 9

A data engineer is writing a SQL query in BigQuery to calculate the running total of sales per product over time. The table 'sales' has columns product_id, sale_date, and amount. The result must include the cumulative sum ordered by sale_date for each product. Which SQL construct should be used?

Accepted Answer

SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Option B is correct because it uses a window function with a PARTITION BY clause to reset the running total per product and an ORDER BY with a ROWS frame to compute the cumulative sum over time. This is the standard SQL construct in BigQuery for calculating running totals within partitions.

Answer

GROUP BY product_id, sale_date with SUM(amount)

Answer

ROW_NUMBER() OVER (ORDER BY sale_date)

Answer

LAG(amount, 1, 0) OVER (ORDER BY sale_date)

Question 10

A BI team uses BigQuery to report on customer orders. The 'customers' dimension table is updated nightly with Type 2 Slowly Changing Dimensions (SCD). However, some reports show incorrect historical aggregates because the fact table references only the current customer key. Which approach resolves this issue?

Accepted Answer

Store the surrogate customer key from the dimension table in the fact table at transaction time. Option B is correct because with Type 2 SCD, each customer row has a unique surrogate key that represents a specific version of the customer's attributes over time. Storing that surrogate key in the fact table at transaction time ensures that historical facts are permanently linked to the correct customer attributes as they existed at the time of the order. This prevents incorrect aggregates when the dimension table is updated, as the fact table will always join to the precise version of the customer record that was active when the transaction occurred.

Answer

Update the fact table nightly to replace old customer keys with the current key

Answer

Denormalize customer attributes into the fact table

Answer

Use the natural customer ID in the fact table and join with the dimension using a BETWEEN condition on effective dates

Question 11

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 12

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 13

A BI team needs to analyze user behavior with sessionization. Each event has a timestamp and session ID. The table 'sessions' contains columns: session_id, user_id, event_time, event_name. The team wants the first event time per session. Which query is most efficient?

Accepted Answer

SELECT session_id, event_time FROM sessions QUALIFY ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) = 1. Option D is correct because it uses the QUALIFY clause with ROW_NUMBER() to filter directly within the window function, avoiding a self-join or subquery. This approach is efficient in Snowflake and similar platforms, as it processes the window function once and then filters to the first event per session without materializing intermediate results.

Answer

SELECT session_id, ARRAY_AGG(event_time ORDER BY event_time LIMIT 1) FROM sessions GROUP BY session_id

Answer

SELECT a.session_id, a.event_time FROM sessions a INNER JOIN (SELECT session_id, MIN(event_time) min_ts FROM sessions GROUP BY session_id) b ON a.session_id = b.session_id AND a.event_time = b.min_ts

Answer

SELECT session_id, MIN(event_time) FROM sessions GROUP BY session_id

Question 14

In BigQuery, a BI analyst wants to store financial data with high precision and avoid rounding errors. Which data type should be used for currency columns?

Accepted Answer

NUMERIC. NUMERIC (also known as DECIMAL) is the correct choice because it stores exact numeric values with up to 38 digits of precision and a user-defined scale, making it ideal for financial data where rounding errors from binary floating-point representation are unacceptable. In BigQuery, NUMERIC uses fixed-point arithmetic, ensuring that calculations like tax or interest accruals remain exact to the specified decimal places.

Answer

FLOAT64

Answer

INT64

Answer

STRING

Question 15

A company uses BigQuery with a table 'orders' that has a column 'items' of type ARRAY>. An analyst needs to find orders that contain a specific product, 'ABC'. Which query is most efficient?

Accepted Answer

SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM UNNEST(items) WHERE product_id = 'ABC'). Option A is correct because it uses a correlated subquery with `UNNEST` and `EXISTS`, which stops scanning as soon as a matching product_id is found within each row's array. This is the most efficient pattern for checking array membership in BigQuery, as it avoids unnecessary row multiplication and leverages short-circuit evaluation.

Answer

SELECT * FROM orders WHERE ARRAY_LENGTH(items) > 0

Answer

SELECT * FROM orders WHERE 'ABC' IN UNNEST(items)

Answer

SELECT o.*, item FROM orders o, UNNEST(items) item WHERE item.product_id = 'ABC'