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

Question 1

A company stores user events in BigQuery as nested repeated fields. They want to use Looker to build dashboards on individual events. Which SQL pattern should they use in a derived table to flatten the data?

Accepted Answer

SELECT fields FROM table, UNNEST(events) AS event. Option B is correct because UNNEST(events) in BigQuery SQL flattens the nested repeated field 'events' into individual rows, enabling Looker to treat each event as a separate record for dashboarding. This is the standard pattern for denormalizing arrays in BigQuery derived tables, as it converts each array element into its own row while preserving the parent record's fields.

Answer

SELECT fields FROM table WHERE events IS NOT NULL

Answer

SELECT ARRAY_AGG(events) FROM table

Answer

SELECT events.* FROM table

Question 2

A company uses Cloud SQL for PostgreSQL for its BI database. Queries involving joins on large tables are slow. Which TWO strategies should they implement to improve join performance? (Choose TWO.)

Accepted Answer

Denormalize tables to reduce the number of joins. Denormalizing tables reduces the number of joins required in queries by combining related data into fewer tables. This directly minimizes the computational overhead of join operations in Cloud SQL for PostgreSQL, which is especially beneficial for large BI datasets where join performance is critical.

Answer

Increase the number of CPU cores on the instance

Answer

Create read replicas for the join queries

Answer

Use connection pooling to reduce connection overhead

Question 3

A company wants to reduce BigQuery query costs for their BI workloads. Which THREE actions effectively lower the amount of data processed per query? (Choose THREE.)

Accepted Answer

Use partitioned tables on date column. Partitioned tables in BigQuery allow queries to use the WHERE clause to filter on the partition column (e.g., a date column), so BigQuery can prune entire partitions from the scan. This directly reduces the amount of data read and billed, lowering query costs. Option A is correct because it is a primary cost-control mechanism in BigQuery.

Answer

Use LIMIT in subqueries to reduce output

Answer

Use SELECT * to avoid missing columns

Question 4

Which TWO BigQuery features are specifically designed to accelerate BI dashboard query performance? (Choose TWO.)

Accepted Answer

Clustering. Clustering (B) physically co-locates rows with similar values in the same storage blocks, allowing BigQuery to skip entire blocks when processing queries with filters on clustered columns. This dramatically reduces the amount of data scanned, directly accelerating BI dashboard queries that often filter by date, region, or customer ID. Cached results (D) store the output of recent queries for up to 24 hours, so repeated dashboard refreshes or concurrent user requests can be served instantly without re-scanning any data.

Answer

Wildcard tables

Answer

User-defined functions (UDFs)

Answer

Column-level security

Question 5

The user runs a BigQuery query on a non-partitioned table and receives the error shown. Which optimization should be applied first to resolve the issue?

Accepted Answer

Partition the table by the event_date column. The error indicates that the query is scanning too much data, likely exceeding the free tier or slot quota. Partitioning the non-partitioned table by `event_date` allows BigQuery to perform partition pruning, scanning only the relevant date range instead of the entire table. This directly reduces the data processed, which is the most effective first optimization for cost and performance.

Answer

Increase the BigQuery reservation slot count

Answer

Create a materialized view that pre-aggregates the data

Answer

Cluster the table by event_date

Question 6

A Looker developer configured a new connection to BigQuery as shown. The connection test fails with the error above. What is the most likely cause?

Accepted Answer

The Looker service account lacks the required BigQuery roles on the dataset. Option D is correct because the error indicates a permissions issue during the connection test. Looker uses a service account to authenticate to BigQuery, and if that service account lacks the required BigQuery roles (e.g., BigQuery Data Viewer, BigQuery Job User) on the dataset, the connection test will fail with an access denied error. The error message shown in the question (not provided here but implied) typically states 'Access Denied' or 'Permission denied' when the service account does not have the necessary IAM permissions on the dataset or project.

Answer

The dataset mydataset does not exist in the project

Answer

The BigQuery query quota has been exceeded for the project

Answer

The Looker instance is located in a different region than the BigQuery dataset

Question 7

A Dataflow streaming pipeline that writes to a BigQuery table fails with the error above. Which change should be made to the table schema to prevent this error?

Accepted Answer

Partition the table by ingestion time. Partitioning the table by ingestion time (e.g., _PARTITIONTIME) distributes the streaming buffer across multiple partitions, avoiding the per-partition buffer limit. Increasing the buffer size is a workaround but not a schema change. Clustering does not affect the streaming buffer. Using a wildcard table is unrelated.

Answer

Add a clustering column

Answer

Increase the streaming buffer size in the table definition

Answer

Change the table to use a wildcard table pattern

Question 8

A company is designing a data warehouse for business intelligence reporting. They want to organize data into fact and dimension tables to support fast aggregations. Which schema design is most appropriate for this purpose?

Accepted Answer

Star schema. The star schema is most appropriate for business intelligence reporting because it denormalizes dimension tables around a central fact table, enabling fast aggregations and simple queries. This design minimizes the number of joins required for analytical queries, which is critical for performance in OLAP workloads. In contrast, normalized schemas like 3NF or snowflake increase join complexity and degrade query speed.

Answer

Third Normal Form (3NF) schema

Answer

Snowflake schema

Answer

Entity-relationship schema

Question 9

A data analyst reports that a BI dashboard query on BigQuery is taking over 30 seconds to execute. The table is partitioned by date and clustered by customer_id. The query filters on a specific date range and aggregates sales by customer. What is the most likely cause of the slow performance?

Accepted Answer

The query filters on a date column that is not the partition column, causing a full table scan.. Option C is correct because the query filters on a specific date range, but the table is partitioned by date, so BigQuery can prune partitions to scan only the relevant ones. If the filter were on a column that is not the partition column, a full table scan would occur, causing slow performance. Since the table is partitioned by date and the query filters on a date range, partition pruning should work efficiently, making C the most likely cause only if the filter column is misidentified. However, the question states the table is partitioned by date and the query filters on a specific date range, so partition pruning should apply; the correct answer is actually A, as clustering on customer_id provides no benefit without a filter on that column, leading to a full scan of the clustered data.

Answer

The query does not include a filter on the clustering column, so clustering provides no benefit.

Answer

The query uses a LEFT JOIN that requires a broadcast join, increasing network overhead.

Answer

The table does not have a primary key, so BigQuery cannot use index scans.

Question 10

A company uses BigQuery for BI reporting. They have a materialized view that refreshes automatically to provide pre-aggregated sales data. Recently, the materialized view stopped reflecting new data inserted into the base table. The base table is a streaming buffer table with ingestion-time partitioning. What is the most likely reason?

Accepted Answer

The materialized view does not support streaming buffer tables.. Materialized views in BigQuery do not support base tables that use a streaming buffer, such as ingestion-time partitioned tables. The streaming buffer contains data that has not yet been committed to managed storage, and materialized views can only read from committed storage. Therefore, when new data is inserted into the streaming buffer, the materialized view cannot reflect it until the data is flushed from the buffer, which can cause the view to appear stale or stop reflecting new data entirely.

Answer

The automatic refresh interval has been exceeded due to high query load.

Answer

The materialized view has reached the maximum number of partitions allowed.

Answer

The base table's schema has changed, making the materialized view incompatible.

Question 11

A database engineer is designing a data model for a BI dashboard that tracks daily sales by product category. The data source is a transactional database with a normalized schema. Which BigQuery feature should they use to update the fact table incrementally each day?

Accepted Answer

Scheduled queries with MERGE statements. Scheduled queries with MERGE statements allow incremental updates by inserting new rows and updating existing ones based on a unique key, such as date and product category. This avoids full table reloads, making it efficient for daily fact table refreshes from a normalized transactional source.

Answer

Streaming inserts

Answer

BigQuery Data Transfer Service

Answer

Load jobs with WRITE_TRUNCATE

Question 12

A BI team finds that their BigQuery query that aggregates sales by region runs slower than expected, even with appropriate clustering and partitioning. The query filters on a date range and then groups by region. The table is partitioned by date and clustered by region. What can the team do to improve query performance without increasing cost?

Accepted Answer

Use a materialized view that pre-aggregates sales by region and date.. Option D is correct because a materialized view in BigQuery can pre-aggregate sales by region and date, allowing the query to read precomputed results instead of scanning the entire table. This reduces the amount of data processed and speeds up the query without increasing cost, as the materialized view is automatically maintained and only incremental changes are processed.

Answer

Increase the number of clusters to include more columns.

Answer

Change the partition type to ingestion-time partitioning.

Answer

Add an ORDER BY clause to the query.

Question 13

A company has a BigQuery table that stores JSON data in a single column. They want to allow BI analysts to query nested fields using standard SQL. What is the best approach to make the data more query-friendly for BI tools?

Accepted Answer

Unnest the JSON into multiple columns using a persistent table with a flattened schema.. Option A is correct because flattening the JSON into a persistent table with a normalized schema eliminates the need for runtime parsing, allowing BI tools to query nested fields directly with standard SQL. This approach improves query performance by avoiding repeated JSON function calls and enables the use of indexed columns, which is critical for interactive BI workloads.

Answer

Use BigQuery's automatic schema detection to infer the structure.

Answer

Create a view that uses JSON_QUERY and JSON_VALUE functions to expose nested fields as columns.

Answer

Use the EXTRACT function to parse JSON fields in each query.

Question 14

A company needs to store raw event logs for future BI analysis. The logs are semistructured with varying fields. Which BigQuery data type should they use to store the event payload?

Accepted Answer

JSON. Option D is correct because BigQuery's JSON data type is designed to store semistructured data with varying fields, such as raw event logs. It allows schema flexibility, efficient querying of nested fields using JSON functions like `JSON_EXTRACT`, and avoids the need to predefine a rigid schema, which is ideal for BI analysis of event payloads.

Answer

ARRAY

Answer

STRING

Answer

FLOAT64

Question 15

A BI analyst wrote a query that computes the running total of sales over time for each product. The query uses a window function with an ORDER BY clause. The results are correct, but the query processes a large amount of data and is slow. What is the most efficient way to optimize this query?

Accepted Answer

Add a PARTITION BY clause to the window function.. Option D is correct because adding a PARTITION BY clause to the window function allows the running total to be computed independently for each product, which reduces the data set the window function must sort and aggregate over. Without PARTITION BY, the query computes a single running total across all products, forcing the database engine to process the entire table as one partition, which is inefficient for large datasets. Partitioning by product ensures that the ORDER BY and frame operations are scoped to each product group, significantly reducing memory and CPU usage.

Answer

Use the LAG function instead of a window function.

Answer

Materialize the running total in a separate table using a scheduled query.

Answer

Use the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame.