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

Question 1

A large e-commerce platform uses BigQuery for business intelligence. They have a fact table `orders` (10 TB, partitioned by order_date, clustered by customer_id) and a dimension table `customers` (2 TB, not partitioned, not clustered). The BI team runs a daily dashboard query that joins these tables on customer_id and filters on order_date = CURRENT_DATE() and customer_country = 'US'. The query currently scans the full `customers` table and 2 GB of the `orders` table, taking 30 seconds. The business wants to reduce cost and latency. The `customers` table has 500 million rows and is updated incrementally every hour. Which action will most effectively reduce the amount of data scanned and query time?

Accepted Answer

Cluster the `customers` table on customer_id.. Clustering the `customers` table on `customer_id` will physically co-locate rows with the same `customer_id`, allowing the query to use block-level pruning when joining with the filtered `orders` table. Since the query filters `orders` by `order_date = CURRENT_DATE()` (2 GB scanned) and then joins on `customer_id`, BigQuery can skip reading most of the `customers` table if it is clustered on the join key, drastically reducing the 2 TB full scan and lowering both cost and latency.

Answer

Denormalize customer country and other attributes into the `orders` table.

Answer

Create a materialized view that joins `orders` and `customers` on customer_id.

Answer

Partition the `customers` table by customer_id.

Question 2

Order the steps to migrate an on-premises MySQL database to Cloud SQL using Database Migration Service (DMS).

Question 3

Order the steps to export data from Cloud Bigtable to Cloud Storage using Dataflow.

Question 4

Order the steps to perform a disaster recovery drill for a Cloud Spanner database using backups.

Question 5

Match each Cloud SQL high-availability feature to its description.

Question 6

Match each Cloud SQL tier to its description.

Question 7

Match each BigQuery DDL statement to its function.

Question 8

A data analyst needs to create a reporting table that aggregates sales data by month. They want to ensure the table is optimized for querying by month and product category. Which table design best supports this?

Accepted Answer

Partition by month and cluster by product_category.. Option D is correct because partitioning by month physically separates data into monthly segments, allowing query pruning to skip irrelevant partitions when filtering by month. Clustering by product_category within each partition co-locates rows with the same category, reducing the amount of data scanned for queries that filter on both month and category. This design optimizes both I/O and scan efficiency for the described workload.

Answer

Use a table with clustering on product_category only.

Answer

Use a flat table with no partitioning.

Answer

Use a view that selects month and product_category.

Question 9

A company is using BigQuery for BI and needs to reduce costs for a large historical dataset that is infrequently queried. Which approach should they take?

Accepted Answer

Partition by ingestion time and set expiration on partitions older than 90 days.. Option C is correct because partitioning by ingestion time allows BigQuery to automatically manage data lifecycle by setting partition expiration. This reduces storage costs for historical data that is infrequently queried, as partitions older than 90 days are deleted without manual intervention. This approach directly addresses the need to reduce costs for a large historical dataset while maintaining query performance on recent data.

Answer

Use materialized views for common aggregations.

Answer

Use clustered tables.

Answer

Use a view with a WHERE clause filtering recent data.

Question 10

An analyst writes a SQL query that joins a fact table with multiple dimension tables. The query runs slowly due to shuffling. Which optimization technique should be applied?

Accepted Answer

Cluster the fact table on the dimension join keys.. Shuffling occurs when data must be redistributed across nodes during joins, often because the join keys are not co-located. Clustering the fact table on the dimension join keys physically co-locates rows with the same join key values, minimizing data movement during the join. This is a direct optimization for shuffle-heavy workloads in distributed SQL engines like Spark SQL or Hive.

Answer

Use a subquery in the FROM clause to pre-aggregate.

Answer

Use a LIMIT clause to restrict rows.

Answer

Use a window function to precompute values.

Question 11

A BI team wants to create a report that shows daily active users for the last 7 days. Which SQL construct is most appropriate for fast performance on a large dataset?

Accepted Answer

SELECT APPROX_COUNT_DISTINCT(user_id) ... WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY). Option D is correct because APPROX_COUNT_DISTINCT uses HyperLogLog (HLL) algorithm, which provides near-exact distinct counts with significantly less memory and faster performance than COUNT(DISTINCT) on large datasets. This is ideal for a daily active users report over 7 days where exact precision is not critical.

Answer

SELECT COUNT(DISTINCT user_id) ... WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

Answer

SELECT DISTINCT user_id ...

Answer

SELECT COUNT(user_id) ... GROUP BY user_id

Question 12

A data engineer notices that a scheduled query exporting BigQuery data to Cloud Storage is failing with a timeout error. The dataset contains 500 million rows. What should they do?

Accepted Answer

Export each partition separately.. Option D is correct because exporting a 500-million-row table as a single operation can exceed BigQuery's 6-hour timeout limit. By exporting each partition separately, you reduce the data volume per export job, allowing each to complete within the timeout window. This approach leverages BigQuery's partitioned table structure to parallelize the export and avoid hitting the timeout threshold.

Answer

Use SELECT * without filters.

Answer

Change the export format from CSV to Avro.

Answer

Increase the query timeout setting.

Question 13

A company uses BigQuery BI Engine for sub-second query performance. However, some queries are hitting the BI Engine memory limit. Which action should be taken?

Accepted Answer

Increase BI Engine capacity allocation.. BI Engine is an in-memory analysis service that accelerates queries by caching data in memory. When queries exceed the allocated memory, they spill to disk, causing performance degradation. Increasing the BI Engine capacity allocation directly addresses this by providing more memory for caching, enabling sub-second query performance for larger datasets.

Answer

Cluster the tables more granularly.

Answer

Use a reservation with a higher slot count.

Answer

Optimize the dimension tables by denormalizing.

Question 14

A SQL query with multiple JOINs is returning duplicate rows. What is the most likely cause?

Accepted Answer

There is a one-to-many relationship between tables.. When a SQL query with multiple JOINs returns duplicate rows, the most likely cause is a one-to-many relationship between the tables being joined. Each matching row in the 'many' side of the join multiplies the rows from the 'one' side, producing duplicates. This is a fundamental behavior of JOIN operations in SQL, where the result set is the Cartesian product of matching rows across the joined tables.

Answer

Using INNER JOIN instead of LEFT JOIN.

Answer

Missing ORDER BY clause.

Answer

Using UNION instead of UNION ALL.

Question 15

A data analyst needs to create a rolling 30-day average of daily revenue. Which window function clause is required?

Accepted Answer

ROWS BETWEEN 29 PRECEDING AND CURRENT ROW. Option D is correct because `ROWS BETWEEN 29 PRECEDING AND CURRENT ROW` defines a physical window of exactly 30 rows (the current row plus the 29 preceding rows), which is the standard SQL approach for a rolling 30-day average when each row represents one day of revenue. This clause ensures that the window frame is fixed at 30 rows regardless of gaps in dates, making it reliable for daily data.

Answer

UNBOUNDED PRECEDING

Answer

RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW

Answer

PARTITION BY month