PCDE Practice Test 16 — 15 Questions

Question 1

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 2

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 3

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.

Question 4

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 5

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 6

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 7

In BigQuery, a BI analyst wants to store financial data with high precision and avoid rounding errors. Which data type should be used for currency columns?

Accepted Answer

NUMERIC. NUMERIC (also known as DECIMAL) is the correct choice because it stores exact numeric values with up to 38 digits of precision and a user-defined scale, making it ideal for financial data where rounding errors from binary floating-point representation are unacceptable. In BigQuery, NUMERIC uses fixed-point arithmetic, ensuring that calculations like tax or interest accruals remain exact to the specified decimal places.

Answer

FLOAT64

Answer

INT64

Answer

STRING

Question 8

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 9

A company uses BigQuery with a table 'orders' that has a column 'items' of type ARRAY>. An analyst needs to find orders that contain a specific product, 'ABC'. Which query is most efficient?

Accepted Answer

SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM UNNEST(items) WHERE product_id = 'ABC'). Option A is correct because it uses a correlated subquery with `UNNEST` and `EXISTS`, which stops scanning as soon as a matching product_id is found within each row's array. This is the most efficient pattern for checking array membership in BigQuery, as it avoids unnecessary row multiplication and leverages short-circuit evaluation.

Answer

SELECT * FROM orders WHERE ARRAY_LENGTH(items) > 0

Answer

SELECT * FROM orders WHERE 'ABC' IN UNNEST(items)

Answer

SELECT o.*, item FROM orders o, UNNEST(items) item WHERE item.product_id = 'ABC'

Question 10

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 11

Refer to the exhibit. A data engineer created a materialized view on a table that receives streaming inserts. When they query the materialized view, they get this error. What is the most likely cause?

Accepted Answer

The materialized view cannot read data from the streaming buffer.. The error occurs because materialized views in BigQuery cannot directly read data from the streaming buffer. When a base table receives streaming inserts, the data resides in the streaming buffer for up to 90 minutes before being committed to storage. Materialized views only reflect committed data, so querying them during this window returns an error indicating that the view cannot access the streaming buffer.

Answer

The materialized view definition includes a JOIN that is not supported.

Answer

The materialized view has reached its maximum size limit.

Answer

The base table has a schema change that the materialized view cannot adapt to.

Question 12

Which THREE of the following SQL techniques are commonly used to improve BI query performance in BigQuery?

Accepted Answer

Avoid JOINs by storing all relevant data in a single table. Option B is correct because denormalizing data into a single table avoids expensive JOIN operations, which in BigQuery can cause significant performance degradation due to shuffling and data redistribution across slots. By storing all relevant data in one table, you reduce the need for large-scale data shuffling, leading to faster query execution and lower slot consumption.

Answer

Select all columns using SELECT * to avoid missing data

Answer

Use self-joins to compare rows within the same table

Question 13

A production Cloud SQL for PostgreSQL instance needs to handle increased read traffic and provide automatic failover in case of a zone outage. Which architecture satisfies both requirements?

Accepted Answer

Enable high availability (regional) and create a read replica in the same region.. Option A is correct because enabling high availability (regional) for Cloud SQL for PostgreSQL creates a primary and standby instance in different zones within the same region, providing automatic failover during a zone outage. Adding a read replica in the same region offloads read traffic from the primary instance, satisfying the increased read traffic requirement while the HA configuration ensures high availability.

Answer

Enable high availability only.

Answer

Use a CMEK key and enable binary logging.

Answer

Create multiple read replicas without HA.

Question 14

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 15

You are a database engineer for an e-commerce company. The company uses BigQuery for its BI and analytics. The data pipeline stages raw event data into a table 'raw_events' with columns: event_id, user_id, event_time, event_type, and a JSON string 'event_data'. The BI team wants to query this data for user behavior analysis, but the JSON parsing makes queries slow. They need to perform frequent queries that extract specific fields from the JSON and filter by event_time. The table 'raw_events' is not partitioned and has 2 billion rows. What is the most effective single step to improve query performance and reduce cost?

Accepted Answer

Partition the table on event_time and cluster on event_type. Partitioning the table on event_time allows BigQuery to prune entire partitions when queries filter by event_time, drastically reducing the amount of data scanned. Clustering on event_type further organizes data within each partition, enabling block-level pruning for queries that filter or aggregate by event_type. This combination directly addresses the slow JSON parsing and high cost by minimizing scanned bytes, which is the most effective single step for a 2-billion-row table.

Answer

Create a view that extracts JSON fields into columns

Answer

Increase BigQuery slots to maximum

Answer

Use a materialized view to precompute common queries

PCDE Practice Test 16 — 15 Questions

Exhibit

PCDE Practice Test 16 — 15 Questions

Exhibit