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

Question 1

A startup is building a BI stack on Google Cloud. They have moderate data volumes and need to run ad-hoc analytical queries and real-time dashboards. Which Google Cloud database service is most appropriate for this workload?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse designed for analytical queries and real-time dashboards. It supports ad-hoc SQL queries on large datasets with fast execution via its columnar storage and distributed query engine, making it ideal for BI workloads with moderate data volumes.

Answer

Cloud Spanner

Answer

Firestore

Answer

Cloud SQL

Question 2

Which TWO are best practices for designing a star schema in BigQuery for BI? (Choose two.)

Accepted Answer

Store dimension attributes in a single denormalized dimension table instead of multiple normalized tables.. Option A is correct because in BigQuery, storing dimension attributes in a single denormalized dimension table (star schema) reduces the number of joins required in BI queries, improving query performance and simplifying SQL. BigQuery's columnar storage and distributed architecture handle denormalized dimensions efficiently, avoiding the overhead of multiple normalized tables that would require complex joins and slow down analytical queries.

Answer

Partition fact tables by low-cardinality columns like gender.

Answer

Pre-aggregate all measures at every possible grain in the fact table.

Answer

Avoid using joins entirely by storing all data in one wide table.

Question 3

Which THREE techniques can improve query performance in BigQuery for BI workloads? (Choose three.)

Accepted Answer

Use approximate aggregation functions when exact results are not required.. Option A is correct because approximate aggregation functions (e.g., APPROX_COUNT_DISTINCT, APPROX_QUANTILES) in BigQuery use HyperLogLog++ algorithms to return near-exact results with significantly lower resource consumption and faster execution. For BI workloads where exact precision is not critical (e.g., dashboard approximations), this reduces query cost and latency.

Answer

Use SELECT * with LIMIT to preview data.

Answer

Use ORDER BY on large result sets without LIMIT.

Question 4

Which TWO are effective strategies to control costs when running BI queries on BigQuery? (Choose two.)

Accepted Answer

Set a maximum bytes billed limit for user projects.. Options C and D are correct. Setting a custom cost control (max bytes billed) prevents runaway queries. Using flat-rate reservations provides predictable pricing and can lower costs for steady workloads. Option A is wrong because scheduled queries add to cost if not optimized. Option B is wrong because enabling BI Engine incurs additional reservation costs. Option E is wrong because copying tables increases storage costs.

Answer

Create materialized copies of tables for each dashboard.

Answer

Schedule queries to run every minute to keep the cache warm.

Answer

Enable BI Engine for all tables to speed up queries.

Question 5

Refer to the exhibit. The query joins two large tables and aggregates results. Which optimization would most likely reduce the high shuffle bytes in Stage 3?

Accepted Answer

Add a WHERE clause to filter rows before the join.. Option A is correct because filtering data before the join reduces the amount of data shuffled. Option B is wrong because clustering on the join key reduces the shuffle but may not eliminate it. Option C is wrong because the join itself causes data movement; using a manual broadcast join might help only if one table is small, but it is not automatic. Option D is wrong because ORDER BY is not the main cause of the shuffle; the join is.

Answer

Ensure both tables are clustered on the join key.

Answer

Use a broadcast join hint to force one table to be broadcast.

Answer

Add an ORDER BY clause to sort the data before aggregation.

Question 6

Refer to the exhibit. The query scans 500 GB even though it filters on the partitioning column event_date and only needs data from 30 days. What is the most likely reason?

Accepted Answer

COUNT(DISTINCT) often results in full table scan to ensure accuracy, even with partitions.. Option A is correct because COUNT(DISTINCT) in many SQL engines, including those used in data warehousing like Google BigQuery or Snowflake, often requires a full scan of all partitions to ensure global uniqueness. Even with a filter on the partitioning column, the engine cannot guarantee that distinct values are confined to the filtered partitions without scanning all data, especially if the distinct operation spans across partitions or if the engine's optimizer lacks partition pruning for distinct aggregations.

Answer

The query lacks a LIMIT clause.

Answer

The clustering on user_id is causing a full table scan.

Answer

The table is not actually partitioned by event_date; the filter is on a non-partitioned column.

Question 7

Refer to the exhibit. The BI team creates a view to summarize sales. When they query the view with an additional WHERE clause on region, they notice that the underlying query still processes the same amount of data regardless of the filter. What is the most likely reason?

Accepted Answer

The view is a logical view, not a materialized view, so filters on the view do not reduce the scanned data.. Option A is correct because a logical view (standard view) does not materialize data; the query runs each time, and the outer filter does not push down into the view's WHERE clause. Option B is wrong because the view is not a materialized view. Option C is wrong because the view is standard, not authorized. Option D is wrong because the date filter is in the view definition; the outer filter on region does not reduce processing.

Answer

The view is a materialized view that refreshes every 30 minutes.

Answer

The view's WHERE clause on date is too restrictive, causing a full scan.

Answer

The view uses authorized views, which prevent predicate pushdown.

Question 8

A company uses BigQuery for BI dashboards. Users report that queries on the sales table take longer than expected. The table contains daily transaction data and is not partitioned. Which action will most improve query performance while minimizing cost?

Accepted Answer

Partition the table by the transaction date column. Partitioning by date reduces the data scanned per query, improving performance and cost. Clustering alone may not reduce scanned bytes as effectively. Denormalization can help but may increase storage costs. Increasing reservation slots increases cost without optimizing the query.

Answer

Increase the BigQuery reservation slot count

Answer

Cluster the table by the transaction date column

Answer

Denormalize the table by including dimension attributes

Question 9

A data engineering team ingests JSON logs into BigQuery using a streaming pipeline. Queries need to extract specific fields from nested arrays. Which SQL construct should be used to efficiently transform the nested data into a flat table for BI?

Accepted Answer

UNNEST with CROSS JOIN. Option D is correct because `UNNEST` with `CROSS JOIN` is the standard SQL construct in BigQuery to flatten nested arrays (repeated fields) into a flat table. When JSON logs contain arrays of structs, `CROSS JOIN UNNEST(array_column)` expands each array element into its own row, allowing BI tools to access individual fields directly. This is the most efficient and idiomatic way to transform nested data into a relational format for querying.

Answer

ARRAY_AGG with STRUCT

Answer

STRUCT with nested field access

Answer

SELECT * EXCEPT with UNNEST

Question 10

A financial company uses Cloud SQL for PostgreSQL to store transaction data. They need to create a materialized view that aggregates daily sales for a BI dashboard. The underlying transaction table is updated continuously. Which approach ensures the materialized view remains up to date without manual intervention?

Accepted Answer

Schedule a Cloud Function via Cloud Scheduler to run REFRESH MATERIALIZED VIEW periodically. Option C is correct because Cloud SQL for PostgreSQL does not support automatic materialized view refresh. The only way to keep a materialized view up to date without manual intervention is to schedule a periodic refresh using Cloud Scheduler to invoke a Cloud Function that executes the REFRESH MATERIALIZED VIEW command. This approach balances freshness with resource cost, as refreshing on every transaction would be too expensive.

Answer

Use BigQuery federated query to directly query the Cloud SQL table

Answer

Use a Cloud SQL read replica and create the materialized view on the replica

Answer

Add a trigger on the base table to refresh the materialized view on each update

Question 11

A retail company uses Cloud Spanner for their OLTP system and wants to run BI queries on the same data without impacting transactional performance. Which solution should they implement?

Accepted Answer

Use Cloud Dataflow to stream Spanner changes into BigQuery. Option C is correct because Cloud Dataflow can read the Cloud Spanner change streams and stream mutations into BigQuery in near real-time, enabling BI queries on fresh data without adding read load to the Spanner instance. This decouples the analytical workload from the transactional workload, preserving OLTP performance.

Answer

Create a federated BigQuery query that reads from Spanner

Answer

Export Spanner data to Cloud Storage and then load into BigQuery manually

Answer

Run BI queries directly on Spanner using read-only transactions

Question 12

A gaming company ingests player clickstream data in real time via Cloud Pub/Sub. They need to aggregate events per player session in BigQuery with exactly-once semantics. Which architecture minimizes latency and cost?

Accepted Answer

Use Cloud Dataflow with exactly-once processing to BigQuery. Cloud Dataflow with exactly-once processing is the correct choice because it provides a unified stream and batch processing model that guarantees exactly-once semantics when writing to BigQuery via the BigQuery I/O connector. This minimizes latency by processing events in micro-batches or streaming mode while avoiding duplicate data, and it is cost-effective as Dataflow auto-scales based on the Pub/Sub throughput.

Answer

Use Cloud Functions to write each message directly to BigQuery

Answer

Use Cloud Pub/Sub subscription to write to BigQuery directly

Answer

Use Cloud Dataproc to run Spark streaming jobs

Question 13

A BI analyst needs to calculate a running total of sales by region over time in BigQuery. Which SQL window function should be used?

Accepted Answer

SUM(sales) OVER (PARTITION BY region ORDER BY date). Option B is correct because the SUM() window function with an ORDER BY clause in the OVER() clause computes a running total (cumulative sum) over the specified partition. In BigQuery, when you include ORDER BY inside a window function's OVER() clause, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces the running total for each region ordered by date.

Answer

RANK() OVER (PARTITION BY region ORDER BY date)

Answer

ROW_NUMBER() OVER (PARTITION BY region ORDER BY date)

Answer

COUNT(sales) OVER (PARTITION BY region ORDER BY date)

Question 14

A company has a BigQuery table partitioned by ingestion time. They want to create a BI report showing month-over-month revenue growth. To minimize query cost, what should they do?

Accepted Answer

Use a WHERE clause with _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH) and LAG. Option A is correct because it uses a WHERE clause with _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH) to prune partitions, ensuring BigQuery scans only the necessary 13 months of data. The LAG function then computes month-over-month revenue growth efficiently. This minimizes query cost by reducing the amount of data processed, which is critical for ingestion-time partitioned tables.

Answer

Use DATE_TRUNC on the ingestion timestamp without filtering partitions

Answer

Use LAG without a partition filter

Answer

Use a wildcard table with UNION ALL over monthly tables

Question 15

A financial institution uses Cloud SQL for MySQL to handle transaction processing. They need to generate daily BI reports that aggregate millions of transactions per account. The BI queries are CPU-intensive and degrade OLTP performance. What is the most effective solution?

Accepted Answer

Create a Cloud SQL read replica and run reports against it. Creating a Cloud SQL read replica allows you to offload BI reporting queries to a separate instance that replicates data from the primary using MySQL's asynchronous replication. This isolates the CPU-intensive aggregation queries from the OLTP workload, preventing performance degradation on the primary instance while still providing near-real-time data for reports.

Answer

Schedule reports during off-peak hours only

Answer

Use Cloud SQL's high availability configuration

Answer

Upgrade the primary instance to a higher machine type