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

Question 1

A BI dashboard query is slow and high cost. The query does multiple joins on large tables and uses window functions. The data engineer suggests using materialized views. However, the query uses non-deterministic functions. What is the limitation?

Accepted Answer

Materialized views cannot include non-deterministic functions. Materialized views store the result set of a query physically, like a table. If the query includes non-deterministic functions (e.g., NOW(), RAND(), CURRENT_TIMESTAMP), the stored result would become stale immediately because the function's output changes each time it is evaluated. Most SQL databases (e.g., PostgreSQL, Oracle, Snowflake) explicitly forbid non-deterministic functions in materialized view definitions to prevent this logical inconsistency.

Answer

Materialized views cannot be updated automatically

Answer

Materialized views cannot be created with joins

Answer

Materialized views only support simple aggregation

Question 2

Refer to the exhibit. What is the effect of the partition_expiration_days option?

Accepted Answer

Partitions older than 365 days are automatically deleted. The `partition_expiration_days` option in BigQuery automatically drops partitions that are older than the specified number of days, reducing storage costs and simplifying lifecycle management. When set to 365, any partition with a date older than 365 days from the current date is deleted by BigQuery's background maintenance process.

Answer

The table's storage cost is reduced by 365%

Answer

Queries that reference data older than 365 days will fail

Answer

The table will be partitioned into 365 partitions

Question 3

Refer to the exhibit. What is the likely cause of this error?

Accepted Answer

The query does not include WHERE clause with partition column. The error occurs because the query attempts to access a partitioned table without specifying the partition column in the WHERE clause. In Snowflake (the platform implied by PCDE context), querying a large partitioned table without a partition filter forces a full scan of all partitions, which can exceed resource limits or time out. The correct approach is to include the partition column in the WHERE clause to enable partition pruning.

Answer

The table is a view

Answer

The table is not partitioned

Answer

The user does not have permission to query the table

Question 4

Refer to the exhibit. The query used DATE_TRUNC(order_date, MONTH) as month. order_date is a TIMESTAMP column. What is the data type of the month column in the result?

Accepted Answer

TIMESTAMP. In BigQuery (the SQL engine for the PCDE exam), DATE_TRUNC with a TIMESTAMP input and MONTH granularity returns a TIMESTAMP value, not a DATE or DATETIME. The function truncates the timestamp to the first day of the month at 00:00:00 UTC, preserving the TIMESTAMP data type. Therefore, the month column in the result is of type TIMESTAMP.

Answer

STRING

Answer

DATE

Answer

DATETIME

Question 5

A company is designing a BigQuery data warehouse for sales analytics. They want to minimize query costs when aggregating daily sales by region and product. Which two methods are effective? (Select TWO).

Accepted Answer

Creating a materialized view with GROUP BY region, product, day. Option A is correct because a materialized view in BigQuery pre-computes and stores the results of the GROUP BY query on region, product, and day. When the underlying data changes, the materialized view is incrementally refreshed, so queries that match the view's aggregation are served directly from the stored results, avoiding full table scans and reducing query costs (bytes processed). This is ideal for recurring aggregation patterns like daily sales summaries.

Answer

Using a view that queries the raw data with WHERE clause

Answer

Storing pre-aggregated results in a separate table and updating nightly

Answer

Creating indexes on the raw table

Question 6

A data team uses BigQuery and wants to ensure data freshness for BI reports with low latency. Which three techniques can help achieve near-real-time updates? (Select THREE).

Accepted Answer

Use a live view that queries the source table directly. Option B is correct because a live view (also known as a logical view) queries the source table directly each time it is accessed, ensuring that BI reports always see the most current data without any materialization delay. This provides near-real-time freshness by avoiding periodic refresh cycles.

Answer

Create a scheduled query that rewrites the entire table every hour

Answer

Use BigQuery's BI Engine for caching

Question 7

A BigQuery dataset contains a table with a STRUCT column for customer address. The BI team needs to query the city field from the struct. Which two approaches are valid? (Select TWO).

Accepted Answer

SELECT JSON_EXTRACT(TO_JSON(address), '$.city') FROM table. Option B is correct because `JSON_EXTRACT(TO_JSON(address), '$.city')` converts the STRUCT to a JSON string and then extracts the `city` field using JSONPath syntax. Option C is correct because BigQuery allows direct field access on a STRUCT column using dot notation (`address.city`), which is the standard SQL syntax for nested fields.

Answer

SELECT UNNEST(address) as city FROM table

Answer

SELECT address['city'] FROM table

Answer

SELECT address.city.standard FROM table

Question 8

A company runs near-real-time dashboards on BigQuery that query a table partitioned by day and clustered by user_id. The most common query filters on user_id and then aggregates sales over the last 7 days. However, many queries still scan full partitions. What is the most likely cause?

Accepted Answer

The table is not clustered on user_id, or the clustering expression does not match the filter.. Option D is correct because the most common cause of full partition scans despite partitioning by day and clustering by user_id is that the clustering expression does not match the filter predicate. In BigQuery, clustering only prunes blocks within a partition when the filter column exactly matches the clustering key; if the filter uses a different expression (e.g., a cast or function) or if clustering is not properly defined, BigQuery falls back to scanning the entire partition. This results in the described behavior where queries still scan full partitions even though the table is partitioned and clustered.

Answer

The dashboard is configured to refresh every 5 minutes, causing too many queries.

Answer

The table uses a wide-column schema with many repeated fields.

Answer

The table is partitioned by hour, not by day.

Question 9

A data engineer creates a clustered table in BigQuery with clustering order: country, city, product_id. The BI team frequently runs a query that filters on city and product_id but rarely on country. What is the most likely performance issue?

Accepted Answer

The query does not filter on the first clustering column (country), so block pruning is minimal.. BigQuery clustered tables use block pruning to skip reading blocks that don't match the query's filter. Pruning is most effective when the filter includes the first clustering column (country). Without it, BigQuery must scan more blocks, leading to higher query costs and slower performance.

Answer

BigQuery allows only one clustering column per table.

Answer

The table should be partitioned by country instead of clustered.

Answer

The query filters on too many clustering columns, causing overhead.

Question 10

A BI developer needs to write a query that calculates total sales by month for the current year. They create a Common Table Expression (CTE) to define monthly aggregates, then reference it in a final SELECT. What is the main benefit of using a CTE over a subquery in this scenario?

Accepted Answer

CTEs enhance query readability and maintainability.. Option D is correct because CTEs improve query readability and maintainability by allowing you to define a named temporary result set once and reference it multiple times in the final SELECT. In this scenario, the CTE clearly separates the monthly aggregation logic from the final output, making the query easier to understand and modify compared to nesting subqueries.

Answer

CTEs are always faster than subqueries.

Answer

CTEs reduce the amount of memory used by the query.

Answer

CTEs automatically cache results for subsequent queries.

Question 11

A company uses BigQuery materialized views to pre-aggregate sales data for a BI dashboard. The dashboard requires near-real-time data, but the materialized view currently reflects data up to 30 minutes old. What is the most effective way to reduce the refresh interval without significantly increasing costs?

Accepted Answer

Reduce the max_staleness parameter of the materialized view.. Reducing the `max_staleness` parameter directly controls the maximum acceptable age of the data in a BigQuery materialized view. By lowering this value, you force the view to refresh more frequently, achieving near-real-time data without incurring the cost of a full manual refresh or additional streaming infrastructure. This parameter is designed to balance freshness against cost, making it the most effective and efficient solution.

Answer

Disable automatic refresh and schedule a manual refresh every minute.

Answer

Use a streaming buffer with the base table to reduce latency.

Answer

Create additional materialized views with overlapping time windows.

Question 12

A BI team uses BigQuery BI Engine to accelerate dashboards. They have a 100 GB table and enable BI Engine with a reservation of 10 GB. Some queries on this table are still slow. What is the most likely reason?

Accepted Answer

The query selects columns that are not fully cached due to the small reservation size.. BI Engine accelerates queries by caching columns in memory. With a 100 GB table and only a 10 GB reservation, the cache can hold only a fraction of the table's columns. Queries that reference columns not fully cached will fall back to BigQuery's standard execution, causing slow performance.

Answer

BI Engine only works with SQL views, not direct tables.

Answer

The table uses clustering, which BI Engine ignores.

Answer

The table is partitioned, which BI Engine does not support.

Question 13

A BI developer is designing a BigQuery dataset for a sales dashboard. Which column naming convention is considered a best practice for column names in BI reports?

Accepted Answer

Use descriptive, snake_case names (e.g., total_revenue).. BigQuery column names are case-insensitive but must follow standard SQL naming rules. Using descriptive snake_case (e.g., total_revenue) improves readability, avoids ambiguity, and is consistent with BigQuery's own system tables and best practices for BI tools like Looker or Tableau, which often expect clean, underscore-separated identifiers.

Answer

Use names with spaces (e.g., Total Revenue).

Answer

Use short, cryptic abbreviations (e.g., tr).

Answer

Use camelCase names (e.g., totalRevenue).

Question 14

A BI query uses COUNT(column) to count non-null values and COUNT(*) to count all rows. The analyst expects both counts to be equal, but COUNT(column) returns fewer rows. What is the most likely explanation?

Accepted Answer

The column contains NULL values, which are not counted by COUNT(column).. COUNT(column) ignores NULL values in the specified column, while COUNT(*) counts every row in the result set regardless of NULLs. If the column contains any NULLs, COUNT(column) will return a lower number. This is a fundamental SQL behavior defined in the ANSI SQL standard and is consistent across all major BI platforms (e.g., Tableau, Power BI, Looker) that generate SQL queries.

Answer

The query has a WHERE clause that filters some rows.

Answer

COUNT(*) is faster, so it's not accurate.

Answer

COUNT(*) counts duplicate rows, while COUNT(column) does not.

Question 15

A BigQuery table is partitioned by ingestion time (pseudo column _PARTITIONTIME) and uses the default partition expiration of 90 days. A data engineer runs a DELETE statement to remove rows older than 100 days. Why does this query process more bytes than expected?

Accepted Answer

The DELETE statement must scan all partitions because it uses a condition that does not prune partitions.. Option D is correct because the DELETE statement uses a condition that does not reference the partitioning column (_PARTITIONTIME) in a way that allows partition pruning. Since the table is partitioned by ingestion time, BigQuery must scan all partitions to evaluate the filter, even though the condition logically targets rows older than 100 days. This results in processing more bytes than expected, as the default partition expiration of 90 days does not reduce the scan scope when the WHERE clause does not leverage the partitioning column.

Answer

The table is not partitioned; it is clustered.

Answer

The DELETE statement does not use a WHERE clause on a clustering column.

Answer

The DELETE statement filters on a custom timestamp column instead of _PARTITIONTIME.