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

Question 1

A BI manager needs to restrict access to sensitive sales data so that salespeople can only see their own region's data. Which BigQuery feature should be used to implement row-level security without duplicating tables?

Accepted Answer

Create an authorized view that uses SESSION_USER() in a WHERE clause to filter rows. Option C is correct because an authorized view with SESSION_USER() in a WHERE clause dynamically filters rows based on the caller's identity, providing row-level security without duplicating tables. This approach leverages BigQuery's ability to share a single view with different users, each seeing only their authorized subset of data, which aligns with the requirement to restrict salespeople to their own region's data.

Answer

Use column-level security to hide sensitive columns

Answer

Use BigQuery row-level access policies

Answer

Create separate IAM roles for each region

Question 2

A user runs the query above on a large table and receives an out-of-memory error. What is the most likely cause?

Accepted Answer

The ORDER BY clause forces sorting of the entire dataset in memory on a single worker. Option C is correct because the ORDER BY clause in a distributed SQL engine like Snowflake or BigQuery forces all data to be sent to a single worker node for sorting, which can exceed the memory limit of that node when the dataset is large. This is a common cause of out-of-memory errors in MPP (Massively Parallel Processing) systems, as sorting is not a distributable operation by default without explicit partitioning or window functions.

Answer

The table is a materialized view that cannot handle ORDER BY

Answer

The query uses COUNT(*) without a GROUP BY

Answer

The table is not partitioned, so full table scan causes memory overflow

Question 3

The query above fails with 'Resources exceeded: UDF out of memory' on a large table. What is the best way to fix this?

Accepted Answer

Rewrite the function as a SQL UDF to avoid JavaScript overhead. Option A is correct because JavaScript UDFs in BigQuery run in a sandbox with limited memory (typically 6 MB per UDF instance). When processing a large table, the UDF may exceed this memory due to per-row overhead or large intermediate results. Rewriting the function as a SQL UDF eliminates JavaScript overhead and runs natively within BigQuery's distributed execution engine, which can handle larger datasets without memory constraints.

Answer

Add a GROUP BY clause to reduce the number of rows processed

Answer

Convert the temporary UDF to a persistent UDF

Answer

Increase the memory allocation for JavaScript UDFs

Question 4

What should be adjusted to improve performance and resolve the connection error?

Accepted Answer

Increase max_connections and implement connection pooling. The connection error is likely due to the database reaching its maximum connection limit, which causes new connection attempts to be rejected. Increasing `max_connections` allows more concurrent client connections, while implementing connection pooling (e.g., using PgBouncer or similar) reuses existing connections efficiently, reducing overhead and preventing connection exhaustion. This directly resolves the error without requiring hardware changes.

Answer

Disable automatic failover to reduce overhead

Answer

Change the instance type to a higher memory machine

Answer

Increase the disk size to handle more I/O

Question 5

Which TWO best practices should be followed when modeling data for a Looker BI dashboard to optimize query performance?

Accepted Answer

Use persistent derived tables (PDTs) to materialize intermediate results. Option B is correct because Persistent Derived Tables (PDTs) materialize intermediate query results into physical tables in the underlying database (e.g., BigQuery). This avoids re-executing complex logic on every user interaction, drastically reducing query latency and cost. PDTs are a core Looker optimization for repeated, heavy transformations.

Answer

Use derived tables for all complex logic

Answer

Use native derived tables to leverage BigQuery's UDFs

Answer

Use materialized views in the underlying database

Question 6

Which TWO statements are true about designing a star schema for BI reporting?

Accepted Answer

Dimension tables are denormalized to reduce the number of joins. Option B is correct because dimension tables in a star schema are intentionally denormalized to reduce the number of joins required for BI queries. This denormalization improves query performance by allowing fact tables to join directly to dimension tables without traversing multiple normalized tables, which is a key design principle for OLAP reporting.

Answer

Fact tables store descriptive attributes like product names

Answer

Fact tables use natural keys to enforce referential integrity

Answer

Dimension tables are normalized to minimize redundancy

Question 7

Which THREE methods are effective for improving query performance in BigQuery for BI workloads?

Accepted Answer

Clustering on frequently filtered columns. Option A is correct because clustering on frequently filtered columns physically co-locates related data within blocks, significantly reducing the amount of data scanned for queries with filter predicates. This is especially effective for BI workloads that often filter on high-cardinality columns like customer ID or transaction type, as it avoids full table scans and improves query performance without additional storage costs.

Answer

Replacing joins with subqueries

Answer

Using SELECT * in queries

Question 8

A company uses BigQuery for BI. They need to create a table that stores daily sales data with millions of rows. The query pattern is to aggregate sales by month for specific product categories. Which table design is most cost-effective and performant?

Accepted Answer

Partitioned table by date with clustering on product_category. Partitioning by date allows BigQuery to prune entire partitions when querying monthly aggregates, drastically reducing the data scanned. Clustering on product_category further organizes data within each partition, enabling efficient block-level pruning for category filters. This combination minimizes both cost (bytes billed) and query latency for the described workload.

Answer

Non-partitioned table with clustering on product_category

Answer

Non-partitioned, non-clustered table with manual sharding by date

Answer

Partitioned table by product_category with clustering on date

Question 9

A data analyst runs a query joining several large tables and gets 'Resources exceeded' error. They need to reduce memory usage without changing the query logic. What should they do?

Accepted Answer

Use a subquery to pre-aggregate the largest table before joining. Option A is correct because pre-aggregating the largest table in a subquery reduces the amount of data that needs to be shuffled and joined in memory. In BigQuery, this minimizes the bytes processed and the memory footprint of the join operation, directly addressing the 'Resources exceeded' error without altering the overall query logic.

Answer

Use APPROX_COUNT_DISTINCT for counting distinct values

Answer

Increase the slot reservation

Answer

Use SELECT * in the subquery to ensure all columns are available

Question 10

A company has a BigQuery dataset with many views. They need to ensure that only the latest 30 days of data is used in BI reports for performance. The source table is partitioned by ingestion_time. Which approach reduces query cost and improves performance?

Accepted Answer

Create a materialized view with the date filter. Option C is correct because a materialized view precomputes and stores the filtered result set, allowing BigQuery to serve BI queries directly from the materialized view's storage without scanning the entire source table. This eliminates the need to re-process the full table on every query, significantly reducing query cost and improving performance for the 30-day sliding window.

Answer

Use BigQuery BI Engine to cache results

Answer

Create a view with WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)

Answer

Use a scheduled query to copy the last 30 days to a separate table

Question 11

A BI analyst wants to create a report that displays total revenue by product category and month, with ability to drill down to individual products. Which schema design supports this in BigQuery?

Accepted Answer

Star schema with fact table and dimension tables. Option A is correct because a star schema with a fact table and dimension tables allows efficient aggregation and drill-down through joins. Snowflake schema is over-normalized for BigQuery. Wide tables cause duplication and slow aggregation. Repeated fields are not suitable for drill-down.

Answer

Denormalized table with repeated fields

Answer

Single wide table with all dimensions and measures

Answer

Snowflake schema with normalized dimensions

Question 12

Which SQL function in BigQuery is best for replacing NULL values in a numeric column with a default value?

Accepted Answer

COALESCE. Option D, COALESCE, is correct because it returns the first non-NULL value from a list of expressions, making it ideal for replacing NULLs in a numeric column with a default value. In BigQuery, COALESCE is the standard, flexible function that can handle multiple arguments, unlike IFNULL which only accepts two. This aligns with SQL ANSI standards and is the recommended approach for NULL handling in numeric columns.

Answer

NULLIF

Answer

NVL

Answer

IFNULL

Question 13

A company has a BigQuery table with a TIMESTAMP column and wants to query data for a specific date range efficiently. Which WHERE clause ensures partition pruning if the table is partitioned by that TIMESTAMP column?

Accepted Answer

WHERE timestamp_col BETWEEN TIMESTAMP('2023-01-01') AND TIMESTAMP('2023-01-31'). Option A is correct because it directly references the TIMESTAMP column without wrapping it in a function, allowing BigQuery's partition pruning to eliminate irrelevant partitions. When a table is partitioned by a TIMESTAMP column, the query engine can compare the partition boundaries directly against the literal TIMESTAMP values in the WHERE clause, scanning only the partitions that fall within the specified range.

Answer

WHERE TIMESTAMP_TRUNC(timestamp_col, DAY) BETWEEN '2023-01-01' AND '2023-01-31'

Answer

WHERE timestamp_col >= '2023-01-01' AND timestamp_col < '2023-02-01'

Answer

WHERE DATE(timestamp_col) BETWEEN '2023-01-01' AND '2023-01-31'

Question 14

A data engineer needs to design a table to store time-series sensor data arriving every second. The data will be queried mainly for the last hour over a specific device. Which table design minimizes query costs?

Accepted Answer

Partition by ingestion_time, cluster by device_id. Option D minimizes query costs because partitioning by ingestion_time allows the query engine to skip partitions outside the last hour, while clustering by device_id further narrows the scan to only the relevant device's data within those partitions. This combination reduces the amount of data read and the number of files scanned, which is critical for high-frequency time-series data.

Answer

Partition by ingestion_time, cluster by timestamp

Answer

Partition by ingestion_time, no clustering

Answer

No partitioning, cluster by device_id

Question 15

A company is using BigQuery and needs to implement row-level security so that sales representatives only see their own region's data. Which approach?

Accepted Answer

Use authorized views with WHERE clause filtering by session user's region. Option C is correct because BigQuery authorized views allow you to enforce row-level security by embedding a WHERE clause that filters data based on the session user's region (e.g., using SESSION_USER() or a mapping table). This ensures each sales representative sees only their own region's data without exposing the underlying tables directly.

Answer

Use BigQuery column-level security to filter by region

Answer

Create separate tables for each region and union in views

Answer

Use IAM conditions at the dataset level