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

Question 1

A BI dashboard query is taking too long because it reads all columns from a large table. The dashboard only needs a few columns. What is the best practice?

Accepted Answer

Create a view that selects specific columns.. Creating a view that selects specific columns is the best practice because it allows the BI dashboard to query only the necessary columns without altering the underlying table structure. Views provide a logical abstraction layer, enabling column pruning at the query level while preserving data integrity and access control. This approach reduces I/O and memory consumption by avoiding full table scans on unnecessary columns, directly addressing the performance bottleneck.

Answer

Create a table with only the needed columns.

Answer

Use a subquery to filter columns in the FROM clause.

Answer

Use a LIMIT clause to reduce rows.

Question 2

Which TWO strategies reduce query costs for ad-hoc analysis in BigQuery? (Choose two.)

Accepted Answer

Use clustered tables on frequently filtered columns.. Option B is correct because clustered tables in BigQuery physically sort data based on the specified columns, which allows the query engine to skip entire blocks of data that don't match filter predicates. This reduces the amount of data scanned and thus lowers query costs for ad-hoc analysis. Option E is correct because materialized views precompute and store the results of common aggregations, so queries against them only read the precomputed results rather than scanning the base table, significantly reducing bytes processed.

Answer

Use LIMIT 10 to preview data.

Answer

Use a flat table without partitioning.

Answer

Use SELECT * in all queries.

Question 3

Which THREE components are required to compute a 7-day moving average of daily sales using a window function? (Choose three.)

Accepted Answer

AVG() function. Option C is correct because the AVG() function is the aggregate function that computes the arithmetic mean of the sales values over the specified window frame. In a moving average calculation, AVG() is applied to the rows defined by the window frame to produce the average for each row.

Answer

PARTITION BY product

Answer

WINDOW clause

Question 4

Which TWO optimizations best address slow join performance caused by excessive broadcasting in BigQuery? (Choose two.)

Accepted Answer

Use a materialized view that pre-joins the tables.. Option D is correct because a materialized view can pre-compute and store the join result, eliminating the need to re-execute the join at query time. This avoids the overhead of broadcasting the dimension table repeatedly, as the materialized view is incrementally refreshed and queried directly, reducing both shuffle and broadcast costs.

Answer

Use a large query timeout.

Answer

Set the dimension table to be very large to prevent broadcast.

Answer

Increase the number of slots.

Question 5

The query returns results but takes a long time. The orders table has 500M rows with order_date as a timestamp and revenue as float. How can the query be optimized?

Accepted Answer

Partition the table by month on order_date.. Partitioning the table by month on order_date (Option B) is correct because it physically separates the data into monthly partitions, allowing the query engine to prune partitions that do not match the query's time range. This dramatically reduces the amount of data scanned, which is the primary cause of slow performance on a 500M-row table. In BigQuery, partitioning by a timestamp column like order_date is a native, cost-effective optimization that directly addresses the scan bottleneck.

Answer

Add a clustering key on order_date.

Answer

Use a wildcard table over multiple date-sharded tables.

Answer

Use a materialized view that caches the query result.

Question 6

A data analyst runs a query that joins two large tables on a high-cardinality column with many NULL values. Which action is most likely to resolve the error?

Accepted Answer

Add a WHERE clause to filter out NULLs from the join key.. Option C is correct because filtering out NULLs from the join key with a WHERE clause prevents the database from attempting to match NULL values, which cannot be equated in a standard SQL join (since NULL != NULL). This reduces the cardinality of the join operation and avoids potential performance degradation or errors caused by the large number of NULLs being processed in a high-cardinality column.

Answer

Use a DISTINCT clause on the join key.

Answer

Increase the query timeout setting.

Answer

Use a UNION ALL to combine tables.

Question 7

A BI team queries this table with a WHERE clause that filters on product_id but does not include a sale_date filter. What is the outcome?

Accepted Answer

The query fails with an error.. In a partitioned table (e.g., using Hive-style partitioning or a similar system like BigQuery or Snowflake), a WHERE clause that filters only on `product_id` without including the partition key `sale_date` forces a full scan of all partitions. However, if the table is defined with a strict partition pruning requirement (e.g., in Databricks or Spark SQL with dynamic partition pruning disabled, or in a system that requires the partition column in the filter), the query may fail with an error because the engine cannot determine which partitions to read without the partition key. The correct answer is A because the scenario implies a system (like certain SQL-on-Hadoop engines or strict partitioning rules) where omitting the partition column in the WHERE clause results in a query error, not a successful scan.

Answer

The query runs successfully and only scans partitions containing product_id values.

Answer

The query runs successfully and scans only the latest partition.

Answer

The query runs successfully but scans all partitions.

Question 8

A company is designing a star schema for a BI dashboard that tracks sales performance. The dashboard needs to aggregate sales by product, store, and date. Which schema design is most appropriate?

Accepted Answer

Create a fact table with foreign keys to dimension tables for product, store, and date. Option C is correct because a star schema uses a central fact table with foreign keys to dimension tables, which is optimal for BI aggregation queries. Option A is wrong because a single wide table with all attributes leads to data redundancy and slower queries. Option B is wrong because a fully normalized schema (e.g., snowflake) introduces extra joins that can slow BI queries. Option D is wrong because storing data as JSON arrays in a single table is not suitable for efficient SQL aggregation.

Answer

Store all data in a single table using nested JSON arrays for product and store details

Answer

Create a single wide table with all attributes (product, store, date, sales)

Answer

Use a fully normalized snowflake schema with separate tables for each level of hierarchy

Question 9

A data analyst is running a BigQuery query that joins multiple tables to generate a BI report. The query is slow and uses many LEFT JOINs. What is the best approach to improve performance without changing the business logic?

Accepted Answer

Denormalize the data using nested repeated fields to avoid joins. Denormalizing data using nested repeated fields in BigQuery reduces the number of JOIN operations, which are expensive in a distributed, columnar storage system. By storing related data in a single table with REPEATED fields, the query avoids shuffling large datasets across slots, directly improving performance while preserving the original business logic.

Answer

Add indexes on the join columns

Answer

Replace LEFT JOINs with INNER JOINs where possible

Answer

Increase the number of BigQuery slots

Question 10

A BI team is designing a BigQuery table for a sales dashboard that queries daily sales by product category and region. The dashboard often filters on a specific date range and a specific region. Which combination of partitioning and clustering should be used?

Accepted Answer

Partition by date, cluster by region. Partitioning by date (e.g., on a DATE or TIMESTAMP column) allows BigQuery to prune entire partitions when the dashboard filters on a specific date range, reducing the amount of data scanned. Clustering by region then sorts the data within each partition by region, enabling efficient block-level pruning when the dashboard filters on a specific region. This combination optimizes both the date range and region filters, which are the most common query patterns for this sales dashboard.

Answer

Partition by region, cluster by date

Answer

Use only clustering on date and region without partitioning

Answer

Partition by month, cluster by date

Question 11

A BI developer needs to display sales data in a dashboard that shows sales in local time zones. The source data stores all timestamps in UTC. Which is the best practice for handling time zone conversions?

Accepted Answer

Store all timestamps in UTC and convert them to the desired time zone in SQL queries. Option B is correct because storing timestamps in UTC and converting them in SQL queries ensures that the conversion logic is centralized, auditable, and consistent across all BI reports. This approach leverages the database engine's time zone functions (e.g., AT TIME ZONE in SQL Server or CONVERT_TZ in MySQL) to handle daylight saving time transitions accurately, avoiding the pitfalls of application-layer conversions that may be inconsistent or not applied uniformly.

Answer

Store timestamps in UTC and convert to local time in the BI tool's application layer

Answer

Store timestamps as text strings with time zone offset to avoid conversion

Answer

Store both UTC and local time in separate columns

Question 12

A BI report requires a running total of sales over the last 30 days for each product. The data is in a BigQuery table with columns: sale_date, product_id, amount. Which SQL window function is most efficient?

Accepted Answer

Use SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Option C is correct because it uses a window function with `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` to compute a running total over all rows up to the current row. However, the question asks for a running total over the last 30 days, not all preceding rows. The most efficient approach for a 30-day sliding window is actually `ROWS BETWEEN 29 PRECEDING AND CURRENT ROW` (or `RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW` in BigQuery), but among the given options, C is the only one that produces a running total (cumulative sum) rather than a fixed 30-day window. Option C is marked as correct in the answer key, but note that it does not limit to 30 days; it sums all prior sales. In BigQuery, for a true 30-day rolling sum, `RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW` is the correct syntax.

Answer

Use GROUP BY with SUM(amount)

Answer

Use SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW)

Answer

Use a correlated subquery to sum over previous dates

Question 13

A BI team uses a complex SQL query with multiple Common Table Expressions (CTEs) that are referenced several times within the main query. The query performs poorly. What is the best optimization strategy?

Accepted Answer

Use temporary tables or table snapshots to materialize the CTE results. Option B is correct because CTEs in SQL Server are not materialized by default; they are evaluated each time they are referenced, leading to repeated execution of the same logic. By using temporary tables or table snapshots, you materialize the intermediate result set once, which avoids redundant scans and significantly improves performance for complex queries with multiple CTE references.

Answer

Add indexes on the tables used in the CTEs

Answer

Reuse the same CTE names as often as possible in the query

Answer

Replace CTEs with derived tables in the FROM clause

Question 14

A financial BI application stores monetary values such as revenue and tax amounts. Which BigQuery data type should be used to ensure accuracy in calculations?

Accepted Answer

Use NUMERIC or BIGNUMERIC. Option D is correct because NUMERIC and BIGNUMERIC are exact numeric types with fixed precision and scale, designed to avoid floating-point rounding errors. In BigQuery, monetary calculations require exact decimal arithmetic, and these types provide up to 38 (NUMERIC) or 76 (BIGNUMERIC) digits of precision, ensuring accuracy for revenue and tax computations.

Answer

Use STRING and parse numbers as needed

Answer

Use INT64 and store amounts in cents

Answer

Use FLOAT64

Question 15

A company tracks customer demographics that change over time (e.g., address). They need to maintain historical accuracy in BI reports. Which approach correctly implements a Type 2 slowly changing dimension?

Accepted Answer

Add effective start and end date columns for each dimension attribute. Option B is correct because Type 2 SCD uses start and end dates to track effective periods, allowing queries to join based on the snapshot date. Option A is wrong because overwriting loses history. Option C is wrong because an append-only log requires complex queries to get current snapshot. Option D is wrong because a single column storing only current value loses history.

Answer

Store only the current value and rely on the fact table's timestamp to infer history

Answer

Store only the current value in the dimension table and use an audit log for changes

Answer

Overwrite the old value with the new value